// services/sales/schema.js
// Idempotent DDL + seed for every Sales Order module table (ZSO_*), run once
// at server start (server.js bootstrap). New ERP-created orders/samples start
// at ID 50001 so they can never collide with migrated msale IDs (msale was at
// ~11.7k in 2026) — the ID is what goes into SAP ORDR.U_WEB_SO_NO.
'use strict';
const { query } = require('./db');
const { USER_TYPES, DEFAULT_DIVISIONS, DEFAULT_SETTINGS } = require('./constants');

const T = (name, body) => `IF OBJECT_ID('dbo.${name}','U') IS NULL CREATE TABLE dbo.${name} (${body})`;

const DDL = [
  T('ZSO_SETTINGS', `SKEY NVARCHAR(100) NOT NULL PRIMARY KEY, SVALUE NVARCHAR(MAX) NULL, UPDATED_AT DATETIME2 DEFAULT SYSDATETIME(), UPDATED_BY NVARCHAR(100) NULL`),
  T('ZSO_DIVISIONS', `ID INT NOT NULL PRIMARY KEY, NAME NVARCHAR(100) NOT NULL, CODE NVARCHAR(10) NULL, ITEM_GROUPS NVARCHAR(200) NULL,
    WAREHOUSE NVARCHAR(16) NULL, SORT INT DEFAULT 0, ACTIVE BIT DEFAULT 1`),
  T('ZSO_PRODUCTS', `ID INT IDENTITY(1,1) PRIMARY KEY, CATALOG NVARCHAR(10) NOT NULL DEFAULT 'domestic', DIVISION_ID INT NOT NULL,
    ITEM_CODE NVARCHAR(100) NOT NULL, DISPLAY_CODE NVARCHAR(100) NULL, DESCRIPTION NVARCHAR(400) NULL, SHORT_DESC NVARCHAR(400) NULL,
    PACK_SIZE INT DEFAULT 1, HSN NVARCHAR(50) NULL, UNIT_PRICE DECIMAL(19,4) DEFAULT 0, IS_INSTRUMENT BIT DEFAULT 0, ACTIVE BIT DEFAULT 1,
    LEGACY_ID INT NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), UPDATED_AT DATETIME2 NULL, UPDATED_BY NVARCHAR(100) NULL`),
  T('ZSO_USER_TYPES', `ID INT NOT NULL PRIMARY KEY, NAME NVARCHAR(100) NOT NULL, SCOPE NVARCHAR(10) NOT NULL DEFAULT 'all',
    LIST_STATUSES NVARCHAR(100) NULL, DEFAULT_STEPS NVARCHAR(MAX) NULL, DEFAULT_MODULES NVARCHAR(MAX) NULL, ACTIVE BIT DEFAULT 1`),
  T('ZSO_USER_PROFILES', `USER_ID INT NOT NULL PRIMARY KEY, USER_TYPE_ID INT NULL, DIVISIONS NVARCHAR(200) NULL, EMP_CODE NVARCHAR(30) NULL,
    TERRITORY NVARCHAR(150) NULL, CC_EMAILS NVARCHAR(500) NULL, LEGACY_EMP_ID INT NULL, UPDATED_AT DATETIME2 NULL, UPDATED_BY NVARCHAR(100) NULL`),
  T('ZSO_SALES_PERSONS', `ID INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(200) NOT NULL, EMAIL NVARCHAR(150) NULL, DESIGNATION NVARCHAR(100) NULL,
    DIVISIONS NVARCHAR(100) NULL, USER_ID INT NULL, ACTIVE BIT DEFAULT 1, LEGACY_ID INT NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME()`),
  T('ZSO_SP_CUSTOMER_MAP', `ID INT IDENTITY(1,1) PRIMARY KEY, SALES_PERSON_ID INT NOT NULL, CARD_CODE NVARCHAR(30) NOT NULL, ACTIVE BIT DEFAULT 1,
    CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), CREATED_BY NVARCHAR(100) NULL`),
  T('ZSO_CUSTOMERS', `CARD_CODE NVARCHAR(30) NOT NULL PRIMARY KEY, CARD_NAME NVARCHAR(200) NULL, EMAIL NVARCHAR(200) NULL, PASSWORD_HASH NVARCHAR(200) NULL,
    ACTIVE BIT DEFAULT 1, LOCKED BIT DEFAULT 0, LOGIN_ATTEMPTS INT DEFAULT 0, MUST_CHANGE_PWD BIT DEFAULT 0, LAST_LOGIN DATETIME2 NULL,
    CREDIT_LIMIT DECIMAL(19,2) DEFAULT 0, LEGACY_ID INT NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), UPDATED_AT DATETIME2 NULL, UPDATED_BY NVARCHAR(100) NULL`),
  T('ZSO_ORDERS', `ID INT IDENTITY(50001,1) PRIMARY KEY, REF_NO NVARCHAR(30) NULL, COMPANY NVARCHAR(100) NULL,
    SO_TYPE TINYINT NOT NULL, ORDER_TYPE NVARCHAR(12) NULL, CARD_CODE NVARCHAR(30) NOT NULL, CARD_NAME NVARCHAR(200) NULL, DIVISION_ID INT NOT NULL,
    CUST_ORDER_NO NVARCHAR(100) NULL, ORDER_DATE DATE NULL, DELIVERY_DATE DATE NULL, CONTACT_PERSON NVARCHAR(200) NULL, SALES_EMPLOYEE NVARCHAR(200) NULL,
    SHIP_TO_CODE NVARCHAR(100) NULL, SHIP_TO_TEXT NVARCHAR(1000) NULL, SHIP_STATE NVARCHAR(10) NULL, BILL_TO_CODE NVARCHAR(100) NULL, BILL_TO_TEXT NVARCHAR(1000) NULL,
    TAX_CODE NVARCHAR(30) NULL, IGST_RATE DECIMAL(9,3) DEFAULT 0, CGST_RATE DECIMAL(9,3) DEFAULT 0, SGST_RATE DECIMAL(9,3) DEFAULT 0, TCS_RATE DECIMAL(9,3) DEFAULT 0,
    IGST_VAL DECIMAL(19,2) DEFAULT 0, CGST_VAL DECIMAL(19,2) DEFAULT 0, SGST_VAL DECIMAL(19,2) DEFAULT 0, TCS_VAL DECIMAL(19,2) DEFAULT 0,
    SUB_TOTAL DECIMAL(19,2) DEFAULT 0, GRAND_TOTAL DECIMAL(19,2) DEFAULT 0, CREDIT_DAYS INT NULL,
    PAYMENT_MODE NVARCHAR(30) NULL, PAYMENT_REF NVARCHAR(150) NULL, PAYMENT_DATE DATE NULL, PAYMENT_DETAIL NVARCHAR(500) NULL,
    PAID_AMOUNT DECIMAL(19,2) NULL, TDS_VAL DECIMAL(19,2) DEFAULT 0, WALLET_USED DECIMAL(19,2) NULL, REBATE DECIMAL(19,2) NULL, PAID_STATUS NVARCHAR(30) NULL,
    CUSTOMER_REMARKS NVARCHAR(MAX) NULL, EMPLOYEE_REMARKS NVARCHAR(MAX) NULL, INTERNAL_REMARKS NVARCHAR(MAX) NULL,
    STATUS INT NOT NULL DEFAULT 1, IS_MULTI_CONSIGNEE BIT DEFAULT 0, NEEDS_REVIEW BIT DEFAULT 0,
    SAP_DOC_ENTRY INT NULL, SAP_DOC_NUM INT NULL, SAP_ORDER_DATE DATE NULL, SAP_POSTED_AT DATETIME2 NULL, SAP_POSTED_BY NVARCHAR(100) NULL, SAP_ERROR NVARCHAR(MAX) NULL,
    INVOICE_JSON NVARCHAR(MAX) NULL, LAST_SAP_SYNC DATETIME2 NULL,
    CREATED_BY_TYPE NVARCHAR(10) NULL, CREATED_BY NVARCHAR(100) NULL, CREATED_BY_NAME NVARCHAR(200) NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(),
    UPDATED_AT DATETIME2 NULL, UPDATED_BY_NAME NVARCHAR(200) NULL, LEGACY BIT DEFAULT 0`),
  T('ZSO_ORDER_ITEMS', `ID INT IDENTITY(1,1) PRIMARY KEY, ORDER_ID INT NOT NULL, LINE_NO INT NOT NULL, PRODUCT_ID INT NULL, ITEM_CODE NVARCHAR(100) NOT NULL,
    DISPLAY_CODE NVARCHAR(100) NULL, DESCRIPTION NVARCHAR(400) NULL, QTY DECIMAL(19,3) NOT NULL, NOP INT NULL, PRICE DECIMAL(19,4) DEFAULT 0,
    SPECIAL_PRICE DECIMAL(19,4) DEFAULT 0, COMMISSION DECIMAL(9,3) DEFAULT 0, HSN NVARCHAR(50) NULL, LINE_TOTAL DECIMAL(19,2) DEFAULT 0, ACTIVE BIT DEFAULT 1`),
  T('ZSO_ORDER_CONSIGNEES', `ID INT IDENTITY(1,1) PRIMARY KEY, ORDER_ID INT NOT NULL, SHIP_TO_CODE NVARCHAR(100) NULL, SHIP_TO_TEXT NVARCHAR(1000) NULL,
    SE_NAME NVARCHAR(200) NULL, SE_EMAIL NVARCHAR(200) NULL, ITEMS_JSON NVARCHAR(MAX) NULL, SAP_DOC_ENTRY INT NULL, SAP_DOC_NUM INT NULL, ACTIVE BIT DEFAULT 1`),
  T('ZSO_DOCS', `ID INT IDENTITY(1,1) PRIMARY KEY, ENTITY NVARCHAR(20) NOT NULL, ENTITY_ID INT NOT NULL, DOC_TYPE NVARCHAR(30) NOT NULL,
    TITLE NVARCHAR(300) NULL, FILE_NAME NVARCHAR(300) NOT NULL, ORIG_NAME NVARCHAR(300) NULL, ACTIVE BIT DEFAULT 1,
    CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), CREATED_BY_NAME NVARCHAR(200) NULL`),
  T('ZSO_LOG', `ID INT IDENTITY(1,1) PRIMARY KEY, ENTITY NVARCHAR(20) NOT NULL, ENTITY_ID INT NOT NULL, ACTION NVARCHAR(50) NOT NULL,
    FROM_STATUS INT NULL, TO_STATUS INT NULL, REMARKS NVARCHAR(MAX) NULL, ACTOR_TYPE NVARCHAR(10) NULL, ACTOR NVARCHAR(100) NULL,
    ACTOR_NAME NVARCHAR(200) NULL, AT DATETIME2 DEFAULT SYSDATETIME()`),
  T('ZSO_WALLET_TXN', `ID INT IDENTITY(1,1) PRIMARY KEY, CARD_CODE NVARCHAR(30) NOT NULL, TXN_TYPE NVARCHAR(10) NOT NULL, AMOUNT DECIMAL(19,2) NOT NULL,
    TDS DECIMAL(19,2) DEFAULT 0, STATUS NVARCHAR(12) NOT NULL, REF_TYPE NVARCHAR(15) NOT NULL, ORDER_ID INT NULL,
    PAYMENT_MODE NVARCHAR(30) NULL, PAYMENT_REF NVARCHAR(150) NULL, PAYMENT_DATE DATE NULL, PAYMENT_DETAIL NVARCHAR(500) NULL, REMARKS NVARCHAR(1000) NULL,
    BANK_API_ID NVARCHAR(50) NULL, DOC_ID INT NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), CREATED_BY_NAME NVARCHAR(200) NULL,
    DECIDED_AT DATETIME2 NULL, DECIDED_BY_NAME NVARCHAR(200) NULL`),
  T('ZSO_SAMPLES', `ID INT IDENTITY(50001,1) PRIMARY KEY, REF_NO NVARCHAR(30) NULL, CARD_CODE NVARCHAR(30) NOT NULL, CARD_NAME NVARCHAR(200) NULL,
    ADDRESS NVARCHAR(1000) NULL, BB_LICENCE_NO NVARCHAR(200) NULL, BB_NAME NVARCHAR(200) NULL, DIVISION_ID INT NOT NULL, REQ_DATE DATE NULL,
    DELIVERY_DAYS INT NULL, STATUS INT NOT NULL DEFAULT 1, INTERNAL_REMARKS NVARCHAR(MAX) NULL, MODIFICATION_REMARKS NVARCHAR(1000) NULL,
    REJECTION_REMARKS NVARCHAR(1000) NULL, STOCK_AVAILABLE BIT NULL, QA_DUE_DATE DATE NULL, QA_VERIFIED BIT NULL, OUT_OF_STOCK_DATE DATE NULL,
    CHALLAN_NO NVARCHAR(100) NULL, CHALLAN_DATE DATE NULL, COURIER_NO NVARCHAR(100) NULL, DISPATCH_INFO NVARCHAR(500) NULL, FEEDBACK NVARCHAR(1000) NULL,
    CREATED_BY INT NULL, CREATED_BY_NAME NVARCHAR(200) NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), UPDATED_AT DATETIME2 NULL,
    UPDATED_BY_NAME NVARCHAR(200) NULL, LEGACY BIT DEFAULT 0`),
  T('ZSO_SAMPLE_ITEMS', `ID INT IDENTITY(1,1) PRIMARY KEY, SAMPLE_ID INT NOT NULL, KIND NVARCHAR(10) NOT NULL DEFAULT 'domestic', PRODUCT_ID INT NULL,
    ITEM_CODE NVARCHAR(100) NOT NULL, DISPLAY_CODE NVARCHAR(100) NULL, DESCRIPTION NVARCHAR(400) NULL, QTY DECIMAL(19,3) NOT NULL, NOP INT NULL,
    BATCH_NO NVARCHAR(200) NULL, ACTIVE BIT DEFAULT 1`),
  T('ZSO_EXPORT_SAMPLES', `ID INT IDENTITY(50001,1) PRIMARY KEY, REF_NO NVARCHAR(30) NULL, REGION NVARCHAR(500) NULL, SAMPLE_AGAINST NVARCHAR(500) NULL,
    CUST_NAME NVARCHAR(200) NOT NULL, COUNTRY NVARCHAR(200) NULL, SHIPPING_ADDRESS NVARCHAR(1000) NULL, CONTACT_PERSON NVARCHAR(200) NULL,
    CONTACT_EMAIL NVARCHAR(200) NULL, CONTACT_NO NVARCHAR(100) NULL, ACCOUNT_DETAILS NVARCHAR(300) NULL, COURIER_BY_CUSTOMER BIT DEFAULT 0,
    DIVISION_ID INT NULL, REQ_DATE DATE NULL, DELIVERY_DATE DATE NULL, DELIVERY_DAYS INT NULL, REMARKS_QA NVARCHAR(1000) NULL,
    REMARKS_LOGISTICS NVARCHAR(500) NULL, STATUS INT NOT NULL DEFAULT 1, STOCK_AVAILABLE BIT NULL, QA_DUE_DATE DATE NULL, QA_VERIFIED BIT NULL,
    CHALLAN_NO NVARCHAR(100) NULL, CHALLAN_DATE DATE NULL, COURIER_NO NVARCHAR(100) NULL, DISPATCH_DETAILS NVARCHAR(500) NULL,
    CREATED_BY INT NULL, CREATED_BY_NAME NVARCHAR(200) NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), UPDATED_AT DATETIME2 NULL,
    UPDATED_BY_NAME NVARCHAR(200) NULL, LEGACY BIT DEFAULT 0`),
  // One row per SAP invoice already announced by email (never mail twice).
  T('ZSO_INVOICE_NOTICES', `ID INT IDENTITY(1,1) PRIMARY KEY, ORDER_ID INT NOT NULL, INVOICE_DOC_ENTRY INT NOT NULL, INVOICE_NO INT NULL,
    INVOICE_DATE DATE NULL, EMAILED BIT DEFAULT 0, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), CONSTRAINT UQ_ZSO_INV_NOTICE UNIQUE (ORDER_ID, INVOICE_DOC_ENTRY)`),
  T('ZSO_INVOICE_FILES', `ID INT IDENTITY(1,1) PRIMARY KEY, INVOICE_NO NVARCHAR(50) NOT NULL, FILE_NAME NVARCHAR(300) NOT NULL,
    UPLOADED_AT DATETIME2 DEFAULT SYSDATETIME()`),
];

