/**
 * 模块 SQL 来源注释：在驱动执行前注入首部注释，便于阿里云慢 SQL 定位来源。
 * 安全校验须先对原始 SQL 执行，再调用本工具。
 */

export type SqlAction =
  | 'select'
  | 'insert'
  | 'update'
  | 'delete'
  | 'merge'
  | 'create'
  | 'analyze'
  | 'explain'
  | 'call'
  | 'copy'
  | 'other';

export interface ModuleSqlCommentContext {
  /** 模块完整名，如 `@ops/output-retention-monitor`（注释里写短名） */
  moduleName: string;
  /**
   * 当前登录人员工 id；缺省时注释写 `user: unknown`。
   * 生产/本地由请求 ALS 注入，也可显式传入（便于单测）。
   */
  userId?: number | null;
  startTime: Date;
}

const KEYWORD_TO_ACTION: Record<string, SqlAction> = {
  select: 'select',
  insert: 'insert',
  update: 'update',
  delete: 'delete',
  merge: 'merge',
  create: 'create',
  analyze: 'analyze',
  explain: 'explain',
  call: 'call',
  copy: 'copy',
};

function isIdentStart(ch: string): boolean {
  return /[A-Za-z_]/.test(ch);
}

function isIdentPart(ch: string): boolean {
  return /[A-Za-z0-9_]/.test(ch);
}

function skipLineComment(sql: string, index: number): number {
  let i = index + 2;
  while (i < sql.length && sql[i] !== '\n' && sql[i] !== '\r') i += 1;
  return i;
}

function skipBlockComment(sql: string, index: number): number {
  let i = index + 2;
  while (i < sql.length - 1) {
    if (sql[i] === '*' && sql[i + 1] === '/') return i + 2;
    i += 1;
  }
  return sql.length;
}

/** 跳过空白与前置 SQL 注释 */
export function skipTrivia(sql: string, start = 0): number {
  let i = start;
  while (i < sql.length) {
    const ch = sql[i]!;
    if (/\s/.test(ch)) {
      i += 1;
      continue;
    }
    if (ch === '-' && sql[i + 1] === '-') {
      i = skipLineComment(sql, i);
      continue;
    }
    if (ch === '/' && sql[i + 1] === '*') {
      i = skipBlockComment(sql, i);
      continue;
    }
    break;
  }
  return i;
}

function readKeyword(sql: string, start: number): { keyword: string; next: number } | null {
  const i = skipTrivia(sql, start);
  if (i >= sql.length || !isIdentStart(sql[i]!)) return null;
  let j = i + 1;
  while (j < sql.length && isIdentPart(sql[j]!)) j += 1;
  return { keyword: sql.slice(i, j).toLowerCase(), next: j };
}

/** 跳过单引号字符串（含 '' 转义） */
function skipSingleQuoted(sql: string, index: number): number {
  let i = index + 1;
  while (i < sql.length) {
    if (sql[i] === "'") {
      if (sql[i + 1] === "'") {
        i += 2;
        continue;
      }
      return i + 1;
    }
    i += 1;
  }
  return sql.length;
}

/** 跳过双引号标识符（含 "" 转义） */
function skipDoubleQuoted(sql: string, index: number): number {
  let i = index + 1;
  while (i < sql.length) {
    if (sql[i] === '"') {
      if (sql[i + 1] === '"') {
        i += 2;
        continue;
      }
      return i + 1;
    }
    i += 1;
  }
  return sql.length;
}

/** 跳过美元引用字符串：$tag$...$tag$ 或 $$...$$ */
function skipDollarQuoted(sql: string, index: number): number {
  let i = index + 1;
  while (i < sql.length && /[A-Za-z0-9_]/.test(sql[i]!)) i += 1;
  if (sql[i] !== '$') return index + 1;
  const tag = sql.slice(index, i + 1);
  i += 1;
  const end = sql.indexOf(tag, i);
  return end < 0 ? sql.length : end + tag.length;
}

/**
 * 从 openParen 位置（指向 '('）跳到匹配的 ')'，忽略字符串/注释中的括号。
 */
function skipBalancedParens(sql: string, openParen: number): number {
  let depth = 0;
  let i = openParen;
  while (i < sql.length) {
    const ch = sql[i]!;
    if (ch === '-' && sql[i + 1] === '-') {
      i = skipLineComment(sql, i);
      continue;
    }
    if (ch === '/' && sql[i + 1] === '*') {
      i = skipBlockComment(sql, i);
      continue;
    }
    if (ch === "'") {
      i = skipSingleQuoted(sql, i);
      continue;
    }
    if (ch === '"') {
      i = skipDoubleQuoted(sql, i);
      continue;
    }
    if (ch === '$') {
      i = skipDollarQuoted(sql, i);
      continue;
    }
    if (ch === '(') {
      depth += 1;
      i += 1;
      continue;
    }
    if (ch === ')') {
      depth -= 1;
      i += 1;
      if (depth === 0) return i;
      continue;
    }
    i += 1;
  }
  return sql.length;
}

