// services/woHeaderProfileStore.js
// Work Order print/PDF header — previously a single flat config (Admin →
// System Settings → "Header Details") burned onto every printed Work Order
// regardless of product. Now a LIST of named profiles (e.g. "Blood Bag",
// "CAPD", "Accessories"), each mapping to a set of SAP Item Groups; the
// profile actually used for a given Work Order is resolved client-side in
// work-order-print.html from the WO's product's SAP item group. Exactly one
// profile is always the Default — used as the fallback for any item group
// not explicitly mapped to a specific profile.
const sql = require('mssql');

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

let _conn = null;
async function getConn() {
  if (_conn) return _conn;
  _conn = await sql.connect({
    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 },
  });
  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, (m, offset, string) => {
    const i = (string.slice(0, offset).match(/\?/g) || []).length;
    return `@param${i}`;
  });
  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('[WO-HEADER-PROFILES] Checking table', TABLE, '...');
  await exec(`
    CREATE TABLE ${TABLE} (
      ID              INT IDENTITY(1,1) PRIMARY KEY,
      NAME            NVARCHAR(100) NOT NULL,
      IS_DEFAULT      BIT DEFAULT 0,
      ITEM_GROUPS     NVARCHAR(MAX),
      COMPANY_NAME    NVARCHAR(200),
      COMPANY_ADDRESS NVARCHAR(400),
      FORM_NO         NVARCHAR(100),
      EFFECTIVE_DATE  NVARCHAR(20),
      REVIEW_DATE     NVARCHAR(20),
      LOGO            NVARCHAR(MAX),
      CREATED_BY      NVARCHAR(50),
      CREATED_NAME    NVARCHAR(100),
      CREATED_AT      DATETIME2,
      UPDATED_AT      DATETIME2,
      IS_DELETED      BIT DEFAULT 0
    )
  `).catch(e => {
    if (isAlreadyExists(e)) { console.log('[WO-HEADER-PROFILES] Table exists — OK'); }
    else throw e;
  });

  // One-time seed: if the table is completely empty (fresh install of this
  // feature), carry the OLD single flat System Settings values over as the
  // Default profile, so nothing on any already-printed-from workflow breaks
  // the moment this ships — existing users see exactly the same header they
  // had yesterday, just now editable as "Default" instead of the old flat
  // settings fields.
  const existing = await exec(`SELECT COUNT(*) AS N FROM ${TABLE} WHERE IS_DELETED = 0 OR IS_DELETED IS NULL`);
  if (!existing.length || !existing[0].N) {
    const appSettings = require('./appSettingsStore');
    const now = toTs(new Date().toISOString());
    await exec(`
      INSERT INTO ${TABLE} (NAME, IS_DEFAULT, ITEM_GROUPS, COMPANY_NAME, COMPANY_ADDRESS, FORM_NO, EFFECTIVE_DATE, REVIEW_DATE, LOGO, CREATED_BY, CREATED_NAME, CREATED_AT)
      VALUES ('Default', 1, '[]', ?, ?, ?, ?, ?, ?, 'system', 'System (migrated)', ?)
    `, [
      appSettings.woCompanyName(), appSettings.woCompanyAddress(), appSettings.woFormNo(),
      appSettings.woEffectiveDate(), appSettings.woReviewDate(), appSettings.woLogo(), now,
    ]);
    console.log('[WO-HEADER-PROFILES] Seeded "Default" profile from legacy System Settings values');
  }
  console.log('[WO-HEADER-PROFILES] ✅ Ready');
}

function toTs(iso) { return iso ? iso.replace('T', ' ').replace('Z', '').substring(0, 23) : null; }
function safeJson(v, f) { if (!v) return f; try { return JSON.parse(v); } catch { return f; } }

