// services/productionPlanningItemDefaultsStore.js
// Per-item-code master defaults for Production Planning: No. of Package,
// Per Cycle Qty, No. of Cycle/Day, Product Type — set once per SAP item
// code, then auto-filled into a new plan entry when that item is picked
// (see routes/productionPlanning.js's /item-defaults endpoints). "Prod. As
// per Single" is NOT stored here — it's always Quantity(required, typed
// per entry) × No. of Package, computed live in the plan entry itself.
const sql = require('mssql');

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

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');
}
function nowTs() { return new Date().toISOString().replace('T', ' ').substring(0, 23); }

async function bootstrap() {
  console.log('[PP-ITEM-DEFAULTS] Checking table', TABLE, '...');
  await exec(`
    CREATE TABLE ${TABLE} (
      ID              INT IDENTITY(1,1) PRIMARY KEY,
      ITEM_CODE       NVARCHAR(50) NOT NULL,
      ITEM_NAME       NVARCHAR(200),
      NO_OF_PACKAGE   FLOAT DEFAULT 0,
      PER_CYCLE_QTY   FLOAT DEFAULT 0,
      NO_OF_CYCLE_DAY FLOAT DEFAULT 0,
      PRODUCT_TYPE    NVARCHAR(50),
      CREATED_BY      NVARCHAR(50),
      CREATED_NAME    NVARCHAR(100),
      CREATED_AT      DATETIME2,
      UPDATED_BY      NVARCHAR(50),
      UPDATED_NAME    NVARCHAR(100),
      UPDATED_AT      DATETIME2,
      IS_DELETED      BIT DEFAULT 0
    )
  `).catch(e => { if (!isAlreadyExists(e)) throw e; });
  await exec(`IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ZPP_ITEM_DEFAULTS' AND COLUMN_NAME='PRODUCT_TYPE') ALTER TABLE ${TABLE} ADD [PRODUCT_TYPE] NVARCHAR(50)`).catch(() => {});
  console.log('[PP-ITEM-DEFAULTS] ✅ Ready');
}

function fromRow(row) {
  return {
    id: row.ID,
    itemCode: row.ITEM_CODE || '',
    itemName: row.ITEM_NAME || '',
    noOfPackage: row.NO_OF_PACKAGE || 0,
    perCycleQty: row.PER_CYCLE_QTY || 0,
    noOfCycleDay: row.NO_OF_CYCLE_DAY || 0,
    productType: row.PRODUCT_TYPE || '',
    createdBy: row.CREATED_BY || '',
    createdByName: row.CREATED_NAME || '',
    createdAt: row.CREATED_AT ? new Date(row.CREATED_AT).toISOString() : null,
    updatedBy: row.UPDATED_BY || '',
    updatedByName: row.UPDATED_NAME || '',
    updatedAt: row.UPDATED_AT ? new Date(row.UPDATED_AT).toISOString() : null,
    isDeleted: !!row.IS_DELETED,
  };
}

async function listDefaults() {
  const rows = await exec(`SELECT * FROM ${TABLE} WHERE IS_DELETED=0 ORDER BY ITEM_CODE ASC`);
  return rows.map(fromRow);
}

async function findByItemCode(itemCode) {
  const code = String(itemCode || '').trim().toUpperCase();
  if (!code) return null;
  const rows = await exec(`SELECT * FROM ${TABLE} WHERE IS_DELETED=0 AND UPPER(ITEM_CODE)=?`, [code]);
  return rows.length ? fromRow(rows[0]) : null;
}

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

// Create-or-update by item code — one active default row per item.
async function upsertDefault(p, { by, byName } = {}) {
  const itemCode = String(p.itemCode || '').trim().toUpperCase();
  if (!itemCode) throw new Error('Item Code is required');
  const existing = await findByItemCode(itemCode);
  const now = nowTs();
  if (existing) {
    await exec(`
      UPDATE ${TABLE} SET
        ITEM_NAME=?, NO_OF_PACKAGE=?, PER_CYCLE_QTY=?, NO_OF_CYCLE_DAY=?, PRODUCT_TYPE=?,
        UPDATED_BY=?, UPDATED_NAME=?, UPDATED_AT=?
      WHERE ID=?
    `, [
      p.itemName || existing.itemName, parseFloat(p.noOfPackage) || 0,
      parseFloat(p.perCycleQty) || 0, parseFloat(p.noOfCycleDay) || 0, p.productType || '',
      by || '', byName || by || '', now, existing.id,
    ]);
    return findById(existing.id);
  }
  const idRows = await exec(`
    INSERT INTO ${TABLE} (
      ITEM_CODE, ITEM_NAME, NO_OF_PACKAGE, PER_CYCLE_QTY, NO_OF_CYCLE_DAY, PRODUCT_TYPE,
      CREATED_BY, CREATED_NAME, CREATED_AT
    ) VALUES (?,?,?,?,?,?,?,?,?);
    SELECT SCOPE_IDENTITY() AS ID;
  `, [
    itemCode, p.itemName || '', parseFloat(p.noOfPackage) || 0,
    parseFloat(p.perCycleQty) || 0, parseFloat(p.noOfCycleDay) || 0, p.productType || '',
    by || '', byName || by || '', now,
  ]);
  return findById(idRows[0].ID);
}

async function softDeleteDefault(id) {
  await exec(`UPDATE ${TABLE} SET IS_DELETED=1 WHERE ID=?`, [parseInt(id)]);
}

module.exports = {
  bootstrap, listDefaults, findByItemCode, findById, upsertDefault, softDeleteDefault,
};