-- Migration: AI Image Generation and Caching System
-- Created: 2025-01-13
-- Description: Tables for managing AI-generated images with caching and prompt tracking

-- ============================================================
-- Generated Images Cache
-- ============================================================
-- Stores all AI-generated images with their prompts for reuse
CREATE TABLE IF NOT EXISTS generated_images (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    image_url VARCHAR(500) NOT NULL,
    prompt TEXT NOT NULL,
    style_preset VARCHAR(100) DEFAULT 'cozy_cartoon',
    event_type VARCHAR(100),
    event_category VARCHAR(100),
    provider VARCHAR(50) NOT NULL, -- 'flux', 'dalle3', 'manual'
    model_version VARCHAR(50),
    generation_cost DECIMAL(10,4) DEFAULT 0.04,
    image_width INT DEFAULT 1024,
    image_height INT DEFAULT 1024,
    is_active BOOLEAN DEFAULT TRUE,
    quality_rating INT, -- 1-5 rating if manually reviewed
    tags JSON, -- ['school', 'building', 'exterior'] for searchability
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_event_type (event_type),
    INDEX idx_category (event_category),
    INDEX idx_provider (provider),
    INDEX idx_active (is_active),
    FULLTEXT idx_prompt (prompt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- Image Generation Queue
-- ============================================================
-- Tracks pending image generation requests
CREATE TABLE IF NOT EXISTS image_generation_queue (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    prompt TEXT NOT NULL,
    style_preset VARCHAR(100) DEFAULT 'cozy_cartoon',
    event_type VARCHAR(100),
    event_category VARCHAR(100),
    priority INT DEFAULT 5, -- 1-10, higher = more urgent
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    provider VARCHAR(50), -- 'flux' or 'dalle3'
    error_message TEXT,
    generated_image_id BIGINT,
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 3,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    completed_at DATETIME,
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_event_type (event_type),
    FOREIGN KEY (generated_image_id) REFERENCES generated_images(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- Event Image Mappings
-- ============================================================
-- Maps event functions to their associated images
CREATE TABLE IF NOT EXISTS event_images (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    event_function_name VARCHAR(200) NOT NULL,
    event_type VARCHAR(100),
    event_category VARCHAR(100),
    generated_image_id BIGINT NOT NULL,
    is_primary BOOLEAN DEFAULT TRUE, -- Primary image for this event
    display_order INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_event_function (event_function_name),
    INDEX idx_event_type (event_type),
    FOREIGN KEY (generated_image_id) REFERENCES generated_images(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- Image Generation Statistics
-- ============================================================
CREATE TABLE IF NOT EXISTS image_generation_stats (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    date DATE NOT NULL,
    provider VARCHAR(50) NOT NULL,
    images_generated INT DEFAULT 0,
    total_cost DECIMAL(10,2) DEFAULT 0.00,
    avg_generation_time_ms INT,
    success_rate DECIMAL(5,2),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_date_provider (date, provider),
    INDEX idx_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- Initial Data: Style Presets
-- ============================================================
-- Add some sample images or manual Midjourney images
-- Example:
-- INSERT INTO generated_images (image_url, prompt, style_preset, event_type, provider, model_version)
-- VALUES
-- ('https://cdn.midjourney.com/...', 'large contemporary architecture high school cute cartoon style --no words', 'cozy_cartoon', 'school', 'manual', 'midjourney_v6');
