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

export const dynamic = "force-dynamic";

const createLearnerState = `CREATE TABLE IF NOT EXISTS learner_state (
  email TEXT PRIMARY KEY,
  display_name TEXT NOT NULL,
  pathway TEXT NOT NULL DEFAULT 'Project management',
  project_started INTEGER NOT NULL DEFAULT 0,
  completed_lessons INTEGER NOT NULL DEFAULT 3,
  portfolio_published INTEGER NOT NULL DEFAULT 0,
  organisation_name TEXT NOT NULL DEFAULT 'ConnectPoint Learning',
  accent_colour TEXT NOT NULL DEFAULT '#08a9df',
  organisation_logo_key TEXT,
  organisation_logo_name TEXT,
  active_role TEXT NOT NULL DEFAULT 'learner',
  updated_at TEXT NOT NULL
)`;

const createEvidence = `CREATE TABLE IF NOT EXISTS evidence (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_email TEXT NOT NULL,
  title TEXT NOT NULL,
  skill TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'growing',
  created_at TEXT NOT NULL
)`;

const createMembers = `CREATE TABLE IF NOT EXISTS organisation_members (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT,
  role TEXT NOT NULL DEFAULT 'learner',
  status TEXT NOT NULL DEFAULT 'invited',
  invited_by TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
)`;

const createPlatformSettings = `CREATE TABLE IF NOT EXISTS platform_settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT NOT NULL
)`;

async function ensureSchema() {
  await env.DB.batch([
    env.DB.prepare(createLearnerState),
    env.DB.prepare(createEvidence),
    env.DB.prepare(createMembers),
    env.DB.prepare(createPlatformSettings),
  ]);
}

async function currentUser() {
  const user = await getChatGPTUser();
  return user;
}

export async function GET() {
  await ensureSchema();
  const user = await currentUser();
  if (!user) return Response.json({ error: "Sign in is required." }, { status: 401 });
  const now = new Date().toISOString();
  await env.DB.prepare(
    `INSERT OR IGNORE INTO learner_state
      (email, display_name, pathway, project_started, completed_lessons, portfolio_published, organisation_name, accent_colour, updated_at)
     VALUES (?, ?, 'Project management', 0, 3, 0, 'ConnectPoint Learning', '#08a9df', ?)`
  ).bind(user.email, user.displayName, now).run();
  await env.DB.prepare(
    "INSERT OR IGNORE INTO platform_settings (key, value, updated_at) VALUES ('owner_email', ?, ?)"
  ).bind(user.email.toLowerCase(), now).run();
  const owner = await env.DB.prepare(
    "SELECT value FROM platform_settings WHERE key='owner_email'"
  ).first<{ value: string }>();
  if (owner?.value.toLowerCase() === user.email.toLowerCase()) {
    await env.DB.prepare(
      "UPDATE learner_state SET active_role='admin', display_name=?, updated_at=? WHERE email=?"
    ).bind(user.displayName, now, user.email).run();
  }
  const membership = await env.DB.prepare(
    "SELECT role FROM organisation_members WHERE lower(email)=lower(?) AND status IN ('invited','active')"
  ).bind(user.email).first<{ role: string }>();
  if (membership && owner?.value.toLowerCase() !== user.email.toLowerCase()) {
    await env.DB.batch([
      env.DB.prepare("UPDATE organisation_members SET status='active', display_name=?, updated_at=? WHERE lower(email)=lower(?)")
        .bind(user.displayName, now, user.email),
      env.DB.prepare("UPDATE learner_state SET active_role=?, display_name=?, updated_at=? WHERE email=?")
        .bind(membership.role, user.displayName, now, user.email),
    ]);
  }

  const state = await env.DB.prepare(
    `SELECT email, display_name AS displayName, pathway, project_started AS projectStarted,
      completed_lessons AS completedLessons, portfolio_published AS portfolioPublished,
      organisation_name AS organisationName, accent_colour AS accentColour,
      organisation_logo_name AS organisationLogoName, active_role AS activeRole,
      updated_at AS updatedAt
     FROM learner_state WHERE email = ?`
  ).bind(user.email).first();

  const evidence = await env.DB.prepare(
    `SELECT id, title, skill, status, created_at AS createdAt
     FROM evidence WHERE owner_email = ? ORDER BY id DESC`
  ).bind(user.email).all();

  return Response.json({ state, evidence: evidence.results });
}

export async function POST(request: Request) {
  await ensureSchema();
  const user = await currentUser();
  if (!user) return Response.json({ error: "Sign in is required." }, { status: 401 });
  const body = await request.json() as Record<string, unknown>;
  const allowed = {
    pathway: typeof body.pathway === "string" ? body.pathway.slice(0, 100) : undefined,
    projectStarted: typeof body.projectStarted === "boolean" ? Number(body.projectStarted) : undefined,
    completedLessons: typeof body.completedLessons === "number" ? Math.max(0, Math.min(5, Math.round(body.completedLessons))) : undefined,
    portfolioPublished: typeof body.portfolioPublished === "boolean" ? Number(body.portfolioPublished) : undefined,
    organisationName: typeof body.organisationName === "string" ? body.organisationName.slice(0, 80) : undefined,
    accentColour: typeof body.accentColour === "string" && /^#[0-9a-f]{6}$/i.test(body.accentColour) ? body.accentColour : undefined,
  };

  const columns: Record<string, string> = {
    pathway: "pathway",
    projectStarted: "project_started",
    completedLessons: "completed_lessons",
    portfolioPublished: "portfolio_published",
    organisationName: "organisation_name",
    accentColour: "accent_colour",
  };
  const entries = Object.entries(allowed).filter((entry) => entry[1] !== undefined);
  if (!entries.length) return Response.json({ error: "No valid changes supplied" }, { status: 400 });

  const assignments = entries.map(([key]) => `${columns[key]} = ?`).join(", ");
  await env.DB.prepare(`UPDATE learner_state SET ${assignments}, updated_at = ? WHERE email = ?`)
    .bind(...entries.map((entry) => entry[1]), new Date().toISOString(), user.email).run();

  if (body.projectStarted === true) {
    await env.DB.prepare(
      `INSERT INTO evidence (owner_email, title, skill, status, created_at)
       SELECT ?, 'Community pop-up event plan', 'Project planning', 'building', ?
       WHERE NOT EXISTS (SELECT 1 FROM evidence WHERE owner_email = ? AND title = 'Community pop-up event plan')`
    ).bind(user.email, new Date().toISOString(), user.email).run();
  }

  return GET();
}
