import { pgQuery } from './client';

export type PgPagination = {
  page?: number;
  pageSize?: number;
  search?: string;
  sortKey?: string;
  sortDir?: 'asc' | 'desc';
  hasAlternativesOnly?: boolean;
  availableOnly?: boolean;
  materialIds?: string[];
  materialFilters?: Partial<Record<'itemNumber' | 'name' | 'itemSymbolName' | 'globalReferenceNumber' | 'barcode' | 'secondaryBarcode' | 'additionalDetails' | 'salePrice' | 'location', string>>;
  materialFilterModes?: Partial<Record<'itemNumber' | 'name' | 'itemSymbolName' | 'globalReferenceNumber' | 'barcode' | 'secondaryBarcode' | 'additionalDetails' | 'salePrice' | 'location', 'contains' | 'startsWith' | 'endsWith'>>;
};

function getPaging(params?: PgPagination) {
  const pageSize = Math.min(5000, Math.max(1, Number(params?.pageSize || 25)));
  const page = Math.max(1, Number(params?.page || 1));
  const offset = (page - 1) * pageSize;
  return { page, pageSize, offset };
}

function normalizeSearchText(value: string): string {
  return String(value || '')
    .toLowerCase()
    .replace(/[\u064B-\u065F\u0670\u06D6-\u06ED]/g, '')
    .replace(/[إأآٱ]/g, 'ا')
    .replace(/ى/g, 'ي')
    .replace(/ؤ/g, 'و')
    .replace(/ئ/g, 'ي')
    .replace(/ة/g, 'ه')
    .replace(/[قف]/g, 'ف')
    .replace(/[صسث]/g, 'س')
    .replace(/[ضظ]/g, 'ض')
    .replace(/[تط]/g, 'ت')
    .replace(/[دذ]/g, 'د')
    .replace(/[زر]/g, 'ر')
    .replace(/[جحخ]/g, 'ح')
    .replace(/\s+/g, '')
    .replace(/[^\p{L}\p{N}]+/gu, '');
}

function normalizeSqlSearchText(value: string): string {
  return String(value || '')
    .toLowerCase()
    .replace(/[\u064B-\u065F\u0670\u06D6-\u06ED]/g, '')
    .replace(/\s+/g, ' ')
    .trim();
}

function escapeLikePattern(value: string): string {
  return String(value || '').replace(/[\\%_]/g, (match) => `\\${match}`);
}

function escapeRegexPattern(value: string): string {
  return String(value || '').replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
}

function buildTokenVariants(rawToken: string, maxVariants = 12): string[] {
  const base = normalizeSqlSearchText(rawToken);
  if (!base) return [];

  const variants = new Set<string>([base]);
  if (base.length >= 3) {
    for (let index = 0; index < base.length && variants.size < maxVariants; index += 1) {
      const shortened = `${base.slice(0, index)}${base.slice(index + 1)}`.trim();
      if (shortened.length >= 2) {
        variants.add(shortened);
      }
    }
  }

  return Array.from(variants);
}

function sortChars(value: string): string {
  return normalizeSearchText(value).split('').sort().join('');
}

function fuzzyMatchToken(token: string, field: string): boolean {
  if (field.includes(token)) return true;
  const tLen = token.length;
  const fLen = field.length;
  if (Math.abs(tLen - fLen) > 1) {
    if (tLen < 3) return false;
    const windowSize = tLen;
    for (let start = 0; start <= fLen - windowSize + 1; start += 1) {
      const window = field.slice(start, start + windowSize);
      let diff = 0;
      for (let index = 0; index < tLen && diff <= 1; index += 1) {
        if (window[index] !== token[index]) diff += 1;
      }
      if (diff <= 1) return true;
    }
    return false;
  }

  const dp: number[] = Array.from({ length: tLen + 1 }, (_, i) => i);
  for (let j = 1; j <= fLen; j += 1) {
    let prev = dp[0];
    dp[0] = j;
    for (let i = 1; i <= tLen; i += 1) {
      const temp = dp[i];
      dp[i] = token[i - 1] === field[j - 1]
        ? prev
        : 1 + Math.min(prev, dp[i], dp[i - 1]);
      prev = temp;
    }
  }
  return dp[tLen] <= 1;
}

function getLevenshteinDistance(left: string, right: string): number {
  const leftLen = left.length;
  const rightLen = right.length;

  if (leftLen === 0) return rightLen;
  if (rightLen === 0) return leftLen;

  const prev: number[] = Array.from({ length: rightLen + 1 }, (_, index) => index);
  const curr: number[] = new Array(rightLen + 1).fill(0);

  for (let i = 1; i <= leftLen; i += 1) {
    curr[0] = i;
    for (let j = 1; j <= rightLen; j += 1) {
      const substitutionCost = left[i - 1] === right[j - 1] ? 0 : 1;
      curr[j] = Math.min(
        prev[j] + 1,
        curr[j - 1] + 1,
        prev[j - 1] + substitutionCost,
      );
    }

    for (let j = 0; j <= rightLen; j += 1) {
      prev[j] = curr[j];
    }
  }

  return prev[rightLen];
}

function getOrderedSimilarityRatio(left: string, right: string): number {
  if (!left && !right) return 1;
  const maxLen = Math.max(left.length, right.length);
  if (maxLen === 0) return 1;
  const distance = getLevenshteinDistance(left, right);
  return 1 - (distance / maxLen);
}

function getSearchCandidates(value: string): string[] {
  const rawText = String(value || '').trim();
  const normalizedWhole = normalizeSearchText(rawText);
  const normalizedTokens = rawText
    .split(/\s+/)
    .map((part) => normalizeSearchText(part))
    .filter(Boolean);

  return Array.from(new Set([normalizedWhole, ...normalizedTokens].filter(Boolean)));
}

function fuzzyIncludesToken(
  token: string,
  fieldValue: string,
  options?: { strictOrdered95?: boolean }
): boolean {
  if (!token) return true;

  const candidates = getSearchCandidates(fieldValue);
  if (candidates.length === 0) return false;

  const strictOrdered95 = options?.strictOrdered95 === true;
  const minSimilarity = 0.95;

  return candidates.some((candidate) => {
    if (candidate.includes(token)) return true;
    if (fuzzyMatchToken(token, candidate)) return true;

    if (!strictOrdered95) {
      const sortedToken = sortChars(token);
      const sortedCandidate = sortChars(candidate);
      if (sortedToken && sortedCandidate.includes(sortedToken)) return true;
      return false;
    }

    const minWindowLength = Math.max(1, token.length - 1);
    const maxWindowLength = Math.min(candidate.length, token.length + 1);

    for (let windowLength = minWindowLength; windowLength <= maxWindowLength; windowLength += 1) {
      for (let start = 0; start <= candidate.length - windowLength; start += 1) {
        const window = candidate.slice(start, start + windowLength);
        if (fuzzyMatchToken(token, window)) return true;
        if (getOrderedSimilarityRatio(token, window) >= minSimilarity) return true;
      }
    }

    return false;
  });
}

function flattenSearchableValues(value: unknown, sink: string[]): void {
  if (value === null || value === undefined) return;
  if (Array.isArray(value)) {
    value.forEach((entry) => flattenSearchableValues(entry, sink));
    return;
  }
  if (typeof value === 'object') {
    Object.values(value as Record<string, unknown>).forEach((entry) => flattenSearchableValues(entry, sink));
    return;
  }
  if (typeof value === 'string' || typeof value === 'number' || typeof value === 'boolean') {
    const text = String(value).trim();
    if (text) sink.push(text);
  }
}

function collectMaterialSearchFields(material: Record<string, unknown>): string[] {
  const fields: string[] = [
    String(material.itemNumber || ''),
    String(material.name || ''),
    String(material.itemSymbolName || ''),
    String(material.globalReferenceNumber || ''),
    String(material.barcode || ''),
    String(material.secondaryBarcode || ''),
    String(material.additionalDetails || ''),
    String(material.salePrice ?? ''),
    String(material.purchasePrice ?? ''),
    String(material.primaryUnit || ''),
    String(material.secondaryUnit || ''),
    String(material.reorderUnit || ''),
  ];

  flattenSearchableValues(material.additionalBarcodes, fields);
  flattenSearchableValues(material.secondaryUnits, fields);
  flattenSearchableValues(material.productionFormula, fields);
  flattenSearchableValues(material.productShapes, fields);
  flattenSearchableValues(material.alternativeItemIds, fields);
  flattenSearchableValues(material, fields);

  return fields.filter(Boolean);
}

