#!/usr/bin/env python3
"""
Heavy Database Query Performance Test - Python Version
Tests querying last year's data with complex operations
"""

import time
import json
import pymysql
from datetime import datetime, timedelta

class DatabasePerformanceTest:
    def __init__(self):
        self.config = {
            'host': '66.94.112.114',
            'port': 3306,
            'user': 'root',
            'password': 'mWoAIt#nP&Qcl8z',
            'database': 'lojalemis_sys',
            'charset': 'utf8mb4'
        }
        self.db = None
        self.results = {}

    def connect(self):
        """Establish database connection"""
        try:
            self.db = pymysql.connect(**self.config)
            return True
        except Exception as e:
            print(f"❌ Connection failed: {e}")
            return False

    def run_all_tests(self):
        """Run all performance tests"""
        if not self.connect():
            return False

        print("🚀 STARTING HEAVY DATABASE QUERY PERFORMANCE TEST (Python)")
        print("========================================================")

        try:
            # Test 1: Simple count query
            self.test_simple_count()

            # Test 2: Complex join with last year's data
            self.test_complex_join()

            # Test 3: Aggregation queries
            self.test_aggregation_queries()

            # Test 4: Full table scan simulation
            self.test_full_table_scan()

            # Test 5: Multiple complex operations
            self.test_multiple_operations()

            return True

        except Exception as e:
            print(f"❌ Error during tests: {e}")
            return False
        finally:
            if self.db:
                self.db.close()

    def test_simple_count(self):
        """Test 1: Simple COUNT Query (Last Year Data)"""
        print("\n📊 Test 1: Simple COUNT Query (Last Year Data)")

        start_time = time.time()

        cursor = self.db.cursor()
        cursor.execute("""
            SELECT COUNT(*) as total_records
            FROM orders
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
        """)
        result = cursor.fetchone()

        end_time = time.time()
        execution_time = (end_time - start_time) * 1000

        self.results['simple_count'] = {
            'query': 'COUNT last year orders',
            'records_found': int(result[0]),
            'execution_time_ms': round(execution_time, 2),
            'memory_usage_mb': 0  # Python doesn't track this easily
        }

        print(f"   Records: {result[0]:,}")
        print(".2f")

    def test_complex_join(self):
        """Test 2: Complex JOIN Query (Last Year Data)"""
        print("\n🔗 Test 2: Complex JOIN Query (Last Year Data)")

        start_time = time.time()

        cursor = self.db.cursor()
        cursor.execute("""
            SELECT
                o.id,
                o.order_number,
                o.customer_name,
                o.total_amount,
                o.platform,
                o.status,
                o.created_at,
                COUNT(oi.id) as item_count,
                SUM(oi.total_price) as items_total
            FROM orders o
            LEFT JOIN order_items oi ON o.id = oi.order_id
            WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
            GROUP BY o.id, o.order_number, o.customer_name, o.total_amount, o.platform, o.status, o.created_at
            ORDER BY o.created_at DESC
            LIMIT 1000
        """)
        results = cursor.fetchall()

        end_time = time.time()
        execution_time = (end_time - start_time) * 1000

        self.results['complex_join'] = {
            'query': 'JOIN orders + order_items (last year)',
            'records_found': len(results),
            'execution_time_ms': round(execution_time, 2),
            'memory_usage_mb': 0
        }

        print(f"   Records: {len(results):,}")
        print(".2f")

    def test_aggregation_queries(self):
        """Test 3: Aggregation Queries (Last Year Data)"""
        print("\n📈 Test 3: Aggregation Queries (Last Year Data)")

        start_time = time.time()
        cursor = self.db.cursor()

        # Multiple aggregation queries
        queries = [
            """SELECT platform, COUNT(*) as count, SUM(total_amount) as total
               FROM orders
               WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
               GROUP BY platform""",

            """SELECT DATE_FORMAT(created_at, '%Y-%m') as month, COUNT(*) as orders, SUM(total_amount) as revenue
               FROM orders
               WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
               GROUP BY month
               ORDER BY month""",

            """SELECT
                   COUNT(*) as total_orders,
                   SUM(total_amount) as total_revenue,
                   AVG(total_amount) as avg_order_value,
                   MIN(total_amount) as min_order,
                   MAX(total_amount) as max_order
               FROM orders
               WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)"""
        ]

        total_records = 0
        for query in queries:
            cursor.execute(query)
            results = cursor.fetchall()
            total_records += len(results)

        end_time = time.time()
        execution_time = (end_time - start_time) * 1000

        self.results['aggregation'] = {
            'query': 'Multiple aggregation queries',
            'records_found': total_records,
            'execution_time_ms': round(execution_time, 2),
            'memory_usage_mb': 0
        }

        print(f"   Records: {total_records:,}")
        print(".2f")

    def test_full_table_scan(self):
        """Test 4: Full Table Scan Simulation (Last Year Data)"""
        print("\n🔍 Test 4: Full Table Scan Simulation (Last Year Data)")

        start_time = time.time()

        cursor = self.db.cursor()
        cursor.execute("""
            SELECT * FROM orders
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
            ORDER BY created_at DESC
            LIMIT 5000
        """)
        results = cursor.fetchall()

        end_time = time.time()
        execution_time = (end_time - start_time) * 1000

        self.results['full_scan'] = {
            'query': 'Full table scan (5k records)',
            'records_found': len(results),
            'execution_time_ms': round(execution_time, 2),
            'memory_usage_mb': 0
        }

        print(f"   Records: {len(results):,}")
        print(".2f")

    def test_multiple_operations(self):
        """Test 5: Multiple Complex Operations (Last Year Data)"""
        print("\n⚡ Test 5: Multiple Complex Operations (Last Year Data)")

        start_time = time.time()
        cursor = self.db.cursor()

        # Simulate complex dashboard queries
        operations = [
            # Top products
            """SELECT p.name, COUNT(oi.id) as sales_count, SUM(oi.total_price) as revenue
               FROM products p
               JOIN order_items oi ON p.id = oi.product_id
               JOIN orders o ON oi.order_id = o.id
               WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
               GROUP BY p.id, p.name
               ORDER BY revenue DESC
               LIMIT 20""",

            # Sales by platform
            """SELECT platform, COUNT(*) as orders, SUM(total_amount) as revenue
               FROM orders
               WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
               GROUP BY platform
               ORDER BY revenue DESC""",

            # Customer analysis
            """SELECT customer_name, COUNT(*) as orders, SUM(total_amount) as total_spent
               FROM orders
               WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
               AND customer_name IS NOT NULL
               GROUP BY customer_name
               HAVING orders > 1
               ORDER BY total_spent DESC
               LIMIT 50"""
        ]

        total_records = 0
        for query in operations:
            cursor.execute(query)
            results = cursor.fetchall()
            total_records += len(results)

        end_time = time.time()
        execution_time = (end_time - start_time) * 1000

        self.results['multiple_ops'] = {
            'query': 'Multiple complex operations',
            'records_found': total_records,
            'execution_time_ms': round(execution_time, 2),
            'memory_usage_mb': 0
        }

        print(f"   Records: {total_records:,}")
        print(".2f")

    def print_summary(self):
        """Print performance test summary"""
        print("\n📊 PERFORMANCE TEST SUMMARY (Python)")
        print("=====================================")

        total_time = 0
        total_records = 0

        for test, data in self.results.items():
            print(f"🔹 {test.replace('_', ' ')}:")
            print(f"   Records: {data['records_found']:,}")
            print(".2f")
            print("   Memory: N/A (Python tracking)"            print()

            total_time += data['execution_time_ms']
            total_records += data['records_found']

        print("📈 OVERALL RESULTS:")
        print(f"   Total Records Processed: {total_records:,}")
        print(".2f")
        print(".2f")
        print(".0f")
        print("\n✅ Python Performance Test Completed!")

        # Save results to JSON file for comparison
        with open('python_performance_results.json', 'w') as f:
            json.dump(self.results, f, indent=2)

if __name__ == "__main__":
    test = DatabasePerformanceTest()
    if test.run_all_tests():
        test.print_summary()
    else:
        print("❌ Failed to run performance tests")