#!/bin/bash

###############################################################################
# Database Performance Validation Script
#
# This script validates database performance under load by checking:
# - Slow query log
# - Connection pool usage
# - Query execution times
# - Index usage statistics
#
# Usage: ./validate-database-performance.sh
###############################################################################

set -e

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

echo "=========================================="
echo "Database Performance Validation"
echo "=========================================="
echo ""

# Load environment variables
if [ -f .env ]; then
    export $(cat .env | grep -v '^#' | xargs)
fi

# Database connection details
DB_HOST=${DB_HOST:-127.0.0.1}
DB_PORT=${DB_PORT:-5432}
DB_NAME=${DB_DATABASE:-cbtappsc_production_db}
DB_USER=${DB_USERNAME:-cbtappsc_admin}

# Check if PostgreSQL is accessible
echo -e "${YELLOW}Checking PostgreSQL connection...${NC}"
if PGPASSWORD=${DB_PASSWORD} psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} -d ${DB_NAME} -c '\l' > /dev/null 2>&1; then
    echo -e "${GREEN}✓ PostgreSQL connection successful${NC}"
else
    echo -e "${RED}✗ Cannot connect to PostgreSQL${NC}"
    exit 1
fi

echo ""
echo "=========================================="
echo "1. Checking Indexes"
echo "=========================================="

# Check for critical indexes
CRITICAL_INDEXES=(
    "exam_participants_student_id"
    "exam_attempts_participant_id"
    "exam_attempts_start_time"
    "exam_answers_attempt_id"
)

for index in "${CRITICAL_INDEXES[@]}"; do
    echo -n "Checking index: $index ... "
    if PGPASSWORD=${DB_PASSWORD} psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} -d ${DB_NAME} -t -c \
        "SELECT 1 FROM pg_indexes WHERE indexname = '${index}';" | grep -q "1"; then
        echo -e "${GREEN}✓ Exists${NC}"
    else
        echo -e "${RED}✗ Missing!${NC}"
    fi
done

echo ""
echo "=========================================="
echo "2. Table Statistics"
echo "=========================================="

TABLES=(
    "exam_participants"
    "exam_attempts"
    "exam_answers"
    "users"
    "students"
    "exam_packages"
    "exam_questions"
)

for table in "${TABLES[@]}"; do
    echo -n "Table: $table ... "
    COUNT=$(PGPASSWORD=${DB_PASSWORD} psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} -d ${DB_NAME} -t -c \
        "SELECT COUNT(*) FROM ${table};" | xargs)
    echo "$COUNT rows"
done

echo ""
echo "=========================================="
echo "3. Query Performance Test"
echo "=========================================="

# Test exam query performance
echo "Testing exam participant query..."
TIME=$(PGPASSWORD=${DB_PASSWORD} psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} -d ${DB_NAME} -t -c \
    "EXPLAIN ANALYZE
    SELECT ep.*, e.name as exam_name, s.name as student_name
    FROM exam_participants ep
    INNER JOIN exams e ON ep.exam_id = e.id
    INNER JOIN students s ON ep.student_id = s.id
    ORDER BY ep.created_at DESC
    LIMIT 100;" 2>&1 | grep "Execution Time" | awk '{print $3}')

echo "Execution Time: ${TIME}ms"

if [ ${TIME%.*} -lt 100 ]; then
    echo -e "${GREEN}✓ Query performance is good (< 100ms)${NC}"
else
    echo -e "${YELLOW}⚠ Query performance could be improved (> 100ms)${NC}"
fi

echo ""
echo "Testing exam attempts query..."
TIME=$(PGPASSWORD=${DB_PASSWORD} psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} -d ${DB_NAME} -t -c \
    "EXPLAIN ANALYZE
    SELECT ea.*, ep.exam_id
    FROM exam_attempts ea
    INNER JOIN exam_participants ep ON ea.exam_participant_id = ep.id
    WHERE ep.student_id = 1
    ORDER BY ea.start_time DESC
    LIMIT 50;" 2>&1 | grep "Execution Time" | awk '{print $3}')

echo "Execution Time: ${TIME}ms"

if [ ${TIME%.*} -lt 100 ]; then
    echo -e "${GREEN}✓ Query performance is good (< 100ms)${NC}"
else
    echo -e "${YELLOW}⚠ Query performance could be improved (> 100ms)${NC}"
fi

echo ""
echo "=========================================="
echo "4. Connection Pool Status"
echo "=========================================="

PGPASSWORD=${DB_PASSWORD} psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} -d ${DB_NAME} -c \
    "SELECT
        count(*) as total_connections,
        count(*) FILTER (WHERE state = 'active') as active_connections,
        count(*) FILTER (WHERE state = 'idle') as idle_connections
    FROM pg_stat_activity WHERE datname = '${DB_NAME}';"

echo ""
echo "=========================================="
echo "5. Slow Query Check"
echo "=========================================="

# Check for slow queries (if pg_stat_statements is enabled)
PGPASSWORD=${DB_PASSWORD} psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} -d ${DB_NAME} -c \
    "SELECT
        query,
        calls,
        total_exec_time / calls as avg_time_ms,
        mean_exec_time as mean_time_ms
    FROM pg_stat_statements
    WHERE mean_exec_time > 100
    ORDER BY mean_exec_time DESC
    LIMIT 10;" 2>/dev/null || echo -e "${YELLOW}pg_stat_statements not enabled. Install it with: CREATE EXTENSION pg_stat_statements;${NC}"

echo ""
echo "=========================================="
echo "6. Table Index Usage"
echo "=========================================="

PGPASSWORD=${DB_PASSWORD} psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} -d ${DB_NAME} -c \
    "SELECT
        schemaname,
        tablename,
        indexname,
        idx_scan as index_scans,
        seq_scan as sequential_scans,
        idx_scan / NULLIF(idx_scan + seq_scan, 0) * 100 as index_usage_pct
    FROM pg_stat_user_tables
    WHERE schemaname = 'public'
    ORDER BY seq_scan DESC
    LIMIT 10;"

echo ""
echo "=========================================="
echo "7. Table Sizes"
echo "=========================================="

PGPASSWORD=${DB_PASSWORD} psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} -d ${DB_NAME} -c \
    "SELECT
        tablename,
        pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
        pg_total_relation_size(schemaname||'.'||tablename) AS bytes
    FROM pg_tables
    WHERE schemaname = 'public'
    ORDER BY bytes DESC
    LIMIT 10;"

echo ""
echo -e "${GREEN}Database validation complete!${NC}"
echo "=========================================="