import { Op } from "sequelize";
import { initDb } from "@/lib/db";
import type { AuditLog } from "@/lib/db/models/audit-log";
import type { User } from "@/lib/db/models/user";
import type { Workspace } from "@/lib/db/models/workspace";
import type { Survey } from "@/lib/db/models/survey";
import type { Respondent } from "@/lib/db/models/respondent";
import { planMrrCents } from "@/config/plans";
import { calcCompletionRate } from "@/lib/utils/calcCompletionRate";
import type { PlanTier } from "@/types";

export interface PlatformOverview {
  mrrCents: number;
  mrrFormatted: string;
  activeClients: number;
  totalSurveys: number;
  globalResponseRate: number;
  newClientsThisMonth: number;
  newSurveysThisMonth: number;
}

export interface RevenueTrendPoint {
  month: string;
  amountCents: number;
}

export interface AuditLogEntry {
  id: string;
  action: string;
  level: "ok" | "warn" | "error";
  message: string;
  createdAt: Date;
}

export interface AdminClientRow {
  id: string;
  name: string;
  planTier: PlanTier;
  subscriptionStatus: string;
  surveyCount: number;
  mrrCents: number;
  mrrFormatted: string;
  isSuspended: boolean;
  ownerEmail: string | null;
  createdAt: Date;
}

export interface AdminSurveyRow {
  id: string;
  title: string;
  status: string;
  workspaceName: string;
  responseCount: number;
  createdAt: Date;
}

function startOfMonth(d = new Date()): Date {
  return new Date(d.getFullYear(), d.getMonth(), 1);
}

function formatMrr(cents: number): string {
  return `$${(cents / 100).toLocaleString("en-US", { maximumFractionDigits: 0 })}`;
}

function auditLevel(action: string): "ok" | "warn" | "error" {
  if (action.includes("failed") || action.includes("suspended")) return "error";
  if (action.includes("past_due") || action.includes("warning")) return "warn";
  return "ok";
}

function auditMessage(log: AuditLog): string {
  const meta = log.metadata ?? {};
  if (typeof meta.message === "string") return meta.message;
  if (log.action === "workspace.created") return "New workspace created";
  if (log.action === "billing.checkout_completed") {
    return `Client upgraded to ${String(meta.planTier ?? "paid plan")}`;
  }
  if (log.action === "billing.subscription_canceled") {
    return "Subscription canceled";
  }
  if (log.action === "stripe.webhook") {
    return String(meta.event ?? "Stripe webhook received");
  }
  return log.action.replace(/\./g, " ");
}

export interface AdminClientFilters {
  plan?: PlanTier | "all";
  search?: string;
}

/** Platform-wide KPIs for admin overview. */
export async function getPlatformOverview(): Promise<PlatformOverview> {
  const { Workspace, Survey, Respondent } = initDb();

  const monthStart = startOfMonth();

  const [workspaces, totalSurveys, allRespondents, completedRespondents] =
    await Promise.all([
      Workspace.findAll({
        where: { isSuspended: false },
        attributes: [
          "id",
          "planTier",
          "billingCycle",
          "subscriptionStatus",
          "createdAt",
        ],
      }),
      Survey.count(),
      Respondent.count(),
      Respondent.count({ where: { status: "completed" } }),
    ]);

  const paying = workspaces.filter(
    (w) => w.planTier !== "free" && w.subscriptionStatus === "active"
  );

  const mrrCents = paying.reduce(
    (sum, w) => sum + planMrrCents(w.planTier, w.billingCycle),
    0
  );

  const newClientsThisMonth = workspaces.filter(
    (w) => w.createdAt >= monthStart
  ).length;

  const newSurveysThisMonth = await Survey.count({
    where: { createdAt: { [Op.gte]: monthStart } },
  });

  return {
    mrrCents,
    mrrFormatted: formatMrr(mrrCents),
    activeClients: workspaces.length,
    totalSurveys,
    globalResponseRate: calcCompletionRate(
      completedRespondents,
      allRespondents
    ),
    newClientsThisMonth,
    newSurveysThisMonth,
  };
}

/** Monthly paid invoice totals for revenue chart. */
export async function getRevenueTrend(
  months = 6
): Promise<RevenueTrendPoint[]> {
  const { Invoice } = initDb();

  const since = new Date();
  since.setMonth(since.getMonth() - (months - 1));
  since.setDate(1);
  since.setHours(0, 0, 0, 0);

  const invoices = await Invoice.findAll({
    where: {
      status: "paid",
      issuedAt: { [Op.gte]: since },
    },
    order: [["issuedAt", "ASC"]],
  });

  const map = new Map<string, number>();

  for (const inv of invoices) {
    const key = inv.issuedAt.toISOString().slice(0, 7);
    map.set(key, (map.get(key) ?? 0) + inv.amount);
  }

  const points: RevenueTrendPoint[] = [];
  const cursor = new Date(since);

  for (let i = 0; i < months; i++) {
    const key = cursor.toISOString().slice(0, 7);
    const [year, month] = key.split("-");
    points.push({
      month: new Date(Number(year), Number(month) - 1).toLocaleDateString(
        "en-US",
        { month: "short", year: "2-digit" }
      ),
      amountCents: map.get(key) ?? 0,
    });
    cursor.setMonth(cursor.getMonth() + 1);
  }

  return points;
}

