/**
 * Image Generation Database Operations
 *
 * Handles caching, storage, and retrieval of generated images.
 * Ported from Python ws/image_generation.py database operations.
 */

import { getPool } from '../../database/pool.js';
import { config } from '../../config.js';
import type { ImageProvider } from './image_generator.js';
import type { RowDataPacket, ResultSetHeader } from 'mysql2';

// ============================================================
// Types
// ============================================================

export interface GeneratedImage {
  id: number;
  image_url: string;
  prompt: string;
  style_preset: string;
  event_type: string | null;
  event_category: string | null;
  provider: ImageProvider;
  model_version: string | null;
  generation_cost: number;
  image_width: number;
  image_height: number;
  is_active: boolean;
  quality_rating: number | null;
  tags: string[] | null;
  created_at: Date;
  updated_at: Date;
}

export interface QueueItem {
  id: number;
  prompt: string;
  style_preset: string;
  event_type: string | null;
  event_category: string | null;
  priority: number;
  status: 'pending' | 'processing' | 'completed' | 'failed';
  provider: ImageProvider;
  error_message: string | null;
  generated_image_id: number | null;
  attempts: number;
  max_attempts: number;
  created_at: Date;
  updated_at: Date;
  completed_at: Date | null;
}

export interface SaveImageOptions {
  imageUrl: string;
  prompt: string;
  provider: ImageProvider;
  eventType?: string;
  eventCategory?: string;
  stylePreset?: string;
  width?: number;
  height?: number;
  cost?: number;
  tags?: string[];
}

// ============================================================
// Database Table Creation
// ============================================================

/**
 * Create image generation tables if they don't exist
 */
export async function createImageTables(): Promise<void> {
  const pool = getPool();

  // Generated Images Cache
  await pool.execute(`
    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,
      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,
      tags JSON,
      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)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);

  // Image Generation Queue
  await pool.execute(`
    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,
      status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
      provider VARCHAR(50),
      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)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);

  // Event Image Mappings
  await pool.execute(`
    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,
      display_order INT DEFAULT 1,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_event_function (event_function_name),
      INDEX idx_event_type (event_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);

  // Image Generation Statistics
  await pool.execute(`
    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
  `);

  console.log('Image generation tables created/verified');
}

// ============================================================
// Save Operations
// ============================================================

/**
 * Save a generated image to the database
 */
export async function saveGeneratedImage(options: SaveImageOptions): Promise<number | null> {
  const {
    imageUrl,
    prompt,
    provider,
    eventType = null,
    eventCategory = null,
    stylePreset = 'cozy_cartoon',
    width = 1024,
    height = 1024,
    cost = config.IMAGE_GENERATION_DEFAULT_COST,
    tags = null,
  } = options;

  const pool = getPool();

  try {
    const tagsJson = tags ? JSON.stringify(tags) : null;

    const [result] = await pool.execute<ResultSetHeader>(
      `INSERT INTO generated_images
       (image_url, prompt, style_preset, event_type, event_category,
        provider, generation_cost, image_width, image_height, tags)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [imageUrl, prompt, stylePreset, eventType, eventCategory,
       provider, cost, width, height, tagsJson]
    );

    const imageId = result.insertId;
    console.log(`Saved generated image ${imageId}: ${imageUrl}`);
    return imageId;
  } catch (e) {
    console.error(`Failed to save generated image: ${e}`);
    return null;
  }
}

// ============================================================
// Retrieval Operations
// ============================================================

/**
 * Get a cached image from database
 */
export async function getCachedImage(options: {
  eventType?: string;
  eventCategory?: string;
  promptKeywords?: string;
}): Promise<GeneratedImage | null> {
  const { eventType, eventCategory, promptKeywords } = options;
  const pool = getPool();

  try {
    let query = 'SELECT * FROM generated_images WHERE is_active = TRUE';
    const params: (string | number)[] = [];

    if (eventType) {
      query += ' AND event_type = ?';
      params.push(eventType);
    }

    if (eventCategory) {
      query += ' AND event_category = ?';
      params.push(eventCategory);
    }

    if (promptKeywords) {
      query += ' AND prompt LIKE ?';
      params.push(`%${promptKeywords}%`);
    }

    query += ' ORDER BY created_at DESC LIMIT 1';

    const [rows] = await pool.execute<RowDataPacket[]>(query, params);

    if (rows.length > 0) {
      const row = rows[0];
      console.log(`Found cached image: ${row.id}`);
      return {
        ...row,
        tags: row.tags ? JSON.parse(row.tags) : null,
      } as GeneratedImage;
    }

    return null;
  } catch (e) {
    console.error(`Failed to retrieve cached image: ${e}`);
    return null;
  }
}

