'use strict';
const { getPool } = require('./appSqlPool');

const COST_CENTRES = [
  'Administration overhead', 'BB & CAPD', 'BLOOD BAG', 'Boiler', 'CAPD', 'Diff', 'Equipment',
  'FACTORY OVERHEAD', 'POWER & FUEL', 'QA', 'QC', 'R&D',
  'Selling & distribution overhead', 'STENT',
];

async function bootstrap() {
  const pool = await getPool();
  await pool.request().query(`
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='salary_uploads')
    CREATE TABLE salary_uploads (
      id          INT IDENTITY(1,1) PRIMARY KEY,
      period      VARCHAR(7)     NOT NULL,
      sheet_name  NVARCHAR(100)  NOT NULL,
      sr_no       NVARCHAR(20)   DEFAULT '',
      emp_code    NVARCHAR(50)   DEFAULT '',
      emp_name    NVARCHAR(200)  DEFAULT '',
      department  NVARCHAR(100)  DEFAULT '',
      gross_amt   DECIMAL(15,2)  DEFAULT 0,
      net_pay     DECIMAL(15,2)  DEFAULT 0,
      uploaded_at DATETIME       DEFAULT GETDATE()
    )`);
  await pool.request().query(`
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='salary_cost_config')
    CREATE TABLE salary_cost_config (
      id          INT IDENTITY(1,1) PRIMARY KEY,
      sheet_name  NVARCHAR(100)  NOT NULL,
      cost_centre NVARCHAR(100)  NOT NULL,
      alloc_pct   DECIMAL(7,4)   NOT NULL DEFAULT 0,
      updated_at  DATETIME       DEFAULT GETDATE(),
      CONSTRAINT uq_sal_cfg UNIQUE (sheet_name, cost_centre)
    )`);
  await pool.request().query(`
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='salary_bbcapd_ratio')
    CREATE TABLE salary_bbcapd_ratio (
      id         INT IDENTITY(1,1) PRIMARY KEY,
      sheet_name NVARCHAR(100)  NOT NULL UNIQUE,
      bb_pct     DECIMAL(7,4)   NOT NULL DEFAULT 70,
      capd_pct   DECIMAL(7,4)   NOT NULL DEFAULT 30,
      updated_at DATETIME       DEFAULT GETDATE()
    )`);
  await pool.request().query(`
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='salary_dept_mapping')
    CREATE TABLE salary_dept_mapping (
      id          INT IDENTITY(1,1) PRIMARY KEY,
      dept_name   NVARCHAR(200)  NOT NULL UNIQUE,
      cost_centre NVARCHAR(100)  NOT NULL DEFAULT '',
      updated_at  DATETIME       DEFAULT GETDATE()
    )`);
}

async function clearPeriod(period) {
  const pool = await getPool();
  await pool.request().input('p', period)
    .query(`DELETE FROM salary_uploads WHERE period=@p`);
}

async function saveSheetRows(period, sheetName, rows) {
  const pool = await getPool();
  await pool.request()
    .input('p', period).input('s', sheetName)
    .query(`DELETE FROM salary_uploads WHERE period=@p AND sheet_name=@s`);
  for (const r of rows) {
    await pool.request()
      .input('p', period).input('s', sheetName)
      .input('sr', r.sr_no || '').input('ec', r.employee_code || '')
      .input('en', r.employee_name || '').input('dp', r.department || '')
      .input('gr', r.gross_salary || 0).input('np', r.net_pay || 0)
      .query(`INSERT INTO salary_uploads (period,sheet_name,sr_no,emp_code,emp_name,department,gross_amt,net_pay)
              VALUES(@p,@s,@sr,@ec,@en,@dp,@gr,@np)`);
  }
}

async function getPeriods() {
  const pool = await getPool();
  const r = await pool.request()
    .query(`SELECT DISTINCT period FROM salary_uploads ORDER BY period DESC`);
  return (r.recordset || []).map(x => x.period);
}