const INDEXES = [
  ['IX_ZSO_ORDERS_CARD', 'ZSO_ORDERS(CARD_CODE, STATUS)'],
  ['IX_ZSO_ORDERS_STATUS', 'ZSO_ORDERS(STATUS, DIVISION_ID)'],
  ['IX_ZSO_ORDER_ITEMS_ORD', 'ZSO_ORDER_ITEMS(ORDER_ID)'],
  ['IX_ZSO_WALLET_CARD', 'ZSO_WALLET_TXN(CARD_CODE, STATUS)'],
  ['IX_ZSO_LOG_ENT', 'ZSO_LOG(ENTITY, ENTITY_ID)'],
  ['IX_ZSO_DOCS_ENT', 'ZSO_DOCS(ENTITY, ENTITY_ID)'],
  ['IX_ZSO_SPMAP_SP', 'ZSO_SP_CUSTOMER_MAP(SALES_PERSON_ID, CARD_CODE)'],
  ['IX_ZSO_PRODUCTS_DIV', 'ZSO_PRODUCTS(CATALOG, DIVISION_ID)'],
  ['IX_ZSO_SAMPLE_ITEMS_S', 'ZSO_SAMPLE_ITEMS(SAMPLE_ID, KIND)'],
];

async function bootstrap() {
  for (const ddl of DDL) await query(ddl);
  for (const [name, on] of INDEXES) {
    const table = on.split('(')[0];
    await query(`IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='${name}' AND object_id=OBJECT_ID('dbo.${table}')) CREATE INDEX ${name} ON dbo.${on}`);
  }
  // Seeds — only insert what's missing, never overwrite admin edits.
  for (const d of DEFAULT_DIVISIONS) {
    await query(`IF NOT EXISTS (SELECT 1 FROM dbo.ZSO_DIVISIONS WHERE ID=?) INSERT INTO dbo.ZSO_DIVISIONS (ID,NAME,CODE,ITEM_GROUPS,WAREHOUSE,SORT,ACTIVE) VALUES (?,?,?,?,?,?,1)`,
      [d.id, d.id, d.name, d.code, d.itemGroups, d.warehouse, d.sort]);
  }
  for (const u of USER_TYPES) {
    await query(`IF NOT EXISTS (SELECT 1 FROM dbo.ZSO_USER_TYPES WHERE ID=?) INSERT INTO dbo.ZSO_USER_TYPES (ID,NAME,SCOPE,LIST_STATUSES,DEFAULT_STEPS,DEFAULT_MODULES,ACTIVE) VALUES (?,?,?,?,?,?,1)`,
      [u.id, u.id, u.name, u.scope, u.listStatuses, JSON.stringify(u.steps), JSON.stringify(u.modules)]);
  }
  // Invoice emails start from the day the feature is first deployed — older
  // invoices are recorded silently so go-live doesn't send a backlog flood.
  await query(`IF NOT EXISTS (SELECT 1 FROM dbo.ZSO_SETTINGS WHERE SKEY='invoiceEmailSince') INSERT INTO dbo.ZSO_SETTINGS (SKEY,SVALUE) VALUES ('invoiceEmailSince',?)`,
    [JSON.stringify(new Date().toISOString().slice(0, 10))]);
  for (const [k, v] of Object.entries(DEFAULT_SETTINGS)) {
    await query(`IF NOT EXISTS (SELECT 1 FROM dbo.ZSO_SETTINGS WHERE SKEY=?) INSERT INTO dbo.ZSO_SETTINGS (SKEY,SVALUE) VALUES (?,?)`, [k, k, JSON.stringify(v)]);
  }
  console.log('[SALES] ✅ Sales Order module tables ready');
}

module.exports = { bootstrap };