import { prisma } from '@/lib/db'

// All table definitions that should exist - keep in sync with API route
const TABLE_DEFINITIONS = [
  {
    name: 'admin_logs',
    sql: `CREATE TABLE IF NOT EXISTS admin_logs (
      id VARCHAR(36) PRIMARY KEY,
      userId VARCHAR(36),
      username VARCHAR(255),
      category VARCHAR(64) NOT NULL,
      action VARCHAR(255) NOT NULL,
      details TEXT,
      createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_category (category),
      INDEX idx_createdAt (createdAt)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  },
  {
    name: 'player_notes',
    sql: `CREATE TABLE IF NOT EXISTS player_notes (
      id VARCHAR(36) PRIMARY KEY,
      playerId VARCHAR(255) NOT NULL,
      content TEXT NOT NULL,
      type ENUM('INFO', 'WARNING', 'BAN_HISTORY', 'POSITIVE', 'NEGATIVE') DEFAULT 'INFO',
      isPrivate TINYINT(1) DEFAULT 0,
      authorId VARCHAR(36) NOT NULL,
      createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      INDEX idx_playerId (playerId)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  },
  {
    name: 'branding_settings',
    sql: `CREATE TABLE IF NOT EXISTS branding_settings (
      id VARCHAR(36) PRIMARY KEY,
      panelName VARCHAR(255) DEFAULT 'SKG UCP',
      logoUrl TEXT,
      backgroundUrl TEXT,
      primaryColor VARCHAR(7) DEFAULT '#3B82F6',
      secondaryColor VARCHAR(7) DEFAULT '#1E40AF',
      themeStyle VARCHAR(32) DEFAULT 'dark',
      customCss LONGTEXT,
      createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  },
  {
    name: 'domain_settings',
    sql: `CREATE TABLE IF NOT EXISTS domain_settings (
      id VARCHAR(36) PRIMARY KEY,
      useDirectIp TINYINT(1) DEFAULT 1,
      mainDomain VARCHAR(255),
      subdomain VARCHAR(255),
      finalUrl VARCHAR(512),
      sslEnabled TINYINT(1) DEFAULT 0,
      nginxConfig LONGTEXT,
      apacheConfig LONGTEXT,
      createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  },
  {
    name: 'installation_state',
    sql: `CREATE TABLE IF NOT EXISTS installation_state (
      id VARCHAR(36) PRIMARY KEY,
      isInstalled TINYINT(1) DEFAULT 0,
      installedAt DATETIME,
      installedBy VARCHAR(255),
      version VARCHAR(32) DEFAULT '1.0.0',
      installData JSON,
      createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  },
  {
    name: 'job_panels',
    sql: `CREATE TABLE IF NOT EXISTS job_panels (
      id VARCHAR(36) PRIMARY KEY,
      jobName VARCHAR(64) NOT NULL UNIQUE,
      displayName VARCHAR(128) NOT NULL,
      description TEXT,
      isActive TINYINT(1) DEFAULT 1,
      features JSON,
      settings JSON,
      createdBy VARCHAR(36),
      createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  },
  {
    name: 'job_panel_members',
    sql: `CREATE TABLE IF NOT EXISTS job_panel_members (
      id VARCHAR(36) PRIMARY KEY,
      panelId VARCHAR(36) NOT NULL,
      odentifier VARCHAR(255) NOT NULL,
      rank INT DEFAULT 0,
      permissions JSON,
      joinedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_panelId (panelId),
      INDEX idx_odentifier (odentifier)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  },
  {
    name: 'job_panel_announcements',
    sql: `CREATE TABLE IF NOT EXISTS job_panel_announcements (
      id VARCHAR(36) PRIMARY KEY,
      panelId VARCHAR(36) NOT NULL,
      title VARCHAR(255) NOT NULL,
      content TEXT NOT NULL,
      priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
      isPinned TINYINT(1) DEFAULT 0,
      authorId VARCHAR(36),
      createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      INDEX idx_panelId (panelId)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  }
]

let migrationRan = false

export async function runAutoMigration(): Promise<{ success: boolean; created: number; errors: number }> {
  // Only run once per server instance
  if (migrationRan) {
    return { success: true, created: 0, errors: 0 }
  }

  let created = 0
  let errors = 0

  try {
    for (const table of TABLE_DEFINITIONS) {
      try {
        // Using CREATE TABLE IF NOT EXISTS - safe to run multiple times
        await prisma.$executeRawUnsafe(table.sql)
        created++
      } catch (error) {
        // Log but don't fail - table might already exist with different structure
        console.error(`[AutoMigration] Error creating ${table.name}:`, error)
        errors++
      }
    }

    migrationRan = true
    console.log(`[AutoMigration] Complete: ${created} tables checked/created, ${errors} errors`)
    
    return { success: errors === 0, created, errors }
  } catch (error) {
    console.error('[AutoMigration] Failed:', error)
    return { success: false, created, errors: errors + 1 }
  }
}

// Check if migrations are needed
export async function checkMigrationStatus(): Promise<{
  needsMigration: boolean
  missingTables: string[]
}> {
  const missingTables: string[] = []

  for (const table of TABLE_DEFINITIONS) {
    try {
      const result = await prisma.$queryRawUnsafe<Array<{ count: bigint }>>(
        `SELECT COUNT(*) as count FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?`,
        table.name
      )
      if (Number(result[0]?.count) === 0) {
        missingTables.push(table.name)
      }
    } catch {
      missingTables.push(table.name)
    }
  }

  return {
    needsMigration: missingTables.length > 0,
    missingTables
  }
}