import {
  getSettingsMap,
  listCustomers,
  createCustomer,
  updateCustomerById,
  deleteCustomerById,
  listSuppliers,
  createSupplier,
  updateSupplierById,
  deleteSupplierById,
  listMaterials,
  listMaterialsForEditor,
  listMaterialsForEditorByIds,
  getMaterialById,
  createMaterial,
  updateMaterialById,
  deleteMaterialById,
  listExpenses,
  createExpense,
  updateExpenseById,
  deleteExpenseById,
  listProductShapeDefinitions,
  createProductShapeDefinition,
  updateProductShapeDefinitionById,
  deleteProductShapeDefinitionById,
  listSalesReps,
  createSalesRep,
  updateSalesRepById,
  deleteSalesRepById,
  listManagedUnits,
  createManagedUnit,
  updateManagedUnitById,
  deleteManagedUnitById,
  listItemGroups,
  createItemGroup,
  updateItemGroupById,
  deleteItemGroupById,
  listWarehouses,
  listActiveWarehouses,
  createWarehouse,
  updateWarehouseById,
  deleteWarehouseById,
  listBanks,
  createBank,
  updateBankById,
  deleteBankById,
  getBankByIdFromPg,
  listCustomerPricing,
  setCustomerPricingByCategory,
  upsertItemUnits,
  createItemUnit,
  listItemUnits,
  upsertInternalTransfers,
  listInternalTransfers,
  upsertInventoryMovements,
  listInventoryMovements,
  upsertCustomerCategories,
  createCustomerCategory,
  deleteCustomerCategoryById,
  listCustomerCategories,
  upsertParties,
  listParties,
  upsertChartOfAccounts,
  listChartOfAccounts,
  upsertChecksIssued,
  listChecksIssued,
  upsertChecksReceived,
  listChecksReceived,
  upsertCurrencies,
  listCurrencies,
  upsertDailyExchangeRates,
  listDailyExchangeRates,
  upsertEmployees,
  listEmployees,
  getEmployeeById,
  upsertExchangeRateSources,
  listExchangeRateSources,
  upsertInternalTransferDocs,
  listInternalTransferDocs,
  upsertJournalEntries,
  listJournalEntries,
  getJournalEntryById,
  deleteJournalEntryById,
  upsertPayments,
  listPayments,
  upsertProductionReports,
  listProductionReports,
  upsertPurchaseCreditNotes,
  listPurchaseCreditNotes,
  upsertPurchaseReturns,
  listPurchaseReturns,
  upsertShiftOverrides,
  listShiftOverrides,
  upsertShipmentSequences,
  listShipmentSequences,
  upsertShipments,
  listShipments,
  upsertSupplierPaymentLocks,
  listSupplierPaymentLocks,
  upsertSupplierPayments,
  listSupplierPayments,
  upsertTransferLocks,
  listTransferLocks,
  upsertSequenceCounters,
  listSequenceCounters,
  listInvoices,
  listPurchaseOrders,
  upsertCustomers,
  upsertSuppliers,
  upsertMaterials,
  upsertExpenses,
  upsertProductShapeDefinitions,
  upsertSalesReps,
  upsertManagedUnits,
  upsertItemGroups,
  upsertWarehouses,
  upsertBanks,
  upsertCustomerPricing,
  upsertInvoices,
  upsertPurchaseOrders,
  upsertSettings,
  getJsonStore,
  type PgPagination,
  upsertJsonStore,
  acquireDocumentLock,
  checkDocumentLock,
  releaseDocumentLock,
  cleanupExpiredLocks,
  type DocumentLock,
} from './repositories';
import { getPgPool, pgQuery } from './client';
import { randomUUID } from 'crypto';
import type { Campaign, CampaignStatus, CampaignType, CheckReturnEvent, Coupon, GeneralBank, PurchaseCreditNote } from '../types';

function createPgId(prefix: string) {
  return `${prefix}-${randomUUID()}`;
}

async function loadPgGeneralBanks(): Promise<GeneralBank[]> {
  const stored = await pgGetJsonStore<GeneralBank[]>('general_banks.json');
  return Array.isArray(stored) ? stored : [];
}

export async function pgGetSettings() {
  return getSettingsMap();
}

export async function pgSaveSettings(settings: Record<string, unknown>) {
  await upsertSettings(settings);
}

export async function pgGetCustomers(params?: PgPagination) {
  return listCustomers(params);
}

export async function pgSaveCustomers(customers: Array<Record<string, unknown>>) {
  await upsertCustomers(customers);
}

export async function pgCreateCustomer(customer: Record<string, unknown>) {
  return createCustomer(customer);
}

export async function pgUpdateCustomer(id: string, patch: Record<string, unknown>) {
  return updateCustomerById(id, patch);
}

export async function pgDeleteCustomer(id: string) {
  return deleteCustomerById(id);
}

export async function pgGetSuppliers(params?: PgPagination) {
  return listSuppliers(params);
}

export async function pgSaveSuppliers(suppliers: Array<Record<string, unknown>>) {
  await upsertSuppliers(suppliers);
}

export async function pgCreateSupplier(supplier: Record<string, unknown>) {
  return createSupplier(supplier);
}

export async function pgUpdateSupplier(id: string, patch: Record<string, unknown>) {
  return updateSupplierById(id, patch);
}

export async function pgDeleteSupplier(id: string) {
  return deleteSupplierById(id);
}

export async function pgGetMaterials(params?: PgPagination) {
  return listMaterials(params);
}

export async function pgGetMaterialsForEditor(params?: { pageSize?: number; search?: string; excludeId?: string }) {
  return listMaterialsForEditor(params);
}

export async function pgGetMaterialsForEditorByIds(ids: string[], params?: { excludeId?: string }) {
  return listMaterialsForEditorByIds(ids, params);
}

export async function pgGetMaterialById(id: string) {
  return getMaterialById(id);
}

/**
 * Calculate stock per material from inventory_movements, excluding movements
 * that belong to cancelled invoices.
 * - Materials with movements → show net qty (0 = نفذ, positive = available)
 * - Materials with NO movements at all → null (treated as unlimited in store)
 */
export async function pgGetMaterialsStock(materialIds?: string[]): Promise<Record<string, number | null>> {
  const filterClause =
    materialIds && materialIds.length > 0
      ? `AND im.material_id = ANY($1::text[])`
      : '';
  const params = materialIds && materialIds.length > 0 ? [materialIds] : [];

  // Sum movements, but exclude any movement linked to a cancelled invoice
  const result = await pgQuery<{ material_id: string; net_qty: string }>(
    `SELECT
       im.material_id,
       COALESCE(SUM(im.quantity_in), 0) - COALESCE(SUM(im.quantity_out), 0) AS net_qty
     FROM inventory_movements im
     LEFT JOIN invoices inv ON im.invoice_id = inv.id
     WHERE (inv.id IS NULL OR inv.raw->>'status' != 'cancelled')
     ${filterClause}
     GROUP BY im.material_id`,
    params
  );

  const filterSet = materialIds && materialIds.length > 0 ? new Set(materialIds) : null;
  const map: Record<string, number | null> = {};

  for (const row of result.rows) {
    if (filterSet && !filterSet.has(row.material_id)) continue;
    // Materials with movements get a real qty (0 = نفذ). null = no movements = unlimited.
    map[row.material_id] = Math.max(0, Number(row.net_qty || 0));
  }

  return map;
}

export async function pgGetMaterialStockLocations(materialIds?: string[]): Promise<Record<string, Array<{ warehouseId: string; shelfId: string; quantity: number }>>> {
  const filterClause =
    materialIds && materialIds.length > 0
      ? `AND im.material_id = ANY($1::text[])`
      : '';
  const params = materialIds && materialIds.length > 0 ? [materialIds] : [];

  const result = await pgQuery<{
    material_id: string;
    warehouse_id: string | null;
    shelf_id: string | null;
    net_qty: string;
  }>(
    `SELECT
       im.material_id,
       im.warehouse_id,
       im.shelf_id,
       COALESCE(SUM(im.quantity_in), 0) - COALESCE(SUM(im.quantity_out), 0) AS net_qty
     FROM inventory_movements im
     LEFT JOIN invoices inv ON im.invoice_id = inv.id
     WHERE (inv.id IS NULL OR inv.raw->>'status' != 'cancelled')
     ${filterClause}
     GROUP BY im.material_id, im.warehouse_id, im.shelf_id`,
    params
  );

  const map: Record<string, Array<{ warehouseId: string; shelfId: string; quantity: number }>> = {};
  for (const row of result.rows) {
    const warehouseId = String(row.warehouse_id || '').trim();
    const shelfId = String(row.shelf_id || '').trim();
    const quantity = Number(row.net_qty || 0);
    if (!warehouseId || !shelfId || quantity <= 0) continue;
    if (!map[row.material_id]) {
      map[row.material_id] = [];
    }
    map[row.material_id].push({ warehouseId, shelfId, quantity });
  }

  return map;
}

export async function pgSaveMaterials(materials: Array<Record<string, unknown>>) {
  await upsertMaterials(materials);
}

export async function pgCreateMaterial(material: Record<string, unknown>) {
  return createMaterial(material);
}

export async function pgUpdateMaterial(id: string, patch: Record<string, unknown>) {
  return updateMaterialById(id, patch);
}

export async function pgDeleteMaterial(id: string) {
  return deleteMaterialById(id);
}

export async function pgGetExpenses(params?: PgPagination) {
  return listExpenses(params);
}

export async function pgSaveExpenses(items: Array<Record<string, unknown>>) {
  await upsertExpenses(items);
}

export async function pgCreateExpense(item: Record<string, unknown>) {
  return createExpense(item);
}

export async function pgUpdateExpense(id: string, patch: Record<string, unknown>) {
  return updateExpenseById(id, patch);
}

