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

export const dynamic = "force-dynamic";

const productsSql = `CREATE TABLE IF NOT EXISTS course_products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  creator_email TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  category TEXT NOT NULL,
  outcome TEXT NOT NULL,
  project_title TEXT NOT NULL,
  lesson_count INTEGER NOT NULL DEFAULT 6,
  price_pence INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'published',
  created_at TEXT NOT NULL
)`;

const enrolmentsSql = `CREATE TABLE IF NOT EXISTS enrolments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  learner_email TEXT NOT NULL,
  product_id INTEGER NOT NULL,
  amount_pence INTEGER NOT NULL,
  payment_status TEXT NOT NULL DEFAULT 'demo',
  progress INTEGER NOT NULL DEFAULT 0,
  enrolled_at TEXT NOT NULL
)`;

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

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

async function seedProducts(creator: string) {
  const found = await env.DB.prepare("SELECT COUNT(*) AS count FROM course_products").first<{ count: number }>();
  if ((found?.count ?? 0) > 0) return;
  const now = new Date().toISOString();
  await env.DB.batch([
    env.DB.prepare(`INSERT INTO course_products
      (creator_email,title,description,category,outcome,project_title,lesson_count,price_pence,status,created_at)
      VALUES (?,?,?,?,?,?,?,?,?,?)`).bind(creator, "Community Project Management", "Plan, budget and deliver a real community initiative.", "Business", "Deliver a stakeholder-ready project plan", "Community pop-up event", 8, 7900, "published", now),
    env.DB.prepare(`INSERT INTO course_products
      (creator_email,title,description,category,outcome,project_title,lesson_count,price_pence,status,created_at)
      VALUES (?,?,?,?,?,?,?,?,?,?)`).bind(creator, "Digital Marketing Foundations", "Build a practical campaign from audience insight to results.", "Marketing", "Launch a measurable digital campaign", "30-day campaign plan", 6, 5900, "published", now),
    env.DB.prepare(`INSERT INTO course_products
      (creator_email,title,description,category,outcome,project_title,lesson_count,price_pence,status,created_at)
      VALUES (?,?,?,?,?,?,?,?,?,?)`).bind(creator, "Creative Portfolio Sprint", "Turn your best work into a clear professional story.", "Creative", "Publish a focused professional portfolio", "Three-project portfolio", 5, 3900, "published", now),
  ]);
}

export async function GET() {
  await ensureSchema();
  const user = await identity();
  await seedProducts(user.email);
  const products = await env.DB.prepare(
    `SELECT p.id,p.title,p.description,p.category,p.outcome,p.project_title AS projectTitle,
      p.lesson_count AS lessonCount,p.price_pence AS pricePence,p.status,
      CASE WHEN e.id IS NULL THEN 0 ELSE 1 END AS enrolled
     FROM course_products p
     LEFT JOIN enrolments e ON e.product_id=p.id AND e.learner_email=?
     WHERE p.status='published' ORDER BY p.id`
  ).bind(user.email).all();
  const metrics = await env.DB.prepare(
    `SELECT COUNT(*) AS sales, COALESCE(SUM(amount_pence),0) AS revenuePence,
      COUNT(DISTINCT learner_email) AS learners FROM enrolments`
  ).first();
  return Response.json({ products: products.results, metrics });
}

export async function POST(request: Request) {
  await ensureSchema();
  const user = await identity();
  const body = await request.json() as Record<string, unknown>;
  if (body.action === "enrol") {
    const productId = Number(body.productId);
    const product = await env.DB.prepare(
      "SELECT id,price_pence AS pricePence FROM course_products WHERE id=? AND status='published'"
    ).bind(productId).first<{ id: number; pricePence: number }>();
    if (!product) return Response.json({ error: "Course not found" }, { status: 404 });
    await env.DB.prepare(
      `INSERT INTO enrolments (learner_email,product_id,amount_pence,payment_status,progress,enrolled_at)
       SELECT ?,?,?,'demo',0,? WHERE NOT EXISTS
       (SELECT 1 FROM enrolments WHERE learner_email=? AND product_id=?)`
    ).bind(user.email, product.id, product.pricePence, new Date().toISOString(), user.email, product.id).run();
    return GET();
  }
  return Response.json({ error: "Unsupported action" }, { status: 400 });
}
