'use strict';
const sql = require('mssql');

async function getPool() {
  return require('./appSqlPool').getPool();
}

async function bootstrap() {
  const pool = await getPool();
  await pool.request().query(`
    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='project_requests' AND xtype='U')
    CREATE TABLE project_requests (
      id                    INT IDENTITY(1,1) PRIMARY KEY,
      project_code          NVARCHAR(50)   UNIQUE,
      project_name          NVARCHAR(200)  NOT NULL,
      project_category      NCHAR(1)       NOT NULL DEFAULT 'B',
      total_days            INT,
      initiation_dt         DATE,
      est_start_dt          DATE,
      est_finish_dt         DATE,
      purpose               NVARCHAR(MAX),
      roi                   NVARCHAR(MAX),
      labour_cost           DECIMAL(15,2)  DEFAULT 0,
      material_cost         DECIMAL(15,2)  DEFAULT 0,
      consultancy_fees      DECIMAL(15,2)  DEFAULT 0,
      promotional_fees      DECIMAL(15,2)  DEFAULT 0,
      other_cost            DECIMAL(15,2)  DEFAULT 0,
      total_est_cost        DECIMAL(15,2)  DEFAULT 0,
      department_name       NVARCHAR(100),
      prepared_by_name      NVARCHAR(100),
      prepared_by_desig     NVARCHAR(100),
      accountable_person    NVARCHAR(100),
      team_members          NVARCHAR(MAX),
      status                NVARCHAR(50)   DEFAULT 'DRAFT',
      authorised_by         NVARCHAR(100),
      authorised_dt         DATE,
      comments              NVARCHAR(MAX),
      submitted_by          NVARCHAR(100),
      project_type          NVARCHAR(20)   DEFAULT 'NEW',
      req_new_project       NVARCHAR(MAX),
      req_upgradation       NVARCHAR(MAX),
      time_cost_recovery    NVARCHAR(200),
      remarks_p2            NVARCHAR(MAX),
      attachments           NVARCHAR(MAX),
      created_at            DATETIME       DEFAULT GETDATE(),
      updated_at            DATETIME       DEFAULT GETDATE()
    )
  `);

  // safe-add every column that might be missing on older DBs
  const alter = pool.request();
  for (const col of [
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='project_type') ALTER TABLE project_requests ADD project_type NVARCHAR(20) DEFAULT 'NEW'`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='req_new_project') ALTER TABLE project_requests ADD req_new_project NVARCHAR(MAX)`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='req_upgradation') ALTER TABLE project_requests ADD req_upgradation NVARCHAR(MAX)`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='time_cost_recovery') ALTER TABLE project_requests ADD time_cost_recovery NVARCHAR(200)`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='remarks_p2') ALTER TABLE project_requests ADD remarks_p2 NVARCHAR(MAX)`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='attachments') ALTER TABLE project_requests ADD attachments NVARCHAR(MAX)`,
    // purchase
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='purchase_verified_dt') ALTER TABLE project_requests ADD purchase_verified_dt DATE`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='purchase_applicable') ALTER TABLE project_requests ADD purchase_applicable BIT DEFAULT 1`,
    // engineering
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='engineering_verified_dt') ALTER TABLE project_requests ADD engineering_verified_dt DATE`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='engineering_applicable') ALTER TABLE project_requests ADD engineering_applicable BIT DEFAULT 1`,
    // qa
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='qa_verified_dt') ALTER TABLE project_requests ADD qa_verified_dt DATE`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='qa_applicable') ALTER TABLE project_requests ADD qa_applicable BIT DEFAULT 1`,
    // qc
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='qc_verified_dt') ALTER TABLE project_requests ADD qc_verified_dt DATE`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='qc_applicable') ALTER TABLE project_requests ADD qc_applicable BIT DEFAULT 1`,
    // legal
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='legal_verified_dt') ALTER TABLE project_requests ADD legal_verified_dt DATE`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='legal_applicable') ALTER TABLE project_requests ADD legal_applicable BIT DEFAULT 1`,
    // project owner
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='owner_approved_dt') ALTER TABLE project_requests ADD owner_approved_dt DATE`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='owner_applicable') ALTER TABLE project_requests ADD owner_applicable BIT DEFAULT 1`,
    // plant manager
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='plant_applicable') ALTER TABLE project_requests ADD plant_applicable BIT DEFAULT 1`,
    // finance (last step)
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='finance_verified_dt') ALTER TABLE project_requests ADD finance_verified_dt DATE`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='finance_verified_by') ALTER TABLE project_requests ADD finance_verified_by NVARCHAR(100)`,
    `IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('project_requests') AND name='finance_applicable') ALTER TABLE project_requests ADD finance_applicable BIT DEFAULT 1`,
  ]) await alter.query(col);

  console.log('[ProjectStore] ✅ Table ready');
}

async function nextCode() {
  const pool = await getPool();
  // Financial year: April–March  e.g. May 2026 → "26-27", Jan 2027 → "26-27"
  const now = new Date();
  const month = now.getMonth() + 1;
  const year  = now.getFullYear();
  const fyStart = month >= 4 ? year : year - 1;
  const fy = `${String(fyStart).slice(-2)}-${String(fyStart + 1).slice(-2)}`;
  const prefix = `MIPL/${fy}/`;

  // Max from existing MIPL codes (handles gaps after deletion)
  const r1 = await pool.request().query(
    `SELECT MAX(CAST(SUBSTRING(project_code, ${prefix.length + 1}, 10) AS INT)) AS mipl_max
     FROM project_requests WHERE project_code LIKE '${prefix}%'`
  );
  // Total project count covers migration from old PRJ-YYYY format
  const r2 = await pool.request().query(
    `SELECT COUNT(*) AS total FROM project_requests`
  );
  const miplMax = r1.recordset[0].mipl_max || 0;
  const total   = r2.recordset[0].total   || 0;
  // Floor of 11 so the sequence never generates below 012
  const next = Math.max(miplMax, total, 11) + 1;
  return `${prefix}${String(next).padStart(3, '0')}`;
}

async function create(data) {
  const pool = await getPool();
  const code = await nextCode();
  const r = pool.request();
  r.input('code',    sql.NVarChar, code);
  r.input('name',    sql.NVarChar, data.project_name);
  r.input('cat',     sql.NChar,    data.project_category || 'B');
  r.input('days',    sql.Int,      data.total_days || null);
  r.input('init_dt', sql.Date,     data.initiation_dt   || null);
  r.input('start_dt',sql.Date,     data.est_start_dt    || null);
  r.input('end_dt',  sql.Date,     data.est_finish_dt   || null);
  r.input('purpose', sql.NVarChar, data.purpose         || null);
  r.input('roi',     sql.NVarChar, data.roi             || null);
  r.input('labour',  sql.Decimal,  parseFloat(data.labour_cost)      || 0);
  r.input('material',sql.Decimal,  parseFloat(data.material_cost)    || 0);
  r.input('consult', sql.Decimal,  parseFloat(data.consultancy_fees) || 0);
  r.input('promo',   sql.Decimal,  parseFloat(data.promotional_fees) || 0);
  r.input('other',   sql.Decimal,  parseFloat(data.other_cost)       || 0);
  r.input('total',   sql.Decimal,  parseFloat(data.total_est_cost)   || 0);
  r.input('dept',    sql.NVarChar, data.department_name      || null);
  r.input('pbname',  sql.NVarChar, data.prepared_by_name     || null);
  r.input('pbdesig', sql.NVarChar, data.prepared_by_desig    || null);
  r.input('acct',    sql.NVarChar, data.accountable_person   || null);
  r.input('team',    sql.NVarChar, JSON.stringify(data.team_members || []));
  r.input('status',  sql.NVarChar, data.status || 'DRAFT');
  r.input('subby',   sql.NVarChar, data.submitted_by || null);
  r.input('ptype',   sql.NVarChar, data.project_type || 'NEW');
  r.input('reqnew',  sql.NVarChar, data.req_new_project   || null);
  r.input('requpg',  sql.NVarChar, data.req_upgradation   || null);
  r.input('tcr',     sql.NVarChar, data.time_cost_recovery || null);
  r.input('rem2',    sql.NVarChar, data.remarks_p2        || null);
  // BIT flags — use explicit 0/1, never || null (0 is valid)
  r.input('qa_app',  sql.Bit, data.qa_applicable  === 0 ? 0 : 1);
  r.input('qc_app',  sql.Bit, data.qc_applicable  === 0 ? 0 : 1);
  const res = await r.query(`
    INSERT INTO project_requests (
      project_code, project_name, project_category, total_days,
      initiation_dt, est_start_dt, est_finish_dt, purpose, roi,
      labour_cost, material_cost, consultancy_fees, promotional_fees,
      other_cost, total_est_cost, department_name, prepared_by_name,
      prepared_by_desig, accountable_person, team_members, status, submitted_by,
      project_type, req_new_project, req_upgradation, time_cost_recovery, remarks_p2,
      qa_applicable, qc_applicable
    ) VALUES (
      @code,@name,@cat,@days,@init_dt,@start_dt,@end_dt,@purpose,@roi,
      @labour,@material,@consult,@promo,@other,@total,
      @dept,@pbname,@pbdesig,@acct,@team,@status,@subby,
      @ptype,@reqnew,@requpg,@tcr,@rem2,
      @qa_app,@qc_app
    );
    SELECT SCOPE_IDENTITY() AS id;
  `);
  return { id: res.recordset[0].id, project_code: code };
}

async function list({ status, search } = {}) {
  const pool = await getPool();
  const r = pool.request();
  let q = `SELECT id, project_code, project_name, project_category, status,
             department_name, prepared_by_name, est_start_dt, est_finish_dt,
             total_est_cost, submitted_by, created_at
           FROM project_requests WHERE 1=1`;
  if (status) { r.input('st', sql.NVarChar, status); q += ' AND status=@st'; }
  if (search) { r.input('s', sql.NVarChar, `%${search}%`); q += ' AND (project_name LIKE @s OR project_code LIKE @s)'; }
  q += ' ORDER BY created_at DESC';
  const res = await r.query(q);
  return res.recordset || [];
}

async function findById(id) {
  const pool = await getPool();
  const res = await pool.request().input('id', sql.Int, parseInt(id))
    .query('SELECT * FROM project_requests WHERE id=@id');
  const row = res.recordset[0];
  if (!row) return null;
  try { row.team_members = JSON.parse(row.team_members || '[]'); } catch { row.team_members = []; }
  try { row.attachments = JSON.parse(row.attachments || '[]'); } catch { row.attachments = []; }
  return row;
}

async function update(id, data) {
  const pool = await getPool();
  const r = pool.request();
  r.input('id', sql.Int, parseInt(id));
  const sets = ['updated_at=GETDATE()'];

  const map = {
    project_name:        ['nm',  sql.NVarChar], project_category:    ['cat', sql.NChar],
    total_days:          ['days',sql.Int],       initiation_dt:       ['idt', sql.Date],
    est_start_dt:        ['sdt', sql.Date],      est_finish_dt:       ['edt', sql.Date],
    purpose:             ['pur', sql.NVarChar],  roi:                 ['roi', sql.NVarChar],
    labour_cost:         ['lab', sql.Decimal],   material_cost:       ['mat', sql.Decimal],
    consultancy_fees:    ['con', sql.Decimal],   promotional_fees:    ['pro', sql.Decimal],
    other_cost:          ['oth', sql.Decimal],   total_est_cost:      ['tot', sql.Decimal],
    department_name:     ['dep', sql.NVarChar],  prepared_by_name:    ['pbn', sql.NVarChar],
    prepared_by_desig:   ['pbd', sql.NVarChar],  accountable_person:  ['acc', sql.NVarChar],
    status:              ['sta', sql.NVarChar],
    authorised_by:       ['aub', sql.NVarChar],  authorised_dt:       ['aud', sql.Date],
    comments:            ['com', sql.NVarChar],
    project_type:        ['pty', sql.NVarChar],  req_new_project:     ['rnw', sql.NVarChar],
    req_upgradation:     ['rug', sql.NVarChar],  time_cost_recovery:  ['tcr', sql.NVarChar],
    remarks_p2:          ['rm2', sql.NVarChar],
    // purchase
    purchase_verified_dt:    ['pvd', sql.Date],  purchase_applicable:     ['pap', sql.Bit],
    // engineering
    engineering_verified_dt: ['evd', sql.Date],  engineering_applicable:  ['eap', sql.Bit],
    // qa
    qa_verified_dt:          ['qad', sql.Date],  qa_applicable:           ['qaa', sql.Bit],
    // qc
    qc_verified_dt:          ['qcd', sql.Date],  qc_applicable:           ['qca', sql.Bit],
    // legal
    legal_verified_dt:       ['lvd', sql.Date],  legal_applicable:        ['lap', sql.Bit],
    // owner
    owner_approved_dt:       ['oad', sql.Date],  owner_applicable:        ['oap', sql.Bit],
    // plant
    plant_applicable:        ['pla', sql.Bit],
    // finance
    finance_verified_dt:     ['fvd', sql.Date],  finance_verified_by:     ['fvb', sql.NVarChar],
    finance_applicable:      ['fap', sql.Bit],
  };

  for (const [col, [param, type]] of Object.entries(map)) {
    if (data[col] !== undefined) {
      // Use ?? so 0/false are stored as-is (|| null would wrongly convert 0 → null for BIT columns)
      const v = data[col];
      r.input(param, type, (v !== null && v !== undefined && v !== '') ? v : null);
      sets.push(`${col}=@${param}`);
    }
  }
  if (data.team_members !== undefined) {
    r.input('tm', sql.NVarChar, JSON.stringify(data.team_members));
    sets.push('team_members=@tm');
  }
  if (data.attachments !== undefined) {
    r.input('att', sql.NVarChar, JSON.stringify(data.attachments));
    sets.push('attachments=@att');
  }
  await r.query(`UPDATE project_requests SET ${sets.join(',')} WHERE id=@id`);
}

// Approval step map: Purchase → Engineering → QA → QC → Legal → Owner → Plant → Finance
const STEP_MAP = [
  { key: 'purchase',    from: 'PENDING',     to: 'PURCHASE',    dtCol: 'purchase_verified_dt',    appCol: 'purchase_applicable' },
  { key: 'engineering', from: 'PURCHASE',    to: 'ENGINEERING', dtCol: 'engineering_verified_dt', appCol: 'engineering_applicable' },
  { key: 'qa',          from: 'ENGINEERING', to: 'QA_DONE',     dtCol: 'qa_verified_dt',          appCol: 'qa_applicable' },
  { key: 'qc',          from: 'QA_DONE',     to: 'QC_DONE',     dtCol: 'qc_verified_dt',          appCol: 'qc_applicable' },
  { key: 'legal',       from: 'QC_DONE',     to: 'LEGAL',       dtCol: 'legal_verified_dt',       appCol: 'legal_applicable' },
  { key: 'owner',       from: 'LEGAL',       to: 'OWNER',       dtCol: 'owner_approved_dt',       appCol: 'owner_applicable' },
  { key: 'plant',       from: 'OWNER',       to: 'PLANT',       dtCol: 'authorised_dt',           appCol: 'plant_applicable', nameCol: 'authorised_by' },
  { key: 'finance',     from: 'PLANT',       to: 'APPROVED',    dtCol: 'finance_verified_dt',     appCol: 'finance_applicable' },
];

async function stepApprove(id, { step, applicable, date, name }) {
  const p = await findById(id);
  if (!p) throw new Error('Project not found');
  const s = STEP_MAP.find(m => m.key === step);
  if (!s) throw new Error('Invalid approval step: ' + step);
  if (p.status !== s.from) throw new Error(`Expected status ${s.from}, current is ${p.status}`);
  const updates = { status: s.to };
  if (applicable && date) updates[s.dtCol] = date;
  if (!applicable && s.appCol) updates[s.appCol] = 0;
  if (s.nameCol && name) updates[s.nameCol] = name;
  await update(id, updates);

  // Auto-advance through any consecutive steps pre-marked as N/A (e.g. QA/QC not applicable in team)
// Auto-advance through any consecutive steps pre-marked as N/A
  for (let i = 0; i < STEP_MAP.length; i++) {
    const current = await findById(id);
    if (!current || current.status === 'APPROVED' || current.status === 'REJECTED') break;
    const next = STEP_MAP.find(m => m.from === current.status);
    if (!next) break;
    // If no appCol, step is always required — stop
    if (!next.appCol) break;
    const appVal = current[next.appCol];
    // SQL Server BIT returns as JS boolean (true/false) OR number (1/0).
    // null/undefined = never explicitly set = treat as applicable, do NOT skip.
    const isNotApplicable = (appVal === false || appVal === 0);
    if (isNotApplicable) {
      await update(id, { status: next.to });
    } else {
      break;
    }
  }
}

async function remove(id) {
  const pool = await getPool();
  await pool.request().input('id', sql.Int, parseInt(id))
    .query('DELETE FROM project_requests WHERE id=@id');
}

module.exports = { bootstrap, create, list, findById, update, remove, stepApprove, nextCode };