import mysql from 'mysql2/promise'
import { prisma } from './db'

// Cached connection pool for job panel database
let jobPanelPool: mysql.Pool | null = null

export interface JobPanelDbConfig {
  host: string
  port: number
  database: string
  user: string
  password: string
}

/**
 * Get the job panel database configuration (uses same credentials as game db)
 */
export async function getJobPanelDbConfig(): Promise<JobPanelDbConfig | null> {
  try {
    // Check if the table exists first
    let config
    try {
      config = await prisma.gameDatabaseConfig.findFirst({
        where: { isActive: true },
      })
    } catch (tableError) {
      // Table might not exist - check if it's a "table doesn't exist" error
      const errorMsg = tableError instanceof Error ? tableError.message : String(tableError)
      if (errorMsg.includes("doesn't exist") || errorMsg.includes('does not exist')) {
        console.log('[JobPanelDB] game_database_configs table does not exist yet')
        return null
      }
      throw tableError
    }

    if (!config) {
      return null
    }

    return {
      host: config.host,
      port: config.port,
      database: 'skg_job_panel', // Separate database for job panels
      user: config.user,
      password: config.password,
    }
  } catch (error) {
    console.error('[JobPanelDB] Failed to get config:', error)
    return null
  }
}

/**
 * Check if the job panel database exists and is properly set up
 */
export async function checkJobPanelDbExists(): Promise<boolean> {
  try {
    const gameConfig = await prisma.gameDatabaseConfig.findFirst({
      where: { isActive: true },
    })

    if (!gameConfig) {
      return false
    }

    const tempPool = mysql.createPool({
      host: gameConfig.host,
      port: gameConfig.port,
      user: gameConfig.user,
      password: gameConfig.password,
      connectionLimit: 1,
    })

    try {
      // Check if database exists
      const [rows] = await tempPool.query(
        `SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'skg_job_panel'`
      ) as [any[], any]
      
      if (rows.length === 0) {
        await tempPool.end()
        return false
      }

      // Check if main table exists
      const [tables] = await tempPool.query(
        `SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'skg_job_panel' AND TABLE_NAME = 'job_panels'`
      ) as [any[], any]

      await tempPool.end()
      return tables.length > 0
    } catch {
      await tempPool.end()
      return false
    }
  } catch (error) {
    console.error('[JobPanelDB] Check failed:', error)
    return false
  }
}

/**
 * Initialize the job panel database (create if not exists)
 */
export async function initializeJobPanelDatabase(): Promise<{ success: boolean; error?: string }> {
  try {
    const gameConfig = await prisma.gameDatabaseConfig.findFirst({
      where: { isActive: true },
    })

    if (!gameConfig) {
      return { success: false, error: 'Game database not configured' }
    }

    // Connect without database to create it
    const tempPool = mysql.createPool({
      host: gameConfig.host,
      port: gameConfig.port,
      user: gameConfig.user,
      password: gameConfig.password,
      connectionLimit: 1,
    })

    // Create database if not exists
    await tempPool.query(`CREATE DATABASE IF NOT EXISTS skg_job_panel CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`)
    
    // Close temp pool
    await tempPool.end()

    // Now connect to the new database and create tables
    const jobPool = mysql.createPool({
      host: gameConfig.host,
      port: gameConfig.port,
      user: gameConfig.user,
      password: gameConfig.password,
      database: 'skg_job_panel',
      connectionLimit: 5,
    })

    // Create all required tables
    await createJobPanelTables(jobPool)

    await jobPool.end()

    return { success: true }
  } catch (error) {
    console.error('[JobPanelDB] Initialization failed:', error)
    return { success: false, error: error instanceof Error ? error.message : 'Initialization failed' }
  }
}

/**
 * Create all job panel tables
 */
