#!/usr/bin/env python3
"""
Clean up existing test data from the database
"""
import mysql.connector
import sys
import json

def load_env_config():
    """Load database configuration from .env file"""
    config = {}
    env_path = '/home/whgoparts/public_html/whims-dev/.env'

    with open(env_path, 'r') as f:
        for line in f:
            line = line.strip()
            if line and not line.startswith('#') and '=' in line:
                key, value = line.split('=', 1)
                config[key] = value.strip('"').strip("'")

    return config

def get_db_connection():
    """Create database connection"""
    config = load_env_config()

    return mysql.connector.connect(
        host=config.get('DB_HOST', 'localhost'),
        port=int(config.get('DB_PORT', '3306')),
        user=config.get('DB_USERNAME', 'root'),
        password=config.get('DB_PASSWORD', ''),
        database=config.get('DB_DATABASE', 'whims_dev')
    )

def cleanup_database():
    """Clean up all transactional data, keeping structure and essential records"""
    conn = get_db_connection()
    cursor = conn.cursor()

    print("🧹 Starting database cleanup...")

    try:
        # Disable foreign key checks temporarily
        cursor.execute("SET FOREIGN_KEY_CHECKS = 0")

        # Tables to truncate (clear all data)
        tables_to_truncate = [
            'stock_history',
            'stock_transfer_items',
            'stock_transfers',
            'stocks',
            'purchase_order_items',
            'purchase_orders',
            'return_import_items',
            'return_imports',
            'product_history',
            'products',
            'locations',
            'report_logs',
            'notifications',
            'failed_jobs',
            'jobs',
            'job_batches'
        ]

        for table in tables_to_truncate:
            cursor.execute(f"TRUNCATE TABLE {table}")
            print(f"  ✅ Cleaned table: {table}")

        # Keep warehouses but ensure they match Finale data
        print("\n📦 Updating warehouses...")

        # Clear existing warehouses
        cursor.execute("DELETE FROM warehouses")

        # Insert warehouses based on Finale data
        warehouses = [
            ('Go-Parts GA', 'GA', 'Georgia'),
            ('SimplePrice', 'SP', 'SimplePrice Location'),
            ('Today Delivery INC', 'TDI', 'Today Delivery INC'),
            ('RapidFulfillment', 'RF', 'RapidFulfillment')
        ]

        for name, code, state in warehouses:
            cursor.execute("""
                INSERT INTO warehouses (name, state, country, is_active, created_at, updated_at)
                VALUES (%s, %s, 'USA', 1, NOW(), NOW())
            """, (name, state))

        print(f"  ✅ Created {len(warehouses)} warehouses")

        # Re-enable foreign key checks
        cursor.execute("SET FOREIGN_KEY_CHECKS = 1")

        conn.commit()
        print("\n✅ Database cleanup completed successfully!")

        # Get counts to verify
        cursor.execute("SELECT COUNT(*) FROM products")
        product_count = cursor.fetchone()[0]

        cursor.execute("SELECT COUNT(*) FROM purchase_orders")
        po_count = cursor.fetchone()[0]

        cursor.execute("SELECT COUNT(*) FROM stocks")
        stock_count = cursor.fetchone()[0]

        print(f"\nCurrent counts:")
        print(f"  Products: {product_count}")
        print(f"  Purchase Orders: {po_count}")
        print(f"  Stock Records: {stock_count}")

    except Exception as e:
        conn.rollback()
        print(f"❌ Error during cleanup: {str(e)}")
        sys.exit(1)

    finally:
        cursor.close()
        conn.close()

if __name__ == "__main__":
    # Check for backup before proceeding
    try:
        with open('/home/whgoparts/public_html/whims-dev/migration-dev/finale-files/last_backup.json', 'r') as f:
            backup_info = json.load(f)
            print(f"📋 Found backup: {backup_info['backup_file']}")
    except FileNotFoundError:
        print("⚠️  Warning: No backup found. Run 01_backup_database.py first!")
        response = input("Continue anyway? (yes/no): ")
        if response.lower() != 'yes':
            sys.exit(0)

    cleanup_database()