import { query } from '../util/db.js';

let ensureTablePromise = null;

export async function ensureSystemErrorLogsTable() {
  if (!ensureTablePromise) {
    const sql = `
      CREATE TABLE IF NOT EXISTS SystemErrorLogs (
        ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        AppName VARCHAR(100) NOT NULL DEFAULT 'miaoguo_system_server',
        Env VARCHAR(32) NULL,
        Host VARCHAR(128) NULL,
        PM2Process VARCHAR(128) NULL,
        PID INT NULL,
        Level VARCHAR(32) NOT NULL DEFAULT 'error',
        Source VARCHAR(64) NOT NULL DEFAULT 'application',
        Message VARCHAR(1024) NOT NULL,
        Stack MEDIUMTEXT NULL,
        FilePath VARCHAR(512) NULL,
        LineNumber INT NULL,
        ColumnNumber INT NULL,
        RequestMethod VARCHAR(16) NULL,
        RequestUrl VARCHAR(2048) NULL,
        RequestQuery MEDIUMTEXT NULL,
        RequestBody MEDIUMTEXT NULL,
        UserID VARCHAR(64) NULL,
        NodeVersion VARCHAR(32) NULL,
        GitCommit VARCHAR(64) NULL,
        Fingerprint CHAR(64) NOT NULL,
        Count INT UNSIGNED NOT NULL DEFAULT 1,
        Status VARCHAR(32) NOT NULL DEFAULT 'open',
        Extra MEDIUMTEXT NULL,
        FirstSeenAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        LastSeenAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        CreateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UpdateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (ID),
        UNIQUE KEY uk_SystemErrorLogs_Fingerprint (Fingerprint),
        KEY idx_SystemErrorLogs_Status_LastSeenAt (Status, LastSeenAt),
        KEY idx_SystemErrorLogs_LastSeenAt (LastSeenAt),
        KEY idx_SystemErrorLogs_Source (Source),
        KEY idx_SystemErrorLogs_FilePath (FilePath(191))
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `;

    ensureTablePromise = query(sql).catch((error) => {
      ensureTablePromise = null;
      throw error;
    });
  }

  return ensureTablePromise;
}

export async function upsertErrorLog(log) {
  await ensureSystemErrorLogsTable();

  const sql = `
    INSERT INTO SystemErrorLogs SET ?
    ON DUPLICATE KEY UPDATE
      AppName=VALUES(AppName),
      Env=VALUES(Env),
      Host=VALUES(Host),
      PM2Process=VALUES(PM2Process),
      PID=VALUES(PID),
      Level=VALUES(Level),
      Source=VALUES(Source),
      Message=VALUES(Message),
      Stack=VALUES(Stack),
      FilePath=VALUES(FilePath),
      LineNumber=VALUES(LineNumber),
      ColumnNumber=VALUES(ColumnNumber),
      RequestMethod=VALUES(RequestMethod),
      RequestUrl=VALUES(RequestUrl),
      RequestQuery=VALUES(RequestQuery),
      RequestBody=VALUES(RequestBody),
      UserID=VALUES(UserID),
      NodeVersion=VALUES(NodeVersion),
      GitCommit=VALUES(GitCommit),
      Extra=VALUES(Extra),
      Count=\`Count\` + 1,
      Status=IF(\`Status\`='fixed', 'reopened', \`Status\`),
      LastSeenAt=VALUES(LastSeenAt),
      UpdateTime=NOW()
  `;

  return await query(sql, [log]);
}

export async function listErrorLogs(options = {}) {
  await ensureSystemErrorLogsTable();

  const {
    sinceId = 0,
    limit = 50,
    statusList = ['open', 'reopened'],
    source,
    fingerprint,
    order = 'ASC',
  } = options;

  const where = ['ID > ?'];
  const params = [Number(sinceId) || 0];

  if (statusList && statusList.length > 0) {
    where.push('Status IN (?)');
    params.push(statusList);
  }

  if (source) {
    where.push('Source = ?');
    params.push(source);
  }

  if (fingerprint) {
    where.push('Fingerprint = ?');
    params.push(fingerprint);
  }

  params.push(Math.min(Math.max(Number(limit) || 50, 1), 200));

  const orderSql = String(order).toUpperCase() === 'DESC' ? 'DESC' : 'ASC';
  const sql = `
    SELECT
      ID, AppName, Env, Host, PM2Process, PID, Level, Source, Message,
      Stack, FilePath, LineNumber, ColumnNumber, RequestMethod, RequestUrl,
      RequestQuery, RequestBody, UserID, NodeVersion, GitCommit, Fingerprint,
      Count, Status, Extra, FirstSeenAt, LastSeenAt, CreateTime, UpdateTime
    FROM SystemErrorLogs
    WHERE ${where.join(' AND ')}
    ORDER BY ID ${orderSql}
    LIMIT ?
  `;

  return await query(sql, params);
}

export async function getErrorLogById(id) {
  await ensureSystemErrorLogsTable();

  const sql = 'SELECT * FROM SystemErrorLogs WHERE ID=? LIMIT 1';
  const rows = await query(sql, [Number(id) || 0]);
  return rows[0] || null;
}

export async function updateErrorLogStatus(id, status) {
  await ensureSystemErrorLogsTable();

  const sql = 'UPDATE SystemErrorLogs SET Status=?, UpdateTime=NOW() WHERE ID=?';
  return await query(sql, [status, Number(id) || 0]);
}