function matchesMaterialColumnFilters(
  material: Record<string, unknown>,
  filters?: PgPagination['materialFilters'],
  modes?: PgPagination['materialFilterModes']
): boolean {
  if (!filters) return true;

  const locationText = Array.isArray(material.stockLocations)
    ? (material.stockLocations as Array<Record<string, unknown>>)
        .map((entry) => `${String(entry.warehouseName || '')} ${String(entry.shelfName || '')}`.trim())
        .join(' ')
    : '';

  const fieldMap: Record<string, string> = {
    itemNumber: String(material.itemNumber || ''),
    name: String(material.name || ''),
    itemSymbolName: String(material.itemSymbolName || ''),
    globalReferenceNumber: String(material.globalReferenceNumber || ''),
    barcode: `${String(material.barcode || '')} ${Array.isArray(material.additionalBarcodes) ? material.additionalBarcodes.join(' ') : ''}`,
    secondaryBarcode: `${String(material.secondaryBarcode || '')} ${Array.isArray(material.secondaryUnits) ? (material.secondaryUnits as Array<Record<string, unknown>>).map((entry) => String(entry.barcode || '')).join(' ') : ''}`,
    additionalDetails: String(material.additionalDetails || ''),
    salePrice: String(material.salePrice ?? ''),
    location: locationText,
  };

  const strictOrderedColumns = new Set<string>(['name']);

  return Object.entries(filters).every(([key, rawValue]) => {
    const filterTokens = String(rawValue || '')
      .trim()
      .split(/\s+/)
      .map((part) => normalizeSearchText(part))
      .filter(Boolean);

    if (filterTokens.length === 0) return true;
    const mode = (modes?.[key as keyof NonNullable<PgPagination['materialFilterModes']>] || 'contains') as 'contains' | 'startsWith' | 'endsWith';
    return filterTokens.every((token) => {
      const fieldValue = fieldMap[key] || '';
      const candidates = getSearchCandidates(fieldValue);
      if (candidates.length === 0) return false;

      if (mode === 'startsWith') {
        return candidates.some((candidate) => {
          if (candidate.startsWith(token)) return true;
          const minLength = Math.max(1, token.length - 1);
          const maxLength = Math.min(candidate.length, token.length + 1);
          for (let length = minLength; length <= maxLength; length += 1) {
            if (fuzzyMatchToken(token, candidate.slice(0, length))) return true;
          }
          return false;
        });
      }

      if (mode === 'endsWith') {
        return candidates.some((candidate) => {
          if (candidate.endsWith(token)) return true;
          const minLength = Math.max(1, token.length - 1);
          const maxLength = Math.min(candidate.length, token.length + 1);
          for (let length = minLength; length <= maxLength; length += 1) {
            if (fuzzyMatchToken(token, candidate.slice(candidate.length - length))) return true;
          }
          return false;
        });
      }

      return fuzzyIncludesToken(token, fieldValue, {
        strictOrdered95: strictOrderedColumns.has(key),
      });
    });
  });
}

function compareMaterialRecords(
  left: Record<string, unknown>,
  right: Record<string, unknown>,
  sortKey: string,
  sortDir: 'ASC' | 'DESC',
): number {
  const direction = sortDir === 'ASC' ? 1 : -1;
  const valueByKey: Record<string, [unknown, unknown]> = {
    itemNumber: [left.itemNumber, right.itemNumber],
    name: [left.name, right.name],
    itemSymbolName: [left.itemSymbolName, right.itemSymbolName],
    globalReferenceNumber: [left.globalReferenceNumber, right.globalReferenceNumber],
    barcode: [left.barcode, right.barcode],
    secondaryBarcode: [left.secondaryBarcode, right.secondaryBarcode],
    additionalDetails: [left.additionalDetails, right.additionalDetails],
    salePrice: [left.salePrice, right.salePrice],
  };

  if (sortKey === 'salePrice') {
    const leftValue = Number(valueByKey.salePrice?.[0] || 0);
    const rightValue = Number(valueByKey.salePrice?.[1] || 0);
    if (leftValue !== rightValue) return (leftValue - rightValue) * direction;
  } else if (sortKey === 'itemNumber') {
    const leftRaw = String(valueByKey.itemNumber?.[0] || '');
    const rightRaw = String(valueByKey.itemNumber?.[1] || '');
    const leftNum = Number.parseFloat(leftRaw);
    const rightNum = Number.parseFloat(rightRaw);
    if (Number.isFinite(leftNum) && Number.isFinite(rightNum) && leftNum !== rightNum) {
      return (leftNum - rightNum) * direction;
    }
    const textCmp = leftRaw.localeCompare(rightRaw, 'ar', { numeric: true });
    if (textCmp !== 0) return textCmp * direction;
  } else if (sortKey && valueByKey[sortKey]) {
    const leftText = String(valueByKey[sortKey][0] || '');
    const rightText = String(valueByKey[sortKey][1] || '');
    const cmp = leftText.localeCompare(rightText, 'ar', { numeric: true });
    if (cmp !== 0) return cmp * direction;
  }

  const nameCmp = String(left.name || '').localeCompare(String(right.name || ''), 'ar', { numeric: true });
  if (nameCmp !== 0) return nameCmp;
  return String(right.updatedAt || right.updated_at || '').localeCompare(String(left.updatedAt || left.updated_at || ''));
}

function matchesAdvancedMaterialSearch(material: Record<string, unknown>, query: string): boolean {
  const tokens = String(query || '')
    .trim()
    .split(/\s+/)
    .map((part) => normalizeSearchText(part))
    .filter((part) => part.length > 0);

  if (tokens.length === 0) return true;

  const searchableFields = collectMaterialSearchFields(material).filter(Boolean);

  return tokens.every((token) => searchableFields.some((field) => fuzzyIncludesToken(token, field)));
}

type RawEntityConfig = {
  tableName: string;
  orderBy?: string;
};

function normalizeRawEntityPayload(payload: unknown): Array<Record<string, unknown>> {
  if (Array.isArray(payload)) {
    return payload.map((entry, index) => {
      if (entry && typeof entry === 'object' && !Array.isArray(entry)) {
        return entry as Record<string, unknown>;
      }
      return { value: entry, _index: index };
    });
  }

  if (payload && typeof payload === 'object') {
    const obj = payload as Record<string, unknown>;
    return Object.entries(obj).map(([key, value]) => {
      if (value && typeof value === 'object' && !Array.isArray(value)) {
        return { ...(value as Record<string, unknown>), id: String((value as Record<string, unknown>).id || key) };
      }
      return { id: key, value };
    });
  }

  if (payload === null || payload === undefined) {
    return [];
  }

  return [{ value: payload }];
}

function resolveRawEntityId(entry: Record<string, unknown>, index: number): string {
  const candidates = [
    entry.id,
    entry.code,
    entry.number,
    entry.invoiceId,
    entry.paymentId,
    entry.entryId,
    entry.name,
    entry._index,
  ];

  for (const candidate of candidates) {
    const value = String(candidate ?? '').trim();
    if (value) return value;
  }

  return `row-${index + 1}`;
}

