-- ============================================
-- MIGRACIÓN 004: leads, lead_technical_profiles
-- ============================================

CREATE TABLE IF NOT EXISTS leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    first_name VARCHAR(150) NOT NULL,
    last_name VARCHAR(150) NOT NULL,
    email VARCHAR(200) NULL,
    phone VARCHAR(50) NULL,
    company VARCHAR(200) NULL,
    position VARCHAR(200) NULL,
    city VARCHAR(150) NULL,
    region VARCHAR(150) NULL,
    client_type JSON NULL,
    industry JSON NULL,
    classification VARCHAR(20) NULL,
    observations TEXT NULL,
    source ENUM('manual', 'scan', 'voice') NOT NULL DEFAULT 'manual',
    status ENUM('nuevo', 'contactado', 'calificado', 'propuesta', 'negociacion', 'ganado', 'perdido') NOT NULL DEFAULT 'nuevo',
    is_duplicate TINYINT(1) NOT NULL DEFAULT 0,
    duplicate_of INT NULL,
    loss_reason_id INT NULL,
    created_by INT NOT NULL,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    FOREIGN KEY (duplicate_of) REFERENCES leads(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_technical_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL UNIQUE,
    animal_type VARCHAR(100) NULL,
    herd_size VARCHAR(100) NULL,
    current_supplier VARCHAR(200) NULL,
    interest_area VARCHAR(200) NULL,
    additional_data JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_leads_event_id ON leads(event_id);
CREATE INDEX idx_leads_status ON leads(status);
CREATE INDEX idx_leads_created_by ON leads(created_by);
CREATE INDEX idx_leads_email ON leads(email);
CREATE INDEX idx_leads_phone ON leads(phone);