import { prisma } from '@/lib/db'
import { queryGameDb, getGameDbConfig, getGameDbPool } from '@/lib/game-db'
import type { RowDataPacket } from 'mysql2'
import type { CustomPage, CustomPageWidget, WidgetType, PageVisibility } from '@prisma/client'

// ==========================================
// TYPES
// ==========================================

export interface DataSource {
  id: string
  name: string
  label: string
  description: string
  type: 'predefined' | 'custom'
  table?: string
  columns?: DataSourceColumn[]
}

export interface DataSourceColumn {
  name: string
  label: string
  type: 'string' | 'number' | 'boolean' | 'json' | 'date'
  format?: string
}

export interface WidgetConfig {
  id: string
  type: WidgetType
  title: string
  dataSource?: string
  columns?: string[]
  aggregation?: 'count' | 'sum' | 'avg' | 'min' | 'max'
  field?: string
  filters?: { column: string; operator: string; value: unknown }[]
  limit?: number
  chartType?: 'bar' | 'line' | 'pie' | 'area'
  refreshInterval?: number
}

export interface PageConfig {
  id: string
  title: string
  slug: string
  icon: string
  description?: string
  isPublished: boolean
  visibility: PageVisibility
  allowedRoles?: string[]
  widgets: WidgetConfig[]
  layout?: {
    columns: number
    gap: number
  }
}

// ==========================================
// PREDEFINED DATA SOURCES
// ==========================================

export function getPredefinedDataSources(framework: 'ESX' | 'QBCORE'): DataSource[] {
  if (framework === 'QBCORE') {
    return [
      {
        id: 'players',
        name: 'players',
        label: 'Players',
        description: 'All registered player characters',
        type: 'predefined',
        table: 'players',
        columns: [
          { name: 'citizenid', label: 'Citizen ID', type: 'string' },
          { name: 'name', label: 'Name', type: 'string' },
          { name: 'license', label: 'License', type: 'string' },
          { name: 'charinfo', label: 'Character Info', type: 'json' },
          { name: 'money', label: 'Money', type: 'json' },
          { name: 'job', label: 'Job', type: 'json' },
          { name: 'gang', label: 'Gang', type: 'json' },
          { name: 'metadata', label: 'Metadata', type: 'json' },
          { name: 'last_updated', label: 'Last Updated', type: 'date' },
        ],
      },
      {
        id: 'vehicles',
        name: 'player_vehicles',
        label: 'Vehicles',
        description: 'Player owned vehicles',
        type: 'predefined',
        table: 'player_vehicles',
        columns: [
          { name: 'id', label: 'ID', type: 'number' },
          { name: 'citizenid', label: 'Owner', type: 'string' },
          { name: 'plate', label: 'Plate', type: 'string' },
          { name: 'vehicle', label: 'Model', type: 'string' },
          { name: 'garage', label: 'Garage', type: 'string' },
          { name: 'state', label: 'State', type: 'number' },
          { name: 'fuel', label: 'Fuel', type: 'number' },
          { name: 'engine', label: 'Engine', type: 'number' },
          { name: 'body', label: 'Body', type: 'number' },
        ],
      },
      {
        id: 'inventory',
        name: 'player_inventory', 
        label: 'Inventory',
        description: 'Player inventory items',
        type: 'predefined',
        table: 'player_inventory',
        columns: [
          { name: 'id', label: 'ID', type: 'number' },
          { name: 'citizenid', label: 'Owner', type: 'string' },
          { name: 'item', label: 'Item', type: 'string' },
          { name: 'amount', label: 'Amount', type: 'number' },
          { name: 'slot', label: 'Slot', type: 'number' },
          { name: 'info', label: 'Info', type: 'json' },
        ],
      },
    ]
  }

  // ESX
  return [
    {
      id: 'players',
      name: 'users',
      label: 'Players',
      description: 'All registered player characters',
      type: 'predefined',
      table: 'users',
      columns: [
        { name: 'identifier', label: 'Identifier', type: 'string' },
        { name: 'firstname', label: 'First Name', type: 'string' },
        { name: 'lastname', label: 'Last Name', type: 'string' },
        { name: 'accounts', label: 'Accounts', type: 'json' },
        { name: 'job', label: 'Job', type: 'string' },
        { name: 'job_grade', label: 'Job Grade', type: 'number' },
        { name: 'phone_number', label: 'Phone', type: 'string' },
        { name: 'dateofbirth', label: 'Date of Birth', type: 'string' },
        { name: 'sex', label: 'Sex', type: 'string' },
      ],
    },
    {
      id: 'vehicles',
      name: 'owned_vehicles',
      label: 'Vehicles',
      description: 'Player owned vehicles',
      type: 'predefined',
      table: 'owned_vehicles',
      columns: [
        { name: 'owner', label: 'Owner', type: 'string' },
        { name: 'plate', label: 'Plate', type: 'string' },
        { name: 'vehicle', label: 'Vehicle Data', type: 'json' },
        { name: 'stored', label: 'Stored', type: 'boolean' },
        { name: 'garage', label: 'Garage', type: 'string' },
      ],
    },
    {
      id: 'inventory',
      name: 'user_inventory',
      label: 'Inventory',
      description: 'Player inventory items',
      type: 'predefined',
      table: 'user_inventory',
      columns: [
        { name: 'identifier', label: 'Owner', type: 'string' },
        { name: 'item', label: 'Item', type: 'string' },
        { name: 'count', label: 'Count', type: 'number' },
      ],
    },
  ]
}

