-- ============================================
-- rioCata - Tests de Estructura de BD
-- ============================================
-- Tests que verifican la existencia y correcta
-- configuración de la estructura de la base de datos
-- ============================================

\echo '=========================================='
\echo 'Tests de Estructura de Base de Datos'
\echo '=========================================='
\echo ''

-- ============================================
-- TEST: Verificar existencia de tablas
-- ============================================
\echo '[STRUCTURE 1] Verificando existencia de tablas...'

DO $$
DECLARE
  tabla_count int;
BEGIN
  SELECT COUNT(*) INTO tabla_count
  FROM information_schema.tables
  WHERE table_schema = 'public'
    AND table_name IN ('sesion', 'sesion_participante', 'muestra', 'evaluacion');

  IF tabla_count = 4 THEN
    RAISE NOTICE '  ✓ Todas las tablas principales existen (4/4)';
  ELSE
    RAISE EXCEPTION '  ✗ Faltan tablas. Encontradas: %/4', tabla_count;
  END IF;

  -- Verificar tabla auth.users
  SELECT COUNT(*) INTO tabla_count
  FROM information_schema.tables
  WHERE table_schema = 'auth' AND table_name = 'users';

  IF tabla_count = 1 THEN
    RAISE NOTICE '  ✓ Tabla auth.users existe';
  ELSE
    RAISE EXCEPTION '  ✗ Tabla auth.users no existe';
  END IF;

  RAISE NOTICE '✓ Todas las tablas existen correctamente';
END $$;

\echo ''

-- ============================================
-- TEST: Verificar tipo ENUM defecto_tipo
-- ============================================
\echo '[STRUCTURE 2] Verificando tipo ENUM defecto_tipo...'

DO $$
DECLARE
  enum_exists boolean;
  enum_values text[];
  expected_values text[] := ARRAY['Mohoso', 'Fenólico', 'Papa'];
BEGIN
  SELECT EXISTS (
    SELECT 1 FROM pg_type WHERE typname = 'defecto_tipo'
  ) INTO enum_exists;

  IF NOT enum_exists THEN
    RAISE EXCEPTION '  ✗ Tipo ENUM defecto_tipo no existe';
  END IF;

  -- Verificar valores del ENUM
  SELECT array_agg(enumlabel ORDER BY enumsortorder)
  INTO enum_values
  FROM pg_enum
  WHERE enumtypid = 'defecto_tipo'::regtype;

  IF enum_values = expected_values THEN
    RAISE NOTICE '  ✓ Tipo ENUM defecto_tipo existe con valores correctos: %',
      array_to_string(enum_values, ', ');
  ELSE
    RAISE EXCEPTION '  ✗ Valores ENUM incorrectos. Encontrados: %, Esperados: %',
      enum_values, expected_values;
  END IF;

  RAISE NOTICE '✓ Tipo ENUM defecto_tipo correcto';
END $$;

\echo ''

-- ============================================
-- TEST: Verificar triggers
-- ============================================
\echo '[STRUCTURE 3] Verificando triggers...'

DO $$
DECLARE
  trigger_count int;
  trigger_names text[];
BEGIN
  SELECT COUNT(*), array_agg(trigger_name ORDER BY trigger_name)
  INTO trigger_count, trigger_names
  FROM information_schema.triggers
  WHERE event_object_table = 'evaluacion'
    AND trigger_name IN ('trg_eval_updated_at', 'trg_eval_score_bi');

  IF trigger_count >= 2 THEN
    RAISE NOTICE '  ✓ Triggers principales existen (% encontrados): %',
      trigger_count, array_to_string(trigger_names, ', ');
  ELSE
    RAISE EXCEPTION '  ✗ Faltan triggers. Encontrados: %', trigger_count;
  END IF;

  RAISE NOTICE '✓ Todos los triggers existen';
END $$;

\echo ''

-- ============================================
-- TEST: Verificar índices
-- ============================================
\echo '[STRUCTURE 4] Verificando índices...'

DO $$
DECLARE
  index_count int;
  expected_min int := 15;
BEGIN
  SELECT COUNT(*) INTO index_count
  FROM pg_indexes
  WHERE schemaname = 'public'
    AND indexname LIKE 'idx_%';

  IF index_count >= expected_min THEN
    RAISE NOTICE '  ✓ Índices creados correctamente (% encontrados, esperado: >= %)',
      index_count, expected_min;
  ELSE
    RAISE WARNING '  ⚠ Se esperaban al menos % índices, se encontraron: %',
      expected_min, index_count;
  END IF;

  RAISE NOTICE '✓ Verificación de índices completada';
END $$;

\echo ''

