CREATE TABLE finanto_inss_pipeline_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    case_id INT UNSIGNED NOT NULL,
    proposal_id VARCHAR(64) NULL,
    broker_id INT UNSIGNED NULL,
    stage_key VARCHAR(40) NOT NULL,
    finanto_status VARCHAR(80) NOT NULL,
    amount DECIMAL(12,2) NULL,
    finanto_updated_at DATETIME NULL,
    payload_json JSON NULL,
    synced_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_finanto_pipeline_case FOREIGN KEY (case_id) REFERENCES finanto_inss_cases (id) ON DELETE CASCADE,
    CONSTRAINT fk_finanto_pipeline_broker FOREIGN KEY (broker_id) REFERENCES users (id) ON DELETE SET NULL,
    UNIQUE KEY idx_finanto_pipeline_case (case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_finanto_pipeline_stage ON finanto_inss_pipeline_snapshots (stage_key, broker_id);
CREATE INDEX idx_finanto_pipeline_status ON finanto_inss_pipeline_snapshots (finanto_status);
CREATE INDEX idx_finanto_pipeline_synced ON finanto_inss_pipeline_snapshots (synced_at);
CREATE INDEX idx_finanto_pipeline_case_synced ON finanto_inss_pipeline_snapshots (case_id, synced_at);