import { getPgPool, pgQuery } from '../lib/postgres/client';

type JournalLine = {
  accountCode?: string;
  accountName?: string;
  debit?: number | string;
  credit?: number | string;
  description?: string;
};

type JournalRaw = {
  id?: string;
  referenceType?: string;
  referenceId?: string;
  invoiceId?: string;
  description?: string;
  lines?: JournalLine[];
  status?: string;
  createdBy?: string;
  createdAt?: string;
};

type AuditFinding = {
  severity: 'HIGH' | 'MEDIUM' | 'LOW';
  type: 'BALANCE' | 'REFERENCE' | 'ACCOUNT';
  message: string;
};

type AuditedEntry = {
  id: string;
  referenceType: string;
  referenceId: string;
  description: string;
  totalDebit: number;
  totalCredit: number;
  findings: AuditFinding[];
};

function toNumber(value: unknown): number {
  const n = Number(value ?? 0);
  return Number.isFinite(n) ? n : 0;
}

function normalizeText(value: unknown): string {
  return String(value ?? '').trim();
}

function inferFlow(referenceType: string, description: string): 'sales' | 'pos' | 'unknown' {
  const rt = referenceType.toLowerCase();
  const desc = description.toLowerCase();

  if (rt.includes('pos') || desc.includes('pos') || desc.includes('نقطة البيع')) {
    return 'pos';
  }

  if (
    rt.includes('sales') ||
    rt.includes('invoice') ||
    rt.includes('payment') ||
    rt.includes('return') ||
    desc.includes('sales') ||
    desc.includes('فاتورة') ||
    desc.includes('مبيعات') ||
    desc.includes('return') ||
    desc.includes('مرتجع')
  ) {
    return 'sales';
  }

  return 'unknown';
}

function hasAnyAccount(lines: JournalLine[], accountCodes: string[]): boolean {
  const set = new Set(accountCodes);
  return lines.some((line) => set.has(normalizeText(line.accountCode)));
}

function hasRevenueCredit(lines: JournalLine[]): boolean {
  return lines.some((line) => {
    const code = normalizeText(line.accountCode);
    const credit = toNumber(line.credit);
    return /^41\d{2}/.test(code) && credit > 0;
  });
}

function findSalesPosEntries(rows: Array<{ id: string; raw: JournalRaw }>): AuditedEntry[] {
  const audited: AuditedEntry[] = [];

  for (const row of rows) {
    const raw = row.raw || {};
    const id = normalizeText(raw.id || row.id);
    const referenceType = normalizeText(raw.referenceType);
    const referenceId = normalizeText(raw.referenceId || raw.invoiceId);
    const description = normalizeText(raw.description);
    const lines = Array.isArray(raw.lines) ? raw.lines : [];

    const flow = inferFlow(referenceType, description);
    if (flow === 'unknown') continue;

    const totalDebit = Number(lines.reduce((sum, line) => sum + toNumber(line.debit), 0).toFixed(3));
    const totalCredit = Number(lines.reduce((sum, line) => sum + toNumber(line.credit), 0).toFixed(3));

    const findings: AuditFinding[] = [];

    if (Math.abs(totalDebit - totalCredit) > 0.01) {
      findings.push({
        severity: 'HIGH',
        type: 'BALANCE',
        message: `Entry not balanced: debit=${totalDebit}, credit=${totalCredit}`,
      });
    }

    if (!referenceType || !referenceId) {
      findings.push({
        severity: 'HIGH',
        type: 'REFERENCE',
        message: 'Missing referenceType or referenceId',
      });
    }

    const rt = referenceType.toLowerCase();

    if (rt === 'sales_invoice') {
      const hasDebitLeg = lines.some((line) => toNumber(line.debit) > 0);
      const hasRevenueCreditLeg = hasRevenueCredit(lines);

      if (!hasDebitLeg) {
        findings.push({
          severity: 'MEDIUM',
          type: 'ACCOUNT',
          message: 'Sales invoice entry has no debit leg',
        });
      }

      if (!hasRevenueCreditLeg) {
        findings.push({
          severity: 'HIGH',
          type: 'ACCOUNT',
          message: 'Sales invoice does not include revenue credit (41xx)',
        });
      }
    }

    if (rt === 'sales_return') {
      const hasReturnsAccount = hasAnyAccount(lines, ['4130']);
      if (!hasReturnsAccount) {
        findings.push({
          severity: 'HIGH',
          type: 'ACCOUNT',
          message: 'Sales return entry missing returns account 4130',
        });
      }
    }

    if (rt.includes('pos') || description.includes('POS') || description.includes('نقطة البيع')) {
      const hasCashOrBank = hasAnyAccount(lines, ['1110', '1120']);
      if (!hasCashOrBank) {
        findings.push({
          severity: 'MEDIUM',
          type: 'ACCOUNT',
          message: 'POS-related entry missing cash/bank leg (1110/1120)',
        });
      }
    }

    audited.push({
      id,
      referenceType,
      referenceId,
      description,
      totalDebit,
      totalCredit,
      findings,
    });
  }

  return audited;
}

