#!/bin/bash
# =============================================================================
# DROP AND RECREATE DATABASE - ERP CONSTRUCCION
# =============================================================================
# Script de carga limpia con soporte para seeds por ambiente
#
# Uso: ./drop-and-recreate-database.sh [opciones] [DATABASE_URL]
#
# Opciones:
#   -s, --seeds         Cargar seeds despues de DDL
#   -e, --env ENV       Ambiente: dev|staging|prod (default: dev)
#   -f, --force         No pedir confirmacion
#   -v, --verbose       Modo verbose
#   -h, --help          Mostrar ayuda
#
# Ejemplos:
#   ./drop-and-recreate-database.sh --seeds
#   ./drop-and-recreate-database.sh --seeds --env dev
#   ./drop-and-recreate-database.sh -s -f "postgresql://user:pass@localhost:5432/mydb"
#
# Referencia: @SIMCO-DDL-UNIFIED, @TRIGGER-DDL-WSL
# =============================================================================

set -e

# =============================================================================
# CONFIGURACION
# =============================================================================

# Colores
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
CYAN='\033[0;36m'
NC='\033[0m'

# Directorios
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
SCHEMAS_DIR="$SCRIPT_DIR/schemas"
INIT_SCRIPTS_DIR="$SCRIPT_DIR/init-scripts"
SEEDS_DIR="$SCRIPT_DIR/seeds"

# Valores por defecto
LOAD_SEEDS=false
ENVIRONMENT="dev"
FORCE=false
VERBOSE=false
DATABASE_URL=""

# Credenciales por defecto (segun WORKSPACE-INTEGRATION.yml)
DEFAULT_HOST="localhost"
DEFAULT_PORT="5432"
DEFAULT_DB="erp_construccion_demo"
DEFAULT_USER="erp_admin"
DEFAULT_PASS="erp_dev_2026"

# =============================================================================
# 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"; }
log_step() { echo -e "${CYAN}[STEP]${NC} $1"; }

show_help() {
    cat << EOF
Uso: $0 [opciones] [DATABASE_URL]

Script de recreacion de base de datos con soporte para seeds.

Opciones:
  -s, --seeds         Cargar seeds despues del DDL
  -e, --env ENV       Ambiente para seeds: dev|staging|prod (default: dev)
  -f, --force         No pedir confirmacion para DROP
  -v, --verbose       Mostrar output detallado
  -h, --help          Mostrar esta ayuda

DATABASE_URL:
  Formato: postgresql://user:pass@host:port/database
  Default: postgresql://$DEFAULT_USER:****@$DEFAULT_HOST:$DEFAULT_PORT/$DEFAULT_DB

Ejemplos:
  $0 --seeds                    # Recrear BD y cargar seeds dev
  $0 --seeds --env staging      # Recrear BD con seeds staging
  $0 -s -f                      # Recrear sin confirmacion
  $0 "postgresql://..."         # Usar URL personalizada

Seeds disponibles por ambiente:
  dev/      - Datos completos para desarrollo (usuarios demo, proyectos, etc.)
  staging/  - Datos minimos para pruebas
  prod/     - Solo catalogos base (sin datos de prueba)

EOF
}

# =============================================================================
# PARSEO DE ARGUMENTOS
# =============================================================================

parse_args() {
    while [[ $# -gt 0 ]]; do
        case $1 in
            -h|--help)
                show_help
                exit 0
                ;;
            -s|--seeds)
                LOAD_SEEDS=true
                shift
                ;;
            -e|--env)
                ENVIRONMENT="$2"
                shift 2
                ;;
            -f|--force)
                FORCE=true
                shift
                ;;
            -v|--verbose)
                VERBOSE=true
                shift
                ;;
            postgresql://*)
                DATABASE_URL="$1"
                shift
                ;;
            *)
                log_error "Opcion desconocida: $1"
                show_help
                exit 1
                ;;
        esac
    done

    # Construir DATABASE_URL si no se proporciono
    if [ -z "$DATABASE_URL" ]; then
        DATABASE_URL="postgresql://$DEFAULT_USER:$DEFAULT_PASS@$DEFAULT_HOST:$DEFAULT_PORT/$DEFAULT_DB"
    fi

    # Validar ambiente
    if [[ ! "$ENVIRONMENT" =~ ^(dev|staging|prod)$ ]]; then
        log_error "Ambiente invalido: $ENVIRONMENT (usar: dev|staging|prod)"
        exit 1
    fi
}

