#!/bin/bash
# Populate MySQL Database with Sample Data for Presentations
# This script imports all sample data from SQL dumps

set -e

DB_USER="root"
DB_PASS="5bad89a3"
DB_NAME="LRS43"
MYSQL_CMD="mysql -u $DB_USER -p$DB_PASS $DB_NAME"

echo "🚀 Starting database population for presentations..."
echo "=================================================="

# Get the script directory
SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
DUMP_DIR="$SCRIPT_DIR/database_dump/mysql"

# Check if database exists
echo "📊 Checking database..."
if ! mysql -u $DB_USER -p$DB_PASS -e "USE $DB_NAME" 2>/dev/null; then
    echo "❌ Database $DB_NAME does not exist. Please create it first."
    exit 1
fi

# Function to import SQL file with error handling
import_sql_file() {
    local file=$1
    local description=$2
    
    if [ ! -f "$file" ]; then
        echo "⚠️  File not found: $file"
        return 1
    fi
    
    echo "📥 Importing $description..."
    if $MYSQL_CMD < "$file" 2>&1 | grep -v "Warning: Using a password"; then
        echo "   ✅ Successfully imported $description"
        return 0
    else
        echo "   ⚠️  Import completed (warnings may be present)"
        return 0
    fi
}

# 1. Import boundary commission schema (if not already imported)
echo ""
echo "📋 Step 1: Boundary Commission Schema"
if [ -f "$DUMP_DIR/boundary_commission_schema.sql" ]; then
    import_sql_file "$DUMP_DIR/boundary_commission_schema.sql" "Boundary Commission Schema"
fi

# 2. Import boundary documents schema (if not already imported)
echo ""
echo "📋 Step 2: Boundary Documents Schema"
if [ -f "$DUMP_DIR/boundary_documents_schema.sql" ]; then
    import_sql_file "$DUMP_DIR/boundary_documents_schema.sql" "Boundary Documents Schema"
fi

# 3. Import boundary sample data (with INSERT IGNORE to avoid duplicates)
echo ""
echo "📋 Step 3: Boundary Sample Data"
if [ -f "$DUMP_DIR/insert_boundary_sample_data.sql" ]; then
    # Use INSERT IGNORE to avoid duplicate key errors
    echo "📥 Importing Boundary Sample Data..."
    sed 's/^INSERT INTO/INSERT IGNORE INTO/g' "$DUMP_DIR/insert_boundary_sample_data.sql" | $MYSQL_CMD 2>&1 | grep -v "Warning: Using a password" || true
    echo "   ✅ Boundary sample data imported"
fi

# 4. Import all MySQL sample data files
echo ""
echo "📋 Step 4: Core Application Data"
for file in "$DUMP_DIR"/data_mysql_*.sql; do
    if [ -f "$file" ]; then
        filename=$(basename "$file")
        # Skip if already imported (check by table name in filename)
        table_name=$(echo "$filename" | sed 's/data_mysql_\(.*\)_SAMPLE.*/\1/' | tr '_' ' ')
        import_sql_file "$file" "$table_name data"
    fi
done

# 5. Verify data counts
echo ""
echo "📊 Step 5: Verifying Data Population"
echo "=================================================="

$MYSQL_CMD <<EOF 2>/dev/null | grep -v "Warning: Using a password"
SELECT 
    'boundary' as table_name, COUNT(*) as row_count FROM boundary
UNION ALL SELECT 'boundary_dispute', COUNT(*) FROM boundary_dispute
UNION ALL SELECT 'boundary_document', COUNT(*) FROM boundary_document
UNION ALL SELECT 'lr_dictionary', COUNT(*) FROM lr_dictionary
UNION ALL SELECT 'lr_transaction', COUNT(*) FROM lr_transaction
UNION ALL SELECT 'lr_source_document', COUNT(*) FROM lr_source_document
UNION ALL SELECT 'lr_party', COUNT(*) FROM lr_party
UNION ALL SELECT 'lr_transaction_document', COUNT(*) FROM lr_transaction_document
UNION ALL SELECT 'boundary_coordinate', COUNT(*) FROM boundary_coordinate
UNION ALL SELECT 'boundary_marker', COUNT(*) FROM boundary_marker
UNION ALL SELECT 'boundary_survey', COUNT(*) FROM boundary_survey
UNION ALL SELECT 'boundary_treaty', COUNT(*) FROM boundary_treaty
UNION ALL SELECT 'stakeholder', COUNT(*) FROM stakeholder
UNION ALL SELECT 'dispute_evidence', COUNT(*) FROM dispute_evidence
ORDER BY table_name;
EOF

echo ""
echo "✅ Database population complete!"
echo "=================================================="
echo ""
echo "📝 Summary:"
echo "   - Boundary data: Ready for presentations"
echo "   - Dictionary data: Populated for dropdowns"
echo "   - Transaction data: Available for searches"
echo "   - Document data: Ready for document management"
echo ""
echo "🎯 Your application is now ready with sample data!"