ALTER TABLE lead_forms
    ADD COLUMN IF NOT EXISTS require_double_opt_in TINYINT(1) NOT NULL DEFAULT 0 AFTER is_active,
    ADD COLUMN IF NOT EXISTS anti_spam_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER require_double_opt_in,
    ADD COLUMN IF NOT EXISTS honeypot_field VARCHAR(80) NULL AFTER anti_spam_enabled,
    ADD COLUMN IF NOT EXISTS require_captcha TINYINT(1) NOT NULL DEFAULT 0 AFTER honeypot_field;

ALTER TABLE lead_captures
    ADD COLUMN IF NOT EXISTS normalized_email VARCHAR(190) NULL AFTER email,
    ADD COLUMN IF NOT EXISTS normalized_phone VARCHAR(50) NULL AFTER phone,
    ADD COLUMN IF NOT EXISTS source_detail VARCHAR(120) NULL AFTER source,
    ADD COLUMN IF NOT EXISTS territory VARCHAR(120) NULL AFTER company,
    ADD COLUMN IF NOT EXISTS industry VARCHAR(120) NULL AFTER territory,
    ADD COLUMN IF NOT EXISTS first_contact_at DATETIME NULL AFTER assigned_user_id,
    ADD COLUMN IF NOT EXISTS sla_due_at DATETIME NULL AFTER first_contact_at,
    ADD COLUMN IF NOT EXISTS qualification_stage ENUM('raw', 'mql', 'sql', 'nurturing', 'discarded') NOT NULL DEFAULT 'raw' AFTER status,
    ADD COLUMN IF NOT EXISTS opt_in_token_hash CHAR(64) NULL AFTER qualification_stage,
    ADD COLUMN IF NOT EXISTS opt_in_expires_at DATETIME NULL AFTER opt_in_token_hash,
    ADD COLUMN IF NOT EXISTS opted_in_at DATETIME NULL AFTER opt_in_expires_at,
    ADD COLUMN IF NOT EXISTS last_activity_at DATETIME NULL AFTER opted_in_at,
    ADD COLUMN IF NOT EXISTS decay_exempt TINYINT(1) NOT NULL DEFAULT 0 AFTER last_activity_at;

ALTER TABLE lead_assignment_rules
    ADD COLUMN IF NOT EXISTS team_id BIGINT UNSIGNED NULL AFTER assign_user_id,
    ADD COLUMN IF NOT EXISTS max_open_leads INT UNSIGNED NULL AFTER team_id,
    ADD COLUMN IF NOT EXISTS strategy ENUM('round_robin', 'user', 'team_round_robin', 'least_load') NOT NULL DEFAULT 'round_robin' AFTER max_open_leads,
    ADD CONSTRAINT fk_lead_assignment_rules_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS lead_form_submissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    form_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    honeypot_value VARCHAR(255) NULL,
    captcha_passed TINYINT(1) NOT NULL DEFAULT 0,
    spam_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('accepted', 'blocked') NOT NULL DEFAULT 'accepted',
    payload_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_form_submissions_tenant_form (tenant_id, form_id, created_at),
    CONSTRAINT fk_lead_form_submissions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_form_submissions_form FOREIGN KEY (form_id) REFERENCES lead_forms(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_duplicates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    lead_capture_id BIGINT UNSIGNED NULL,
    existing_lead_id BIGINT UNSIGNED NULL,
    existing_contact_id BIGINT UNSIGNED NULL,
    match_type ENUM('email', 'phone', 'email_phone') NOT NULL,
    confidence_score TINYINT UNSIGNED NOT NULL DEFAULT 80,
    resolved TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_duplicates_tenant (tenant_id, resolved, created_at),
    CONSTRAINT fk_lead_duplicates_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_duplicates_lead FOREIGN KEY (lead_capture_id) REFERENCES lead_captures(id) ON DELETE SET NULL,
    CONSTRAINT fk_lead_duplicates_existing_lead FOREIGN KEY (existing_lead_id) REFERENCES lead_captures(id) ON DELETE SET NULL,
    CONSTRAINT fk_lead_duplicates_contact FOREIGN KEY (existing_contact_id) REFERENCES contacts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_change_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    lead_capture_id BIGINT UNSIGNED NOT NULL,
    actor_user_id BIGINT UNSIGNED NULL,
    change_type VARCHAR(80) NOT NULL,
    old_value JSON NULL,
    new_value JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_change_logs_tenant_lead (tenant_id, lead_capture_id, created_at),
    CONSTRAINT fk_lead_change_logs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_change_logs_lead FOREIGN KEY (lead_capture_id) REFERENCES lead_captures(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_change_logs_user FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_webhook_deliveries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    endpoint_id BIGINT UNSIGNED NOT NULL,
    event_type VARCHAR(80) NOT NULL,
    payload_json JSON NULL,
    status ENUM('pending', 'sent', 'failed') NOT NULL DEFAULT 'pending',
    http_status SMALLINT NULL,
    response_body TEXT NULL,
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    next_attempt_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME NULL,
    INDEX idx_lead_webhook_deliveries_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_lead_webhook_deliveries_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_webhook_deliveries_endpoint FOREIGN KEY (endpoint_id) REFERENCES lead_webhook_endpoints(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_csv_import_batches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    created_by BIGINT UNSIGNED NULL,
    source_name VARCHAR(140) NOT NULL,
    mapping_json JSON NULL,
    strict_mode TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('preview', 'processing', 'applied', 'rolled_back', 'failed') NOT NULL DEFAULT 'preview',
    total_rows INT UNSIGNED NOT NULL DEFAULT 0,
    valid_rows INT UNSIGNED NOT NULL DEFAULT 0,
    invalid_rows INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME NULL,
    INDEX idx_lead_csv_import_batches_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_lead_csv_import_batches_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_csv_import_batches_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_csv_import_preview_rows (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    batch_id BIGINT UNSIGNED NOT NULL,
    row_number INT UNSIGNED NOT NULL,
    raw_json JSON NULL,
    mapped_json JSON NULL,
    validation_errors JSON NULL,
    status ENUM('valid', 'invalid', 'imported', 'failed') NOT NULL DEFAULT 'valid',
    imported_lead_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_csv_preview_batch (batch_id, row_number),
    CONSTRAINT fk_lead_csv_preview_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_csv_preview_batch FOREIGN KEY (batch_id) REFERENCES lead_csv_import_batches(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_csv_preview_lead FOREIGN KEY (imported_lead_id) REFERENCES lead_captures(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_sla_alerts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    lead_capture_id BIGINT UNSIGNED NOT NULL,
    alert_type ENUM('sla_warning', 'sla_breach') NOT NULL,
    status ENUM('open', 'resolved') NOT NULL DEFAULT 'open',
    message VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    INDEX idx_lead_sla_alerts_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_lead_sla_alerts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_sla_alerts_lead FOREIGN KEY (lead_capture_id) REFERENCES lead_captures(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_decay_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    affected_leads INT UNSIGNED NOT NULL DEFAULT 0,
    decay_points SMALLINT NOT NULL DEFAULT 0,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_decay_runs_tenant (tenant_id, created_at),
    CONSTRAINT fk_lead_decay_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_reassignment_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    affected_leads INT UNSIGNED NOT NULL DEFAULT 0,
    reason VARCHAR(120) NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_reassignment_runs_tenant (tenant_id, created_at),
    CONSTRAINT fk_lead_reassignment_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
