"""
Schema management utilities for tenant separation.
Handles creation, deletion, and migration of tenant schemas.
"""
from django.db import connection, transaction
from django.core.management import execute_from_command_line
from django.apps import apps
import logging

logger = logging.getLogger(__name__)

class SchemaManager:
    """Utility class for managing tenant schemas."""
    
    @staticmethod
    def create_tenant_schema(tenant_code):
        """Create a new schema for a tenant."""
        schema_name = f"tenant_{tenant_code}"
        
        with connection.cursor() as cursor:
            try:
                # Create the schema
                cursor.execute(f'CREATE SCHEMA IF NOT EXISTS "{schema_name}"')
                logger.info(f"Created schema: {schema_name}")
                
                # Set search path to new schema
                cursor.execute(f'SET search_path TO "{schema_name}", public')
                
                return True
                
            except Exception as e:
                logger.error(f"Error creating schema {schema_name}: {e}")
                return False
    
    @staticmethod
    def drop_tenant_schema(tenant_code, cascade=False):
        """Drop a tenant schema."""
        schema_name = f"tenant_{tenant_code}"
        cascade_clause = "CASCADE" if cascade else "RESTRICT"
        
        with connection.cursor() as cursor:
            try:
                cursor.execute(f'DROP SCHEMA IF EXISTS "{schema_name}" {cascade_clause}')
                logger.info(f"Dropped schema: {schema_name}")
                return True
                
            except Exception as e:
                logger.error(f"Error dropping schema {schema_name}: {e}")
                return False
    
    @staticmethod
    def schema_exists(tenant_code):
        """Check if a tenant schema exists."""
        schema_name = f"tenant_{tenant_code}"
        
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT EXISTS(
                    SELECT 1 FROM information_schema.schemata 
                    WHERE schema_name = %s
                )
            """, [schema_name])
            return cursor.fetchone()[0]
    
    @staticmethod
    def migrate_tenant_schema(tenant_code):
        """Run migrations for a specific tenant schema."""
        from .router import set_tenant, get_current_tenant
        from .models import Tenant
        
        try:
            tenant = Tenant.objects.get(tenant_code=tenant_code)
            
            # Set tenant context
            original_tenant = get_current_tenant()
            set_tenant(tenant)
            
            # Get tenant-aware apps
            tenant_apps = ['customers', 'inventory', 'sales', 'orders', 'suppliers', 'hr', 'billing']
            
            for app_label in tenant_apps:
                try:
                    # Run migrations for this app in tenant schema
                    execute_from_command_line([
                        'manage.py', 'migrate', app_label, '--verbosity=0'
                    ])
                    logger.info(f"Migrated app {app_label} for tenant {tenant_code}")
                    
                except Exception as e:
                    logger.error(f"Error migrating {app_label} for tenant {tenant_code}: {e}")
            
            return True
            
        except Exception as e:
            logger.error(f"Error migrating tenant {tenant_code}: {e}")
            return False
            
        finally:
            # Restore original tenant context
            set_tenant(original_tenant)
    
    @staticmethod
    def list_tenant_schemas():
        """List all tenant schemas in the database."""
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT schema_name FROM information_schema.schemata 
                WHERE schema_name LIKE 'tenant_%'
                ORDER BY schema_name
            """)
            return [row[0] for row in cursor.fetchall()]
    
    @staticmethod
    def copy_data_to_tenant_schema(tenant_code, source_schema='public'):
        """Copy existing data from source schema to tenant schema."""
        schema_name = f"tenant_{tenant_code}"
        tenant_apps = ['customers', 'inventory', 'sales']  # Core apps with data
        
        with connection.cursor() as cursor:
            try:
                # Get all tables that need data migration
                cursor.execute(f"""
                    SELECT table_name FROM information_schema.tables 
                    WHERE table_schema = '{source_schema}' 
                    AND table_name LIKE 'customers_%' 
                    OR table_name LIKE 'inventory_%' 
                    OR table_name LIKE 'sales_%'
                """)
                
                tables = [row[0] for row in cursor.fetchall()]
                
                for table_name in tables:
                    # Copy data with tenant filtering (if tenant_id exists)
                    try:
                        # Check if table has tenant_id column
                        cursor.execute(f"""
                            SELECT column_name FROM information_schema.columns 
                            WHERE table_schema = '{source_schema}' 
                            AND table_name = '{table_name}' 
                            AND column_name = 'tenant_id'
                        """)
                        
                        has_tenant_id = cursor.fetchone() is not None
                        
                        if has_tenant_id:
                            # Copy only data for this tenant
                            cursor.execute(f"""
                                INSERT INTO "{schema_name}".{table_name}
                                SELECT * FROM {source_schema}.{table_name}
                                WHERE tenant_id = (
                                    SELECT id FROM {source_schema}.tenants_tenant 
                                    WHERE tenant_code = %s
                                )
                            """, [tenant_code])
                        else:
                            # Copy all data (for tables without tenant_id)
                            cursor.execute(f"""
                                INSERT INTO "{schema_name}".{table_name}
                                SELECT * FROM {source_schema}.{table_name}
                            """)
                        
                        logger.info(f"Copied data to {schema_name}.{table_name}")
                        
                    except Exception as e:
                        logger.warning(f"Could not copy {table_name}: {e}")
                        continue
                
                return True
                
            except Exception as e:
                logger.error(f"Error copying data to {schema_name}: {e}")
                return False


# Convenience functions for easy access
def get_tenant_schema_name(tenant_code):
    """Get schema name for a tenant code."""
    return f"tenant_{tenant_code}"

def create_tenant_schema(tenant_code):
    """Create schema for a tenant."""
    return SchemaManager.create_tenant_schema(tenant_code)

def schema_exists(tenant_code):
    """Check if tenant schema exists."""
    return SchemaManager.schema_exists(tenant_code)

def set_current_tenant_schema(tenant_code):
    """Set current database schema to tenant schema."""
    schema_name = get_tenant_schema_name(tenant_code)
    with connection.cursor() as cursor:
        cursor.execute(f'SET search_path TO "{schema_name}", public')

def migrate_tenant_schema(tenant_code):
    """Run migrations for a tenant schema."""
    return SchemaManager.migrate_tenant_schema(tenant_code)

def get_current_tenant_schema():
    """Get current database schema."""
    with connection.cursor() as cursor:
        cursor.execute("SELECT current_schema()")
        return cursor.fetchone()[0]