-- =====================================================================
--  E-Factor CRM — Consolidated Schema Upgrade (Sprints 1–7)
--  Target: MySQL 8.x  |  Run ONCE against the efactor database.
--  Prefer `php artisan migrate` (idempotent migrations shipped in
--  database/migrations/). This raw script is a convenience alternative
--  for DBAs who apply DDL directly. Review before running on production.
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- SPRINT 1 — RBAC modules, Lead Categories, lead audit columns
-- ---------------------------------------------------------------------

-- S1-B1: CRM modules for RBAC (adjust column list to your `modules` table)
INSERT INTO `modules` (`slug`,`name`,`is_active`,`created_at`,`updated_at`) VALUES
  ('crm_dashboard','CRM Dashboard',1,NOW(),NOW()),
  ('crm_leads','CRM Leads',1,NOW(),NOW()),
  ('crm_tenders','CRM Tenders',1,NOW(),NOW()),
  ('crm_masters','CRM Masters',1,NOW(),NOW()),
  ('crm_reports','CRM Reports',1,NOW(),NOW())
ON DUPLICATE KEY UPDATE `is_active`=VALUES(`is_active`);

-- S1-B3: Lead Categories master
CREATE TABLE IF NOT EXISTS `crm_lead_categories` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(20) NOT NULL,
  `description` text NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `sort_order` int DEFAULT 0,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `crm_lead_categories_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- S1-B4: audit + category columns on crm_leads
ALTER TABLE `crm_leads`
  ADD COLUMN `lead_category_id` bigint UNSIGNED NULL AFTER `lead_type`,
  ADD COLUMN `stage_changed_at` timestamp NULL AFTER `stage`,
  ADD COLUMN `stage_changed_by` bigint UNSIGNED NULL AFTER `stage_changed_at`,
  ADD COLUMN `followup_count`   int NOT NULL DEFAULT 0 AFTER `next_followup_date`;

-- S1-B7: default lead categories
INSERT INTO `crm_lead_categories` (`name`,`code`,`sort_order`,`is_active`,`created_at`,`updated_at`) VALUES
  ('Software Development','SW_DEV',1,1,NOW(),NOW()),
  ('Networking & Infrastructure','NETWORK',2,1,NOW(),NOW()),
  ('CCTV & Surveillance','CCTV',3,1,NOW(),NOW()),
  ('AMC / Support Contract','AMC',4,1,NOW(),NOW()),
  ('ERP / HRM Solution','ERP',5,1,NOW(),NOW()),
  ('e-Governance','EGOVT',6,1,NOW(),NOW()),
  ('Consultancy','CONSULT',7,1,NOW(),NOW()),
  ('Cloud Services','CLOUD',8,1,NOW(),NOW())
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

-- ---------------------------------------------------------------------
-- SPRINT 2 — Quotation module (line items + GST)
-- ---------------------------------------------------------------------
ALTER TABLE `crm_lead_proposals`
  ADD COLUMN `quot_no` varchar(30) NULL AFTER `proposal_no`,
  ADD COLUMN `sub_total` decimal(14,2) DEFAULT 0 AFTER `proposed_value`,
  ADD COLUMN `discount_pct` decimal(5,2) DEFAULT 0 AFTER `sub_total`,
  ADD COLUMN `discount_amount` decimal(14,2) DEFAULT 0 AFTER `discount_pct`,
  ADD COLUMN `gst_type` enum('cgst_sgst','igst','exempt') DEFAULT 'cgst_sgst' AFTER `discount_amount`,
  ADD COLUMN `gst_pct` decimal(5,2) DEFAULT 18 AFTER `gst_type`,
  ADD COLUMN `cgst_amount` decimal(14,2) DEFAULT 0 AFTER `gst_pct`,
  ADD COLUMN `sgst_amount` decimal(14,2) DEFAULT 0 AFTER `cgst_amount`,
  ADD COLUMN `igst_amount` decimal(14,2) DEFAULT 0 AFTER `sgst_amount`,
  ADD COLUMN `total_gst` decimal(14,2) DEFAULT 0 AFTER `igst_amount`,
  ADD COLUMN `total_amount` decimal(14,2) DEFAULT 0 AFTER `total_gst`,
  ADD COLUMN `place_of_supply` varchar(100) NULL AFTER `total_amount`,
  ADD COLUMN `payment_terms` varchar(255) NULL AFTER `place_of_supply`,
  ADD COLUMN `delivery_terms` varchar(255) NULL AFTER `payment_terms`;

CREATE TABLE IF NOT EXISTS `crm_proposal_items` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `proposal_id` bigint UNSIGNED NOT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  `description` text NOT NULL,
  `unit` varchar(30) NULL,
  `quantity` decimal(10,3) NOT NULL DEFAULT 1,
  `rate` decimal(14,2) NOT NULL DEFAULT 0,
  `discount_pct` decimal(5,2) NOT NULL DEFAULT 0,
  `amount` decimal(14,2) NOT NULL DEFAULT 0,
  `gst_pct` decimal(5,2) NOT NULL DEFAULT 18,
  `cgst_amount` decimal(14,2) NOT NULL DEFAULT 0,
  `sgst_amount` decimal(14,2) NOT NULL DEFAULT 0,
  `igst_amount` decimal(14,2) NOT NULL DEFAULT 0,
  `total_amount` decimal(14,2) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  KEY `crm_proposal_items_proposal_id_idx` (`proposal_id`),
  CONSTRAINT `crm_proposal_items_proposal_id_fk`
    FOREIGN KEY (`proposal_id`) REFERENCES `crm_lead_proposals`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- S2-B5: link converted proposals back to a receivable
ALTER TABLE `fin_receivables` ADD COLUMN `lead_id` bigint UNSIGNED NULL;

-- ---------------------------------------------------------------------
-- SPRINT 3 — Contacts + client enrichment
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_lead_contacts` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `lead_id` bigint UNSIGNED NOT NULL,
  `party_id` bigint UNSIGNED NULL,
  `name` varchar(150) NOT NULL,
  `designation` varchar(100) NULL,
  `department` varchar(100) NULL,
  `email` varchar(150) NULL,
  `mobile` varchar(30) NULL,
  `phone` varchar(30) NULL,
  `role` enum('decision_maker','influencer','technical','financial','admin','other') DEFAULT 'other',
  `is_primary` tinyint(1) NOT NULL DEFAULT 0,
  `notes` varchar(255) NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  KEY `crm_lead_contacts_lead_id_idx` (`lead_id`),
  CONSTRAINT `crm_lead_contacts_lead_id_fk`
    FOREIGN KEY (`lead_id`) REFERENCES `crm_leads`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `fin_parties`
  ADD COLUMN `client_category` enum('govt','psu','private','ngo','educational','individual','other') NULL AFTER `party_type`,
  ADD COLUMN `sector` varchar(100) NULL AFTER `client_category`,
  ADD COLUMN `website` varchar(200) NULL AFTER `phone`,
  ADD COLUMN `annual_turnover` decimal(14,2) NULL AFTER `credit_days`;

