/**
 * lib/sql-discovery.ts — Discover the live navigation skeleton (apps/modules/
 * sections/resources), permissions and role-permission grants from a generated
 * SmartStack app's database.
 *
 * The nav tables (core.nav_*) and auth tables (core.auth_*) are PACKAGE-OWNED:
 * present at RUNTIME in the DB but absent from the app's source — so SQL is the
 * authoritative source of the navigation skeleton (plan D2/F4). Sub-sets that are
 * NOT in the DB (edit/create/list/detail) come from reading the generated code.
 *
 * Parsing is PURE and unit-tested; only discover*() shell out via sqlcmd.
 */
import type { ParsedConnection } from './appsettings.js';
import { runSqlcmd, parsePipeRows, type SqlcmdOptions } from './sqlcmd.js';

export type NavKind = 'application' | 'module' | 'section' | 'resource';

export interface NavNode {
  kind: NavKind;
  id: string;
  code: string;
  label: string;
  /** Parent node id ('' for applications). */
  parentId: string;
  route: string;
  /** Applications only: personal-workspace flag. */
  isPersonal: boolean;
}

export interface NavPermission {
  id: string;
  path: string;
  action: string;
  level: string;
  isWildcard: boolean;
}

export interface RolePermissionGrant {
  roleId: string;
  roleName: string;
  category: string;
  isSystem: boolean;
  permissionId: string;
  path: string;
  isWildcard: boolean;
}

const TYPE_TO_KIND: Record<string, NavKind | undefined> = {
  APP: 'application',
  MOD: 'module',
  SEC: 'section',
  RES: 'resource',
};

/** 4-level nav UNION — emits `TYPE|Id|Code|Label|ParentId|Route|Flag` rows (active only). */
export const NAV_DISCOVERY_SQL = `SET NOCOUNT ON;
SELECT 'APP|' + CONVERT(NVARCHAR(50), Id) + '|' + Code + '|' + ISNULL(Label,'') + '||' + ISNULL(Route,'') + '|' + CAST(IsPersonal AS NVARCHAR(1)) FROM core.nav_Applications WHERE IsActive = 1
UNION ALL
SELECT 'MOD|' + CONVERT(NVARCHAR(50), Id) + '|' + Code + '|' + ISNULL(Label,'') + '|' + CONVERT(NVARCHAR(50), ApplicationId) + '|' + ISNULL(Route,'') + '|0' FROM core.nav_Modules WHERE IsActive = 1
UNION ALL
SELECT 'SEC|' + CONVERT(NVARCHAR(50), Id) + '|' + Code + '|' + ISNULL(Label,'') + '|' + CONVERT(NVARCHAR(50), ModuleId) + '|' + ISNULL(Route,'') + '|0' FROM core.nav_Sections WHERE IsActive = 1
UNION ALL
SELECT 'RES|' + CONVERT(NVARCHAR(50), Id) + '|' + Code + '|' + ISNULL(Label,'') + '|' + CONVERT(NVARCHAR(50), SectionId) + '|' + ISNULL(Route,'') + '|0' FROM core.nav_Resources WHERE IsActive = 1`;

/** Permissions — emits `Id|Path|Action|Level|IsWildcard` rows. */
export const PERMISSIONS_SQL = `SET NOCOUNT ON;
SELECT CONCAT(CONVERT(NVARCHAR(50), p.Id), '|', p.Path, '|', ISNULL(p.Action,''), '|', p.Level, '|', CASE WHEN p.IsWildcard = 1 THEN '1' ELSE '0' END)
FROM core.nav_Permissions p ORDER BY p.Path`;

/** Role grants — emits `RoleId|Name|Category|IsSystem|PermId|Path|IsWildcard` rows. */
export const ROLE_PERMISSIONS_SQL = `SET NOCOUNT ON;
SELECT CONCAT(CONVERT(NVARCHAR(50), r.Id), '|', r.Name, '|', ISNULL(r.Category,''), '|', CASE WHEN r.IsSystem = 1 THEN '1' ELSE '0' END, '|', CONVERT(NVARCHAR(50), p.Id), '|', p.Path, '|', CASE WHEN p.IsWildcard = 1 THEN '1' ELSE '0' END)
FROM core.auth_Roles r
INNER JOIN core.auth_RolePermissions rp ON rp.RoleId = r.Id
INNER JOIN core.nav_Permissions p ON p.Id = rp.PermissionId
ORDER BY r.Name, p.Path`;

/**
 * Full role catalogue — emits `Id|Name|Code` per row. Unlike ROLE_PERMISSIONS_SQL
 * (an inner join that hides zero-grant roles), this lists EVERY role so a role with
 * no grants is still tested (expected: denied everywhere). The Id is the ONLY
 * stable join key across the SQL and API surfaces: Name is a display field the API
 * serves LOCALIZED (Accept-Language, fallback fr), and Code is NULL on most
 * platform roles (only generated/extension roles carry one).
 */
export const ROLE_CATALOG_SQL = `SET NOCOUNT ON;
SELECT CONCAT(CONVERT(NVARCHAR(50), r.Id), '|', r.Name, '|', ISNULL(r.Code,'')) FROM core.auth_Roles r ORDER BY r.Name`;

/** Parse `TYPE|Id|Code|Label|ParentId|Route|Flag` rows into nav nodes. PURE. */
export function parseNavRows(rows: string[][]): NavNode[] {
  const nodes: NavNode[] = [];
  for (const cells of rows) {
    const kind = TYPE_TO_KIND[cells[0]];
    if (!kind || cells.length < 7) continue;
    nodes.push({
      kind,
      id: cells[1],
      code: cells[2],
      label: cells[3],
      parentId: cells[4],
      route: cells[5],
      isPersonal: kind === 'application' && cells[6] === '1',
    });
  }
  return nodes;
}