# =============================================================================
# FUNCIONES DE BASE DE DATOS
# =============================================================================

run_sql() {
    local sql="$1"
    if [ "$VERBOSE" = true ]; then
        psql "$DATABASE_URL" -c "$sql"
    else
        psql "$DATABASE_URL" -c "$sql" > /dev/null 2>&1
    fi
}

run_sql_file() {
    local file="$1"
    if [ "$VERBOSE" = true ]; then
        psql "$DATABASE_URL" -f "$file"
    else
        psql "$DATABASE_URL" -f "$file" > /dev/null 2>&1
    fi
}

run_sql_quiet() {
    local sql="$1"
    psql "$DATABASE_URL" -t -A -c "$sql" 2>/dev/null | tr -d ' \n'
}

# =============================================================================
# PASO 0: VERIFICACIONES INICIALES
# =============================================================================

verify_connection() {
    log_step "Verificando conexion a PostgreSQL..."

    if ! psql "$DATABASE_URL" -c "SELECT 1" > /dev/null 2>&1; then
        log_error "No se puede conectar a PostgreSQL"
        log_info "URL: $DATABASE_URL"
        log_info "Verificar que PostgreSQL esta corriendo y las credenciales son correctas"
        exit 1
    fi

    log_success "Conexion establecida"
}

confirm_drop() {
    if [ "$FORCE" = true ]; then
        return 0
    fi

    local db_name=$(echo "$DATABASE_URL" | sed -E 's|.*/([^?]+).*|\1|')

    echo ""
    echo -e "${RED}ADVERTENCIA: Esto eliminara TODOS los datos de: $db_name${NC}"
    echo ""
    read -p "Escribe 'SI' para confirmar: " confirm

    if [ "$confirm" != "SI" ]; then
        log_info "Operacion cancelada"
        exit 0
    fi
}

# =============================================================================
# PASO 1: DROP SCHEMAS EXISTENTES
# =============================================================================

drop_schemas() {
    log_step "Eliminando schemas existentes..."

    # Schemas a eliminar (orden inverso de dependencias)
    local schemas=(
        "documents" "assets" "finance" "infonavit" "hse" "hr"
        "purchase" "inventory" "estimates" "construction"
        "tenants" "core_shared" "core" "auth"
        # Legacy schemas
        "infonavit_management" "safety_management" "quality_management"
        "construction_management" "inventory_management" "purchasing_management"
        "financial_management" "project_management" "auth_management"
    )

    for schema in "${schemas[@]}"; do
        if [ "$(run_sql_quiet "SELECT 1 FROM pg_namespace WHERE nspname = '$schema'")" = "1" ]; then
            run_sql "DROP SCHEMA IF EXISTS $schema CASCADE"
            [ "$VERBOSE" = true ] && log_info "Schema $schema eliminado"
        fi
    done

    log_success "Schemas eliminados"
}

# =============================================================================
# PASO 2: CREAR SCHEMAS BASE (auth, core)
# =============================================================================

