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,
    cpf VARCHAR(20) NOT NULL,
    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,
    INDEX idx_simplix_case_transaction (balance_transaction_id),
    INDEX idx_simplix_case_proposal (proposal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
