/**
 * lib/sqlcmd.ts — Generic SQL Server access via `sqlcmd` for skill CLIs.
 *
 * Skills run via `npx tsx` with no Node SQL driver, so we shell out to `sqlcmd`
 * (native Windows/LocalDB trusted auth). Promoted to the shared lib so /uat
 * discovery and future skills share ONE invocation + parsing path (the proven
 * idiom already used by smartstack-entity-audit).
 *
 * Two output styles, both supported:
 *   - pipe rows (`-s "|" -W -h -1`): one row per line, cells split on the separator
 *     — used by nav / permission discovery (see sql-discovery.ts).
 *   - FOR JSON  (`-y 0`): sqlcmd splits JSON across ~2033-char rows; we reassemble.
 */
import { execFileSync } from 'node:child_process';
import type { ParsedConnection } from './appsettings.js';

export interface SqlcmdOptions {
  /** Executable name/path. Default 'sqlcmd' (resolved on PATH). */
  sqlcmdPath?: string;
  /** Column separator for pipe-row mode (`-s`). Default '|'. */
  separator?: string;
  /** execFileSync maxBuffer in bytes. Default 128 MiB. */
  maxBuffer?: number;
  /** Emit FOR-JSON-friendly flags (`-y 0`) instead of a pipe separator. */
  forJson?: boolean;
  /**
   * Client code page forced via `-f`. Default 65001 (UTF-8): ODBC sqlcmd otherwise
   * writes stdout in the console's OEM code page (CP850/CP437 on Windows) while Node
   * decodes UTF-8 — every accented character becomes U+FFFD. `null` omits the flag.
   */
  codePage?: number | null;
}

const DEFAULT_MAX_BUFFER = 128 * 1024 * 1024;

/**
 * Build the sqlcmd argv for a connection + query. PURE (no spawn) so the argument
 * contract is unit-testable. Auth: `-E` (trusted) unless a SQL user is configured.
 */
export function buildSqlcmdArgs(conn: ParsedConnection, query: string, opts: SqlcmdOptions = {}): string[] {
  const auth = conn.useWindowsAuth || !conn.user ? ['-E'] : ['-U', conn.user, '-P', conn.password ?? ''];
  const args = ['-S', conn.server, '-d', conn.database, ...auth, '-b', '-h', '-1', '-W'];
  if (opts.codePage !== null) args.push('-f', String(opts.codePage ?? 65001));
  if (opts.forJson) {
    args.push('-y', '0');
  } else {
    args.push('-s', opts.separator ?? '|');
  }
  if (conn.trustServerCertificate) args.push('-C');
  args.push('-Q', query);
  return args;
}

/**
 * Does an sqlcmd failure look like the binary rejecting the `-f` code-page flag?
 * go-sqlcmd (which emits UTF-8 natively) may not implement `-f`; ODBC sqlcmd does.
 * PURE so the detection contract is unit-testable.
 */
export function isCodePageFlagError(detail: string): boolean {
  return /(unknown (shorthand )?flag|unrecognized|not supported|invalid (option|argument))/i.test(detail) && /(^|[^a-z])-?'?f'?([^a-z]|$)/i.test(detail);
}

/** Run a query, returning raw stdout. Throws a clear error on missing sqlcmd / SQL failure. */
export function runSqlcmd(conn: ParsedConnection, query: string, opts: SqlcmdOptions = {}): string {
  const sqlcmdPath = opts.sqlcmdPath ?? 'sqlcmd';
  const args = buildSqlcmdArgs(conn, query, opts);
  try {
    return execFileSync(sqlcmdPath, args, { encoding: 'utf-8', maxBuffer: opts.maxBuffer ?? DEFAULT_MAX_BUFFER });
  } catch (e: unknown) {
    const err = e as { code?: string; stderr?: string; stdout?: string; message?: string };
    if (err.code === 'ENOENT') {
      throw new Error(
        `'${sqlcmdPath}' not found on PATH. Install the SQL Server command-line tools (sqlcmd) or pass sqlcmdPath.`,
      );
    }
    const detail = [err.stderr, err.stdout].filter(Boolean).join(' ').trim() || err.message || String(e);
    // go-sqlcmd may reject `-f`; it already emits UTF-8 natively, so retry once without it.
    if (opts.codePage !== null && isCodePageFlagError(detail)) {
      return runSqlcmd(conn, query, { ...opts, codePage: null });
    }
    throw new Error(`sqlcmd failed (server="${conn.server}", db="${conn.database}"): ${detail}`);
  }
}

/**
 * Split pipe-row sqlcmd output into cells. PURE. Drops blank lines and a pure-dashes
 * separator line (defensive — `-h -1` already suppresses headers); trims trailing space.
 */
export function parsePipeRows(raw: string, separator = '|'): string[][] {
  return raw
    .split(/\r?\n/)
    .map((l) => l.replace(/\s+$/, ''))
    .filter((l) => l.length > 0 && !/^-+$/.test(l.trim()))
    .map((l) => l.split(separator));
}

/** Reassemble + parse a `FOR JSON` result sqlcmd split across rows. PURE. */
export function parseForJson<T = unknown>(raw: string): T[] {
  const joined = raw
    .split(/\r?\n/)
    .map((l) => l.trim())
    .filter(Boolean)
    .join('');
  if (!joined || joined.toLowerCase() === 'null') return [];
  let parsed: unknown;
  try {
    parsed = JSON.parse(joined);
  } catch (e) {
    throw new Error(
      `Could not parse sqlcmd JSON output: ${(e as Error).message}. First 200 chars: ${joined.slice(0, 200)}`,
    );
  }
  return Array.isArray(parsed) ? (parsed as T[]) : [];
}

/** Convenience: run a FOR JSON query and parse the reassembled result. */
export function runSqlcmdJson<T = unknown>(conn: ParsedConnection, query: string, opts: SqlcmdOptions = {}): T[] {
  return parseForJson<T>(runSqlcmd(conn, query, { ...opts, forJson: true }));
}