-- CRM Database Tables for WebBooks Travel ERP

-- Add company_id to existing crm_contacts if not exists
-- ALTER TABLE crm_contacts ADD COLUMN company_id INT NULL AFTER company;
-- ALTER TABLE crm_contacts ADD COLUMN assigned_to INT NULL AFTER company_id;
-- ALTER TABLE crm_contacts ADD COLUMN last_contact_at DATETIME NULL AFTER assigned_to;

-- Create CRM Companies table
CREATE TABLE IF NOT EXISTS crm_companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    industry VARCHAR(100) NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(255) NULL,
    address TEXT NULL,
    website VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_agency (agency_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create CRM Notes table
CREATE TABLE IF NOT EXISTS crm_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contact_id INT NOT NULL,
    author_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_contact (contact_id),
    INDEX idx_author (author_id),
    FOREIGN KEY (contact_id) REFERENCES crm_contacts(id) ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add additional fields to crm_contacts for better CRM functionality
ALTER TABLE crm_contacts ADD COLUMN company_id INT NULL AFTER company;
ALTER TABLE crm_contacts ADD COLUMN assigned_to INT NULL AFTER company_id;
ALTER TABLE crm_contacts ADD COLUMN last_contact_at DATETIME NULL AFTER assigned_to;

-- Update crm_deals to match new stage options
ALTER TABLE crm_deals MODIFY COLUMN stage ENUM('lead', 'quote_sent', 'negotiation', 'confirmed', 'closed_lost') DEFAULT 'lead';

-- Add owner_id to crm_deals for tracking
ALTER TABLE crm_deals ADD COLUMN owner_id INT NULL AFTER contact_id;
ALTER TABLE crm_deals ADD COLUMN probability INT DEFAULT 10 AFTER owner_id;