import prisma from "@/lib/prisma";

export default async function AdminSubscriptionsPage() {
  let plans: {
    id: number;
    name: string;
    slug: string;
    price_monthly: number;
    price_yearly: number;
    active: boolean;
    subscriber_count: bigint;
  }[] = [];

  let recentSubs: {
    id: number;
    user_id: number;
    username: string;
    email: string;
    plan_name: string;
    plan_slug: string;
    status: string;
    current_period_start: Date | null;
    current_period_end: Date | null;
    created_at: Date;
  }[] = [];

  try {
    plans = await prisma.$queryRawUnsafe(
      `SELECT sp.id, sp.name, sp.slug, sp.price_monthly, sp.price_yearly, sp.active,
              (SELECT COUNT(*)::bigint FROM user_subscriptions us WHERE us.plan_id = sp.id AND us.status = 'active') as subscriber_count
       FROM subscription_plans sp
       ORDER BY sp.id ASC`
    );
  } catch (e) {
    console.error("Failed to load subscription plans:", e);
  }

  try {
    recentSubs = await prisma.$queryRawUnsafe(
      `SELECT us.id, us.user_id, u.username, u.email,
              sp.name as plan_name, sp.slug as plan_slug,
              us.status,
              us.current_period_start, us.current_period_end,
              us.created_at
       FROM user_subscriptions us
       JOIN users u ON u.id = us.user_id
       JOIN subscription_plans sp ON sp.id = us.plan_id
       ORDER BY us.created_at DESC
       LIMIT 50`
    );
  } catch (e) {
    console.error("Failed to load recent subscriptions:", e);
  }

  const statusColors: Record<string, string> = {
    active: "bg-green-500/20 text-green-400",
    canceled: "bg-red-500/20 text-red-400",
    past_due: "bg-yellow-500/20 text-yellow-400",
    trialing: "bg-blue-500/20 text-blue-400",
    incomplete: "bg-orange-500/20 text-orange-400",
  };

  const planColors: Record<string, string> = {
    free: "bg-gray-500/20 text-gray-400",
    basic: "bg-blue-500/20 text-blue-400",
    premium: "bg-primary/20 text-primary",
    vip: "bg-vip/20 text-vip",
  };

  return (
    <div className="space-y-6">
      <h1 className="text-2xl font-bold">Subscription Management</h1>

      {/* Plans Overview */}
      <div className="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-4 gap-4">
        {plans.map((plan) => (
          <div key={plan.id} className="bg-surface rounded-lg p-5">
            <div className="flex items-center justify-between mb-3">
              <span
                className={`px-2 py-0.5 rounded text-xs font-semibold ${
                  planColors[plan.slug] || "bg-gray-500/20 text-gray-400"
                }`}
              >
                {plan.name}
              </span>
              <span
                className={`text-xs ${
                  plan.active ? "text-green-400" : "text-red-400"
                }`}
              >
                {plan.active ? "Active" : "Inactive"}
              </span>
            </div>
            <div className="text-3xl font-bold text-text mb-1">
              {Number(plan.subscriber_count).toLocaleString()}
            </div>
            <p className="text-text-muted text-sm">active subscribers</p>
            <div className="mt-3 text-text-muted text-xs space-y-1">
              <p>
                Monthly: ${Number(plan.price_monthly).toFixed(2)} | Yearly: $
                {Number(plan.price_yearly).toFixed(2)}
              </p>
            </div>
          </div>
        ))}
      </div>

      {/* Recent Subscriptions */}
      <div className="bg-surface rounded-lg p-6">
        <h2 className="text-lg font-semibold mb-4">Recent Subscriptions</h2>
        <div className="overflow-x-auto">
          <table className="w-full text-left">
            <thead>
              <tr className="border-b border-surface-light text-text-muted text-sm">
                <th className="pb-3 font-medium">User</th>
                <th className="pb-3 font-medium">Plan</th>
                <th className="pb-3 font-medium">Status</th>
                <th className="pb-3 font-medium">Period End</th>
                <th className="pb-3 font-medium">Created</th>
              </tr>
            </thead>
            <tbody>
              {recentSubs.map((sub) => (
                <tr
                  key={sub.id}
                  className="border-b border-surface-light last:border-0"
                >
                  <td className="py-3">
                    <div className="font-medium text-sm">{sub.username}</div>
                    <div className="text-text-muted text-xs">{sub.email}</div>
                  </td>
                  <td className="py-3">
                    <span
                      className={`inline-block px-2 py-0.5 rounded text-xs font-medium ${
                        planColors[sub.plan_slug] ||
                        "bg-gray-500/20 text-gray-400"
                      }`}
                    >
                      {sub.plan_name}
                    </span>
                  </td>
                  <td className="py-3">
                    <span
                      className={`inline-block px-2 py-0.5 rounded text-xs font-medium ${
                        statusColors[sub.status] ||
                        "bg-gray-500/20 text-gray-400"
                      }`}
                    >
                      {sub.status}
                    </span>
                  </td>
                  <td className="py-3 text-text-muted text-sm">
                    {sub.current_period_end
                      ? new Date(sub.current_period_end).toLocaleDateString()
                      : "-"}
                  </td>
                  <td className="py-3 text-text-muted text-sm">
                    {new Date(sub.created_at).toLocaleDateString()}
                  </td>
                </tr>
              ))}
              {recentSubs.length === 0 && (
                <tr>
                  <td
                    colSpan={5}
                    className="py-8 text-center text-text-muted"
                  >
                    No subscriptions yet.
                  </td>
                </tr>
              )}
            </tbody>
          </table>
        </div>
      </div>
    </div>
  );
}