import {
  db,
  usersTable,
  appsTable,
  servicesTable,
  executiveMeetingFontSettingsTable,
  executiveMeetingPdfArchivesTable,
  rolesTable,
} from "@workspace/db";
import { eq, inArray, sql } from "drizzle-orm";
import { getEffectiveRoleIds } from "../middlewares/auth";
import { getVisibleAppsForUser } from "./appsVisibility";

// Roles that gate read access to the Executive Meetings module.
// Mirrors EXECUTIVE_READ_ROLES in middlewares/auth.ts (kept private
// here to avoid widening that export surface).
const EXECUTIVE_READ_ROLES: ReadonlyArray<string> = [
  "admin",
  "executive_ceo",
  "executive_office_manager",
  "executive_coord_lead",
  "executive_coordinator",
  "executive_viewer",
];

async function getRoleNamesForUser(userId: number): Promise<Set<string>> {
  const ids = await getEffectiveRoleIds(userId);
  if (ids.length === 0) return new Set();
  const rows = await db
    .select({ name: rolesTable.name })
    .from(rolesTable)
    .where(inArray(rolesTable.id, ids));
  return new Set(rows.map((r) => r.name));
}

/**
 * Per-entity authorization for a private object path served by
 * `GET /api/storage/objects/*`. Returns true iff the given user is
 * allowed to read the object; false for orphan objects (never
 * referenced by any known entity) and for users who lack the entity's
 * read role. The route translates `false` into a 404 to avoid leaking
 * which `/objects/<id>` paths exist.
 *
 * Authorization model (per MR-H1 / MR-M7 in `.local/security/manual-review.md`):
 *
 *   admin                                          : allow any REFERENCED
 *                                                    path (orphan paths
 *                                                    still deny — see
 *                                                    fallthrough below)
 *   users.avatar_url == path                       : allow any authenticated user
 *                                                    (avatars are exposed by the
 *                                                    user-directory + @-mention
 *                                                    picker the SPA shows to
 *                                                    every authed user; tightening
 *                                                    here would break those flows)
 *   apps.image_url == path                         : allow only users for whom
 *                                                    the app is visible per
 *                                                    getVisibleAppsForUser
 *                                                    (mirrors GET /apps)
 *   services.image_url == path                     : allow any authenticated user
 *                                                    (`GET /services` is gated
 *                                                    by requireAuth only)
 *   executive_meeting_font_settings.logo_*         : require executive read role
 *   executive_meeting_pdf_archives.file_path       : require executive read role
 *   executive_meetings.attachments contains path   : require executive read role
 *   else (orphan / unknown / forbidden)            : deny
 *
 * Multiple entities may reference the same path. Broader-permission
 * entities (avatar / app icon / service image) are checked first so
 * "public-ish" usage wins; executive-only entities are checked last.
 */
export async function canUserReadObjectPath(
  userId: number,
  objectPath: string,
): Promise<boolean> {
  if (!objectPath.startsWith("/objects/")) return false;

  const roles = await getRoleNamesForUser(userId);
  const isAdmin = roles.has("admin");
  const hasExecutiveAccess =
    isAdmin || EXECUTIVE_READ_ROLES.some((r) => roles.has(r));

  // IMPORTANT: admin is NOT given an early bypass here. The deny-on-
  // orphan guarantee at the bottom of this function MUST apply to
  // every role, otherwise admins could enumerate `/objects/<id>`
  // paths in storage that no entity references (and that therefore
  // have no policy to apply). Each entity branch below grants
  // admin via the role check alongside the entity-specific role
  // requirement.

  // 1. Avatars — visible to anyone authenticated (directory / @-mention).
  const avatar = await db
    .select({ id: usersTable.id })
    .from(usersTable)
    .where(eq(usersTable.avatarUrl, objectPath))
    .limit(1);
  if (avatar.length > 0) return true; // any authed (incl. admin)

  // 2. App icons — gated to apps the caller is allowed to see. Mirrors
  //    the launcher's RBAC: a user who cannot list an app must not be
  //    able to download its custom icon either. Admins see every app
  //    via getVisibleAppsForUser's short-circuit.
  const appRows = await db
    .select({ id: appsTable.id })
    .from(appsTable)
    .where(eq(appsTable.imageUrl, objectPath))
    .limit(1);
  if (appRows.length > 0) {
    const appId = appRows[0]!.id;
    const visible = await getVisibleAppsForUser(userId);
    return visible.some((a) => a.id === appId);
  }

  // 3. Service images — services list is exposed to any authed user.
  const svc = await db
    .select({ id: servicesTable.id })
    .from(servicesTable)
    .where(eq(servicesTable.imageUrl, objectPath))
    .limit(1);
  if (svc.length > 0) return true; // any authed (incl. admin)

  // 4. Executive brand logo — only executive-module roles see the PDF.
  const logo = await db
    .select({ id: executiveMeetingFontSettingsTable.id })
    .from(executiveMeetingFontSettingsTable)
    .where(eq(executiveMeetingFontSettingsTable.logoObjectPath, objectPath))
    .limit(1);
  if (logo.length > 0) return hasExecutiveAccess;

  // 5. Executive PDF archives — only executive-module roles.
  const archive = await db
    .select({ id: executiveMeetingPdfArchivesTable.id })
    .from(executiveMeetingPdfArchivesTable)
    .where(eq(executiveMeetingPdfArchivesTable.filePath, objectPath))
    .limit(1);
  if (archive.length > 0) return hasExecutiveAccess;

  // 6. Meeting attachments — jsonb array of loosely-typed records.
  //    Use jsonb_path_exists with a strict equality predicate so we
  //    only match when some string value (at any depth) is exactly
  //    objectPath. This avoids the substring/prefix false-positives a
  //    LIKE check could produce on serialized JSON.
  const meeting = await db.execute(
    sql`SELECT id FROM executive_meetings
        WHERE jsonb_path_exists(
          attachments,
          '$.** ? (@ == $p)'::jsonpath,
          jsonb_build_object('p', ${objectPath}::text)
        )
        LIMIT 1`,
  );
  const rows = (meeting as unknown as { rows?: unknown[] }).rows ?? [];
  if (rows.length > 0) return hasExecutiveAccess;

  // Orphan path — no entity references it. Treat as not-found for
  // every role (including admin). This is what stops object
  // enumeration: even with admin credentials, you cannot probe
  // `/api/storage/objects/<guess>` and learn whether a file exists.
  return false;
}
