<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');

$ts = date('c');
$out = [
  'ts' => $ts,
  'generator' => 'db-stats-live.php v1.0 - doctrine 60 UX dynamic',
  'error' => null,
];

$cmd = 'PGPASSWORD=admin123 psql -h 10.1.0.3 -U admin -d adx_system -t -A -F "|" -c "
SELECT schemaname || \'.\' || tablename as tbl,
  (xpath(\'/row/cnt/text()\', xml_count))[1]::text::int as rows
FROM pg_tables,
  LATERAL (SELECT query_to_xml(format(\'select count(*) as cnt from %I.%I\', schemaname, tablename), false, true, \'\') xml_count) x
WHERE schemaname IN (\'admin\', \'ethica\', \'weval\')
ORDER BY rows DESC
" 2>&1';

$raw = @shell_exec($cmd);
$lines = array_filter(array_map('trim', explode("\n", $raw)));

$tables = [];
$empty_count = 0;
$total_rows = 0;

foreach ($lines as $line) {
  if (strpos($line, '|') === false) continue;
  list($tbl, $rows) = array_map('trim', explode('|', $line, 2));
  $rows = (int)$rows;
  $tables[] = ['table' => $tbl, 'rows' => $rows];
  if ($rows === 0) $empty_count++;
  $total_rows += $rows;
}

// Top 20 biggest
$top = array_slice($tables, 0, 20);

// Group by schema
$schemas = [];
foreach ($tables as $t) {
  list($s, $n) = explode('.', $t['table'], 2);
  if (!isset($schemas[$s])) $schemas[$s] = ['tables' => 0, 'rows' => 0, 'empty' => 0];
  $schemas[$s]['tables']++;
  $schemas[$s]['rows'] += $t['rows'];
  if ($t['rows'] === 0) $schemas[$s]['empty']++;
}

// Critical business tables status
$critical = [
  'admin.leads' => 'B2B lead pool',
  'admin.send_contacts' => 'Email send candidates',
  'admin.send_data' => 'Legacy CRM contacts (ex crm_contacts)',
  'admin.crm_contacts' => 'CRM contacts VUE',
  'admin.graph_send_log' => 'Send history',
  'admin.inbox_accounts' => 'Graph accounts',
  'admin.office_accounts' => 'O365 accounts',
  'ethica.medecins_validated' => 'Ethica HCPs source',
  'ethica.medecins_real' => 'Ethica HCPs active VUE',
  'admin.pipeline_deals' => 'Twenty CRM deals',
  'admin.pipeline_contacts' => 'Twenty CRM contacts',
  'admin.pipeline_companies' => 'Twenty CRM companies',
];
$critical_status = [];
foreach ($critical as $t => $desc) {
  $found = null;
  foreach ($tables as $tab) {
    if ($tab['table'] === $t) { $found = $tab['rows']; break; }
  }
  $critical_status[$t] = ['rows' => $found, 'desc' => $desc];
}

$out['summary'] = [
  'total_tables' => count($tables),
  'empty_tables' => $empty_count,
  'active_tables' => count($tables) - $empty_count,
  'total_rows' => $total_rows,
  'schemas' => $schemas,
];
$out['top_tables'] = $top;
$out['critical_status'] = $critical_status;

echo json_encode($out, JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);