#!/bin/bash
# ==============================================================================
# Script: extract-ddl-schema.sh
# Description: Extrae estructura completa de todos los archivos DDL
# ==============================================================================

BASE_DIR="C:/Empresas/ISEM/workspace-v2/projects/trading-platform"
DDL_DIR="$BASE_DIR/apps/database/ddl/schemas"
OUTPUT_FILE="$BASE_DIR/orchestration/analisis/coherencia/DDL-COMPLETE-MATRIX.yml"

# Crear directorio de salida
mkdir -p "$BASE_DIR/orchestration/analisis/coherencia"

# Iniciar archivo YAML
cat > "$OUTPUT_FILE" << 'EOF'
# ==============================================================================
# DDL Complete Matrix - Trading Platform
# Generated: $(date -u +"%Y-%m-%d %H:%M:%S UTC")
# Total Schemas: 10
# ==============================================================================
version: "1.0.0"
project: "trading-platform"
generated_at: "$(date -u +"%Y-%m-%d %H:%M:%S UTC")"

schemas:
EOF

# Iterar por cada schema
for schema_dir in "$DDL_DIR"/*; do
  if [ -d "$schema_dir" ]; then
    schema_name=$(basename "$schema_dir")

    echo "  $schema_name:" >> "$OUTPUT_FILE"

    # Extraer enums
    echo "    enums:" >> "$OUTPUT_FILE"
    if [ -f "$schema_dir/00-enums.sql" ] || [ -f "$schema_dir/01-enums.sql" ]; then
      enum_file=$(ls "$schema_dir"/*enum*.sql 2>/dev/null | head -1)
      if [ -f "$enum_file" ]; then
        grep -E "CREATE TYPE.*AS ENUM" "$enum_file" | sed 's/CREATE TYPE /      - /' | sed 's/ AS ENUM.*//' >> "$OUTPUT_FILE"
      fi
    else
      echo "      []" >> "$OUTPUT_FILE"
    fi

    # Extraer tablas
    echo "    tables:" >> "$OUTPUT_FILE"
    if [ -d "$schema_dir/tables" ]; then
      for table_file in "$schema_dir/tables"/*.sql; do
        if [ -f "$table_file" ]; then
          table_name=$(grep -m1 "CREATE TABLE" "$table_file" | sed 's/CREATE TABLE //' | sed 's/ (.*//' | sed "s/${schema_name}\.//" | tr -d '(')

          if [ ! -z "$table_name" ]; then
            echo "      - name: $table_name" >> "$OUTPUT_FILE"
            echo "        file: $(basename "$table_file")" >> "$OUTPUT_FILE"

            # Extraer columnas principales (PK, FK, campos importantes)
            echo "        columns:" >> "$OUTPUT_FILE"
            grep -E "^  [a-z_]+.*," "$table_file" | head -20 | sed 's/^  /          - /' | sed 's/,.*//' >> "$OUTPUT_FILE"

            # Extraer FKs
            echo "        foreign_keys:" >> "$OUTPUT_FILE"
            grep -i "REFERENCES" "$table_file" | sed 's/.*REFERENCES /          - references: /' | sed 's/(.*//' >> "$OUTPUT_FILE" || echo "          []" >> "$OUTPUT_FILE"

            # Extraer índices
            echo "        indexes:" >> "$OUTPUT_FILE"
            grep "CREATE INDEX" "$table_file" | sed 's/CREATE INDEX /          - /' | sed 's/ ON.*//' >> "$OUTPUT_FILE" || echo "          []" >> "$OUTPUT_FILE"
          fi
        fi
      done
    else
      echo "      []" >> "$OUTPUT_FILE"
    fi

    # Extraer funciones
    echo "    functions:" >> "$OUTPUT_FILE"
    if [ -d "$schema_dir/functions" ]; then
      for func_file in "$schema_dir/functions"/*.sql; do
        if [ -f "$func_file" ]; then
          func_name=$(grep -m1 "CREATE.*FUNCTION" "$func_file" | sed 's/.*FUNCTION //' | sed 's/(.*//')
          if [ ! -z "$func_name" ]; then
            echo "      - $(basename "$func_file" .sql): $func_name" >> "$OUTPUT_FILE"
          fi
        fi
      done
    else
      echo "      []" >> "$OUTPUT_FILE"
    fi

    # Extraer triggers
    echo "    triggers:" >> "$OUTPUT_FILE"
    if [ -d "$schema_dir/functions" ]; then
      grep -h "CREATE TRIGGER" "$schema_dir/functions"/*.sql 2>/dev/null | sed 's/CREATE TRIGGER /      - /' | sed 's/ BEFORE.*//' | sed 's/ AFTER.*//' >> "$OUTPUT_FILE" || echo "      []" >> "$OUTPUT_FILE"
    else
      echo "      []" >> "$OUTPUT_FILE"
    fi

    echo "" >> "$OUTPUT_FILE"
  fi
done

echo "DDL matrix generada en: $OUTPUT_FILE"
echo "Total de lineas: $(wc -l < "$OUTPUT_FILE")"