ALTER TABLE finanto_inss_cases
    ADD COLUMN port_refin_status VARCHAR(60) NOT NULL DEFAULT 'draft' AFTER stage,
    ADD COLUMN port_refin_last_error TEXT NULL AFTER port_refin_status,
    ADD COLUMN port_refin_product_payload MEDIUMTEXT NULL AFTER port_refin_last_error,
    ADD COLUMN port_refin_rules_payload MEDIUMTEXT NULL AFTER port_refin_product_payload,
    ADD COLUMN calculation_payload MEDIUMTEXT NULL AFTER calculated_items_updated_at,
    ADD COLUMN product_id CHAR(36) NULL AFTER product_name,
    ADD COLUMN refinancing_term INT NULL AFTER selected_loan_value,
    ADD COLUMN refinancing_rate DECIMAL(8,4) NULL AFTER refinancing_term,
    ADD COLUMN refinancing_installment_value DECIMAL(12,2) NULL AFTER refinancing_rate,
    ADD COLUMN refinancing_loan_value DECIMAL(12,2) NULL AFTER refinancing_installment_value;

CREATE INDEX idx_finanto_inss_port_status ON finanto_inss_cases (port_refin_status);

CREATE TABLE finanto_requests_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    case_id INT UNSIGNED NOT NULL,
    endpoint VARCHAR(255) NOT NULL,
    method VARCHAR(10) NOT NULL,
    request_json MEDIUMTEXT NULL,
    response_json MEDIUMTEXT NULL,
    http_status INT NULL,
    error_code VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_finanto_requests_case (case_id),
    INDEX idx_finanto_requests_endpoint (endpoint(120)),
    CONSTRAINT fk_finanto_requests_case FOREIGN KEY (case_id) REFERENCES finanto_inss_cases (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finanto_files (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    case_id INT UNSIGNED NOT NULL,
    type VARCHAR(40) NOT NULL,
    file_id VARCHAR(120) NOT NULL,
    label VARCHAR(120) NULL,
    source_path VARCHAR(255) NULL,
    external_url VARCHAR(255) NULL,
    status VARCHAR(40) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY idx_finanto_files_case_type (case_id, type),
    CONSTRAINT fk_finanto_files_case FOREIGN KEY (case_id) REFERENCES finanto_inss_cases (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;