create_base_schemas() {
    log_step "Creando schemas base (auth, core)..."

    # auth y core deben existir antes de ejecutar los DDLs
    psql "$DATABASE_URL" << 'EOSQL' > /dev/null 2>&1
-- Crear schemas base que son prerequisito
CREATE SCHEMA IF NOT EXISTS auth;
CREATE SCHEMA IF NOT EXISTS core;
CREATE SCHEMA IF NOT EXISTS core_shared;
CREATE SCHEMA IF NOT EXISTS tenants;

-- Tabla auth.tenants (necesaria para FK)
CREATE TABLE IF NOT EXISTS auth.tenants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    settings JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Tabla auth.users (necesaria para FK)
CREATE TABLE IF NOT EXISTS auth.users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID REFERENCES auth.tenants(id),
    email VARCHAR(255) NOT NULL,
    username VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON SCHEMA auth IS 'Schema de autenticacion - tablas base para FK';
COMMENT ON SCHEMA core IS 'Schema de catalogos core';
EOSQL

    log_success "Schemas base creados"
}

# =============================================================================
# PASO 3: EJECUTAR DDL INICIAL
# =============================================================================

execute_init_ddl() {
    log_step "Ejecutando DDL inicial..."

    if [ -f "$INIT_SCRIPTS_DIR/01-init-database.sql" ]; then
        run_sql_file "$INIT_SCRIPTS_DIR/01-init-database.sql"
        log_success "01-init-database.sql ejecutado"
    else
        log_error "No se encontro archivo de inicializacion"
        exit 1
    fi
}

# =============================================================================
# PASO 4: EJECUTAR DDL DE SCHEMAS
# =============================================================================