export async function pgDeleteExpense(id: string) {
  return deleteExpenseById(id);
}

export async function pgGetProductShapeDefinitions(params?: PgPagination) {
  return listProductShapeDefinitions(params);
}

export async function pgSaveProductShapeDefinitions(items: Array<Record<string, unknown>>) {
  await upsertProductShapeDefinitions(items);
}

export async function pgCreateProductShapeDefinition(item: Record<string, unknown>) {
  return createProductShapeDefinition(item);
}

export async function pgUpdateProductShapeDefinition(id: string, patch: Record<string, unknown>) {
  return updateProductShapeDefinitionById(id, patch);
}

export async function pgCreateProductShapeDefinitions(items: Array<Record<string, unknown>>) {
  const created: Record<string, unknown>[] = [];
  for (const item of items) {
    const result = await createProductShapeDefinition(item);
    created.push(result);
  }
  return created;
}

export async function pgBulkCreateProductShapeDefinitions(rawItems: Array<Record<string, unknown>>) {
  return pgCreateProductShapeDefinitions(rawItems);
}

export async function pgDeleteProductShapeDefinition(id: string) {
  return deleteProductShapeDefinitionById(id);
}

export async function pgGetSalesReps(params?: PgPagination) {
  return listSalesReps(params);
}

export async function pgSaveSalesReps(items: Array<Record<string, unknown>>) {
  await upsertSalesReps(items);
}

export async function pgCreateSalesRep(item: Record<string, unknown>) {
  return createSalesRep(item);
}

export async function pgUpdateSalesRep(id: string, patch: Record<string, unknown>) {
  return updateSalesRepById(id, patch);
}

export async function pgDeleteSalesRep(id: string) {
  return deleteSalesRepById(id);
}

export async function pgGetManagedUnits(params?: PgPagination) {
  return listManagedUnits(params);
}

export async function pgSaveManagedUnits(items: Array<Record<string, unknown>>) {
  await upsertManagedUnits(items);
}

export async function pgCreateManagedUnit(item: Record<string, unknown>) {
  return createManagedUnit(item);
}

export async function pgUpdateManagedUnit(id: string, patch: Record<string, unknown>) {
  return updateManagedUnitById(id, patch);
}

export async function pgDeleteManagedUnit(id: string) {
  return deleteManagedUnitById(id);
}

export async function pgGetItemGroups(params?: PgPagination) {
  return listItemGroups(params);
}

export async function pgSaveItemGroups(items: Array<Record<string, unknown>>) {
  await upsertItemGroups(items);
}

export async function pgCreateItemGroup(item: Record<string, unknown>) {
  return createItemGroup(item);
}

export async function pgUpdateItemGroup(id: string, patch: Record<string, unknown>) {
  return updateItemGroupById(id, patch);
}

export async function pgDeleteItemGroup(id: string) {
  return deleteItemGroupById(id);
}

export async function pgGetWarehouses(params?: PgPagination) {
  return listWarehouses(params);
}

export async function pgGetActiveWarehouses() {
  return listActiveWarehouses();
}

export async function pgSaveWarehouses(items: Array<Record<string, unknown>>) {
  await upsertWarehouses(items);
}

export async function pgCreateWarehouse(item: Record<string, unknown>) {
  return createWarehouse(item);
}

export async function pgUpdateWarehouse(id: string, patch: Record<string, unknown>) {
  return updateWarehouseById(id, patch);
}

export async function pgDeleteWarehouse(id: string) {
  return deleteWarehouseById(id);
}

export async function pgGetBanks(params?: PgPagination) {
  return listBanks(params);
}

export async function pgSaveBanks(items: Array<Record<string, unknown>>) {
  await upsertBanks(items);
}

export async function pgCreateBank(item: Record<string, unknown>) {
  return createBank(item);
}

export async function pgUpdateBank(id: string, patch: Record<string, unknown>) {
  return updateBankById(id, patch);
}

export async function pgDeleteBank(id: string) {
  return deleteBankById(id);
}

export async function pgGetBankById(id: string) {
  return getBankByIdFromPg(id);
}

// --- General Banks (PostgreSQL json-store compatibility) ---
export async function pgGetGeneralBanks(params?: PgPagination) {
  return loadPgGeneralBanks();
}

export async function pgGetGeneralBankById(id: string) {
  const banks = await loadPgGeneralBanks();
  return banks.find((bank) => String(bank.id || '').trim() === String(id || '').trim()) || null;
}

export async function pgGetGeneralBanksByCountry(country: string) {
  const normalizedCountry = String(country || '').trim().toLowerCase();
  const banks = await loadPgGeneralBanks();
  return banks.filter((bank) => {
    const bankCountry = String(bank.country || '').trim().toLowerCase();
    return bankCountry === normalizedCountry && bank.isActive !== false;
  });
}

export async function pgAddGeneralBank(
  bank: Record<string, unknown>
) {
  const banks = await loadPgGeneralBanks();
  const normalizedName = String(bank.name || '').trim();
  const normalizedNameEn = String(bank.nameEn || normalizedName).trim();
  const normalizedCode = String(bank.code || '').trim().toUpperCase();

  if (!normalizedCode) {
    throw new Error('GENERAL_BANK_CODE_REQUIRED');
  }

  const hasCodeConflict = banks.some(
    (existing) => String(existing.code || '').trim().toUpperCase() === normalizedCode
  );
  if (hasCodeConflict) {
    throw new Error('GENERAL_BANK_CODE_EXISTS');
  }

  const newBank: GeneralBank = {
    ...(bank as Partial<GeneralBank>),
    id: String(bank.id || createPgId('gbank')),
    code: normalizedCode,
    name: normalizedName,
    nameEn: normalizedNameEn,
    accountId: String(bank.accountId || '').trim() || undefined,
    accountCode: String(bank.accountCode || '').trim() || undefined,
    swiftCode: String(bank.swiftCode || '').trim() || undefined,
    branchCode: String(bank.branchCode || '').trim() || undefined,
    branch: String(bank.branch || '').trim() || undefined,
    country: String(bank.country || '').trim() || undefined,
    city: String(bank.city || '').trim() || undefined,
    address: String(bank.address || '').trim() || undefined,
    phone: String(bank.phone || '').trim() || undefined,
    email: String(bank.email || '').trim() || undefined,
    ibanPattern: String(bank.ibanPattern || '').trim() || undefined,
    isActive: bank.isActive !== false,
    notes: String(bank.notes || '').trim() || undefined,
    createdAt: String(bank.createdAt || new Date().toISOString()),
    createdBy: String(bank.createdBy || 'system'),
    updatedAt: String(bank.updatedAt || '') || undefined,
  };

  await pgSaveJsonStore('general_banks.json', [...banks, newBank]);
  return newBank;
}

export async function pgUpdateGeneralBank(
  id: string,
  updates: Record<string, unknown>
) {
  const banks = await loadPgGeneralBanks();
  const bankIndex = banks.findIndex((bank) => String(bank.id || '').trim() === String(id || '').trim());
  if (bankIndex === -1) return null;

  const merged = { ...banks[bankIndex], ...(updates as Partial<GeneralBank>) };
  const normalizedCode = String(merged.code || '').trim().toUpperCase();

  const hasCodeConflict = banks.some(
    (existing, index) => index !== bankIndex && String(existing.code || '').trim().toUpperCase() === normalizedCode
  );
  if (hasCodeConflict) {
    throw new Error('GENERAL_BANK_CODE_EXISTS');
  }

  const nextBank: GeneralBank = {
    ...merged,
    id: String(banks[bankIndex].id),
    code: normalizedCode,
    name: String(merged.name || '').trim(),
    nameEn: String(merged.nameEn || merged.name || '').trim(),
    accountId: String(merged.accountId || '').trim() || undefined,
    accountCode: String(merged.accountCode || '').trim() || undefined,
    swiftCode: String(merged.swiftCode || '').trim() || undefined,
    branchCode: String(merged.branchCode || '').trim() || undefined,
    branch: String(merged.branch || '').trim() || undefined,
    country: String(merged.country || '').trim() || undefined,
    city: String(merged.city || '').trim() || undefined,
    address: String(merged.address || '').trim() || undefined,
    phone: String(merged.phone || '').trim() || undefined,
    email: String(merged.email || '').trim() || undefined,
    ibanPattern: String(merged.ibanPattern || '').trim() || undefined,
    isActive: merged.isActive !== false,
    notes: String(merged.notes || '').trim() || undefined,
    updatedAt: new Date().toISOString(),
  };

  const nextBanks = [...banks];
  nextBanks[bankIndex] = nextBank;
  await pgSaveJsonStore('general_banks.json', nextBanks);
  return nextBank;
}

export async function pgDeleteGeneralBank(id: string) {
  const banks = await loadPgGeneralBanks();
  const nextBanks = banks.filter((bank) => String(bank.id || '').trim() !== String(id || '').trim());
  if (nextBanks.length === banks.length) return false;
  await pgSaveJsonStore('general_banks.json', nextBanks);
  return true;
}

export async function pgGetCustomerPricing(params?: PgPagination) {
  return listCustomerPricing(params);
}

export async function pgSaveCustomerPricing(items: Array<Record<string, unknown>>) {
  await upsertCustomerPricing(items);
}

export async function pgSetCustomerPricing(item: Record<string, unknown>) {
  return setCustomerPricingByCategory(item);
}

export async function pgGetInvoices(params?: PgPagination) {
  return listInvoices(params);
}

export async function pgSaveInvoices(invoices: Array<Record<string, unknown>>) {
  await upsertInvoices(invoices);
}

export async function pgGetPurchaseOrders(params?: PgPagination) {
  return listPurchaseOrders(params);
}

