#!/usr/bin/env python
"""
Comprehensive database verification and synchronization script for production
"""
import os
import sys
import json
import requests
from datetime import datetime
from sqlalchemy import create_engine, inspect, text
from typing import Dict, List, Any

# Configuration
BASE_URL = "https://rateright-au.fly.dev"
API_BASE = f"{BASE_URL}/api"

class DatabaseVerifier:
    def __init__(self):
        self.results = {
            "schema_check": {},
            "crud_operations": {},
            "constraint_tests": {},
            "data_integrity": {},
            "fixes_applied": [],
            "errors": []
        }
        self.session = requests.Session()
        
    def check_schema(self, engine):
        """Compare ORM models with actual database schema"""
        print("=== Checking Database Schema ===")
        inspector = inspect(engine)
        
        # Expected tables based on models
        expected_tables = [
            'users', 'jobs', 'job_applications', 'contracts', 
            'messages', 'conversations', 'ratings', 'payments',
            'availability', 'bookings', 'categories', 'gamification_achievements',
            'user_achievements', 'leaderboard_entries', 'notifications',
            'time_tracking', 'legal_documents', 'safety_incidents'
        ]
        
        # Get actual tables
        actual_tables = inspector.get_table_names()
        
        # Check for missing tables
        missing_tables = set(expected_tables) - set(actual_tables)
        extra_tables = set(actual_tables) - set(expected_tables)
        
        self.results["schema_check"]["missing_tables"] = list(missing_tables)
        self.results["schema_check"]["extra_tables"] = list(extra_tables)
        self.results["schema_check"]["actual_tables"] = actual_tables
        
        # Check columns for each table
        for table in expected_tables:
            if table in actual_tables:
                columns = inspector.get_columns(table)
                self.results["schema_check"][f"{table}_columns"] = [col['name'] for col in columns]
                
                # Check indexes
                indexes = inspector.get_indexes(table)
                self.results["schema_check"][f"{table}_indexes"] = indexes
                
                # Check foreign keys
                foreign_keys = inspector.get_foreign_keys(table)
                self.results["schema_check"][f"{table}_foreign_keys"] = foreign_keys
        
        return missing_tables, extra_tables
    
    def test_auth_routes(self):
        """Test authentication CRUD operations"""
        print("=== Testing Auth Routes ===")
        test_user = {
            "email": f"test_{datetime.now().timestamp()}@example.com",
            "password": "TestPass123!",
            "first_name": "Test",
            "last_name": "User",
            "role": "worker"
        }
        
        # Test registration (CREATE)
        try:
            resp = self.session.post(f"{BASE_URL}/auth/register", json=test_user)
            self.results["crud_operations"]["auth_register"] = {
                "status": resp.status_code,
                "success": resp.status_code in [200, 201, 302]
            }
        except Exception as e:
            self.results["crud_operations"]["auth_register"] = {"error": str(e)}
        
        # Test login (READ)
        try:
            resp = self.session.post(f"{BASE_URL}/auth/login", json={
                "email": test_user["email"],
                "password": test_user["password"]
            })
            self.results["crud_operations"]["auth_login"] = {
                "status": resp.status_code,
                "success": resp.status_code in [200, 302]
            }
        except Exception as e:
            self.results["crud_operations"]["auth_login"] = {"error": str(e)}
    
    def test_job_routes(self):
        """Test job CRUD operations"""
        print("=== Testing Job Routes ===")
        
        # Test job creation
        test_job = {
            "title": "Test Job",
            "description": "Test job description",
            "category": "General",
            "budget": 1000,
            "location": "Sydney",
            "duration": "1 week"
        }
        
        try:
            resp = self.session.post(f"{API_BASE}/jobs", json=test_job)
            self.results["crud_operations"]["job_create"] = {
                "status": resp.status_code,
                "success": resp.status_code in [200, 201]
            }
            
            if resp.status_code in [200, 201]:
                job_id = resp.json().get("id")
                
                # Test job read
                resp = self.session.get(f"{API_BASE}/jobs/{job_id}")
                self.results["crud_operations"]["job_read"] = {
                    "status": resp.status_code,
                    "success": resp.status_code == 200
                }
                
                # Test job update
                resp = self.session.put(f"{API_BASE}/jobs/{job_id}", json={"title": "Updated Job"})
                self.results["crud_operations"]["job_update"] = {
                    "status": resp.status_code,
                    "success": resp.status_code == 200
                }
                
                # Test job delete
                resp = self.session.delete(f"{API_BASE}/jobs/{job_id}")
                self.results["crud_operations"]["job_delete"] = {
                    "status": resp.status_code,
                    "success": resp.status_code in [200, 204]
                }
        except Exception as e:
            self.results["crud_operations"]["job_operations"] = {"error": str(e)}
    
    def test_contract_routes(self):
        """Test contract CRUD operations"""
        print("=== Testing Contract Routes ===")
        
        test_contract = {
            "job_id": 1,
            "worker_id": 1,
            "terms": "Test contract terms",
            "amount": 500,
            "status": "pending"
        }
        
        try:
            resp = self.session.post(f"{API_BASE}/contracts", json=test_contract)
            self.results["crud_operations"]["contract_create"] = {
                "status": resp.status_code,
                "success": resp.status_code in [200, 201]
            }
        except Exception as e:
            self.results["crud_operations"]["contract_create"] = {"error": str(e)}
    
    def test_message_routes(self):
        """Test message CRUD operations"""
        print("=== Testing Message Routes ===")
        
        test_message = {
            "recipient_id": 1,
            "content": "Test message content"
        }
        
        try:
            resp = self.session.post(f"{API_BASE}/messages", json=test_message)
            self.results["crud_operations"]["message_create"] = {
                "status": resp.status_code,
                "success": resp.status_code in [200, 201]
            }
        except Exception as e:
            self.results["crud_operations"]["message_create"] = {"error": str(e)}
    
    def test_rating_routes(self):
        """Test rating CRUD operations"""
        print("=== Testing Rating Routes ===")
        
        test_rating = {
            "contract_id": 1,
            "rating": 5,
            "review": "Excellent work!"
        }
        
        try:
            resp = self.session.post(f"{API_BASE}/ratings", json=test_rating)
            self.results["crud_operations"]["rating_create"] = {
                "status": resp.status_code,
                "success": resp.status_code in [200, 201]
            }
        except Exception as e:
            self.results["crud_operations"]["rating_create"] = {"error": str(e)}
    
    def test_constraints(self, engine):
        """Test database constraints"""
        print("=== Testing Database Constraints ===")
        
        with engine.begin() as conn:
            # Test unique constraint on users.email
            try:
                conn.execute(text("""
                    INSERT INTO users (email, password_hash, first_name, last_name, role)
                    VALUES ('duplicate@test.com', 'hash1', 'Test', 'User', 'worker')
                """))
                conn.execute(text("""
                    INSERT INTO users (email, password_hash, first_name, last_name, role)
                    VALUES ('duplicate@test.com', 'hash2', 'Test2', 'User2', 'client')
                """))
                self.results["constraint_tests"]["unique_email"] = "FAILED - No unique constraint"
            except:
                self.results["constraint_tests"]["unique_email"] = "PASSED - Unique constraint enforced"
                conn.rollback()
            
            # Test foreign key constraints
            try:
                conn.execute(text("""
                    INSERT INTO job_applications (job_id, worker_id, status)
                    VALUES (99999, 99999, 'pending')
                """))
                self.results["constraint_tests"]["foreign_keys"] = "FAILED - No FK constraint"
            except:
                self.results["constraint_tests"]["foreign_keys"] = "PASSED - FK constraint enforced"
                conn.rollback()
            
            # Test NOT NULL constraints
            try:
                conn.execute(text("""
                    INSERT INTO users (email) VALUES ('incomplete@test.com')
                """))
                self.results["constraint_tests"]["not_null"] = "FAILED - NULL values accepted"
            except:
                self.results["constraint_tests"]["not_null"] = "PASSED - NOT NULL enforced"
    
    def check_data_integrity(self, engine):
        """Verify data integrity across tables"""
        print("=== Checking Data Integrity ===")
        
        with engine.connect() as conn:
            # Check orphaned records
            orphaned_apps = conn.execute(text("""
                SELECT COUNT(*) as count FROM job_applications ja
                LEFT JOIN jobs j ON ja.job_id = j.id
                WHERE j.id IS NULL
            """)).fetchone()
            
            orphaned_contracts = conn.execute(text("""
                SELECT COUNT(*) as count FROM contracts c
                LEFT JOIN jobs j ON c.job_id = j.id
                WHERE j.id IS NULL
            """)).fetchone()
            
            orphaned_messages = conn.execute(text("""
                SELECT COUNT(*) as count FROM messages m
                LEFT JOIN users u ON m.sender_id = u.id
                WHERE u.id IS NULL
            """)).fetchone()
            
            self.results["data_integrity"]["orphaned_applications"] = orphaned_apps[0] if orphaned_apps else 0
            self.results["data_integrity"]["orphaned_contracts"] = orphaned_contracts[0] if orphaned_contracts else 0
            self.results["data_integrity"]["orphaned_messages"] = orphaned_messages[0] if orphaned_messages else 0
    
    def apply_fixes(self, engine, missing_tables, issues):
        """Apply necessary fixes to the database"""
        print("=== Applying Database Fixes ===")
        
        with engine.begin() as conn:
            # Add missing columns for calendar sync (already attempted earlier)
            try:
                conn.execute(text("ALTER TABLE users ADD COLUMN IF NOT EXISTS calendar_sync_enabled BOOLEAN DEFAULT FALSE"))
                conn.execute(text("ALTER TABLE users ADD COLUMN IF NOT EXISTS calendar_sync_data_encrypted TEXT"))
                conn.execute(text("ALTER TABLE users ADD COLUMN IF NOT EXISTS calendar_last_sync TIMESTAMP"))
                self.results["fixes_applied"].append("Added calendar sync columns to users table")
            except Exception as e:
                pass  # Columns might already exist
            
            # Create missing tables if any
            for table in missing_tables:
                # This would need the actual CREATE TABLE statements
                self.results["fixes_applied"].append(f"TODO: Create table {table}")
            
            # Add missing indexes
            try:
                conn.execute(text("CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status)"))
                conn.execute(text("CREATE INDEX IF NOT EXISTS idx_contracts_status ON contracts(status)"))
                conn.execute(text("CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at)"))
                self.results["fixes_applied"].append("Added performance indexes")
            except Exception as e:
                self.results["errors"].append(f"Index creation error: {str(e)}")
    
    def generate_report(self):
        """Generate comprehensive report"""
        print("\n" + "="*60)
        print("DATABASE VERIFICATION REPORT")
        print("="*60)
        
        print("\n## Schema Check")
        print(f"Missing Tables: {self.results['schema_check'].get('missing_tables', [])}")
        print(f"Extra Tables: {self.results['schema_check'].get('extra_tables', [])}")
        print(f"Total Tables Found: {len(self.results['schema_check'].get('actual_tables', []))}")
        
        print("\n## CRUD Operations Test")
        for route, result in self.results["crud_operations"].items():
            status = "✓ PASS" if result.get("success") else "✗ FAIL"
            print(f"{route}: {status}")
            if "error" in result:
                print(f"  Error: {result['error']}")
        
        print("\n## Constraint Tests")
        for test, result in self.results["constraint_tests"].items():
            print(f"{test}: {result}")
        
        print("\n## Data Integrity")
        for check, count in self.results["data_integrity"].items():
            status = "✓" if count == 0 else "⚠"
            print(f"{status} {check}: {count}")
        
        print("\n## Fixes Applied")
        for fix in self.results["fixes_applied"]:
            print(f"- {fix}")
        
        if self.results["errors"]:
            print("\n## Errors Encountered")
            for error in self.results["errors"]:
                print(f"- {error}")
        
        # Save full report
        with open("database_verification_report.json", "w") as f:
            json.dump(self.results, f, indent=2, default=str)
        
        return self.results

def main():
    # This would need DATABASE_URL from environment or secrets
    print("Starting comprehensive database verification...")
    
    verifier = DatabaseVerifier()
    
    # For production, we'd need to connect via SSH or use fly proxy
    # This is a placeholder structure
    print("\nNote: Full database verification requires production database access.")
    print("The script structure is ready for execution with proper credentials.")
    
    # Test what we can via API
    verifier.test_auth_routes()
    verifier.test_job_routes()
    verifier.test_contract_routes()
    verifier.test_message_routes()
    verifier.test_rating_routes()
    
    # Generate report
    verifier.generate_report()
    
    print("\n✓ Database verification script created and partially executed")
    print("✓ API endpoint tests completed")
    print("⚠ Direct database access required for full schema verification")

if __name__ == "__main__":
    main()