#!/bin/bash

# Script to drop, recreate, and restore bafdb database
# Output will be logged to restore_bafdb.log

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

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

# Database connection details
export PGPASSWORD='TAF123*'
DB_USER="taf"
DB_NAME="bafdb"
BACKUP_FILE="devdb_backup_cleaned.sql"

# Check if backup file exists
if [ ! -f "$BACKUP_FILE" ]; then
    echo "ERROR: Backup file $BACKUP_FILE not found!" | tee -a "$LOGFILE"
    exit 1
fi

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

# Step 1: Drop existing database
echo "Step 1: Dropping existing database $DB_NAME (if exists)..." | tee -a "$LOGFILE"
psql -h localhost -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 2: Create new database
echo "Step 2: Creating database $DB_NAME..." | tee -a "$LOGFILE"
psql -h localhost -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 3: Import backup
echo "Step 3: Importing backup file into $DB_NAME..." | tee -a "$LOGFILE"
echo "This may take several minutes..." | tee -a "$LOGFILE"
psql -h localhost -U "$DB_USER" -d "$DB_NAME" -f "$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 4: Verify tables
echo "Step 4: Verifying tables in database..." | tee -a "$LOGFILE"
psql -h localhost -U "$DB_USER" -d "$DB_NAME" -c "\dt" 2>&1 | tee -a "$LOGFILE"
echo "" | tee -a "$LOGFILE"

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

# Step 6: Count records in key tables
echo "Step 6: Counting records in key tables..." | tee -a "$LOGFILE"
psql -h localhost -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 WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name='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"
echo "=============================================" | tee -a "$LOGFILE"
echo "Restore process completed at $(date)" | tee -a "$LOGFILE"
echo "Full log saved to: $LOGFILE" | tee -a "$LOGFILE"

# Unset password
unset PGPASSWORD