import { Op, fn, col } from "sequelize";
import { initDb } from "@/lib/db";
import { calcCompletionRate } from "@/lib/utils/calcCompletionRate";
import { formatDuration } from "@/lib/utils/format";

export interface DashboardStats {
  totalViews: number;
  responses: number;
  completionRate: number;
  avgTimeToComplete: string;
  responseQuotaUsed: number;
  responseQuotaLimit: number;
  planTier: string;
}

export interface DashboardTimePoint {
  date: string;
  count: number;
}

export interface DashboardNamedCount {
  name: string;
  value: number;
}

export interface DashboardSurveySnapshot {
  id: string;
  title: string;
  status: "draft" | "active" | "closed";
  responseCount: number;
}

export interface DashboardOverview extends DashboardStats {
  surveyTotal: number;
  surveyActive: number;
  surveyDraft: number;
  surveyClosed: number;
  inProgress: number;
  abandoned: number;
  viewsDeltaPct: number | null;
  responsesDeltaPct: number | null;
  quotaPercent: number;
  responsesOverTime: DashboardTimePoint[];
  statusBreakdown: DashboardNamedCount[];
  topSurveys: DashboardSurveySnapshot[];
  activeSurveys: DashboardSurveySnapshot[];
}

/** Aggregate dashboard stats for a workspace. */
export async function getDashboardStats(
  workspaceId: string
): Promise<DashboardStats> {
  const { Workspace, Survey, Respondent } = initDb();

  const workspace = await Workspace.findByPk(workspaceId);
  if (!workspace) {
    throw new Error("Workspace not found");
  }

  const surveys = await Survey.findAll({
    where: { workspaceId },
    attributes: ["id"],
  });

  const surveyIds = surveys.map((s) => s.id);

  if (surveyIds.length === 0) {
    return {
      totalViews: 0,
      responses: 0,
      completionRate: 0,
      avgTimeToComplete: "—",
      responseQuotaUsed: workspace.responseQuotaUsed,
      responseQuotaLimit: workspace.responseQuotaLimit,
      planTier: workspace.planTier,
    };
  }

  const [totalViews, completedCount, avgRow] = await Promise.all([
    Respondent.count({ where: { surveyId: { [Op.in]: surveyIds } } }),
    Respondent.count({
      where: { surveyId: { [Op.in]: surveyIds }, status: "completed" },
    }),
    Respondent.findOne({
      where: {
        surveyId: { [Op.in]: surveyIds },
        status: "completed",
        timeToCompleteSeconds: { [Op.not]: null },
      },
      attributes: [[fn("AVG", col("timeToCompleteSeconds")), "avgSeconds"]],
      raw: true,
    }) as Promise<{ avgSeconds: string | number | null } | null>,
  ]);

  const completionRate = calcCompletionRate(completedCount, totalViews);
  const avgRaw = avgRow?.avgSeconds;
  const avgTime =
    avgRaw == null || avgRaw === ""
      ? null
      : Math.round(Number(avgRaw));

  return {
    totalViews,
    responses: completedCount,
    completionRate,
    avgTimeToComplete: formatDuration(avgTime),
    responseQuotaUsed: workspace.responseQuotaUsed,
    responseQuotaLimit: workspace.responseQuotaLimit,
    planTier: workspace.planTier,
  };
}

function localDateKey(d: Date): string {
  return `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, "0")}-${String(d.getDate()).padStart(2, "0")}`;
}

function daysAgo(n: number): Date {
  const d = new Date();
  d.setHours(0, 0, 0, 0);
  d.setDate(d.getDate() - n);
  return d;
}

function pctChange(current: number, previous: number): number | null {
  if (previous === 0) return current > 0 ? 100 : null;
  return Math.round(((current - previous) / previous) * 1000) / 10;
}