function fromRow(row) {
  if (!row) return null;
  return {
    id:              row.ID,
    name:            row.NAME || '',
    isDefault:       !!row.IS_DEFAULT,
    itemGroups:      safeJson(row.ITEM_GROUPS, []).map(String),
    companyName:     row.COMPANY_NAME || '',
    companyAddress:  row.COMPANY_ADDRESS || '',
    formNo:          row.FORM_NO || '',
    effectiveDate:   row.EFFECTIVE_DATE || '',
    reviewDate:      row.REVIEW_DATE || '',
    logo:            row.LOGO || '',
    createdBy:       row.CREATED_BY || '',
    createdByName:   row.CREATED_NAME || '',
    createdAt:       row.CREATED_AT ? new Date(row.CREATED_AT).toISOString() : null,
    updatedAt:       row.UPDATED_AT ? new Date(row.UPDATED_AT).toISOString() : null,
    isDeleted:       !!row.IS_DELETED,
  };
}

async function listProfiles() {
  const rows = await exec(`SELECT * FROM ${TABLE} WHERE IS_DELETED = 0 OR IS_DELETED IS NULL ORDER BY IS_DEFAULT DESC, NAME`);
  return rows.map(fromRow);
}

async function findById(id) {
  const rows = await exec(`SELECT * FROM ${TABLE} WHERE ID = ?`, [parseInt(id)]);
  return rows.length ? fromRow(rows[0]) : null;
}

async function createProfile(p) {
  const now = toTs(new Date().toISOString());
  const idRows = await exec(`
    INSERT INTO ${TABLE} (NAME, IS_DEFAULT, ITEM_GROUPS, COMPANY_NAME, COMPANY_ADDRESS, FORM_NO, EFFECTIVE_DATE, REVIEW_DATE, LOGO, CREATED_BY, CREATED_NAME, CREATED_AT)
    VALUES (?,0,?,?,?,?,?,?,?,?,?,?);
    SELECT SCOPE_IDENTITY() AS ID;
  `, [
    p.name || 'Untitled', JSON.stringify(p.itemGroups || []),
    p.companyName || '', p.companyAddress || '', p.formNo || '',
    p.effectiveDate || '', p.reviewDate || '', p.logo || '',
    p.createdBy, p.createdByName || p.createdBy, now,
  ]);
  return findById(idRows[0].ID);
}

async function updateProfile(id, patch) {
  const now = toTs(new Date().toISOString());
  await exec(`
    UPDATE ${TABLE}
    SET NAME = ?, ITEM_GROUPS = ?, COMPANY_NAME = ?, COMPANY_ADDRESS = ?, FORM_NO = ?, EFFECTIVE_DATE = ?, REVIEW_DATE = ?, LOGO = ?, UPDATED_AT = ?
    WHERE ID = ? AND (IS_DELETED = 0 OR IS_DELETED IS NULL)
  `, [
    patch.name || 'Untitled', JSON.stringify(patch.itemGroups || []),
    patch.companyName || '', patch.companyAddress || '', patch.formNo || '',
    patch.effectiveDate || '', patch.reviewDate || '', patch.logo || '',
    now, parseInt(id),
  ]);
  return findById(id);
}

// Exactly one profile is ever the Default — set this one, unset every other.
async function setDefault(id) {
  const now = toTs(new Date().toISOString());
  await exec(`UPDATE ${TABLE} SET IS_DEFAULT = 0, UPDATED_AT = ? WHERE IS_DEFAULT = 1`, [now]);
  await exec(`UPDATE ${TABLE} SET IS_DEFAULT = 1, UPDATED_AT = ? WHERE ID = ?`, [now, parseInt(id)]);
  return findById(id);
}

// Refuses to delete the last remaining Default (there must always be a
// fallback for unmapped item groups) — the caller should setDefault() on a
// different profile first if they want to remove today's Default.
async function softDelete(id) {
  const p = await findById(id);
  if (!p) return;
  if (p.isDefault) throw new Error('Cannot delete the Default profile — set a different profile as Default first');
  await exec(`UPDATE ${TABLE} SET IS_DELETED = 1, UPDATED_AT = ? WHERE ID = ?`, [toTs(new Date().toISOString()), parseInt(id)]);
}

module.exports = {
  bootstrap, listProfiles, findById, createProfile, updateProfile, setDefault, softDelete,
};