/**
 * cli:derive-seed-delta — generate-sql.ts
 *
 * Pure renderers: DeltaPlan → (a) the idempotent SQL delta script committed in
 * the release and applied once at boot by CoreSeedScriptRunner, (b) the
 * markdown summary destined for the PR description.
 *
 * Physical schema (verified against SmartStack.app EF configurations):
 *   core.nav_Applications / nav_Modules / nav_Sections / nav_Resources
 *     — Code, Label, Icon, IconType (int enum: Lucide=0, Url=1, Svg=2),
 *       Route, DisplayOrder, IsActive, UpdatedAt; unique (parent, Code)
 *   core.auth_Roles       — ApplicationId, Code, Name, ShortName, UpdatedAt
 *   core.nav_Permissions  — Path (unique), Action (enum stored as STRING name,
 *                           nullable), SectionId, UpdatedAt
 *   core.auth_RolePermissions — PK (RoleId, PermissionId), AssignedBy (256)
 *
 * Safety rules baked into every statement:
 *   - renames guard against a collision on the target key (NOT EXISTS) and
 *     PRINT when nothing matched (already applied / admin-modified);
 *   - nav removals are DEACTIVATIONS (IsActive=0) — never a physical DELETE;
 *   - permission deletions first remove their mappings (counted, including
 *     admin-granted — the PR reviewer sees it), then the permission;
 *   - revocations only touch seed-owned rows (AssignedBy NULL = legacy seed,
 *     or 'scaffold-core-seed%'), never admin grants;
 *   - role removals are never executed (auth_UserRoles is Restrict) — the
 *     summary reports them for a manual decision.
 */
import { PERMISSION_ACTION_ENUM } from '../../../../../lib/permission-actions.js';
import { SEED_ASSIGNED_BY_PREFIX } from '../scaffold-core-seed/state.js';
import type { CoreSeedStateLevel } from '../scaffold-core-seed/state.js';
import { planHasSqlWork, type DeltaPlan, type FieldChange, type NavKeyRef } from './types.js';

export interface ScriptContext {
  version: string;
  baseRef: string;
}

const ICON_TYPE_VALUES: Record<string, number> = { Lucide: 0, Url: 1, Svg: 2 };

/**
 * action (incl. the `read.all` row-scope tier → Read) → SQL Action literal.
 * Canonical in lib/permission-actions.ts — re-exported here so the repo-side
 * drift test can pin this module to the SSOT.
 */
export const ACTION_NAMES: Record<string, string> = { ...PERMISSION_ACTION_ENUM };

