#!/usr/bin/env python3
"""
Database Synchronization Manager

A Python interface for managing MySQL database synchronization between
remote production and local development environments.

Features:
- Interactive menu system
- Progress tracking with real-time updates
- Automatic backup management
- Safety checks and confirmations
- Detailed logging
- Table selection and filtering
"""

import os
import sys
import subprocess
import time
import datetime
import json
from pathlib import Path

class DatabaseSyncManager:
    def __init__(self):
        self.script_dir = Path("/var/www/html/aei_site/DATABASE_SYNC")
        self.backups_dir = self.script_dir / "backups"
        self.logs_dir = self.script_dir / "logs"
        
        # Ensure directories exist
        self.backups_dir.mkdir(exist_ok=True)
        self.logs_dir.mkdir(exist_ok=True)
        
        # Script paths
        self.structure_script = self.script_dir / "database_structure_sync.sh"
        self.full_script = self.script_dir / "database_full_sync.sh"
        
        # Colors for output
        self.colors = {
            'red': '\033[91m',
            'green': '\033[92m',
            'yellow': '\033[93m',
            'blue': '\033[94m',
            'purple': '\033[95m',
            'cyan': '\033[96m',
            'white': '\033[97m',
            'bold': '\033[1m',
            'end': '\033[0m'
        }

    def print_colored(self, text, color='white', bold=False):
        """Print colored text to console"""
        color_code = self.colors.get(color, self.colors['white'])
        if bold:
            color_code = self.colors['bold'] + color_code
        print(f"{color_code}{text}{self.colors['end']}")

    def print_header(self, title):
        """Print formatted header"""
        print("\n" + "=" * 60)
        self.print_colored(f" {title}", 'cyan', bold=True)
        print("=" * 60)

    def print_menu(self):
        """Display main menu"""
        self.print_header("Database Synchronization Manager")
        print("\n🔄 Sync Operations:")
        print("  1. Structure Sync Only (Safe - No Data Changes)")
        print("  2. Data Sync Only (⚠️  Overwrites Local Data)")
        print("  3. Full Sync (⚠️  Complete Structure + Data)")
        print("  4. Custom Sync (Select Tables/Options)")
        
        print("\n📊 Management:")
        print("  5. Compare Database Status")
        print("  6. Create Manual Backup")
        print("  7. List Available Backups")
        print("  8. Restore from Backup")
        
        print("\n📋 Information:")
        print("  9. View Sync History")
        print(" 10. Check System Status")
        print(" 11. Help & Documentation")
        
        print("\n 0. Exit")
        print("-" * 60)

    def get_user_input(self, prompt, valid_options=None):
        """Get validated user input"""
        while True:
            try:
                response = input(f"{prompt}: ").strip()
                if valid_options and response not in valid_options:
                    self.print_colored(f"Invalid option. Choose from: {', '.join(valid_options)}", 'red')
                    continue
                return response
            except KeyboardInterrupt:
                self.print_colored("\n\nOperation cancelled by user.", 'yellow')
                return None

    def confirm_action(self, message, default='n'):
        """Get user confirmation for dangerous operations"""
        self.print_colored(f"\n⚠️  {message}", 'yellow', bold=True)
        response = self.get_user_input(f"Continue? (y/N)", ['y', 'n', 'yes', 'no', ''])
        if response is None:
            return False
        return response.lower() in ['y', 'yes']

    def run_command(self, command, show_output=True):
        """Execute shell command with progress tracking"""
        try:
            if show_output:
                self.print_colored(f"Executing: {' '.join(command)}", 'blue')
            
            process = subprocess.Popen(
                command,
                stdout=subprocess.PIPE,
                stderr=subprocess.STDOUT,
                universal_newlines=True,
                cwd=self.script_dir
            )
            
            output_lines = []
            while True:
                output = process.stdout.readline()
                if output == '' and process.poll() is not None:
                    break
                if output and show_output:
                    print(output.strip())
                    output_lines.append(output.strip())
            
            return_code = process.poll()
            return return_code == 0, '\n'.join(output_lines)
            
        except Exception as e:
            self.print_colored(f"Error executing command: {e}", 'red')
            return False, str(e)

    def structure_sync(self):
        """Run structure-only synchronization"""
        self.print_header("Structure Synchronization")
        self.print_colored("This operation is SAFE - only creates missing table structures", 'green')
        
        if not self.confirm_action("Run structure synchronization?", 'y'):
            return
        
        success, output = self.run_command([str(self.structure_script)])
        
        if success:
            self.print_colored("\n✅ Structure synchronization completed successfully!", 'green', bold=True)
        else:
            self.print_colored("\n❌ Structure synchronization failed!", 'red', bold=True)
        
        input("\nPress Enter to continue...")

    def data_sync(self):
        """Run data-only synchronization"""
        self.print_header("Data Synchronization")
        self.print_colored("⚠️  WARNING: This will OVERWRITE local database data!", 'red', bold=True)
        
        if not self.confirm_action("This operation will overwrite local data. Continue?"):
            return
        
        # Create backup first
        if not self.create_backup():
            self.print_colored("Backup failed - aborting data sync", 'red')
            return
        
        success, output = self.run_command([str(self.full_script), "--data-only"])
        
        if success:
            self.print_colored("\n✅ Data synchronization completed successfully!", 'green', bold=True)
        else:
            self.print_colored("\n❌ Data synchronization failed!", 'red', bold=True)
        
        input("\nPress Enter to continue...")

    def full_sync(self):
        """Run full synchronization (structure + data)"""
        self.print_header("Full Synchronization")
        self.print_colored("⚠️  DANGER: This will DROP and RECREATE tables with data!", 'red', bold=True)
        
        if not self.confirm_action("This operation will completely replace local database. Continue?"):
            return
        
        # Double confirmation for full sync
        if not self.confirm_action("Are you ABSOLUTELY SURE? This cannot be undone!"):
            return
        
        # Create backup first
        if not self.create_backup():
            self.print_colored("Backup failed - aborting full sync", 'red')
            return
        
        success, output = self.run_command([str(self.full_script), "--full"])
        
        if success:
            self.print_colored("\n✅ Full synchronization completed successfully!", 'green', bold=True)
        else:
            self.print_colored("\n❌ Full synchronization failed!", 'red', bold=True)
        
        input("\nPress Enter to continue...")

    def custom_sync(self):
        """Custom synchronization with user-selected options"""
        self.print_header("Custom Synchronization")
        
        print("Sync Mode:")
        print("1. Structure Only")
        print("2. Data Only")
        print("3. Full Sync")
        
        mode = self.get_user_input("Select mode (1-3)", ['1', '2', '3'])
        if mode is None:
            return
        
        mode_args = {
            '1': '--structure-only',
            '2': '--data-only', 
            '3': '--full'
        }
        
        command = [str(self.full_script), mode_args[mode]]
        
        # Table selection
        table_option = self.get_user_input("Specify tables? (s)pecific tables, (e)xclude tables, (a)ll tables", ['s', 'e', 'a'])
        if table_option is None:
            return
        
        if table_option == 's':
            tables = input("Enter table names (comma-separated): ").strip()
            if tables:
                command.extend(['--tables', tables])
        elif table_option == 'e':
            tables = input("Enter tables to exclude (comma-separated): ").strip()
            if tables:
                command.extend(['--exclude', tables])
        
        # Additional options
        if self.get_user_input("Dry run only? (y/n)", ['y', 'n']) == 'y':
            command.append('--dry-run')
        
        if mode in ['2', '3']:  # Data operations
            if not self.confirm_action(f"This will modify local data. Continue?"):
                return
            if not self.create_backup():
                self.print_colored("Backup failed - aborting sync", 'red')
                return
        
        success, output = self.run_command(command)
        
        if success:
            self.print_colored("\n✅ Custom synchronization completed!", 'green', bold=True)
        else:
            self.print_colored("\n❌ Custom synchronization failed!", 'red', bold=True)
        
        input("\nPress Enter to continue...")

    def compare_databases(self):
        """Compare remote and local database status"""
        self.print_header("Database Status Comparison")
        
        self.print_colored("Analyzing database status...", 'blue')
        
        # This would ideally run a comparison query
        # For now, we'll run the structure sync to see what's missing
        success, output = self.run_command([str(self.structure_script)], show_output=False)
        
        if "All missing tables synchronized successfully" in output:
            self.print_colored("✅ Databases are in sync - no missing tables", 'green')
        elif "missing tables to sync" in output:
            lines = output.split('\n')
            for line in lines:
                if "missing tables to sync" in line:
                    self.print_colored(f"⚠️  {line}", 'yellow')
                    break
        else:
            self.print_colored("Status check completed - see details above", 'blue')
        
        input("\nPress Enter to continue...")

    def create_backup(self):
        """Create manual backup of local database"""
        timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
        backup_file = self.backups_dir / f"manual_backup_{timestamp}.sql"
        
        self.print_colored(f"Creating backup: {backup_file.name}", 'blue')
        
        command = [
            "mysqldump",
            "--socket=/var/run/mysqld/mysqld.sock",
            "mandhdesign_schedular"
        ]
        
        try:
            with open(backup_file, 'w') as f:
                process = subprocess.run(command, stdout=f, stderr=subprocess.PIPE, text=True)
            
            if process.returncode == 0:
                self.print_colored(f"✅ Backup created: {backup_file.name}", 'green')
                return True
            else:
                self.print_colored(f"❌ Backup failed: {process.stderr}", 'red')
                return False
        except Exception as e:
            self.print_colored(f"❌ Backup error: {e}", 'red')
            return False

    def list_backups(self):
        """List available backup files"""
        self.print_header("Available Backups")
        
        backup_files = list(self.backups_dir.glob("*.sql"))
        backup_files.sort(key=lambda x: x.stat().st_mtime, reverse=True)
        
        if not backup_files:
            self.print_colored("No backup files found", 'yellow')
        else:
            print(f"{'#':<3} {'Filename':<35} {'Size':<10} {'Date'}")
            print("-" * 60)
            
            for i, backup in enumerate(backup_files[:10], 1):
                stat = backup.stat()
                size = f"{stat.st_size / (1024*1024):.1f}MB"
                date = datetime.datetime.fromtimestamp(stat.st_mtime).strftime("%Y-%m-%d %H:%M")
                print(f"{i:<3} {backup.name:<35} {size:<10} {date}")
        
        input("\nPress Enter to continue...")

    def restore_backup(self):
        """Restore from backup file"""
        self.print_header("Restore from Backup")
        
        backup_files = list(self.backups_dir.glob("*.sql"))
        backup_files.sort(key=lambda x: x.stat().st_mtime, reverse=True)
        
        if not backup_files:
            self.print_colored("No backup files found", 'red')
            input("\nPress Enter to continue...")
            return
        
        print("Available backups:")
        for i, backup in enumerate(backup_files[:10], 1):
            stat = backup.stat()
            date = datetime.datetime.fromtimestamp(stat.st_mtime).strftime("%Y-%m-%d %H:%M")
            print(f"{i}. {backup.name} ({date})")
        
        choice = self.get_user_input(f"Select backup (1-{min(10, len(backup_files))})")
        if choice is None:
            return
        
        try:
            backup_index = int(choice) - 1
            selected_backup = backup_files[backup_index]
        except (ValueError, IndexError):
            self.print_colored("Invalid selection", 'red')
            return
        
        if not self.confirm_action(f"Restore from {selected_backup.name}? This will overwrite current data!"):
            return
        
        # Create backup before restore
        if not self.create_backup():
            self.print_colored("Could not create safety backup - aborting restore", 'red')
            return
        
        command = [
            "mysql",
            "--socket=/var/run/mysqld/mysqld.sock",
            "mandhdesign_schedular"
        ]
        
        try:
            with open(selected_backup, 'r') as f:
                process = subprocess.run(command, stdin=f, stderr=subprocess.PIPE, text=True)
            
            if process.returncode == 0:
                self.print_colored(f"✅ Database restored from {selected_backup.name}", 'green', bold=True)
            else:
                self.print_colored(f"❌ Restore failed: {process.stderr}", 'red')
        except Exception as e:
            self.print_colored(f"❌ Restore error: {e}", 'red')
        
        input("\nPress Enter to continue...")

    def view_sync_history(self):
        """View synchronization history logs"""
        self.print_header("Sync History")
        
        log_files = list(self.logs_dir.glob("sync_*.log"))
        if not log_files:
            log_files = list(self.script_dir.glob("sync_*.log"))
        
        if not log_files:
            self.print_colored("No sync log files found", 'yellow')
        else:
            log_files.sort(key=lambda x: x.stat().st_mtime, reverse=True)
            latest_log = log_files[0]
            
            self.print_colored(f"Latest sync log: {latest_log.name}", 'blue')
            print("-" * 60)
            
            try:
                with open(latest_log, 'r') as f:
                    lines = f.readlines()
                    # Show last 20 lines
                    for line in lines[-20:]:
                        print(line.rstrip())
            except Exception as e:
                self.print_colored(f"Error reading log: {e}", 'red')
        
        input("\nPress Enter to continue...")

    def check_system_status(self):
        """Check system requirements and status"""
        self.print_header("System Status Check")
        
        # Check if scripts exist
        status_items = [
            ("Structure Sync Script", self.structure_script.exists()),
            ("Full Sync Script", self.full_script.exists()),
            ("Backups Directory", self.backups_dir.exists()),
            ("Scripts Executable", os.access(self.structure_script, os.X_OK)),
        ]
        
        # Check MySQL connectivity
        try:
            result = subprocess.run(
                ["mysql", "--socket=/var/run/mysqld/mysqld.sock", "-e", "SELECT 1;"],
                capture_output=True, text=True
            )
            mysql_local = result.returncode == 0
        except:
            mysql_local = False
        
        status_items.append(("Local MySQL Connection", mysql_local))
        
        # Check remote connectivity
        try:
            result = subprocess.run(
                ["mysql", "-h18.225.0.90", "-P3306", "-uAEI_User", "-pP@55w02d7777", "-e", "SELECT 1;"],
                capture_output=True, text=True, timeout=10
            )
            mysql_remote = result.returncode == 0
        except:
            mysql_remote = False
        
        status_items.append(("Remote MySQL Connection", mysql_remote))
        
        # Display status
        for item, status in status_items:
            color = 'green' if status else 'red'
            symbol = '✅' if status else '❌'
            self.print_colored(f"{symbol} {item:<25}: {'OK' if status else 'FAILED'}", color)
        
        input("\nPress Enter to continue...")

    def show_help(self):
        """Show help and documentation"""
        self.print_header("Help & Documentation")
        
        print("📖 Available Documentation:")
        print("  • DATABASE_SYNC_README.md - Complete structure sync documentation")
        print("  • FULL_SYNC_README.md - Full sync with data documentation")
        print("  • QUICK_START.md - Quick reference guide")
        print("  • SYNC_QUICK_REFERENCE.md - Command reference")
        
        print("\n🔗 Script Files:")
        print("  • database_structure_sync.sh - Structure-only sync")
        print("  • database_full_sync.sh - Full sync with data options")
        print("  • database_sync_manager.py - This Python interface")
        
        print("\n⚡ Quick Commands:")
        print("  Structure Sync: ./database_structure_sync.sh")
        print("  Data Sync:      ./database_full_sync.sh --data-only")
        print("  Full Sync:      ./database_full_sync.sh --full")
        
        input("\nPress Enter to continue...")

    def run(self):
        """Main application loop"""
        while True:
            try:
                os.system('clear')  # Clear screen
                self.print_menu()
                
                choice = input("\nSelect option: ").strip()
                
                if choice == '0':
                    self.print_colored("Goodbye!", 'green')
                    break
                elif choice == '1':
                    self.structure_sync()
                elif choice == '2':
                    self.data_sync()
                elif choice == '3':
                    self.full_sync()
                elif choice == '4':
                    self.custom_sync()
                elif choice == '5':
                    self.compare_databases()
                elif choice == '6':
                    self.create_backup()
                    input("Press Enter to continue...")
                elif choice == '7':
                    self.list_backups()
                elif choice == '8':
                    self.restore_backup()
                elif choice == '9':
                    self.view_sync_history()
                elif choice == '10':
                    self.check_system_status()
                elif choice == '11':
                    self.show_help()
                else:
                    self.print_colored("Invalid option. Please try again.", 'red')
                    time.sleep(1)
                    
            except KeyboardInterrupt:
                self.print_colored("\n\nExiting...", 'yellow')
                break
            except Exception as e:
                self.print_colored(f"\nUnexpected error: {e}", 'red')
                input("Press Enter to continue...")

if __name__ == "__main__":
    manager = DatabaseSyncManager()
    manager.run()