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

export const dynamic = "force-dynamic";

const programmeSql = `CREATE TABLE IF NOT EXISTS programmes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_email TEXT NOT NULL,
  title TEXT NOT NULL,
  audience TEXT NOT NULL,
  skill TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft',
  created_at TEXT NOT NULL
)`;

const attemptSql = `CREATE TABLE IF NOT EXISTS assessment_attempts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  learner_email TEXT NOT NULL,
  programme_id INTEGER NOT NULL,
  answer TEXT NOT NULL,
  score INTEGER NOT NULL,
  feedback TEXT NOT NULL,
  created_at TEXT NOT NULL
)`;

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

async function seed(ownerEmail: string) {
  await env.DB.prepare(
    `INSERT INTO programmes (owner_email, title, audience, skill, status, created_at)
     SELECT ?, 'Community Project Management', 'Early-career project coordinators', 'Project planning', 'published', ?
     WHERE NOT EXISTS (SELECT 1 FROM programmes WHERE owner_email = ?)`
  ).bind(ownerEmail, new Date().toISOString(), ownerEmail).run();
}

export async function GET() {
  await ensureSchema();
  const user = await getAppIdentity();
  if (!user) return unauthorised();
  await seed(user.email);
  const programmes = await env.DB.prepare(
    `SELECT id, title, audience, skill, status, created_at AS createdAt
     FROM programmes WHERE owner_email = ? ORDER BY id DESC`
  ).bind(user.email).all();
  const attempts = await env.DB.prepare(
    `SELECT id, programme_id AS programmeId, answer, score, feedback, created_at AS createdAt
     FROM assessment_attempts WHERE learner_email = ? ORDER BY id DESC LIMIT 5`
  ).bind(user.email).all();
  return Response.json({ programmes: programmes.results, attempts: attempts.results });
}

export async function POST(request: Request) {
  await ensureSchema();
  const user = await getAppIdentity();
  if (!user) return unauthorised();
  const body = await request.json() as Record<string, unknown>;

  if (body.action === "assess") {
    if (!["learner", "admin"].includes(user.role)) return forbidden();
    const answer = typeof body.answer === "string" ? body.answer.slice(0, 200) : "";
    const correct = answer === "Set aside a contingency reserve";
    const feedback = correct
      ? "Strong decision. A contingency reserve protects delivery when costs change."
      : "Review budget risk: protect essential delivery by planning a contingency before reducing outcomes.";
    await env.DB.prepare(
      `INSERT INTO assessment_attempts (learner_email, programme_id, answer, score, feedback, created_at)
       VALUES (?, 1, ?, ?, ?, ?)`
    ).bind(user.email, answer, correct ? 100 : 40, feedback, new Date().toISOString()).run();
    return Response.json({ score: correct ? 100 : 40, feedback });
  }

  if (user.role !== "admin") return forbidden();
  const title = typeof body.title === "string" ? body.title.trim().slice(0, 100) : "";
  const audience = typeof body.audience === "string" ? body.audience.trim().slice(0, 140) : "";
  const skill = typeof body.skill === "string" ? body.skill.trim().slice(0, 80) : "";
  if (!title || !audience || !skill) {
    return Response.json({ error: "Title, audience and skill are required" }, { status: 400 });
  }
  await env.DB.prepare(
    `INSERT INTO programmes (owner_email, title, audience, skill, status, created_at)
     VALUES (?, ?, ?, ?, 'draft', ?)`
  ).bind(user.email, title, audience, skill, new Date().toISOString()).run();
  return GET();
}
