import { NextResponse } from 'next/server'
import { getSession } from '@/lib/auth'
import { prisma } from '@/lib/db'

// All table definitions that should exist
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: 'user_groups',
    sql: `CREATE TABLE IF NOT EXISTS user_groups (
      id VARCHAR(36) PRIMARY KEY,
      name VARCHAR(64) NOT NULL UNIQUE,
      displayName VARCHAR(128) NOT NULL,
      color VARCHAR(16) DEFAULT '#6366f1',
      icon VARCHAR(64),
      priority INT DEFAULT 0,
      permissions JSON,
      isDefault TINYINT(1) DEFAULT 0,
      createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  },
  {
    name: 'user_group_members',
    sql: `CREATE TABLE IF NOT EXISTS user_group_members (
      id VARCHAR(36) PRIMARY KEY,
      userId VARCHAR(36) NOT NULL,
      groupId VARCHAR(36) NOT NULL,
      joinedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY unique_user_group (userId, groupId)
    ) 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`
  },
  {
    name: 'support_tickets',
    sql: `CREATE TABLE IF NOT EXISTS support_tickets (
      id VARCHAR(36) PRIMARY KEY,
      number INT AUTO_INCREMENT UNIQUE,
      subject VARCHAR(255) NOT NULL,
      status ENUM('OPEN', 'IN_PROGRESS', 'WAITING_FOR_USER', 'RESOLVED', 'CLOSED') DEFAULT 'OPEN',
      priority ENUM('LOW', 'MEDIUM', 'HIGH', 'URGENT') DEFAULT 'MEDIUM',
      category VARCHAR(64),
      userId VARCHAR(36) NOT NULL,
      assignedToId VARCHAR(36),
      createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      closedAt DATETIME,
      INDEX idx_userId (userId),
      INDEX idx_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  },
  {
    name: 'ticket_messages',
    sql: `CREATE TABLE IF NOT EXISTS ticket_messages (
      id VARCHAR(36) PRIMARY KEY,
      ticketId VARCHAR(36) NOT NULL,
      authorId VARCHAR(36) NOT NULL,
      content TEXT NOT NULL,
      isInternal TINYINT(1) DEFAULT 0,
      createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_ticketId (ticketId)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  }
]

// Check if table exists
async function tableExists(tableName: string): Promise<boolean> {
  try {
    const result = await prisma.$queryRawUnsafe<Array<{ count: number }>>(
      `SELECT COUNT(*) as count FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?`,
      tableName
    )
    return result[0]?.count > 0
  } catch {
    return false
  }
}

// GET: Check migration status
export async function GET() {
  try {
    const session = await getSession()
    if (!session || !['ADMIN', 'SUPERADMIN'].includes(session.role)) {
      return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
    }

    const status = await Promise.all(
      TABLE_DEFINITIONS.map(async (table) => ({
        name: table.name,
        exists: await tableExists(table.name)
      }))
    )

    const missingTables = status.filter(t => !t.exists).map(t => t.name)
    const existingTables = status.filter(t => t.exists).map(t => t.name)

    return NextResponse.json({
      total: TABLE_DEFINITIONS.length,
      existing: existingTables.length,
      missing: missingTables.length,
      missingTables,
      existingTables,
      needsMigration: missingTables.length > 0
    })
  } catch (error) {
    console.error('[API] Migration status error:', error)
    return NextResponse.json({ error: 'Failed to check migration status' }, { status: 500 })
  }
}

// POST: Run migrations
export async function POST() {
  try {
    const session = await getSession()
    if (!session || session.role !== 'SUPERADMIN') {
      return NextResponse.json({ error: 'Only superadmin can run migrations' }, { status: 401 })
    }

    const results: { table: string; status: 'created' | 'exists' | 'error'; error?: string }[] = []

    for (const table of TABLE_DEFINITIONS) {
      try {
        const exists = await tableExists(table.name)
        if (exists) {
          results.push({ table: table.name, status: 'exists' })
        } else {
          await prisma.$executeRawUnsafe(table.sql)
          results.push({ table: table.name, status: 'created' })
        }
      } catch (error) {
        results.push({ 
          table: table.name, 
          status: 'error', 
          error: error instanceof Error ? error.message : 'Unknown error' 
        })
      }
    }

    const created = results.filter(r => r.status === 'created').length
    const errors = results.filter(r => r.status === 'error').length

    return NextResponse.json({
      success: errors === 0,
      message: `Migration complete: ${created} tables created, ${errors} errors`,
      results
    })
  } catch (error) {
    console.error('[API] Migration error:', error)
    return NextResponse.json({ error: 'Migration failed' }, { status: 500 })
  }
}