-- Migration 017: CRM pipeline  -  stages + opportunity tracking
-- Phase 15  -  GHL-compete feature

CREATE TABLE IF NOT EXISTS pipeline_stages (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id          BIGINT UNSIGNED NOT NULL,
    name            VARCHAR(80) NOT NULL,
    position        INT NOT NULL DEFAULT 0,
    color           VARCHAR(7) NOT NULL DEFAULT '#4a90d9',
    win_probability TINYINT UNSIGNED NOT NULL DEFAULT 50,  -- 0 - 100 %
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_org_pos (org_id, position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pipeline_opportunities (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id          BIGINT UNSIGNED NOT NULL,
    lead_id         BIGINT UNSIGNED NULL,
    stage_id        BIGINT UNSIGNED NOT NULL,
    title           VARCHAR(160) NOT NULL,
    value_aud       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    expected_close  DATE NULL,
    assigned_to     BIGINT UNSIGNED NULL,
    lost_reason     TEXT NULL,
    status          ENUM('open','won','lost') NOT NULL DEFAULT 'open',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_org_stage (org_id, stage_id),
    KEY idx_org_status (org_id, status),
    CONSTRAINT fk_opp_stage FOREIGN KEY (stage_id)
        REFERENCES pipeline_stages(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
