CREATE TABLE c6_bank_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,
    borrower_name VARCHAR(150) NULL,
    borrower_cpf VARCHAR(20) NULL,
    borrower_email VARCHAR(150) NULL,
    borrower_phone VARCHAR(30) NULL,
    borrower_mother VARCHAR(150) NULL,
    borrower_birth_date DATE NULL,
    borrower_gender VARCHAR(15) NULL,
    borrower_marital_status VARCHAR(30) NULL,
    borrower_income DECIMAL(12,2) NULL,
    borrower_spouse_name VARCHAR(150) NULL,
    borrower_politically_exposed VARCHAR(10) NULL,
    document_type VARCHAR(20) NULL,
    document_number VARCHAR(40) NULL,
    document_issuing_date DATE NULL,
    document_issuing_entity VARCHAR(40) NULL,
    document_issuing_state CHAR(2) 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,
    bank_code VARCHAR(10) NULL,
    bank_agency VARCHAR(10) NULL,
    bank_agency_digit VARCHAR(5) NULL,
    bank_account VARCHAR(20) NULL,
    bank_account_digit VARCHAR(5) NULL,
    bank_account_type VARCHAR(40) NULL,
    benefit_number VARCHAR(30) NULL,
    benefit_type VARCHAR(30) NULL,
    benefit_state CHAR(2) NULL,
    receive_card_benefit VARCHAR(10) NULL,
    client_founder_enrollment VARCHAR(30) NULL,
    promoter_code VARCHAR(30) NULL,
    covenant_group VARCHAR(30) NULL,
    public_agency VARCHAR(20) NULL,
    typist_code VARCHAR(30) NULL,
    tax_identifier_of_certified_agent VARCHAR(20) NULL,
    operation_type VARCHAR(20) NULL,
    product_type_code VARCHAR(20) NULL,
    simulation_type VARCHAR(30) NULL,
    formalization_subtype VARCHAR(30) NULL,
    installment_quantity SMALLINT NULL,
    requested_amount DECIMAL(12,2) NULL,
    installment_amount DECIMAL(12,2) NULL,
    expenses_payload LONGTEXT NULL,
    simulation_payload LONGTEXT NULL,
    simulation_last_run_at DATETIME NULL,
    selected_credit_payload LONGTEXT NULL,
    include_payload LONGTEXT NULL,
    proposal_number VARCHAR(100) NULL,
    proposal_payload LONGTEXT NULL,
    consult_payload LONGTEXT NULL,
    formalization_payload LONGTEXT NULL,
    formalization_url VARCHAR(255) NULL,
    formalization_status VARCHAR(60) NULL,
    movement_payload LONGTEXT NULL,
    upag_payload LONGTEXT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_c6_inss_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_c6_inss_integration FOREIGN KEY (integration_id) REFERENCES api_integrations (id) ON DELETE RESTRICT,
    CONSTRAINT fk_c6_inss_proposal FOREIGN KEY (loan_proposal_id) REFERENCES loan_proposals (id) ON DELETE SET NULL,
    INDEX idx_c6_inss_cpf (borrower_cpf),
    INDEX idx_c6_inss_proposal (proposal_number),
    INDEX idx_c6_inss_integration (integration_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