/**
 * Get image URL for an event (synchronous-style for use in event functions)
 */
export async function getEventImageUrl(
  eventType: string,
  eventCategory?: string
): Promise<string | null> {
  const pool = getPool();

  try {
    const [rows] = await pool.execute<RowDataPacket[]>(
      `SELECT gi.image_url
       FROM event_images ei
       JOIN generated_images gi ON ei.generated_image_id = gi.id
       WHERE ei.event_type = ? AND gi.is_active = TRUE
       ORDER BY ei.is_primary DESC, ei.display_order ASC
       LIMIT 1`,
      [eventType]
    );

    if (rows.length > 0) {
      return rows[0].image_url;
    }

    // Fallback: try direct lookup in generated_images
    const cached = await getCachedImage({ eventType, eventCategory });
    return cached?.image_url ?? null;
  } catch (e) {
    console.error(`Failed to get event image: ${e}`);
    return null;
  }
}

/**
 * Get all images for a category
 */
export async function getImagesByCategory(category: string): Promise<GeneratedImage[]> {
  const pool = getPool();

  try {
    const [rows] = await pool.execute<RowDataPacket[]>(
      `SELECT * FROM generated_images
       WHERE event_category = ? AND is_active = TRUE
       ORDER BY created_at DESC`,
      [category]
    );

    return rows.map(row => ({
      ...row,
      tags: row.tags ? JSON.parse(row.tags) : null,
    })) as GeneratedImage[];
  } catch (e) {
    console.error(`Failed to get images by category: ${e}`);
    return [];
  }
}

/**
 * Get image statistics
 */
export async function getImageStats(): Promise<{
  totalImages: number;
  totalCost: number;
  byProvider: Record<string, number>;
  byCategory: Record<string, number>;
}> {
  const pool = getPool();

  try {
    // Total count and cost
    const [totals] = await pool.execute<RowDataPacket[]>(
      `SELECT COUNT(*) as count, SUM(generation_cost) as cost
       FROM generated_images WHERE is_active = TRUE`
    );

    // By provider
    const [providers] = await pool.execute<RowDataPacket[]>(
      `SELECT provider, COUNT(*) as count
       FROM generated_images WHERE is_active = TRUE
       GROUP BY provider`
    );

    // By category
    const [categories] = await pool.execute<RowDataPacket[]>(
      `SELECT event_category, COUNT(*) as count
       FROM generated_images WHERE is_active = TRUE AND event_category IS NOT NULL
       GROUP BY event_category`
    );

    const byProvider: Record<string, number> = {};
    for (const row of providers) {
      byProvider[row.provider] = row.count;
    }

    const byCategory: Record<string, number> = {};
    for (const row of categories) {
      byCategory[row.event_category] = row.count;
    }

    return {
      totalImages: totals[0]?.count ?? 0,
      totalCost: parseFloat(totals[0]?.cost ?? '0'),
      byProvider,
      byCategory,
    };
  } catch (e) {
    console.error(`Failed to get image stats: ${e}`);
    return { totalImages: 0, totalCost: 0, byProvider: {}, byCategory: {} };
  }
}

// ============================================================
// Update Operations
// ============================================================

/**
 * Rate an image quality (1-5)
 */
export async function rateImage(imageId: number, rating: number): Promise<boolean> {
  const pool = getPool();

  try {
    await pool.execute(
      'UPDATE generated_images SET quality_rating = ? WHERE id = ?',
      [Math.max(1, Math.min(5, rating)), imageId]
    );
    return true;
  } catch (e) {
    console.error(`Failed to rate image: ${e}`);
    return false;
  }
}

/**
 * Deactivate an image
 */
export async function deactivateImage(imageId: number): Promise<boolean> {
  const pool = getPool();

  try {
    await pool.execute(
      'UPDATE generated_images SET is_active = FALSE WHERE id = ?',
      [imageId]
    );
    return true;
  } catch (e) {
    console.error(`Failed to deactivate image: ${e}`);
    return false;
  }
}

// ============================================================
// Event Image Mapping
// ============================================================

/**
 * Map an event to an image
 */
export async function mapEventToImage(
  eventFunctionName: string,
  eventType: string,
  imageId: number,
  isPrimary: boolean = true
): Promise<boolean> {
  const pool = getPool();

  try {
    await pool.execute(
      `INSERT INTO event_images
       (event_function_name, event_type, generated_image_id, is_primary)
       VALUES (?, ?, ?, ?)`,
      [eventFunctionName, eventType, imageId, isPrimary]
    );
    return true;
  } catch (e) {
    console.error(`Failed to map event to image: ${e}`);
    return false;
  }
}