/**
 * sql.ts — SQL Server introspection via `sqlcmd`. No node SQL driver: we shell out
 * to sqlcmd (which handles Windows/LocalDB trusted auth natively) and read the
 * result of each `FOR JSON PATH` query.
 *
 * sqlcmd gotchas handled here:
 *   -y 0  → no truncation of nvarchar(max) (default is 256 chars → broken JSON).
 *   -b    → non-zero exit on SQL error so execFileSync throws.
 *   FOR JSON splits its output across ~2033-char rows → we concatenate every line.
 */
import { execFileSync } from 'node:child_process'
import type { ParsedConnection } from '../../../lib/appsettings.js'
import type { DbColumn, DbFk, DbTable } from './classify.js'

/** Only allow safe identifier chars in schema names injected into the IN (...) list. */
function sanitizeIdent(name: string): string {
  return name.replace(/[^A-Za-z0-9_]/g, '')
}

const qColumns = (schemas: string[]): string => {
  const list = schemas.map((s) => `'${sanitizeIdent(s)}'`).join(',') || `''`
  return `SET NOCOUNT ON;
SELECT s.name AS [schema], t.name AS [table], c.name AS [column], ty.name AS [dataType]
FROM sys.columns c
JOIN sys.tables t ON t.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
WHERE s.name IN (${list}) AND c.name LIKE '%Id' AND c.name <> 'Id'
ORDER BY s.name, t.name, c.name
FOR JSON PATH;`
}

const Q_FKS = `SET NOCOUNT ON;
SELECT ps.name AS [schema], pt.name AS [table], pc.name AS [column]
FROM sys.foreign_key_columns fkc
JOIN sys.tables pt ON pt.object_id = fkc.parent_object_id
JOIN sys.schemas ps ON ps.schema_id = pt.schema_id
JOIN sys.columns pc ON pc.object_id = fkc.parent_object_id AND pc.column_id = fkc.parent_column_id
FOR JSON PATH;`

const Q_TABLES = `SET NOCOUNT ON;
SELECT s.name AS [schema], t.name AS [table]
FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
FOR JSON PATH;`

function runSqlcmd(conn: ParsedConnection, sqlcmdPath: string, query: string): 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', '-y', '0', '-C', '-Q', query]
  try {
    return execFileSync(sqlcmdPath, args, { encoding: 'utf-8', maxBuffer: 128 * 1024 * 1024 })
  } catch (e: any) {
    if (e?.code === 'ENOENT') {
      throw new Error(`'${sqlcmdPath}' not found on PATH. Install the SQL Server command-line tools (sqlcmd) or pass sqlcmdPath.`)
    }
    const detail = [e?.stderr, e?.stdout].filter(Boolean).join(' ').trim() || e?.message || String(e)
    throw new Error(`sqlcmd failed (server="${conn.server}", db="${conn.database}"): ${detail}`)
  }
}

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

export interface SchemaSnapshot {
  columns: DbColumn[]
  fks: DbFk[]
  tables: DbTable[]
}

/** Introspect the live database: candidate `*Id` columns, existing FKs, and all tables. */
export function fetchSchema(conn: ParsedConnection, schemas: string[], sqlcmdPath: string): SchemaSnapshot {
  return {
    columns: parseForJson(runSqlcmd(conn, sqlcmdPath, qColumns(schemas))) as DbColumn[],
    fks: parseForJson(runSqlcmd(conn, sqlcmdPath, Q_FKS)) as DbFk[],
    tables: parseForJson(runSqlcmd(conn, sqlcmdPath, Q_TABLES)) as DbTable[],
  }
}