/** Workspace overview for the home dashboard (counts + chart series). */
export async function getDashboardOverview(
  workspaceId: string
): Promise<DashboardOverview> {
  const { Workspace, Survey, Respondent } = initDb();

  const workspace = await Workspace.findByPk(workspaceId);
  if (!workspace) {
    throw new Error("Workspace not found");
  }

  const surveys = await Survey.findAll({
    where: { workspaceId },
    attributes: ["id", "title", "status", "createdAt", "publishedAt"],
    order: [["createdAt", "DESC"]],
  });

  const emptyStats: DashboardOverview = {
    totalViews: 0,
    responses: 0,
    completionRate: 0,
    avgTimeToComplete: "—",
    responseQuotaUsed: workspace.responseQuotaUsed,
    responseQuotaLimit: workspace.responseQuotaLimit,
    planTier: workspace.planTier,
    surveyTotal: 0,
    surveyActive: 0,
    surveyDraft: 0,
    surveyClosed: 0,
    inProgress: 0,
    abandoned: 0,
    viewsDeltaPct: null,
    responsesDeltaPct: null,
    quotaPercent: Math.min(
      100,
      (workspace.responseQuotaUsed / Math.max(workspace.responseQuotaLimit, 1)) *
        100
    ),
    responsesOverTime: [],
    statusBreakdown: [
      { name: "Active", value: 0 },
      { name: "Draft", value: 0 },
      { name: "Closed", value: 0 },
    ],
    topSurveys: [],
    activeSurveys: [],
  };

  if (surveys.length === 0) {
    return emptyStats;
  }

  const surveyIds = surveys.map((s) => s.id);
  const from30 = daysAgo(30);
  const from60 = daysAgo(60);
  const from14 = daysAgo(13);

  const dayKeys: string[] = [];
  for (let i = 13; i >= 0; i--) {
    dayKeys.push(localDateKey(daysAgo(i)));
  }

  const [
    statusRows,
    avgRow,
    completedBySurveyRows,
    viewsThis,
    viewsPrev,
    respThis,
    respPrev,
    dayRows,
  ] = await Promise.all([
    Respondent.findAll({
      attributes: ["status", [fn("COUNT", col("id")), "count"]],
      where: { surveyId: { [Op.in]: surveyIds } },
      group: ["status"],
      raw: true,
    }) as unknown as Promise<Array<{ status: string; count: string | number }>>,
    Respondent.findOne({
      where: {
        surveyId: { [Op.in]: surveyIds },
        status: "completed",
        timeToCompleteSeconds: { [Op.not]: null },
      },
      attributes: [[fn("AVG", col("timeToCompleteSeconds")), "avgSeconds"]],
      raw: true,
    }) as Promise<{ avgSeconds: string | number | null } | null>,
    Respondent.findAll({
      attributes: ["surveyId", [fn("COUNT", col("id")), "count"]],
      where: {
        surveyId: { [Op.in]: surveyIds },
        status: "completed",
      },
      group: ["surveyId"],
      raw: true,
    }) as unknown as Promise<
      Array<{ surveyId: string; count: string | number }>
    >,
    Respondent.count({
      where: {
        surveyId: { [Op.in]: surveyIds },
        startedAt: { [Op.gte]: from30 },
      },
    }),
    Respondent.count({
      where: {
        surveyId: { [Op.in]: surveyIds },
        startedAt: { [Op.gte]: from60, [Op.lt]: from30 },
      },
    }),
    Respondent.count({
      where: {
        surveyId: { [Op.in]: surveyIds },
        status: "completed",
        [Op.or]: [
          { completedAt: { [Op.gte]: from30 } },
          {
            completedAt: null,
            startedAt: { [Op.gte]: from30 },
          },
        ],
      },
    }),
    Respondent.count({
      where: {
        surveyId: { [Op.in]: surveyIds },
        status: "completed",
        [Op.or]: [
          { completedAt: { [Op.gte]: from60, [Op.lt]: from30 } },
          {
            completedAt: null,
            startedAt: { [Op.gte]: from60, [Op.lt]: from30 },
          },
        ],
      },
    }),
    Respondent.findAll({
      attributes: [
        [
          fn("DATE", fn("COALESCE", col("completedAt"), col("startedAt"))),
          "day",
        ],
        [fn("COUNT", col("id")), "count"],
      ],
      where: {
        surveyId: { [Op.in]: surveyIds },
        status: "completed",
        [Op.or]: [
          { completedAt: { [Op.gte]: from14 } },
          {
            completedAt: null,
            startedAt: { [Op.gte]: from14 },
          },
        ],
      },
      group: ["day"],
      raw: true,
    }) as unknown as Promise<Array<{ day: string; count: string | number }>>,
  ]);

  const statusMap = new Map(
    statusRows.map((r) => [r.status, Number(r.count) || 0])
  );
  const totalViews = Array.from(statusMap.values()).reduce((a, b) => a + b, 0);
  const completedCount = statusMap.get("completed") ?? 0;
  const inProgress = statusMap.get("in_progress") ?? 0;
  const abandoned = statusMap.get("abandoned") ?? 0;

  const avgRaw = avgRow?.avgSeconds;
  const avgSeconds =
    avgRaw == null || avgRaw === "" ? null : Math.round(Number(avgRaw));

  const surveyActive = surveys.filter((s) => s.status === "active").length;
  const surveyDraft = surveys.filter((s) => s.status === "draft").length;
  const surveyClosed = surveys.filter((s) => s.status === "closed").length;

  const completedBySurvey = new Map(
    completedBySurveyRows.map((r) => [r.surveyId, Number(r.count) || 0])
  );

  const snapshots: DashboardSurveySnapshot[] = surveys.map((s) => ({
    id: s.id,
    title: s.title,
    status: s.status,
    responseCount: completedBySurvey.get(s.id) ?? 0,
  }));

  const byDay = new Map(dayKeys.map((k) => [k, 0]));
  for (const row of dayRows) {
    const key =
      typeof row.day === "string"
        ? row.day.slice(0, 10)
        : localDateKey(new Date(row.day));
    if (byDay.has(key)) {
      byDay.set(key, Number(row.count) || 0);
    }
  }

  const quotaLimit = Math.max(workspace.responseQuotaLimit, 1);

  return {
    totalViews,
    responses: completedCount,
    completionRate: calcCompletionRate(completedCount, totalViews),
    avgTimeToComplete: formatDuration(avgSeconds),
    responseQuotaUsed: workspace.responseQuotaUsed,
    responseQuotaLimit: workspace.responseQuotaLimit,
    planTier: workspace.planTier,
    surveyTotal: surveys.length,
    surveyActive,
    surveyDraft,
    surveyClosed,
    inProgress,
    abandoned,
    viewsDeltaPct: pctChange(viewsThis, viewsPrev),
    responsesDeltaPct: pctChange(respThis, respPrev),
    quotaPercent: Math.min(
      100,
      (workspace.responseQuotaUsed / quotaLimit) * 100
    ),
    responsesOverTime: dayKeys.map((date) => ({
      date,
      count: byDay.get(date) ?? 0,
    })),
    statusBreakdown: [
      { name: "Active", value: surveyActive },
      { name: "Draft", value: surveyDraft },
      { name: "Closed", value: surveyClosed },
    ].filter((row) => row.value > 0),
    topSurveys: [...snapshots]
      .sort((a, b) => b.responseCount - a.responseCount)
      .slice(0, 5),
    activeSurveys: snapshots.filter((s) => s.status === "active").slice(0, 6),
  };
}

/** Load workspace for sidebar display. */
export async function getWorkspaceById(workspaceId: string) {
  const { Workspace } = initDb();
  return Workspace.findByPk(workspaceId);
}