#!/bin/bash
# Comprehensive Data Import Script
# Imports all MySQL-compatible data files from the database_dump directory

set -e

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

echo "🚀 Comprehensive Database Data Import"
echo "=================================================="

SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
MYSQL_DUMP_DIR="$SCRIPT_DIR/database_dump/mysql"
ROOT_DUMP_DIR="$SCRIPT_DIR/database_dump"

# 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 INSERT IGNORE for duplicates
import_sql_file() {
    local file=$1
    local description=$2
    
    if [ ! -f "$file" ]; then
        echo "⚠️  File not found: $file"
        return 1
    fi
    
    echo "📥 Importing $description..."
    # Use INSERT IGNORE to handle duplicates gracefully
    sed 's/^INSERT INTO/INSERT IGNORE INTO/g' "$file" | $MYSQL_CMD 2>&1 | grep -v "Warning: Using a password" || true
    echo "   ✅ Completed: $description"
    return 0
}

# Step 1: Import all MySQL data files from mysql/ directory
echo ""
echo "📋 Step 1: Importing MySQL Data Files"
echo "=================================================="

# Import all data_mysql_*.sql files
for file in "$MYSQL_DUMP_DIR"/data_mysql_*.sql; do
    if [ -f "$file" ]; then
        filename=$(basename "$file")
        table_name=$(echo "$filename" | sed 's/data_mysql_\(.*\)_SAMPLE.*/\1/' | tr '_' ' ')
        import_sql_file "$file" "$table_name"
    fi
done

# Step 2: Check if root directory has MySQL-compatible files (they're MSSQL format, skip them)
echo ""
echo "📋 Step 2: Checking Root Directory Files"
echo "=================================================="
echo "⚠️  Root directory contains MSSQL format files (LRSAdmin. schema, [column] syntax)"
echo "   These are NOT compatible with MySQL. Using MySQL versions from mysql/ directory instead."

# Step 3: Verify all data is imported
echo ""
echo "📊 Step 3: Verifying Data Import"
echo "=================================================="

$MYSQL_CMD <<EOF 2>/dev/null | grep -v "Warning: Using a password"
SELECT 
    'lr_party' as table_name, COUNT(*) as row_count FROM lr_party
UNION ALL SELECT 'lr_source_document', COUNT(*) FROM lr_source_document
UNION ALL SELECT 'lr_transaction', COUNT(*) FROM lr_transaction
UNION ALL SELECT 'lr_transaction_document', COUNT(*) FROM lr_transaction_document
UNION ALL SELECT 'lr_dictionary', COUNT(*) FROM lr_dictionary
UNION ALL SELECT 'alf_content', COUNT(*) FROM alf_content
UNION ALL SELECT 'alf_content_url', COUNT(*) FROM alf_content_url
UNION ALL SELECT 'alf_node', COUNT(*) FROM alf_node
UNION ALL SELECT 'alf_node_properties', COUNT(*) FROM alf_node_properties
UNION ALL SELECT 'alf_qname', COUNT(*) FROM alf_qname
ORDER BY table_name;
EOF

echo ""
echo "✅ Data import verification complete!"
echo "=================================================="
echo ""
echo "📝 Note: Root directory files (data_*.sql) are MSSQL format."
echo "   MySQL-compatible versions are in database_dump/mysql/ directory"
echo "   and have been imported above."
echo ""