-- Migration: Add opportunities system and MKL quoting module fields
-- Date: 2026-03-20
-- Description: Creates opportunities table for grouping quotes, adds MKL quoting
--              fields to deals and deal_items tables.

-- ─── Opportunity sequences ───
CREATE TABLE IF NOT EXISTS opportunity_sequences (
    prefix VARCHAR(10) PRIMARY KEY,
    next_number INT NOT NULL DEFAULT 10001
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO opportunity_sequences (prefix, next_number) VALUES ('OPP', 10001);

-- ─── Opportunities table ───
CREATE TABLE IF NOT EXISTS opportunities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    opp_number VARCHAR(20) UNIQUE,
    account_name VARCHAR(255) NOT NULL,
    customer_org_id INT DEFAULT NULL,
    contact_name VARCHAR(255) DEFAULT NULL,
    contact_email VARCHAR(255) DEFAULT NULL,
    rep VARCHAR(100),
    status ENUM('open','won','lost','stale') DEFAULT 'open',
    lost_reason VARCHAR(255) DEFAULT NULL,
    competitor VARCHAR(255) DEFAULT NULL,
    close_date DATE DEFAULT NULL,
    expected_revenue DECIMAL(12,2) DEFAULT 0,
    notes TEXT,
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_opp_status (status),
    INDEX idx_opp_rep (rep),
    INDEX idx_opp_customer (customer_org_id),
    INDEX idx_opp_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Quote sequences (MKL-native quotes use Q-XXXXXX numbering) ───
CREATE TABLE IF NOT EXISTS quote_sequences (
    prefix VARCHAR(10) PRIMARY KEY,
    next_number INT NOT NULL DEFAULT 100001
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO quote_sequences (prefix, next_number) VALUES ('Q', 100001);

-- ─── Add MKL quoting fields to deals table ───
-- source: distinguishes Zoho-imported deals from MKL-native quotes
ALTER TABLE deals ADD COLUMN IF NOT EXISTS source ENUM('zoho','mkl') DEFAULT 'zoho';
ALTER TABLE deals ADD COLUMN IF NOT EXISTS opportunity_id INT DEFAULT NULL;
ALTER TABLE deals ADD COLUMN IF NOT EXISTS customer_org_id INT DEFAULT NULL;
ALTER TABLE deals ADD COLUMN IF NOT EXISTS contact_name VARCHAR(255) DEFAULT NULL;
ALTER TABLE deals ADD COLUMN IF NOT EXISTS contact_email VARCHAR(255) DEFAULT NULL;
ALTER TABLE deals ADD COLUMN IF NOT EXISTS outcome ENUM('pending','won','lost','superseded') DEFAULT 'pending';
ALTER TABLE deals ADD COLUMN IF NOT EXISTS sent_at TIMESTAMP NULL;
ALTER TABLE deals ADD COLUMN IF NOT EXISTS pdf_path VARCHAR(500) DEFAULT NULL;

-- Make zoho_quote_number nullable (MKL quotes don't have one)
ALTER TABLE deals MODIFY COLUMN zoho_quote_number VARCHAR(100) DEFAULT NULL;

-- Indexes for new columns
ALTER TABLE deals ADD INDEX IF NOT EXISTS idx_deal_source (source);
ALTER TABLE deals ADD INDEX IF NOT EXISTS idx_deal_opportunity (opportunity_id);
ALTER TABLE deals ADD INDEX IF NOT EXISTS idx_deal_outcome (outcome);
ALTER TABLE deals ADD INDEX IF NOT EXISTS idx_deal_customer_org (customer_org_id);

-- ─── Add MKL quoting fields to deal_items table ───
ALTER TABLE deal_items ADD COLUMN IF NOT EXISTS vendor VARCHAR(50) DEFAULT NULL;
ALTER TABLE deal_items ADD COLUMN IF NOT EXISTS vendor_sku VARCHAR(100) DEFAULT NULL;
ALTER TABLE deal_items ADD COLUMN IF NOT EXISTS vendor_qty INT DEFAULT NULL;
ALTER TABLE deal_items ADD COLUMN IF NOT EXISTS is_accessory TINYINT(1) DEFAULT 0;

-- Mark all existing deals as zoho-sourced
UPDATE deals SET source = 'zoho' WHERE source IS NULL;