/** Resolve restaurant-facing email recipients (owner email). */
import { db } from '@server/db/drizzle';
import { sql } from 'drizzle-orm';

export async function resolveRestaurantOwnerEmail(restaurantId: string): Promise<string | null> {
  try {
    const { rows } = await db.execute(sql`
      SELECT u.email FROM users u
      JOIN restaurants r ON r.owner_id = u.id
      WHERE r.id = ${restaurantId} LIMIT 1
    `);
    return (rows[0] as { email?: string } | undefined)?.email ?? null;
  } catch {
    return null;
  }
}

/** Returns the owner's user_id and email together, for per-user notification fan-out. */
export async function resolveRestaurantOwnerUser(restaurantId: string): Promise<{ userId: string; email: string } | null> {
  try {
    const { rows } = await db.execute(sql`
      SELECT u.id AS user_id, u.email FROM users u
      JOIN restaurants r ON r.owner_id = u.id
      WHERE r.id = ${restaurantId} LIMIT 1
    `);
    const row = rows[0] as { user_id?: string; email?: string } | undefined;
    if (!row?.user_id || !row?.email) return null;
    return { userId: row.user_id, email: row.email };
  } catch {
    return null;
  }
}

/**
 * Task #298: resolve the active branch managers for an inventory alert.
 * Returns staff rows (with their linked user_id when available) whose role
 * is 'manager' or 'owner' AND who either belong to the supplied branch or
 * are restaurant-wide (branch_id IS NULL). When `branchId` is null we only
 * return restaurant-wide managers — branch-specific managers shouldn't
 * receive alerts for items they don't own.
 *
 * The `user_id` field is used by the in-app notification fan-out so each
 * manager sees the alert in their personal inbox, while the email field
 * drives the email recipients list. Inactive staff are skipped.
 */
export interface BranchManagerRecipient {
  staffId: string;
  userId: string | null;
  email: string | null;
  name: string;
}

export async function resolveBranchManagers(
  restaurantId: string,
  branchId: string | null
): Promise<BranchManagerRecipient[]> {
  try {
    const { rows } = branchId
      ? await db.execute(sql`
          SELECT s.id, s.user_id, s.name, COALESCE(u.email, s.email) AS email
            FROM staff s
            LEFT JOIN users u ON u.id = s.user_id
           WHERE s.restaurant_id = ${restaurantId}
             AND s.is_active = true
             AND (s.role = 'owner' OR s.role = 'manager')
             AND (s.branch_id = ${branchId}::uuid OR s.branch_id IS NULL)
        `)
      : await db.execute(sql`
          SELECT s.id, s.user_id, s.name, COALESCE(u.email, s.email) AS email
            FROM staff s
            LEFT JOIN users u ON u.id = s.user_id
           WHERE s.restaurant_id = ${restaurantId}
             AND s.is_active = true
             AND (s.role = 'owner' OR s.role = 'manager')
             AND s.branch_id IS NULL
        `);
    return (rows as Array<Record<string, unknown>>).map(r => ({
      staffId: r.id as string,
      userId: (r.user_id as string | null) ?? null,
      email: ((r.email as string | null) ?? '').trim() || null,
      name: (r.name as string) ?? '',
    }));
  } catch {
    return [];
  }
}

export async function getRestaurantNotificationMode(restaurantId: string): Promise<'immediate' | 'digest'> {
  try {
    const { rows } = await db.execute(sql`SELECT notification_mode FROM restaurants WHERE id = ${restaurantId} LIMIT 1`);
    const m = (rows[0] as { notification_mode?: string } | undefined)?.notification_mode;
    return m === 'immediate' ? 'immediate' : 'digest';
  } catch {
    return 'digest';
  }
}