export interface RoleCatalogRow {
  id: string;
  name: string;
  /** Technical key when the role carries one (extension roles do; NULL on most platform roles). */
  code?: string;
}

/** Parse `Id|Name|Code` rows into catalogue rows (empty Code → undefined). PURE. */
export function parseRoleCatalogRows(rows: string[][]): RoleCatalogRow[] {
  const out: RoleCatalogRow[] = [];
  for (const cells of rows) {
    if (cells.length < 2 || !cells[0] || !cells[1]) continue;
    out.push({ id: cells[0], name: cells[1], ...(cells[2] ? { code: cells[2] } : {}) });
  }
  return out;
}

/** Parse `Id|Path|Action|Level|IsWildcard` rows into permissions. PURE. */
export function parsePermissionRows(rows: string[][]): NavPermission[] {
  const out: NavPermission[] = [];
  for (const cells of rows) {
    if (cells.length < 5) continue;
    out.push({ id: cells[0], path: cells[1], action: cells[2], level: cells[3], isWildcard: cells[4] === '1' });
  }
  return out;
}

/** Parse `RoleId|Name|Category|IsSystem|PermId|Path|IsWildcard` rows into grants. PURE. */
export function parseRolePermissionRows(rows: string[][]): RolePermissionGrant[] {
  const out: RolePermissionGrant[] = [];
  for (const cells of rows) {
    if (cells.length < 7) continue;
    out.push({
      roleId: cells[0],
      roleName: cells[1],
      category: cells[2],
      isSystem: cells[3] === '1',
      permissionId: cells[4],
      path: cells[5],
      isWildcard: cells[6] === '1',
    });
  }
  return out;
}

export interface CorruptedName {
  source: 'nav' | 'role' | 'permission' | 'grant';
  value: string;
}

/**
 * Find U+FFFD (the Unicode replacement character) in discovered names. U+FFFD marks
 * a byte the decoder THREW AWAY (wrong code page on the sqlcmd stdout) — it is not
 * recoverable, and any downstream match on such a value fails under another name
 * ("role not found") three phases later. Callers must fail loudly instead. PURE.
 */
export function findReplacementCharCorruption(input: {
  nav?: readonly NavNode[];
  roleNames?: readonly string[];
  permissions?: readonly NavPermission[];
  grants?: readonly RolePermissionGrant[];
}): CorruptedName[] {
  const out: CorruptedName[] = [];
  const hit = (s: string) => s.includes('�');
  for (const n of input.nav ?? []) {
    if (hit(n.label) || hit(n.code)) out.push({ source: 'nav', value: n.label || n.code });
  }
  for (const name of input.roleNames ?? []) {
    if (hit(name)) out.push({ source: 'role', value: name });
  }
  for (const p of input.permissions ?? []) {
    if (hit(p.path)) out.push({ source: 'permission', value: p.path });
  }
  for (const g of input.grants ?? []) {
    if (hit(g.roleName)) out.push({ source: 'grant', value: g.roleName });
  }
  return out;
}

export interface NavTreeNode extends NavNode {
  children: NavTreeNode[];
}

/** Nest flat nav nodes by parentId into a forest rooted at applications. PURE. */
export function buildNavTree(nodes: NavNode[]): NavTreeNode[] {
  const byId = new Map<string, NavTreeNode>();
  for (const n of nodes) byId.set(n.id, { ...n, children: [] });
  const roots: NavTreeNode[] = [];
  for (const node of byId.values()) {
    const parent = node.parentId ? byId.get(node.parentId) : undefined;
    if (node.kind === 'application' || !parent) {
      roots.push(node);
    } else {
      parent.children.push(node);
    }
  }
  return roots;
}

/**
 * Group role grants into `{ roleName -> [{ path, isWildcard }] }`, the exact shape
 * rbac-matrix.buildAccessMap consumes (kept structural so this shared lib does not
 * depend on the /uat-specific rbac-matrix module).
 */
export function groupGrantsByRole(
  grants: RolePermissionGrant[],
): Record<string, { path: string; isWildcard: boolean }[]> {
  const out: Record<string, { path: string; isWildcard: boolean }[]> = {};
  for (const g of grants) {
    (out[g.roleName] ??= []).push({ path: g.path, isWildcard: g.isWildcard });
  }
  return out;
}

/** Discover the nav skeleton (I/O). */
export function discoverNav(conn: ParsedConnection, opts?: SqlcmdOptions): NavNode[] {
  return parseNavRows(parsePipeRows(runSqlcmd(conn, NAV_DISCOVERY_SQL, opts)));
}

/** Discover all permissions (I/O). */
export function discoverPermissions(conn: ParsedConnection, opts?: SqlcmdOptions): NavPermission[] {
  return parsePermissionRows(parsePipeRows(runSqlcmd(conn, PERMISSIONS_SQL, opts)));
}

/** Discover role-permission grants (I/O). */
export function discoverRolePermissions(conn: ParsedConnection, opts?: SqlcmdOptions): RolePermissionGrant[] {
  return parseRolePermissionRows(parsePipeRows(runSqlcmd(conn, ROLE_PERMISSIONS_SQL, opts)));
}

/** Discover the FULL role catalogue (id/name/code), zero-grant roles included (I/O). */
export function discoverRoleCatalog(conn: ParsedConnection, opts?: SqlcmdOptions): RoleCatalogRow[] {
  return parseRoleCatalogRows(parsePipeRows(runSqlcmd(conn, ROLE_CATALOG_SQL, opts)));
}