async function getSheets(period) {
  const pool = await getPool();
  const r = await pool.request().input('p', period).query(`
    SELECT sheet_name, COUNT(*) AS row_count,
           SUM(gross_amt) AS total_gross, SUM(net_pay) AS total_net
    FROM salary_uploads WHERE period=@p
    GROUP BY sheet_name ORDER BY sheet_name`);
  return r.recordset || [];
}

async function getRows(period, sheetName) {
  const pool = await getPool();
  const r = await pool.request()
    .input('p', period).input('s', sheetName)
    .query(`SELECT sr_no,emp_code,emp_name,department,gross_amt,net_pay
            FROM salary_uploads WHERE period=@p AND sheet_name=@s ORDER BY id`);
  return r.recordset || [];
}

async function getConfig() {
  const pool = await getPool();
  const r = await pool.request()
    .query(`SELECT sheet_name,cost_centre,alloc_pct FROM salary_cost_config ORDER BY sheet_name,cost_centre`);
  return r.recordset || [];
}

async function saveConfig(entries) {
  const pool = await getPool();
  for (const e of entries) {
    await pool.request()
      .input('s', e.sheet_name).input('c', e.cost_centre).input('p', e.alloc_pct)
      .query(`MERGE salary_cost_config AS t
              USING (SELECT @s AS sheet_name,@c AS cost_centre) AS src
                 ON t.sheet_name=src.sheet_name AND t.cost_centre=src.cost_centre
              WHEN MATCHED     THEN UPDATE SET alloc_pct=@p,updated_at=GETDATE()
              WHEN NOT MATCHED THEN INSERT(sheet_name,cost_centre,alloc_pct) VALUES(@s,@c,@p);`);
  }
}

async function getBBRatios() {
  const pool = await getPool();
  const r = await pool.request()
    .query(`SELECT sheet_name,bb_pct,capd_pct FROM salary_bbcapd_ratio`);
  return r.recordset || [];
}

async function saveBBRatio(sheetName, bbPct, capdPct) {
  const pool = await getPool();
  await pool.request()
    .input('s', sheetName).input('b', bbPct).input('c', capdPct)
    .query(`MERGE salary_bbcapd_ratio AS t
            USING (SELECT @s AS sheet_name) AS src ON t.sheet_name=src.sheet_name
            WHEN MATCHED     THEN UPDATE SET bb_pct=@b,capd_pct=@c,updated_at=GETDATE()
            WHEN NOT MATCHED THEN INSERT(sheet_name,bb_pct,capd_pct) VALUES(@s,@b,@c);`);
}

async function getDeptMappings() {
  const pool = await getPool();
  const r = await pool.request()
    .query(`SELECT dept_name, cost_centre FROM salary_dept_mapping ORDER BY dept_name`);
  return r.recordset || [];
}

async function saveDeptMapping(deptName, costCentre) {
  const pool = await getPool();
  await pool.request()
    .input('d', deptName).input('c', costCentre)
    .query(`MERGE salary_dept_mapping AS t
            USING (SELECT @d AS dept_name) AS src ON t.dept_name=src.dept_name
            WHEN MATCHED     THEN UPDATE SET cost_centre=@c, updated_at=GETDATE()
            WHEN NOT MATCHED THEN INSERT(dept_name,cost_centre) VALUES(@d,@c);`);
}

async function getDistinctDepts(period) {
  const pool = await getPool();
  const r = await pool.request().input('p', period).query(`
    SELECT department AS dept_name,
           COUNT(*)        AS emp_count,
           SUM(net_pay)    AS total_net
    FROM salary_uploads
    WHERE period=@p AND department <> ''
    GROUP BY department
    ORDER BY department`);
  return r.recordset || [];
}

