#!/bin/bash
# =============================================================
# SCRIPT: recreate-database.sh
# DESCRIPCION: Script de recreacion completa de base de datos ERP-Core
# VERSION: 1.0.0
# PROYECTO: ERP-Core V2
# FECHA: 2026-01-10
# =============================================================

set -e

# Colores para output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# Configuracion por defecto
DB_HOST="${DB_HOST:-localhost}"
DB_PORT="${DB_PORT:-5432}"
DB_NAME="${DB_NAME:-erp_core}"
DB_USER="${DB_USER:-postgres}"
DB_PASSWORD="${DB_PASSWORD:-}"

# Directorios
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
DATABASE_DIR="$(dirname "$SCRIPT_DIR")"
DDL_DIR="$DATABASE_DIR/ddl"
MIGRATIONS_DIR="$DATABASE_DIR/migrations"
SEEDS_DIR="$DATABASE_DIR/seeds"

# Flags
DROP_DB=false
LOAD_SEEDS=false
VERBOSE=false
DRY_RUN=false

# Funciones de utilidad
log_info() {
    echo -e "${BLUE}[INFO]${NC} $1"
}

log_success() {
    echo -e "${GREEN}[OK]${NC} $1"
}

log_warning() {
    echo -e "${YELLOW}[WARN]${NC} $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

show_help() {
    echo "Uso: $0 [opciones]"
    echo ""
    echo "Script de recreacion de base de datos ERP-Core"
    echo ""
    echo "Opciones:"
    echo "  -h, --help          Mostrar esta ayuda"
    echo "  -d, --drop          Eliminar y recrear la base de datos completa"
    echo "  -s, --seeds         Cargar seeds de desarrollo"
    echo "  -v, --verbose       Modo verbose"
    echo "  --dry-run           Mostrar comandos sin ejecutar"
    echo ""
    echo "Variables de entorno:"
    echo "  DB_HOST             Host de la base de datos (default: localhost)"
    echo "  DB_PORT             Puerto de la base de datos (default: 5432)"
    echo "  DB_NAME             Nombre de la base de datos (default: erp_core)"
    echo "  DB_USER             Usuario de la base de datos (default: postgres)"
    echo "  DB_PASSWORD         Password de la base de datos"
    echo ""
    echo "Ejemplos:"
    echo "  $0                  Ejecutar DDL y migraciones sin eliminar DB"
    echo "  $0 -d               Eliminar y recrear DB completa"
    echo "  $0 -d -s            Eliminar, recrear DB y cargar seeds"
    echo "  DB_HOST=db.example.com $0 -d   Usar host remoto"
}

parse_args() {
    while [[ $# -gt 0 ]]; do
        case $1 in
            -h|--help)
                show_help
                exit 0
                ;;
            -d|--drop)
                DROP_DB=true
                shift
                ;;
            -s|--seeds)
                LOAD_SEEDS=true
                shift
                ;;
            -v|--verbose)
                VERBOSE=true
                shift
                ;;
            --dry-run)
                DRY_RUN=true
                shift
                ;;
            *)
                log_error "Opcion desconocida: $1"
                show_help
                exit 1
                ;;
        esac
    done
}

# Construir connection string
get_psql_cmd() {
    local cmd="psql -h $DB_HOST -p $DB_PORT -U $DB_USER"
    if [ -n "$DB_PASSWORD" ]; then
        cmd="PGPASSWORD=$DB_PASSWORD $cmd"
    fi
    echo "$cmd"
}

run_sql() {
    local db="$1"
    local sql="$2"
    local psql_cmd=$(get_psql_cmd)

    if [ "$DRY_RUN" = true ]; then
        log_info "[DRY-RUN] Ejecutaria en $db: $sql"
        return 0
    fi

    if [ "$VERBOSE" = true ]; then
        log_info "Ejecutando: $sql"
    fi

    eval "$psql_cmd -d $db -c \"$sql\""
}