export async function pgSavePurchaseOrders(orders: Array<Record<string, unknown>>) {
  await upsertPurchaseOrders(orders);
}

// ========== Wave 5: Item Units ==========
export async function pgGetItemUnits(params?: PgPagination) {
  return listItemUnits(params);
}

export async function pgSaveItemUnits(items: Array<Record<string, unknown>>) {
  await upsertItemUnits(items);
}

export async function pgCreateItemUnit(item: Record<string, unknown>) {
  return createItemUnit(item);
}

// ========== Wave 6: Internal Transfers ==========
export async function pgGetInternalTransfers(params?: PgPagination) {
  return listInternalTransfers(params);
}

export async function pgSaveInternalTransfers(items: Array<Record<string, unknown>>) {
  await upsertInternalTransfers(items);
}

// ========== Wave 7: Inventory Movements ==========
export async function pgGetInventoryMovements(params?: PgPagination) {
  return listInventoryMovements(params);
}

export async function pgSaveInventoryMovements(items: Array<Record<string, unknown>>) {
  await upsertInventoryMovements(items);
}

// ========== Wave 8: Customer Categories ==========
export async function pgGetCustomerCategories(params?: PgPagination) {
  return listCustomerCategories(params);
}

export async function pgSaveCustomerCategories(items: Array<Record<string, unknown>>) {
  await upsertCustomerCategories(items);
}

export async function pgCreateCustomerCategory(item: Record<string, unknown>) {
  return createCustomerCategory(item);
}

export async function pgDeleteCustomerCategory(id: string) {
  return deleteCustomerCategoryById(id);
}

// ========== Wave 9: Parties ==========
export async function pgGetParties(params?: PgPagination) {
  return listParties(params);
}

export async function pgSaveParties(items: Array<Record<string, unknown>>) {
  await upsertParties(items);
}

// ========== Wave 10: Remaining Legacy Entities ==========
export async function pgGetChartOfAccounts(params?: PgPagination) {
  return listChartOfAccounts(params);
}

export async function pgSaveChartOfAccounts(payload: unknown) {
  await upsertChartOfAccounts(payload);
}

export async function pgGetChecksIssued(params?: PgPagination) {
  return listChecksIssued(params);
}

export async function pgSaveChecksIssued(payload: unknown) {
  await upsertChecksIssued(payload);
}

export async function pgGetChecksReceived(params?: PgPagination) {
  return listChecksReceived(params);
}

export async function pgSaveChecksReceived(payload: unknown) {
  await upsertChecksReceived(payload);
}

export async function pgGetCurrencies(params?: PgPagination) {
  return listCurrencies(params);
}

export async function pgSaveCurrencies(payload: unknown) {
  await upsertCurrencies(payload);
}

export async function pgGetDailyExchangeRates(params?: PgPagination) {
  return listDailyExchangeRates(params);
}

export async function pgSaveDailyExchangeRates(payload: unknown) {
  await upsertDailyExchangeRates(payload);
}

export async function pgGetCurrencyByCode(code: string) {
  const normalizedCode = String(code || '').trim().toUpperCase();
  if (!normalizedCode) return null;
  const result = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM currencies WHERE UPPER(COALESCE(raw->>'code', '')) = $1 LIMIT 1`,
    [normalizedCode]
  );
  return result.rows[0]?.raw || null;
}

export async function pgGetDefaultCurrency() {
  const result = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM currencies WHERE COALESCE(raw->>'isDefault', 'false') = 'true' ORDER BY updated_at DESC LIMIT 1`
  );
  return result.rows[0]?.raw || null;
}

export async function pgCreateCurrency(currency: Record<string, unknown>) {
  const normalizedCode = String(currency.code || '').trim().toUpperCase();
  if (!normalizedCode) {
    throw new Error('CURRENCY_CODE_REQUIRED');
  }

  const existingWithCode = await pgGetCurrencyByCode(normalizedCode);
  if (existingWithCode) {
    throw new Error('CURRENCY_CODE_EXISTS');
  }

  const providedRef = String(currency.currencyNumber || '').trim().toUpperCase();
  const existingCurrencies = await pgGetCurrencies({ page: 1, pageSize: 5000 });
  const existingRefs = new Set(
    existingCurrencies.items
      .map((entry) => String((entry as any)?.currencyNumber || '').trim().toUpperCase())
      .filter((value) => value.length > 0)
  );

  if (providedRef && existingRefs.has(providedRef)) {
    throw new Error('CURRENCY_REFERENCE_EXISTS');
  }

  const now = new Date().toISOString();
  let nextRef = `CUR-${String(await pgNextNamedSequence('currency-ref')).padStart(4, '0')}`;
  while (existingRefs.has(nextRef)) {
    nextRef = `CUR-${String(await pgNextNamedSequence('currency-ref')).padStart(4, '0')}`;
  }
  const newCurrency: Record<string, unknown> = {
    ...currency,
    code: normalizedCode,
    currencyNumber: providedRef || nextRef,
    name: String(currency.name || '').trim(),
    nameEn: String(currency.nameEn || '').trim(),
    symbol: String(currency.symbol || '').trim(),
    id: String(currency.id || createPgId('cur')),
    createdAt: currency.createdAt || now,
    lastUpdatedAt: now,
    createdBy: currency.createdBy || 'system',
  };

  if (newCurrency.isDefault === true) {
    const existing = await pgGetCurrencies({ page: 1, pageSize: 5000 });
    const reset = existing.items.map((entry) => ({ ...entry, isDefault: false }));
    await pgSaveCurrencies(reset);
  }

  await pgSaveCurrencies([newCurrency]);
  return newCurrency;
}

export async function pgUpdateCurrencyByCode(code: string, updates: Record<string, unknown>) {
  const current = await pgGetCurrencyByCode(code);
  if (!current) return null;

  if (updates.isDefault === true) {
    const existing = await pgGetCurrencies({ page: 1, pageSize: 5000 });
    const reset = existing.items
      .filter((entry) => entry.id !== current.id)
      .map((entry) => ({ ...entry, isDefault: false }));
    await pgSaveCurrencies(reset);
  }

  const merged = {
    ...current,
    ...updates,
    id: current.id,
    lastUpdatedAt: new Date().toISOString(),
  };
  await pgSaveCurrencies([merged]);
  return merged;
}

export async function pgDeleteCurrencyByCode(code: string) {
  const current = await pgGetCurrencyByCode(code);
  if (!current) return false;
  if (current.isDefault === true || current.isDefault === 'true') return false;
  const result = await pgQuery('DELETE FROM currencies WHERE id = $1', [String(current.id)]);
  return (result.rowCount || 0) > 0;
}

function normalizePgRateDate(date: string | Date) {
  const value = typeof date === 'string' ? new Date(date) : date;
  return value.toISOString().slice(0, 10);
}

