import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { NotFoundError, ValidationError } from '@server/errors';
import { bindValue } from '@server/db/bind';

export interface TicketFilters {
  restaurantId: string;
  status?: string;
  priority?: string;
  search?: string;
  page?: number;
  limit?: number;
}

export async function listTickets(filters: TicketFilters) {
  await initDatabase();
  const { restaurantId, status, priority, search, page = 1, limit = 20 } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];

  if (status) conditions.push(sql`status = ${status}`);
  if (priority) conditions.push(sql`priority = ${priority}`);
  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(subject ILIKE ${pattern} OR category ILIKE ${pattern})`);
  }

  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT COUNT(*) FROM support_tickets WHERE ... */
  const { rows: countRows } = await db.execute(sql`SELECT COUNT(*) FROM support_tickets WHERE ${where}`);
  const total = parseInt((countRows[0] as { count: string })?.count ?? '0', 10);

  /* raw: SELECT * FROM support_tickets WHERE ... ORDER BY created_at DESC LIMIT ... OFFSET ... */
  const { rows } = await db.execute(sql`SELECT * FROM support_tickets WHERE ${where} ORDER BY created_at DESC LIMIT ${limit} OFFSET ${offset}`);
  return { data: rows, total, page, limit, pages: Math.ceil(total / limit) };
}

export async function getTicket(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT * FROM support_tickets WHERE id = $1 AND restaurant_id = $2 */
  const { rows } = await db.execute(sql`SELECT * FROM support_tickets WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if (!rows[0]) throw new NotFoundError('Support ticket');
  return rows[0];
}

export async function createTicket(restaurantId: string, userId: string, data: Record<string, unknown>) {
  await initDatabase();
  /* raw: INSERT INTO support_tickets (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO support_tickets (restaurant_id, user_id, subject, category, priority, description, status)
    VALUES (${restaurantId}, ${userId}, ${data.subject as string}, ${(data.category as string) ?? 'Other'},
            ${(data.priority as string) ?? 'medium'}, ${(data.description as string) ?? null}, ${'open'})
    RETURNING *
  `);
  return rows[0];
}

export async function updateTicket(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = ['subject','category','priority','status','description'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    sets.push(sql`${sql.raw(k)} = ${bindValue(v)}`);
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');
  sets.push(sql`updated_at = NOW()`);

  /* raw: UPDATE support_tickets SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE support_tickets SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Support ticket');
  return rows[0];
}

export async function appendTicketMessage(
  id: string,
  restaurantId: string,
  message: { role: string; content: string; timestamp?: string }
) {
  await initDatabase();
  const newMsg = { ...message, timestamp: message.timestamp ?? new Date().toISOString() };
  /* raw: UPDATE support_tickets SET messages = messages || $1::jsonb, updated_at = NOW() WHERE id = $2 AND restaurant_id = $3 RETURNING * */
  const { rows } = await db.execute(sql`
    UPDATE support_tickets SET messages = messages || ${JSON.stringify([newMsg])}::jsonb, updated_at = NOW()
    WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *
  `);
  if (!rows[0]) throw new NotFoundError('Support ticket');
  return rows[0];
}

export async function deleteTicket(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: DELETE FROM support_tickets WHERE id = $1 AND restaurant_id = $2 */
  const result = await db.execute(sql`DELETE FROM support_tickets WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Support ticket');
}