import { pgQuery, getPgPool } from '../lib/postgres/client';
import { pgUpdateMaterial } from '../lib/postgres/data-access';

type MaterialRow = {
  id: string;
  raw: Record<string, unknown>;
};

function toTrimmedString(value: unknown): string {
  return String(value || '').trim();
}

function toFiniteNumber(value: unknown): number | undefined {
  if (value === '' || value === undefined || value === null) return undefined;
  const numeric = Number(value);
  return Number.isFinite(numeric) ? numeric : undefined;
}

function normalizeMaterialUnitFields(raw: Record<string, unknown>): Record<string, unknown> {
  const normalized = { ...raw };

  const primaryUnit = toTrimmedString(normalized.primaryUnit);
  if (primaryUnit) {
    normalized.primaryUnit = primaryUnit;
    const primaryQuantity = toFiniteNumber(normalized.primaryUnitQuantity);
    normalized.primaryUnitQuantity = typeof primaryQuantity === 'number' && primaryQuantity > 0 ? primaryQuantity : 1;
  } else {
    delete normalized.primaryUnit;
    delete normalized.primaryUnitQuantity;
  }

  const normalizedSecondaryUnits = (Array.isArray(normalized.secondaryUnits) ? normalized.secondaryUnits : [])
    .map((entry: any) => {
      const unit = toTrimmedString(entry?.unit);
      const barcode = toTrimmedString(entry?.barcode);
      const quantity = toFiniteNumber(entry?.quantity);
      const price = toFiniteNumber(entry?.price);
      return {
        unit,
        barcode,
        quantity,
        price,
      };
    })
    .filter((entry) => entry.unit || entry.barcode || typeof entry.quantity === 'number' || typeof entry.price === 'number');

  const legacySecondaryUnit = toTrimmedString(normalized.secondaryUnit);
  const legacySecondaryBarcode = toTrimmedString(normalized.secondaryBarcode);
  const legacySecondaryQuantity = toFiniteNumber(normalized.secondaryUnitQuantity);
  const legacySecondaryPrice = toFiniteNumber(normalized.secondaryUnitPrice);

  if (
    normalizedSecondaryUnits.length === 0
    && (legacySecondaryUnit || legacySecondaryBarcode || typeof legacySecondaryQuantity === 'number' || typeof legacySecondaryPrice === 'number')
  ) {
    normalizedSecondaryUnits.push({
      unit: legacySecondaryUnit,
      barcode: legacySecondaryBarcode,
      quantity: legacySecondaryQuantity,
      price: legacySecondaryPrice,
    });
  }

  if (normalizedSecondaryUnits.length > 0) {
    normalized.secondaryUnits = normalizedSecondaryUnits;
    normalized.secondaryUnit = normalizedSecondaryUnits[0].unit || undefined;
    normalized.secondaryBarcode = normalizedSecondaryUnits[0].barcode || undefined;
    normalized.secondaryUnitQuantity = normalizedSecondaryUnits[0].quantity;
    normalized.secondaryUnitPrice = normalizedSecondaryUnits[0].price;
  } else {
    delete normalized.secondaryUnits;
    delete normalized.secondaryUnit;
    delete normalized.secondaryBarcode;
    delete normalized.secondaryUnitQuantity;
    delete normalized.secondaryUnitPrice;
  }

  return normalized;
}

function stableSerialize(value: unknown): string {
  return JSON.stringify(value);
}

async function main() {
  const rows = await pgQuery<MaterialRow>('SELECT id, raw FROM materials');
  let updated = 0;
  let unchanged = 0;
  let failed = 0;

  for (const row of rows.rows) {
    const id = String(row.id || '').trim();
    const current = (row.raw || {}) as Record<string, unknown>;
    const next = normalizeMaterialUnitFields(current);

    if (stableSerialize(current) === stableSerialize(next)) {
      unchanged += 1;
      continue;
    }

    try {
      await pgUpdateMaterial(id, next);
      updated += 1;
    } catch (error) {
      failed += 1;
      console.error(`Failed to normalize material ${id}:`, error);
    }
  }

  console.log('Material unit normalization completed.');
  console.log(`Scanned: ${rows.rowCount || rows.rows.length}`);
  console.log(`Updated: ${updated}`);
  console.log(`Unchanged: ${unchanged}`);
  console.log(`Failed: ${failed}`);

  if (failed > 0) {
    process.exitCode = 1;
  }
}

main()
  .catch((error) => {
    console.error('Normalization failed:', error);
    process.exitCode = 1;
  })
  .finally(async () => {
    await getPgPool().end();
  });