function printSummary(entries: AuditedEntry[]): void {
  const totals = {
    entries: entries.length,
    clean: 0,
    withFindings: 0,
    high: 0,
    medium: 0,
    low: 0,
  };

  for (const entry of entries) {
    if (entry.findings.length === 0) {
      totals.clean += 1;
    } else {
      totals.withFindings += 1;
      for (const finding of entry.findings) {
        if (finding.severity === 'HIGH') totals.high += 1;
        if (finding.severity === 'MEDIUM') totals.medium += 1;
        if (finding.severity === 'LOW') totals.low += 1;
      }
    }
  }

  console.log('=== Sales/POS Accounting Audit Summary ===');
  console.log(`Entries scanned: ${totals.entries}`);
  console.log(`Clean entries: ${totals.clean}`);
  console.log(`Entries with findings: ${totals.withFindings}`);
  console.log(`Findings by severity: HIGH=${totals.high}, MEDIUM=${totals.medium}, LOW=${totals.low}`);

  const byReferenceType = new Map<string, number>();
  for (const entry of entries) {
    const key = entry.referenceType || '(empty)';
    byReferenceType.set(key, (byReferenceType.get(key) || 0) + 1);
  }

  console.log('\nEntries by referenceType:');
  for (const [key, count] of [...byReferenceType.entries()].sort((a, b) => b[1] - a[1])) {
    console.log(`- ${key}: ${count}`);
  }

  const duplicates = new Map<string, AuditedEntry[]>();
  for (const entry of entries) {
    const key = `${entry.referenceType}::${entry.referenceId}`;
    const bucket = duplicates.get(key) || [];
    bucket.push(entry);
    duplicates.set(key, bucket);
  }

  const duplicateGroups = [...duplicates.entries()].filter(([, list]) => list.length > 1);
  console.log(`\nDuplicate reference groups: ${duplicateGroups.length}`);
  for (const [key, list] of duplicateGroups.slice(0, 20)) {
    console.log(`- ${key} -> ${list.length} entries`);
  }

  console.log('\nDetailed findings (up to 60 entries):');
  const withFindings = entries.filter((entry) => entry.findings.length > 0).slice(0, 60);
  if (withFindings.length === 0) {
    console.log('- No findings detected with current rules.');
    return;
  }

  for (const entry of withFindings) {
    console.log(`\n[${entry.id}] refType=${entry.referenceType} refId=${entry.referenceId}`);
    console.log(`description: ${entry.description}`);
    console.log(`debit=${entry.totalDebit} credit=${entry.totalCredit}`);
    for (const finding of entry.findings) {
      console.log(`  - (${finding.severity}/${finding.type}) ${finding.message}`);
    }
  }
}

function isPosInvoiceNumber(invoiceNumber: string): boolean {
  const normalized = normalizeText(invoiceNumber).toUpperCase();
  return normalized.startsWith('ST/') || normalized.startsWith('POS/');
}

async function auditInvoiceJournalCoverage(entries: AuditedEntry[]): Promise<void> {
  const invoiceRows = await pgQuery<{ raw: any }>(`
    SELECT raw
    FROM invoices
    ORDER BY created_at DESC
    LIMIT 4000
  `);

  const invoices = (invoiceRows.rows || [])
    .map((row) => row.raw || {})
    .filter((raw) => {
      const status = normalizeText(raw.status || 'active').toLowerCase();
      const invoiceId = normalizeText(raw.id);
      const invoiceNumber = normalizeText(raw.invoiceNumber);
      return Boolean(invoiceId && invoiceNumber) && status !== 'cancelled';
    });

  const salesInvoices = invoices.filter((inv) => {
    const type = normalizeText(inv.type || 'sale').toLowerCase();
    return type === 'sale' || type === 'return';
  });

  const postedSalesInvoices = salesInvoices.filter((inv) => {
    const postingStatus = normalizeText(inv.postingStatus).toLowerCase();
    return postingStatus === 'posted';
  });

  const posInvoices = postedSalesInvoices.filter((inv) => isPosInvoiceNumber(normalizeText(inv.invoiceNumber)));

  const byRef = new Set(entries.map((entry) => `${entry.referenceType}::${entry.referenceId}`));

  const missingSales = postedSalesInvoices.filter((inv) => !byRef.has(`sales_invoice::${normalizeText(inv.id)}`));
  const missingPos = posInvoices.filter((inv) => !byRef.has(`sales_invoice::${normalizeText(inv.id)}`));

  console.log('\n=== Document Coverage Check ===');
  console.log(`Active sales invoices found: ${salesInvoices.length}`);
  console.log(`Posted sales invoices checked: ${postedSalesInvoices.length}`);
  console.log(`POS invoices checked (ST/POS prefix): ${posInvoices.length}`);
  console.log(`Missing sales invoice journals: ${missingSales.length}`);
  console.log(`Missing POS invoice journals: ${missingPos.length}`);

  if (missingSales.length > 0) {
    console.log('\nMissing sales invoice journal samples (up to 20):');
    for (const inv of missingSales.slice(0, 20)) {
      console.log(`- id=${normalizeText(inv.id)} number=${normalizeText(inv.invoiceNumber)} type=${normalizeText(inv.type)}`);
    }
  }

  if (missingPos.length > 0) {
    console.log('\nMissing POS invoice journal samples (up to 20):');
    for (const inv of missingPos.slice(0, 20)) {
      console.log(`- id=${normalizeText(inv.id)} number=${normalizeText(inv.invoiceNumber)} type=${normalizeText(inv.type)}`);
    }
  }
}

async function main() {
  const rows = await pgQuery<{ id: string; raw: JournalRaw }>(`
    SELECT id, raw
    FROM journal_entries
    ORDER BY created_at DESC
    LIMIT 2000
  `);

  const entries = findSalesPosEntries(rows.rows || []);
  printSummary(entries);
  await auditInvoiceJournalCoverage(entries);
}

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