-- Leads module for Sales & CRM workspace
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

CREATE TABLE IF NOT EXISTS `lead_sources` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(100)        NOT NULL,
    `code`                  VARCHAR(20)         NOT NULL,
    `description`           TEXT                NULL,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `code` (`code`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `leads` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `first_name`            VARCHAR(100)        NOT NULL,
    `last_name`             VARCHAR(100)        NOT NULL,
    `company_name`          VARCHAR(150)        NULL,
    `email`                 VARCHAR(191)        NULL,
    `phone`                 VARCHAR(30)         NULL,
    `job_title`             VARCHAR(100)        NULL,
    `lead_source_id`        BIGINT UNSIGNED     NULL,
    `assigned_to`           BIGINT UNSIGNED     NULL,
    `score`                 INT                 NOT NULL DEFAULT 0,
    `status`                ENUM('new','contacted','qualified','unqualified','converted') NOT NULL DEFAULT 'new',
    `priority`              ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
    `estimated_value`       DECIMAL(15,2)       NULL,
    `notes`                 TEXT                NULL,
    `converted_at`          DATETIME            NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `lead_source_id` (`lead_source_id`),
    KEY `assigned_to` (`assigned_to`),
    KEY `status` (`status`),
    KEY `score` (`score`),
    KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `lead_assignment_rules` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(100)        NOT NULL,
    `lead_source_id`        BIGINT UNSIGNED     NULL,
    `assign_to_user_id`     BIGINT UNSIGNED     NOT NULL,
    `priority`              INT                 NOT NULL DEFAULT 0,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `lead_source_id` (`lead_source_id`),
    KEY `assign_to_user_id` (`assign_to_user_id`),
    KEY `status` (`status`),
    KEY `priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `lead_scoring_rules` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(100)        NOT NULL,
    `criterion`             ENUM('has_email','has_phone','has_company','source','estimated_value','priority') NOT NULL,
    `operator`              ENUM('equals','not_equals','greater_than','less_than','is_set') NOT NULL DEFAULT 'is_set',
    `value`                 VARCHAR(255)        NULL,
    `points`                INT                 NOT NULL DEFAULT 0,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `lead_conversions` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `lead_id`               BIGINT UNSIGNED     NOT NULL,
    `converted_by`          BIGINT UNSIGNED     NULL,
    `conversion_type`       ENUM('customer','opportunity') NOT NULL DEFAULT 'customer',
    `notes`                 TEXT                NULL,
    `converted_at`          DATETIME            NOT NULL,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `lead_id` (`lead_id`),
    KEY `converted_by` (`converted_by`),
    KEY `converted_at` (`converted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default lead sources
INSERT INTO `lead_sources` (`name`, `code`, `description`, `status`, `created_at`)
SELECT 'Website', 'WEB', 'Inbound leads from company website', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `lead_sources` WHERE `code` = 'WEB');

INSERT INTO `lead_sources` (`name`, `code`, `description`, `status`, `created_at`)
SELECT 'Referral', 'REF', 'Referred by existing customer or partner', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `lead_sources` WHERE `code` = 'REF');

INSERT INTO `lead_sources` (`name`, `code`, `description`, `status`, `created_at`)
SELECT 'Cold Call', 'CC', 'Outbound cold calling campaign', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `lead_sources` WHERE `code` = 'CC');

INSERT INTO `lead_sources` (`name`, `code`, `description`, `status`, `created_at`)
SELECT 'Trade Show', 'TS', 'Event or exhibition leads', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `lead_sources` WHERE `code` = 'TS');

INSERT INTO `lead_sources` (`name`, `code`, `description`, `status`, `created_at`)
SELECT 'Social Media', 'SM', 'LinkedIn, Facebook, or other social channels', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `lead_sources` WHERE `code` = 'SM');

-- Default scoring rules
INSERT INTO `lead_scoring_rules` (`name`, `criterion`, `operator`, `value`, `points`, `status`, `created_at`)
SELECT 'Has email address', 'has_email', 'is_set', NULL, 10, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `lead_scoring_rules` WHERE `name` = 'Has email address');

INSERT INTO `lead_scoring_rules` (`name`, `criterion`, `operator`, `value`, `points`, `status`, `created_at`)
SELECT 'Has phone number', 'has_phone', 'is_set', NULL, 10, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `lead_scoring_rules` WHERE `name` = 'Has phone number');

INSERT INTO `lead_scoring_rules` (`name`, `criterion`, `operator`, `value`, `points`, `status`, `created_at`)
SELECT 'Has company name', 'has_company', 'is_set', NULL, 15, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `lead_scoring_rules` WHERE `name` = 'Has company name');

INSERT INTO `lead_scoring_rules` (`name`, `criterion`, `operator`, `value`, `points`, `status`, `created_at`)
SELECT 'High priority lead', 'priority', 'equals', 'high', 20, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `lead_scoring_rules` WHERE `name` = 'High priority lead');

INSERT INTO `lead_scoring_rules` (`name`, `criterion`, `operator`, `value`, `points`, `status`, `created_at`)
SELECT 'Estimated value over 10,000', 'estimated_value', 'greater_than', '10000', 25, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `lead_scoring_rules` WHERE `name` = 'Estimated value over 10,000');

-- Permissions
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'leads', 'leads.view', 'View Leads', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'leads.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'leads', 'leads.create', 'Create Leads', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'leads.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'leads', 'leads.edit', 'Edit Leads', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'leads.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'leads', 'leads.delete', 'Delete Leads', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'leads.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_sources', 'lead_sources.view', 'View Lead Sources', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_sources.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_sources', 'lead_sources.create', 'Create Lead Sources', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_sources.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_sources', 'lead_sources.edit', 'Edit Lead Sources', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_sources.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_sources', 'lead_sources.delete', 'Delete Lead Sources', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_sources.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_assignment', 'lead_assignment.view', 'View Lead Assignment', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_assignment.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_assignment', 'lead_assignment.edit', 'Manage Lead Assignment', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_assignment.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_scoring', 'lead_scoring.view', 'View Lead Scoring', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_scoring.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_scoring', 'lead_scoring.create', 'Create Lead Scoring Rules', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_scoring.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_scoring', 'lead_scoring.edit', 'Edit Lead Scoring Rules', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_scoring.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_scoring', 'lead_scoring.delete', 'Delete Lead Scoring Rules', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_scoring.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_conversion', 'lead_conversion.view', 'View Lead Conversions', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_conversion.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'lead_conversion', 'lead_conversion.convert', 'Convert Leads', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'lead_conversion.convert');

-- Grant all lead permissions to Administrator role
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created_at`)
SELECT r.id, p.id, NOW()
FROM `roles` r
CROSS JOIN `permissions` p
WHERE r.slug = 'administrator'
  AND (p.slug LIKE 'leads.%' OR p.slug LIKE 'lead_sources.%'
       OR p.slug LIKE 'lead_assignment.%' OR p.slug LIKE 'lead_scoring.%'
       OR p.slug LIKE 'lead_conversion.%')
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