-- ---------------------------------------------------------------------
-- SPRINT 4 / 7 — Tender competitor + workflow, project link
-- ---------------------------------------------------------------------
ALTER TABLE `crm_tender_details`
  ADD COLUMN `l2_company` varchar(200) NULL AFTER `is_l1`,
  ADD COLUMN `l2_value` decimal(16,2) NULL AFTER `l2_company`,
  ADD COLUMN `l3_company` varchar(200) NULL AFTER `l2_value`,
  ADD COLUMN `l3_value` decimal(16,2) NULL AFTER `l3_company`,
  ADD COLUMN `work_order_no` varchar(100) NULL AFTER `contract_value`,
  ADD COLUMN `contract_agreement_no` varchar(100) NULL AFTER `work_order_no`,
  ADD COLUMN `tender_fee` decimal(14,2) NULL AFTER `estimated_cost`,
  ADD COLUMN `go_no_go_by` bigint UNSIGNED NULL AFTER `bid_status`,
  ADD COLUMN `go_no_go_reason` text NULL AFTER `go_no_go_by`,
  ADD COLUMN `tender_status_changed_at` timestamp NULL;

-- S7-B1 / S4-B4: link a lead to a delivery project
ALTER TABLE `crm_leads` ADD COLUMN `project_id` bigint UNSIGNED NULL AFTER `department_id`;