async function upsertRawEntity(config: RawEntityConfig, payload: unknown): Promise<void> {
  const rows = normalizeRawEntityPayload(payload);
  if (!rows.length) return;

  await pgQuery('BEGIN');
  try {
    for (let index = 0; index < rows.length; index += 1) {
      const raw = rows[index];
      const id = resolveRawEntityId(raw, index);
      await pgQuery(
        `
        INSERT INTO ${config.tableName} (id, raw, updated_at)
        VALUES ($1, $2::jsonb, NOW())
        ON CONFLICT (id)
        DO UPDATE SET raw = EXCLUDED.raw, updated_at = NOW()
        `,
        [id, JSON.stringify({ ...raw, id })]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

async function listRawEntity(config: RawEntityConfig, params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const limitIndex = '$1';
  const offsetIndex = '$2';
  const orderBy = config.orderBy || 'updated_at DESC';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM ${config.tableName} ORDER BY ${orderBy} LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    [pageSize, offset]
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM ${config.tableName}`
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function upsertSettings(settings: Record<string, unknown>): Promise<void> {
  const entries = Object.entries(settings || {});
  if (entries.length === 0) return;

  await pgQuery('BEGIN');
  try {
    for (const [key, value] of entries) {
      await pgQuery(
        `
        INSERT INTO settings(key, value, updated_at)
        VALUES($1, $2::jsonb, NOW())
        ON CONFLICT (key)
        DO UPDATE SET value = EXCLUDED.value, updated_at = NOW()
        `,
        [key, JSON.stringify(value ?? null)]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function getSettingsMap(): Promise<Record<string, unknown>> {
  const result = await pgQuery<{ key: string; value: unknown }>('SELECT key, value FROM settings');
  const settings: Record<string, unknown> = {};
  for (const row of result.rows) {
    settings[row.key] = row.value;
  }
  return settings;
}

export async function upsertCustomers(customers: Array<Record<string, unknown>>): Promise<void> {
  if (!customers.length) return;
  await pgQuery('BEGIN');
  try {
    for (const customer of customers) {
      const id = String(customer.id || '');
      await pgQuery(
        `
        INSERT INTO customers (
          id, customer_number, name, phone, address, category,
          credit_limit, balance, allowed_discount, sales_rep_id, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5, $6,
          $7, $8, $9, $10, $11::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          customer_number = EXCLUDED.customer_number,
          name = EXCLUDED.name,
          phone = EXCLUDED.phone,
          address = EXCLUDED.address,
          category = EXCLUDED.category,
          credit_limit = EXCLUDED.credit_limit,
          balance = EXCLUDED.balance,
          allowed_discount = EXCLUDED.allowed_discount,
          sales_rep_id = EXCLUDED.sales_rep_id,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          customer.customerNumber ?? null,
          String(customer.name || ''),
          customer.phone ?? null,
          customer.address ?? null,
          customer.category ?? null,
          Number(customer.creditLimit || 0),
          Number(customer.balance || 0),
          Number(customer.allowedDiscount || 0),
          customer.salesRepId ?? null,
          JSON.stringify(customer),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createCustomer(customer: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(customer.id || '');
  await upsertCustomers([customer]);
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM customers WHERE id = $1 LIMIT 1', [id]);
  return row.rows[0]?.raw || customer;
}

export async function updateCustomerById(id: string, patch: Record<string, unknown>): Promise<Record<string, unknown> | null> {
  const current = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM customers WHERE id = $1 LIMIT 1', [id]);
  if (!current.rowCount) return null;
  const merged = { ...current.rows[0].raw, ...patch, id };
  await upsertCustomers([merged]);
  const next = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM customers WHERE id = $1 LIMIT 1', [id]);
  return next.rows[0]?.raw || merged;
}

export async function deleteCustomerById(id: string): Promise<boolean> {
  const result = await pgQuery('DELETE FROM customers WHERE id = $1', [id]);
  return (result.rowCount || 0) > 0;
}

export async function listCustomers(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

    const whereSql = search ? "WHERE LOWER(name) LIKE $1 OR LOWER(COALESCE(customer_number, '')) LIKE $1" : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM customers ${whereSql} ORDER BY updated_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM customers ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function upsertSuppliers(suppliers: Array<Record<string, unknown>>): Promise<void> {
  if (!suppliers.length) return;
  await pgQuery('BEGIN');
  try {
    for (const supplier of suppliers) {
      const id = String(supplier.id || '');
      await pgQuery(
        `
        INSERT INTO suppliers (
          id, supplier_number, name, phone, address, payment_terms, tax_number,
          account_id, account_code, balance, inactive, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7,
          $8, $9, $10, $11, $12::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          supplier_number = EXCLUDED.supplier_number,
          name = EXCLUDED.name,
          phone = EXCLUDED.phone,
          address = EXCLUDED.address,
          payment_terms = EXCLUDED.payment_terms,
          tax_number = EXCLUDED.tax_number,
          account_id = EXCLUDED.account_id,
          account_code = EXCLUDED.account_code,
          balance = EXCLUDED.balance,
          inactive = EXCLUDED.inactive,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          supplier.supplierNumber ?? null,
          String(supplier.name || ''),
          supplier.phone ?? null,
          supplier.address ?? null,
          Number(supplier.paymentTerms || 0),
          supplier.taxNumber ?? null,
          supplier.accountId ?? null,
          supplier.accountCode ?? null,
          Number(supplier.balance || 0),
          Boolean(supplier.inactive || false),
          JSON.stringify(supplier),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createSupplier(supplier: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(supplier.id || '');
  await upsertSuppliers([supplier]);
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM suppliers WHERE id = $1 LIMIT 1', [id]);
  return row.rows[0]?.raw || supplier;
}

export async function updateSupplierById(id: string, patch: Record<string, unknown>): Promise<Record<string, unknown> | null> {
  const current = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM suppliers WHERE id = $1 LIMIT 1', [id]);
  if (!current.rowCount) return null;
  const merged = { ...current.rows[0].raw, ...patch, id };
  await upsertSuppliers([merged]);
  const next = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM suppliers WHERE id = $1 LIMIT 1', [id]);
  return next.rows[0]?.raw || merged;
}

export async function deleteSupplierById(id: string): Promise<boolean> {
  const result = await pgQuery('DELETE FROM suppliers WHERE id = $1', [id]);
  return (result.rowCount || 0) > 0;
}

export async function listSuppliers(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

  const whereSql = search ? "WHERE LOWER(name) LIKE $1 OR LOWER(COALESCE(supplier_number, '')) LIKE $1" : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM suppliers ${whereSql} ORDER BY updated_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM suppliers ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function upsertMaterials(materials: Array<Record<string, unknown>>): Promise<void> {
  if (!materials.length) return;
  await pgQuery('BEGIN');
  try {
    for (const material of materials) {
      const id = String(material.id || '');
      await pgQuery(
        `
        INSERT INTO materials (
          id, item_number, name, barcode, sale_price, purchase_price, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          item_number = EXCLUDED.item_number,
          name = EXCLUDED.name,
          barcode = EXCLUDED.barcode,
          sale_price = EXCLUDED.sale_price,
          purchase_price = EXCLUDED.purchase_price,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          material.itemNumber ?? null,
          String(material.name || ''),
          material.barcode ?? null,
          Number(material.salePrice || 0),
          Number(material.purchasePrice || 0),
          JSON.stringify(material),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createMaterial(material: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(material.id || '');
  await upsertMaterials([material]);
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM materials WHERE id = $1 LIMIT 1', [id]);
  return row.rows[0]?.raw || material;
}

export async function getMaterialById(id: string): Promise<Record<string, unknown> | null> {
  const normalizedId = String(id || '').trim();
  if (!normalizedId) return null;
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM materials WHERE id = $1 LIMIT 1', [normalizedId]);
  return row.rows[0]?.raw || null;
}

export async function updateMaterialById(id: string, patch: Record<string, unknown>): Promise<Record<string, unknown> | null> {
  const current = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM materials WHERE id = $1 LIMIT 1', [id]);
  if (!current.rowCount) return null;
  const merged = { ...current.rows[0].raw, ...patch, id };
  await upsertMaterials([merged]);
  const next = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM materials WHERE id = $1 LIMIT 1', [id]);
  return next.rows[0]?.raw || merged;
}

export async function deleteMaterialById(id: string): Promise<boolean> {
  const result = await pgQuery('DELETE FROM materials WHERE id = $1', [id]);
  return (result.rowCount || 0) > 0;
}

export async function listMaterials(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();
  const hasAlternativesOnly = params.hasAlternativesOnly === true;
  const materialIds = Array.isArray(params.materialIds)
    ? params.materialIds.map((value) => String(value || '').trim()).filter(Boolean)
    : [];
  const materialFilters = params.materialFilters;
  const materialFilterModes = params.materialFilterModes;
  const hasMaterialFilters = !!materialFilters && Object.values(materialFilters).some((value) => String(value || '').trim() !== '');
  const sortKey = String(params.sortKey || '').trim();
  const sortDir = String(params.sortDir || 'desc').trim().toLowerCase() === 'asc' ? 'ASC' : 'DESC';

  const orderByMap: Record<string, string> = {
    itemNumber: "COALESCE(item_number, '')",
    name: "COALESCE(name, '')",
    itemSymbolName: "COALESCE(raw->>'itemSymbolName', '')",
    globalReferenceNumber: "COALESCE(raw->>'globalReferenceNumber', '')",
    barcode: "COALESCE(barcode, '')",
    secondaryBarcode: "COALESCE(raw->>'secondaryBarcode', '')",
    additionalDetails: "COALESCE(raw->>'additionalDetails', '')",
    salePrice: "COALESCE(sale_price, 0)",
  };
  const orderByExpr = orderByMap[sortKey] || 'updated_at';
  const secondaryOrder = sortKey === 'salePrice' ? ', updated_at DESC' : ", COALESCE(name, '') ASC, updated_at DESC";

  if (search || hasMaterialFilters || hasAlternativesOnly || materialIds.length > 0) {
    // Build SQL WHERE conditions for column filters that map to real columns/jsonb fields.
    // This pre-filters in DB to reduce JS-side work significantly.
    const sqlConditions: string[] = [];
    const sqlValues: unknown[] = [];

    const addLikeByMode = (expr: string, rawValue: string, mode: 'contains' | 'startsWith' | 'endsWith' = 'contains') => {
      const variants = buildTokenVariants(rawValue, 12);
      if (variants.length === 0) return;

      const normalizedExpr = `LOWER(LTRIM(regexp_replace(${expr}, '[\\u064B-\\u065F\\u0670\\u06D6-\\u06ED]', '', 'g')))`;
      const localOr: string[] = [];

      variants.forEach((variant) => {
        const escapedVariant = escapeLikePattern(variant);
        const pattern = mode === 'startsWith'
          ? `${escapedVariant}%`
          : mode === 'endsWith'
            ? `%${escapedVariant}`
            : `%${escapedVariant}%`;
        sqlValues.push(pattern);
        localOr.push(`${normalizedExpr} LIKE $${sqlValues.length} ESCAPE E'\\\\'`);
      });

      const baseToken = variants[0] || '';
      if (baseToken.length >= 2) {
        const regex = baseToken.split('').map((char) => escapeRegexPattern(char)).join('.*');
        if (regex) {
          const modePrefix = mode === 'startsWith' ? '^' : '';
          const modeSuffix = mode === 'endsWith' ? '$' : '';
          sqlValues.push(`${modePrefix}${regex}${modeSuffix}`);
          localOr.push(`${normalizedExpr} ~ $${sqlValues.length}`);
        }
      }

      if (localOr.length > 0) {
        sqlConditions.push(`(${localOr.join(' OR ')})`);
      }
    };

    if (materialFilters) {
      const { itemNumber, name, barcode, salePrice } = materialFilters;
      if (itemNumber) addLikeByMode("COALESCE(item_number, '')", itemNumber, materialFilterModes?.itemNumber || 'contains');
      if (name) addLikeByMode("COALESCE(name, '')", name, materialFilterModes?.name || 'contains');
      if (barcode) addLikeByMode("COALESCE(barcode, '')", barcode, materialFilterModes?.barcode || 'contains');
      if (salePrice) {
        const priceNum = Number(salePrice);
        if (Number.isFinite(priceNum)) {
          sqlValues.push(priceNum);
          sqlConditions.push(`COALESCE(sale_price, 0) = $${sqlValues.length}`);
        }
      }
    }

    if (search) {
      const searchTokens = String(search || '')
        .trim()
        .split(/\s+/)
        .map((part) => normalizeSqlSearchText(part))
        .filter(Boolean);

      const searchExprs = [
        "COALESCE(item_number, '')",
        "COALESCE(name, '')",
        "COALESCE(barcode, '')",
        "COALESCE(raw->>'itemSymbolName', '')",
        "COALESCE(raw->>'globalReferenceNumber', '')",
        "COALESCE(raw->>'additionalDetails', '')",
      ];

      searchTokens.forEach((token) => {
        const variants = buildTokenVariants(token, 12);
        if (variants.length === 0) return;

        const perExprOr: string[] = [];
        searchExprs.forEach((expr) => {
          const normalizedExpr = `LOWER(LTRIM(regexp_replace(${expr}, '[\\u064B-\\u065F\\u0670\\u06D6-\\u06ED]', '', 'g')))`;

          variants.forEach((variant) => {
            const escapedVariant = escapeLikePattern(variant);
            sqlValues.push(`%${escapedVariant}%`);
            perExprOr.push(`${normalizedExpr} LIKE $${sqlValues.length} ESCAPE E'\\\\'`);
          });

          const baseToken = variants[0] || '';
          if (baseToken.length >= 2) {
            const regex = baseToken.split('').map((char) => escapeRegexPattern(char)).join('.*');
            if (regex) {
              sqlValues.push(regex);
              perExprOr.push(`${normalizedExpr} ~ $${sqlValues.length}`);
            }
          }
        });

        if (perExprOr.length > 0) {
          sqlConditions.push(`(${perExprOr.join(' OR ')})`);
        }
      });
    }

    if (hasAlternativesOnly) {
      sqlConditions.push(`jsonb_typeof(raw->'alternativeItemIds') = 'array' AND jsonb_array_length(raw->'alternativeItemIds') > 0`);
    }

    if (materialIds.length > 0) {
      sqlValues.push(materialIds);
      sqlConditions.push(`id = ANY($${sqlValues.length}::text[])`);
    }

    const whereClause = sqlConditions.length > 0 ? `WHERE ${sqlConditions.join(' AND ')}` : '';

    const candidateRows = await pgQuery<{ raw: Record<string, unknown> }>(
      `SELECT raw FROM materials ${whereClause}`,
      sqlValues
    );

    const matched = candidateRows.rows
      .map((row) => row.raw)
      .filter((row) => {
        // hasAlternativesOnly: handled in SQL, skip JS check
        // column filters on non-SQL fields still need JS check
        const passesSearch = !search || matchesAdvancedMaterialSearch(row, search);
        const passesColumnFilters = matchesMaterialColumnFilters(row, materialFilters, materialFilterModes);
        return passesSearch && passesColumnFilters;
      })
      .sort((left, right) => compareMaterialRecords(left, right, sortKey, sortDir));

    const totalItems = matched.length;
    const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));
    const pageItems = matched.slice(offset, offset + pageSize);
    const withBarcodeCount = matched.filter((entry) => String(entry.barcode || '').trim() !== '').length;
    const totalSaleValue = matched.reduce((sum, entry) => sum + Number(entry.salePrice || 0), 0);

    return {
      items: pageItems,
      page,
      pageSize,
      totalItems,
      totalPages,
      hasNext: page < totalPages,
      hasPrev: page > 1,
      withBarcodeCount,
      totalSaleValue,
    };
  }

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM materials ORDER BY ${orderByExpr} ${sortDir}${secondaryOrder} LIMIT $1::int OFFSET $2::int`,
    [pageSize, offset]
  );

  const count = await pgQuery<{ total: string }>('SELECT COUNT(*)::text AS total FROM materials');

  const stats = await pgQuery<{ with_barcode_count: string; total_sale_value: string }>(
    `SELECT COUNT(*) FILTER (WHERE COALESCE(barcode, '') <> '')::text AS with_barcode_count, COALESCE(SUM(COALESCE(sale_price, 0)), 0)::text AS total_sale_value FROM materials`
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));
  const withBarcodeCount = Number(stats.rows[0]?.with_barcode_count || 0);
  const totalSaleValue = Number(stats.rows[0]?.total_sale_value || 0);

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
    withBarcodeCount,
    totalSaleValue,
  };
}

export async function listMaterialsForEditor(params: { pageSize?: number; search?: string; excludeId?: string } = {}) {
  const pageSize = Math.min(5000, Math.max(1, Number(params.pageSize || 5000) || 5000));
  const search = String(params.search || '').trim();
  const excludeId = String(params.excludeId || '').trim();
  const conditions: string[] = [];
  const values: unknown[] = [];

  if (excludeId) {
    values.push(excludeId);
    conditions.push(`id <> $${values.length}`);
  }

  if (search) {
    const searchPattern = `%${search.toLowerCase()}%`;
    values.push(searchPattern);
    const searchIndex = `$${values.length}`;
    conditions.push(`(
      LOWER(COALESCE(name, '')) LIKE ${searchIndex}
      OR LOWER(COALESCE(item_number, '')) LIKE ${searchIndex}
      OR LOWER(COALESCE(barcode, '')) LIKE ${searchIndex}
      OR LOWER(COALESCE(raw->>'itemSymbolName', '')) LIKE ${searchIndex}
      OR LOWER(COALESCE(raw->>'globalReferenceNumber', '')) LIKE ${searchIndex}
      OR LOWER(COALESCE(raw->>'additionalDetails', '')) LIKE ${searchIndex}
    )`);
  }

  values.push(pageSize);
  const limitIndex = `$${values.length}`;
  const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT jsonb_build_object(
      'id', id,
      'itemNumber', item_number,
      'name', name,
      'itemSymbolName', COALESCE(raw->>'itemSymbolName', ''),
      'globalReferenceNumber', COALESCE(raw->>'globalReferenceNumber', ''),
      'barcode', barcode,
      'secondaryBarcode', COALESCE(raw->>'secondaryBarcode', ''),
      'additionalBarcodes', COALESCE(raw->'additionalBarcodes', '[]'::jsonb),
      'secondaryUnits', COALESCE(raw->'secondaryUnits', '[]'::jsonb),
      'salePrice', COALESCE(sale_price, 0),
      'purchasePrice', COALESCE(purchase_price, 0),
      'additionalDetails', COALESCE(raw->>'additionalDetails', ''),
      'primaryUnit', COALESCE(raw->>'primaryUnit', ''),
      'primaryUnitQuantity', COALESCE((raw->>'primaryUnitQuantity')::numeric, 1),
      'secondaryUnit', COALESCE(raw->>'secondaryUnit', ''),
      'secondaryUnitQuantity', COALESCE((raw->>'secondaryUnitQuantity')::numeric, 0),
      'secondaryUnitPrice', COALESCE((raw->>'secondaryUnitPrice')::numeric, 0),
      'groupId', COALESCE(raw->>'groupId', ''),
      'usesDefaultTax', COALESCE((raw->>'usesDefaultTax')::boolean, true),
      'taxRate', COALESCE((raw->>'taxRate')::numeric, 0),
      'alternativeItemIds', COALESCE(raw->'alternativeItemIds', '[]'::jsonb)
    ) AS raw
    FROM materials
    ${whereClause}
    ORDER BY COALESCE(name, '') ASC, updated_at DESC
    LIMIT ${limitIndex}::int`,
    values,
  );

  return {
    items: rows.rows.map((row) => row.raw),
  };
}

export async function listMaterialsForEditorByIds(ids: string[] = [], params: { excludeId?: string } = {}) {
  const excludeId = String(params.excludeId || '').trim();
  const normalizedIds = Array.from(
    new Set(
      (Array.isArray(ids) ? ids : [])
        .map((value) => String(value || '').trim())
        .filter((value) => value && value !== excludeId)
    )
  );

  if (normalizedIds.length === 0) {
    return {
      items: [] as Record<string, unknown>[],
    };
  }

  const values: unknown[] = [normalizedIds];
  const conditions: string[] = [`id = ANY($1::text[])`];

  if (excludeId) {
    values.push(excludeId);
    conditions.push(`id <> $${values.length}`);
  }

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT jsonb_build_object(
      'id', id,
      'itemNumber', item_number,
      'name', name,
      'itemSymbolName', COALESCE(raw->>'itemSymbolName', ''),
      'globalReferenceNumber', COALESCE(raw->>'globalReferenceNumber', ''),
      'barcode', barcode,
      'secondaryBarcode', COALESCE(raw->>'secondaryBarcode', ''),
      'additionalBarcodes', COALESCE(raw->'additionalBarcodes', '[]'::jsonb),
      'secondaryUnits', COALESCE(raw->'secondaryUnits', '[]'::jsonb),
      'salePrice', COALESCE(sale_price, 0),
      'purchasePrice', COALESCE(purchase_price, 0),
      'additionalDetails', COALESCE(raw->>'additionalDetails', ''),
      'primaryUnit', COALESCE(raw->>'primaryUnit', ''),
      'primaryUnitQuantity', COALESCE((raw->>'primaryUnitQuantity')::numeric, 1),
      'secondaryUnit', COALESCE(raw->>'secondaryUnit', ''),
      'secondaryUnitQuantity', COALESCE((raw->>'secondaryUnitQuantity')::numeric, 0),
      'secondaryUnitPrice', COALESCE((raw->>'secondaryUnitPrice')::numeric, 0),
      'groupId', COALESCE(raw->>'groupId', ''),
      'usesDefaultTax', COALESCE((raw->>'usesDefaultTax')::boolean, true),
      'taxRate', COALESCE((raw->>'taxRate')::numeric, 0),
      'alternativeItemIds', COALESCE(raw->'alternativeItemIds', '[]'::jsonb)
    ) AS raw
    FROM materials
    WHERE ${conditions.join(' AND ')}
    ORDER BY COALESCE(name, '') ASC, updated_at DESC`,
    values,
  );

  return {
    items: rows.rows.map((row) => row.raw),
  };
}

export async function upsertExpenses(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || entry.itemNumber || '').trim();
      if (!id) continue;
      await pgQuery(
        `
        INSERT INTO expenses (
          id, item_number, name, sale_price, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          item_number = EXCLUDED.item_number,
          name = EXCLUDED.name,
          sale_price = EXCLUDED.sale_price,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          entry.itemNumber ?? id,
          String(entry.name || ''),
          Number(entry.salePrice || 0),
          JSON.stringify({ ...entry, id, itemNumber: entry.itemNumber ?? id }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createExpense(item: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(item.id || item.itemNumber || '').trim();
  await upsertExpenses([item]);
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM expenses WHERE id = $1 LIMIT 1', [id]);
  return row.rows[0]?.raw || item;
}

export async function updateExpenseById(id: string, patch: Record<string, unknown>): Promise<Record<string, unknown> | null> {
  const current = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM expenses WHERE id = $1 LIMIT 1', [id]);
  if (!current.rowCount) return null;
  const merged = { ...current.rows[0].raw, ...patch, id };
  await upsertExpenses([merged]);
  const next = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM expenses WHERE id = $1 LIMIT 1', [id]);
  return next.rows[0]?.raw || merged;
}

export async function deleteExpenseById(id: string): Promise<boolean> {
  const result = await pgQuery('DELETE FROM expenses WHERE id = $1', [id]);
  return (result.rowCount || 0) > 0;
}

export async function listExpenses(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

  const whereSql = search ? "WHERE LOWER(name) LIKE $1 OR LOWER(COALESCE(item_number, '')) LIKE $1" : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM expenses ${whereSql} ORDER BY updated_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM expenses ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function upsertProductShapeDefinitions(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || entry.itemNumber || '').trim();
      if (!id) continue;
      await pgQuery(
        `
        INSERT INTO product_shape_definitions (
          id, item_number, name, sale_price, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          item_number = EXCLUDED.item_number,
          name = EXCLUDED.name,
          sale_price = EXCLUDED.sale_price,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          entry.itemNumber ?? id,
          String(entry.name || ''),
          Number(entry.salePrice || 0),
          JSON.stringify({ ...entry, id, itemNumber: entry.itemNumber ?? id }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createProductShapeDefinition(item: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(item.id || item.itemNumber || '').trim();
  await upsertProductShapeDefinitions([item]);
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM product_shape_definitions WHERE id = $1 LIMIT 1', [id]);
  return row.rows[0]?.raw || item;
}

export async function updateProductShapeDefinitionById(id: string, patch: Record<string, unknown>): Promise<Record<string, unknown> | null> {
  const current = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM product_shape_definitions WHERE id = $1 LIMIT 1', [id]);
  if (!current.rowCount) return null;
  const merged = { ...current.rows[0].raw, ...patch, id };
  await upsertProductShapeDefinitions([merged]);
  const next = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM product_shape_definitions WHERE id = $1 LIMIT 1', [id]);
  return next.rows[0]?.raw || merged;
}

export async function deleteProductShapeDefinitionById(id: string): Promise<boolean> {
  const result = await pgQuery('DELETE FROM product_shape_definitions WHERE id = $1', [id]);
  return (result.rowCount || 0) > 0;
}

export async function listProductShapeDefinitions(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

  const whereSql = search ? "WHERE LOWER(name) LIKE $1 OR LOWER(COALESCE(item_number, '')) LIKE $1" : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM product_shape_definitions ${whereSql} ORDER BY updated_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM product_shape_definitions ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function upsertSalesReps(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || '').trim();
      if (!id) continue;
      await pgQuery(
        `
        INSERT INTO sales_reps (
          id, name, phone, region, commission_rate, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5, $6::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          name = EXCLUDED.name,
          phone = EXCLUDED.phone,
          region = EXCLUDED.region,
          commission_rate = EXCLUDED.commission_rate,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          String(entry.name || ''),
          entry.phone ?? null,
          entry.region ?? null,
          Number(entry.commissionRate || 0),
          JSON.stringify({ ...entry, id }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createSalesRep(item: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(item.id || '').trim();
  await upsertSalesReps([item]);
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM sales_reps WHERE id = $1 LIMIT 1', [id]);
  return row.rows[0]?.raw || item;
}

export async function updateSalesRepById(id: string, patch: Record<string, unknown>): Promise<Record<string, unknown> | null> {
  const current = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM sales_reps WHERE id = $1 LIMIT 1', [id]);
  if (!current.rowCount) return null;
  const merged = { ...current.rows[0].raw, ...patch, id };
  await upsertSalesReps([merged]);
  const next = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM sales_reps WHERE id = $1 LIMIT 1', [id]);
  return next.rows[0]?.raw || merged;
}

export async function deleteSalesRepById(id: string): Promise<boolean> {
  const result = await pgQuery('DELETE FROM sales_reps WHERE id = $1', [id]);
  return (result.rowCount || 0) > 0;
}

export async function listSalesReps(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

  const whereSql = search ? "WHERE LOWER(name) LIKE $1 OR LOWER(COALESCE(phone, '')) LIKE $1 OR LOWER(COALESCE(region, '')) LIKE $1" : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM sales_reps ${whereSql} ORDER BY updated_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM sales_reps ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function upsertManagedUnits(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || '').trim();
      if (!id) continue;
      const unitNumber = String(entry.unitNumber || '').trim();
      await pgQuery(
        `
        INSERT INTO managed_units (
          id, unit_number, description, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          unit_number = EXCLUDED.unit_number,
          description = EXCLUDED.description,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          unitNumber || null,
          String(entry.description || ''),
          JSON.stringify({ ...entry, id, unitNumber: unitNumber || entry.unitNumber || '' }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createManagedUnit(item: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(item.id || '').trim();
  await upsertManagedUnits([item]);
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM managed_units WHERE id = $1 LIMIT 1', [id]);
  return row.rows[0]?.raw || item;
}

export async function updateManagedUnitById(id: string, patch: Record<string, unknown>): Promise<Record<string, unknown> | null> {
  const current = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM managed_units WHERE id = $1 LIMIT 1', [id]);
  if (!current.rowCount) return null;
  const merged = { ...current.rows[0].raw, ...patch, id };
  await upsertManagedUnits([merged]);
  const next = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM managed_units WHERE id = $1 LIMIT 1', [id]);
  return next.rows[0]?.raw || merged;
}

export async function deleteManagedUnitById(id: string): Promise<boolean> {
  const result = await pgQuery('DELETE FROM managed_units WHERE id = $1', [id]);
  return (result.rowCount || 0) > 0;
}

export async function listManagedUnits(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

  const whereSql = search ? "WHERE LOWER(description) LIKE $1 OR LOWER(COALESCE(unit_number, '')) LIKE $1" : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM managed_units ${whereSql} ORDER BY updated_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM managed_units ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function upsertItemGroups(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || '').trim();
      if (!id) continue;
      await pgQuery(
        `
        INSERT INTO item_groups (
          id, name, is_pos_enabled, uses_default_tax, tax_rate, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5, $6::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          name = EXCLUDED.name,
          is_pos_enabled = EXCLUDED.is_pos_enabled,
          uses_default_tax = EXCLUDED.uses_default_tax,
          tax_rate = EXCLUDED.tax_rate,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          String(entry.name || ''),
          Boolean(entry.isPosEnabled || false),
          entry.usesDefaultTax === undefined ? true : Boolean(entry.usesDefaultTax),
          entry.taxRate === undefined || entry.taxRate === null ? null : Number(entry.taxRate),
          JSON.stringify({ ...entry, id }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createItemGroup(item: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(item.id || '').trim();
  const name = String(item.name || '');
  const isPosEnabled = Boolean(item.isPosEnabled || false);
  const usesDefaultTax = item.usesDefaultTax === undefined ? true : Boolean(item.usesDefaultTax);
  const taxRate = item.taxRate === undefined || item.taxRate === null ? null : Number(item.taxRate);

  await pgQuery('BEGIN');
  try {
    // Serialize number generation to prevent duplicates during concurrent inserts.
    await pgQuery('SELECT pg_advisory_xact_lock($1)', [2403001]);

    const maxNumberRow = await pgQuery<{ max_number: string }>(
      `
      SELECT COALESCE(
        MAX((NULLIF(REGEXP_REPLACE(COALESCE(raw->>'groupNumber', ''), '[^0-9]', '', 'g'), ''))::bigint),
        0
      )::text AS max_number
      FROM item_groups
      `
    );
    const maxNumber = Number(maxNumberRow.rows[0]?.max_number || 0);
    const nextGroupNumber = String(Number.isFinite(maxNumber) ? maxNumber + 1 : 1);

    const payload = { ...item, id, groupNumber: nextGroupNumber };

    await pgQuery(
      `
      INSERT INTO item_groups (
        id, name, is_pos_enabled, uses_default_tax, tax_rate, raw, updated_at
      ) VALUES (
        $1, $2, $3, $4, $5, $6::jsonb, NOW()
      )
      ON CONFLICT (id)
      DO UPDATE SET
        name = EXCLUDED.name,
        is_pos_enabled = EXCLUDED.is_pos_enabled,
        uses_default_tax = EXCLUDED.uses_default_tax,
        tax_rate = EXCLUDED.tax_rate,
        raw = EXCLUDED.raw,
        updated_at = NOW()
      `,
      [
        id,
        name,
        isPosEnabled,
        usesDefaultTax,
        taxRate,
        JSON.stringify(payload),
      ]
    );

    const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM item_groups WHERE id = $1 LIMIT 1', [id]);
    await pgQuery('COMMIT');
    return row.rows[0]?.raw || payload;
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function updateItemGroupById(id: string, patch: Record<string, unknown>): Promise<Record<string, unknown> | null> {
  const current = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM item_groups WHERE id = $1 LIMIT 1', [id]);
  if (!current.rowCount) return null;
  const merged = { ...current.rows[0].raw, ...patch, id };
  await upsertItemGroups([merged]);
  const next = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM item_groups WHERE id = $1 LIMIT 1', [id]);
  return next.rows[0]?.raw || merged;
}

export async function deleteItemGroupById(id: string): Promise<boolean> {
  const result = await pgQuery('DELETE FROM item_groups WHERE id = $1', [id]);
  return (result.rowCount || 0) > 0;
}

export async function listItemGroups(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

  const whereSql = search ? "WHERE LOWER(name) LIKE $1" : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM item_groups ${whereSql} ORDER BY updated_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM item_groups ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function upsertWarehouses(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || '').trim();
      if (!id) continue;
      const shelves = Array.isArray(entry.shelves) ? entry.shelves : [];
      await pgQuery(
        `
        INSERT INTO warehouses (
          id, name, shelves, raw, updated_at
        ) VALUES (
          $1, $2, $3::jsonb, $4::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          name = EXCLUDED.name,
          shelves = EXCLUDED.shelves,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          String(entry.name || ''),
          JSON.stringify(shelves),
          JSON.stringify({ ...entry, id, shelves }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createWarehouse(item: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(item.id || '').trim();
  await upsertWarehouses([item]);
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM warehouses WHERE id = $1 LIMIT 1', [id]);
  return row.rows[0]?.raw || item;
}

export async function updateWarehouseById(id: string, patch: Record<string, unknown>): Promise<Record<string, unknown> | null> {
  const current = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM warehouses WHERE id = $1 LIMIT 1', [id]);
  if (!current.rowCount) return null;
  const merged = { ...current.rows[0].raw, ...patch, id };
  await upsertWarehouses([merged]);
  const next = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM warehouses WHERE id = $1 LIMIT 1', [id]);
  return next.rows[0]?.raw || merged;
}

export async function deleteWarehouseById(id: string): Promise<boolean> {
  const result = await pgQuery('DELETE FROM warehouses WHERE id = $1', [id]);
  return (result.rowCount || 0) > 0;
}

export async function listWarehouses(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

  const whereSql = search ? "WHERE LOWER(name) LIKE $1" : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM warehouses ${whereSql} ORDER BY updated_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM warehouses ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function listActiveWarehouses() {
  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM warehouses WHERE (raw->>'inactive')::boolean IS NOT TRUE ORDER BY raw->>'warehouseNumber'`
  );
  const items = rows.rows.map((row) => {
    const wh = row.raw as any;
    return {
      ...wh,
      shelves: Array.isArray(wh.shelves)
        ? wh.shelves.filter((shelf: any) => shelf?.inactive !== true)
        : [],
    };
  });
  return { items };
}

export async function upsertBanks(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || '').trim();
      if (!id) continue;
      await pgQuery(
        `
        INSERT INTO banks (
          id, code, name, name_en, account_id, account_code, bank_number, full_name,
          branch, iban, currency_code, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7, $8,
          $9, $10, $11, $12::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          code = EXCLUDED.code,
          name = EXCLUDED.name,
          name_en = EXCLUDED.name_en,
          account_id = EXCLUDED.account_id,
          account_code = EXCLUDED.account_code,
          bank_number = EXCLUDED.bank_number,
          full_name = EXCLUDED.full_name,
          branch = EXCLUDED.branch,
          iban = EXCLUDED.iban,
          currency_code = EXCLUDED.currency_code,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          String(entry.code || ''),
          String(entry.name || ''),
          entry.nameEn ?? null,
          entry.accountId ?? null,
          entry.accountCode ?? null,
          entry.bankNumber ?? null,
          entry.fullName ?? null,
          entry.branch ?? null,
          entry.iban ?? null,
          entry.currencyCode ?? null,
          JSON.stringify({ ...entry, id }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createBank(item: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(item.id || '').trim();
  try {
    await upsertBanks([item]);
  } catch (error: any) {
    if (error?.code === '23505') {
      throw new Error('BANK_CODE_EXISTS');
    }
    throw error;
  }
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM banks WHERE id = $1 LIMIT 1', [id]);
  return row.rows[0]?.raw || item;
}

export async function updateBankById(id: string, patch: Record<string, unknown>): Promise<Record<string, unknown> | null> {
  const current = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM banks WHERE id = $1 LIMIT 1', [id]);
  if (!current.rowCount) return null;
  const merged = { ...current.rows[0].raw, ...patch, id };
  try {
    await upsertBanks([merged]);
  } catch (error: any) {
    if (error?.code === '23505') {
      throw new Error('BANK_CODE_EXISTS');
    }
    throw error;
  }
  const next = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM banks WHERE id = $1 LIMIT 1', [id]);
  return next.rows[0]?.raw || merged;
}

export async function deleteBankById(id: string): Promise<boolean> {
  const result = await pgQuery('DELETE FROM banks WHERE id = $1', [id]);
  return (result.rowCount || 0) > 0;
}

export async function getBankByIdFromPg(id: string): Promise<Record<string, unknown> | null> {
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM banks WHERE id = $1 LIMIT 1', [id]);
  return row.rows[0]?.raw || null;
}

export async function listBanks(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

  const whereSql = search ? "WHERE LOWER(name) LIKE $1 OR LOWER(code) LIKE $1 OR LOWER(COALESCE(bank_number, '')) LIKE $1" : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM banks ${whereSql} ORDER BY updated_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM banks ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function upsertCustomerPricing(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const category = String(entry.category || '').trim();
      if (!category) continue;
      const normalized = {
        category,
        prices: Array.isArray(entry.prices) ? entry.prices : [],
        bonus: entry.bonus,
      };
      await pgQuery(
        `
        INSERT INTO customer_pricing (
          category, raw, updated_at
        ) VALUES (
          $1, $2::jsonb, NOW()
        )
        ON CONFLICT (category)
        DO UPDATE SET
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [category, JSON.stringify(normalized)]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function setCustomerPricingByCategory(item: Record<string, unknown>): Promise<Record<string, unknown>> {
  const category = String(item.category || '').trim();
  await upsertCustomerPricing([item]);
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM customer_pricing WHERE category = $1 LIMIT 1', [category]);
  return row.rows[0]?.raw || item;
}

export async function listCustomerPricing(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

  const whereSql = search ? 'WHERE LOWER(category) LIKE $1' : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM customer_pricing ${whereSql} ORDER BY category ASC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM customer_pricing ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

// ========== Wave 5: Item Units ==========
export async function upsertItemUnits(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || '').trim();
      if (!id) continue;
      await pgQuery(
        `
        INSERT INTO item_units (
          id, item_id, rfid_code, status, invoice_id, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5, $6::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          item_id = EXCLUDED.item_id,
          rfid_code = EXCLUDED.rfid_code,
          status = EXCLUDED.status,
          invoice_id = EXCLUDED.invoice_id,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          String(entry.itemId || ''),
          entry.rfidCode ?? null,
          String(entry.status || 'active'),
          entry.invoiceId ?? null,
          JSON.stringify({ ...entry, id }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createItemUnit(item: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(item.id || '').trim();
  await upsertItemUnits([item]);
  const row = await pgQuery<{ raw: Record<string, unknown> }>('SELECT raw FROM item_units WHERE id = $1 LIMIT 1', [id]);
  return row.rows[0]?.raw || item;
}

export async function listItemUnits(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();
  const whereSql = search ? 'WHERE LOWER(item_id) LIKE $1 OR LOWER(status) LIKE $1' : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';
  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM item_units ${whereSql} ORDER BY created_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );
  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM item_units ${whereSql}`,
    search ? [`%${search}%`] : []
  );
  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));
  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

// ========== Wave 6: Internal Transfers ==========
export async function upsertInternalTransfers(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || '').trim();
      if (!id) continue;
      await pgQuery(
        `
        INSERT INTO internal_transfers (
          id, transfer_number, material_id, quantity, from_warehouse_id, from_shelf_id, 
          to_warehouse_id, to_shelf_id, transfer_date, created_by, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          transfer_number = EXCLUDED.transfer_number,
          material_id = EXCLUDED.material_id,
          quantity = EXCLUDED.quantity,
          transfer_date = EXCLUDED.transfer_date,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          String(entry.transferNumber || ''),
          String(entry.materialId || ''),
          Number(entry.quantity || 0),
          String(entry.fromWarehouseId || ''),
          entry.fromShelfId ?? null,
          String(entry.toWarehouseId || ''),
          entry.toShelfId ?? null,
          entry.date ? new Date(entry.date as string) : new Date(),
          String(entry.createdBy || 'system'),
          JSON.stringify({ ...entry, id }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function listInternalTransfers(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();
  const whereSql = search ? 'WHERE LOWER(transfer_number) LIKE $1 OR LOWER(material_id) LIKE $1' : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';
  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM internal_transfers ${whereSql} ORDER BY transfer_date DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );
  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM internal_transfers ${whereSql}`,
    search ? [`%${search}%`] : []
  );
  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));
  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

// ========== Wave 7: Inventory Movements ==========
export async function upsertInventoryMovements(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || '').trim();
      if (!id) continue;
      await pgQuery(
        `
        INSERT INTO inventory_movements (
          id, material_id, warehouse_id, shelf_id, quantity_in, quantity_out, 
          reference_type, reference_id, shipment_id, invoice_id, movement_date, raw
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12::jsonb
        )
        ON CONFLICT (id)
        DO UPDATE SET
          quantity_in = EXCLUDED.quantity_in,
          quantity_out = EXCLUDED.quantity_out,
          raw = EXCLUDED.raw
        `,
        [
          id,
          String(entry.materialId || ''),
          String(entry.warehouseId || ''),
          entry.shelfId ?? null,
          Number(entry.quantityIn || 0),
          Number(entry.quantityOut || 0),
          String(entry.referenceType || 'other'),
          entry.referenceId ?? null,
          entry.shipmentId ?? null,
          entry.invoiceId ?? null,
          entry.date ? new Date(entry.date as string) : new Date(),
          JSON.stringify({ ...entry, id }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function listInventoryMovements(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();
  const whereSql = search ? 'WHERE LOWER(material_id) LIKE $1 OR LOWER(warehouse_id) LIKE $1' : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';
  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM inventory_movements ${whereSql} ORDER BY movement_date DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );
  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM inventory_movements ${whereSql}`,
    search ? [`%${search}%`] : []
  );
  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));
  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

// ========== Wave 8: Customer Categories ==========
export async function upsertCustomerCategories(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || '').trim();
      if (!id) continue;
      const categoryNumber = String(entry.categoryNumber || entry.category_number || '').trim();
      const discountPercent = Number(entry.discountPercent ?? entry.discount_percent ?? 0);
      await pgQuery(
        `
        INSERT INTO customer_categories (
          id, category_number, name, name_en, discount_percent, description, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          category_number = EXCLUDED.category_number,
          name = EXCLUDED.name,
          name_en = EXCLUDED.name_en,
          discount_percent = EXCLUDED.discount_percent,
          description = EXCLUDED.description,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          categoryNumber || null,
          String(entry.name || ''),
          entry.nameEn ?? entry.name_en ?? null,
          Number.isFinite(discountPercent) ? discountPercent : 0,
          entry.description ?? null,
          JSON.stringify({ ...entry, id, categoryNumber: categoryNumber || undefined, discountPercent: Number.isFinite(discountPercent) ? discountPercent : 0 }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function createCustomerCategory(item: Record<string, unknown>): Promise<Record<string, unknown>> {
  const id = String(item.id || '').trim();
  const categoryNumber = String(item.categoryNumber || item.category_number || '').trim() || null;
  const discountPercent = Number(item.discountPercent ?? item.discount_percent ?? 0);
  const normalized: Record<string, unknown> = {
    ...item,
    id,
    categoryNumber: categoryNumber || undefined,
    name: String(item.name || '').trim(),
    nameEn: String(item.nameEn ?? item.name_en ?? '').trim() || undefined,
    discountPercent: Number.isFinite(discountPercent) ? discountPercent : 0,
    description: String(item.description || '').trim() || undefined,
  };

  const result = await pgQuery<{ raw: Record<string, unknown> }>(
    `
    INSERT INTO customer_categories (
      id, category_number, name, name_en, discount_percent, description, raw, updated_at
    ) VALUES (
      $1, $2, $3, $4, $5, $6, $7::jsonb, NOW()
    )
    RETURNING raw
    `,
    [
      id,
      categoryNumber,
      String(normalized.name || ''),
      normalized.nameEn ?? null,
      Number(normalized.discountPercent || 0),
      normalized.description ?? null,
      JSON.stringify(normalized),
    ]
  );
  return result.rows[0]?.raw || normalized;
}

export async function deleteCustomerCategoryById(id: string): Promise<boolean> {
  const result = await pgQuery('DELETE FROM customer_categories WHERE id = $1', [id]);
  return (result.rowCount || 0) > 0;
}

export async function listCustomerCategories(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();
  const whereSql = search ? "WHERE LOWER(name) LIKE $1 OR LOWER(COALESCE(name_en, '')) LIKE $1 OR LOWER(COALESCE(category_number, '')) LIKE $1" : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';
  const rows = await pgQuery<{
    raw: Record<string, unknown>;
    id: string;
    category_number: string | null;
    name: string;
    name_en: string | null;
    discount_percent: string | number | null;
    description: string | null;
  }>(
    `SELECT id, category_number, name, name_en, discount_percent, description, raw FROM customer_categories ${whereSql} ORDER BY name ASC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );
  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM customer_categories ${whereSql}`,
    search ? [`%${search}%`] : []
  );
  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));
  return {
    items: rows.rows.map((row) => ({
      ...(row.raw || {}),
      id: row.id,
      categoryNumber: row.category_number || undefined,
      name: row.name,
      nameEn: row.name_en || undefined,
      discountPercent: Number(row.discount_percent || 0),
      description: row.description || undefined,
    })),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

// ========== Wave 9: Parties ==========
export async function upsertParties(items: Array<Record<string, unknown>>): Promise<void> {
  if (!items.length) return;
  await pgQuery('BEGIN');
  try {
    for (const entry of items) {
      const id = String(entry.id || '').trim();
      if (!id) continue;
      await pgQuery(
        `
        INSERT INTO parties (
          id, party_type, entity_id, party_name, party_code, contact_info, address, notes, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          party_type = EXCLUDED.party_type,
          entity_id = EXCLUDED.entity_id,
          party_name = EXCLUDED.party_name,
          party_code = EXCLUDED.party_code,
          contact_info = EXCLUDED.contact_info,
          address = EXCLUDED.address,
          notes = EXCLUDED.notes,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          String(entry.party_type || 'other'),
          String(entry.entity_id || ''),
          String(entry.party_name || ''),
          entry.party_code ?? null,
          entry.contact_info ?? null,
          entry.address ?? null,
          entry.notes ?? null,
          JSON.stringify({ ...entry, id }),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function listParties(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();
  const whereSql = search ? 'WHERE LOWER(party_name) LIKE $1 OR LOWER(party_code) LIKE $1' : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';
  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM parties ${whereSql} ORDER BY party_name ASC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );
  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM parties ${whereSql}`,
    search ? [`%${search}%`] : []
  );
  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));
  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

// ========== Wave 10: Remaining Legacy Entities ==========
export async function upsertChartOfAccounts(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'chart_of_accounts' }, payload);
}

export async function listChartOfAccounts(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'chart_of_accounts' }, params);
}

export async function upsertChecksIssued(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'checks_issued' }, payload);
}

export async function listChecksIssued(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'checks_issued' }, params);
}

export async function upsertChecksReceived(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'checks_received' }, payload);
}

export async function listChecksReceived(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'checks_received' }, params);
}

export async function upsertCurrencies(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'currencies' }, payload);
}

export async function listCurrencies(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'currencies' }, params);
}

export async function upsertDailyExchangeRates(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'daily_exchange_rates' }, payload);
}

export async function listDailyExchangeRates(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'daily_exchange_rates' }, params);
}

export async function upsertEmployees(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'employees' }, payload);
}

export async function listEmployees(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'employees' }, params);
}

export async function getEmployeeById(id: string): Promise<Record<string, unknown> | null> {
  const normalizedId = String(id || '').trim();
  if (!normalizedId) return null;
  const result = await pgQuery<{ raw: Record<string, unknown> }>(
    'SELECT raw FROM employees WHERE id = $1 LIMIT 1',
    [normalizedId]
  );
  return result.rows[0]?.raw || null;
}

export async function upsertExchangeRateSources(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'exchange_rate_sources' }, payload);
}

