SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS crefaz_public_cases (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL UNIQUE,
    token CHAR(20) NOT NULL UNIQUE,
    proposal_id BIGINT UNSIGNED NULL,
    step VARCHAR(40) NOT NULL DEFAULT 'started',
    consent_given_at DATETIME NULL,
    consent_ip VARCHAR(45) NULL,
    offers_payload MEDIUMTEXT NULL,
    operation_payload MEDIUMTEXT NULL,
    simulation_payload MEDIUMTEXT NULL,
    selected_offer_payload MEDIUMTEXT NULL,
    last_payload MEDIUMTEXT NULL,
    last_error TEXT NULL,
    last_public_action_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_crefaz_public_cases_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    INDEX idx_crefaz_public_cases_proposal (proposal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;