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_father 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_rule_code VARCHAR(80) 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;