// 2026-11-11 — "Initial audit only" scope for Job Documents users.
//
// Owner request: users who reach Companies ONLY through the Job Documents permission/role
// (e.g. Tehreem Tahir #116) must see only companies that have an INITIAL audit scheduled
// (Audit Requests → Audit Schedule, audit_schedule_rows.audit_type = 'INITIAL', not cancelled),
// and may create jobs / documents only from those initial audits.
//
// Admins and the legacy roles (admin / scheme / coordinator, ADMIN_IDS) are NOT affected —
// they keep seeing every company and every audit. Users without any Job Documents permission
// (e.g. Marketing users that use the companies list in other screens) are NOT affected either.
import { DataSource } from 'typeorm';

export const JOB_DOCS_ADMIN_IDS = [1, 8, 29];
export const JOB_DOCS_LEGACY_ROLES = ['admin', 'superadmin', 'super-admin', 'scheme', 'coordinator'];
export const JOB_DOCS_SLUG = 'job-documents';

/** SQL snippet: company has at least one non-cancelled INITIAL audit in the schedule. */
export const INITIAL_AUDIT_EXISTS_SQL = (companyAlias: string) =>
  `EXISTS (SELECT 1 FROM audit_schedule_rows iar
            WHERE iar.company_id = ${companyAlias}.id
              AND iar.audit_type = 'INITIAL'
              AND iar.status <> 'CANCELLED')`;

/** true → this user must only see INITIAL-audit companies / audits. */
export async function isInitialOnlyUser(db: DataSource, userId: number | null | undefined): Promise<boolean> {
  const uid = Number(userId);
  if (!uid || JOB_DOCS_ADMIN_IDS.includes(uid)) return false;
  try {
    const roles: { r: string }[] = await db.query(
      `SELECT LOWER(r.name) AS r FROM user_roles ur JOIN roles r ON r.id = ur.role_id WHERE ur.user_id = ?`,
      [uid],
    );
    if (roles.some((x) => JOB_DOCS_LEGACY_ROLES.includes(x.r))) return false;
    const perms: any[] = await db.query(
      `SELECT 1 FROM permissions p JOIN modules m ON m.id = p.module_id
         JOIN user_permissions up ON up.permission_id = p.id
        WHERE m.slug = ? AND m.is_active = 1 AND up.user_id = ?
       UNION
       SELECT 1 FROM permissions p JOIN modules m ON m.id = p.module_id
         JOIN role_permissions rp ON rp.permission_id = p.id
         JOIN user_roles ur ON ur.role_id = rp.role_id
         JOIN roles r ON r.id = ur.role_id
        WHERE m.slug = ? AND m.is_active = 1 AND r.is_active = 1 AND ur.user_id = ?
       LIMIT 1`,
      [JOB_DOCS_SLUG, uid, JOB_DOCS_SLUG, uid],
    );
    return perms.length > 0;
  } catch {
    return false; // never lock anyone out because of a lookup error
  }
}