/** Recent audit log entries for admin log stream. */
export async function getRecentAuditLogs(limit = 12): Promise<AuditLogEntry[]> {
  const { AuditLog } = initDb();

  const logs = await AuditLog.findAll({
    order: [["createdAt", "DESC"]],
    limit,
  });

  return logs.map((log) => ({
    id: log.id,
    action: log.action,
    level: auditLevel(log.action),
    message: auditMessage(log),
    createdAt: log.createdAt,
  }));
}

/** Paginated client list for admin tables. */
export async function getAdminClients(
  filters: AdminClientFilters = {}
): Promise<AdminClientRow[]> {
  const { Workspace, User, Survey } = initDb();

  const where: Record<string, unknown> = {};

  if (filters.plan && filters.plan !== "all") {
    where.planTier = filters.plan;
  }

  if (filters.search?.trim()) {
    where.name = { [Op.like]: `%${filters.search.trim()}%` };
  }

  const workspaces = await Workspace.findAll({
    where,
    order: [["createdAt", "DESC"]],
    include: [
      {
        model: User,
        as: "users",
        attributes: ["email", "role"],
        required: false,
      },
      {
        model: Survey,
        as: "surveys",
        attributes: ["id"],
        required: false,
      },
    ],
  });

  return workspaces.map((ws) => {
    const users = ws.get("users") as User[] | undefined;
    const owner =
      users?.find((u) => u.role === "client") ?? users?.[0] ?? null;
    const surveys = ws.get("surveys") as Survey[] | undefined;
    const mrr = planMrrCents(ws.planTier, ws.billingCycle);

    return {
      id: ws.id,
      name: ws.name,
      planTier: ws.planTier,
      subscriptionStatus: ws.subscriptionStatus,
      surveyCount: surveys?.length ?? 0,
      mrrCents: mrr,
      mrrFormatted: mrr > 0 ? formatMrr(mrr) : "—",
      isSuspended: ws.isSuspended,
      ownerEmail: owner?.email ?? null,
      createdAt: ws.createdAt,
    };
  });
}

export interface AdminSurveyFilters {
  search?: string;
  status?: string;
}

/** All surveys across tenants for admin global view. */
export async function getAdminSurveys(
  filters: AdminSurveyFilters = {}
): Promise<AdminSurveyRow[]> {
  const { Survey, Workspace, Respondent } = initDb();

  const where: Record<string, unknown> = {};

  if (filters.status && filters.status !== "all") {
    where.status = filters.status;
  }

  if (filters.search?.trim()) {
    where.title = { [Op.like]: `%${filters.search.trim()}%` };
  }

  const surveys = await Survey.findAll({
    where,
    order: [["createdAt", "DESC"]],
    limit: 100,
    include: [
      {
        model: Workspace,
        as: "workspace",
        attributes: ["name"],
      },
      {
        model: Respondent,
        as: "respondents",
        attributes: ["id", "status"],
        required: false,
      },
    ],
  });

  return surveys.map((s) => {
    const workspace = s.get("workspace") as Workspace | undefined;
    const respondents = s.get("respondents") as Respondent[] | undefined;
    const responseCount =
      respondents?.filter((r) => r.status === "completed").length ?? 0;

    return {
      id: s.id,
      title: s.title,
      status: s.status,
      workspaceName: workspace?.name ?? "—",
      responseCount,
      createdAt: s.createdAt,
    };
  });
}

/** Revenue summary from invoices. */
export async function getRevenueSummary() {
  const { Invoice, Workspace } = initDb();

  const [paidTotal, openTotal, failedCount, recentInvoices] = await Promise.all([
    Invoice.sum("amount", { where: { status: "paid" } }),
    Invoice.sum("amount", { where: { status: "open" } }),
    Invoice.count({ where: { status: "failed" } }),
    Invoice.findAll({
      order: [["issuedAt", "DESC"]],
      limit: 20,
      include: [
        {
          model: Workspace,
          as: "workspace",
          attributes: ["name"],
        },
      ],
    }),
  ]);

  return {
    paidTotalCents: paidTotal ?? 0,
    openTotalCents: openTotal ?? 0,
    failedCount,
    recentInvoices: recentInvoices.map((inv) => {
      const workspace = inv.get("workspace") as Workspace | undefined;
      return {
        id: inv.id,
        workspaceName: workspace?.name ?? "—",
        amountCents: inv.amount,
        currency: inv.currency,
        status: inv.status,
        issuedAt: inv.issuedAt,
      };
    }),
  };
}