-- ============================================================================
-- AUDITORÍA DE ESQUEMA PARA BACKUP MULTIEMPRESA (por domain_id)
-- Correr EN EL SERVIDOR donde la BD responde:
--   mysql -h127.0.0.1 -u<user> -p <database> < database/tenant_backup_audit.sql
-- Objetivo: generar el manifiesto autoritativo de tablas.
-- ============================================================================

-- 1) Toda tabla: ¿tiene columna domain_id? + filas aprox.
--    => Grupo 1 (tenant-owned) = has_domain_id = YES
SELECT
    t.TABLE_NAME,
    CASE WHEN c.COLUMN_NAME IS NULL THEN 'no' ELSE 'YES' END AS has_domain_id,
    t.TABLE_ROWS AS rows_aprox
FROM information_schema.TABLES t
LEFT JOIN information_schema.COLUMNS c
       ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
      AND c.TABLE_NAME   = t.TABLE_NAME
      AND c.COLUMN_NAME  = 'domain_id'
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_TYPE   = 'BASE TABLE'
ORDER BY has_domain_id DESC, t.TABLE_NAME;

-- 2) Mapa de FKs: hijo -> padre.
--    => Para tablas SIN domain_id, sirve para llegar al padre tenant
--       y para calcular el ORDEN de inserción al restaurar.
SELECT
    kcu.TABLE_NAME            AS tabla_hijo,
    kcu.COLUMN_NAME           AS columna_fk,
    kcu.REFERENCED_TABLE_NAME AS tabla_padre,
    kcu.REFERENCED_COLUMN_NAME AS columna_padre
FROM information_schema.KEY_COLUMN_USAGE kcu
WHERE kcu.TABLE_SCHEMA = DATABASE()
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY kcu.TABLE_NAME, kcu.COLUMN_NAME;

-- 3) Tablas SIN domain_id y SIN FK saliente:
--    => candidatas a CATÁLOGO GLOBAL (ubigeo, etc.) o INFRA (migrations,
--       password_resets, jobs, cache). Revisar una por una a mano.
SELECT t.TABLE_NAME
FROM information_schema.TABLES t
LEFT JOIN information_schema.COLUMNS c
       ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
      AND c.TABLE_NAME   = t.TABLE_NAME
      AND c.COLUMN_NAME  = 'domain_id'
LEFT JOIN information_schema.KEY_COLUMN_USAGE k
       ON k.TABLE_SCHEMA = t.TABLE_SCHEMA
      AND k.TABLE_NAME   = t.TABLE_NAME
      AND k.REFERENCED_TABLE_NAME IS NOT NULL
WHERE t.TABLE_SCHEMA = DATABASE()
  AND t.TABLE_TYPE   = 'BASE TABLE'
  AND c.COLUMN_NAME IS NULL
  AND k.CONSTRAINT_NAME IS NULL
ORDER BY t.TABLE_NAME;

-- 4) Columnas tipo blob/text grandes (ej. contenido_base64):
--    => decidir si van en backup diario o solo semanal/mensual.
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND (DATA_TYPE IN ('longtext','mediumtext','longblob','mediumblob','blob','text')
       OR COLUMN_NAME LIKE '%base64%')
ORDER BY TABLE_NAME, COLUMN_NAME;