SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS marketing_gamma_generations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    source_type ENUM('prompt', 'template') NOT NULL DEFAULT 'prompt',
    title VARCHAR(160) NULL,
    category VARCHAR(80) NOT NULL DEFAULT '',
    channel VARCHAR(80) NOT NULL DEFAULT '',
    format VARCHAR(30) NOT NULL DEFAULT 'presentation',
    text_mode VARCHAR(30) NOT NULL DEFAULT 'generate',
    input_text TEXT NOT NULL,
    additional_instructions TEXT NULL,
    template_gamma_id VARCHAR(120) NULL,
    theme_id VARCHAR(120) NULL,
    folder_ids JSON NULL,
    output_language VARCHAR(32) NULL,
    export_as VARCHAR(32) NULL,
    gamma_generation_id VARCHAR(120) NULL,
    internal_status VARCHAR(40) NOT NULL DEFAULT 'queued',
    gamma_status VARCHAR(40) NULL,
    gamma_url VARCHAR(500) NULL,
    edit_url VARCHAR(500) NULL,
    embed_url VARCHAR(500) NULL,
    download_url VARCHAR(500) NULL,
    pdf_url VARCHAR(500) NULL,
    pptx_url VARCHAR(500) NULL,
    request_payload JSON NULL,
    response_payload JSON NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_marketing_gamma_user (user_id),
    INDEX idx_marketing_gamma_internal_status (internal_status),
    INDEX idx_marketing_gamma_gamma_status (gamma_status),
    INDEX idx_marketing_gamma_generation_id (gamma_generation_id),
    CONSTRAINT fk_marketing_gamma_user
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gamma_api_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    correlation_id VARCHAR(64) NOT NULL,
    user_id INT UNSIGNED NULL,
    generation_record_id INT UNSIGNED NULL,
    endpoint VARCHAR(180) NOT NULL,
    http_method VARCHAR(10) NOT NULL,
    request_payload LONGTEXT NULL,
    response_payload LONGTEXT NULL,
    response_json JSON NULL,
    http_status INT NULL,
    error_message VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_gamma_api_logs_correlation (correlation_id),
    INDEX idx_gamma_api_logs_created_at (created_at),
    INDEX idx_gamma_api_logs_endpoint (endpoint),
    INDEX idx_gamma_api_logs_generation (generation_record_id),
    CONSTRAINT fk_gamma_api_logs_user
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_gamma_api_logs_generation
        FOREIGN KEY (generation_record_id) REFERENCES marketing_gamma_generations (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;