export async function listExchangeRateSources(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'exchange_rate_sources' }, params);
}

export async function upsertInternalTransferDocs(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'internal_transfer_docs' }, payload);
}

export async function listInternalTransferDocs(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'internal_transfer_docs' }, params);
}

export async function upsertJournalEntries(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'journal_entries' }, payload);
}

export async function listJournalEntries(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'journal_entries' }, params);
}

export async function getJournalEntryById(id: string): Promise<unknown | null> {
  const normalizedId = String(id || '').trim();
  if (!normalizedId) return null;
  const result = await pgQuery<{ raw: Record<string, unknown> }>(
    'SELECT raw FROM journal_entries WHERE id = $1 LIMIT 1',
    [normalizedId]
  );
  if (!result.rows || result.rows.length === 0) return null;
  return result.rows[0]?.raw || null;
}

export async function deleteJournalEntryById(id: string): Promise<boolean> {
  const normalizedId = String(id || '').trim();
  if (!normalizedId) return false;
  const result = await pgQuery('DELETE FROM journal_entries WHERE id = $1', [normalizedId]);
  return Number(result.rowCount || 0) > 0;
}

export async function upsertPayments(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'payments' }, payload);
}

export async function listPayments(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'payments' }, params);
}

export async function upsertProductionReports(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'production_reports' }, payload);
}

