'use strict';
// services/auditStore.js — application-wide Audit Trail. Every mutating API
// request (create / update / delete / approve / login …) is recorded here by
// middleware/auditLogger.js, so admins (or users granted the 'audit' module)
// can review who changed what and when. Stored in the portal's own app DB.
// NOTE: the timestamp column is EVENT_AT, not "AT" — "AT" is a SQL Server
// reserved keyword and misbehaves unbracketed. All column names are bracketed.
const { getPool } = require('./appSqlPool');

const TABLE = `[dbo].[ZAUDIT_LOG]`;

async function exec(sqlQuery, params = []) {
  const pool = await getPool();
  const req  = pool.request();
  let i = 0;
  const text = sqlQuery.replace(/\?/g, () => { const n = `p${i}`; req.input(n, params[i]); i++; return `@${n}`; });
  const result = await req.query(text);
  return result.recordset || [];
}
function isAlreadyExists(e) {
  const m = (e.message || '').toLowerCase();
  return m.includes('already exists') || m.includes('there is already an object');
}

async function bootstrap() {
  console.log('[AUDIT] Checking table', TABLE, '…');
  await exec(`
    CREATE TABLE ${TABLE} (
      [ID]        BIGINT IDENTITY(1,1) PRIMARY KEY,
      [EVENT_AT]  DATETIME2 NOT NULL,
      [USER_ID]   INT,
      [USERNAME]  NVARCHAR(80),
      [ROLE]      NVARCHAR(30),
      [METHOD]    NVARCHAR(10),
      [ACTION]    NVARCHAR(30),
      [ENTITY]    NVARCHAR(80),
      [ENTITY_ID] NVARCHAR(80),
      [SUBACTION] NVARCHAR(80),
      [PATH]      NVARCHAR(400),
      [STATUS]    INT,
      [OK]        BIT,
      [SUMMARY]   NVARCHAR(400),
      [DETAILS]   NVARCHAR(MAX),
      [IP]        NVARCHAR(60),
      [COMPANY]   NVARCHAR(80)
    )
  `).catch(e => { if (isAlreadyExists(e)) { console.log('[AUDIT] Table exists — OK'); } else throw e; });
  // Migration: an earlier build used a reserved-word column "AT" (NOT NULL,
  // indexed) which breaks new inserts. Add EVENT_AT, backfill from AT, then
  // drop the legacy index + column (fallback: just make AT nullable).
  await exec(`IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZAUDIT_LOG' AND COLUMN_NAME='EVENT_AT') ALTER TABLE ${TABLE} ADD [EVENT_AT] DATETIME2`).catch(() => {});
  await exec(`IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZAUDIT_LOG' AND COLUMN_NAME='AT') UPDATE ${TABLE} SET [EVENT_AT] = [AT] WHERE [EVENT_AT] IS NULL`).catch(() => {});
  await exec(`IF EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_ZAUDIT_AT' AND object_id=OBJECT_ID('dbo.ZAUDIT_LOG')) DROP INDEX IX_ZAUDIT_AT ON ${TABLE}`).catch(() => {});
  await exec(`IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZAUDIT_LOG' AND COLUMN_NAME='AT') ALTER TABLE ${TABLE} DROP COLUMN [AT]`)
    .catch(() => exec(`ALTER TABLE ${TABLE} ALTER COLUMN [AT] DATETIME2 NULL`).catch(() => {}));
  await exec(`CREATE INDEX IX_ZAUDIT_AT ON ${TABLE} ([EVENT_AT] DESC)`).catch(() => {});
  await exec(`CREATE INDEX IX_ZAUDIT_ENTITY ON ${TABLE} ([ENTITY], [EVENT_AT] DESC)`).catch(() => {});
  console.log('[AUDIT] ✅ Ready');
}