-- ============================================
-- TEST: Verificar funciones auxiliares
-- ============================================
\echo '[STRUCTURE 5] Verificando funciones auxiliares...'

DO $$
DECLARE
  func_count int;
  func_names text[];
BEGIN
  SELECT COUNT(*), array_agg(proname ORDER BY proname)
  INTO func_count, func_names
  FROM pg_proc
  WHERE proname IN ('get_promedio_parametro_afectivo', 'get_top_muestras')
    AND pronamespace = 'public'::regnamespace;

  IF func_count = 2 THEN
    RAISE NOTICE '  ✓ Todas las funciones auxiliares existen (2/2): %',
      array_to_string(func_names, ', ');
  ELSE
    RAISE EXCEPTION '  ✗ Faltan funciones. Encontradas: %/2', func_count;
  END IF;

  RAISE NOTICE '✓ Funciones auxiliares existen';
END $$;

\echo ''

-- ============================================
-- TEST: Verificar claves foráneas
-- ============================================
\echo '[STRUCTURE 6] Verificando claves foráneas...'

DO $$
DECLARE
  fk_count int;
  expected_fks int := 5;
BEGIN
  SELECT COUNT(*) INTO fk_count
  FROM information_schema.table_constraints
  WHERE constraint_schema = 'public'
    AND constraint_type = 'FOREIGN KEY';

  IF fk_count >= expected_fks THEN
    RAISE NOTICE '  ✓ Claves foráneas configuradas (% encontradas)', fk_count;
  ELSE
    RAISE WARNING '  ⚠ Se esperaban al menos % FK, encontradas: %',
      expected_fks, fk_count;
  END IF;

  RAISE NOTICE '✓ Verificación de claves foráneas completada';
END $$;

\echo ''

-- ============================================
-- TEST: Verificar columnas de evaluacion
-- ============================================
\echo '[STRUCTURE 7] Verificando columnas de tabla evaluacion...'

DO $$
DECLARE
  required_columns text[] := ARRAY[
    'id', 'muestra_id', 'sesion_participante_id',
    'intensidades', 'fragancia_aroma_notas', 'sabor_notas',
    'tazas_no_uniformes', 'tazas_defectuosas',
    'sensacion_en_boca', 'gustos_predominantes',
    'defecto', 'otras_notas', 'puntaje_final',
    'created_at', 'updated_at'
  ];
  found_columns text[];
  missing_columns text[];
BEGIN
  SELECT array_agg(column_name)
  INTO found_columns
  FROM information_schema.columns
  WHERE table_schema = 'public'
    AND table_name = 'evaluacion'
    AND column_name = ANY(required_columns);

  -- Buscar columnas faltantes
  SELECT array_agg(col)
  INTO missing_columns
  FROM unnest(required_columns) AS col
  WHERE col NOT IN (SELECT unnest(found_columns));

  IF missing_columns IS NULL THEN
    RAISE NOTICE '  ✓ Todas las columnas requeridas existen (15/15)';
  ELSE
    RAISE EXCEPTION '  ✗ Columnas faltantes: %', array_to_string(missing_columns, ', ');
  END IF;

  RAISE NOTICE '✓ Columnas de evaluacion correctas';
END $$;

\echo ''

-- ============================================
-- TEST: Verificar tipos de datos de columnas críticas
-- ============================================
\echo '[STRUCTURE 8] Verificando tipos de datos...'

DO $$
DECLARE
  wrong_types text;
BEGIN
  SELECT string_agg(
    column_name || ' (' || data_type || ')',
    ', '
  )
  INTO wrong_types
  FROM information_schema.columns
  WHERE table_schema = 'public'
    AND table_name = 'evaluacion'
    AND (
      (column_name = 'intensidades' AND data_type != 'jsonb') OR
      (column_name = 'fragancia_aroma_notas' AND data_type != 'jsonb') OR
      (column_name = 'sabor_notas' AND data_type != 'jsonb') OR
      (column_name = 'tazas_no_uniformes' AND data_type != 'ARRAY') OR
      (column_name = 'tazas_defectuosas' AND data_type != 'ARRAY') OR
      (column_name = 'puntaje_final' AND data_type NOT IN ('integer', 'bigint'))
    );

  IF wrong_types IS NOT NULL THEN
    RAISE EXCEPTION '  ✗ Tipos de datos incorrectos: %', wrong_types;
  END IF;

  RAISE NOTICE '  ✓ Tipos de datos correctos para campos críticos';
  RAISE NOTICE '✓ Tipos de datos verificados';
END $$;

\echo ''

\echo '=========================================='
\echo 'Tests de Estructura completados'
\echo '=========================================='