// ==========================================
// GET AVAILABLE TABLES
// ==========================================

export async function getAvailableTables(): Promise<string[]> {
  try {
    const pool = await getGameDbPool()
    if (!pool) return []

    const [rows] = await pool.query<RowDataPacket[]>('SHOW TABLES')
    const tableKey = Object.keys(rows[0] || {})[0]
    
    return rows.map(row => row[tableKey]).filter(Boolean)
  } catch (error) {
    console.error('[PanelBuilder] Error getting tables:', error)
    return []
  }
}

// ==========================================
// GET TABLE COLUMNS
// ==========================================

export async function getTableColumns(tableName: string): Promise<DataSourceColumn[]> {
  try {
    const pool = await getGameDbPool()
    if (!pool) return []

    const [rows] = await pool.query<RowDataPacket[]>(`DESCRIBE ${tableName}`)
    
    return rows.map(row => ({
      name: row.Field,
      label: row.Field.replace(/_/g, ' ').replace(/\b\w/g, (l: string) => l.toUpperCase()),
      type: mapMySQLType(row.Type),
    }))
  } catch (error) {
    console.error('[PanelBuilder] Error getting columns:', error)
    return []
  }
}

function mapMySQLType(mysqlType: string): 'string' | 'number' | 'boolean' | 'json' | 'date' {
  const type = mysqlType.toLowerCase()
  
  if (type.includes('int') || type.includes('decimal') || type.includes('float') || type.includes('double')) {
    return 'number'
  }
  if (type.includes('json') || type.includes('longtext') || type.includes('mediumtext')) {
    return 'json'
  }
  if (type.includes('date') || type.includes('time')) {
    return 'date'
  }
  if (type.includes('tinyint(1)') || type.includes('boolean')) {
    return 'boolean'
  }
  return 'string'
}

// ==========================================
// EXECUTE WIDGET QUERY
// ==========================================

export async function executeWidgetQuery(
  widget: WidgetConfig,
  dataSource: DataSource
): Promise<unknown> {
  if (!dataSource.table) return null

  try {
    const pool = await getGameDbPool()
    if (!pool) return null

    let sql = ''
    const params: unknown[] = []

    switch (widget.type) {
      case 'STAT_CARD':
        if (widget.aggregation && widget.field) {
          sql = `SELECT ${widget.aggregation.toUpperCase()}(${widget.field}) as value FROM ${dataSource.table}`
        } else {
          sql = `SELECT COUNT(*) as value FROM ${dataSource.table}`
        }
        break

      case 'TABLE':
        const columns = widget.columns?.length ? widget.columns.join(', ') : '*'
        sql = `SELECT ${columns} FROM ${dataSource.table}`
        if (widget.limit) {
          sql += ` LIMIT ${widget.limit}`
        }
        break

      case 'CHART':
        if (widget.field) {
          sql = `SELECT ${widget.field} as label, COUNT(*) as value FROM ${dataSource.table} GROUP BY ${widget.field} LIMIT 10`
        }
        break

      default:
        return null
    }

    // Add filters
    if (widget.filters?.length) {
      const whereClauses = widget.filters.map(filter => {
        params.push(filter.value)
        return `${filter.column} ${filter.operator} ?`
      })
      sql = sql.replace('FROM', `FROM`).replace(dataSource.table, `${dataSource.table} WHERE ${whereClauses.join(' AND ')}`)
    }

    const [rows] = await pool.query<RowDataPacket[]>(sql, params)
    
    if (widget.type === 'STAT_CARD') {
      return rows[0]?.value || 0
    }
    
    return rows
  } catch (error) {
    console.error('[PanelBuilder] Query error:', error)
    return null
  }
}

