#!/bin/bash

# Script to restore database locally using Docker
# This will start Docker containers and restore the cleaned backup

set -e  # Exit on error

LOGFILE="restore_local_$(date +%Y%m%d_%H%M%S).log"

echo "Starting local database restore process at $(date)" | tee -a "$LOGFILE"
echo "=============================================" | tee -a "$LOGFILE"

# Database connection details
DB_USER="taf"
DB_NAME="bafdb"
BACKUP_FILE="devdb_backup_cleaned.sql"
CONTAINER_NAME="postgres"

# Check if backup file exists
if [ ! -f "$BACKUP_FILE" ]; then
    echo "ERROR: Backup file $BACKUP_FILE not found!" | tee -a "$LOGFILE"
    echo "Run ./fix_timeclocks_data.sh first to create the cleaned backup" | tee -a "$LOGFILE"
    exit 1
fi

echo "Backup file found: $BACKUP_FILE" | tee -a "$LOGFILE"
echo "" | tee -a "$LOGFILE"

# Step 1: Start Docker containers
echo "Step 1: Starting Docker containers..." | tee -a "$LOGFILE"
docker compose up -d postgres 2>&1 | tee -a "$LOGFILE"

echo "Waiting for PostgreSQL to be ready..." | tee -a "$LOGFILE"
sleep 5

# Wait for PostgreSQL to be ready
for i in {1..30}; do
    if docker exec "$CONTAINER_NAME" pg_isready -U "$DB_USER" > /dev/null 2>&1; then
        echo "✓ PostgreSQL is ready" | tee -a "$LOGFILE"
        break
    fi
    if [ $i -eq 30 ]; then
        echo "✗ PostgreSQL failed to start" | tee -a "$LOGFILE"
        exit 1
    fi
    echo "Waiting... ($i/30)" | tee -a "$LOGFILE"
    sleep 2
done
echo "" | tee -a "$LOGFILE"

# Step 2: Drop existing database
echo "Step 2: Dropping existing database $DB_NAME (if exists)..." | tee -a "$LOGFILE"
docker exec "$CONTAINER_NAME" psql -U "$DB_USER" -d postgres -c "DROP DATABASE IF EXISTS $DB_NAME;" 2>&1 | tee -a "$LOGFILE"

if [ ${PIPESTATUS[0]} -eq 0 ]; then
    echo "✓ Database dropped successfully" | tee -a "$LOGFILE"
else
    echo "✗ Failed to drop database" | tee -a "$LOGFILE"
fi
echo "" | tee -a "$LOGFILE"

# Step 3: Create new database
echo "Step 3: Creating database $DB_NAME..." | tee -a "$LOGFILE"
docker exec "$CONTAINER_NAME" psql -U "$DB_USER" -d postgres -c "CREATE DATABASE $DB_NAME OWNER $DB_USER;" 2>&1 | tee -a "$LOGFILE"

if [ ${PIPESTATUS[0]} -eq 0 ]; then
    echo "✓ Database created successfully" | tee -a "$LOGFILE"
else
    echo "✗ Failed to create database" | tee -a "$LOGFILE"
    exit 1
fi
echo "" | tee -a "$LOGFILE"

# Step 4: Copy backup file into container
echo "Step 4: Copying backup file into container..." | tee -a "$LOGFILE"
docker cp "$BACKUP_FILE" "$CONTAINER_NAME:/tmp/$BACKUP_FILE" 2>&1 | tee -a "$LOGFILE"

if [ ${PIPESTATUS[0]} -eq 0 ]; then
    echo "✓ Backup file copied to container" | tee -a "$LOGFILE"
else
    echo "✗ Failed to copy backup file" | tee -a "$LOGFILE"
    exit 1
fi
echo "" | tee -a "$LOGFILE"

# Step 5: Import backup
echo "Step 5: Importing backup file into $DB_NAME..." | tee -a "$LOGFILE"
echo "This may take several minutes..." | tee -a "$LOGFILE"
docker exec "$CONTAINER_NAME" psql -U "$DB_USER" -d "$DB_NAME" -f "/tmp/$BACKUP_FILE" 2>&1 | tee -a "$LOGFILE"

if [ ${PIPESTATUS[0]} -eq 0 ]; then
    echo "✓ Backup imported successfully" | tee -a "$LOGFILE"
else
    echo "✗ Failed to import backup (check log for errors)" | tee -a "$LOGFILE"
fi
echo "" | tee -a "$LOGFILE"

# Step 6: Verify tables
echo "Step 6: Verifying tables in database..." | tee -a "$LOGFILE"
docker exec "$CONTAINER_NAME" psql -U "$DB_USER" -d "$DB_NAME" -c "\dt" 2>&1 | tee -a "$LOGFILE"
echo "" | tee -a "$LOGFILE"

# Step 7: Check time_clocks table specifically
echo "Step 7: Checking time_clocks table..." | tee -a "$LOGFILE"
docker exec "$CONTAINER_NAME" psql -U "$DB_USER" -d "$DB_NAME" -c "\d time_clocks" 2>&1 | tee -a "$LOGFILE"
echo "" | tee -a "$LOGFILE"

# Step 8: Count records in key tables
echo "Step 8: Counting records in key tables..." | tee -a "$LOGFILE"
docker exec "$CONTAINER_NAME" psql -U "$DB_USER" -d "$DB_NAME" -c "
SELECT 
    'users' as table_name, COUNT(*) as record_count FROM users
UNION ALL
SELECT 'orgs', COUNT(*) FROM orgs
UNION ALL
SELECT 'time_clocks', COUNT(*) FROM time_clocks
UNION ALL
SELECT 'modules', COUNT(*) FROM modules
UNION ALL
SELECT 'groups', COUNT(*) FROM groups;
" 2>&1 | tee -a "$LOGFILE"

echo "" | tee -a "$LOGFILE"

# Step 9: Update API config to use bafdb
echo "Step 9: Checking API database configuration..." | tee -a "$LOGFILE"
if grep -q "DB_NAME.*tafdb" api/config.php 2>/dev/null; then
    echo "⚠ WARNING: api/config.php is configured for 'tafdb' database" | tee -a "$LOGFILE"
    echo "You may need to update it to use 'bafdb' for testing" | tee -a "$LOGFILE"
else
    echo "✓ API config looks OK" | tee -a "$LOGFILE"
fi
echo "" | tee -a "$LOGFILE"

# Step 10: Start other services
echo "Step 10: Starting other Docker services..." | tee -a "$LOGFILE"
docker compose up -d 2>&1 | tee -a "$LOGFILE"
echo "" | tee -a "$LOGFILE"

echo "=============================================" | tee -a "$LOGFILE"
echo "Restore process completed at $(date)" | tee -a "$LOGFILE"
echo "" | tee -a "$LOGFILE"
echo "Database: $DB_NAME" | tee -a "$LOGFILE"
echo "Access: http://localhost:8080" | tee -a "$LOGFILE"
echo "Full log saved to: $LOGFILE" | tee -a "$LOGFILE"
echo "" | tee -a "$LOGFILE"
echo "To test the Kotlin app locally, update the baseUrl in AttendanceRepository.kt to:" | tee -a "$LOGFILE"
echo "  http://YOUR_LOCAL_IP:8080/api/" | tee -a "$LOGFILE"