run_sql_file() {
    local db="$1"
    local file="$2"
    local psql_cmd=$(get_psql_cmd)

    if [ ! -f "$file" ]; then
        log_error "Archivo no encontrado: $file"
        return 1
    fi

    if [ "$DRY_RUN" = true ]; then
        log_info "[DRY-RUN] Ejecutaria archivo: $file"
        return 0
    fi

    if [ "$VERBOSE" = true ]; then
        log_info "Ejecutando archivo: $file"
    fi

    eval "$psql_cmd -d $db -f \"$file\""
}

# Drop y recrear base de datos
drop_and_create_db() {
    local psql_cmd=$(get_psql_cmd)

    log_info "Eliminando base de datos existente: $DB_NAME"

    if [ "$DRY_RUN" = true ]; then
        log_info "[DRY-RUN] DROP DATABASE IF EXISTS $DB_NAME"
        log_info "[DRY-RUN] CREATE DATABASE $DB_NAME"
        return 0
    fi

    # Terminar conexiones activas
    eval "$psql_cmd -d postgres -c \"SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$DB_NAME' AND pid <> pg_backend_pid();\"" 2>/dev/null || true

    # Eliminar y crear base de datos
    eval "$psql_cmd -d postgres -c \"DROP DATABASE IF EXISTS $DB_NAME;\""
    eval "$psql_cmd -d postgres -c \"CREATE DATABASE $DB_NAME WITH ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' TEMPLATE template0;\""

    log_success "Base de datos $DB_NAME creada"
}

# Crear schemas base
create_base_schemas() {
    log_info "Creando schemas base..."

    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS auth;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS core;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS mobile;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS billing;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS users;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS flags;"
    # Sprint 3+ schemas
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS notifications;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS audit;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS webhooks;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS storage;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS ai;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS whatsapp;"
    # Business modules schemas
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS partners;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS products;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS inventory;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS sales;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS purchases;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS operations;"
    # Fiscal / CFDI
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS fiscal;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS payment_terminals;"
    # Settings, Reports, Financial, HR, Projects
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS core_settings;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS reports;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS financial;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS hr;"
    run_sql "$DB_NAME" "CREATE SCHEMA IF NOT EXISTS projects;"

    log_success "Schemas base creados"
}

# Crear extensiones requeridas
create_extensions() {
    log_info "Creando extensiones requeridas..."

    run_sql "$DB_NAME" "CREATE EXTENSION IF NOT EXISTS cube;"
    run_sql "$DB_NAME" "CREATE EXTENSION IF NOT EXISTS earthdistance;"

    log_success "Extensiones creadas"
}

# Verificar si existen tablas base
check_base_tables() {
    local psql_cmd=$(get_psql_cmd)
    local result

    result=$(eval "$psql_cmd -d $DB_NAME -t -c \"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'auth' AND table_name IN ('tenants', 'users');\"" 2>/dev/null | tr -d ' ')

    if [ "$result" -eq "2" ]; then
        return 0  # Tablas existen
    else
        return 1  # Tablas no existen
    fi
}

# Crear tablas base (si no existen y es una recreacion)
create_base_tables() {
    log_info "Creando tablas base (auth.tenants, auth.users)..."

    # Solo crear si estamos en modo drop o si no existen
    run_sql "$DB_NAME" "
        CREATE TABLE IF NOT EXISTS auth.tenants (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            name VARCHAR(200) NOT NULL,
            slug VARCHAR(100) UNIQUE NOT NULL,
            is_active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
            deleted_at TIMESTAMPTZ
        );

        CREATE TABLE IF NOT EXISTS auth.users (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            tenant_id UUID REFERENCES auth.tenants(id) ON DELETE CASCADE,
            email VARCHAR(255) NOT NULL,
            password_hash TEXT,
            first_name VARCHAR(100),
            last_name VARCHAR(100),
            is_active BOOLEAN DEFAULT TRUE,
            email_verified BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
            deleted_at TIMESTAMPTZ,
            UNIQUE(tenant_id, email)
        );

        CREATE INDEX IF NOT EXISTS idx_users_tenant ON auth.users(tenant_id);
        CREATE INDEX IF NOT EXISTS idx_users_email ON auth.users(email);
    "

    log_success "Tablas base creadas"
}