export async function listProductionReports(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'production_reports' }, params);
}

export async function upsertPurchaseCreditNotes(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'purchase_credit_notes' }, payload);
}

export async function listPurchaseCreditNotes(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'purchase_credit_notes' }, params);
}

export async function upsertPurchaseReturns(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'purchase_returns' }, payload);
}

export async function listPurchaseReturns(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'purchase_returns' }, params);
}

export async function upsertShiftOverrides(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'shift_overrides' }, payload);
}

export async function listShiftOverrides(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'shift_overrides' }, params);
}

export async function upsertShipmentSequences(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'shipment_sequences' }, payload);
}

export async function listShipmentSequences(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'shipment_sequences' }, params);
}

export async function upsertShipments(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'shipments' }, payload);
}

export async function listShipments(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'shipments' }, params);
}

export async function upsertSupplierPaymentLocks(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'supplier_payment_locks' }, payload);
}

export async function listSupplierPaymentLocks(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'supplier_payment_locks' }, params);
}

export async function upsertSupplierPayments(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'supplier_payments' }, payload);
}

export async function listSupplierPayments(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'supplier_payments' }, params);
}

// Document locking functions
export interface DocumentLock {
  id: string;
  documentType: string;
  documentId: string;
  userId: string;
  userName: string;
  lockedAt: string;
  expiresAt: string;
}

