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

export const SECTIONS = [
  'dashboard',
  'orders', 'reservations',
  'ai_config', 'knowledge_base', 'chat_widget', 'conversations',
  'telephone', 'whatsapp',
  'menu', 'customers', 'loyalty', 'storefront',
  'marketing', 'gift_cards', 'coupons',
  'staff', 'branches', 'analytics', 'settings',
] as const;
export type Section = typeof SECTIONS[number];

export interface CrudPerms {
  create: boolean;
  read: boolean;
  update: boolean;
  delete: boolean;
}

export type PermissionsMap = Record<Section, CrudPerms>;

interface RoleRow {
  id: string;
  restaurant_id: string;
  name: string;
  description: string | null;
  permissions: PermissionsMap;
  is_system: boolean;
  created_at: string;
  updated_at: string;
}

export function defaultPermissionsMap(allFalse = true): PermissionsMap {
  return Object.fromEntries(
    SECTIONS.map(s => [s, { create: !allFalse, read: !allFalse, update: !allFalse, delete: !allFalse }])
  ) as PermissionsMap;
}

export interface RoleFilters {
  restaurantId: string;
}

export async function listRoles(filters: RoleFilters) {
  await initDatabase();
  /* raw: SELECT * FROM roles WHERE restaurant_id = $1 ORDER BY is_system DESC, name ASC */
  const { rows } = await db.execute(sql`SELECT * FROM roles WHERE restaurant_id = ${filters.restaurantId} ORDER BY is_system DESC, name ASC`);
  return rows as unknown as RoleRow[];
}

export async function getRole(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT * FROM roles WHERE id = $1 AND restaurant_id = $2 */
  const { rows } = await db.execute(sql`SELECT * FROM roles WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  const row = rows[0] as unknown as RoleRow | undefined;
  if (!row) throw new NotFoundError('Role');
  return row;
}

export async function createRole(restaurantId: string, data: { name: string; description?: string; permissions: PermissionsMap }) {
  await initDatabase();
  if (!data.name?.trim()) throw new ValidationError('Role name is required');
  /* raw: INSERT INTO roles (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO roles (restaurant_id, name, description, permissions, is_system)
    VALUES (${restaurantId}, ${data.name.trim()}, ${data.description ?? null}, ${JSON.stringify(data.permissions)}::jsonb, false)
    RETURNING *
  `);
  return rows[0] as unknown as RoleRow;
}

export async function updateRole(id: string, restaurantId: string, data: { name?: string; description?: string; permissions?: PermissionsMap }) {
  await initDatabase();
  /* raw: SELECT * FROM roles WHERE id = $1 AND restaurant_id = $2 */
  const { rows: existingRows } = await db.execute(sql`SELECT * FROM roles WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  const existing = existingRows[0] as unknown as RoleRow | undefined;
  if (!existing) throw new NotFoundError('Role');
  if (existing.is_system && existing.name === 'Owner') {
    throw new ForbiddenError('The Owner role cannot be modified');
  }

  const sets: SQL[] = [];
  if (data.name !== undefined) sets.push(sql`name = ${bindValue(data.name.trim())}`);
  if (data.description !== undefined) sets.push(sql`description = ${bindValue(data.description ?? null)}`);
  if (data.permissions !== undefined) sets.push(sql`permissions = ${bindValue(JSON.stringify(data.permissions))}::jsonb`);

  if (sets.length === 0) throw new ValidationError('No valid fields to update');

  /* Wrap the role update + optional staff cascade in a transaction so they stay in sync */
  const row = await db.transaction(async (tx) => {
    /* raw: UPDATE roles SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
    const { rows } = await tx.execute(sql`UPDATE roles SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
    const updated = rows[0] as unknown as RoleRow | undefined;
    if (!updated) throw new NotFoundError('Role');

    /* Cascade permission changes to all staff assigned this role */
    if (data.permissions !== undefined) {
      /* raw: UPDATE staff SET permissions = $1 WHERE role_id = $2 AND restaurant_id = $3 */
      await tx.execute(sql`UPDATE staff SET permissions = ${bindValue(JSON.stringify(data.permissions))}::jsonb WHERE role_id = ${id} AND restaurant_id = ${restaurantId}`);
    }

    return updated;
  });

  return row;
}

export async function deleteRole(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT * FROM roles WHERE id = $1 AND restaurant_id = $2 */
  const { rows: existingRows } = await db.execute(sql`SELECT * FROM roles WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  const existing = existingRows[0] as unknown as RoleRow | undefined;
  if (!existing) throw new NotFoundError('Role');
  if (existing.is_system) throw new ForbiddenError('System roles cannot be deleted');

  /* raw: UPDATE staff SET role_id = NULL WHERE role_id = $1 AND restaurant_id = $2 */
  await db.execute(sql`UPDATE staff SET role_id = NULL WHERE role_id = ${id} AND restaurant_id = ${restaurantId}`);
  /* raw: DELETE FROM roles WHERE id = $1 AND restaurant_id = $2 */
  const result = await db.execute(sql`DELETE FROM roles WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Role');
}