/** 跳过一个 CTE：name [ (cols) ] AS [ [NOT] MATERIALIZED ] (body) */
function skipOneCte(sql: string, start: number): number | null {
  let i = skipTrivia(sql, start);
  // CTE 名：普通标识符或双引号标识符
  if (i >= sql.length) return null;
  if (sql[i] === '"') {
    i = skipDoubleQuoted(sql, i);
  } else if (isIdentStart(sql[i]!)) {
    while (i < sql.length && isIdentPart(sql[i]!)) i += 1;
  } else {
    return null;
  }

  i = skipTrivia(sql, i);
  // 可选列清单
  if (sql[i] === '(') {
    i = skipBalancedParens(sql, i);
    i = skipTrivia(sql, i);
  }

  const asKw = readKeyword(sql, i);
  if (!asKw || asKw.keyword !== 'as') return null;
  i = asKw.next;

  i = skipTrivia(sql, i);
  // 可选 MATERIALIZED / NOT MATERIALIZED
  const maybeNot = readKeyword(sql, i);
  if (maybeNot?.keyword === 'not') {
    const mat = readKeyword(sql, maybeNot.next);
    if (mat?.keyword === 'materialized') i = mat.next;
  } else if (maybeNot?.keyword === 'materialized') {
    i = maybeNot.next;
  }

  i = skipTrivia(sql, i);
  if (sql[i] !== '(') return null;
  return skipBalancedParens(sql, i);
}

/** 越过 WITH [RECURSIVE] cte [, cte ...] 后返回主语句起点 */
function skipWithClause(sql: string, afterWith: number): number | null {
  let i = afterWith;
  const maybeRecursive = readKeyword(sql, i);
  if (maybeRecursive?.keyword === 'recursive') {
    i = maybeRecursive.next;
  }

  while (true) {
    const next = skipOneCte(sql, i);
    if (next === null) return null;
    i = skipTrivia(sql, next);
    if (sql[i] === ',') {
      i += 1;
      continue;
    }
    return i;
  }
}

/**
 * 识别 SQL 主操作类型。CTE 越过 WITH 定义后看主语句。
 * 无法可靠识别时返回 `other`，不抛错。
 */
export function detectSqlAction(sql: string): SqlAction {
  const first = readKeyword(sql, 0);
  if (!first) return 'other';

  if (first.keyword === 'with') {
    const mainStart = skipWithClause(sql, first.next);
    if (mainStart === null) return 'other';
    const main = readKeyword(sql, mainStart);
    if (!main) return 'other';
    return KEYWORD_TO_ACTION[main.keyword] ?? 'other';
  }

  return KEYWORD_TO_ACTION[first.keyword] ?? 'other';
}

function sanitizeToken(value: string, allow: RegExp): string {
  return value
    .replace(/\r|\n/g, '_')
    .replace(allow, '_')
    .replace(/\*\//g, '*_/');
}

function sanitizeModuleName(moduleName: string): string {
  return sanitizeToken(moduleName, /[^A-Za-z0-9@/_-]/g);
}

/** `@ops/foo` → `foo`；已是短名则原样返回 */
export function shortModuleName(moduleName: string): string {
  const cleaned = sanitizeModuleName(moduleName);
  const slash = cleaned.lastIndexOf('/');
  return slash >= 0 ? cleaned.slice(slash + 1) : cleaned;
}

function sanitizeUsername(username: string): string {
  const cleaned = sanitizeToken(username, /[^A-Za-z0-9@./_-]/g);
  return cleaned || 'unknown';
}

/** 解析连接池使用的数据库账号；缺失时返回 unknown（不阻断查询） */
export function resolveDatabaseUser(user?: string | null): string {
  const raw = typeof user === 'string' ? user.trim() : '';
  if (!raw) return 'unknown';
  return sanitizeUsername(raw);
}

/**
 * 将瞬时时间格式化为东八区 ISO（毫秒 + `+08:00`）。
 * 例：UTC `2026-08-31T08:20:31.123Z` → `2026-08-31T16:20:31.123+08:00`
 */
export function formatBeijingIso(date: Date): string {
  const shifted = new Date(date.getTime() + 8 * 60 * 60 * 1000);
  return `${shifted.toISOString().replace(/Z$/, '')}+08:00`;
}

/** 登录人 id：正整数；否则 unknown */
export function formatCommentUserId(userId?: number | null): string {
  if (typeof userId === 'number' && Number.isFinite(userId) && userId > 0) {
    return String(Math.trunc(userId));
  }
  return 'unknown';
}

/**
 * 在 SQL 首部注入来源注释。不改写占位符与正文。
 * 协议：`/* module: <短名>, user: <登录人id>, start_time: <UTC+8> *\/`
 */
export function annotateModuleSql(sql: string, context: ModuleSqlCommentContext): string {
  const module = shortModuleName(context.moduleName) || 'unknown';
  const user = formatCommentUserId(context.userId);
  const startTime = formatBeijingIso(context.startTime);
  const comment = `/* module: ${module}, user: ${user}, start_time: ${startTime} */`;
  return `${comment} ${sql}`;
}