import { env } from "cloudflare:workers";
import { getChatGPTUser } from "../../chatgpt-auth";

export const dynamic = "force-dynamic";

const submissionSql = `CREATE TABLE IF NOT EXISTS project_submissions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  learner_email TEXT NOT NULL,
  project_title TEXT NOT NULL,
  reflection TEXT NOT NULL,
  file_key TEXT,
  file_name TEXT,
  content_type TEXT,
  status TEXT NOT NULL DEFAULT 'submitted',
  score INTEGER,
  rubric_feedback TEXT,
  cv_statement TEXT,
  created_at TEXT NOT NULL,
  assessed_at TEXT
)`;

async function identity() {
  return await getChatGPTUser() ?? { email: "prototype@connectpoint.net", displayName: "Maya Patel" };
}

async function ensureSchema() {
  await env.DB.prepare(submissionSql).run();
}

export async function GET() {
  await ensureSchema();
  const user = await identity();
  const submissions = await env.DB.prepare(
    `SELECT id, project_title AS projectTitle, reflection, file_name AS fileName,
      status, score, rubric_feedback AS rubricFeedback, cv_statement AS cvStatement,
      created_at AS createdAt, assessed_at AS assessedAt
     FROM project_submissions WHERE learner_email = ? ORDER BY id DESC`
  ).bind(user.email).all();
  return Response.json({ submissions: submissions.results });
}

export async function POST(request: Request) {
  await ensureSchema();
  const user = await identity();
  const contentType = request.headers.get("content-type") ?? "";

  if (contentType.includes("application/json")) {
    const body = await request.json() as { action?: string; id?: number };
    if (body.action !== "assess" || !Number.isInteger(body.id)) {
      return Response.json({ error: "Invalid assessment request" }, { status: 400 });
    }
    const submission = await env.DB.prepare(
      `SELECT id, project_title AS projectTitle, reflection, file_name AS fileName
       FROM project_submissions WHERE id = ? AND learner_email = ?`
    ).bind(body.id, user.email).first<{ id: number; projectTitle: string; reflection: string; fileName: string | null }>();
    if (!submission) return Response.json({ error: "Submission not found" }, { status: 404 });

    const evidenceScore = submission.fileName ? 35 : 15;
    const reflectionScore = Math.min(35, Math.max(15, Math.round(submission.reflection.length / 12)));
    const outcomeScore = /outcome|result|delivered|improved|completed/i.test(submission.reflection) ? 30 : 18;
    const score = Math.min(100, evidenceScore + reflectionScore + outcomeScore);
    const feedback = score >= 80
      ? "Strong evidence: the submission connects planning decisions, delivery and outcomes. Add one quantified result to make the proof exceptional."
      : "Good foundation. Strengthen the evidence by naming the result, your personal decisions and one measurable outcome.";
    const cvStatement = `Planned and delivered ${submission.projectTitle.toLowerCase()}, applying project planning, budgeting and stakeholder communication to produce a documented outcome.`;
    await env.DB.prepare(
      `UPDATE project_submissions SET status = 'verified', score = ?, rubric_feedback = ?,
       cv_statement = ?, assessed_at = ? WHERE id = ? AND learner_email = ?`
    ).bind(score, feedback, cvStatement, new Date().toISOString(), submission.id, user.email).run();
    return GET();
  }

  const form = await request.formData();
  const projectTitle = String(form.get("projectTitle") ?? "").trim().slice(0, 120);
  const reflection = String(form.get("reflection") ?? "").trim().slice(0, 3000);
  const file = form.get("file");
  if (!projectTitle || reflection.length < 40) {
    return Response.json({ error: "Add a project title and a reflection of at least 40 characters." }, { status: 400 });
  }

  let fileKey: string | null = null;
  let fileName: string | null = null;
  let uploadType: string | null = null;
  if (file instanceof File && file.size > 0) {
    if (file.size > 10 * 1024 * 1024) return Response.json({ error: "Evidence files must be under 10 MB." }, { status: 400 });
    const allowed = ["application/pdf", "image/png", "image/jpeg", "text/plain"];
    if (!allowed.includes(file.type)) return Response.json({ error: "Upload a PDF, PNG, JPG or text file." }, { status: 400 });
    fileKey = `${user.email}/${crypto.randomUUID()}-${file.name.replace(/[^a-z0-9._-]/gi, "_")}`;
    fileName = file.name;
    uploadType = file.type;
    await env.EVIDENCE_BUCKET.put(fileKey, file.stream(), {
      httpMetadata: { contentType: file.type },
      customMetadata: { owner: user.email, projectTitle },
    });
  }

  await env.DB.prepare(
    `INSERT INTO project_submissions
      (learner_email, project_title, reflection, file_key, file_name, content_type, status, created_at)
     VALUES (?, ?, ?, ?, ?, ?, 'submitted', ?)`
  ).bind(user.email, projectTitle, reflection, fileKey, fileName, uploadType, new Date().toISOString()).run();
  return GET();
}
