CREATE TABLE finanto_inss_offers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    case_id INT UNSIGNED NOT NULL,
    loan_proposal_id INT UNSIGNED NULL,
    rule_id VARCHAR(64) NOT NULL,
    term INT NOT NULL,
    installment_value DECIMAL(12,2) NOT NULL,
    loan_value DECIMAL(12,2) NOT NULL,
    net_value DECIMAL(12,2) NULL,
    interest_rate DECIMAL(8,4) NULL,
    cet DECIMAL(8,4) NULL,
    sequence_order INT NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    status_updated_at DATETIME NULL,
    selection_payload MEDIUMTEXT NULL,
    simulation_id VARCHAR(64) NULL,
    simulation_payload MEDIUMTEXT NULL,
    simulation_requested_at DATETIME NULL,
    loan_id VARCHAR(64) NULL,
    loan_payload MEDIUMTEXT NULL,
    loan_requested_at DATETIME NULL,
    signature_link VARCHAR(500) NULL,
    signature_status VARCHAR(50) NULL,
    signature_payload MEDIUMTEXT NULL,
    signature_status_synced_at DATETIME NULL,
    accepted_at DATETIME NULL,
    accepted_ip VARCHAR(45) NULL,
    rejected_at DATETIME NULL,
    submitted_at DATETIME NULL,
    completed_at DATETIME NULL,
    failed_at DATETIME NULL,
    failure_message TEXT NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_finanto_offer_case FOREIGN KEY (case_id) REFERENCES finanto_inss_cases (id) ON DELETE CASCADE,
    CONSTRAINT fk_finanto_offer_proposal FOREIGN KEY (loan_proposal_id) REFERENCES loan_proposals (id) ON DELETE SET NULL,
    INDEX idx_finanto_offer_case (case_id),
    INDEX idx_finanto_offer_status (status),
    INDEX idx_finanto_offer_simulation (simulation_id),
    INDEX idx_finanto_offer_loan (loan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;