execute_schema_ddl() {
    log_step "Ejecutando DDL de schemas..."

    if [ ! -d "$SCHEMAS_DIR" ]; then
        log_warning "Directorio schemas/ no encontrado"
        return 0
    fi

    # Ejecutar en orden numerico
    for ddl_file in $(ls -1 "$SCHEMAS_DIR"/*.sql 2>/dev/null | sort -V); do
        local filename=$(basename "$ddl_file")
        echo -ne "  - ${YELLOW}$filename${NC}..."

        if run_sql_file "$ddl_file"; then
            echo -e " ${GREEN}OK${NC}"
        else
            echo -e " ${RED}ERROR${NC}"
            log_error "Fallo al ejecutar: $ddl_file"
            exit 1
        fi
    done

    log_success "DDL de schemas completado"
}

# =============================================================================
# PASO 4: CREAR TABLAS COMPLEMENTARIAS (para seeds)
# =============================================================================

create_complementary_tables() {
    log_step "Creando tablas complementarias de erp-core..."

    # Estas tablas son necesarias para que los seeds funcionen
    # cuando se ejecuta standalone (sin erp-core completo)

    psql "$DATABASE_URL" << 'EOSQL' > /dev/null 2>&1
-- ============================================================
-- TABLAS COMPLEMENTARIAS PARA SEEDS
-- Estas tablas normalmente vienen de erp-core
-- ============================================================

-- Schema tenants
CREATE SCHEMA IF NOT EXISTS tenants;

-- tenants.tenants (estructura completa)
CREATE TABLE IF NOT EXISTS tenants.tenants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    legal_name VARCHAR(255),
    rfc VARCHAR(20),
    business_type VARCHAR(100),
    status VARCHAR(20) DEFAULT 'active',
    address_street TEXT,
    address_city VARCHAR(100),
    address_state VARCHAR(100),
    address_zip VARCHAR(20),
    address_country VARCHAR(100) DEFAULT 'Mexico',
    phone VARCHAR(50),
    email VARCHAR(255),
    website VARCHAR(255),
    logo_url VARCHAR(500),
    primary_color VARCHAR(10),
    secondary_color VARCHAR(10),
    fiscal_regime VARCHAR(10),
    cfdi_certificate_number VARCHAR(50),
    settings JSONB DEFAULT '{}',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    updated_by UUID
);

-- tenants.tenant_settings
CREATE TABLE IF NOT EXISTS tenants.tenant_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID REFERENCES tenants.tenants(id),
    setting_key VARCHAR(100) NOT NULL,
    setting_value JSONB,
    setting_type VARCHAR(50),
    description TEXT,
    is_system BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- tenants.branches
CREATE TABLE IF NOT EXISTS tenants.branches (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID REFERENCES tenants.tenants(id),
    code VARCHAR(50) NOT NULL,
    name VARCHAR(200) NOT NULL,
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    phone VARCHAR(50),
    is_main BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID,
    UNIQUE(tenant_id, code)
);

-- auth.tenants (si no existe)
CREATE TABLE IF NOT EXISTS auth.tenants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    settings JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- auth.users (extender si existe)
DO $$ BEGIN
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS password_hash VARCHAR(255);
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS first_name VARCHAR(100);
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS last_name VARCHAR(100);
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS middle_name VARCHAR(100);
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS phone VARCHAR(50);
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS avatar_url VARCHAR(500);
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS position VARCHAR(100);
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS department VARCHAR(100);
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS role_id UUID;
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS language VARCHAR(10) DEFAULT 'es';
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS timezone VARCHAR(50) DEFAULT 'America/Mexico_City';
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMPTZ;
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS email_verified BOOLEAN DEFAULT false;
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS is_verified BOOLEAN DEFAULT false;
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS is_system BOOLEAN DEFAULT false;
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS failed_login_attempts INT DEFAULT 0;
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS locked_until TIMESTAMPTZ;
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS must_change_password BOOLEAN DEFAULT false;
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS created_by UUID;
    ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS settings JSONB DEFAULT '{}';
EXCEPTION WHEN undefined_table THEN NULL; END $$;

-- auth.roles
CREATE TABLE IF NOT EXISTS auth.roles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID,
    code VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    permissions JSONB DEFAULT '[]',
    is_system BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, code)
);

-- auth.permissions
CREATE TABLE IF NOT EXISTS auth.permissions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID,
    code VARCHAR(100) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    module VARCHAR(50),
    action VARCHAR(50),
    resource VARCHAR(100),
    is_system BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, code)
);

-- auth.user_roles
CREATE TABLE IF NOT EXISTS auth.user_roles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID,
    role_id UUID,
    tenant_id UUID,
    is_primary BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, role_id)
);

-- auth.role_permissions
CREATE TABLE IF NOT EXISTS auth.role_permissions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role_id UUID,
    permission_id UUID,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(role_id, permission_id)
);

-- core.units (estructura completa)
DROP TABLE IF EXISTS core.units CASCADE;
CREATE TABLE core.units (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    symbol VARCHAR(10),
    abbreviation VARCHAR(10),
    category VARCHAR(50),
    unit_type VARCHAR(50),
    description TEXT,
    base_unit_id UUID,
    conversion_factor DECIMAL(18,6) DEFAULT 1,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(code)
);

-- core.currencies
CREATE TABLE IF NOT EXISTS core.currencies (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(3) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    symbol VARCHAR(10),
    decimal_places INT DEFAULT 2,
    is_active BOOLEAN DEFAULT true,
    is_default BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- core.taxes
CREATE TABLE IF NOT EXISTS core.taxes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    rate DECIMAL(5,4) NOT NULL,
    tax_type VARCHAR(20),
    is_retention BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- core.payment_methods
CREATE TABLE IF NOT EXISTS core.payment_methods (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    requires_reference BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- core.banks
CREATE TABLE IF NOT EXISTS core.banks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    short_name VARCHAR(50),
    swift_code VARCHAR(20),
    country VARCHAR(50) DEFAULT 'Mexico',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- core.states
CREATE TABLE IF NOT EXISTS core.states (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(10) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    abbreviation VARCHAR(10),
    country VARCHAR(50) DEFAULT 'Mexico',
    country_code VARCHAR(3) DEFAULT 'MX',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- core.municipalities
CREATE TABLE IF NOT EXISTS core.municipalities (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    state_id UUID REFERENCES core.states(id),
    code VARCHAR(10),
    name VARCHAR(200),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- core.material_categories
CREATE TABLE IF NOT EXISTS core.material_categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    parent_id UUID,
    description TEXT,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- core.cost_types
CREATE TABLE IF NOT EXISTS core.cost_types (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    factor DECIMAL(5,2),
    is_direct BOOLEAN DEFAULT true,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- core.document_types
CREATE TABLE IF NOT EXISTS core.document_types (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    category VARCHAR(50),
    requires_signature BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Columnas adicionales para tablas existentes
DO $$ BEGIN
    -- construction.fraccionamientos
    ALTER TABLE construction.fraccionamientos ADD COLUMN IF NOT EXISTS project_type VARCHAR(50);
    ALTER TABLE construction.fraccionamientos ADD COLUMN IF NOT EXISTS estimated_end_date DATE;
    ALTER TABLE construction.fraccionamientos ADD COLUMN IF NOT EXISTS budget DECIMAL(18,2);
    ALTER TABLE construction.fraccionamientos ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;

    -- construction.conceptos
    ALTER TABLE construction.conceptos ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;

    -- hse.incidentes
    ALTER TABLE hse.incidentes ADD COLUMN IF NOT EXISTS acciones_inmediatas TEXT;
    ALTER TABLE hse.incidentes ADD COLUMN IF NOT EXISTS investigado_por UUID;
    ALTER TABLE hse.incidentes ADD COLUMN IF NOT EXISTS fecha_investigacion DATE;
    ALTER TABLE hse.incidentes ADD COLUMN IF NOT EXISTS cerrado_por UUID;
    ALTER TABLE hse.incidentes ADD COLUMN IF NOT EXISTS fecha_cierre DATE;

    -- hse.capacitaciones
    ALTER TABLE hse.capacitaciones ADD COLUMN IF NOT EXISTS puntaje_minimo INTEGER;

    -- hse.horas_trabajadas
    ALTER TABLE hse.horas_trabajadas ADD COLUMN IF NOT EXISTS trabajadores_directos INTEGER;
    ALTER TABLE hse.horas_trabajadas ADD COLUMN IF NOT EXISTS trabajadores_indirectos INTEGER;

    -- assets.assets
    ALTER TABLE assets.assets ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;

    -- assets.maintenance_plans
    ALTER TABLE assets.maintenance_plans ADD COLUMN IF NOT EXISTS estimated_cost DECIMAL(18,2);
    ALTER TABLE assets.maintenance_plans ADD COLUMN IF NOT EXISTS actual_cost DECIMAL(18,2);
    ALTER TABLE assets.maintenance_plans ADD COLUMN IF NOT EXISTS tasks_description TEXT;
    ALTER TABLE assets.maintenance_plans ADD COLUMN IF NOT EXISTS last_performed_at TIMESTAMPTZ;
    ALTER TABLE assets.maintenance_plans ADD COLUMN IF NOT EXISTS next_due_at TIMESTAMPTZ;
    ALTER TABLE assets.maintenance_plans ALTER COLUMN activities DROP NOT NULL;
EXCEPTION WHEN undefined_table THEN NULL; END $$;

EOSQL

    log_success "Tablas complementarias creadas"
}

# =============================================================================
# PASO 5: OTORGAR PERMISOS
# =============================================================================

grant_permissions() {
    log_step "Otorgando permisos..."

    local db_user=$(echo "$DATABASE_URL" | sed -E 's|.*://([^:]+):.*|\1|')

    psql "$DATABASE_URL" << EOSQL > /dev/null 2>&1
-- Permisos sobre schemas
GRANT ALL ON SCHEMA auth TO $db_user;
GRANT ALL ON SCHEMA core TO $db_user;
GRANT ALL ON SCHEMA core_shared TO $db_user;
GRANT ALL ON SCHEMA tenants TO $db_user;
GRANT ALL ON SCHEMA construction TO $db_user;
GRANT ALL ON SCHEMA estimates TO $db_user;
GRANT ALL ON SCHEMA infonavit TO $db_user;
GRANT ALL ON SCHEMA hse TO $db_user;
GRANT ALL ON SCHEMA hr TO $db_user;
GRANT ALL ON SCHEMA inventory TO $db_user;
GRANT ALL ON SCHEMA purchase TO $db_user;
GRANT ALL ON SCHEMA finance TO $db_user;
GRANT ALL ON SCHEMA assets TO $db_user;
GRANT ALL ON SCHEMA documents TO $db_user;

-- Permisos sobre tablas
GRANT ALL ON ALL TABLES IN SCHEMA auth TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA core TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA core_shared TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA tenants TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA construction TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA estimates TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA infonavit TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA hse TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA hr TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA inventory TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA purchase TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA finance TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA assets TO $db_user;
GRANT ALL ON ALL TABLES IN SCHEMA documents TO $db_user;

-- Permisos sobre secuencias
GRANT ALL ON ALL SEQUENCES IN SCHEMA auth TO $db_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA core TO $db_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA construction TO $db_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA estimates TO $db_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA infonavit TO $db_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA hse TO $db_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA hr TO $db_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA inventory TO $db_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA purchase TO $db_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA finance TO $db_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA assets TO $db_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA documents TO $db_user;

-- Permisos por defecto
ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT ALL ON TABLES TO $db_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA core GRANT ALL ON TABLES TO $db_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA construction GRANT ALL ON TABLES TO $db_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA estimates GRANT ALL ON TABLES TO $db_user;
EOSQL

    log_success "Permisos otorgados"
}

# =============================================================================
# PASO 6: CARGAR SEEDS
# =============================================================================

load_seeds() {
    local env_dir="$SEEDS_DIR/$ENVIRONMENT"

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

    log_step "Cargando seeds ($ENVIRONMENT)..."

    # Seeds en orden de dependencias (las tablas complementarias ya existen)
    local seeds=(
        "01-core-catalogs.sql"
        "02-users-profiles.sql"
        "03-empresa-tenant.sql"
        "04-proyectos-obras.sql"
        "02b-hr-employees.sql"
        "05-presupuestos.sql"
        "06-estimaciones.sql"
        "07-avances-calidad.sql"
        "08-hse.sql"
        "09-finanzas.sql"
        "10-activos-documentos.sql"
        "11-infonavit.sql"
    )

    local success=0
    local failed=0
    local failed_seeds=""

    for seed in "${seeds[@]}"; do
        local seed_file="$env_dir/$seed"

        if [ ! -f "$seed_file" ]; then
            [ "$VERBOSE" = true ] && log_warning "Seed no encontrado: $seed"
            continue
        fi

        echo -ne "  - ${YELLOW}$seed${NC}..."

        # Ejecutar seed (las transacciones estan dentro de cada archivo)
        local output=$(psql "$DATABASE_URL" -f "$seed_file" 2>&1)

        if echo "$output" | grep -qE "(^COMMIT|^INSERT|^DO)"; then
            echo -e " ${GREEN}OK${NC}"
            success=$((success + 1))
        else
            echo -e " ${RED}FAIL${NC}"
            failed=$((failed + 1))
            failed_seeds="$failed_seeds $seed"
            if [ "$VERBOSE" = true ]; then
                echo "$output" | grep -E "(^ERROR|violates)" | head -3
            fi
        fi
    done

    echo ""
    if [ $failed -eq 0 ]; then
        log_success "Todos los seeds cargados ($success/$success)"
    else
        log_warning "Seeds cargados: $success exitosos, $failed fallidos"
        [ "$VERBOSE" = true ] && log_info "Fallidos:$failed_seeds"
    fi
}

# =============================================================================
# PASO 7: VERIFICAR RESULTADO
# =============================================================================

verify_result() {
    log_step "Verificando resultado..."

    local schema_count=$(run_sql_quiet "
        SELECT COUNT(*) FROM pg_namespace
        WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast', 'public')
        AND nspname NOT LIKE 'pg_%'
    ")

    local table_count=$(run_sql_quiet "
        SELECT COUNT(*) FROM pg_tables
        WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'public')
    ")

    echo ""
    echo -e "${GREEN}=== RESUMEN ===${NC}"
    psql "$DATABASE_URL" -c "
        SELECT schemaname AS \"Schema\", COUNT(*) AS \"Tablas\"
        FROM pg_tables
        WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'public')
        GROUP BY schemaname
        ORDER BY schemaname;
    "

    if [ "$LOAD_SEEDS" = true ]; then
        echo ""
        echo -e "${CYAN}=== DATOS CARGADOS ===${NC}"
        psql "$DATABASE_URL" -c "
            SELECT 'auth.users' as tabla, COUNT(*) as registros FROM auth.users
            UNION ALL SELECT 'auth.roles', COUNT(*) FROM auth.roles
            UNION ALL SELECT 'core.units', COUNT(*) FROM core.units
            UNION ALL SELECT 'construction.conceptos', COUNT(*) FROM construction.conceptos
            UNION ALL SELECT 'hr.employees', COUNT(*) FROM hr.employees
            ORDER BY tabla;
        " 2>/dev/null || true
    fi

    echo ""
    echo -e "${GREEN}=============================================================================${NC}"
    echo -e "${GREEN}   RECREACION COMPLETADA EXITOSAMENTE${NC}"
    echo -e "${GREEN}=============================================================================${NC}"
    echo -e "  Schemas: ${YELLOW}$schema_count${NC}"
    echo -e "  Tablas:  ${YELLOW}$table_count${NC}"
    [ "$LOAD_SEEDS" = true ] && echo -e "  Seeds:   ${YELLOW}$ENVIRONMENT${NC}"
    echo -e "${GREEN}=============================================================================${NC}"

    if [ "$LOAD_SEEDS" = true ] && [ "$ENVIRONMENT" = "dev" ]; then
        echo ""
        echo -e "${CYAN}Usuarios demo disponibles:${NC}"
        echo "  - admin@demo.com / Demo2026! (Super Admin)"
        echo "  - director@demo.com / Demo2026! (Director)"
        echo "  - gerente1@demo.com / Demo2026! (Gerente Obra)"
        echo "  - residente1@demo.com / Demo2026! (Residente)"
        echo ""
        echo -e "${CYAN}Tenant ID:${NC} 00000000-0000-0000-0003-000000000001"
        echo ""
    fi
}

# =============================================================================
# MAIN
# =============================================================================

main() {
    parse_args "$@"

    echo -e "${BLUE}=============================================================================${NC}"
    echo -e "${BLUE}   ERP CONSTRUCCION - Recreacion de Base de Datos${NC}"
    echo -e "${BLUE}=============================================================================${NC}"
    echo ""

    local db_name=$(echo "$DATABASE_URL" | sed -E 's|.*/([^?]+).*|\1|')
    local db_host=$(echo "$DATABASE_URL" | sed -E 's|.*@([^:]+):.*|\1|')
    local db_port=$(echo "$DATABASE_URL" | sed -E 's|.*:([0-9]+)/.*|\1|')

    echo -e "Database: ${YELLOW}$db_name${NC}"
    echo -e "Host:     ${YELLOW}$db_host:$db_port${NC}"
    echo -e "Seeds:    ${YELLOW}$([ "$LOAD_SEEDS" = true ] && echo "$ENVIRONMENT" || echo "No")${NC}"
    echo ""

    # Ejecutar pasos
    verify_connection
    confirm_drop

    echo ""
    drop_schemas
    create_base_schemas
    execute_init_ddl
    execute_schema_ddl
    create_complementary_tables
    grant_permissions

    if [ "$LOAD_SEEDS" = true ]; then
        echo ""
        load_seeds
    fi

    echo ""
    verify_result
}

main "$@"