// services/passwordResetStore.js
// "Forgot password" requests from the login page — this portal has no
// email/SMTP set up and admins already reset passwords directly in User
// Management, so a request here is just a queue an admin reviews and
// actions (reset the password, then mark the request resolved).
const sql = require('mssql');

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

let _conn = null;

async function getConn() {
  if (_conn) return _conn;
  const config = {
    server: process.env.APP_SQL_HOST,
    port: parseInt(process.env.APP_SQL_PORT),
    user: process.env.APP_SQL_USER,
    password: process.env.APP_SQL_PASSWORD,
    database: process.env.APP_SQL_DATABASE,
    options: { encrypt: true, trustServerCertificate: true },
  };
  _conn = await sql.connect(config);
  return _conn;
}

async function exec(sqlQuery, params = []) {
  const conn = await getConn();
  const request = conn.request();
  params.forEach((param, index) => { request.input(`param${index}`, param); });
  const replacedSql = sqlQuery.replace(/\?/g, (match, offset, string) => {
    const paramIndex = (string.slice(0, offset).match(/\?/g) || []).length;
    return `@param${paramIndex}`;
  });
  const result = await request.query(replacedSql);
  return result.recordset || [];
}

function isAlreadyExists(e) {
  const m = (e.message || '').toLowerCase();
  return m.includes('already exists') || m.includes('duplicate') || m.includes('existing object') || m.includes('there is already an object');
}

async function bootstrap() {
  console.log('[PW-RESET-STORE] Checking table', TABLE, '...');
  await exec(`
    CREATE TABLE ${TABLE} (
      ID           INT IDENTITY(1,1) PRIMARY KEY,
      USERNAME     NVARCHAR(50) NOT NULL,
      NOTE         NVARCHAR(500),
      STATUS       NVARCHAR(20) NOT NULL DEFAULT 'PENDING',
      CREATED_AT   DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
      RESOLVED_BY  NVARCHAR(50),
      RESOLVED_AT  DATETIME2
    )
  `).catch(e => {
    if (isAlreadyExists(e)) { console.log('[PW-RESET-STORE] Table exists — OK'); }
    else throw e;
  });
  console.log('[PW-RESET-STORE] ✅ Ready');
}

function fromRow(row) {
  return {
    id: row.ID,
    username: row.USERNAME,
    note: row.NOTE || '',
    status: row.STATUS,
    createdAt: row.CREATED_AT,
    resolvedBy: row.RESOLVED_BY || '',
    resolvedAt: row.RESOLVED_AT,
  };
}

async function createRequest(username, note) {
  const idRows = await exec(`
    INSERT INTO ${TABLE} (USERNAME, NOTE) VALUES (?, ?);
    SELECT SCOPE_IDENTITY() AS ID;
  `, [username, note || '']);
  return idRows[0].ID;
}

async function listRequests(status) {
  const rows = status
    ? await exec(`SELECT * FROM ${TABLE} WHERE STATUS = ? ORDER BY CREATED_AT DESC`, [status])
    : await exec(`SELECT * FROM ${TABLE} ORDER BY CREATED_AT DESC`);
  return rows.map(fromRow);
}

async function resolveRequest(id, status, resolvedBy) {
  await exec(`
    UPDATE ${TABLE} SET STATUS = ?, RESOLVED_BY = ?, RESOLVED_AT = SYSDATETIME()
    WHERE ID = ?
  `, [status, resolvedBy || '', id]);
}

module.exports = { bootstrap, createRequest, listRequests, resolveRequest };