CREATE TABLE IF NOT EXISTS lead_forms (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    slug VARCHAR(120) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    success_message VARCHAR(255) NULL,
    redirect_url VARCHAR(255) NULL,
    settings_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_lead_forms_slug (tenant_id, slug),
    CONSTRAINT fk_lead_forms_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_form_fields (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    form_id BIGINT UNSIGNED NOT NULL,
    field_key VARCHAR(80) NOT NULL,
    label VARCHAR(140) NOT NULL,
    field_type ENUM('text', 'email', 'phone', 'textarea', 'number') NOT NULL DEFAULT 'text',
    is_required TINYINT(1) NOT NULL DEFAULT 0,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_lead_form_fields (form_id, field_key),
    CONSTRAINT fk_lead_form_fields_form FOREIGN KEY (form_id) REFERENCES lead_forms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_captures (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    form_id BIGINT UNSIGNED NULL,
    source ENUM('web_form', 'csv_import', 'manual', 'api') NOT NULL DEFAULT 'manual',
    full_name VARCHAR(180) NOT NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(50) NULL,
    company VARCHAR(160) NULL,
    notes TEXT NULL,
    utm_source VARCHAR(120) NULL,
    utm_medium VARCHAR(120) NULL,
    utm_campaign VARCHAR(120) NULL,
    score SMALLINT NOT NULL DEFAULT 0,
    status ENUM('new', 'qualified', 'rejected', 'converted') NOT NULL DEFAULT 'new',
    assigned_user_id BIGINT UNSIGNED NULL,
    converted_contact_id BIGINT UNSIGNED NULL,
    payload_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_lead_captures_tenant_status (tenant_id, status, created_at),
    INDEX idx_lead_captures_tenant_assigned (tenant_id, assigned_user_id, created_at),
    CONSTRAINT fk_lead_captures_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_captures_form FOREIGN KEY (form_id) REFERENCES lead_forms(id) ON DELETE SET NULL,
    CONSTRAINT fk_lead_captures_assigned_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_lead_captures_contact FOREIGN KEY (converted_contact_id) REFERENCES contacts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_assignment_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    priority INT UNSIGNED NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    condition_json JSON NULL,
    assign_type ENUM('round_robin', 'user') NOT NULL DEFAULT 'round_robin',
    assign_user_id BIGINT UNSIGNED NULL,
    last_assigned_user_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_lead_assignment_rules_tenant_priority (tenant_id, is_active, priority),
    CONSTRAINT fk_lead_assignment_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_assignment_rules_user FOREIGN KEY (assign_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_lead_assignment_rules_last_user FOREIGN KEY (last_assigned_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_score_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    points SMALLINT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    condition_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_score_rules_tenant (tenant_id, is_active),
    CONSTRAINT fk_lead_score_rules_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_routing_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    lead_capture_id BIGINT UNSIGNED NOT NULL,
    rule_id BIGINT UNSIGNED NULL,
    assigned_user_id BIGINT UNSIGNED NULL,
    score_before SMALLINT NOT NULL DEFAULT 0,
    score_after SMALLINT NOT NULL DEFAULT 0,
    reason VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_routing_logs_tenant (tenant_id, created_at),
    CONSTRAINT fk_lead_routing_logs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_routing_logs_lead FOREIGN KEY (lead_capture_id) REFERENCES lead_captures(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_routing_logs_rule FOREIGN KEY (rule_id) REFERENCES lead_assignment_rules(id) ON DELETE SET NULL,
    CONSTRAINT fk_lead_routing_logs_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO lead_forms (tenant_id, name, slug, is_active, success_message)
SELECT t.id, 'Formulario Web Demo', 'web-demo', 1, 'Gracias, te contactaremos pronto.'
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE name = VALUES(name), is_active = VALUES(is_active), success_message = VALUES(success_message);

INSERT INTO lead_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT lf.id, 'full_name', 'Nombre completo', 'text', 1, 1
FROM lead_forms lf
JOIN tenants t ON t.id = lf.tenant_id
WHERE t.slug = 'demo' AND lf.slug = 'web-demo'
ON DUPLICATE KEY UPDATE label = VALUES(label), field_type = VALUES(field_type), is_required = VALUES(is_required), sort_order = VALUES(sort_order);

INSERT INTO lead_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT lf.id, 'email', 'Email', 'email', 1, 2
FROM lead_forms lf
JOIN tenants t ON t.id = lf.tenant_id
WHERE t.slug = 'demo' AND lf.slug = 'web-demo'
ON DUPLICATE KEY UPDATE label = VALUES(label), field_type = VALUES(field_type), is_required = VALUES(is_required), sort_order = VALUES(sort_order);

INSERT INTO lead_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT lf.id, 'phone', 'Telefono', 'phone', 0, 3
FROM lead_forms lf
JOIN tenants t ON t.id = lf.tenant_id
WHERE t.slug = 'demo' AND lf.slug = 'web-demo'
ON DUPLICATE KEY UPDATE label = VALUES(label), field_type = VALUES(field_type), is_required = VALUES(is_required), sort_order = VALUES(sort_order);

INSERT INTO lead_score_rules (tenant_id, name, points, is_active, condition_json)
SELECT t.id, 'Lead con email', 20, 1, JSON_OBJECT('field', 'email', 'operator', 'not_empty')
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE points = VALUES(points), condition_json = VALUES(condition_json);

INSERT INTO lead_score_rules (tenant_id, name, points, is_active, condition_json)
SELECT t.id, 'Lead con telefono', 15, 1, JSON_OBJECT('field', 'phone', 'operator', 'not_empty')
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE points = VALUES(points), condition_json = VALUES(condition_json);

INSERT INTO lead_assignment_rules (tenant_id, name, priority, is_active, assign_type)
SELECT t.id, 'Round robin comercial', 100, 1, 'round_robin'
FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_active = VALUES(is_active), assign_type = VALUES(assign_type), priority = VALUES(priority);
