

CREATE TABLE leads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    broker_id INT UNSIGNED NOT NULL,
    employee_id INT UNSIGNED NULL,
    created_by INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(50) NULL,
    phone_alternatives TEXT NULL,
    validated_phone VARCHAR(50) NULL,
    validated_phone_marked_at DATETIME NULL,
    validated_phone_marked_by INT UNSIGNED NULL,
    cpf VARCHAR(20) NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'novo',
    business_type ENUM('fgts','consignado_clt','consignado_inss','consignado_servidor','credito_pessoal','credito_imobiliario','financiamento_auto','car_equity','cartao_credito','cartao_beneficio','saque_complementar') NULL,
    contract_status ENUM('nao_informado','aguardando_pagamento','pago','cancelado') NOT NULL DEFAULT 'aguardando_pagamento',
    origin VARCHAR(100) NULL,
    potential_value DECIMAL(12,2) NULL,
    notes TEXT NULL,
    last_exported_at DATETIME NULL,
    entered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_leads_broker FOREIGN KEY (broker_id) REFERENCES users (id),
    CONSTRAINT fk_leads_employee FOREIGN KEY (employee_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_leads_creator FOREIGN KEY (created_by) REFERENCES users (id),
    CONSTRAINT fk_leads_validated_phone_user FOREIGN KEY (validated_phone_marked_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_leads_status (status),
    INDEX idx_leads_origin (origin),
    INDEX idx_leads_entered_at (entered_at),
    INDEX idx_leads_business_type (business_type),
    INDEX idx_leads_contract_status (contract_status),
    INDEX idx_leads_cpf (cpf),
    INDEX idx_leads_last_exported_at (last_exported_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_histories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    interaction_type VARCHAR(50) NOT NULL,
    notes TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_history_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_history_user FOREIGN KEY (user_id) REFERENCES users (id),
    INDEX idx_history_lead (lead_id),
    INDEX idx_history_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_reminders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    remind_at DATETIME NOT NULL,
    note VARCHAR(255) NOT NULL,
    status ENUM('pendente','concluido','ignorado') NOT NULL DEFAULT 'pendente',
    notification_status ENUM('pendente','notificado') NOT NULL DEFAULT 'pendente',
    notified_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_reminder_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_reminder_user FOREIGN KEY (user_id) REFERENCES users (id),
    INDEX idx_reminder_status (status),
    INDEX idx_reminder_date (remind_at),
    INDEX idx_reminder_notification (notification_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    label VARCHAR(120) NOT NULL,
    storage_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NULL,
    file_size INT UNSIGNED NULL,
    uploaded_by INT UNSIGNED NULL,
    uploaded_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_documents_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_documents_user FOREIGN KEY (uploaded_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_lead_documents_lead (lead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_opportunities (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    hiscon_snapshot_id INT UNSIGNED NULL,
    contract_origin_code VARCHAR(50) NULL,
    contract_number VARCHAR(80) NULL,
    type ENUM('portabilidade', 'refinanciamento', 'novo_consignado', 'outro') NOT NULL,
    source ENUM('manual_simulation', 'rule_engine') NOT NULL DEFAULT 'rule_engine',
    engine VARCHAR(80) NULL,
    payload JSON NOT NULL,
    status ENUM('nova', 'em_andamento', 'concluida', 'descartada', 'agendada') NOT NULL DEFAULT 'nova',
    priority TINYINT UNSIGNED NOT NULL DEFAULT 3,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    scheduled_for DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_opportunity_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_opportunity_snapshot FOREIGN KEY (hiscon_snapshot_id) REFERENCES lead_hiscon_snapshots (id) ON DELETE SET NULL,
    CONSTRAINT fk_lead_opportunity_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_lead_opportunities_lead (lead_id),
    INDEX idx_lead_opportunities_status (status),
    INDEX idx_lead_opportunities_type (type),
    INDEX idx_lead_opportunities_source (source),
    INDEX idx_lead_opportunities_engine (engine),
    INDEX idx_lead_opportunities_snapshot (hiscon_snapshot_id),
    INDEX idx_lead_opportunities_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE opportunity_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    condition_expression TEXT NOT NULL,
    action_type ENUM('portabilidade', 'refinanciamento', 'novo_consignado', 'custom') NOT NULL DEFAULT 'custom',
    priority TINYINT UNSIGNED NOT NULL DEFAULT 3,
    schedule ENUM('monthly', 'weekly', 'on_import') NOT NULL DEFAULT 'monthly',
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    valid_from DATETIME NULL,
    valid_to DATETIME NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_opportunity_rules_schedule (schedule),
    INDEX idx_opportunity_rules_enabled (enabled),
    INDEX idx_opportunity_rules_validity (valid_from, valid_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE opportunity_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_opportunity_id INT UNSIGNED NOT NULL,
    rule_id INT UNSIGNED NULL,
    log_type ENUM('created', 'updated', 'closed', 'error') NOT NULL,
    message VARCHAR(255) NULL,
    payload JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_opportunity_log_opportunity FOREIGN KEY (lead_opportunity_id) REFERENCES lead_opportunities (id) ON DELETE CASCADE,
    CONSTRAINT fk_opportunity_log_rule FOREIGN KEY (rule_id) REFERENCES opportunity_rules (id) ON DELETE SET NULL,
    INDEX idx_opportunity_logs_opportunity (lead_opportunity_id),
    INDEX idx_opportunity_logs_rule (rule_id),
    INDEX idx_opportunity_logs_type (log_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
    settings_key VARCHAR(100) PRIMARY KEY,
    settings_value TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lgpd_requests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    protocol VARCHAR(30) NOT NULL UNIQUE,
    request_type ENUM('access','rectification','portability','deletion') NOT NULL,
    status ENUM('pending','approved','rejected','executed','cancelled') NOT NULL DEFAULT 'pending',
    requester_name VARCHAR(150) NOT NULL,
    requester_email VARCHAR(150) NOT NULL,
    requester_cpf VARCHAR(20) NULL,
    requester_phone VARCHAR(30) NULL,
    request_reason TEXT NOT NULL,
    requested_scope TEXT NULL,
    data_snapshot LONGTEXT NULL,
    due_at DATETIME NULL,
    approved_by INT UNSIGNED NULL,
    approved_at DATETIME NULL,
    rejected_by INT UNSIGNED NULL,
    rejected_at DATETIME NULL,
    rejection_reason TEXT NULL,
    executed_by INT UNSIGNED NULL,
    executed_at DATETIME NULL,
    execution_notes TEXT NULL,
    request_ip VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_lgpd_requests_approved_by FOREIGN KEY (approved_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_lgpd_requests_rejected_by FOREIGN KEY (rejected_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_lgpd_requests_executed_by FOREIGN KEY (executed_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_lgpd_requests_status (status),
    INDEX idx_lgpd_requests_type (request_type),
    INDEX idx_lgpd_requests_email (requester_email),
    INDEX idx_lgpd_requests_cpf (requester_cpf)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lgpd_request_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id INT UNSIGNED NOT NULL,
    action VARCHAR(50) NOT NULL,
    description TEXT NOT NULL,
    actor_type ENUM('user','admin','system') NOT NULL DEFAULT 'system',
    actor_id INT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lgpd_logs_request FOREIGN KEY (request_id) REFERENCES lgpd_requests (id) ON DELETE CASCADE,
    CONSTRAINT fk_lgpd_logs_actor FOREIGN KEY (actor_id) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_lgpd_logs_request (request_id),
    INDEX idx_lgpd_logs_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_statuses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(50) NOT NULL UNIQUE,
    label VARCHAR(100) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_proposal_status_catalog (
    slug VARCHAR(50) PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    pipeline_group VARCHAR(50) NOT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    is_final TINYINT(1) NOT NULL DEFAULT 0,
    is_positive TINYINT(1) NOT NULL DEFAULT 0,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bank_catalog (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) NOT NULL,
    name VARCHAR(150) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY idx_bank_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_rate_tables (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    description VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO loan_proposal_status_catalog (slug, label, pipeline_group, sort_order, is_final, is_positive, is_system)
VALUES
    ('digitado', 'Digitado', 'preparation', 10, 0, 0, 1),
    ('ag_assinatura', 'Ag. assinatura', 'preparation', 20, 0, 0, 1),
    ('pendente_documento', 'Pend. documento', 'preparation', 30, 0, 0, 1),
    ('enviada', 'Enviada', 'bank_submission', 40, 0, 0, 1),
    ('analise_banco', 'Analise banco', 'bank_process', 50, 0, 0, 1),
    ('ag_saldo_devedor', 'Ag. saldo devedor', 'results', 60, 1, 1, 1),
    ('enviado_quitacao', 'Enviado quitacao', 'results', 70, 1, 1, 1),
    ('saldo_quitado', 'Saldo quitado', 'after_sales', 80, 1, 1, 1),
    ('reprovada', 'Reprovada', 'results', 90, 1, 0, 1),
    ('cancelada', 'Cancelada', 'results', 100, 1, 0, 1);

INSERT INTO bank_catalog (code, name)
VALUES
    ('001', 'Banco do Brasil'),
    ('033', 'Santander'),
    ('104', 'Caixa Economica Federal'),
    ('237', 'Bradesco'),
    ('341', 'Itau'),
    ('748', 'Sicredi');

INSERT INTO loan_rate_tables (slug, name, description)
VALUES
    ('tabela_padrao', 'Tabela padrao', 'Tabela base utilizada para operacoes gerais'),
    ('tabela_promocional', 'Tabela promocional', 'Tabela com taxas diferenciadas para campanhas'),
    ('tabela_parceira', 'Tabela parceira', 'Tabela aplicada a convenios de parceiros');

CREATE TABLE loan_proposals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    broker_id INT UNSIGNED NOT NULL,
    employee_id INT UNSIGNED NULL,
    created_by INT UNSIGNED NOT NULL,
    api_integration_id INT UNSIGNED NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'digitado',
    product_type VARCHAR(60) NOT NULL,
    requested_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    approved_amount DECIMAL(12,2) NULL,
    disbursed_amount DECIMAL(12,2) NULL,
    installment_value DECIMAL(12,2) NULL,
    term_months SMALLINT UNSIGNED NULL,
    interest_rate DECIMAL(8,4) NULL,
    commission_value DECIMAL(12,2) NULL,
    commission_percent DECIMAL(5,2) NULL,
    sign_method ENUM('digital','presencial','biometria') NOT NULL DEFAULT 'digital',
    external_reference VARCHAR(120) NULL,
    proposal_number VARCHAR(80) NULL,
    submitted_at DATETIME NULL,
    approved_at DATETIME NULL,
    funded_at DATETIME NULL,
    paid_at DATETIME NULL,
    last_synced_at DATETIME NULL,
    next_sync_at DATETIME NULL,
    notes TEXT NULL,
    metadata LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_loan_proposal_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_loan_proposal_broker FOREIGN KEY (broker_id) REFERENCES users (id) ON DELETE RESTRICT,
    CONSTRAINT fk_loan_proposal_employee FOREIGN KEY (employee_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_loan_proposal_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE RESTRICT,
    CONSTRAINT fk_loan_proposal_integration FOREIGN KEY (api_integration_id) REFERENCES api_integrations (id) ON DELETE SET NULL,
    CONSTRAINT fk_loan_proposal_status FOREIGN KEY (status) REFERENCES loan_proposal_status_catalog (slug) ON UPDATE CASCADE,
    UNIQUE KEY uq_loan_proposals_proposal_number (proposal_number),
    INDEX idx_loan_proposals_status (status),
    INDEX idx_loan_proposals_broker_status (broker_id, status),
    INDEX idx_loan_proposals_employee (employee_id),
    INDEX idx_loan_proposals_product (product_type),
    INDEX idx_loan_proposals_submitted (submitted_at),
    INDEX idx_loan_proposals_funded (funded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_proposal_status_history (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id INT UNSIGNED NOT NULL,
    old_status VARCHAR(50) NULL,
    new_status VARCHAR(50) NOT NULL,
    description TEXT NULL,
    payload LONGTEXT NULL,
    changed_by INT UNSIGNED NULL,
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) NULL,
    CONSTRAINT fk_history_proposal FOREIGN KEY (proposal_id) REFERENCES loan_proposals (id) ON DELETE CASCADE,
    CONSTRAINT fk_history_old_status FOREIGN KEY (old_status) REFERENCES loan_proposal_status_catalog (slug) ON UPDATE CASCADE,
    CONSTRAINT fk_history_new_status FOREIGN KEY (new_status) REFERENCES loan_proposal_status_catalog (slug) ON UPDATE CASCADE,
    CONSTRAINT fk_history_user FOREIGN KEY (changed_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_history_proposal (proposal_id, changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_proposal_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id INT UNSIGNED NOT NULL,
    document_type ENUM('document','evidence') NOT NULL DEFAULT 'document',
    label VARCHAR(120) NOT NULL,
    storage_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NULL,
    file_size INT UNSIGNED NULL,
    status ENUM('pending','uploaded','approved','rejected') NOT NULL DEFAULT 'pending',
    uploaded_by INT UNSIGNED NULL,
    uploaded_at DATETIME NULL,
    reviewed_at DATETIME NULL,
    expires_at DATETIME NULL,
    review_notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_documents_proposal FOREIGN KEY (proposal_id) REFERENCES loan_proposals (id) ON DELETE CASCADE,
    CONSTRAINT fk_documents_user FOREIGN KEY (uploaded_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_documents_proposal (proposal_id),
    INDEX idx_documents_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_proposal_bank_payloads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id INT UNSIGNED NOT NULL,
    direction ENUM('request','response','webhook') NOT NULL,
    endpoint VARCHAR(150) NOT NULL,
    payload LONGTEXT NOT NULL,
    status_code SMALLINT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_payloads_proposal FOREIGN KEY (proposal_id) REFERENCES loan_proposals (id) ON DELETE CASCADE,
    INDEX idx_payloads_proposal (proposal_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bem_proposals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    crm_proposal_id INT UNSIGNED NULL,
    bem_proposta_numero BIGINT NULL,
    bem_protocolo VARCHAR(120) NULL,
    tipo_operacao ENUM('NOVO','REFIN','PORTAB','PORTAB_REFIN') NOT NULL,
    cpf_cliente VARCHAR(20) NOT NULL,
    cpf_agente VARCHAR(20) NOT NULL,
    conveniada VARCHAR(50) NOT NULL,
    orgao VARCHAR(80) NOT NULL,
    plano VARCHAR(50) NOT NULL,
    prazo VARCHAR(20) NOT NULL,
    status_local ENUM(
        'RASCUNHO','SIMULADA','ENVIADA','EM_ANDAMENTO','PENDENTE_DOC','PENDENTE_ASSINATURA',
        'PENDENTE_ACEITE','APROVADA','REPROVADA','CANCELADA'
    ) NOT NULL DEFAULT 'RASCUNHO',
    situacao_codigo VARCHAR(50) NULL,
    situacao_descricao VARCHAR(150) NULL,
    situacao_detalhada TEXT NULL,
    permissoes_json JSON NULL,
    demandas_json JSON NULL,
    docs_pendentes_json JSON NULL,
    resumo_json JSON NULL,
    payload_submit_json JSON NULL,
    payload_response_json JSON NULL,
    last_sync_at DATETIME NULL,
    needs_attention TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_bem_proposal_crm FOREIGN KEY (crm_proposal_id) REFERENCES loan_proposals (id) ON DELETE SET NULL,
    INDEX idx_bem_proposal_crm (crm_proposal_id),
    INDEX idx_bem_proposal_numero (bem_proposta_numero),
    INDEX idx_bem_proposal_protocolo (bem_protocolo),
    INDEX idx_bem_proposal_cpf (cpf_cliente),
    INDEX idx_bem_proposal_status (status_local),
    INDEX idx_bem_proposal_sync (last_sync_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bem_sync_runs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    run_type ENUM('MANUAL','CRON','IMPORT_INCLUSAO','IMPORT_MOVIMENTACAO','IMPORT_ULTIMAS') NOT NULL,
    ref_date DATE NULL,
    pagina_atual INT UNSIGNED NOT NULL DEFAULT 0,
    finished TINYINT(1) NOT NULL DEFAULT 0,
    log TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_preapproved_banks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    contract_data_id INT UNSIGNED NOT NULL,
    benefit_number VARCHAR(30) NULL,
    bank_id INT UNSIGNED NOT NULL,
    status ENUM('aprovado','pre_aprovado') NOT NULL,
    change_amount DECIMAL(15,2) NULL,
    source VARCHAR(20) NOT NULL DEFAULT 'RVX',
    evaluated_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_preapproved_lead (lead_id),
    INDEX idx_preapproved_bank (bank_id),
    INDEX idx_preapproved_bank_lead (bank_id, lead_id),
    INDEX idx_preapproved_benefit (benefit_number),
    INDEX idx_preapproved_contract (contract_data_id),
    UNIQUE KEY idx_preapproved_unique (contract_data_id, bank_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_filter_metrics (
    lead_id INT UNSIGNED NOT NULL,
    ir_margin_available DECIMAL(15,2) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (lead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO lead_statuses (slug, label, sort_order, is_system)
VALUES 
    ('novo', 'Novo', 1, 1),
    ('em_contato', 'Em contato', 2, 1),
    ('proposta', 'Proposta', 3, 1),
    ('fechado', 'Fechado', 4, 1),
    ('perdido', 'Perdido', 5, 1);