export async function pgFindDailyExchangeRate(baseCurrency: string, currency: string, date: string | Date) {
  const targetDate = normalizePgRateDate(date);
  const result = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM daily_exchange_rates
     WHERE raw->>'baseCurrency' = $1 AND raw->>'currency' = $2 AND raw->>'date' = $3
     LIMIT 1`,
    [baseCurrency, currency, targetDate]
  );
  return result.rows[0]?.raw || null;
}

export async function pgUpsertDailyExchangeRate(params: {
  baseCurrency: string;
  currency: string;
  rate: number;
  date: string | Date;
  source?: string;
  isAuto?: boolean;
}) {
  const { baseCurrency, currency, rate, date, source, isAuto } = params;
  const targetDate = normalizePgRateDate(date);
  const now = new Date().toISOString();
  const existing = await pgFindDailyExchangeRate(baseCurrency, currency, targetDate);
  const entry = {
    id: existing?.id || createPgId('fx'),
    baseCurrency,
    currency,
    rate,
    date: targetDate,
    source,
    isAuto,
    createdAt: existing?.createdAt || now,
  };
  await pgSaveDailyExchangeRates([entry]);
  return entry;
}

export async function pgGetExchangeRate(fromCurrency: string, toCurrency: string) {
  const fromCurr = await pgGetCurrencyByCode(fromCurrency);
  const toCurr = await pgGetCurrencyByCode(toCurrency);
  if (!fromCurr || !toCurr) return 1;
  return Math.round((Number(toCurr.exchangeRate || 1) / Number(fromCurr.exchangeRate || 1)) * 10000) / 10000;
}

export async function pgGetEmployees(params?: PgPagination) {
  const result = await listEmployees(params);
  const items = (result.items || []).filter((employee) => {
    return !(employee as Record<string, unknown>)._deleted;
  });
  return {
    ...result,
    items,
    totalItems: items.length,
    totalPages: Math.max(1, Math.ceil(items.length / (result.pageSize || 1))),
  };
}

export async function pgGetEmployeeById(id: string) {
  const employee = await getEmployeeById(id);
  if (!employee) return null;
  if ((employee as Record<string, unknown>)._deleted) return null;
  return employee;
}

export async function pgSaveEmployees(payload: unknown) {
  await upsertEmployees(payload);
}

export async function pgGetExchangeRateSources(params?: PgPagination) {
  return listExchangeRateSources(params);
}

export async function pgSaveExchangeRateSources(payload: unknown) {
  await upsertExchangeRateSources(payload);
}

export async function pgGetInternalTransferDocs(params?: PgPagination) {
  return listInternalTransferDocs(params);
}

export async function pgSaveInternalTransferDocs(payload: unknown) {
  await upsertInternalTransferDocs(payload);
}

export async function pgGetJournalEntries(params?: PgPagination) {
  return listJournalEntries(params);
}

export async function pgGetJournalEntryById(id: string) {
  return getJournalEntryById(id);
}

export async function pgSaveJournalEntries(payload: unknown) {
  await upsertJournalEntries(payload);
}

export async function pgGetPayments(params?: PgPagination) {
  return listPayments(params);
}

export async function pgSavePayments(payload: unknown) {
  await upsertPayments(payload);
}

export async function pgGetProductionReports(params?: PgPagination) {
  return listProductionReports(params);
}

export async function pgSaveProductionReports(payload: unknown) {
  await upsertProductionReports(payload);
}

export async function pgGetPurchaseCreditNotes(params?: PgPagination) {
  return listPurchaseCreditNotes(params);
}

export async function pgSavePurchaseCreditNotes(payload: unknown) {
  await upsertPurchaseCreditNotes(payload);
}

export async function pgGetPurchaseReturns(params?: PgPagination) {
  return listPurchaseReturns(params);
}

export async function pgSavePurchaseReturns(payload: unknown) {
  await upsertPurchaseReturns(payload);
}

export async function pgGetShiftOverrides(params?: PgPagination) {
  return listShiftOverrides(params);
}

export async function pgSaveShiftOverrides(payload: unknown) {
  await upsertShiftOverrides(payload);
}

export async function pgGetShipmentSequences(params?: PgPagination) {
  return listShipmentSequences(params);
}

export async function pgSaveShipmentSequences(payload: unknown) {
  await upsertShipmentSequences(payload);
}

export async function pgGetShipments(params?: PgPagination) {
  return listShipments(params);
}

export async function pgSaveShipments(payload: unknown) {
  await upsertShipments(payload);
}

export async function pgGetSupplierPaymentLocks(params?: PgPagination) {
  return listSupplierPaymentLocks(params);
}

export async function pgSaveSupplierPaymentLocks(payload: unknown) {
  await upsertSupplierPaymentLocks(payload);
}

export async function pgGetSupplierPayments(params?: PgPagination) {
  return listSupplierPayments(params);
}

export async function pgSaveSupplierPayments(payload: unknown) {
  await upsertSupplierPayments(payload);
}

export async function pgGetTransferLocks(params?: PgPagination) {
  return listTransferLocks(params);
}

export async function pgSaveTransferLocks(payload: unknown) {
  await upsertTransferLocks(payload);
}

export async function pgGetSequenceCounters(params?: PgPagination) {
  return listSequenceCounters(params);
}

export async function pgSaveSequenceCounters(payload: unknown) {
  await upsertSequenceCounters(payload);
}

export async function pgGetJsonStore<T = unknown>(filename: string) {
  return getJsonStore<T>(filename);
}

export async function pgSaveJsonStore(filename: string, payload: unknown) {
  await upsertJsonStore(filename, payload);
}

export async function pgGetCoupons(): Promise<Coupon[]> {
  const stored = await pgGetJsonStore<Coupon[]>('coupons.json');
  return Array.isArray(stored) ? stored : [];
}

export async function pgGetCampaigns(): Promise<Campaign[]> {
  const stored = await pgGetJsonStore<Campaign[]>('campaigns.json');
  return Array.isArray(stored) ? stored : [];
}

export async function pgCreateCampaign(payload: {
  name: string;
  type?: CampaignType;
  startDate: string;
  endDate: string;
  status?: CampaignStatus;
  description?: string;
  branchIds?: string[];
  customerTags?: string[];
  autoApplyInPos?: boolean;
  allowRepeats?: boolean;
  allowStacking?: boolean;
  priority?: number;
  maxApplicationsPerInvoice?: number;
  items: Array<{
    productId?: string;
    categoryId?: string;
    minQty: number;
    bundlePrice: number;
    regularUnitPrice?: number;
    maxApplications?: number;
  }>;
  createdBy?: string;
}): Promise<Campaign> {
  const pool = getPgPool();
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      `
      INSERT INTO data_json_store(filename, payload, updated_at)
      VALUES($1, '[]'::jsonb, NOW())
      ON CONFLICT (filename) DO NOTHING
      `,
      ['campaigns.json']
    );

    const locked = await client.query<{ payload: unknown }>(
      'SELECT payload FROM data_json_store WHERE filename = $1 FOR UPDATE',
      ['campaigns.json']
    );

    const campaigns = Array.isArray(locked.rows[0]?.payload) ? (locked.rows[0].payload as Campaign[]) : [];
    const now = new Date().toISOString();
    const campaignId = createPgId('campaign');
    const created: Campaign = {
      id: campaignId,
      name: String(payload.name || '').trim(),
      type: payload.type || 'multi_buy',
      startDate: String(payload.startDate || '').trim(),
      endDate: String(payload.endDate || '').trim(),
      status: payload.status || 'active',
      description: String(payload.description || '').trim() || undefined,
      branchIds: Array.isArray(payload.branchIds)
        ? payload.branchIds.map((value) => String(value || '').trim()).filter(Boolean)
        : undefined,
      customerTags: Array.isArray(payload.customerTags)
        ? payload.customerTags.map((value) => String(value || '').trim()).filter(Boolean)
        : undefined,
      autoApplyInPos: payload.autoApplyInPos !== false,
      allowRepeats: payload.allowRepeats !== false,
      allowStacking: payload.allowStacking === true,
      priority: Number.isFinite(Number(payload.priority)) ? Number(payload.priority) : undefined,
      maxApplicationsPerInvoice: Number.isFinite(Number(payload.maxApplicationsPerInvoice))
        ? Number(payload.maxApplicationsPerInvoice)
        : undefined,
      items: (payload.items || []).map((item) => ({
        id: createPgId('campaign-item'),
        campaignId,
        productId: String(item.productId || '').trim() || undefined,
        categoryId: String(item.categoryId || '').trim() || undefined,
        minQty: Number(item.minQty || 0),
        bundlePrice: Number(item.bundlePrice || 0),
        regularUnitPrice: Number.isFinite(Number(item.regularUnitPrice)) ? Number(item.regularUnitPrice) : undefined,
        maxApplications: Number.isFinite(Number(item.maxApplications)) ? Number(item.maxApplications) : undefined,
      })),
      createdAt: now,
      createdBy: String(payload.createdBy || '').trim() || undefined,
    };

    const nextCampaigns = [created, ...campaigns];
    await client.query(
      'UPDATE data_json_store SET payload = $2::jsonb, updated_at = NOW() WHERE filename = $1',
      ['campaigns.json', JSON.stringify(nextCampaigns)]
    );

    await client.query('COMMIT');
    return created;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

export async function pgUpdateCampaign(campaignId: string, updates: Partial<Omit<Campaign, 'id' | 'createdAt' | 'createdBy'>>): Promise<Campaign | null> {
  const pool = getPgPool();
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      `
      INSERT INTO data_json_store(filename, payload, updated_at)
      VALUES($1, '[]'::jsonb, NOW())
      ON CONFLICT (filename) DO NOTHING
      `,
      ['campaigns.json']
    );

    const locked = await client.query<{ payload: unknown }>(
      'SELECT payload FROM data_json_store WHERE filename = $1 FOR UPDATE',
      ['campaigns.json']
    );

    const campaigns = Array.isArray(locked.rows[0]?.payload) ? (locked.rows[0].payload as Campaign[]) : [];
    const index = campaigns.findIndex((entry) => String(entry.id || '').trim() === String(campaignId || '').trim());
    if (index === -1) {
      await client.query('COMMIT');
      return null;
    }

    const current = campaigns[index];
    const nextItems = Array.isArray(updates.items)
      ? updates.items.map((item) => ({
          ...item,
          id: String(item.id || '').trim() || createPgId('campaign-item'),
          campaignId: current.id,
          minQty: Number(item.minQty || 0),
          bundlePrice: Number(item.bundlePrice || 0),
          regularUnitPrice: Number.isFinite(Number(item.regularUnitPrice)) ? Number(item.regularUnitPrice) : undefined,
          maxApplications: Number.isFinite(Number(item.maxApplications)) ? Number(item.maxApplications) : undefined,
        }))
      : current.items;

    campaigns[index] = {
      ...current,
      ...updates,
      items: nextItems,
      updatedAt: new Date().toISOString(),
    };

    await client.query(
      'UPDATE data_json_store SET payload = $2::jsonb, updated_at = NOW() WHERE filename = $1',
      ['campaigns.json', JSON.stringify(campaigns)]
    );

    await client.query('COMMIT');
    return campaigns[index];
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

export async function pgDeleteCampaign(campaignId: string): Promise<boolean> {
  const pool = getPgPool();
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      `
      INSERT INTO data_json_store(filename, payload, updated_at)
      VALUES($1, '[]'::jsonb, NOW())
      ON CONFLICT (filename) DO NOTHING
      `,
      ['campaigns.json']
    );

    const locked = await client.query<{ payload: unknown }>(
      'SELECT payload FROM data_json_store WHERE filename = $1 FOR UPDATE',
      ['campaigns.json']
    );

    const campaigns = Array.isArray(locked.rows[0]?.payload) ? (locked.rows[0].payload as Campaign[]) : [];
    const filtered = campaigns.filter((entry) => String(entry.id || '').trim() !== String(campaignId || '').trim());
    if (filtered.length === campaigns.length) {
      await client.query('COMMIT');
      return false;
    }

    await client.query(
      'UPDATE data_json_store SET payload = $2::jsonb, updated_at = NOW() WHERE filename = $1',
      ['campaigns.json', JSON.stringify(filtered)]
    );

    await client.query('COMMIT');
    return true;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

export async function pgCreateCoupon(payload: {
  title: string;
  amount: number;
  couponCode?: string;
  currencyCode?: string;
  baseCurrencyCode?: string;
  exchangeRate?: number;
  rateDate?: string;
  originalAmountBase?: number;
  remainingAmountBase?: number;
  customerType?: 'cash' | 'customer';
  customerId?: string;
  customerName?: string;
  customerAccountCode?: string;
  paymentMethod?: 'cash' | 'check' | 'credit';
  settlementAccountCode?: string;
  settlementAccountName?: string;
  liabilityAccountCode?: string;
  liabilityAccountName?: string;
  expiryDate?: string;
  notes?: string;
  createdBy?: string;
}): Promise<Coupon> {
  const pool = getPgPool();
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    await client.query(
      `
      INSERT INTO data_json_store(filename, payload, updated_at)
      VALUES($1, '[]'::jsonb, NOW())
      ON CONFLICT (filename) DO NOTHING
      `,
      ['coupons.json']
    );

    const locked = await client.query<{ payload: unknown }>(
      'SELECT payload FROM data_json_store WHERE filename = $1 FOR UPDATE',
      ['coupons.json']
    );

    const coupons = Array.isArray(locked.rows[0]?.payload) ? (locked.rows[0].payload as Coupon[]) : [];
    const usedCodes = new Set(coupons.map((coupon) => String(coupon.couponCode || '').trim().toLowerCase()).filter(Boolean));
    const usedNumbers = new Set(coupons.map((coupon) => String(coupon.couponNumber || '').trim().toUpperCase()).filter(Boolean));

    const requestedCode = String(payload.couponCode || '').trim();
    let couponCode = requestedCode;
    if (!couponCode) {
      let generated = `CPN-${String(await pgNextNamedSequence('coupon-code')).padStart(8, '0')}`;
      while (usedCodes.has(generated.toLowerCase())) {
        generated = `CPN-${String(await pgNextNamedSequence('coupon-code')).padStart(8, '0')}`;
      }
      couponCode = generated;
    }
    if (usedCodes.has(couponCode.toLowerCase())) {
      throw new Error('COUPON_CODE_EXISTS');
    }

    let couponNumber = `CPN/${new Date().getFullYear()}/${String(await pgNextNamedSequence('coupon-ref')).padStart(5, '0')}`;
    while (usedNumbers.has(couponNumber.toUpperCase())) {
      couponNumber = `CPN/${new Date().getFullYear()}/${String(await pgNextNamedSequence('coupon-ref')).padStart(5, '0')}`;
    }

    const amount = Math.max(0, Number(payload.amount || 0));
    if (amount <= 0) {
      throw new Error('COUPON_AMOUNT_INVALID');
    }

    const now = new Date().toISOString();
    const created: Coupon = {
      id: createPgId('coupon'),
      couponNumber,
      couponCode,
      title: String(payload.title || '').trim() || couponCode,
      originalAmount: amount,
      remainingAmount: amount,
      originalAmountBase: Number.isFinite(Number(payload.originalAmountBase)) ? Number(payload.originalAmountBase) : undefined,
      remainingAmountBase: Number.isFinite(Number(payload.remainingAmountBase)) ? Number(payload.remainingAmountBase) : undefined,
      status: 'active',
      currencyCode: String(payload.currencyCode || '').trim() || undefined,
      baseCurrencyCode: String(payload.baseCurrencyCode || '').trim() || undefined,
      exchangeRate: Number.isFinite(Number(payload.exchangeRate)) && Number(payload.exchangeRate) > 0 ? Number(payload.exchangeRate) : undefined,
      rateDate: String(payload.rateDate || '').trim() || undefined,
      customerType: payload.customerType === 'customer' ? 'customer' : 'cash',
      customerId: String(payload.customerId || '').trim() || undefined,
      customerName: String(payload.customerName || '').trim() || undefined,
      customerAccountCode: String(payload.customerAccountCode || '').trim() || undefined,
      paymentMethod: payload.paymentMethod || 'cash',
      settlementAccountCode: String(payload.settlementAccountCode || '').trim() || undefined,
      settlementAccountName: String(payload.settlementAccountName || '').trim() || undefined,
      liabilityAccountCode: String(payload.liabilityAccountCode || '').trim() || undefined,
      liabilityAccountName: String(payload.liabilityAccountName || '').trim() || undefined,
      soldAt: now,
      expiryDate: String(payload.expiryDate || '').trim() || undefined,
      notes: String(payload.notes || '').trim() || undefined,
      createdBy: String(payload.createdBy || '').trim() || undefined,
      createdAt: now,
    };

    const nextCoupons = [created, ...coupons];
    await client.query(
      'UPDATE data_json_store SET payload = $2::jsonb, updated_at = NOW() WHERE filename = $1',
      ['coupons.json', JSON.stringify(nextCoupons)]
    );

    await client.query('COMMIT');
    return created;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

export async function pgUpdateCouponStatus(couponId: string, status: Coupon['status'], notes?: string): Promise<Coupon | null> {
  const pool = getPgPool();
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      `
      INSERT INTO data_json_store(filename, payload, updated_at)
      VALUES($1, '[]'::jsonb, NOW())
      ON CONFLICT (filename) DO NOTHING
      `,
      ['coupons.json']
    );
    const locked = await client.query<{ payload: unknown }>(
      'SELECT payload FROM data_json_store WHERE filename = $1 FOR UPDATE',
      ['coupons.json']
    );
    const coupons = Array.isArray(locked.rows[0]?.payload) ? (locked.rows[0].payload as Coupon[]) : [];
    const index = coupons.findIndex((coupon) => String(coupon.id || '').trim() === String(couponId || '').trim());
    if (index === -1) {
      await client.query('COMMIT');
      return null;
    }
    const current = coupons[index];
    const next: Coupon = {
      ...current,
      status,
      notes: String(notes || current.notes || '').trim() || current.notes,
      updatedAt: new Date().toISOString(),
    };
    coupons[index] = next;
    await client.query(
      'UPDATE data_json_store SET payload = $2::jsonb, updated_at = NOW() WHERE filename = $1',
      ['coupons.json', JSON.stringify(coupons)]
    );
    await client.query('COMMIT');
    return next;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

export async function pgApplyCouponFxDifference(couponId: string, deltaBase: number, referenceId?: string): Promise<Coupon | null> {
  const pool = getPgPool();
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      `
      INSERT INTO data_json_store(filename, payload, updated_at)
      VALUES($1, '[]'::jsonb, NOW())
      ON CONFLICT (filename) DO NOTHING
      `,
      ['coupons.json']
    );

    const locked = await client.query<{ payload: unknown }>(
      'SELECT payload FROM data_json_store WHERE filename = $1 FOR UPDATE',
      ['coupons.json']
    );

    const coupons = Array.isArray(locked.rows[0]?.payload) ? (locked.rows[0].payload as Coupon[]) : [];
    const index = coupons.findIndex((coupon) => String(coupon.id || '').trim() === String(couponId || '').trim());
    if (index === -1) {
      await client.query('COMMIT');
      return null;
    }

    const delta = Number(deltaBase || 0);
    if (!Number.isFinite(delta) || Math.abs(delta) <= 0.0000001) {
      await client.query('COMMIT');
      return coupons[index];
    }

    const current = coupons[index];
    const nowIso = new Date().toISOString();
    coupons[index] = {
      ...current,
      cumulativeFxDifferenceBase: Math.round(((Number(current.cumulativeFxDifferenceBase || 0) + delta) * 1_000_000)) / 1_000_000,
      lastFxSettlementAt: nowIso,
      lastFxReferenceId: String(referenceId || '').trim() || current.lastFxReferenceId,
      updatedAt: nowIso,
    };

    await client.query(
      'UPDATE data_json_store SET payload = $2::jsonb, updated_at = NOW() WHERE filename = $1',
      ['coupons.json', JSON.stringify(coupons)]
    );

    await client.query('COMMIT');
    return coupons[index];
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

export async function pgRedeemCouponsBatch(payload: {
  lines: Array<{ couponCode: string; amount: number }>;
  referenceId?: string;
  redeemedBy?: string;
}): Promise<{ success: boolean; error?: string; applied?: Array<{ couponId: string; couponCode: string; amount: number; amountBase?: number }> }> {
  const normalizedLines = (payload.lines || [])
    .map((line) => ({
      couponCode: String(line.couponCode || '').trim(),
      amount: Math.max(0, Number(line.amount || 0)),
    }))
    .filter((line) => line.couponCode && line.amount > 0);

  if (normalizedLines.length === 0) {
    return { success: true, applied: [] };
  }

  const pool = getPgPool();
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    await client.query(
      `
      INSERT INTO data_json_store(filename, payload, updated_at)
      VALUES($1, '[]'::jsonb, NOW())
      ON CONFLICT (filename) DO NOTHING
      `,
      ['coupons.json']
    );

    const locked = await client.query<{ payload: unknown }>(
      'SELECT payload FROM data_json_store WHERE filename = $1 FOR UPDATE',
      ['coupons.json']
    );

    const coupons = Array.isArray(locked.rows[0]?.payload) ? (locked.rows[0].payload as Coupon[]) : [];
    const byCode = new Map(coupons.map((coupon) => [String(coupon.couponCode || '').trim().toLowerCase(), coupon]));
    const nowMs = Date.now();

    for (const line of normalizedLines) {
      const coupon = byCode.get(line.couponCode.toLowerCase());
      if (!coupon) {
        await client.query('ROLLBACK');
        return { success: false, error: `COUPON_NOT_FOUND:${line.couponCode}` };
      }
      if (coupon.status === 'cancelled' || coupon.status === 'expired') {
        await client.query('ROLLBACK');
        return { success: false, error: `COUPON_NOT_ACTIVE:${line.couponCode}` };
      }
      if (coupon.expiryDate) {
        const expiryMs = new Date(coupon.expiryDate).getTime();
        if (!Number.isNaN(expiryMs) && expiryMs < nowMs) {
          await client.query('ROLLBACK');
          return { success: false, error: `COUPON_EXPIRED:${line.couponCode}` };
        }
      }
      if (Number(coupon.remainingAmount || 0) + 1e-9 < line.amount) {
        await client.query('ROLLBACK');
        return { success: false, error: `COUPON_INSUFFICIENT:${line.couponCode}` };
      }
    }

    const applied: Array<{ couponId: string; couponCode: string; amount: number; amountBase?: number }> = [];
    normalizedLines.forEach((line) => {
      const coupon = byCode.get(line.couponCode.toLowerCase());
      if (!coupon) return;
      const couponRate = Number(coupon.exchangeRate || 0) > 0 ? Number(coupon.exchangeRate) : 1;
      const amountBase = line.amount * couponRate;
      const nextRemaining = Math.max(0, Number(coupon.remainingAmount || 0) - line.amount);
      const currentRemainingBase = Number.isFinite(Number(coupon.remainingAmountBase))
        ? Number(coupon.remainingAmountBase)
        : Number(coupon.remainingAmount || 0) * couponRate;
      const nextRemainingBase = Math.max(0, currentRemainingBase - amountBase);
      coupon.remainingAmount = Math.round(nextRemaining * 100) / 100;
      coupon.remainingAmountBase = Math.round(nextRemainingBase * 1_000_000) / 1_000_000;
      coupon.status = coupon.remainingAmount <= 0 ? 'used' : 'active';
      coupon.lastUsedAt = new Date().toISOString();
      coupon.lastUsedBy = String(payload.redeemedBy || '').trim() || undefined;
      coupon.lastReferenceId = String(payload.referenceId || '').trim() || undefined;
      coupon.updatedAt = coupon.lastUsedAt;
      applied.push({ couponId: coupon.id, couponCode: coupon.couponCode, amount: line.amount, amountBase });
    });

    await client.query(
      'UPDATE data_json_store SET payload = $2::jsonb, updated_at = NOW() WHERE filename = $1',
      ['coupons.json', JSON.stringify(coupons)]
    );

    await client.query('COMMIT');
    return { success: true, applied };
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

async function pgNextSequenceValue(sequenceKey: string): Promise<number> {
  const result = await pgQuery<{ value: number }>(
    `
    INSERT INTO sequence_counters (id, raw, updated_at)
    VALUES ($1::text, jsonb_build_object('id', $1::text, 'value', 1), NOW())
    ON CONFLICT (id)
    DO UPDATE SET
      raw = jsonb_set(
        jsonb_set(COALESCE(sequence_counters.raw, '{}'::jsonb), '{id}', to_jsonb(sequence_counters.id), true),
        '{value}',
        to_jsonb(COALESCE((sequence_counters.raw->>'value')::int, 0) + 1),
        true
      ),
      updated_at = NOW()
    RETURNING COALESCE((raw->>'value')::int, 1) AS value
    `,
    [sequenceKey]
  );
  return Number(result.rows[0]?.value || 1);
}

export async function pgNextNamedSequence(sequenceKey: string): Promise<number> {
  const normalized = String(sequenceKey || '').trim();
  if (!normalized) {
    throw new Error('Sequence key is required.');
  }
  return pgNextSequenceValue(normalized);
}

  // Payment processing helpers (Phase 1: Customer payments migration)
  export interface CustomerPaymentInput {
    date: string;
    customerId: string;
    amount: number;
    status?: 'active' | 'cancelled';
    amountBase?: number;
    currencyCode?: string;
    baseCurrencyCode?: string;
    exchangeRate?: number;
    rateDate?: string;
    isAdvance?: boolean;
    salesRepId?: string;
    method?: string;
    postingStatus?: 'draft' | 'saved' | 'posted';
    reference?: string;
    notes?: string;
    allocations?: Array<{
      invoiceId: string;
      invoiceNumber?: string;
      invoiceCurrencyCode?: string;
      invoiceExchangeRate?: number;
      invoiceRateDate?: string;
      amount: number;
      amountBase?: number;
      fxDifferenceBase?: number;
    }>;
    appliedAmount?: number;
    unappliedAmount?: number;
    totalFxDifferenceBase?: number;
    createdBy?: string;
    cancelledAt?: string;
    cancelledBy?: string;
    cancelledReason?: string;
  }

  export interface IncomingCheckInput {
    checkNumber: string;
    bankId: string;
    bankName: string;
    checkDate: string;
    amount: number;
    currency: string;
    convertedAmount: number;
    exchangeRate: number;
    customerId: string;
    customerName: string;
    reference: string;
    status: 'pending' | 'cleared' | 'rejected' | 'returned';
    notes: string;
    createdBy: string;
  }

  /** Create a new customer payment record in PostgreSQL */
  export async function pgAddPayment(payment: CustomerPaymentInput) {
    const newPayment = {
      ...payment,
      status: payment.status || 'active',
      id: `pay-${randomUUID()}`,
    };
    await pgSavePayments([newPayment]);
    return newPayment;
  }

  export async function pgUpdatePayment(paymentId: string, updates: Partial<CustomerPaymentInput>) {
    const existing = await pgGetPayments({ page: 1, pageSize: 5000 });
    const payment = existing.items.find((entry) => entry.id === paymentId) as Record<string, unknown> | undefined;
    if (!payment) {
      return { success: false as const, error: 'Payment voucher not found.' };
    }

    const updated = {
      ...payment,
      ...updates,
      id: paymentId,
    };

    await pgSavePayments([updated]);
    return { success: true as const, payment: updated };
  }

  export async function pgCancelPayment(paymentId: string, cancelledBy: string, reason?: string) {
    const result = await pgUpdatePayment(paymentId, {
      status: 'cancelled',
      cancelledAt: new Date().toISOString(),
      cancelledBy,
      cancelledReason: reason,
    });
    return result;
  }

  /** Create a new incoming check record in PostgreSQL */
  export async function pgAddIncomingCheck(check: IncomingCheckInput) {
    const newCheck = {
      ...check,
      id: `chk-${randomUUID()}`,
      createdAt: new Date().toISOString(),
      updatedAt: new Date().toISOString(),
    };
    await pgSaveChecksReceived([newCheck]);
    return newCheck;
  }

  export async function pgRemovePendingIncomingChecksBySourceTag(sourceTag: string): Promise<number> {
    const normalizedTag = String(sourceTag || '').trim();
    if (!normalizedTag) return 0;

    const result = await pgGetChecksReceived({ page: 1, pageSize: 5000 });
    const candidates = (result.items || []) as Array<Record<string, unknown>>;
    const idsToDelete = candidates
      .filter((check) => {
        const notes = String(check.notes || '');
        const isTagged = notes.includes(normalizedTag);
        const isPending = String(check.status || '') === 'pending';
        const endorsementStatus = String(check.endorsementStatus || 'none');
        const isNotEndorsed = endorsementStatus === '' || endorsementStatus === 'none';
        return isTagged && isPending && isNotEndorsed;
      })
      .map((check) => String(check.id || '').trim())
      .filter(Boolean);

    if (idsToDelete.length === 0) return 0;

    for (const id of idsToDelete) {
      await pgQuery('DELETE FROM checks_received WHERE id = $1', [id]);
    }

    return idsToDelete.length;
  }

  export async function pgMarkPendingIncomingChecksBySourceTagAsReturned(sourceTag: string, reason?: string): Promise<number> {
    const normalizedTag = String(sourceTag || '').trim();
    if (!normalizedTag) return 0;

    const result = await pgGetChecksReceived({ page: 1, pageSize: 5000 });
    const candidates = (result.items || []) as Array<Record<string, unknown>>;
    const returnedDate = new Date().toISOString();

    const updates = candidates
      .filter((check) => {
        const notes = String(check.notes || '');
        const isTagged = notes.includes(normalizedTag);
        const isPending = String(check.status || '') === 'pending';
        const endorsementStatus = String(check.endorsementStatus || 'none');
        const isNotEndorsed = endorsementStatus === '' || endorsementStatus === 'none';
        return isTagged && isPending && isNotEndorsed;
      })
      .map((check) => {
        const notes = String(check.notes || '');
        return {
          ...check,
          status: 'returned',
          returnedDate,
          updatedAt: returnedDate,
          rejectedReason: String(check.rejectedReason || reason || ''),
          notes: notes ? `${notes}\n[RETURNED] ${reason || 'Invoice cancelled'}` : `[RETURNED] ${reason || 'Invoice cancelled'}`,
        };
      });

    if (updates.length === 0) return 0;

    await pgSaveChecksReceived(updates);
    return updates.length;
  }

  /** Update customer balance by delta amount */
  export async function pgUpdateCustomerBalance(customerId: string, amountChange: number) {
    // First fetch the current customer
    const result = await listCustomers();
    const customers = result.items || [];
    const customer = customers.find(c => c.id === customerId);
    if (!customer) {
      return false;
    }
    // Update with new balance
    await pgUpdateCustomer(customerId, {
      balance: ((customer.balance as number) || 0) + amountChange,
    });
    return true;
  }

  /** Save journal entry records (batch or single) */
  export async function pgSaveJournalEntry(entries: Array<Record<string, unknown>>) {
    await pgSaveJournalEntries(entries);
  }

  export async function pgDeleteJournalEntry(id: string) {
    return deleteJournalEntryById(id);
  }

  export interface SupplierPaymentInput {
    [key: string]: unknown;
  }

  export interface IssuedCheckInput {
    [key: string]: unknown;
  }

  /** Create a new supplier payment record in PostgreSQL */
  export async function pgAddSupplierPayment(payment: SupplierPaymentInput) {
    const newPayment = {
      ...payment,
      id: createPgId('spay'),
      status: 'active',
      postingStatus: (payment.postingStatus as string) || 'posted',
    };
    await pgSaveSupplierPayments([newPayment]);
    return newPayment;
  }

  /** Update supplier balance by delta amount */
  export async function pgUpdateSupplierBalance(supplierId: string, amountChange: number) {
    const result = await listSuppliers();
    const suppliers = result.items || [];
    const supplier = suppliers.find((s) => s.id === supplierId);
    if (!supplier) {
      return false;
    }
    await pgUpdateSupplier(supplierId, {
      balance: ((supplier.balance as number) || 0) + amountChange,
    });
    return true;
  }

  /** Create a new issued check record in PostgreSQL */
  export async function pgAddIssuedCheck(check: IssuedCheckInput) {
    const newCheck = {
      ...check,
      id: String(Date.now()),
      createdAt: new Date().toISOString(),
      updatedAt: new Date().toISOString(),
    };
    await pgSaveChecksIssued([newCheck]);
    return newCheck;
  }

  /** Endorse an incoming check in PostgreSQL */
  export async function pgEndorseIncomingCheck(
    checkId: string,
    endorsementType: 'transferred-to-collection' | 'endorsed-to-party',
    endorsedToId?: string,
    endorsedToName?: string,
    endorsedToType?: 'customer' | 'supplier',
    endorsementReference?: string,
    notes?: string
  ) {
    const checksResult = await pgGetChecksReceived({ page: 1, pageSize: 200 });
    const check = checksResult.items.find((c) => c.id === checkId);
    if (!check) {
      return false;
    }

    const updatedCheck = {
      ...check,
      endorsementStatus: endorsementType,
      endorsedToId: endorsementType === 'endorsed-to-party' ? endorsedToId : check.endorsedToId,
      endorsedToName: endorsementType === 'endorsed-to-party' ? endorsedToName : check.endorsedToName,
      endorsedToType: endorsementType === 'endorsed-to-party' ? endorsedToType : check.endorsedToType,
      endorsementReference: endorsementType === 'endorsed-to-party'
        ? String(endorsementReference || '').trim() || check.endorsementReference
        : check.endorsementReference,
      endorsedAt: new Date().toISOString(),
      notes: notes || check.notes,
      updatedAt: new Date().toISOString(),
    };

    await pgSaveChecksReceived([updatedCheck]);
    return true;
  }

  type SupplierPaymentLock = {
    beneficiaryId: string;
    userId: string;
    userName: string;
    lockedAt: string;
    expiresAt: string;
  };

  export async function pgGetSupplierPaymentLockByBeneficiary(beneficiaryId: string) {
    const result = await pgGetSupplierPaymentLocks({ page: 1, pageSize: 200 });
    const now = new Date().toISOString();
    const activeLocks = result.items.filter((lock) => String(lock.expiresAt || '') > now);
    return (activeLocks.find((lock) => lock.beneficiaryId === beneficiaryId) as SupplierPaymentLock | undefined) || null;
  }

  export async function pgAcquireSupplierPaymentLock(beneficiaryId: string, userId: string, userName: string) {
    const result = await pgGetSupplierPaymentLocks({ page: 1, pageSize: 200 });
    const now = new Date().toISOString();
    const activeLocks = result.items.filter((lock) => String(lock.expiresAt || '') > now) as SupplierPaymentLock[];

    const existing = activeLocks.find((lock) => lock.beneficiaryId === beneficiaryId);
    if (existing && existing.userId !== userId) {
      return { success: false, lockedBy: existing.userName };
    }

    const nextLock: SupplierPaymentLock = existing
      ? { ...existing, expiresAt: new Date(Date.now() + 5 * 60 * 1000).toISOString() }
      : {
          beneficiaryId,
          userId,
          userName,
          lockedAt: new Date().toISOString(),
          expiresAt: new Date(Date.now() + 5 * 60 * 1000).toISOString(),
        };

    await pgSaveSupplierPaymentLocks([nextLock]);
    return { success: true };
  }

  export async function pgReleaseSupplierPaymentLock(beneficiaryId: string, userId: string) {
    const lock = await pgGetSupplierPaymentLockByBeneficiary(beneficiaryId);
    if (!lock || lock.userId !== userId) {
      return;
    }
    await pgSaveSupplierPaymentLocks([{ ...lock, expiresAt: new Date(0).toISOString() }]);
  }

  export async function pgReturnEndorsedIncomingCheck(params: {
    checkId: string;
    reason: string;
    returnedDate?: string;
    bankFeeAmount?: number;
    chargeFeeToCustomer?: boolean;
    notes?: string;
    createdBy?: string;
  }) {
    const {
      checkId,
      reason,
      returnedDate,
      bankFeeAmount = 0,
      chargeFeeToCustomer = false,
      notes,
      createdBy = 'system',
    } = params;

    const normalizedReason = String(reason || '').trim();
    if (!normalizedReason) {
      throw new Error('CHECK_RETURN_REASON_REQUIRED');
    }

    const checksResult = await pgGetChecksReceived({ page: 1, pageSize: 5000 });
    const check = checksResult.items.find((entry) => entry.id === checkId) as Record<string, any> | undefined;
    if (!check) {
      throw new Error('CHECK_NOT_FOUND');
    }
    if (check.status === 'returned') {
      throw new Error('CHECK_ALREADY_RETURNED');
    }
    if (check.endorsementStatus !== 'endorsed-to-party' || check.endorsedToType !== 'supplier' || !check.endorsedToId) {
      throw new Error('CHECK_NOT_ENDORSED_TO_SUPPLIER');
    }

    const normalizeText = (value: unknown) => String(value || '').trim().toLowerCase();

    const suppliersResult = await pgGetSuppliers({ page: 1, pageSize: 5000 });
    const supplierById = suppliersResult.items.find((entry) => entry.id === check.endorsedToId) as Record<string, any> | undefined;
    const supplierByName = suppliersResult.items.find(
      (entry) => normalizeText((entry as Record<string, any>).name) === normalizeText(check.endorsedToName)
    ) as Record<string, any> | undefined;
    const supplier = supplierById || supplierByName;
    if (!supplier) {
      throw new Error('SUPPLIER_NOT_FOUND');
    }

    const customersResult = await pgGetCustomers({ page: 1, pageSize: 5000 });
    const customerById = customersResult.items.find((entry) => entry.id === check.customerId) as Record<string, any> | undefined;
    const customerByName = customersResult.items.find(
      (entry) => normalizeText((entry as Record<string, any>).name) === normalizeText(check.customerName)
    ) as Record<string, any> | undefined;
    const customer = customerById || customerByName;
    if (!customer) {
      throw new Error('CUSTOMER_NOT_FOUND');
    }

    const currenciesResult = await pgGetCurrencies({ page: 1, pageSize: 5000 });
    const baseCurrencyCode = String(
      currenciesResult.items.find((currency) => currency.isDefault === true || currency.isDefault === 'true')?.code || 'JOD'
    ).trim();
    const nowIso = new Date().toISOString();
    const effectiveDateIso = returnedDate ? new Date(returnedDate).toISOString() : nowIso;
    const checkCurrency = String(check.currency || baseCurrencyCode).trim();
    const checkAmountBase = checkCurrency === baseCurrencyCode
      ? Number(check.amount || 0)
      : Number(check.convertedAmount || 0) > 0
        ? Number(check.convertedAmount || 0)
        : Number(check.amount || 0) * Number(check.exchangeRate || 1);

    const normalizedBankFee = Math.max(0, Number(bankFeeAmount || 0));
    const customerLoad = checkAmountBase + (chargeFeeToCustomer ? normalizedBankFee : 0);

    const supplierUpdated = await pgUpdateSupplierBalance(String(supplier.id), checkAmountBase);
    const customerUpdated = await pgUpdateCustomerBalance(String(customer.id), customerLoad);
    if (!supplierUpdated || !customerUpdated) {
      throw new Error('CHECK_RETURN_BALANCE_UPDATE_FAILED');
    }

    const journalLines = [
      {
        accountId: '1150',
        accountCode: '1150',
        accountName: 'ذمم العملاء',
        debit: checkAmountBase,
        credit: 0,
        description: `إعادة تحميل العميل بقيمة الشيك المرتجع ${check.checkNumber}`,
      },
      {
        accountId: supplier.accountId || '2110',
        accountCode: supplier.accountCode || '2110',
        accountName: `ذمم موردين - ${supplier.name}`,
        debit: 0,
        credit: checkAmountBase,
        description: `إعادة فتح ذمة المورد بسبب ارتجاع الشيك ${check.checkNumber}`,
      },
    ] as Array<Record<string, unknown>>;

    if (normalizedBankFee > 0) {
      journalLines.push(
        {
          accountId: chargeFeeToCustomer ? '1150' : '5360',
          accountCode: chargeFeeToCustomer ? '1150' : '5360',
          accountName: chargeFeeToCustomer ? 'ذمم العملاء' : 'مصروفات بنكية',
          debit: normalizedBankFee,
          credit: 0,
          description: chargeFeeToCustomer
            ? `تحميل رسوم الشيك المرتجع على العميل ${check.checkNumber}`
            : `رسوم بنكية للشيك المرتجع ${check.checkNumber}`,
        },
        {
          accountId: chargeFeeToCustomer ? '4390' : '1110',
          accountCode: chargeFeeToCustomer ? '4390' : '1110',
          accountName: chargeFeeToCustomer ? 'إيراد رسوم الشيكات المرتجعة' : 'الصندوق',
          debit: 0,
          credit: normalizedBankFee,
          description: chargeFeeToCustomer
            ? `إثبات إيراد رسوم الشيك المرتجع ${check.checkNumber}`
            : `سداد رسوم بنكية للشيك المرتجع ${check.checkNumber}`,
        }
      );
    }

    const journalEntryId = `CHK-RETURN-${check.id}`;
    await pgSaveJournalEntry([{ 
      id: journalEntryId,
      date: effectiveDateIso,
      referenceType: 'payment',
      referenceId: journalEntryId,
      description: `ارتجاع شيك وارد رقم ${check.checkNumber}`,
      lines: journalLines,
      createdBy,
    }]);

    const updatedCheck = {
      ...check,
      status: 'returned',
      returnedDate: effectiveDateIso,
      rejectedReason: normalizedReason,
      notes: [check.notes, notes].filter(Boolean).join(' | ').trim() || undefined,
      updatedAt: nowIso,
    };
    await pgSaveChecksReceived([updatedCheck]);

    const events = (await pgGetJsonStore<CheckReturnEvent[]>('checks_return_events.json')) || [];
    const event: CheckReturnEvent = {
      id: createPgId('chkret'),
      checkId: String(check.id),
      checkNumber: String(check.checkNumber),
      customerId: String(customer.id),
      customerName: String(customer.name),
      supplierId: String(supplier.id),
      supplierName: String(supplier.name),
      reason: normalizedReason,
      bankFeeAmount: normalizedBankFee,
      chargeFeeToCustomer,
      amountBase: checkAmountBase,
      currency: checkCurrency,
      amountOriginal: Number(check.amount || 0),
      journalEntryId,
      notes: notes || undefined,
      createdAt: nowIso,
      createdBy,
    };
    await pgSaveJsonStore('checks_return_events.json', [...events, event]);

    return { check: updatedCheck, event, journalEntryId };
  }

  export async function pgAddPurchaseCreditNote(
    data: Omit<
      PurchaseCreditNote,
      'id' | 'creditNoteNumber' | 'status' | 'createdAt' | 'createdBy' | 'postingStatus' | 'journalEntryId' | 'journalEntryIdForImpactOnBalance'
    > & { createdBy: string }
  ) {
    const creditNotesResult = await pgGetPurchaseCreditNotes({ page: 1, pageSize: 5000 });
    const creditNotes = creditNotesResult.items as Record<string, any>[];
    const nextSeqNum = await pgNextSequenceValue('purchase_credit_note_seq');
    const creditNoteNumber = `CN/${new Date().getFullYear()}/${String(nextSeqNum).padStart(4, '0')}`;

    const normalizedScope = data.creditScope ?? (data.items?.length ? 'items' : 'invoice-total');
    const hasLinkedInvoice = Boolean(data.invoiceId && data.invoiceId !== '__supplier_balance__');

    if (Number(data.grandTotal || 0) <= 0) {
      throw new Error('CREDIT_NOTE_AMOUNT_INVALID');
    }

    if (hasLinkedInvoice) {
      const invoiceId = String(data.invoiceId);
      const ordersResult = await pgGetPurchaseOrders({ page: 1, pageSize: 5000 });
      const invoice = ordersResult.items.find((entry) => entry.id === invoiceId) as Record<string, any> | undefined;
      if (!invoice) {
        throw new Error(`Invoice ${invoiceId} not found`);
      }

      const existingTotal = creditNotes
        .filter((cn) => cn.invoiceId === invoiceId && cn.status !== 'cancelled')
        .reduce((sum, cn) => sum + Number(cn.grandTotal || 0), 0);

      if (existingTotal + Number(data.grandTotal || 0) > Number(invoice.grandTotal || 0)) {
        throw new Error('CREDIT_NOTE_EXCEEDS_INVOICE');
      }
    }

    const newCreditNote = {
      ...data,
      creditScope: normalizedScope,
      id: createPgId('cn'),
      creditNoteNumber,
      status: 'draft',
      postingStatus: 'draft',
      createdAt: new Date().toISOString(),
    };

    await pgSavePurchaseCreditNotes([newCreditNote]);
    return newCreditNote;
  }

  export async function pgPostPurchaseCreditNote(creditNoteId: string, createdBy: string) {
    const creditNotesResult = await pgGetPurchaseCreditNotes({ page: 1, pageSize: 5000 });
    const creditNote = creditNotesResult.items.find((cn) => cn.id === creditNoteId) as Record<string, any> | undefined;

    if (!creditNote) {
      throw new Error('CREDIT_NOTE_NOT_FOUND');
    }

    if (creditNote.status === 'posted') {
      throw new Error('CREDIT_NOTE_ALREADY_POSTED');
    }

    const hasLinkedInvoice = Boolean(creditNote.invoiceId && creditNote.invoiceId !== '__supplier_balance__');
    if (hasLinkedInvoice) {
      const ordersResult = await pgGetPurchaseOrders({ page: 1, pageSize: 5000 });
      const invoice = ordersResult.items.find((entry) => entry.id === String(creditNote.invoiceId));
      if (!invoice) {
        throw new Error('INVOICE_NOT_FOUND');
      }
    }

    const suppliersResult = await pgGetSuppliers({ page: 1, pageSize: 5000 });
    const supplier = suppliersResult.items.find((entry) => entry.id === creditNote.supplierId) as Record<string, any> | undefined;
    if (!supplier) {
      throw new Error('SUPPLIER_NOT_FOUND');
    }

    const entries: Array<Record<string, unknown>> = [];
    const baseAmount = Number(creditNote.grandTotalBase || creditNote.grandTotal || 0);
    const taxAmount = Number(creditNote.taxAmount || 0);
    const baseSubTotal = Number(creditNote.subTotal || 0) - Number(creditNote.totalDiscount || 0);

    entries.push({
      accountId: '2100',
      accountCode: '2100',
      debit: baseAmount,
      credit: 0,
      description: `Credit Note: ${creditNote.creditNoteNumber}`,
    });

    if (creditNote.reason === 'return') {
      entries.push({
        accountId: '5150',
        accountCode: '5150',
        debit: 0,
        credit: baseSubTotal,
        description: `Credit Note Return: ${creditNote.creditNoteNumber}`,
      });
      if (taxAmount > 0) {
        entries.push({
          accountId: '1160',
          accountCode: '1160',
          debit: 0,
          credit: taxAmount,
          description: `Credit Note Tax: ${creditNote.creditNoteNumber}`,
        });
      }
    } else if (creditNote.reason === 'price-adjustment' || creditNote.reason === 'invoice-error') {
      entries.push({
        accountId: '5140',
        accountCode: '5140',
        debit: 0,
        credit: baseSubTotal,
        description: `Credit Note Adjustment: ${creditNote.creditNoteNumber}`,
      });
      if (taxAmount > 0) {
        entries.push({
          accountId: '1160',
          accountCode: '1160',
          debit: 0,
          credit: taxAmount,
          description: `Credit Note Tax: ${creditNote.creditNoteNumber}`,
        });
      }
    } else {
      entries.push({
        accountId: '5150',
        accountCode: '5150',
        debit: 0,
        credit: baseSubTotal,
        description: `Credit Note: ${creditNote.creditNoteNumber}`,
      });
      if (taxAmount > 0) {
        entries.push({
          accountId: '1160',
          accountCode: '1160',
          debit: 0,
          credit: taxAmount,
          description: `Credit Note Tax: ${creditNote.creditNoteNumber}`,
        });
      }
    }

    const journalEntry = {
      id: createPgId('je'),
      date: creditNote.date,
      reference: creditNote.creditNoteNumber,
      description: `Purchase Credit Note: ${creditNote.creditNoteNumber} - ${supplier.name}`,
      currencyCode: creditNote.currencyCode || 'USD',
      lines: entries,
      createdBy,
      createdAt: new Date().toISOString(),
      status: 'posted',
    };

    await pgSaveJournalEntry([journalEntry]);
    await pgUpdateSupplierBalance(String(creditNote.supplierId), -baseAmount);

    const updatedCreditNote = {
      ...creditNote,
      status: 'posted',
      postingStatus: 'posted',
      journalEntryId: journalEntry.id,
    };

    await pgSavePurchaseCreditNotes([updatedCreditNote]);
    return updatedCreditNote;
  }

  export async function pgCancelPurchaseCreditNote(creditNoteId: string, reason: string, cancelledBy: string) {
    const creditNotesResult = await pgGetPurchaseCreditNotes({ page: 1, pageSize: 5000 });
    const creditNote = creditNotesResult.items.find((cn) => cn.id === creditNoteId) as Record<string, any> | undefined;

    if (!creditNote) {
      throw new Error('CREDIT_NOTE_NOT_FOUND');
    }

    if (creditNote.status === 'cancelled') {
      throw new Error('CREDIT_NOTE_ALREADY_CANCELLED');
    }

    if (creditNote.status === 'posted' && creditNote.journalEntryId) {
      const journalEntriesResult = await pgGetJournalEntries({ page: 1, pageSize: 5000 });
      const entry = journalEntriesResult.items.find((journalEntry) => journalEntry.id === creditNote.journalEntryId) as Record<string, any> | undefined;
      if (entry) {
        const reversingEntry = {
          id: createPgId('je'),
          date: new Date().toISOString(),
          reference: `REVERSE-${creditNote.creditNoteNumber}`,
          description: `Reversal of Credit Note: ${creditNote.creditNoteNumber}`,
          currencyCode: entry.currencyCode,
          lines: (entry.lines || []).map((line: Record<string, any>) => ({
            ...line,
            debit: Number(line.credit || 0),
            credit: Number(line.debit || 0),
          })),
          createdBy: cancelledBy,
          createdAt: new Date().toISOString(),
          status: 'posted',
        };
        await pgSaveJournalEntry([reversingEntry]);
        creditNote.journalEntryId = reversingEntry.id;
      }
    }

    const updatedCreditNote = {
      ...creditNote,
      status: 'cancelled',
      cancelledAt: new Date().toISOString(),
      cancelledBy,
      cancelledReason: reason,
    };

    await pgSavePurchaseCreditNotes([updatedCreditNote]);
    return updatedCreditNote;
  }
