"""
Schema Analyzer - Analyzes SQLAlchemy models to detect schema issues
"""
import os
import re
from pathlib import Path
from typing import Dict, Set, List, Tuple


class SchemaAnalyzer:
    """Analyzes SQLAlchemy models"""
    
    def __init__(self, project_dir: str):
        self.project_dir = Path(project_dir)
        self.models_dir = self.project_dir / "backend" / "app" / "models"
    
    def analyze_models(self) -> Dict[str, Dict[str, str]]:
        """Analyze SQLAlchemy models to extract table names and columns with types"""
        models_info = {}
        
        if not self.models_dir.exists():
            return models_info
        
        for model_file in self.models_dir.glob("*.py"):
            if model_file.name == "__init__.py":
                continue
            
            tables_info = self._extract_model_info(model_file)
            for table_name, columns in tables_info:
                if table_name and columns:
                    models_info[table_name] = columns
        
        return models_info
    
    def _extract_model_info(self, model_file: Path) -> List[Tuple[str, Dict[str, str]]]:
        """Extract table name and columns with types from a model file (may have multiple tables)"""
        try:
            with open(model_file, 'r', encoding='utf-8') as f:
                content = f.read()
            
            tables_info = []
            
            # Find all class definitions with __tablename__
            class_pattern = r'class\s+(\w+)\([^)]+\):.*?__tablename__\s*=\s*["\']([^"\']+)["\']'
            
            for class_match in re.finditer(class_pattern, content, re.DOTALL):
                class_name = class_match.group(1)
                table_name = class_match.group(2)
                
                # Extract column names and types from this class
                # Get the class content
                class_start = class_match.start()
                # Find the indentation and class content
                lines = content[class_start:].split('\n')
                class_content = []
                indent_level = None
                
                for line in lines:
                    if indent_level is None and line.strip():
                        indent_level = len(line) - len(line.lstrip())
                    if indent_level is not None:
                        if line.strip() and (len(line) - len(line.lstrip())) <= indent_level:
                            if not line.strip().startswith('class'):
                                class_content.append(line)
                            else:
                                break
                        else:
                            class_content.append(line)
                
                class_content_str = '\n'.join(class_content)
                
                # Extract columns
                columns = {}
                column_pattern = r'(\w+)\s*=\s*Column\(([^)]+)\)'
                for col_match in re.finditer(column_pattern, class_content_str):
                    column_name = col_match.group(1)
                    column_args = col_match.group(2)
                    # Skip common non-column attributes
                    if column_name not in ['__tablename__', '__table_args__', '__mapper_args__']:
                        # Extract type from arguments
                        type_match = re.search(r'(String|Integer|Text|Boolean|DateTime|UUID|ForeignKey)\(?\d*\)?', column_args)
                        if type_match:
                            column_type = type_match.group(1)
                            # Map SQLAlchemy types to SQL types
                            sql_type = self._map_sqlalchemy_type_to_sql(column_type)
                            columns[column_name] = sql_type
                
                tables_info.append((table_name, columns))
            
            return tables_info
            
        except Exception as e:
            print(f"Error analyzing {model_file}: {e}")
            return []
    
    def _map_sqlalchemy_type_to_sql(self, sqlalchemy_type: str) -> str:
        """Map SQLAlchemy type names to SQL type names"""
        type_mapping = {
            'String': 'VARCHAR(255)',
            'Integer': 'INTEGER',
            'Text': 'TEXT',
            'Boolean': 'BOOLEAN',
            'DateTime': 'TIMESTAMP WITH TIME ZONE',
            'UUID': 'UUID',
            'ForeignKey': 'UUID'  # Simplified, would need actual reference
        }
        return type_mapping.get(sqlalchemy_type, 'TEXT')
    
    def get_model_info(self) -> Dict[str, Dict[str, str]]:
        """Get model information (alias for analyze_models)"""
        return self.analyze_models()


if __name__ == "__main__":
    # Test the analyzer
    project_dir = Path(__file__).parent.parent.parent
    analyzer = SchemaAnalyzer(str(project_dir))
    
    models_info = analyzer.analyze_models()
    print(f"Found {len(models_info)} models:")
    for table_name, columns in models_info.items():
        print(f"  {table_name}: {len(columns)} columns")