async function record(e) {
  try {
    const now = new Date().toISOString().replace('T', ' ').replace('Z', '').substring(0, 23);
    await exec(
      `INSERT INTO ${TABLE} ([EVENT_AT],[USER_ID],[USERNAME],[ROLE],[METHOD],[ACTION],[ENTITY],[ENTITY_ID],[SUBACTION],[PATH],[STATUS],[OK],[SUMMARY],[DETAILS],[IP],[COMPANY])
       VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
      [now, e.userId || null, e.username || '', e.role || '', e.method || '', e.action || '',
       e.entity || '', e.entityId || null, e.sub || null, e.path || '',
       e.status || 0, e.ok ? 1 : 0, (e.summary || '').slice(0, 400),
       e.details != null ? JSON.stringify(e.details).slice(0, 100000) : null,
       e.ip || '', e.company || '']
    );
  } catch (err) { console.warn('[AUDIT] record failed (non-fatal):', err.message); }
}

function fromRow(r) {
  const at = r.EVENT_AT || r.AT || null;   // AT = legacy rows before the rename
  return {
    id: Number(r.ID), at: at ? new Date(at).toISOString() : null,
    userId: r.USER_ID || null, username: r.USERNAME || '', role: r.ROLE || '',
    method: r.METHOD || '', action: r.ACTION || '', entity: r.ENTITY || '',
    entityId: r.ENTITY_ID || '', sub: r.SUBACTION || '', path: r.PATH || '',
    status: r.STATUS || 0, ok: !!r.OK, summary: r.SUMMARY || '',
    details: r.DETAILS ? safe(r.DETAILS) : null, ip: r.IP || '', company: r.COMPANY || '',
  };
}
function safe(v) { try { return JSON.parse(v); } catch { return v; } }

async function list({ from, to, username, entity, action, status, q, top = 50, skip = 0 } = {}) {
  const where = []; const params = [];
  if (from)     { where.push(`[EVENT_AT] >= ?`); params.push(String(from).slice(0, 10) + ' 00:00:00'); }
  if (to)       { where.push(`[EVENT_AT] <= ?`); params.push(String(to).slice(0, 10) + ' 23:59:59'); }
  if (username) { where.push(`[USERNAME] = ?`); params.push(username); }
  if (entity)   { where.push(`[ENTITY] = ?`); params.push(entity); }
  if (action)   { where.push(`[ACTION] = ?`); params.push(action); }
  if (status === 'ok')   where.push(`[OK] = 1`);
  if (status === 'fail') where.push(`[OK] = 0`);
  if (q) { where.push(`([SUMMARY] LIKE ? OR [PATH] LIKE ? OR [USERNAME] LIKE ? OR [ENTITY_ID] LIKE ?)`); const like = `%${q}%`; params.push(like, like, like, like); }
  const w = where.length ? `WHERE ${where.join(' AND ')}` : '';
  const t = Math.min(200, parseInt(top) || 50);
  const s = Math.max(0, parseInt(skip) || 0);
  const rows = await exec(`SELECT * FROM ${TABLE} ${w} ORDER BY [ID] DESC OFFSET ${s} ROWS FETCH NEXT ${t} ROWS ONLY`, params);
  const cnt = await exec(`SELECT COUNT(*) AS N FROM ${TABLE} ${w}`, params);
  return { data: rows.map(fromRow), total: Number(cnt[0] && cnt[0].N) || 0 };
}

async function facets() {
  const ent = await exec(`SELECT DISTINCT [ENTITY] AS V FROM ${TABLE} WHERE [ENTITY] <> '' ORDER BY [ENTITY]`);
  const act = await exec(`SELECT DISTINCT [ACTION] AS V FROM ${TABLE} WHERE [ACTION] <> '' ORDER BY [ACTION]`);
  const usr = await exec(`SELECT DISTINCT [USERNAME] AS V FROM ${TABLE} WHERE [USERNAME] <> '' ORDER BY [USERNAME]`);
  return { entities: ent.map(r => r.V), actions: act.map(r => r.V), users: usr.map(r => r.V) };
}

module.exports = { bootstrap, record, list, facets };