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

export const dynamic = "force-dynamic";

const schema = `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
)`;

async function ensureSchema() {
  await env.DB.batch([
    env.DB.prepare(schema),
    env.DB.prepare("CREATE INDEX IF NOT EXISTS organisation_members_status_idx ON organisation_members(status)"),
  ]);
}

async function requireAdmin() {
  const identity = await getAppIdentity();
  if (!identity) return { response: unauthorised() };
  if (identity.role !== "admin") return { response: forbidden() };
  return { identity };
}

async function listMembers() {
  const result = await env.DB.prepare(
    `SELECT id, email, display_name AS displayName, role, status,
      invited_by AS invitedBy, created_at AS createdAt, updated_at AS updatedAt
     FROM organisation_members ORDER BY CASE status WHEN 'active' THEN 0 WHEN 'invited' THEN 1 ELSE 2 END, email`
  ).all();
  return result.results;
}

export async function GET() {
  await ensureSchema();
  const auth = await requireAdmin();
  if (auth.response) return auth.response;
  return Response.json({ members: await listMembers() });
}

export async function POST(request: Request) {
  await ensureSchema();
  const auth = await requireAdmin();
  if (auth.response || !auth.identity) return auth.response!;
  const body = await request.json() as Record<string, unknown>;
  const action = body.action === "update" ? "update" : "invite";
  const roles: AppRole[] = ["learner", "creator", "admin"];
  const statuses = ["invited", "active", "suspended"];

  if (action === "invite") {
    const email = typeof body.email === "string" ? body.email.trim().toLowerCase() : "";
    const role = roles.includes(body.role as AppRole) ? body.role as AppRole : "learner";
    if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
      return Response.json({ error: "Enter a valid email address." }, { status: 400 });
    }
    const now = new Date().toISOString();
    await env.DB.prepare(
      `INSERT INTO organisation_members (email, role, status, invited_by, created_at, updated_at)
       VALUES (?, ?, 'invited', ?, ?, ?)
       ON CONFLICT(email) DO UPDATE SET role=excluded.role, status='invited', invited_by=excluded.invited_by, updated_at=excluded.updated_at`
    ).bind(email, role, auth.identity.email, now, now).run();
  } else {
    const id = Number(body.id);
    const role = roles.includes(body.role as AppRole) ? body.role as AppRole : null;
    const status = statuses.includes(String(body.status)) ? String(body.status) : null;
    if (!Number.isInteger(id) || (!role && !status)) {
      return Response.json({ error: "Choose a valid member and access change." }, { status: 400 });
    }
    const fields: string[] = [];
    const values: unknown[] = [];
    if (role) { fields.push("role=?"); values.push(role); }
    if (status) { fields.push("status=?"); values.push(status); }
    fields.push("updated_at=?"); values.push(new Date().toISOString(), id);
    await env.DB.prepare(`UPDATE organisation_members SET ${fields.join(",")} WHERE id=?`).bind(...values).run();
  }

  return Response.json({ members: await listMembers() });
}
