import { NextResponse } from "next/server";
import { eq, sql } from "@fitai/database";
import { db } from "@fitai/database";
import log from "@/lib/logger";

async function ensureGymsTable() {
  await db.run(sql`
    CREATE TABLE IF NOT EXISTS gyms (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      location TEXT,
      status TEXT NOT NULL CHECK (status IN ('active','inactive')) DEFAULT 'active',
      admin_user_id TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    )
  `);
}

// GET /api/gyms/[id]/stats
// Get stats for a specific gym
export async function GET(
  request: Request,
  { params }: { params: Promise<{ id: string }> },
) {
  try {
    const { id: gymId } = await params;
    await ensureGymsTable();

    // Get gym info
    const gymRows = await db.all(sql`SELECT * FROM gyms WHERE id = ${gymId}`);
    if (gymRows.length === 0) {
      return new NextResponse("Gym not found", { status: 404 });
    }
    const gym = gymRows[0];

    // Get user counts
    const usersResult = await db.all(
      sql`SELECT role, COUNT(*) as count FROM users WHERE gym_id = ${gymId} GROUP BY role`,
    );

    const userCounts: Record<string, number> = {
      admin: 0,
      trainer: 0,
      client: 0,
    };
    for (const row of usersResult as any[]) {
      if (row.role in userCounts) {
        userCounts[row.role] = row.count;
      }
    }

    // Get client stats
    const clientsResult = (await db.all(
      sql`SELECT 
        membership_type, 
        membership_status, 
        COUNT(*) as count 
      FROM clients 
      WHERE user_id IN (SELECT id FROM users WHERE gym_id = ${gymId})
      GROUP BY membership_type, membership_status`,
    )) as any[];

    const membershipStats: Record<string, number> = {
      basic: 0,
      premium: 0,
      vip: 0,
    };
    const activeClients = clientsResult.filter(
      (c: any) => c.membership_status === "active",
    );
    for (const row of activeClients) {
      if (row.membership_type in membershipStats) {
        membershipStats[row.membership_type] = row.count;
      }
    }

    // Get recent activity (attendance in last 30 days)
    const thirtyDaysAgo = Date.now() - 30 * 24 * 60 * 60 * 1000;
    const attendanceResult = (await db.all(
      sql`SELECT COUNT(*) as count FROM attendance 
          WHERE user_id IN (SELECT id FROM users WHERE gym_id = ${gymId})
          AND check_in_time > ${thirtyDaysAgo}`,
    )) as any[];
    const attendanceCount = attendanceResult[0]?.count || 0;

    const stats = {
      totalUsers: userCounts.admin + userCounts.trainer + userCounts.client,
      admins: userCounts.admin,
      trainers: userCounts.trainer,
      clients: userCounts.client,
      membershipStats,
      activeClients: activeClients.reduce(
        (sum: number, c: any) => sum + c.count,
        0,
      ),
      attendanceLast30Days: attendanceCount,
    };

    return NextResponse.json({ gym, stats });
  } catch (error) {
    log.error("Failed to get gym stats", error);
    return new NextResponse("Internal Server Error", { status: 500 });
  }
}
