import { execute, queryAll, queryOne } from './db';
import type { Env } from './env';

const SUB_STATUS = new Set(['pending', 'subscribed', 'unsubscribed']);

interface SubscriberRow {
  id: string;
  party_id: string;
  status: string;
  created_at: string;
  updated_at: string;
}

interface SubscriptionRow {
  id: string;
  subscriber_id: string;
  list_id: string;
  status: string;
  source: string | null;
  consent: string | null;
  created_at: string;
  updated_at: string;
  confirmed_at: string | null;
  unsubscribed_at: string | null;
}

interface ListRow {
  id: string;
  slug: string;
  name: string;
  created_at: string;
}

function jsonError(status: number, message: string): Response {
  return Response.json({ ok: false, error: message }, { status });
}

function requireDb(env: Env): D1Database | Response {
  if (!env.DB) return jsonError(503, 'Database not configured');
  return env.DB;
}

async function readJson<T>(request: Request): Promise<T | null> {
  try {
    return (await request.json()) as T;
  } catch {
    return null;
  }
}

async function ensureList(db: D1Database, slug: string): Promise<ListRow> {
  const existing = await queryOne<ListRow>(db, 'SELECT * FROM newsletter_lists WHERE slug = ?', [slug]);
  if (existing) return existing;
  const id = crypto.randomUUID();
  await execute(db, 'INSERT INTO newsletter_lists (id, slug, name) VALUES (?, ?, ?)', [id, slug, slug.toUpperCase()]);
  return (await queryOne<ListRow>(db, 'SELECT * FROM newsletter_lists WHERE id = ?', [id])) as ListRow;
}

async function ensureParty(db: D1Database, email: string): Promise<{ partyId: string } | Response> {
  const party = await queryOne<{ id: string }>(
    db,
    `SELECT parties.id as id
     FROM parties
     JOIN party_emails ON party_emails.party_id = parties.id
     WHERE party_emails.email = ?
     LIMIT 1`,
    [email]
  );
  if (party?.id) return { partyId: party.id };

  const partyId = crypto.randomUUID();
  const emailId = crypto.randomUUID();
  await execute(db, 'INSERT INTO parties (id, kind, name) VALUES (?, ?, ?)', [partyId, 'person', email]);
  await execute(db, 'INSERT INTO party_emails (id, party_id, email, is_primary) VALUES (?, ?, ?, 1)', [
    emailId,
    partyId,
    email
  ]);
  return { partyId };
}

async function ensureSubscriber(db: D1Database, partyId: string): Promise<SubscriberRow> {
  const existing = await queryOne<SubscriberRow>(
    db,
    'SELECT * FROM newsletter_subscribers WHERE party_id = ?',
    [partyId]
  );
  if (existing) return existing;
  const id = crypto.randomUUID();
  await execute(db, 'INSERT INTO newsletter_subscribers (id, party_id, status) VALUES (?, ?, ?)', [
    id,
    partyId,
    'pending'
  ]);
  return (await queryOne<SubscriberRow>(db, 'SELECT * FROM newsletter_subscribers WHERE id = ?', [id])) as SubscriberRow;
}

async function ensureSubscription({
  db,
  subscriberId,
  listId,
  source,
  consent
}: {
  db: D1Database;
  subscriberId: string;
  listId: string;
  source?: string;
  consent?: Record<string, unknown>;
}): Promise<SubscriptionRow> {
  const existing = await queryOne<SubscriptionRow>(
    db,
    'SELECT * FROM newsletter_subscriptions WHERE subscriber_id = ? AND list_id = ?',
    [subscriberId, listId]
  );
  const now = new Date().toISOString();
  const consentJson = consent ? JSON.stringify(consent) : null;

  if (existing) {
    await execute(
      db,
      `UPDATE newsletter_subscriptions
       SET status = 'pending', source = ?, consent = ?, updated_at = ?
       WHERE id = ?`,
      [source ?? existing.source ?? null, consentJson ?? existing.consent ?? null, now, existing.id]
    );
    return (await queryOne<SubscriptionRow>(db, 'SELECT * FROM newsletter_subscriptions WHERE id = ?', [
      existing.id
    ])) as SubscriptionRow;
  }

  const id = crypto.randomUUID();
  await execute(
    db,
    `INSERT INTO newsletter_subscriptions (id, subscriber_id, list_id, status, source, consent)
     VALUES (?, ?, ?, 'pending', ?, ?)`,
    [id, subscriberId, listId, source ?? null, consentJson]
  );
  return (await queryOne<SubscriptionRow>(db, 'SELECT * FROM newsletter_subscriptions WHERE id = ?', [id])) as SubscriptionRow;
}

async function createToken({
  db,
  subscriberId,
  listId,
  kind,
  ttlHours
}: {
  db: D1Database;
  subscriberId: string;
  listId: string;
  kind: string;
  ttlHours: number;
}): Promise<string> {
  const token = crypto.randomUUID().replace(/-/g, '');
  const expires = new Date(Date.now() + ttlHours * 60 * 60 * 1000).toISOString();
  await execute(
    db,
    'INSERT INTO newsletter_tokens (token, subscriber_id, list_id, kind, expires_at) VALUES (?, ?, ?, ?, ?)',
    [token, subscriberId, listId, kind, expires]
  );
  return token;
}

