#!/bin/bash
# Lab 05 - Database & RDS
# Test 99: Final Verification (Double Check)
# Verifica finale end-to-end per studenti

set -euo pipefail

# Colori per output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m'

# Contatori
pass_count=0
fail_count=0
skip_count=0

# Funzioni helper
inc_pass() { ((pass_count++)) || true; }
inc_fail() { ((fail_count++)) || true; }
inc_skip() { ((skip_count++)) || true; }

echo "=========================================="
echo "Lab 05 - Final Verification (Double Check)"
echo "=========================================="
echo ""
echo "Verifica completa: Lab 05 - Database & RDS"
echo "Parallelo: PostgreSQL in Docker ↔ RDS in AWS VPC"
echo ""

# Verifica file docker-compose.yml
echo -n "[CHECK] Verifica docker-compose.yml esista... "
if [ -f "docker-compose.yml" ]; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
    echo "ERRORE: docker-compose.yml non trovato"
    exit 1
fi

echo ""
echo "=== VERIFICA CONFIGURAZIONE ==="

# Verifica servizio database
echo -n "[CHECK] Servizio 'database' definito... "
if grep -q "database:" docker-compose.yml; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

# Verifica immagine PostgreSQL
echo -n "[CHECK] Immagine PostgreSQL... "
if grep -q "image: postgres" docker-compose.yml; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

# Verifica credenziali
echo -n "[CHECK] Credenziali PostgreSQL configurate... "
if grep -q "POSTGRES_DB:" docker-compose.yml && \
   grep -q "POSTGRES_USER:" docker-compose.yml && \
   grep -q "POSTGRES_PASSWORD:" docker-compose.yml; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

# Verifica volume
echo -n "[CHECK] Volume 'db-data' configurato... "
if grep -q "db-data:" docker-compose.yml; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

# Verifica rete privata
echo -n "[CHECK] Database in rete 'vpc-private'... "
if grep -A 20 "database:" docker-compose.yml | grep -q "vpc-private"; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

# Verifica nessuna porta esposta
echo -n "[CHECK] NESSUNA porta esposta (INF-02)... "
if grep -A 30 "database:" docker-compose.yml | grep -q "ports:"; then
    echo -e "${YELLOW}WARN${NC} (porte configurate - RDS non espone porte)"
    inc_skip
else
    echo -e "${GREEN}OK${NC}"
    inc_pass
fi

# Verifica limiti risorse
echo -n "[CHECK] Limiti risorse configurati (INF-03)... "
if grep -A 30 "database:" docker-compose.yml | grep -q "cpus:" && \
   grep -A 30 "database:" docker-compose.yml | grep -q "memory:"; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

echo ""
echo "=== VERIFICA ESECUZIONE ==="

# Verifica container in esecuzione
echo -n "[CHECK] Container 'lab05-db' in esecuzione... "
if docker ps --format '{{{{Names}}}}' | grep -q "^lab05-db$"; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
    echo "Avviare i container: docker-compose up -d"
    echo ""
    echo "Risultato: $pass_count PASS, $fail_count FAIL, $skip_count SKIP"
    exit 1
fi

# Verifica healthcheck
echo -n "[CHECK] Healthcheck configurato... "
health_status=$(docker inspect lab05-db --format '{{.State.Health.Status}}' 2>/dev/null || echo "unknown")
if [ "$health_status" != "unknown" ]; then
    echo -e "${GREEN}OK${NC} ($health_status)"
    inc_pass
else
    echo -e "${YELLOW}WARN${NC} (nessun healthcheck)"
    inc_skip
fi

# Verifica pg_isready
echo -n "[CHECK] PostgreSQL pronto (pg_isready)... "
if docker exec lab05-db pg_isready &>/dev/null; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${YELLOW}WARN${NC} (PostgreSQL non ancora pronto)"
    inc_skip
fi

echo ""
echo "=== VERIFICA SICUREZZA ==="

# INF-01: Non-root
echo -n "[CHECK] Container NON gira come root (INF-01)... "
container_user=$(docker exec lab05-db whoami 2>/dev/null || echo "unknown")
if [ "$container_user" = "postgres" ]; then
    echo -e "${GREEN}OK${NC} ($container_user)"
    inc_pass
else
    echo -e "${RED}FAIL${NC} ($container_user)"
    inc_fail
fi

# INF-02: No host ports
echo -n "[CHECK] NESSUNA porta su host (INF-02)... "
db_port=$(docker port lab05-db 5432 2>/dev/null || echo "")
if [ -z "$db_port" ]; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC} (porta $db_port)"
    inc_fail
fi

# INF-03: Resource limits
echo -n "[CHECK] Limiti risorsa applicati (INF-03)... "
if docker inspect lab05-db --format '{{.HostConfig.Memory}}' | grep -q "[1-9]"; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

# INF-04: Volume persistence
echo -n "[CHECK] Volume persistenza (INF-04)... "
if docker volume ls --format '{{{{.Name}}}}' | grep -q "^lab05_db-data$"; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

echo ""
echo "=== VERIFICA FUNZIONALITÀ ==="

# Test connessione database
echo -n "[CHECK] Connessione database funziona... "
if docker exec lab05-db psql -U lab05_user -d lab05_db -c "SELECT 1;" &>/dev/null; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

# Test creazione tabella
echo -n "[CHECK] Creazione tabella... "
if docker exec lab05-db psql -U lab05_user -d lab05_db -c "CREATE TABLE IF NOT EXISTS verify_test (id SERIAL);" &>/dev/null; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

# Test inserimento dati
echo -n "[CHECK] Inserimento dati... "
if docker exec lab05-db psql -U lab05_user -d lab05_db -c "INSERT INTO verify_test DEFAULT VALUES;" &>/dev/null; then
    echo -e "${GREEN}OK${NC}"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

# Test query dati
echo -n "[CHECK] Query dati... "
count=$(docker exec lab05-db psql -U lab05_user -d lab05_db -t -c "SELECT COUNT(*) FROM verify_test;" 2>/dev/null | tr -d ' ')
if [ -n "$count" ] && [ "$count" -gt 0 ]; then
    echo -e "${GREEN}OK${NC} ($count righe)"
    inc_pass
else
    echo -e "${RED}FAIL${NC}"
    inc_fail
fi

echo ""
echo "=========================================="
echo "RISULTATO FINALE:"
echo "  $pass_count PASS"
echo "  $fail_count FAIL"
echo "  $skip_count SKIP"
echo "=========================================="

if [ $fail_count -eq 0 ]; then
    echo ""
    echo -e "${GREEN}✓ LAB 05 COMPLETATO CON SUCCESSO${NC}"
    echo ""
    echo "Paralleli confermati:"
    echo "  PostgreSQL container → RDS Instance"
    echo "  Private network → VPC Private Subnet"
    echo "  Named volume → EBS Volume"
    echo "  Resource limits → DB Instance Class"
    echo ""
    exit 0
else
    echo ""
    echo -e "${RED}✗ LAB 05 HA ERRORI - RISOLVERE E RIPETERE${NC}"
    echo ""
    exit 1
fi