# Ejecutar archivos DDL en orden
run_ddl_files() {
    log_info "Ejecutando archivos DDL..."

    # Orden especifico de ejecucion
    # Nota: El orden es importante por las dependencias entre tablas
    local ddl_files=(
        # Auth base (tenants + users DDL - también creadas inline como safety net)
        "00-auth-base.sql"
        # Core existente
        "01-auth-profiles.sql"
        "02-auth-devices.sql"
        "03-core-branches.sql"
        "03b-core-companies.sql"
        "04-mobile.sql"
        "05-billing-usage.sql"
        # SaaS Extensions - Sprint 1-2 (EPIC-SAAS-001, EPIC-SAAS-002)
        "06-auth-extended.sql"
        "07-users-rbac.sql"
        "07a-permissions-seed.sql"
        "08-plans.sql"
        # SaaS Extensions - Sprint 3+ (EPIC-SAAS-003 - EPIC-SAAS-008)
        "09-notifications.sql"
        "10-audit.sql"
        "11-feature-flags.sql"
        "12-webhooks.sql"
        "13-storage.sql"
        "14-ai.sql"
        "15-whatsapp.sql"
        # Business Modules - ERP Core
        "16-partners.sql"
        "17-products.sql"
        "18-warehouses.sql"
        "19-product-attributes.sql"
        "20-core-catalogs.sql"
        "21-inventory.sql"
        "21b-inventory-extended.sql"
        "22-sales.sql"
        "23-purchases.sql"
        "24-invoices.sql"
        "25-payment-terminals.sql"
        # Fiscal / CFDI
        "26-fiscal-catalogs.sql"
        "27-cfdi-core.sql"
        "28-cfdi-operations.sql"
        "29-cfdi-rls-functions.sql"
        # Settings & Reports
        "30-settings.sql"
        "31-reports.sql"
        # HR & Purchases Extended
        "45-hr.sql"
        "46-purchases-matching.sql"
        # Financial Module
        "50-financial-schema.sql"
        "51-financial-accounts.sql"
        "52-financial-journals.sql"
        "53-financial-entries.sql"
        "54-financial-invoices.sql"
        "55-financial-payments.sql"
        "56-financial-taxes.sql"
        "57-financial-bank-reconciliation.sql"
        # Projects
        "60-projects-timesheets.sql"
        # RLS (must be last - depends on all tables)
        "99-rls-erp-modules.sql"
    )

    for ddl_file in "${ddl_files[@]}"; do
        local file_path="$DDL_DIR/$ddl_file"
        if [ -f "$file_path" ]; then
            log_info "Ejecutando: $ddl_file"
            run_sql_file "$DB_NAME" "$file_path"
            log_success "Completado: $ddl_file"
        else
            log_warning "Archivo no encontrado: $ddl_file"
        fi
    done
}

