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

export interface NotificationFilters {
  restaurantId: string;
  userId?: string;
  isRead?: boolean;
  type?: string;
  page?: number;
  limit?: number;
}

export async function listNotifications(filters: NotificationFilters) {
  await initDatabase();
  const { restaurantId, userId, isRead, type, page = 1, limit = 30 } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];

  if (userId) conditions.push(sql`user_id = ${userId}`);
  if (isRead !== undefined) conditions.push(sql`is_read = ${isRead}`);
  if (type) conditions.push(sql`type = ${type}`);

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

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

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

export async function markNotificationRead(id: string, restaurantId: string, userId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`id = ${id}`, sql`restaurant_id = ${restaurantId}`];
  if (userId) conditions.push(sql`user_id = ${userId}`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: UPDATE notifications SET is_read = true WHERE ... RETURNING * */
  const { rows } = await db.execute(sql`UPDATE notifications SET is_read = true WHERE ${where} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Notification');
  return rows[0];
}

export async function markAllNotificationsRead(restaurantId: string, userId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`, sql`is_read = false`];
  if (userId) conditions.push(sql`user_id = ${userId}`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: UPDATE notifications SET is_read = true WHERE ... */
  const result = await db.execute(sql`UPDATE notifications SET is_read = true WHERE ${where}`);
  return { updated: result.rowCount ?? 0 };
}

export async function createNotification(
  restaurantId: string,
  data: {
    type?: string;
    title: string;
    message?: string;
    userId?: string;
    actionLabel?: string;
    actionHref?: string;
  }
) {
  await initDatabase();
  /* raw: INSERT INTO notifications (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO notifications (restaurant_id, user_id, type, title, message, action_label, action_href)
    VALUES (${restaurantId}, ${data.userId ?? null}, ${data.type ?? 'system'},
            ${data.title}, ${data.message ?? null}, ${data.actionLabel ?? null}, ${data.actionHref ?? null})
    RETURNING *
  `);
  return rows[0];
}

export async function deleteNotification(id: string, restaurantId: string, userId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`id = ${id}`, sql`restaurant_id = ${restaurantId}`];
  if (userId) conditions.push(sql`user_id = ${userId}`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: DELETE FROM notifications WHERE ... */
  const result = await db.execute(sql`DELETE FROM notifications WHERE ${where}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Notification');
}