/**
 * Acquire a lock on a document for editing
 * Returns { success: true, lock: {...} } or { success: false, lockedBy: {...} }
 */
export async function acquireDocumentLock(
  documentType: string,
  documentId: string,
  userId: string,
  userName: string
): Promise<{ success: boolean; lock?: DocumentLock; lockedBy?: Partial<DocumentLock> }> {
  try {
    // First, clean up expired locks
    await pgQuery(
      'DELETE FROM document_locks WHERE expires_at <= NOW()',
      []
    );

    // Check if document is already locked by someone else
    const existing = await pgQuery<DocumentLock>(
      `
      SELECT
        id,
        document_type AS "documentType",
        document_id AS "documentId",
        user_id AS "userId",
        user_name AS "userName",
        locked_at AS "lockedAt",
        expires_at AS "expiresAt"
      FROM document_locks
      WHERE document_type = $1 AND document_id = $2 AND expires_at > NOW()
      `,
      [documentType, documentId]
    );

    if (existing.rowCount && existing.rows[0]?.userId !== userId) {
      return {
        success: false,
        lockedBy: {
          userId: existing.rows[0].userId,
          userName: existing.rows[0].userName,
          lockedAt: existing.rows[0].lockedAt,
        },
      };
    }

    // Create or update lock
    const lockId = `${documentType}:${documentId}:${userId}`;
    const result = await pgQuery<DocumentLock>(
      `
      INSERT INTO document_locks (id, document_type, document_id, user_id, user_name, locked_at, expires_at)
      VALUES ($1, $2, $3, $4, $5, NOW(), NOW() + INTERVAL '30 minutes')
      ON CONFLICT (document_type, document_id)
      DO UPDATE SET user_id = $4, user_name = $5, locked_at = NOW(), expires_at = NOW() + INTERVAL '30 minutes',updated_at = NOW()
      RETURNING
        id,
        document_type AS "documentType",
        document_id AS "documentId",
        user_id AS "userId",
        user_name AS "userName",
        locked_at AS "lockedAt",
        expires_at AS "expiresAt"
      `,
      [lockId, documentType, documentId, userId, userName]
    );

    const lock = result.rows[0];
    return { success: true, lock };
  } catch (error) {
    console.error('Error acquiring document lock:', error);
    throw error;
  }
}

