CREATE TABLE crefaz_proposals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    proposal_code BIGINT UNSIGNED NULL,
    cpf VARCHAR(20) NOT NULL,
    mode ENUM('manual', 'integrado') NOT NULL DEFAULT 'integrado',
    status VARCHAR(60) NULL,
    status_description VARCHAR(150) NULL,
    renda_presumida DECIMAL(12,2) NULL,
    valor_limite DECIMAL(12,2) NULL,
    valor_limite_parcela DECIMAL(12,2) NULL,
    valor_limite_minimo_parcela DECIMAL(12,2) NULL,
    selected_offer JSON NULL,
    last_payload JSON NULL,
    last_synced_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_crefaz_proposal_code (proposal_code),
    KEY idx_crefaz_proposals_lead (lead_id),
    CONSTRAINT fk_crefaz_proposals_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crefaz_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    crefaz_proposal_id INT UNSIGNED NULL,
    document_id INT NOT NULL,
    document_name VARCHAR(150) NULL,
    is_required TINYINT(1) NOT NULL DEFAULT 0,
    document_type VARCHAR(80) NULL,
    storage_path VARCHAR(255) NULL,
    checksum VARCHAR(128) NULL,
    uploaded_at DATETIME NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_crefaz_documents_lead (lead_id),
    KEY idx_crefaz_documents_proposal (crefaz_proposal_id),
    CONSTRAINT fk_crefaz_documents_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_crefaz_documents_proposal FOREIGN KEY (crefaz_proposal_id) REFERENCES crefaz_proposals (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crefaz_sync_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NULL,
    crefaz_proposal_id INT UNSIGNED NULL,
    action VARCHAR(80) NOT NULL,
    endpoint VARCHAR(150) NULL,
    request_payload JSON NULL,
    response_payload JSON NULL,
    status_code SMALLINT UNSIGNED NULL,
    success TINYINT(1) NOT NULL DEFAULT 0,
    message VARCHAR(255) NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_crefaz_sync_logs_lead (lead_id),
    KEY idx_crefaz_sync_logs_proposal (crefaz_proposal_id),
    CONSTRAINT fk_crefaz_sync_logs_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE SET NULL,
    CONSTRAINT fk_crefaz_sync_logs_proposal FOREIGN KEY (crefaz_proposal_id) REFERENCES crefaz_proposals (id) ON DELETE SET NULL,
    CONSTRAINT fk_crefaz_sync_logs_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE crefaz_webhook_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NULL,
    crefaz_proposal_id INT UNSIGNED NULL,
    status VARCHAR(60) NULL,
    description VARCHAR(150) NULL,
    payload JSON NOT NULL,
    received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME NULL,
    processing_error VARCHAR(255) NULL,
    KEY idx_crefaz_webhook_lead (lead_id),
    KEY idx_crefaz_webhook_proposal (crefaz_proposal_id),
    CONSTRAINT fk_crefaz_webhook_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE SET NULL,
    CONSTRAINT fk_crefaz_webhook_proposal FOREIGN KEY (crefaz_proposal_id) REFERENCES crefaz_proposals (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE leads
    ADD COLUMN crefaz_enabled TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN crefaz_mode ENUM('manual', 'integrado') NULL,
    ADD COLUMN crefaz_proposta_id BIGINT UNSIGNED NULL,
    ADD COLUMN crefaz_status VARCHAR(60) NULL,
    ADD COLUMN crefaz_status_description VARCHAR(150) NULL,
    ADD COLUMN crefaz_renda_presumida DECIMAL(12,2) NULL,
    ADD COLUMN crefaz_webhook_url VARCHAR(255) NULL,
    ADD COLUMN crefaz_last_synced_at DATETIME NULL,
    ADD COLUMN crefaz_last_status_at DATETIME NULL;