import prisma from "@/lib/prisma";
import Link from "next/link";

export const metadata = { title: "Churn Dashboard - Admin" };

export default async function ChurnDashboardPage() {
  const thirtyDaysAgo = new Date();
  thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

  // Users who haven't logged in for 30+ days but were active before
  const churnRisk = await prisma.$queryRawUnsafe<
    {
      id: number;
      username: string | null;
      email: string | null;
      user_type: string | null;
      lastonline_at: Date | null;
      total_spend: number;
      message_count: bigint;
      days_since_login: number;
    }[]
  >(
    `SELECT u.id, u.username, u.email, u.user_type, u.lastonline_at,
            COALESCE(c.credits, 0) AS total_spend,
            COALESCE(msg_count.cnt, 0) AS message_count,
            EXTRACT(DAY FROM NOW() - u.lastonline_at)::int AS days_since_login
     FROM users u
     LEFT JOIN credits c ON c.user_id = u.id
     LEFT JOIN (
       SELECT user_id, COUNT(*) AS cnt FROM messages GROUP BY user_id
     ) msg_count ON msg_count.user_id = u.id
     WHERE u.lastonline_at IS NOT NULL
       AND u.lastonline_at < $1
       AND COALESCE(msg_count.cnt, 0) > 0
     ORDER BY u.lastonline_at DESC
     LIMIT 100`,
    thirtyDaysAgo
  ).catch(() => []);

  return (
    <div className="space-y-6">
      <div className="flex items-center justify-between">
        <div>
          <h1 className="text-2xl font-bold">Predictive Churn Dashboard</h1>
          <p className="text-text-muted text-sm mt-1">
            Users inactive for 30+ days who previously engaged with the platform.
          </p>
        </div>
        <span className="text-sm text-text-muted bg-surface px-3 py-1 rounded-lg">
          {churnRisk.length} at risk
        </span>
      </div>

      <div className="bg-surface rounded-xl border border-white/5 overflow-hidden">
        {churnRisk.length === 0 ? (
          <div className="p-8 text-center text-text-muted">
            No churn-risk users found. Great news!
          </div>
        ) : (
          <div className="overflow-x-auto">
            <table className="w-full">
              <thead>
                <tr className="border-b border-white/5 text-left text-xs text-text-muted uppercase tracking-wider">
                  <th className="px-4 py-3">User</th>
                  <th className="px-4 py-3">Type</th>
                  <th className="px-4 py-3">Days Inactive</th>
                  <th className="px-4 py-3">Messages Sent</th>
                  <th className="px-4 py-3">Credits Balance</th>
                  <th className="px-4 py-3">Last Online</th>
                  <th className="px-4 py-3">Action</th>
                </tr>
              </thead>
              <tbody>
                {churnRisk.map((user) => {
                  const riskLevel =
                    user.days_since_login > 90
                      ? "high"
                      : user.days_since_login > 60
                      ? "medium"
                      : "low";

                  return (
                    <tr key={user.id} className="border-b border-white/5 hover:bg-surface-light/50 transition-colors">
                      <td className="px-4 py-3">
                        <div>
                          <p className="font-medium text-text text-sm">{user.username || "Unknown"}</p>
                          <p className="text-xs text-text-muted">{user.email}</p>
                        </div>
                      </td>
                      <td className="px-4 py-3">
                        <span className="text-xs px-2 py-0.5 rounded-full bg-surface-light text-text-muted capitalize">
                          {user.user_type || "user"}
                        </span>
                      </td>
                      <td className="px-4 py-3">
                        <span className={`text-sm font-medium ${
                          riskLevel === "high"
                            ? "text-red-400"
                            : riskLevel === "medium"
                            ? "text-orange-400"
                            : "text-yellow-400"
                        }`}>
                          {user.days_since_login} days
                        </span>
                      </td>
                      <td className="px-4 py-3 text-sm text-text-muted">
                        {Number(user.message_count)}
                      </td>
                      <td className="px-4 py-3 text-sm text-text-muted">
                        {Number(user.total_spend).toFixed(0)}
                      </td>
                      <td className="px-4 py-3 text-sm text-text-muted">
                        {user.lastonline_at
                          ? new Date(user.lastonline_at).toLocaleDateString()
                          : "Never"}
                      </td>
                      <td className="px-4 py-3">
                        <Link
                          href={`/admin/users?search=${encodeURIComponent(user.email || "")}`}
                          className="text-xs font-medium text-primary hover:text-primary-light transition-colors"
                        >
                          View User
                        </Link>
                      </td>
                    </tr>
                  );
                })}
              </tbody>
            </table>
          </div>
        )}
      </div>

      {/* Suggested actions */}
      <div className="bg-surface rounded-xl p-6 border border-white/5">
        <h2 className="text-lg font-semibold mb-3">Suggested Actions</h2>
        <ul className="space-y-2 text-sm text-text-muted">
          <li className="flex items-center gap-2">
            <span className="w-2 h-2 bg-yellow-400 rounded-full shrink-0" />
            Send "We miss you" email to users inactive for 30-60 days
          </li>
          <li className="flex items-center gap-2">
            <span className="w-2 h-2 bg-orange-400 rounded-full shrink-0" />
            Offer credit bonus to users inactive for 60-90 days
          </li>
          <li className="flex items-center gap-2">
            <span className="w-2 h-2 bg-red-400 rounded-full shrink-0" />
            High-risk users (90+ days) may need personal outreach or account review
          </li>
        </ul>
      </div>
    </div>
  );
}