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

export interface StaffFilters {
  restaurantId: string;
  branchId?: string;
  role?: string;
  isActive?: boolean;
  search?: string;
  page?: number;
  limit?: number;
}

export async function listStaff(filters: StaffFilters) {
  await initDatabase();
  const { restaurantId, branchId, role, isActive, search, page = 1, limit = 20 } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];

  if (branchId) conditions.push(sql`branch_id = ${branchId}`);
  if (role) conditions.push(sql`role = ${role}`);
  if (isActive !== undefined) conditions.push(sql`is_active = ${isActive}`);
  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(name ILIKE ${pattern} OR email ILIKE ${pattern})`);
  }

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

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

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

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

export async function createStaffMember(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  if (data.email) {
    /* raw: SELECT id FROM staff WHERE restaurant_id = $1 AND email = $2 */
    const { rows: existing } = await db.execute(sql`SELECT id FROM staff WHERE restaurant_id = ${restaurantId} AND email = ${data.email as string}`);
    if (existing[0]) throw new ConflictError('A staff member with this email already exists');
  }
  if (data.role_id) {
    /* raw: SELECT id, permissions FROM roles WHERE id = $1 AND restaurant_id = $2 */
    const { rows: roleRows } = await db.execute(sql`SELECT id, permissions FROM roles WHERE id = ${data.role_id as string} AND restaurant_id = ${restaurantId}`);
    if (!roleRows[0]) throw new ForbiddenError('Role does not belong to this restaurant');
    /* Sync role permissions to the new staff member unless caller explicitly provided permissions */
    if (data.permissions === undefined) {
      data = { ...data, permissions: (roleRows[0] as Record<string, unknown>).permissions };
    }
  }
  /* raw: INSERT INTO staff (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO staff (restaurant_id, branch_id, name, email, role, role_id, permissions, is_active)
    VALUES (${restaurantId}, ${(data.branch_id as string) ?? null}, ${data.name as string}, ${(data.email as string) ?? null},
            ${(data.role as string) ?? 'staff'}, ${(data.role_id as string) ?? null},
            ${JSON.stringify(data.permissions ?? {})}::jsonb, ${data.is_active ?? true})
    RETURNING *
  `);
  const row = rows[0];
  if (row) dispatchWebhook(restaurantId, 'staff.created', row as Record<string, unknown>);
  return row;
}

export async function updateStaffMember(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();

  /* When a role is being assigned, fetch its permissions and sync them */
  if (data.role_id) {
    /* raw: SELECT id, permissions FROM roles WHERE id = $1 AND restaurant_id = $2 */
    const { rows: roleRows } = await db.execute(sql`SELECT id, permissions FROM roles WHERE id = ${data.role_id as string} AND restaurant_id = ${restaurantId}`);
    if (!roleRows[0]) throw new ForbiddenError('Role does not belong to this restaurant');
    /* Override permissions with the role's permissions unless caller explicitly provided them */
    if (data.permissions === undefined) {
      data = { ...data, permissions: (roleRows[0] as Record<string, unknown>).permissions };
    }
  }

  const allowed = ['name','email','role','role_id','permissions','is_active','branch_id','last_active_at'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    const val = k === 'permissions' ? JSON.stringify(v) : v;
    sets.push(sql`${sql.raw(k)} = ${bindValue(val)}`);
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');

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

export async function deactivateStaffMember(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: UPDATE staff SET is_active = false WHERE id = $1 AND restaurant_id = $2 RETURNING * */
  const { rows } = await db.execute(sql`UPDATE staff SET is_active = false WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Staff member');
  return rows[0];
}

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