// services/app/src/api/v1/routes/utils/schemaInspector.mjs
// Utilidades para inspeccionar columnas, claves y relaciones en PostgreSQL.

export async function loadColumns(client, table) {
  const sql = `
    SELECT
      c.column_name,
      c.data_type,
      c.is_nullable = 'YES' AS is_nullable,
      c.column_default,
      (SELECT EXISTS (
         SELECT 1 FROM pg_attribute a
         JOIN pg_class t ON t.oid = a.attrelid
         JOIN pg_index i ON i.indrelid = t.oid AND a.attnum = ANY(i.indkey)
         WHERE t.relname = $1 AND i.indisprimary AND a.attname = c.column_name
      )) AS is_primary,
      (SELECT a.attgenerated = 's' OR a.attidentity IN ('a','d')
       FROM pg_attribute a
       JOIN pg_class t ON t.oid = a.attrelid
       WHERE t.relname = $1 AND a.attname = c.column_name
      ) AS is_identity
    FROM information_schema.columns c
    WHERE c.table_schema='public' AND c.table_name=$1
    ORDER BY c.ordinal_position
  `;
  const { rows } = await client.query(sql, [table]);
  return rows;
}

export async function loadForeignKeys(client, table) {
  const sql = `
    SELECT
      kcu.column_name,
      ccu.table_name AS foreign_table,
      ccu.column_name AS foreign_column
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
      ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage ccu
      ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
    WHERE tc.table_schema='public' AND tc.table_name=$1 AND tc.constraint_type='FOREIGN KEY'
  `;
  const { rows } = await client.query(sql, [table]);
  const map = {};
  for (const r of rows)
    map[r.column_name] = { foreign_table: r.foreign_table, foreign_column: r.foreign_column };
  return map;
}

export async function loadPrimaryKey(client, table) {
  const sql = `
    SELECT a.attname AS column_name
    FROM pg_index i
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
    JOIN pg_class t ON t.oid = i.indrelid
    WHERE t.relname = $1 AND i.indisprimary
  `;
  const { rows } = await client.query(sql, [table]);
  return rows.map(r => r.column_name);
}

export async function pickLabelColumn(client, refTable) {
  const preferred = ['nombre','raz_social','apodo','documento','correo','telefono'];
  const { rows } = await client.query(
    `SELECT column_name, data_type
     FROM information_schema.columns
     WHERE table_schema='public' AND table_name=$1
     ORDER BY ordinal_position`, [refTable]
  );
  for (const cand of preferred)
    if (rows.find(r => r.column_name === cand)) return cand;

  const textish = rows.find(r => /text|character varying|varchar/i.test(r.data_type));
  if (textish) return textish.column_name;
  return rows[0]?.column_name || 'id';
}