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

export const dynamic = "force-dynamic";

const contentSql = `CREATE TABLE IF NOT EXISTS training_content (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_email TEXT NOT NULL,
  title TEXT NOT NULL,
  format TEXT NOT NULL,
  launch_url TEXT,
  package_key TEXT,
  package_name TEXT,
  launch_path TEXT,
  status TEXT NOT NULL DEFAULT 'ready',
  created_at TEXT NOT NULL
)`;
const launchesSql = `CREATE TABLE IF NOT EXISTS content_launches (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content_id INTEGER NOT NULL,
  learner_email TEXT NOT NULL,
  completion_status TEXT NOT NULL DEFAULT 'launched',
  score INTEGER,
  total_seconds INTEGER NOT NULL DEFAULT 0,
  launched_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
)`;
const surveysSql = `CREATE TABLE IF NOT EXISTS survey_responses (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  learner_email TEXT NOT NULL,
  course_title TEXT NOT NULL,
  rating INTEGER NOT NULL,
  confidence INTEGER NOT NULL,
  comment TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL
)`;

async function ensureSchema() {
  await env.DB.batch([env.DB.prepare(contentSql), env.DB.prepare(launchesSql), env.DB.prepare(surveysSql)]);
}

async function listContent() {
  const items = await env.DB.prepare(
    `SELECT c.id,c.title,c.format,c.launch_url AS launchUrl,c.launch_path AS launchPath,c.package_name AS packageName,c.status,c.created_at AS createdAt,
      COUNT(l.id) AS launches,MAX(l.score) AS bestScore,
      MAX(CASE WHEN l.completion_status IN ('completed','passed') THEN 1 ELSE 0 END) AS completed,
      COALESCE(SUM(l.total_seconds),0) AS totalSeconds
     FROM training_content c LEFT JOIN content_launches l ON l.content_id=c.id
     GROUP BY c.id ORDER BY c.id DESC`
  ).all();
  const survey = await env.DB.prepare(
    `SELECT COUNT(*) AS responses,ROUND(AVG(rating),1) AS averageRating,ROUND(AVG(confidence),1) AS averageConfidence
     FROM survey_responses`
  ).first();
  return Response.json({ items: items.results, survey });
}

export async function GET(request: Request) {
  await ensureSchema();
  const user = await getAppIdentity();
  if (!user) return unauthorised();
  const id = Number(new URL(request.url).searchParams.get("package"));
  if (Number.isInteger(id) && id > 0) {
    const item = await env.DB.prepare(
      "SELECT package_key AS packageKey,package_name AS packageName FROM training_content WHERE id=?"
    ).bind(id).first<{ packageKey: string | null; packageName: string | null }>();
    if (!item?.packageKey) return new Response(null, { status: 404 });
    const object = await env.EVIDENCE_BUCKET.get(item.packageKey);
    if (!object) return new Response(null, { status: 404 });
    return new Response(object.body, {
      headers: {
        "content-type": "application/zip",
        "content-disposition": `attachment; filename="${(item.packageName ?? "course.zip").replace(/"/g, "")}"`,
      },
    });
  }
  return listContent();
}

