-- Opportunities 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 `sales_stages` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(100)        NOT NULL,
    `code`                  VARCHAR(20)         NOT NULL,
    `sort_order`            INT                 NOT NULL DEFAULT 0,
    `default_probability`   TINYINT UNSIGNED    NOT NULL DEFAULT 0,
    `is_won`                TINYINT(1)          NOT NULL DEFAULT 0,
    `is_lost`               TINYINT(1)          NOT NULL DEFAULT 0,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `description`           TEXT                NULL,
    `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`),
    KEY `sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `competitors` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(150)        NOT NULL,
    `code`                  VARCHAR(30)         NULL,
    `website`               VARCHAR(255)        NULL,
    `strengths`             TEXT                NULL,
    `weaknesses`            TEXT                NULL,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `notes`                 TEXT                NULL,
    `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`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `opportunities` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(200)        NOT NULL,
    `company_id`            BIGINT UNSIGNED     NULL,
    `contact_id`            BIGINT UNSIGNED     NULL,
    `sales_stage_id`        BIGINT UNSIGNED     NULL,
    `competitor_id`         BIGINT UNSIGNED     NULL,
    `expected_revenue`      DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `probability`           TINYINT UNSIGNED    NOT NULL DEFAULT 0,
    `expected_close_date`   DATE                NULL,
    `status`                ENUM('open','won','lost','abandoned') NOT NULL DEFAULT 'open',
    `description`           TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `company_id` (`company_id`),
    KEY `sales_stage_id` (`sales_stage_id`),
    KEY `status` (`status`),
    KEY `expected_close_date` (`expected_close_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default sales stages
INSERT INTO `sales_stages` (`name`, `code`, `sort_order`, `default_probability`, `is_won`, `is_lost`, `status`, `created_at`)
SELECT 'Qualification', 'QUAL', 10, 10, 0, 0, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sales_stages` WHERE `code` = 'QUAL');

INSERT INTO `sales_stages` (`name`, `code`, `sort_order`, `default_probability`, `is_won`, `is_lost`, `status`, `created_at`)
SELECT 'Needs Analysis', 'NEED', 20, 25, 0, 0, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sales_stages` WHERE `code` = 'NEED');

INSERT INTO `sales_stages` (`name`, `code`, `sort_order`, `default_probability`, `is_won`, `is_lost`, `status`, `created_at`)
SELECT 'Proposal', 'PROP', 30, 50, 0, 0, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sales_stages` WHERE `code` = 'PROP');

INSERT INTO `sales_stages` (`name`, `code`, `sort_order`, `default_probability`, `is_won`, `is_lost`, `status`, `created_at`)
SELECT 'Negotiation', 'NEGO', 40, 75, 0, 0, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sales_stages` WHERE `code` = 'NEGO');

INSERT INTO `sales_stages` (`name`, `code`, `sort_order`, `default_probability`, `is_won`, `is_lost`, `status`, `created_at`)
SELECT 'Closed Won', 'WON', 50, 100, 1, 0, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sales_stages` WHERE `code` = 'WON');

INSERT INTO `sales_stages` (`name`, `code`, `sort_order`, `default_probability`, `is_won`, `is_lost`, `status`, `created_at`)
SELECT 'Closed Lost', 'LOST', 60, 0, 0, 1, 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sales_stages` WHERE `code` = 'LOST');

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- Grant all opportunity 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 'opportunities.%' OR p.slug LIKE 'sales_stages.%'
       OR p.slug LIKE 'expected_revenue.%' OR p.slug LIKE 'probability.%'
       OR p.slug LIKE 'competitors.%')
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
