CREATE TABLE IF NOT EXISTS lead_contacts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lead_uuid CHAR(36) NOT NULL,
    email VARCHAR(255) NOT NULL,
    normalized_email VARCHAR(255) NOT NULL,
    first_name VARCHAR(120) DEFAULT NULL,
    primary_vertical VARCHAR(64) NOT NULL,
    secondary_vertical VARCHAR(64) DEFAULT NULL,
    persona VARCHAR(64) DEFAULT NULL,
    timeline VARCHAR(64) DEFAULT NULL,
    state_code VARCHAR(32) DEFAULT NULL,
    acquisition_source VARCHAR(128) DEFAULT NULL,
    landing_slug VARCHAR(128) DEFAULT NULL,
    utm_source VARCHAR(255) DEFAULT NULL,
    utm_medium VARCHAR(255) DEFAULT NULL,
    utm_campaign VARCHAR(255) DEFAULT NULL,
    utm_content VARCHAR(255) DEFAULT NULL,
    utm_term VARCHAR(255) DEFAULT NULL,
    referrer_url TEXT DEFAULT NULL,
    lead_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    buyer_status ENUM('new','qualified','routed','exported','suppressed') NOT NULL DEFAULT 'new',
    consent_email_marketing TINYINT(1) NOT NULL DEFAULT 0,
    consent_partner_offers TINYINT(1) NOT NULL DEFAULT 0,
    consent_version VARCHAR(32) NOT NULL DEFAULT 'cashhive-lead-v1',
    consent_text TEXT NOT NULL,
    consented_at DATETIME NOT NULL,
    ip_address VARCHAR(64) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_submitted_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_lead_contacts_normalized_email (normalized_email),
    UNIQUE KEY uniq_lead_contacts_uuid (lead_uuid),
    KEY idx_lead_contacts_primary_vertical (primary_vertical),
    KEY idx_lead_contacts_buyer_status (buyer_status),
    KEY idx_lead_contacts_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_answers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lead_contact_id BIGINT UNSIGNED NOT NULL,
    submission_token CHAR(36) NOT NULL,
    question_key VARCHAR(100) NOT NULL,
    answer_value TEXT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_lead_answers_contact_id (lead_contact_id),
    KEY idx_lead_answers_submission_token (submission_token),
    KEY idx_lead_answers_question_key (question_key),
    CONSTRAINT fk_lead_answers_contact_id
        FOREIGN KEY (lead_contact_id) REFERENCES lead_contacts(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lead_contact_id BIGINT UNSIGNED NOT NULL,
    submission_token CHAR(36) NOT NULL,
    event_type VARCHAR(64) NOT NULL,
    event_meta JSON DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_lead_events_contact_id (lead_contact_id),
    KEY idx_lead_events_type (event_type),
    KEY idx_lead_events_submission_token (submission_token),
    CONSTRAINT fk_lead_events_contact_id
        FOREIGN KEY (lead_contact_id) REFERENCES lead_contacts(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