export async function POST(request: Request) {
  await ensureSchema();
  const user = await getAppIdentity();
  if (!user) return unauthorised();
  const contentType = request.headers.get("content-type") ?? "";
  if (contentType.includes("multipart/form-data")) {
    if (!["creator", "admin"].includes(user.role)) return forbidden();
    const form = await request.formData();
    const title = String(form.get("title") ?? "").trim().slice(0, 120);
    const format = String(form.get("format") ?? "");
    const launchUrlValue = String(form.get("launchUrl") ?? "").trim().slice(0, 1000);
    const file = form.get("package");
    if (!title || !["scorm12", "scorm2004", "aicc"].includes(format)) {
      return Response.json({ error: "Add a title and choose a supported format." }, { status: 400 });
    }
    let launchUrl: string | null = null;
    let packageKey: string | null = null;
    let packageName: string | null = null;
    if (format === "aicc") {
      try {
        const parsed = new URL(launchUrlValue);
        if (parsed.protocol !== "https:") throw new Error("Secure URLs only");
        launchUrl = parsed.toString();
      } catch {
        return Response.json({ error: "Add a valid HTTPS AICC launch link." }, { status: 400 });
      }
    } else {
      if (!(file instanceof File) || !file.name.toLowerCase().endsWith(".zip")) {
        return Response.json({ error: "Choose a SCORM ZIP package." }, { status: 400 });
      }
      if (file.size > 25 * 1024 * 1024) {
        return Response.json({ error: "SCORM runtime packages must be under 25 MB in this prototype." }, { status: 400 });
      }
      packageName = file.name;
      const packageId = crypto.randomUUID();
      packageKey = `training-content/${user.email}/${packageId}/source.zip`;
      await env.EVIDENCE_BUCKET.put(packageKey, file.stream(), {
        httpMetadata: { contentType: "application/zip" },
        customMetadata: { owner: user.email, format, title },
      });
      const entries = await unzipScorm(await file.arrayBuffer());
      const manifest = entries.find((entry) => entry.path.toLowerCase().endsWith("imsmanifest.xml"));
      if (!manifest) return Response.json({ error: "The ZIP does not contain an imsmanifest.xml file." }, { status: 400 });
      const manifestText = new TextDecoder().decode(manifest.bytes);
      const href = manifestText.match(/<resource\b[^>]*\bhref\s*=\s*["']([^"']+)["']/i)?.[1];
      if (!href) return Response.json({ error: "No launchable resource was found in the SCORM manifest." }, { status: 400 });
      const base = manifest.path.includes("/") ? manifest.path.slice(0, manifest.path.lastIndexOf("/") + 1) : "";
      launchUrl = normalisePath(base + href);
      await Promise.all(entries.map((entry) => env.EVIDENCE_BUCKET.put(
        `training-content/${user.email}/${packageId}/files/${entry.path}`,
        entry.bytes,
        { httpMetadata: { contentType: contentTypeFor(entry.path) }, customMetadata: { owner: user.email, format } },
      )));
    }
    await env.DB.prepare(
      `INSERT INTO training_content (owner_email,title,format,launch_url,package_key,package_name,launch_path,status,created_at)
       VALUES (?,?,?,?,?,?,?,?,?)`
    ).bind(user.email, title, format, format === "aicc" ? launchUrl : null, packageKey, packageName, format === "aicc" ? null : launchUrl, "ready", new Date().toISOString()).run();
    return listContent();
  }

  const body = await request.json() as Record<string, unknown>;
  if (body.action === "launch") {
    if (!["learner", "admin"].includes(user.role)) return forbidden();
    const id = Number(body.id);
    const item = await env.DB.prepare("SELECT id,launch_url AS launchUrl,launch_path AS launchPath,format FROM training_content WHERE id=?")
      .bind(id).first<{ id: number; launchUrl: string | null; launchPath: string | null; format: string }>();
    if (!item) return Response.json({ error: "Content not found." }, { status: 404 });
    await env.DB.prepare(
      "INSERT INTO content_launches (content_id,learner_email,completion_status,total_seconds,launched_at,updated_at) VALUES (?,?,?,?,?,?)"
    ).bind(id, user.email, "launched", 0, new Date().toISOString(), new Date().toISOString()).run();
    return Response.json({ launchUrl: item.format === "aicc" ? item.launchUrl : `/scorm-player?id=${id}`, format: item.format });
  }
  if (body.action === "runtime") {
    if (!["learner", "admin"].includes(user.role)) return forbidden();
    const id = Number(body.id);
    const status = String(body.status ?? "incomplete").slice(0, 30);
    const allowedStatus = ["launched", "incomplete", "completed", "passed", "failed"].includes(status) ? status : "incomplete";
    const score = body.score === null || body.score === undefined || body.score === "" ? null : Math.max(0, Math.min(100, Math.round(Number(body.score))));
    const seconds = Math.max(0, Math.min(86400, Math.round(Number(body.seconds) || 0)));
    await env.DB.prepare(
      `UPDATE content_launches SET completion_status=?,score=?,total_seconds=?,updated_at=?
       WHERE id=(SELECT id FROM content_launches WHERE content_id=? AND learner_email=? ORDER BY id DESC LIMIT 1)`
    ).bind(allowedStatus, score, seconds, new Date().toISOString(), id, user.email).run();
    return Response.json({ saved: true });
  }
  if (body.action === "survey") {
    if (!["learner", "admin"].includes(user.role)) return forbidden();
    const rating = Math.max(1, Math.min(5, Number(body.rating) || 0));
    const confidence = Math.max(1, Math.min(5, Number(body.confidence) || 0));
    if (!rating || !confidence) return Response.json({ error: "Choose both ratings." }, { status: 400 });
    await env.DB.prepare(
      "INSERT INTO survey_responses (learner_email,course_title,rating,confidence,comment,created_at) VALUES (?,?,?,?,?,?)"
    ).bind(user.email, "Community Project Management", rating, confidence, String(body.comment ?? "").slice(0, 1000), new Date().toISOString()).run();
    return Response.json({ saved: true });
  }
  return Response.json({ error: "Unsupported action." }, { status: 400 });
}