function applyToCC(total, sheetAlloc, cc, amt, bbRatio) {
  if (cc === 'BB & CAPD') {
    const ratio   = bbRatio || { bb_pct: 70, capd_pct: 30 };
    const bbAmt   = amt * (parseFloat(ratio.bb_pct)   || 70) / 100;
    const capdAmt = amt * (parseFloat(ratio.capd_pct) || 30) / 100;
    total['BLOOD BAG'] += bbAmt;   sheetAlloc['BLOOD BAG'] += bbAmt;
    total['CAPD']      += capdAmt; sheetAlloc['CAPD']      += capdAmt;
  } else if (COST_CENTRES.includes(cc)) {
    total[cc] += amt; sheetAlloc[cc] += amt;
  }
}

async function getSummary(period) {
  const pool     = await getPool();
  const sheets   = await getSheets(period);
  const config   = await getConfig();
  const bbRatios = await getBBRatios();
  const deptMaps = await getDeptMappings();

  const cfgMap  = {};
  config.forEach(c => {
    if (!cfgMap[c.sheet_name]) cfgMap[c.sheet_name] = {};
    cfgMap[c.sheet_name][c.cost_centre] = parseFloat(c.alloc_pct) || 0;
  });
  const bbMap  = {};
  bbRatios.forEach(b => { bbMap[b.sheet_name] = b; });
  const deptMap = {};
  deptMaps.forEach(d => { if (d.cost_centre) deptMap[d.dept_name.toLowerCase().trim()] = d.cost_centre; });

  const total = {};
  COST_CENTRES.forEach(cc => { total[cc] = 0; });
  const bySheet = {};

  for (const sheet of sheets) {
    const sheetAlloc = {};
    COST_CENTRES.forEach(cc => { sheetAlloc[cc] = 0; });
    const bbRatio = bbMap[sheet.sheet_name];
    const alloc   = cfgMap[sheet.sheet_name] || {};

    // Get per-employee rows to apply dept mapping individually
    const rows = await pool.request()
      .input('p', period).input('s', sheet.sheet_name)
      .query(`SELECT department, net_pay FROM salary_uploads WHERE period=@p AND sheet_name=@s`);

    const unmappedDepts = {};
    (rows.recordset || []).forEach(row => {
      const net  = parseFloat(row.net_pay) || 0;
      const dept = (row.department || '').toLowerCase().trim();
      const mappedCC = deptMap[dept];

      if (mappedCC) {
        applyToCC(total, sheetAlloc, mappedCC, net, bbRatio);
      } else {
        // Check if dept value directly matches a cost centre name (sheets 6-8 COST CENTER column)
        const directCC = COST_CENTRES.find(cc => cc.toLowerCase() === dept);
        if (directCC) {
          applyToCC(total, sheetAlloc, directCC, net, bbRatio);
        } else {
          // Fall back to sheet-level % allocation
          const allocated = Object.entries(alloc).reduce((sum, [cc, pct]) => {
            applyToCC(total, sheetAlloc, cc, net * pct / 100, bbRatio);
            return sum + pct;
          }, 0);
          const key = row.department || '(no department)';
          if (!unmappedDepts[key]) unmappedDepts[key] = { net: 0, emp: 0 };
          unmappedDepts[key].net += net;
          unmappedDepts[key].emp += 1;
          unmappedDepts[key].pct_applied = allocated;
        }
      }
    });

    bySheet[sheet.sheet_name] = {
      total_net: parseFloat(sheet.total_net) || 0,
      row_count: sheet.row_count,
      allocation: sheetAlloc,
      unmapped_depts: unmappedDepts,
    };
  }

  return { total, by_sheet: bySheet };
}

module.exports = {
  bootstrap, clearPeriod, saveSheetRows, getPeriods, getSheets, getRows,
  getConfig, saveConfig, getBBRatios, saveBBRatio,
  getDeptMappings, saveDeptMapping, getDistinctDepts,
  getSummary, COST_CENTRES,
};