# Ejecutar migraciones
run_migrations() {
    log_info "Ejecutando migraciones..."

    if [ ! -d "$MIGRATIONS_DIR" ]; then
        log_warning "Directorio de migraciones no encontrado: $MIGRATIONS_DIR"
        return 0
    fi

    # Ordenar migraciones por nombre (fecha)
    local migration_files=$(ls -1 "$MIGRATIONS_DIR"/*.sql 2>/dev/null | sort)

    if [ -z "$migration_files" ]; then
        log_info "No hay migraciones pendientes"
        return 0
    fi

    for migration_file in $migration_files; do
        local filename=$(basename "$migration_file")
        log_info "Ejecutando migracion: $filename"
        run_sql_file "$DB_NAME" "$migration_file"
        log_success "Migracion completada: $filename"
    done
}

# Cargar seeds de desarrollo
load_seeds() {
    log_info "Cargando seeds de desarrollo..."

    local seeds_dev_dir="$SEEDS_DIR/dev"

    if [ ! -d "$seeds_dev_dir" ]; then
        log_warning "Directorio de seeds no encontrado: $seeds_dev_dir"
        return 0
    fi

    # Ordenar seeds por nombre
    local seed_files=$(ls -1 "$seeds_dev_dir"/*.sql 2>/dev/null | sort)

    if [ -z "$seed_files" ]; then
        log_info "No hay seeds para cargar"
        return 0
    fi

    for seed_file in $seed_files; do
        local filename=$(basename "$seed_file")
        log_info "Cargando seed: $filename"
        run_sql_file "$DB_NAME" "$seed_file"
        log_success "Seed cargado: $filename"
    done
}

# Validar creacion de tablas
validate_database() {
    log_info "Validando base de datos..."

    local psql_cmd=$(get_psql_cmd)

    if [ "$DRY_RUN" = true ]; then
        log_info "[DRY-RUN] Validaria tablas creadas"
        return 0
    fi

    # Contar tablas por schema
    echo ""
    echo "=== Resumen de tablas por schema ==="
    echo ""

    local schemas=("auth" "core" "mobile" "billing" "users" "flags" "notifications" "audit" "webhooks" "storage" "ai" "whatsapp" "partners" "products" "inventory" "sales" "purchases" "operations" "fiscal" "payment_terminals" "core_settings" "reports" "financial" "hr" "projects")
    local total_tables=0

    for schema in "${schemas[@]}"; do
        local count=$(eval "$psql_cmd -d $DB_NAME -t -c \"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '$schema';\"" | tr -d ' ')
        echo "  $schema: $count tablas"
        total_tables=$((total_tables + count))
    done

    echo ""
    echo "  Total: $total_tables tablas"
    echo ""

    # Listar tablas principales
    echo "=== Tablas principales creadas ==="
    echo ""
    eval "$psql_cmd -d $DB_NAME -c \"
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema IN ('auth', 'core', 'mobile', 'billing', 'users', 'flags', 'notifications', 'audit', 'webhooks', 'storage', 'ai', 'whatsapp', 'partners', 'products', 'inventory', 'sales', 'purchases', 'operations', 'fiscal', 'payment_terminals', 'core_settings', 'reports', 'financial', 'hr', 'projects')
        AND table_type = 'BASE TABLE'
        ORDER BY table_schema, table_name;
    \""

    log_success "Validacion completada"
}

# Mostrar configuracion actual
show_config() {
    echo ""
    echo "=== Configuracion ==="
    echo "  Host: $DB_HOST"
    echo "  Puerto: $DB_PORT"
    echo "  Base de datos: $DB_NAME"
    echo "  Usuario: $DB_USER"
    echo "  Drop DB: $DROP_DB"
    echo "  Cargar seeds: $LOAD_SEEDS"
    echo "  Dry run: $DRY_RUN"
    echo ""
}

# Main
main() {
    parse_args "$@"

    echo "=================================================="
    echo "  ERP-Core Database Recreation Script v1.0.0"
    echo "=================================================="

    show_config

    # Verificar que psql esta disponible
    if ! command -v psql &> /dev/null; then
        log_error "psql no encontrado. Instalar PostgreSQL client."
        exit 1
    fi

    # Verificar conexion
    log_info "Verificando conexion a PostgreSQL..."
    local psql_cmd=$(get_psql_cmd)
    if ! eval "$psql_cmd -d postgres -c 'SELECT 1;'" &> /dev/null; then
        log_error "No se puede conectar a PostgreSQL. Verificar credenciales."
        exit 1
    fi
    log_success "Conexion exitosa"

    # Ejecutar pasos
    if [ "$DROP_DB" = true ]; then
        drop_and_create_db
        create_base_schemas
        create_extensions
        create_base_tables
    fi

    # Ejecutar DDL
    run_ddl_files

    # Ejecutar migraciones
    run_migrations

    # Cargar seeds si se solicito
    if [ "$LOAD_SEEDS" = true ]; then
        load_seeds
    fi

    # Validar
    validate_database

    echo ""
    log_success "Recreacion de base de datos completada exitosamente"
    echo ""
}

main "$@"