export async function createJobPanelTables(pool: mysql.Pool): Promise<void> {
  // Job Panels table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panels (
      id VARCHAR(36) PRIMARY KEY,
      job_name VARCHAR(100) NOT NULL UNIQUE,
      job_label VARCHAR(255) NOT NULL,
      panel_name VARCHAR(255) NOT NULL,
      description TEXT,
      icon VARCHAR(50) DEFAULT 'briefcase',
      is_active BOOLEAN DEFAULT TRUE,
      created_by VARCHAR(36),
      created_by_name VARCHAR(255),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      INDEX idx_job_name (job_name),
      INDEX idx_is_active (is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)
  
  // Add created_by_name column if it doesn't exist (for existing installations)
  try {
    await pool.query(`
      ALTER TABLE job_panels ADD COLUMN IF NOT EXISTS created_by_name VARCHAR(255) AFTER created_by
    `)
  } catch (e) {
    // Column might already exist or DB doesn't support IF NOT EXISTS
    try {
      const [columns] = await pool.query(`
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'skg_job_panel' AND TABLE_NAME = 'job_panels' AND COLUMN_NAME = 'created_by_name'
      `) as [any[], any]
      if (columns.length === 0) {
        await pool.query(`ALTER TABLE job_panels ADD COLUMN created_by_name VARCHAR(255) AFTER created_by`)
      }
    } catch (e2) {
      console.log('[JobPanelDB] created_by_name column check skipped:', e2)
    }
  }

  // Job Panel Features table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_features (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      feature_key VARCHAR(50) NOT NULL,
      is_enabled BOOLEAN DEFAULT FALSE,
      config JSON,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      UNIQUE KEY unique_panel_feature (panel_id, feature_key),
      INDEX idx_panel_id (panel_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  // Job Panel Members (Job Leaders) table - uses game employee data
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_members (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      visible_identifier VARCHAR(100),
      username VARCHAR(100),
      visible_grade INT DEFAULT 0,
      visible_grade_name VARCHAR(100),
      ucp_user_id VARCHAR(36),
      role ENUM('LEADER', 'CO_LEADER', 'MEMBER') DEFAULT 'MEMBER',
      assigned_by VARCHAR(36),
      assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      UNIQUE KEY unique_panel_identifier (panel_id, visible_identifier),
      INDEX idx_panel_id (panel_id),
      INDEX idx_visible_identifier (visible_identifier),
      INDEX idx_ucp_user_id (ucp_user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)
  
  // Migration: Add new columns if table already exists with old schema
  try {
    await pool.query(`ALTER TABLE job_panel_members ADD COLUMN IF NOT EXISTS visible_identifier VARCHAR(100) AFTER panel_id`)
    await pool.query(`ALTER TABLE job_panel_members ADD COLUMN IF NOT EXISTS visible_grade INT DEFAULT 0 AFTER username`)
    await pool.query(`ALTER TABLE job_panel_members ADD COLUMN IF NOT EXISTS visible_grade_name VARCHAR(100) AFTER visible_grade`)
    await pool.query(`ALTER TABLE job_panel_members ADD COLUMN IF NOT EXISTS ucp_user_id VARCHAR(36) AFTER visible_grade_name`)
  } catch (e) {
    // Columns might already exist or DB doesn't support IF NOT EXISTS
  }

  // Job Panel Rank Permissions table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_rank_permissions (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      rank_grade INT NOT NULL,
      rank_name VARCHAR(100),
      permissions JSON NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      UNIQUE KEY unique_panel_rank (panel_id, rank_grade),
      INDEX idx_panel_id (panel_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  // Job Panel Webhook Settings table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_webhooks (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL UNIQUE,
      faction_webhook_url TEXT,
      faction_webhook_enabled BOOLEAN DEFAULT FALSE,
      admin_webhook_url TEXT,
      admin_webhook_enabled BOOLEAN DEFAULT FALSE,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  // Job Panel Logs table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_logs (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      action VARCHAR(100) NOT NULL,
      category ENUM('FACTION', 'ADMIN', 'SYSTEM') DEFAULT 'FACTION',
      actor_id VARCHAR(36),
      actor_name VARCHAR(100),
      target_id VARCHAR(36),
      target_name VARCHAR(100),
      details JSON,
      ip_address VARCHAR(45),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      INDEX idx_panel_id (panel_id),
      INDEX idx_action (action),
      INDEX idx_category (category),
      INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  // Criminal Records table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_criminal_records (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      citizen_id VARCHAR(50) NOT NULL,
      citizen_name VARCHAR(100),
      title VARCHAR(255) NOT NULL,
      description TEXT,
      offense_type ENUM('FELONY', 'MISDEMEANOR', 'INFRACTION', 'WARNING') DEFAULT 'INFRACTION',
      fine_amount DECIMAL(15,2) DEFAULT 0,
      jail_time INT DEFAULT 0,
      status ENUM('ACTIVE', 'SERVED', 'PARDONED', 'APPEALED') DEFAULT 'ACTIVE',
      created_by VARCHAR(36),
      created_by_name VARCHAR(100),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      INDEX idx_panel_id (panel_id),
      INDEX idx_citizen_id (citizen_id),
      INDEX idx_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  // Employee Management table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_employees (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      citizen_id VARCHAR(50) NOT NULL,
      citizen_name VARCHAR(100),
      rank_grade INT DEFAULT 0,
      rank_name VARCHAR(100),
      hire_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      hired_by VARCHAR(36),
      status ENUM('ACTIVE', 'ON_LEAVE', 'SUSPENDED', 'TERMINATED') DEFAULT 'ACTIVE',
      notes TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      INDEX idx_panel_id (panel_id),
      INDEX idx_citizen_id (citizen_id),
      INDEX idx_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  // Vehicle Management table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_vehicles (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      plate VARCHAR(20) NOT NULL,
      model VARCHAR(100),
      label VARCHAR(255),
      assigned_to VARCHAR(50),
      assigned_to_name VARCHAR(100),
      status ENUM('AVAILABLE', 'IN_USE', 'MAINTENANCE', 'IMPOUNDED', 'DESTROYED') DEFAULT 'AVAILABLE',
      notes TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      INDEX idx_panel_id (panel_id),
      INDEX idx_plate (plate),
      INDEX idx_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  // Storage Management table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_storage (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      item_name VARCHAR(100) NOT NULL,
      quantity INT DEFAULT 0,
      category ENUM('weapons', 'equipment', 'medical', 'vehicles', 'evidence', 'other') DEFAULT 'equipment',
      min_stock INT DEFAULT 0,
      added_by VARCHAR(36),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      INDEX idx_panel_id (panel_id),
      INDEX idx_category (category)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  // Dispatch Logs table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_dispatch_logs (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      type ENUM('callout', 'patrol', 'investigation', 'transport', 'other') DEFAULT 'other',
      priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
      title VARCHAR(255) NOT NULL,
      description TEXT,
      location VARCHAR(255),
      status ENUM('PENDING', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED') DEFAULT 'PENDING',
      created_by VARCHAR(36),
      completed_at TIMESTAMP,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      INDEX idx_panel_id (panel_id),
      INDEX idx_status (status),
      INDEX idx_priority (priority)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  // Billing/Invoices table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_invoices (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      invoice_number VARCHAR(50) NOT NULL,
      citizen_id VARCHAR(50) NOT NULL,
      citizen_name VARCHAR(100),
      amount DECIMAL(15,2) NOT NULL,
      reason TEXT,
      status ENUM('PENDING', 'PAID', 'CANCELLED', 'OVERDUE') DEFAULT 'PENDING',
      due_date TIMESTAMP,
      paid_at TIMESTAMP,
      created_by VARCHAR(36),
      created_by_name VARCHAR(100),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      INDEX idx_panel_id (panel_id),
      INDEX idx_citizen_id (citizen_id),
      INDEX idx_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  // Duty Tracking table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_duty_logs (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      citizen_id VARCHAR(50) NOT NULL,
      citizen_name VARCHAR(100),
      action ENUM('CLOCK_IN', 'CLOCK_OUT') NOT NULL,
      timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      session_duration INT,
      notes TEXT,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      INDEX idx_panel_id (panel_id),
      INDEX idx_citizen_id (citizen_id),
      INDEX idx_timestamp (timestamp)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  // Notes System table
  await pool.query(`
    CREATE TABLE IF NOT EXISTS job_panel_notes (
      id VARCHAR(36) PRIMARY KEY,
      panel_id VARCHAR(36) NOT NULL,
      title VARCHAR(255) NOT NULL,
      content TEXT,
      category VARCHAR(50),
      is_pinned BOOLEAN DEFAULT FALSE,
      visibility ENUM('PUBLIC', 'LEADERS_ONLY', 'PRIVATE') DEFAULT 'PUBLIC',
      created_by VARCHAR(36),
      created_by_name VARCHAR(100),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
      INDEX idx_panel_id (panel_id),
      INDEX idx_is_pinned (is_pinned)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  `)

  console.log('[JobPanelDB] All tables created successfully')
}

/**
 * Get or create a connection pool to the job panel database
 */
export async function getJobPanelPool(): Promise<mysql.Pool | null> {
  if (jobPanelPool) return jobPanelPool

  const config = await getJobPanelDbConfig()
  if (!config) return null

  try {
    jobPanelPool = mysql.createPool({
      host: config.host,
      port: config.port,
      user: config.user,
      password: config.password,
      database: config.database,
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0,
      enableKeepAlive: true,
      keepAliveInitialDelay: 0,
    })

    return jobPanelPool
  } catch (error) {
    console.error('[JobPanelDB] Failed to create pool:', error)
    return null
  }
}

/**
 * Ensure all job panel tables exist (creates them if missing)
 */
export async function ensureJobPanelTablesExist(): Promise<boolean> {
  try {
    const pool = await getJobPanelPool()
    if (!pool) return false
    
    // Check if main table exists
    const [tables] = await pool.query(
      `SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'skg_job_panel' AND TABLE_NAME = 'job_panels'`
    ) as [any[], any]
    
    if (tables.length === 0) {
      // Tables don't exist, create them
      await createJobPanelTables(pool)
    }
    
    return true
  } catch (error) {
    console.error('[JobPanelDB] ensureTablesExist failed:', error)
    return false
  }
}

/**
 * Execute a query on the job panel database
 */
export async function queryJobPanelDb<T = mysql.RowDataPacket[]>(
  sql: string,
  params?: unknown[]
): Promise<T> {
  const pool = await getJobPanelPool()
  if (!pool) {
    throw new Error('Job panel database not configured')
  }

  try {
    const [rows] = await pool.query<mysql.RowDataPacket[]>(sql, params)
    return rows as T
  } catch (error) {
    // If table doesn't exist, try to create all tables and retry
    const errorMessage = error instanceof Error ? error.message : String(error)
    if (errorMessage.includes("doesn't exist") || errorMessage.includes('ER_NO_SUCH_TABLE')) {
      console.log('[JobPanelDB] Table missing, attempting to create tables...')
      await createJobPanelTables(pool)
      // Retry the query
      const [rows] = await pool.query<mysql.RowDataPacket[]>(sql, params)
      return rows as T
    }
    throw error
  }
}

/**
 * Close the job panel database connection pool
 */
export async function closeJobPanelPool(): Promise<void> {
  if (jobPanelPool) {
    await jobPanelPool.end()
    jobPanelPool = null
  }
}