-- Saque Facil CRM database schema

DROP TABLE IF EXISTS facebook_pages;
DROP TABLE IF EXISTS facebook_integrations;
DROP TABLE IF EXISTS daycoval_catalog_entries;
DROP TABLE IF EXISTS loan_proposal_bank_payloads;
DROP TABLE IF EXISTS loan_proposal_documents;
DROP TABLE IF EXISTS loan_proposal_status_history;
DROP TABLE IF EXISTS loan_proposals;
DROP TABLE IF EXISTS loan_proposal_status_catalog;
DROP TABLE IF EXISTS simplix_fgts_cases;
DROP TABLE IF EXISTS finanto_inss_cases;
DROP TABLE IF EXISTS bem_sync_runs;
DROP TABLE IF EXISTS bem_proposals;
DROP TABLE IF EXISTS integration_bem_tokens;
DROP TABLE IF EXISTS lgpd_request_logs;
DROP TABLE IF EXISTS lgpd_requests;
DROP TABLE IF EXISTS lead_histories;
DROP TABLE IF EXISTS lead_reminders;
DROP TABLE IF EXISTS lead_documents;
DROP TABLE IF EXISTS leads;
DROP TABLE IF EXISTS api_integrations;
DROP TABLE IF EXISTS lead_statuses;
DROP TABLE IF EXISTS lead_capture_links;
DROP TABLE IF EXISTS lead_disclosure_channels;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    broker_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','broker','employee') NOT NULL DEFAULT 'employee',
    phone VARCHAR(50) NULL,
    birth_date DATE NULL,
    remember_token VARCHAR(120) 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,
    CONSTRAINT fk_users_broker FOREIGN KEY (broker_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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 VARCHAR(100) NULL,
    funnel_id INT UNSIGNED NULL,
    funnel_session_id INT UNSIGNED NULL,
    government_agency VARCHAR(160) NULL,
    government_registration_primary VARCHAR(60) NULL,
    government_registration_secondary VARCHAR(60) 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,
    CONSTRAINT fk_leads_funnel FOREIGN KEY (funnel_id) REFERENCES crefaz_funnels (id) ON DELETE SET NULL,
    CONSTRAINT fk_leads_funnel_session FOREIGN KEY (funnel_session_id) REFERENCES crefaz_funnel_sessions (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_funnel_id (funnel_id),
    INDEX idx_leads_funnel_session (funnel_session_id),
    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_hiscon_snapshots (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    hash CHAR(64) NOT NULL,
    document_datetime DATETIME NULL,
    verification_code VARCHAR(120) NULL,
    payload JSON NOT NULL,
    status ENUM('ok', 'parser_failed') NOT NULL DEFAULT 'ok',
    errors JSON NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_hiscon_snapshot_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_hiscon_snapshot_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    UNIQUE KEY idx_hiscon_snapshots_hash (hash),
    INDEX idx_hiscon_snapshots_lead (lead_id),
    INDEX idx_hiscon_snapshots_status (status)
) 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 lead_disclosure_channels (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(50) NOT NULL UNIQUE,
    label VARCHAR(120) NOT NULL,
    sort_order INT 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 lead_capture_links (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    label VARCHAR(150) NULL,
    business_type VARCHAR(100) NULL,
    disclosure_channel_id INT UNSIGNED NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NULL,
    views_count INT UNSIGNED NOT NULL DEFAULT 0,
    submissions_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_submitted_at DATETIME NULL,
    CONSTRAINT fk_capture_links_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    INDEX idx_capture_links_user (user_id),
    INDEX idx_capture_links_active (is_active),
    INDEX idx_capture_links_channel (disclosure_channel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crefaz_funnels (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    token VARCHAR(40) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    auto_contract_url VARCHAR(500) 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,
    INDEX idx_crefaz_funnels_user (user_id),
    CONSTRAINT fk_crefaz_funnels_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crefaz_funnel_assets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    funnel_id INT UNSIGNED NOT NULL,
    asset_type VARCHAR(50) NOT NULL,
    label VARCHAR(150) NULL,
    file_path VARCHAR(255) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_crefaz_funnel_assets_funnel (funnel_id),
    CONSTRAINT fk_crefaz_funnel_assets_funnel FOREIGN KEY (funnel_id) REFERENCES crefaz_funnels (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crefaz_funnel_copies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    funnel_id INT UNSIGNED NOT NULL,
    copy_type VARCHAR(30) NOT NULL,
    content TEXT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_crefaz_funnel_copies_funnel (funnel_id),
    INDEX idx_crefaz_funnel_copies_type (copy_type),
    CONSTRAINT fk_crefaz_funnel_copies_funnel FOREIGN KEY (funnel_id) REFERENCES crefaz_funnels (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crefaz_funnel_ctas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    funnel_id INT UNSIGNED NOT NULL,
    placement VARCHAR(50) NOT NULL,
    label VARCHAR(120) NOT NULL,
    url VARCHAR(500) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_crefaz_funnel_ctas_funnel (funnel_id),
    INDEX idx_crefaz_funnel_ctas_place (placement),
    CONSTRAINT fk_crefaz_funnel_ctas_funnel FOREIGN KEY (funnel_id) REFERENCES crefaz_funnels (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crefaz_funnel_sessions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    funnel_id INT UNSIGNED NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    ip VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    utm_source VARCHAR(150) NULL,
    utm_medium VARCHAR(150) NULL,
    utm_campaign VARCHAR(150) NULL,
    utm_term VARCHAR(150) NULL,
    utm_content VARCHAR(150) NULL,
    referrer VARCHAR(255) NULL,
    city VARCHAR(120) NULL,
    state VARCHAR(2) NULL,
    last_step VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_at DATETIME NULL,
    INDEX idx_crefaz_funnel_sessions_funnel (funnel_id),
    CONSTRAINT fk_crefaz_funnel_sessions_funnel FOREIGN KEY (funnel_id) REFERENCES crefaz_funnels (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crefaz_funnel_events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    funnel_id INT UNSIGNED NOT NULL,
    session_id INT UNSIGNED NULL,
    event_type VARCHAR(50) NOT NULL,
    item_type VARCHAR(50) NULL,
    item_id INT UNSIGNED NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_crefaz_funnel_events_funnel (funnel_id),
    INDEX idx_crefaz_funnel_events_type (event_type),
    INDEX idx_crefaz_funnel_events_created (created_at),
    CONSTRAINT fk_crefaz_funnel_events_funnel FOREIGN KEY (funnel_id) REFERENCES crefaz_funnels (id) ON DELETE CASCADE,
    CONSTRAINT fk_crefaz_funnel_events_session FOREIGN KEY (session_id) REFERENCES crefaz_funnel_sessions (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crefaz_preapproved_cities (
    state VARCHAR(2) NOT NULL,
    city VARCHAR(120) NOT NULL,
    utility_company VARCHAR(150) NULL,
    PRIMARY KEY (state, city)
) 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 api_integrations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    type VARCHAR(100) NULL,
    base_url VARCHAR(255) NULL,
    api_key TEXT NULL,
    username VARCHAR(150) NULL,
    password VARCHAR(150) NULL,
    extra_config TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_api_integrations_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE daycoval_catalog_entries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    integration_id INT UNSIGNED NOT NULL,
    flow ENUM('refin','margem') NOT NULL,
    catalog_type VARCHAR(60) NOT NULL,
    code VARCHAR(120) NOT NULL,
    name VARCHAR(200) NULL,
    name_normalized VARCHAR(220) NULL,
    payload JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_daycoval_catalog_integration FOREIGN KEY (integration_id) REFERENCES api_integrations (id) ON DELETE CASCADE,
    UNIQUE KEY uniq_daycoval_catalog (integration_id, flow, catalog_type, code),
    INDEX idx_daycoval_catalog_name (integration_id, flow, catalog_type, name_normalized)
) 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 facebook_integrations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    facebook_user_id VARCHAR(50) NOT NULL,
    facebook_name VARCHAR(150) NOT NULL,
    profile_picture_url VARCHAR(255) NULL,
    access_token TEXT NOT NULL,
    token_expires_at DATETIME NULL,
    granted_scopes TEXT NULL,
    declined_scopes TEXT NULL,
    status ENUM('pending','active','revoked','error') NOT NULL DEFAULT 'pending',
    last_synced_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_facebook_integration_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    UNIQUE KEY idx_facebook_integration_unique (user_id, facebook_user_id),
    INDEX idx_facebook_integration_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE facebook_pages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    integration_id INT UNSIGNED NOT NULL,
    page_id VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(150) NULL,
    profile_picture_url VARCHAR(255) NULL,
    tasks TEXT NULL,
    access_token TEXT NULL,
    status ENUM('inactive','active') NOT NULL DEFAULT 'inactive',
    subscribed_at DATETIME NULL,
    unsubscribed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_facebook_page_integration FOREIGN KEY (integration_id) REFERENCES facebook_integrations (id) ON DELETE CASCADE,
    UNIQUE KEY idx_facebook_page_unique (integration_id, page_id),
    INDEX idx_facebook_page_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE simplix_fgts_cases (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL UNIQUE,
    integration_id INT UNSIGNED NOT NULL,
    loan_proposal_id INT UNSIGNED NULL,
    cpf VARCHAR(20) NOT NULL,
    tracking_token VARCHAR(64) NOT NULL,
    public_step VARCHAR(50) NULL,
    public_status_code VARCHAR(100) NULL,
    public_status_message TEXT NULL,
    last_public_action_at DATETIME NULL,
    contact_name VARCHAR(150) NULL,
    contact_phone VARCHAR(30) NULL,
    contact_email VARCHAR(150) NULL,
    followup_required TINYINT(1) NOT NULL DEFAULT 1,
    birth_date DATE NOT NULL,
    marital_status VARCHAR(20) NOT NULL,
    nationality VARCHAR(100) NOT NULL,
    occupation VARCHAR(150) NULL,
    whatsapp TINYINT(1) NOT NULL DEFAULT 1,
    rg VARCHAR(30) NULL,
    address_zip VARCHAR(15) NOT NULL,
    address_state VARCHAR(2) NOT NULL,
    address_city VARCHAR(100) NOT NULL,
    address_neighborhood VARCHAR(100) NOT NULL,
    address_street VARCHAR(150) NOT NULL,
    address_number VARCHAR(20) NOT NULL,
    address_complement VARCHAR(100) NULL,
    bank_code VARCHAR(10) NOT NULL,
    bank_account_type ENUM('ContaCorrente','ContaPoupanca') NOT NULL,
    bank_operation_type ENUM('Transferencia','Pix') NOT NULL DEFAULT 'Transferencia',
    bank_account_number VARCHAR(20) NOT NULL,
    bank_account_digit VARCHAR(5) NOT NULL,
    bank_agency_number VARCHAR(10) NOT NULL,
    bank_agency_digit VARCHAR(5) NULL,
    digitador_login VARCHAR(120) NOT NULL,
    callback_balance_url VARCHAR(255) NULL,
    callback_balance_method VARCHAR(10) NULL,
    callback_proposal_url VARCHAR(255) NULL,
    callback_proposal_method VARCHAR(10) NULL,
    balance_transaction_id CHAR(36) NULL,
    balance_status VARCHAR(100) NULL,
    balance_status_description VARCHAR(255) NULL,
    balance_last_checked_at DATETIME NULL,
    balance_raw_response MEDIUMTEXT NULL,
    simulation_last_run_at DATETIME NULL,
    simulation_payload MEDIUMTEXT NULL,
    selected_simulation_id CHAR(36) NULL,
    proposal_id CHAR(36) NULL,
    proposal_number VARCHAR(100) NULL,
    proposal_link TEXT NULL,
    proposal_status VARCHAR(100) NULL,
    proposal_status_description VARCHAR(255) NULL,
    proposal_last_checked_at DATETIME NULL,
    proposal_raw_response MEDIUMTEXT NULL,
    webhook_last_payload MEDIUMTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_simplix_case_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_simplix_case_integration FOREIGN KEY (integration_id) REFERENCES api_integrations (id) ON DELETE RESTRICT,
    CONSTRAINT fk_simplix_case_proposal FOREIGN KEY (loan_proposal_id) REFERENCES loan_proposals (id) ON DELETE SET NULL,
    UNIQUE KEY idx_simplix_case_tracking (tracking_token),
    INDEX idx_simplix_case_public_step (public_step),
    INDEX idx_simplix_case_transaction (balance_transaction_id),
    INDEX idx_simplix_case_proposal_link (loan_proposal_id),
    INDEX idx_simplix_case_proposal (proposal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finanto_inss_cases (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL UNIQUE,
    integration_id INT UNSIGNED NOT NULL,
    loan_proposal_id INT UNSIGNED NULL,
    tracking_token CHAR(32) NOT NULL,
    benefit_number VARCHAR(30) NOT NULL,
    benefit_state CHAR(2) NOT NULL,
    benefit_start_date DATE NULL,
    benefit_payment_method TINYINT(1) NOT NULL DEFAULT 1,
    benefit_type INT NULL,
    borrower_name VARCHAR(150) NOT NULL,
    borrower_cpf VARCHAR(20) NOT NULL,
    borrower_birth_date DATE NULL,
    borrower_mother VARCHAR(150) NULL,
    borrower_marital_status VARCHAR(30) NULL,
    borrower_sex VARCHAR(10) NULL,
    borrower_income DECIMAL(12,2) NULL,
    borrower_phone VARCHAR(30) NULL,
    borrower_email VARCHAR(150) NULL,
    address_zip VARCHAR(15) NULL,
    address_state CHAR(2) NULL,
    address_city VARCHAR(100) NULL,
    address_district VARCHAR(100) NULL,
    address_street VARCHAR(150) NULL,
    address_number VARCHAR(20) NULL,
    address_complement VARCHAR(100) NULL,
    document_type_code VARCHAR(20) NULL,
    document_type_name VARCHAR(100) NULL,
    document_number VARCHAR(30) NULL,
    document_issuing_date DATE NULL,
    document_issuing_entity VARCHAR(50) NULL,
    document_issuing_state CHAR(2) NULL,
    bank_code VARCHAR(10) NULL,
    bank_branch VARCHAR(10) NULL,
    bank_number VARCHAR(20) NULL,
    bank_digit VARCHAR(5) NULL,
    selected_rule_id CHAR(36) NULL,
    selected_term INT NULL,
    selected_rate DECIMAL(8,4) NULL,
    selected_installment_value DECIMAL(12,2) NULL,
    selected_loan_value DECIMAL(12,2) NULL,
    origin_lender_code INT NULL,
    origin_contract_number VARCHAR(50) NULL,
    origin_term INT NULL,
    origin_installments_remaining INT NULL,
    origin_installment_value DECIMAL(12,2) NULL,
    origin_due_balance_value DECIMAL(12,2) NULL,
    has_insurance TINYINT(1) NOT NULL DEFAULT 0,
    reference_code VARCHAR(100) NULL,
    simulation_id CHAR(36) NULL,
    simulation_status VARCHAR(50) NULL,
    simulation_payload MEDIUMTEXT NULL,
    simulation_last_run_at DATETIME NULL,
    loan_id CHAR(36) NULL,
    loan_code INT NULL,
    loan_status VARCHAR(100) NULL,
    loan_payload MEDIUMTEXT NULL,
    loan_last_checked_at DATETIME NULL,
    files_doc_front_id CHAR(36) NULL,
    files_doc_back_id CHAR(36) NULL,
    files_payload MEDIUMTEXT NULL,
    step_code INT NULL,
    step_name VARCHAR(100) NULL,
    note TEXT NULL,
    webhook_last_payload MEDIUMTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_finanto_case_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_finanto_case_integration FOREIGN KEY (integration_id) REFERENCES api_integrations (id) ON DELETE RESTRICT,
    CONSTRAINT fk_finanto_case_proposal FOREIGN KEY (loan_proposal_id) REFERENCES loan_proposals (id) ON DELETE SET NULL,
    UNIQUE KEY idx_finanto_tracking (tracking_token),
    INDEX idx_finanto_simulation (simulation_id),
    INDEX idx_finanto_loan (loan_id),
    INDEX idx_finanto_case_proposal (loan_proposal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE integration_bem_tokens (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    jwt_token TEXT NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at_estimated DATETIME NULL,
    last_header_token TEXT NULL,
    environment VARCHAR(20) NOT NULL DEFAULT 'prod',
    UNIQUE KEY idx_bem_tokens_env (environment)
) 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 users (name, email, password_hash, role, is_active)
VALUES ('Administrador Saque Facil', 'admin@saquefacil.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9o2uF.V/3A8X9FCh0eDOm', 'admin', 1);

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);