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

export const dynamic = "force-dynamic";

const modulesSql = `CREATE TABLE IF NOT EXISTS course_modules (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  position INTEGER NOT NULL
)`;

const lessonsSql = `CREATE TABLE IF NOT EXISTS course_lessons (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  module_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  lesson_type TEXT NOT NULL DEFAULT 'video',
  summary TEXT NOT NULL,
  media_key TEXT,
  media_name TEXT,
  media_url TEXT,
  duration_minutes INTEGER NOT NULL DEFAULT 5,
  position INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft',
  created_at TEXT NOT NULL
)`;

async function ensureSchema() {
  await env.DB.batch([env.DB.prepare(modulesSql), env.DB.prepare(lessonsSql)]);
}

async function seed() {
  const count = await env.DB.prepare("SELECT COUNT(*) AS count FROM course_modules").first<{ count: number }>();
  if ((count?.count ?? 0) > 0) return;
  await env.DB.batch([
    env.DB.prepare("INSERT INTO course_modules (product_id,title,position) VALUES (1,'Plan the project',1)"),
    env.DB.prepare("INSERT INTO course_modules (product_id,title,position) VALUES (1,'Deliver with confidence',2)"),
  ]);
  const first = await env.DB.prepare("SELECT id FROM course_modules WHERE product_id=1 ORDER BY position LIMIT 1").first<{ id: number }>();
  if (first) {
    const now = new Date().toISOString();
    await env.DB.batch([
      env.DB.prepare(`INSERT INTO course_lessons (module_id,title,lesson_type,summary,duration_minutes,position,status,created_at)
        VALUES (?,?,?,?,?,?,?,?)`).bind(first.id, "Define a successful outcome", "video", "Turn a broad idea into a clear and measurable project outcome.", 7, 1, "published", now),
      env.DB.prepare(`INSERT INTO course_lessons (module_id,title,lesson_type,summary,duration_minutes,position,status,created_at)
        VALUES (?,?,?,?,?,?,?,?)`).bind(first.id, "Map your stakeholders", "activity", "Identify who influences delivery and plan how you will involve them.", 12, 2, "published", now),
    ]);
  }
}

export async function GET() {
  const user = await getAppIdentity();
  if (!user) return unauthorised();
  if (!["creator", "admin"].includes(user.role)) return forbidden();
  await ensureSchema();
  await seed();
  const modules = await env.DB.prepare(
    `SELECT m.id,m.title,m.position,
      COUNT(l.id) AS lessonCount,COALESCE(SUM(l.duration_minutes),0) AS durationMinutes
     FROM course_modules m LEFT JOIN course_lessons l ON l.module_id=m.id
     WHERE m.product_id=1 GROUP BY m.id ORDER BY m.position`
  ).all();
  const lessons = await env.DB.prepare(
    `SELECT l.id,l.module_id AS moduleId,l.title,l.lesson_type AS lessonType,l.summary,
      l.media_name AS mediaName,l.media_url AS mediaUrl,l.duration_minutes AS durationMinutes,l.position,l.status
     FROM course_lessons l JOIN course_modules m ON m.id=l.module_id
     WHERE m.product_id=1 ORDER BY m.position,l.position`
  ).all();
  return Response.json({ modules: modules.results, lessons: lessons.results });
}

export async function POST(request: Request) {
  await ensureSchema();
  const user = await getAppIdentity();
  if (!user) return unauthorised();
  if (!["creator", "admin"].includes(user.role)) return forbidden();
  const form = await request.formData();
  const moduleId = Number(form.get("moduleId"));
  const title = String(form.get("title") ?? "").trim().slice(0, 120);
  const summary = String(form.get("summary") ?? "").trim().slice(0, 1200);
  const lessonType = String(form.get("lessonType") ?? "video");
  const mediaUrlValue = String(form.get("mediaUrl") ?? "").trim().slice(0, 500);
  const durationMinutes = Math.max(1, Math.min(180, Number(form.get("durationMinutes")) || 5));
  const media = form.get("media");
  if (!Number.isInteger(moduleId) || !title || !summary) {
    return Response.json({ error: "Choose a module and add the lesson title and summary." }, { status: 400 });
  }
  const module = await env.DB.prepare("SELECT id FROM course_modules WHERE id=?").bind(moduleId).first();
  if (!module) return Response.json({ error: "Module not found" }, { status: 404 });

  let mediaKey: string | null = null;
  let mediaName: string | null = null;
  let mediaUrl: string | null = null;
  if (mediaUrlValue) {
    try {
      const parsed = new URL(mediaUrlValue);
      if (parsed.protocol !== "https:") throw new Error("Only secure links");
      mediaUrl = parsed.toString();
    } catch {
      return Response.json({ error: "Add a valid HTTPS video link." }, { status: 400 });
    }
  }
  if (media instanceof File && media.size > 0) {
    if (media.size > 50 * 1024 * 1024) return Response.json({ error: "Prototype media uploads must be under 50 MB." }, { status: 400 });
    const allowed = ["video/mp4", "video/webm", "application/pdf", "image/png", "image/jpeg"];
    if (!allowed.includes(media.type)) return Response.json({ error: "Upload MP4, WebM, PDF, PNG or JPG." }, { status: 400 });
    mediaKey = `course-media/${user.email}/${crypto.randomUUID()}-${media.name.replace(/[^a-z0-9._-]/gi, "_")}`;
    mediaName = media.name;
    await env.EVIDENCE_BUCKET.put(mediaKey, media.stream(), {
      httpMetadata: { contentType: media.type },
      customMetadata: { owner: user.email, lessonTitle: title },
    });
  }
  const position = await env.DB.prepare(
    "SELECT COALESCE(MAX(position),0)+1 AS position FROM course_lessons WHERE module_id=?"
  ).bind(moduleId).first<{ position: number }>();
  await env.DB.prepare(
    `INSERT INTO course_lessons
      (module_id,title,lesson_type,summary,media_key,media_name,media_url,duration_minutes,position,status,created_at)
     VALUES (?,?,?,?,?,?,?,?,?,?,?)`
  ).bind(moduleId, title, lessonType, summary, mediaKey, mediaName, mediaUrl, durationMinutes, position?.position ?? 1, "draft", new Date().toISOString()).run();
  return GET();
}
