-- Leads CRM System Schema for MK Construction

CREATE TABLE IF NOT EXISTS `lead_sources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `lead_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `color_code` varchar(20) DEFAULT '#64748b',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `lead_funnels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `description` text,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `lead_stages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `funnel_id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `stage_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `fk_funnel` (`funnel_id`),
  CONSTRAINT `fk_funnel_stages` FOREIGN KEY (`funnel_id`) REFERENCES `lead_funnels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `leads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `source_id` int(11) DEFAULT NULL,
  `status_id` int(11) DEFAULT NULL,
  `funnel_id` int(11) DEFAULT NULL,
  `stage_id` int(11) DEFAULT NULL,
  `is_junk` tinyint(1) DEFAULT 0,
  `notes` text,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_source` (`source_id`),
  KEY `fk_status` (`status_id`),
  KEY `fk_lead_funnel` (`funnel_id`),
  KEY `fk_lead_stage` (`stage_id`),
  CONSTRAINT `fk_lead_source_c` FOREIGN KEY (`source_id`) REFERENCES `lead_sources` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_lead_status_c` FOREIGN KEY (`status_id`) REFERENCES `lead_statuses` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_lead_funnel_c` FOREIGN KEY (`funnel_id`) REFERENCES `lead_funnels` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_lead_stage_c` FOREIGN KEY (`stage_id`) REFERENCES `lead_stages` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `lead_followups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lead_id` int(11) NOT NULL,
  `followup_date` datetime NOT NULL,
  `notes` text,
  `status` enum('pending', 'completed', 'cancelled') DEFAULT 'pending',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_lead` (`lead_id`),
  CONSTRAINT `fk_followup_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert some default values to get started quickly
INSERT INTO `lead_sources` (`name`) VALUES ('Website Enquiries'), ('Direct Call'), ('WhatsApp'), ('JustDial') ON DUPLICATE KEY UPDATE id=id;
INSERT INTO `lead_statuses` (`name`, `color_code`) VALUES ('New Lead', '#3b82f6'), ('Contacted', '#f59e0b'), ('In Progress', '#8b5cf6'), ('Converted', '#22c55e'), ('Lost', '#ef4444') ON DUPLICATE KEY UPDATE id=id;
