import sql from "mssql";
import { loadDataSrcConfig, loadSqlServerConfig } from "../config/loadConfig.js";

/** 提取 SQL 中的 @参数名（忽略 @@ 系统符号） */
export function extractNamedSqlParams(sqlText: string): string[] {
  const re = /(?<![@])@([a-zA-Z_][a-zA-Z0-9_]*)/g;
  const seen = new Set<string>();
  let m: RegExpExecArray | null;
  while ((m = re.exec(sqlText)) !== null) {
    seen.add(m[1]!);
  }
  return [...seen];
}

let pool: sql.ConnectionPool | null = null;

export async function getSqlPool(): Promise<sql.ConnectionPool> {
  if (!pool) {
    const cfg = loadSqlServerConfig();
    pool = await new sql.ConnectionPool(cfg as sql.config).connect();
  }
  return pool;
}

function bindInput(request: sql.Request, name: string, value: unknown): void {
  if (value === undefined) {
    throw new Error(`缺少 SQL 参数: @${name}`);
  }
  if (value instanceof Date) {
    /** 与 SQL 中 CONVERT(DATE, …) 比较，避免与 varchar/datetime2 隐式混用报「从字符串转换」错误 */
    if (name === "startDate" || name === "endDate") {
      request.input(name, sql.Date, value);
    } else {
      request.input(name, sql.DateTime2, value);
    }
    return;
  }
  if (typeof value === "number") {
    if (Number.isInteger(value)) {
      request.input(name, sql.Int, value);
    } else {
      request.input(name, sql.Float, value);
    }
    return;
  }
  if (typeof value === "boolean") {
    request.input(name, sql.Bit, value);
    return;
  }
  request.input(name, sql.NVarChar(sql.MAX), String(value));
}

/**
 * 按 datasrc.json 中的 id 执行 SQL，参数与语句中的 @名称 一一对应，返回行数组（可 JSON 序列化）。
 */
export async function executeDataSourceById(
  id: string,
  params: Record<string, unknown>
): Promise<Record<string, unknown>[]> {
  const { sources } = loadDataSrcConfig();
  const src = sources.find((s) => s.id === id);
  if (!src) {
    throw new Error(`datasrc 未定义 id: ${id}`);
  }
  const paramNames = extractNamedSqlParams(src.sql);
  const p = await getSqlPool();
  const request = p.request();
  for (const name of paramNames) {
    bindInput(request, name, params[name]);
  }
  const result = await request.query(src.sql);
  return (result.recordset ?? []) as Record<string, unknown>[];
}

/**
 * 将 GET/POST 混合格式转为 SQL 参数（字符串中的日期字段尝试解析为 Date）。
 */
export function coerceParamsFromRequest(
  raw: Record<string, unknown>
): Record<string, unknown> {
  const o: Record<string, unknown> = {};
  for (const [k, val] of Object.entries(raw)) {
    if (val === undefined || val === "") {
      continue;
    }
    if (typeof val === "number" || typeof val === "boolean") {
      o[k] = val;
      continue;
    }
    if (val instanceof Date) {
      o[k] = val;
      continue;
    }
    const s = Array.isArray(val) ? String(val[0]) : String(val);
    if (/date$/i.test(k) || /^(start|end)Date$/i.test(k)) {
      const d = new Date(s);
      if (!Number.isNaN(d.getTime())) {
        o[k] = d;
        continue;
      }
    }
    if (/^-?\d+$/.test(s)) {
      o[k] = Number(s);
      continue;
    }
    if (s === "true" || s === "false") {
      o[k] = s === "true";
      continue;
    }
    o[k] = s;
  }
  return o;
}