// ==========================================
// PAGE CRUD OPERATIONS
// ==========================================

export async function getCustomPages(): Promise<CustomPage[]> {
  return prisma.customPage.findMany({
    orderBy: { sortOrder: 'asc' },
    include: {
      widgets: {
        orderBy: { sortOrder: 'asc' },
      },
    },
  })
}

export async function getPublishedPages(): Promise<CustomPage[]> {
  return prisma.customPage.findMany({
    where: { isPublished: true },
    orderBy: { sortOrder: 'asc' },
  })
}

export async function getCustomPage(idOrSlug: string): Promise<(CustomPage & { widgets: CustomPageWidget[] }) | null> {
  return prisma.customPage.findFirst({
    where: {
      OR: [
        { id: idOrSlug },
        { slug: idOrSlug },
      ],
    },
    include: {
      widgets: {
        orderBy: { sortOrder: 'asc' },
      },
    },
  })
}

export async function createCustomPage(
  data: {
    title: string
    slug: string
    icon?: string
    description?: string
    visibility?: PageVisibility
    createdById?: string
  }
): Promise<CustomPage> {
  return prisma.customPage.create({
    data: {
      title: data.title,
      slug: data.slug,
      icon: data.icon || 'file',
      description: data.description,
      visibility: data.visibility || 'ALL_USERS',
      createdById: data.createdById,
    },
  })
}

export async function updateCustomPage(
  id: string,
  data: Partial<{
    title: string
    slug: string
    icon: string
    description: string
    isPublished: boolean
    visibility: PageVisibility
    allowedRoles: string[]
    layout: unknown
    sortOrder: number
  }>
): Promise<CustomPage> {
  return prisma.customPage.update({
    where: { id },
    data,
  })
}

export async function deleteCustomPage(id: string): Promise<void> {
  await prisma.customPage.delete({
    where: { id },
  })
}

// ==========================================
// WIDGET CRUD OPERATIONS
// ==========================================

export async function addWidget(
  pageId: string,
  data: {
    type: WidgetType
    title?: string
    config?: unknown
    dataSource?: unknown
    position?: unknown
    size?: unknown
  }
): Promise<CustomPageWidget> {
  const lastWidget = await prisma.customPageWidget.findFirst({
    where: { pageId },
    orderBy: { sortOrder: 'desc' },
  })

  return prisma.customPageWidget.create({
    data: {
      pageId,
      type: data.type,
      title: data.title,
      config: data.config as object,
      dataSource: data.dataSource as object,
      position: data.position as object,
      size: data.size as object,
      sortOrder: (lastWidget?.sortOrder || 0) + 1,
    },
  })
}

export async function updateWidget(
  id: string,
  data: Partial<{
    type: WidgetType
    title: string
    config: unknown
    dataSource: unknown
    position: unknown
    size: unknown
    sortOrder: number
  }>
): Promise<CustomPageWidget> {
  return prisma.customPageWidget.update({
    where: { id },
    data: data as object,
  })
}

export async function deleteWidget(id: string): Promise<void> {
  await prisma.customPageWidget.delete({
    where: { id },
  })
}

export async function reorderWidgets(pageId: string, widgetIds: string[]): Promise<void> {
  const updates = widgetIds.map((id, index) =>
    prisma.customPageWidget.update({
      where: { id },
      data: { sortOrder: index },
    })
  )
  await prisma.$transaction(updates)
}