#!/bin/bash

# =====================================================
# INSTALL SCRIPT - Schema Education
# =====================================================
# Proyecto: OrbiQuant IA (Trading Platform)
# Módulo: OQI-002 - Education
# Especificación: ET-EDU-001-database.md
# =====================================================

set -e  # Exit on error

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

# Configuration
DB_HOST="${DB_HOST:-localhost}"
DB_PORT="${DB_PORT:-5432}"
DB_NAME="${DB_NAME:-trading_platform}"
DB_USER="${DB_USER:-postgres}"
SCHEMA_NAME="education"

echo -e "${GREEN}=================================================${NC}"
echo -e "${GREEN}  OrbiQuant IA - Education Schema Installation${NC}"
echo -e "${GREEN}=================================================${NC}"
echo ""

# Check if psql is available
if ! command -v psql &> /dev/null; then
    echo -e "${RED}Error: psql command not found${NC}"
    echo "Please install PostgreSQL client"
    exit 1
fi

# Get script directory
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"

echo "Configuration:"
echo "  Database: $DB_NAME"
echo "  Host: $DB_HOST:$DB_PORT"
echo "  User: $DB_USER"
echo "  Schema: $SCHEMA_NAME"
echo ""

# Function to execute SQL file
execute_sql() {
    local file=$1
    local description=$2

    echo -e "${YELLOW}▶${NC} $description"

    if [ ! -f "$file" ]; then
        echo -e "${RED}  ✗ File not found: $file${NC}"
        return 1
    fi

    if PGPASSWORD=$DB_PASSWORD psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -f "$file" > /dev/null 2>&1; then
        echo -e "${GREEN}  ✓ Success${NC}"
        return 0
    else
        echo -e "${RED}  ✗ Failed${NC}"
        return 1
    fi
}

# Create schema if not exists
echo -e "${YELLOW}▶${NC} Creating schema: $SCHEMA_NAME"
PGPASSWORD=$DB_PASSWORD psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "CREATE SCHEMA IF NOT EXISTS $SCHEMA_NAME;" > /dev/null 2>&1
echo -e "${GREEN}  ✓ Schema created/verified${NC}"
echo ""

# 1. Install ENUMs
echo -e "${GREEN}[1/3] Installing ENUMs...${NC}"
execute_sql "$SCRIPT_DIR/00-enums.sql" "Creating ENUM types"
echo ""

# 2. Install Tables
echo -e "${GREEN}[2/3] Installing Tables...${NC}"
execute_sql "$SCRIPT_DIR/tables/01-categories.sql" "Creating table: categories"
execute_sql "$SCRIPT_DIR/tables/02-courses.sql" "Creating table: courses"
execute_sql "$SCRIPT_DIR/tables/03-modules.sql" "Creating table: modules"
execute_sql "$SCRIPT_DIR/tables/04-lessons.sql" "Creating table: lessons"
execute_sql "$SCRIPT_DIR/tables/05-enrollments.sql" "Creating table: enrollments"
execute_sql "$SCRIPT_DIR/tables/06-progress.sql" "Creating table: progress"
execute_sql "$SCRIPT_DIR/tables/07-quizzes.sql" "Creating table: quizzes"
execute_sql "$SCRIPT_DIR/tables/08-quiz_questions.sql" "Creating table: quiz_questions"
execute_sql "$SCRIPT_DIR/tables/09-quiz_attempts.sql" "Creating table: quiz_attempts"
execute_sql "$SCRIPT_DIR/tables/10-certificates.sql" "Creating table: certificates"
execute_sql "$SCRIPT_DIR/tables/11-user_achievements.sql" "Creating table: user_achievements"
execute_sql "$SCRIPT_DIR/tables/12-user_gamification_profile.sql" "Creating table: user_gamification_profile"
execute_sql "$SCRIPT_DIR/tables/13-user_activity_log.sql" "Creating table: user_activity_log"
execute_sql "$SCRIPT_DIR/tables/14-course_reviews.sql" "Creating table: course_reviews"
echo ""

# 3. Install Functions and Triggers
echo -e "${GREEN}[3/3] Installing Functions and Triggers...${NC}"
execute_sql "$SCRIPT_DIR/functions/01-update_updated_at.sql" "Creating trigger: update_updated_at"
execute_sql "$SCRIPT_DIR/functions/02-update_enrollment_progress.sql" "Creating function: update_enrollment_progress"
execute_sql "$SCRIPT_DIR/functions/03-auto_complete_enrollment.sql" "Creating function: auto_complete_enrollment"
execute_sql "$SCRIPT_DIR/functions/04-generate_certificate.sql" "Creating function: generate_certificate_number"
execute_sql "$SCRIPT_DIR/functions/05-update_course_stats.sql" "Creating function: update_course_stats"
execute_sql "$SCRIPT_DIR/functions/06-update_enrollment_count.sql" "Creating function: update_enrollment_count"
execute_sql "$SCRIPT_DIR/functions/07-update_gamification_profile.sql" "Creating functions: gamification"
execute_sql "$SCRIPT_DIR/functions/08-views.sql" "Creating views"
echo ""

# Verify installation
echo -e "${YELLOW}▶${NC} Verifying installation..."

TABLE_COUNT=$(PGPASSWORD=$DB_PASSWORD psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '$SCHEMA_NAME' AND table_type = 'BASE TABLE';" 2>/dev/null | xargs)

if [ "$TABLE_COUNT" -eq "14" ]; then
    echo -e "${GREEN}  ✓ All 14 tables created successfully${NC}"
else
    echo -e "${RED}  ✗ Expected 14 tables, found $TABLE_COUNT${NC}"
fi

echo ""
echo -e "${GREEN}=================================================${NC}"
echo -e "${GREEN}  Installation Complete!${NC}"
echo -e "${GREEN}=================================================${NC}"
echo ""
echo "Schema '$SCHEMA_NAME' has been installed successfully."
echo ""
echo "Next steps:"
echo "  1. Review the README.md for usage examples"
echo "  2. Run seed data scripts if needed"
echo "  3. Configure Row Level Security (RLS) policies"
echo ""