import { env } from "cloudflare:workers";
import { forbidden, getAppIdentity, unauthorised } from "../../api-auth";

export const dynamic = "force-dynamic";

async function scalar(sql: string) {
  const row = await env.DB.prepare(sql).first<{ value: number | null }>();
  return Number(row?.value ?? 0);
}

function csvCell(value: unknown) {
  return `"${String(value ?? "").replaceAll('"', '""')}"`;
}

export async function GET(request: Request) {
  const identity = await getAppIdentity();
  if (!identity) return unauthorised();
  if (identity.role !== "admin") return forbidden();

  const url = new URL(request.url);
  const period = ["7", "30", "90", "all"].includes(url.searchParams.get("period") ?? "") ? url.searchParams.get("period")! : "30";
  const cutoff = period === "all" ? null : new Date(Date.now() - Number(period) * 86400000).toISOString();
  const learnerActivityFilter = cutoff ? ` WHERE datetime(updated_at) >= datetime('${cutoff}')` : "";
  const projectFilter = cutoff ? ` WHERE datetime(created_at) >= datetime('${cutoff}')` : "";
  const launchFilter = cutoff ? ` WHERE datetime(launched_at) >= datetime('${cutoff}')` : "";
  const launchCompletionFilter = cutoff ? ` WHERE completion_status IN ('completed','passed') AND datetime(launched_at) >= datetime('${cutoff}')` : " WHERE completion_status IN ('completed','passed')";
  const surveyFilter = cutoff ? ` WHERE datetime(created_at) >= datetime('${cutoff}')` : "";

  const [learners, activeLearners, completedLessons, projects, assessedProjects, contentLaunches, completions, feedback, courseRating, confidence] = await Promise.all([
    scalar("SELECT COUNT(*) AS value FROM learner_state"),
    scalar(`SELECT COUNT(*) AS value FROM learner_state${learnerActivityFilter}`),
    scalar("SELECT COALESCE(SUM(completed_lessons),0) AS value FROM learner_state"),
    scalar(`SELECT COUNT(*) AS value FROM project_submissions${projectFilter}`),
    scalar(`SELECT COUNT(*) AS value FROM project_submissions${projectFilter}${projectFilter ? " AND" : " WHERE"} assessed_at IS NOT NULL`),
    scalar(`SELECT COUNT(*) AS value FROM content_launches${launchFilter}`),
    scalar(`SELECT COUNT(*) AS value FROM content_launches${launchCompletionFilter}`),
    scalar(`SELECT COUNT(*) AS value FROM survey_responses${surveyFilter}`),
    scalar(`SELECT ROUND(COALESCE(AVG(rating),0),1) AS value FROM survey_responses${surveyFilter}`),
    scalar(`SELECT ROUND(COALESCE(AVG(confidence),0),1) AS value FROM survey_responses${surveyFilter}`),
  ]);

  const people = await env.DB.prepare(
    `SELECT s.email, s.display_name AS displayName, s.pathway,
      s.completed_lessons AS completedLessons, s.project_started AS projectStarted,
      s.portfolio_published AS portfolioPublished, s.updated_at AS updatedAt,
      COALESCE(m.role, s.active_role, 'learner') AS role,
      COALESCE(m.status, 'active') AS status,
      (SELECT COUNT(*) FROM project_submissions p WHERE p.learner_email=s.email) AS projects,
      (SELECT COUNT(*) FROM content_launches l WHERE l.learner_email=s.email AND l.completion_status IN ('completed','passed')) AS completions
     FROM learner_state s
     LEFT JOIN organisation_members m ON lower(m.email)=lower(s.email)
     ORDER BY datetime(s.updated_at) DESC LIMIT 12`
  ).all();

  const completionRate = contentLaunches ? Math.round((completions / contentLaunches) * 100) : 0;
  const projectAssessmentRate = projects ? Math.round((assessedProjects / projects) * 100) : 0;

  const summary = { learners, activeLearners, completedLessons, projects, assessedProjects, contentLaunches, completions, completionRate, projectAssessmentRate, feedback, courseRating, confidence };

  if (url.searchParams.get("format") === "csv") {
    const periodLabel = period === "all" ? "All time" : `Last ${period} days`;
    const rows = [
      ["ConnectPoint Learning Impact Report"],
      ["Reporting period", periodLabel],
      ["Generated", new Date().toISOString()],
      [],
      ["Metric", "Value"],
      ["Total learners", learners],
      ["Active learners", activeLearners],
      ["Lessons completed", completedLessons],
      ["Content launches", contentLaunches],
      ["Content completions", completions],
      ["Completion rate", `${completionRate}%`],
      ["Projects submitted", projects],
      ["Projects assessed", assessedProjects],
      ["Assessment rate", `${projectAssessmentRate}%`],
      ["Feedback responses", feedback],
      ["Average course rating", courseRating],
      ["Average learner confidence", confidence],
      [],
      ["Learner", "Email", "Pathway", "Lessons completed", "Projects", "Content completions", "Portfolio", "Status"],
      ...people.results.map((person) => {
        const p = person as Record<string, unknown>;
        return [p.displayName, p.email, p.pathway, p.completedLessons, p.projects, p.completions, p.portfolioPublished ? "Published" : "Not published", p.status];
      }),
    ];
    const csv = rows.map((row) => row.map(csvCell).join(",")).join("\r\n");
    return new Response(`\uFEFF${csv}`, {
      headers: {
        "content-type": "text/csv; charset=utf-8",
        "content-disposition": `attachment; filename="connectpoint-learning-report-${new Date().toISOString().slice(0, 10)}.csv"`,
      },
    });
  }

  return Response.json({ summary, people: people.results, period });
}