async function consumeToken({
  db,
  token,
  kind
}: {
  db: D1Database;
  token: string;
  kind: string;
}): Promise<{ subscriberId: string; listId: string } | null> {
  const record = await queryOne<{ subscriber_id: string; list_id: string; expires_at: string; consumed_at: string | null }>(
    db,
    'SELECT subscriber_id, list_id, expires_at, consumed_at FROM newsletter_tokens WHERE token = ? AND kind = ?',
    [token, kind]
  );
  if (!record || record.consumed_at) return null;
  if (record.expires_at < new Date().toISOString()) return null;
  await execute(db, 'UPDATE newsletter_tokens SET consumed_at = ? WHERE token = ?', [new Date().toISOString(), token]);
  return { subscriberId: record.subscriber_id, listId: record.list_id };
}

export async function handleNewsletter(request: Request, env: Env): Promise<Response | null> {
  const url = new URL(request.url);
  const parts = url.pathname.split('/').filter(Boolean);
  if (parts[0] !== 'api' || parts[1] !== 'newsletter') return null;

  const dbOrResponse = requireDb(env);
  if (dbOrResponse instanceof Response) return dbOrResponse;
  const db = dbOrResponse;

  if (parts.length === 2 && request.method === 'GET') {
    const subscribers = await queryAll<SubscriberRow>(
      db,
      `SELECT newsletter_subscribers.*, party_emails.email as email
       FROM newsletter_subscribers
       JOIN party_emails ON party_emails.party_id = newsletter_subscribers.party_id
       WHERE party_emails.is_primary = 1
       ORDER BY newsletter_subscribers.created_at DESC
       LIMIT 200`
    );
    return Response.json({ ok: true, subscribers });
  }

  if (parts[2] === 'lists' && request.method === 'GET') {
    const lists = await queryAll<ListRow>(db, 'SELECT * FROM newsletter_lists ORDER BY created_at ASC');
    return Response.json({ ok: true, lists });
  }

  if (parts[2] === 'subscribe' && request.method === 'POST') {
    const body = await readJson<{
      email?: string;
      list?: string;
      source?: string;
      consent?: Record<string, unknown>;
      cfTurnstileResponse?: string;
    }>(request);

    if (!body?.email) return jsonError(400, 'Email required');

    // Optional Turnstile verification (only when secret key is configured)
    if (env.TURNSTILE_SECRET_KEY && body.cfTurnstileResponse) {
      const formData = new FormData();
      formData.append('secret', env.TURNSTILE_SECRET_KEY);
      formData.append('response', body.cfTurnstileResponse);
      const verifyRes = await fetch('https://challenges.cloudflare.com/turnstile/v0/siteverify', {
        method: 'POST',
        body: formData,
      });
      const verifyData = await verifyRes.json() as { success: boolean };
      if (!verifyData.success) return jsonError(400, 'Verification failed');
    }

    const listSlug = (body.list ?? 'cultscale').toLowerCase();

    const party = await ensureParty(db, body.email.trim().toLowerCase());
    if (party instanceof Response) return party;

    const subscriber = await ensureSubscriber(db, party.partyId);
    const list = await ensureList(db, listSlug);
    const subscription = await ensureSubscription({
      db,
      subscriberId: subscriber.id,
      listId: list.id,
      source: body.source,
      consent: body.consent
    });

    const token = await createToken({ db, subscriberId: subscriber.id, listId: list.id, kind: 'confirm', ttlHours: 48 });
    return Response.json({
      ok: true,
      status: subscription.status,
      confirmToken: token
    });
  }

  if (parts[2] === 'confirm' && request.method === 'POST') {
    const body = await readJson<{ token?: string }>(request);
    if (!body?.token) return jsonError(400, 'Token required');
    const record = await consumeToken({ db, token: body.token, kind: 'confirm' });
    if (!record) return jsonError(400, 'Invalid or expired token');

    await execute(
      db,
      `UPDATE newsletter_subscriptions
       SET status = 'subscribed', confirmed_at = ?, updated_at = ?
       WHERE subscriber_id = ? AND list_id = ?`,
      [new Date().toISOString(), new Date().toISOString(), record.subscriberId, record.listId]
    );

    await execute(
      db,
      `UPDATE newsletter_subscribers
       SET status = 'subscribed', updated_at = ?
       WHERE id = ?`,
      [new Date().toISOString(), record.subscriberId]
    );

    return Response.json({ ok: true });
  }

  if (parts[2] === 'unsubscribe' && request.method === 'POST') {
    const body = await readJson<{ token?: string }>(request);
    if (!body?.token) return jsonError(400, 'Token required');
    const record = await consumeToken({ db, token: body.token, kind: 'unsubscribe' });
    if (!record) return jsonError(400, 'Invalid or expired token');

    await execute(
      db,
      `UPDATE newsletter_subscriptions
       SET status = 'unsubscribed', unsubscribed_at = ?, updated_at = ?
       WHERE subscriber_id = ? AND list_id = ?`,
      [new Date().toISOString(), new Date().toISOString(), record.subscriberId, record.listId]
    );

    await execute(
      db,
      `UPDATE newsletter_subscribers
       SET status = 'unsubscribed', updated_at = ?
       WHERE id = ?`,
      [new Date().toISOString(), record.subscriberId]
    );

    return Response.json({ ok: true });
  }

  if (parts[2] === 'subscriptions' && request.method === 'GET') {
    const subs = await queryAll<SubscriptionRow>(
      db,
      `SELECT newsletter_subscriptions.*, newsletter_lists.slug as list_slug
       FROM newsletter_subscriptions
       JOIN newsletter_lists ON newsletter_lists.id = newsletter_subscriptions.list_id
       ORDER BY newsletter_subscriptions.created_at DESC
       LIMIT 200`
    );
    return Response.json({ ok: true, subscriptions: subs });
  }

  return jsonError(404, 'Not Found');
}