/**
 * Check if a document is locked and by whom
 */
export async function checkDocumentLock(
  documentType: string,
  documentId: string
): Promise<DocumentLock | null> {
  const result = await pgQuery<DocumentLock>(
    `
    SELECT
      id,
      document_type AS "documentType",
      document_id AS "documentId",
      user_id AS "userId",
      user_name AS "userName",
      locked_at AS "lockedAt",
      expires_at AS "expiresAt"
    FROM document_locks
    WHERE document_type = $1 AND document_id = $2 AND expires_at > NOW()
    LIMIT 1
    `,
    [documentType, documentId]
  );

  return result.rows[0] || null;
}

/**
 * Release a lock on a document
 */
export async function releaseDocumentLock(
  documentType: string,
  documentId: string,
  userId: string
): Promise<boolean> {
  const result = await pgQuery(
    'DELETE FROM document_locks WHERE document_type = $1 AND document_id = $2 AND user_id = $3',
    [documentType, documentId, userId]
  );

  return (result.rowCount || 0) > 0;
}

/**
 * Release all expired locks
 */
export async function cleanupExpiredLocks(): Promise<number> {
  const result = await pgQuery('DELETE FROM document_locks WHERE expires_at <= NOW()');
  return result.rowCount || 0;
}

export async function upsertTransferLocks(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'transfer_locks' }, payload);
}