/** Escape a string for a SQL N'...' literal. */
function q(s: string): string {
  return s.replace(/'/g, "''");
}

function lit(s: string): string {
  return `N'${q(s)}'`;
}

// ─── Scoped UPDATE/DELETE fragments per navigation level ─────────────────

const NAV_TABLE: Record<CoreSeedStateLevel, string> = {
  application: '[core].[nav_Applications]',
  module: '[core].[nav_Modules]',
  section: '[core].[nav_Sections]',
  resource: '[core].[nav_Resources]',
};

// ─── Permission FK rebinding (multi-grain, state v2) ─────────────────────

/** nav_Permissions.Level is a STRING-converted enum — the member NAME. */
const PERMISSION_LEVEL_SQL: Record<CoreSeedStateLevel, string> = {
  application: 'Application',
  module: 'Module',
  section: 'Section',
  resource: 'Resource',
};

const PERMISSION_FK_COLUMN: Record<CoreSeedStateLevel, string> = {
  application: 'ApplicationId',
  module: 'ModuleId',
  section: 'SectionId',
  resource: 'ResourceId',
};

/** Scalar subquery resolving the nav node a permission binds, scoped to @appId. */
function permissionNodeLocator(level: CoreSeedStateLevel, nodeCode: string): string {
  switch (level) {
    case 'application':
      return '@appId';
    case 'module':
      return `(SELECT TOP 1 m.[Id] FROM ${NAV_TABLE.module} m WHERE m.[ApplicationId] = @appId AND m.[Code] = ${lit(nodeCode)})`;
    case 'section':
      return `(SELECT TOP 1 s.[Id] FROM ${NAV_TABLE.section} s JOIN ${NAV_TABLE.module} m ON m.[Id] = s.[ModuleId] WHERE m.[ApplicationId] = @appId AND s.[Code] = ${lit(nodeCode)})`;
    case 'resource':
      return `(SELECT TOP 1 r.[Id] FROM ${NAV_TABLE.resource} r JOIN ${NAV_TABLE.section} s ON s.[Id] = r.[SectionId] JOIN ${NAV_TABLE.module} m ON m.[Id] = s.[ModuleId] WHERE m.[ApplicationId] = @appId AND r.[Code] = ${lit(nodeCode)})`;
  }
}

/**
 * SET fragments of a permission rebinding: [Level] + the target grain's FK,
 * with the three OTHER FK columns NULLed — a permission carries exactly one
 * nav FK (platform invariant, one CreateForX factory per row).
 */
function permissionFkSets(level: CoreSeedStateLevel, nodeCode: string): string[] {
  const sets = [`[Level] = ${lit(PERMISSION_LEVEL_SQL[level])}`];
  for (const grain of ['application', 'module', 'section', 'resource'] as CoreSeedStateLevel[]) {
    const col = PERMISSION_FK_COLUMN[grain];
    sets.push(grain === level ? `[${col}] = ${permissionNodeLocator(level, nodeCode)}` : `[${col}] = NULL`);
  }
  return sets;
}

/**
 * FROM + WHERE clause locating one nav row by (level, parentCode, code) inside
 * the app resolved as @appId. Alias of the target row is always `t`.
 */
function navLocator(level: CoreSeedStateLevel, parentCode: string | undefined, code: string): string {
  switch (level) {
    case 'application':
      return `FROM ${NAV_TABLE.application} t\nWHERE t.[Code] = ${lit(code)}`;
    case 'module':
      return `FROM ${NAV_TABLE.module} t\nWHERE t.[ApplicationId] = @appId AND t.[Code] = ${lit(code)}`;
    case 'section':
      return [
        `FROM ${NAV_TABLE.section} t`,
        `JOIN ${NAV_TABLE.module} m ON m.[Id] = t.[ModuleId]`,
        `WHERE m.[ApplicationId] = @appId AND m.[Code] = ${lit(parentCode ?? '')} AND t.[Code] = ${lit(code)}`,
      ].join('\n');
    case 'resource':
      return [
        `FROM ${NAV_TABLE.resource} t`,
        `JOIN ${NAV_TABLE.section} s ON s.[Id] = t.[SectionId]`,
        `JOIN ${NAV_TABLE.module} m ON m.[Id] = s.[ModuleId]`,
        `WHERE m.[ApplicationId] = @appId AND s.[Code] = ${lit(parentCode ?? '')} AND t.[Code] = ${lit(code)}`,
      ].join('\n');
  }
}

/** EXISTS(...) probe for the same scope — used as the rename collision guard. */
function navExistsProbe(level: CoreSeedStateLevel, parentCode: string | undefined, code: string): string {
  const locator = navLocator(level, parentCode, code)
    .split('\n')
    .map((l) => '    ' + l)
    .join('\n');
  return `EXISTS (\n    SELECT 1\n${locator}\n)`;
}

function navPathLabel(level: CoreSeedStateLevel, parentCode: string | undefined, code: string): string {
  return parentCode ? `${level} ${parentCode}/${code}` : `${level} ${code}`;
}

function navSetClause(changes: FieldChange[], warnings: string[]): string[] {
  const sets: string[] = [];
  for (const c of changes) {
    switch (c.field) {
      case 'label':
        sets.push(`t.[Label] = ${lit(String(c.to))}`);
        break;
      case 'icon':
        sets.push(`t.[Icon] = ${lit(String(c.to))}`);
        break;
      case 'iconType': {
        const v = ICON_TYPE_VALUES[String(c.to)];
        if (v === undefined) {
          warnings.push(`unknown IconType '${c.to}' — iconType update skipped`);
        } else {
          sets.push(`t.[IconType] = ${v}`);
        }
        break;
      }
      case 'route':
        sets.push(`t.[Route] = ${lit(String(c.to))}`);
        break;
      case 'displayOrder':
        sets.push(`t.[DisplayOrder] = ${Number(c.to)}`);
        break;
      default:
        break;
    }
  }
  return sets;
}

// ─── Script ──────────────────────────────────────────────────────────────

/**
 * Render the delta script for one application. Returns null when the plan has
 * no change at all. A changed plan ALWAYS yields a script — even an
 * addition-only one (guarded reactivations + an explicit record in
 * core_SeedScriptHistory), which keeps the pr gate rule simple: state hash
 * changed ⇒ a script with matching header hashes must be committed.
 */
export function renderDeltaScript(plan: DeltaPlan, ctx: ScriptContext): string | null {
  if (plan.baseline || plan.baseHash === plan.newHash) return null;

  const warnings: string[] = [];
  const out: string[] = [];

  out.push('-- ============================================================');
  out.push(`-- core-seed delta — app '${plan.app}' — version ${ctx.version}`);
  out.push('-- Generated by derive-seed-delta. REVIEW IN THE RELEASE PR before merge.');
  out.push('-- Applied exactly once at boot by CoreSeedScriptRunner');
  out.push('-- (tracked in extensions.core_SeedScriptHistory, one transaction).');
  out.push(`-- baseRef: ${ctx.baseRef}`);
  out.push(`-- baseHash: ${plan.baseHash}`);
  out.push(`-- newHash: ${plan.newHash}`);
  out.push('-- ============================================================');
  out.push('');

  // Application-level renames come BEFORE @appId resolution (the app row keeps
  // its Id — everything below keys on @appId, so child statements are safe).
  for (const r of plan.navRenames.filter((x) => x.level === 'application')) {
    out.push(`-- rename application '${r.from}' -> '${r.to}' (Id preserved)`);
    out.push(
      `UPDATE ${NAV_TABLE.application} SET [Code] = ${lit(r.to)}, [UpdatedAt] = SYSUTCDATETIME()`,
    );
    out.push(`WHERE [Code] = ${lit(r.from)}`);
    out.push(`  AND NOT EXISTS (SELECT 1 FROM ${NAV_TABLE.application} WHERE [Code] = ${lit(r.to)});`);
    out.push(rowcountPrint(plan.app, `application rename ${r.from} -> ${r.to}`));
    out.push('');
  }

  out.push(
    `DECLARE @appId uniqueidentifier = (SELECT [Id] FROM ${NAV_TABLE.application} WHERE [Code] = ${lit(plan.app)});`,
  );
  out.push('IF @appId IS NULL');
  out.push('BEGIN');
  out.push(`    PRINT 'core-seed delta [${q(plan.app)}]: application not found — nothing to reconcile.';`);
  out.push('    RETURN;');
  out.push('END;');
  out.push('');

  // 1. Navigation renames, top-down (module → section → resource) so child
  // locators reference the parent's NEW code.
  for (const level of ['module', 'section', 'resource'] as CoreSeedStateLevel[]) {
    for (const r of plan.navRenames.filter((x) => x.level === level)) {
      out.push(`-- rename ${navPathLabel(level, r.parentCode, r.from)} -> '${r.to}' (Id + FKs preserved)`);
      out.push(`UPDATE t SET t.[Code] = ${lit(r.to)}, t.[UpdatedAt] = SYSUTCDATETIME()`);
      out.push(navLocator(level, r.parentCode, r.from));
      out.push(`  AND NOT ${navExistsProbe(level, r.parentCode, r.to)};`);
      out.push(rowcountPrint(plan.app, `${level} rename ${r.from} -> ${r.to}`));
      out.push('');
    }
  }

  // 2. Role renames (Code + ShortName track the code by convention).
  for (const r of plan.roleRenames) {
    out.push(`-- rename role '${r.from}' -> '${r.to}' (Id + mappings preserved)`);
    out.push(
      `UPDATE [core].[auth_Roles] SET [Code] = ${lit(r.to)}, [ShortName] = ${lit(r.to)}, [UpdatedAt] = SYSUTCDATETIME()`,
    );
    out.push(`WHERE [ApplicationId] = @appId AND [Code] = ${lit(r.from)}`);
    out.push(
      `  AND NOT EXISTS (SELECT 1 FROM [core].[auth_Roles] WHERE [ApplicationId] = @appId AND [Code] = ${lit(r.to)});`,
    );
    out.push(rowcountPrint(plan.app, `role rename ${r.from} -> ${r.to}`));
    out.push('');
  }

  // 3. Permission renames (Path is globally unique).
  for (const r of plan.permissionRenames) {
    out.push(`-- rename permission '${r.from}' -> '${r.to}' (Id + role mappings preserved)`);
    out.push(`UPDATE [core].[nav_Permissions] SET [Path] = ${lit(r.to)}, [UpdatedAt] = SYSUTCDATETIME()`);
    out.push(`WHERE [Path] = ${lit(r.from)}`);
    out.push(`  AND NOT EXISTS (SELECT 1 FROM [core].[nav_Permissions] WHERE [Path] = ${lit(r.to)});`);
    out.push(rowcountPrint(plan.app, `permission rename ${r.from} -> ${r.to}`));
    out.push('');
  }

  // 4. Navigation property updates (seed wins; admin edits on seed-owned rows
  // are overwritten — visible to the PR reviewer via the comments below).
  for (const u of plan.navUpdates) {
    const sets = navSetClause(u.changes, warnings);
    if (sets.length === 0) continue;
    const detail = u.changes.map((c) => `${c.field}: ${JSON.stringify(c.from)} -> ${JSON.stringify(c.to)}`).join(', ');
    out.push(`-- update ${navPathLabel(u.level, u.parentCode, u.code)} (${detail})`);
    out.push(`UPDATE t SET ${sets.join(', ')}, t.[UpdatedAt] = SYSUTCDATETIME()`);
    out.push(navLocator(u.level, u.parentCode, u.code) + ';');
    out.push('');
  }

  // 5. Role property updates.
  for (const u of plan.roleUpdates) {
    for (const c of u.changes) {
      if (c.field !== 'name') continue;
      out.push(`-- update role '${u.code}' (name: ${JSON.stringify(c.from)} -> ${JSON.stringify(c.to)})`);
      out.push(`UPDATE [core].[auth_Roles] SET [Name] = ${lit(String(c.to))}, [UpdatedAt] = SYSUTCDATETIME()`);
      out.push(`WHERE [ApplicationId] = @appId AND [Code] = ${lit(u.code)};`);
      out.push('');
    }
  }

  // 6. Permission property updates (action / node rebinding — level-aware
  // since the multi-grain floor). A rebinding sets [Level] (STRING enum name),
  // the target grain's FK, and NULLs the three other FK columns: a permission
  // carries exactly ONE nav FK.
  for (const u of plan.permissionUpdates) {
    const sets: string[] = [];
    const details: string[] = [];
    let rebindNode: string | null = null;
    for (const c of u.changes) {
      if (c.field === 'action') {
        const name = ACTION_NAMES[String(c.to).toLowerCase()];
        sets.push(name ? `[Action] = ${lit(name)}` : `[Action] = NULL`);
        details.push(`action: ${c.from} -> ${c.to}`);
      } else if (c.field === 'nodeCode') {
        rebindNode = String(c.to);
        details.push(`node: ${c.from} -> ${c.to}`);
      } else if (c.field === 'level') {
        // The FK move rides on the nodeCode rebinding (diff always pairs a
        // level change with one) — only the label goes to the summary.
        details.push(`level: ${c.from} -> ${c.to}`);
      }
    }
    if (rebindNode !== null) sets.push(...permissionFkSets(u.level, rebindNode));
    if (sets.length === 0) continue;
    out.push(`-- update permission '${u.path}' (${details.join(', ')})`);
    out.push(`UPDATE [core].[nav_Permissions] SET ${sets.join(', ')}, [UpdatedAt] = SYSUTCDATETIME()`);
    out.push(`WHERE [Path] = ${lit(u.path)};`);
    out.push('');
  }

  // 7. Guarded reactivations for re-added nav entries: the additive boot seed
  // never reactivates a row a previous delta deactivated; harmless 0-row
  // no-op when the entry is genuinely new.
  for (const a of plan.navAdditions) {
    out.push(`-- reactivate ${navPathLabel(a.level, a.parentCode, a.code)} if a past delta deactivated it`);
    out.push('UPDATE t SET t.[IsActive] = 1, t.[UpdatedAt] = SYSUTCDATETIME()');
    out.push(navLocator(a.level, a.parentCode, a.code));
    out.push('  AND t.[IsActive] = 0;');
    out.push('');
  }

  // 8. Role-permission revocations — seed-owned rows only (AssignedBy NULL =
  // legacy seed; admin grants carry the granting user id and are untouched).
  for (const rp of plan.rolePermissionRevocations) {
    out.push(`-- revoke '${rp.permissionPath}' from role '${rp.roleCode}' (seed-owned mappings only)`);
    out.push('DELETE rp');
    out.push('FROM [core].[auth_RolePermissions] rp');
    out.push('JOIN [core].[auth_Roles] r ON r.[Id] = rp.[RoleId]');
    out.push('JOIN [core].[nav_Permissions] p ON p.[Id] = rp.[PermissionId]');
    out.push(`WHERE r.[ApplicationId] = @appId AND r.[Code] = ${lit(rp.roleCode)} AND p.[Path] = ${lit(rp.permissionPath)}`);
    out.push(`  AND (rp.[AssignedBy] IS NULL OR rp.[AssignedBy] LIKE N'${SEED_ASSIGNED_BY_PREFIX}%');`);
    out.push(rowcountPrint(plan.app, `revocation ${rp.roleCode} - ${rp.permissionPath}`));
    out.push('');
  }

  // 9. Permission deletions — mappings first (ALL of them, admin grants
  // included: the permission's surface no longer exists; the count is printed
  // and the reviewer sees the statement in the PR).
  for (const path of plan.permissionDeletions) {
    out.push(`-- delete permission '${path}' (mappings removed first — including admin-granted, review!)`);
    out.push('DELETE rp');
    out.push('FROM [core].[auth_RolePermissions] rp');
    out.push('JOIN [core].[nav_Permissions] p ON p.[Id] = rp.[PermissionId]');
    out.push(`WHERE p.[Path] = ${lit(path)};`);
    out.push(
      `IF @@ROWCOUNT > 0 PRINT 'core-seed delta [${q(plan.app)}]: removed mappings referencing deleted permission ${q(path)}';`,
    );
    out.push(`DELETE FROM [core].[nav_Permissions] WHERE [Path] = ${lit(path)};`);
    out.push('');
  }

  // 10. Navigation deactivations, bottom-up (resource → section → module).
  // Soft: the menu filters IsActive at every level; nothing is destroyed.
  for (const level of ['resource', 'section', 'module'] as CoreSeedStateLevel[]) {
    for (const d of plan.navDeactivations.filter((x) => x.level === level)) {
      out.push(`-- deactivate ${navPathLabel(level, d.parentCode, d.code)} (removed from the seed; soft — IsActive=0)`);
      out.push('UPDATE t SET t.[IsActive] = 0, t.[UpdatedAt] = SYSUTCDATETIME()');
      out.push(navLocator(level, d.parentCode, d.code) + ';');
      out.push('');
    }
  }

  if (warnings.length > 0) {
    out.push('-- generator warnings:');
    for (const w of warnings) out.push(`--   ${w}`);
    out.push('');
  }

  if (!planHasSqlWork(plan)) {
    out.push('-- No data statements required for this release: the state change is purely');
    out.push('-- additive and the boot seed inserts it. This script exists so the applied');
    out.push('-- release is recorded in extensions.core_SeedScriptHistory.');
    out.push('');
  }

  out.push(`PRINT 'core-seed delta [${q(plan.app)}] ${q(ctx.version)}: done.';`);
  out.push('');
  return out.join('\n');
}

function rowcountPrint(app: string, what: string): string {
  return `IF @@ROWCOUNT = 0 PRINT 'core-seed delta [${q(app)}]: ${q(what)} matched no row (already applied or admin-modified).';`;
}

// ─── PR summary (markdown) ───────────────────────────────────────────────

export function renderDeltaSummary(plans: DeltaPlan[], ctx: ScriptContext): string {
  const lines: string[] = [];
  lines.push(`# core-seed delta — version ${ctx.version} (base ${ctx.baseRef})`);
  lines.push('');

  for (const plan of plans) {
    lines.push(`## ${plan.app}`);
    lines.push('');
    if (plan.baseline) {
      lines.push(
        '_Baseline: no state at the base ref — first release shipping the state file. No script generated; prod is presumed to match the current seed lineage._',
      );
      lines.push('');
      continue;
    }
    if (plan.baseHash === plan.newHash) {
      lines.push('_No seed change._');
      lines.push('');
      continue;
    }
    lines.push(`\`${plan.baseHash}\` → \`${plan.newHash}\``);
    lines.push('');

    const script: string[] = [];
    for (const r of plan.navRenames) script.push(`rename ${navPathLabel(r.level, r.parentCode, r.from)} → \`${r.to}\``);
    for (const r of plan.roleRenames) script.push(`rename role \`${r.from}\` → \`${r.to}\``);
    for (const r of plan.permissionRenames) script.push(`rename permission \`${r.from}\` → \`${r.to}\``);
    for (const u of plan.navUpdates)
      script.push(
        `update ${navPathLabel(u.level, u.parentCode, u.code)} (${u.changes.map((c) => `${c.field} ${JSON.stringify(c.from)} → ${JSON.stringify(c.to)}`).join(', ')})`,
      );
    for (const u of plan.roleUpdates)
      script.push(`update role \`${u.code}\` (${u.changes.map((c) => `${c.field} → ${JSON.stringify(c.to)}`).join(', ')})`);
    for (const u of plan.permissionUpdates)
      script.push(`update permission \`${u.path}\` (${u.changes.map((c) => `${c.field} → ${JSON.stringify(c.to)}`).join(', ')})`);
    for (const rp of plan.rolePermissionRevocations)
      script.push(`revoke \`${rp.permissionPath}\` from \`${rp.roleCode}\` (seed-owned only)`);
    for (const p of plan.permissionDeletions)
      script.push(`**delete** permission \`${p}\` (its mappings removed first — including admin grants ⚠)`);
    for (const d of plan.navDeactivations)
      script.push(`deactivate ${navPathLabel(d.level, d.parentCode, d.code)} (soft, IsActive=0)`);

    if (script.length > 0) {
      lines.push('### Applied by the delta script');
      for (const s of script) lines.push(`- ${s}`);
      lines.push('');
    }

    const additive: string[] = [];
    for (const a of plan.navAdditions) additive.push(`add ${navPathLabel(a.level, a.parentCode, a.code)}`);
    for (const r of plan.roleAdditions) additive.push(`add role \`${r}\``);
    for (const p of plan.permissionAdditions) additive.push(`add permission \`${p}\``);
    for (const rp of plan.rolePermissionAdditions)
      additive.push(`grant \`${rp.permissionPath}\` to \`${rp.roleCode}\``);
    if (additive.length > 0) {
      lines.push('### Handled by the additive boot seed (no SQL)');
      for (const s of additive) lines.push(`- ${s}`);
      lines.push('');
    }

    const reported: string[] = [];
    for (const a of plan.removedApplications)
      reported.push(`application \`${a}\` removed from the seed — NEVER touched automatically; decide manually.`);
    for (const r of plan.removedRoles)
      reported.push(
        `role \`${r}\` removed from the seed — retained in DB (users may hold it, auth_UserRoles is Restrict); revoke/delete manually if intended.`,
      );
    if (reported.length > 0) {
      lines.push('### Reported only (no automatic action)');
      for (const s of reported) lines.push(`- ${s}`);
      lines.push('');
    }

    if (plan.ambiguous.length > 0) {
      lines.push('### ⚠ Ambiguous rename candidates (NOT applied)');
      lines.push('');
      lines.push(
        'Treated as remove+add. If one of these IS a rename, declare `previousCodes`/`previousPaths` upstream (or re-run derive-seed-delta with `resolvedRenames`) — otherwise the old row is deactivated/deleted and its references are lost.',
      );
      for (const a of plan.ambiguous.sort((x, y) => y.similarity - x.similarity)) {
        const scope = a.kind === 'navigation' ? `${a.level} ${a.parentCode ? a.parentCode + '/' : ''}` : a.kind + ' ';
        lines.push(`- ${scope}\`${a.removed}\` removed / \`${a.added}\` added (similarity ${a.similarity.toFixed(2)})`);
      }
      lines.push('');
    }
  }

  return lines.join('\n');
}