import type { RowDataPacket } from 'mysql2'
import { getPool } from '@/lib/db'

export type AdminDashboardData = {
  users: {
    total: number
    last24Hours: number
    last7Days: number
    last30Days: number
  }
  listings: {
    total: number
    active: number
    ownActive: number
    importedActive: number
    ownPublishedLast7Days: number
    pendingReview: number
  }
  imports: {
    pending: number
    approved: number
    rejected: number
    expired: number
    lastSeenAt: string | null
  }
  recentUsers: Array<{
    name: string | null
    email: string
    createdAt: string | null
  }>
  recentListings: Array<{
    title: string
    slug: string
    status: string
    source: 'own' | 'imported'
    createdAt: string | null
    publishedAt: string | null
  }>
}

function numberValue(value: unknown): number {
  const number = Number(value ?? 0)
  return Number.isFinite(number) ? number : 0
}

function dateValue(value: unknown): string | null {
  if (!value) return null
  const date = value instanceof Date ? value : new Date(String(value))
  return Number.isNaN(date.getTime()) ? null : date.toISOString()
}

function row<T extends RowDataPacket>(rows: T[]): T {
  if (!rows[0]) throw new Error('Brak danych statystycznych.')
  return rows[0]
}

/**
 * Dane biznesowe do panelu administratora. Ruch i źródła odwiedzin pozostają
 * w GA4; tutaj pokazujemy wyłącznie dane, których źródłem jest nasza baza.
 */
export async function fetchAdminDashboard(): Promise<AdminDashboardData> {
  if (!process.env.DATABASE_URL?.trim()) {
    throw new Error('Brak połączenia z bazą danych.')
  }

  const pool = getPool()
  const [userCountResult, listingCountResult, importCountResult, recentUserResult, recentListingResult] =
    await Promise.all([
      pool.query<RowDataPacket[]>(
        `SELECT
           COUNT(*) AS total,
           SUM(created_at >= NOW() - INTERVAL 1 DAY) AS last_24h,
           SUM(created_at >= NOW() - INTERVAL 7 DAY) AS last_7d,
           SUM(created_at >= NOW() - INTERVAL 30 DAY) AS last_30d
         FROM users`,
      ),
      pool.query<RowDataPacket[]>(
        `SELECT
           COUNT(*) AS total,
           SUM(status = 'active') AS active,
           SUM(status = 'active' AND (import_source IS NULL OR TRIM(import_source) = '')) AS own_active,
           SUM(status = 'active' AND COALESCE(TRIM(import_source), '') <> '') AS imported_active,
           SUM(
             status = 'active'
             AND (import_source IS NULL OR TRIM(import_source) = '')
             AND published_at >= NOW() - INTERVAL 7 DAY
           ) AS own_published_last_7d,
           SUM(status = 'pending_review') AS pending_review
         FROM listings`,
      ),
      pool.query<RowDataPacket[]>(
        `SELECT
           SUM(status = 'pending') AS pending,
           SUM(status = 'approved') AS approved,
           SUM(status = 'rejected') AS rejected,
           SUM(status = 'expired') AS expired,
           MAX(last_seen_at) AS last_seen_at
         FROM listing_imports`,
      ),
      pool.query<RowDataPacket[]>(
        `SELECT name, email, created_at
         FROM users
         ORDER BY created_at DESC
         LIMIT 6`,
      ),
      pool.query<RowDataPacket[]>(
        `SELECT title, slug, status, import_source, created_at, published_at
         FROM listings
         ORDER BY COALESCE(published_at, created_at) DESC
         LIMIT 8`,
      ),
    ])

  const users = row(userCountResult[0])
  const listings = row(listingCountResult[0])
  const imports = row(importCountResult[0])

  return {
    users: {
      total: numberValue(users.total),
      last24Hours: numberValue(users.last_24h),
      last7Days: numberValue(users.last_7d),
      last30Days: numberValue(users.last_30d),
    },
    listings: {
      total: numberValue(listings.total),
      active: numberValue(listings.active),
      ownActive: numberValue(listings.own_active),
      importedActive: numberValue(listings.imported_active),
      ownPublishedLast7Days: numberValue(listings.own_published_last_7d),
      pendingReview: numberValue(listings.pending_review),
    },
    imports: {
      pending: numberValue(imports.pending),
      approved: numberValue(imports.approved),
      rejected: numberValue(imports.rejected),
      expired: numberValue(imports.expired),
      lastSeenAt: dateValue(imports.last_seen_at),
    },
    recentUsers: recentUserResult[0].map((item) => ({
      name: item.name ? String(item.name) : null,
      email: String(item.email),
      createdAt: dateValue(item.created_at),
    })),
    recentListings: recentListingResult[0].map((item) => ({
      title: String(item.title),
      slug: String(item.slug),
      status: String(item.status),
      source: item.import_source && String(item.import_source).trim() ? 'imported' : 'own',
      createdAt: dateValue(item.created_at),
      publishedAt: dateValue(item.published_at),
    })),
  }
}