export async function listTransferLocks(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'transfer_locks' }, params);
}

export async function upsertSequenceCounters(payload: unknown): Promise<void> {
  await upsertRawEntity({ tableName: 'sequence_counters' }, payload);
}

export async function listSequenceCounters(params: PgPagination = {}) {
  return listRawEntity({ tableName: 'sequence_counters' }, params);
}

export async function upsertInvoices(invoices: Array<Record<string, unknown>>): Promise<void> {
  if (!invoices.length) return;
  await pgQuery('BEGIN');
  try {
    for (const invoice of invoices) {
      const id = String(invoice.id || '');
      await pgQuery(
        `
        INSERT INTO invoices (
          id, invoice_number, customer_id, customer_name, invoice_date,
          currency_code, grand_total, amount_due, status, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5,
          $6, $7, $8, $9, $10::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          invoice_number = EXCLUDED.invoice_number,
          customer_id = EXCLUDED.customer_id,
          customer_name = EXCLUDED.customer_name,
          invoice_date = EXCLUDED.invoice_date,
          currency_code = EXCLUDED.currency_code,
          grand_total = EXCLUDED.grand_total,
          amount_due = EXCLUDED.amount_due,
          status = EXCLUDED.status,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          String(invoice.invoiceNumber || ''),
          invoice.customerId ?? null,
          invoice.customerName ?? null,
          invoice.date ?? null,
          invoice.currencyCode ?? null,
          Number(invoice.grandTotal || 0),
          Number(invoice.amountDue || 0),
          invoice.status ?? null,
          JSON.stringify(invoice),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function listInvoices(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

  const whereSql = search
    ? "WHERE LOWER(invoice_number) LIKE $1 OR LOWER(COALESCE(customer_name, '')) LIKE $1 OR CAST(grand_total AS TEXT) LIKE $1 OR CAST(amount_due AS TEXT) LIKE $1"
    : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM invoices ${whereSql} ORDER BY invoice_date DESC NULLS LAST, updated_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM invoices ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function upsertPurchaseOrders(orders: Array<Record<string, unknown>>): Promise<void> {
  if (!orders.length) return;
  await pgQuery('BEGIN');
  try {
    for (const order of orders) {
      const id = String(order.id || '');
      await pgQuery(
        `
        INSERT INTO purchase_orders (
          id, order_number, supplier_id, supplier_name, order_date,
          currency_code, base_currency_code, grand_total, status, posting_status, raw, updated_at
        ) VALUES (
          $1, $2, $3, $4, $5,
          $6, $7, $8, $9, $10, $11::jsonb, NOW()
        )
        ON CONFLICT (id)
        DO UPDATE SET
          order_number = EXCLUDED.order_number,
          supplier_id = EXCLUDED.supplier_id,
          supplier_name = EXCLUDED.supplier_name,
          order_date = EXCLUDED.order_date,
          currency_code = EXCLUDED.currency_code,
          base_currency_code = EXCLUDED.base_currency_code,
          grand_total = EXCLUDED.grand_total,
          status = EXCLUDED.status,
          posting_status = EXCLUDED.posting_status,
          raw = EXCLUDED.raw,
          updated_at = NOW()
        `,
        [
          id,
          String(order.orderNumber || ''),
          order.supplierId ?? null,
          order.supplierName ?? null,
          order.date ?? null,
          order.currencyCode ?? null,
          order.baseCurrencyCode ?? null,
          Number(order.grandTotal || 0),
          order.status ?? null,
          order.postingStatus ?? null,
          JSON.stringify(order),
        ]
      );
    }
    await pgQuery('COMMIT');
  } catch (error) {
    await pgQuery('ROLLBACK');
    throw error;
  }
}

export async function listPurchaseOrders(params: PgPagination = {}) {
  const { page, pageSize, offset } = getPaging(params);
  const search = String(params.search || '').trim().toLowerCase();

  const whereSql = search
    ? "WHERE LOWER(order_number) LIKE $1 OR LOWER(COALESCE(supplier_name, '')) LIKE $1 OR LOWER(COALESCE(currency_code, '')) LIKE $1"
    : '';
  const values = search ? [`%${search}%`, pageSize, offset] : [pageSize, offset];
  const limitIndex = search ? '$2' : '$1';
  const offsetIndex = search ? '$3' : '$2';

  const rows = await pgQuery<{ raw: Record<string, unknown> }>(
    `SELECT raw FROM purchase_orders ${whereSql} ORDER BY order_date DESC NULLS LAST, updated_at DESC LIMIT ${limitIndex}::int OFFSET ${offsetIndex}::int`,
    values
  );

  const count = await pgQuery<{ total: string }>(
    `SELECT COUNT(*)::text AS total FROM purchase_orders ${whereSql}`,
    search ? [`%${search}%`] : []
  );

  const totalItems = Number(count.rows[0]?.total || 0);
  const totalPages = Math.max(1, Math.ceil(totalItems / pageSize));

  return {
    items: rows.rows.map((row) => row.raw),
    page,
    pageSize,
    totalItems,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

export async function upsertJsonStore(filename: string, payload: unknown): Promise<void> {
  await pgQuery(
    `
    INSERT INTO data_json_store(filename, payload, updated_at)
    VALUES($1, $2::jsonb, NOW())
    ON CONFLICT (filename)
    DO UPDATE SET payload = EXCLUDED.payload, updated_at = NOW()
    `,
    [filename, JSON.stringify(payload ?? null)]
  );
}

export async function getJsonStore<T = unknown>(filename: string): Promise<T | null> {
  const result = await pgQuery<{ payload: T }>('SELECT payload FROM data_json_store WHERE filename = $1 LIMIT 1', [filename]);
  if (!result.rowCount) return null;
  return result.rows[0].payload;
}