type ZipEntry = { path: string; bytes: Uint8Array };

async function unzipScorm(buffer: ArrayBuffer): Promise<ZipEntry[]> {
  const bytes = new Uint8Array(buffer);
  const view = new DataView(buffer);
  let eocd = -1;
  for (let i = bytes.length - 22; i >= Math.max(0, bytes.length - 65557); i--) {
    if (view.getUint32(i, true) === 0x06054b50) { eocd = i; break; }
  }
  if (eocd < 0) throw new Error("Invalid ZIP package");
  const count = view.getUint16(eocd + 10, true);
  let offset = view.getUint32(eocd + 16, true);
  if (count > 600) throw new Error("SCORM package contains too many files");
  const output: ZipEntry[] = [];
  let total = 0;
  for (let index = 0; index < count; index++) {
    if (view.getUint32(offset, true) !== 0x02014b50) throw new Error("Invalid ZIP directory");
    const method = view.getUint16(offset + 10, true);
    const compressedSize = view.getUint32(offset + 20, true);
    const uncompressedSize = view.getUint32(offset + 24, true);
    const nameLength = view.getUint16(offset + 28, true);
    const extraLength = view.getUint16(offset + 30, true);
    const commentLength = view.getUint16(offset + 32, true);
    const localOffset = view.getUint32(offset + 42, true);
    const path = normalisePath(new TextDecoder().decode(bytes.slice(offset + 46, offset + 46 + nameLength)));
    offset += 46 + nameLength + extraLength + commentLength;
    if (!path || path.endsWith("/") || path.includes("..")) continue;
    total += uncompressedSize;
    if (total > 150 * 1024 * 1024) throw new Error("SCORM package expands beyond 150 MB");
    const localNameLength = view.getUint16(localOffset + 26, true);
    const localExtraLength = view.getUint16(localOffset + 28, true);
    const start = localOffset + 30 + localNameLength + localExtraLength;
    const compressed = bytes.slice(start, start + compressedSize);
    let data: Uint8Array;
    if (method === 0) data = compressed;
    else if (method === 8) {
      const stream = new Blob([compressed]).stream().pipeThrough(new DecompressionStream("deflate-raw" as CompressionFormat));
      data = new Uint8Array(await new Response(stream).arrayBuffer());
    } else throw new Error("SCORM package uses an unsupported ZIP compression method");
    output.push({ path, bytes: data });
  }
  return output;
}

function normalisePath(value: string) {
  return value.replace(/\\/g, "/").replace(/^\/+/, "").split("/").filter((part) => part && part !== ".").join("/");
}

function contentTypeFor(path: string) {
  const extension = path.split(".").pop()?.toLowerCase();
  return ({ html: "text/html; charset=utf-8", htm: "text/html; charset=utf-8", js: "text/javascript", css: "text/css", json: "application/json", xml: "application/xml", png: "image/png", jpg: "image/jpeg", jpeg: "image/jpeg", gif: "image/gif", svg: "image/svg+xml", mp4: "video/mp4", mp3: "audio/mpeg", pdf: "application/pdf" } as Record<string, string>)[extension ?? ""] ?? "application/octet-stream";
}
