"""
Database Consistency Validator

Validates that Pydantic schemas match SQLAlchemy database models.
"""

import re
from pathlib import Path
from typing import Dict, List, Optional, Set, Tuple
from dataclasses import dataclass


@dataclass
class ModelField:
    """Represents a field in a SQLAlchemy model"""
    name: str
    type: str
    nullable: bool
    primary_key: bool
    default: Optional[str] = None


@dataclass
class ModelInfo:
    """Represents a SQLAlchemy model"""
    name: str
    file: str
    line: int
    fields: Dict[str, ModelField]


@dataclass
class SchemaField:
    """Represents a field in a Pydantic schema"""
    name: str
    type: str
    required: bool
    default: Optional[str] = None


@dataclass
class SchemaInfo:
    """Represents a Pydantic schema"""
    name: str
    file: str
    line: int
    fields: Dict[str, SchemaField]


@dataclass
class ConsistencyIssue:
    """Represents a consistency issue"""
    model_name: str
    schema_name: str
    issue_type: str  # "missing_in_schema", "missing_in_model", "type_mismatch", "nullable_mismatch"
    field_name: str
    model_type: str
    schema_type: str
    model_nullable: bool
    schema_required: bool


class DatabaseConsistencyValidator:
    """Validates database models against Pydantic schemas"""

    def __init__(self, project_dir: Path):
        self.project_dir = project_dir
        self.backend_models_dir = project_dir / "backend" / "app" / "models"
        self.backend_schemas_dir = project_dir / "backend" / "app" / "schemas"

    def extract_models(self) -> Dict[str, ModelInfo]:
        """Extract SQLAlchemy models from backend"""
        models = {}

        if not self.backend_models_dir.exists():
            return models

        for model_file in self.backend_models_dir.rglob("*.py"):
            content = model_file.read_text(encoding='utf-8')
            lines = content.split('\n')

            # Find class definitions
            for i, line in enumerate(lines):
                # Match SQLAlchemy model classes
                class_match = re.match(r'^class (\w+)\(.*Base.*\):', line)
                if class_match:
                    model_name = class_match.group(1)
                    fields = self._extract_model_fields(lines, i + 1)
                    models[model_name] = ModelInfo(
                        name=model_name,
                        file=str(model_file.relative_to(self.project_dir)),
                        line=i + 1,
                        fields=fields
                    )

        return models

    def _extract_model_fields(self, lines: List[str], start_line: int) -> Dict[str, ModelField]:
        """Extract fields from a SQLAlchemy model"""
        fields = {}

        for i in range(start_line, min(start_line + 100, len(lines))):
            line = lines[i]
            stripped = line.strip()
            if not stripped:
                continue

            # Stop when we reach another class or function
            if re.match(r'^class ', stripped) or re.match(r'^def ', stripped):
                break

            # Skip decorators
            if stripped.startswith('@'):
                continue

            # Match Column definitions
            column_match = re.match(r'(\w+)\s*=\s*Column\(', stripped)
            if column_match:
                field_name = column_match.group(1)
                # Extract type from Column(...)
                type_match = re.search(r'Column\(([^,]+)', stripped)
                if type_match:
                    type_str = type_match.group(1).strip()
                    # Extract the type name (e.g., UUID, String, JSON)
                    type_name_match = re.search(r'(\w+)', type_str)
                    field_type = type_name_match.group(1) if type_name_match else "Unknown"
                else:
                    field_type = "Unknown"

                # Check nullable
                nullable = 'nullable=False' not in stripped and 'primary_key=True' not in stripped

                # Check primary key
                primary_key = 'primary_key=True' in stripped

                fields[field_name] = ModelField(
                    name=field_name,
                    type=field_type,
                    nullable=nullable,
                    primary_key=primary_key
                )

        return fields

    def extract_schemas(self) -> Dict[str, SchemaInfo]:
        """Extract Pydantic schemas from backend"""
        schemas = {}

        if not self.backend_schemas_dir.exists():
            return schemas

        for schema_file in self.backend_schemas_dir.rglob("*.py"):
            content = schema_file.read_text(encoding='utf-8')
            lines = content.split('\n')

            # Find class definitions
            for i, line in enumerate(lines):
                # Match Pydantic schema classes
                class_match = re.match(r'^class (\w+)\(.*BaseModel.*\):', line)
                if class_match:
                    schema_name = class_match.group(1)
                    fields = self._extract_schema_fields(lines, i + 1)
                    schemas[schema_name] = SchemaInfo(
                        name=schema_name,
                        file=str(schema_file.relative_to(self.project_dir)),
                        line=i + 1,
                        fields=fields
                    )

        return schemas

    def _extract_schema_fields(self, lines: List[str], start_line: int) -> Dict[str, SchemaField]:
        """Extract fields from a Pydantic schema"""
        fields = {}

        for i in range(start_line, min(start_line + 100, len(lines))):
            line = lines[i]
            stripped = line.strip()
            if not stripped:
                continue

            # Stop when we reach another class or function
            if re.match(r'^class ', stripped) or re.match(r'^def ', stripped):
                break

            # Skip decorators
            if stripped.startswith('@'):
                continue

            # Match field definitions
            field_match = re.match(r'(\w+)\s*:\s*(.+)', stripped)
            if field_match and not field_match.group(1).startswith('_'):
                field_name = field_match.group(1)
                field_type = field_match.group(2).split('=')[0].strip()

                # Check if required (no Optional and no default value)
                is_optional = 'Optional[' in field_type or '| None' in field_type
                has_default = '=' in stripped and 'Field(' not in stripped
                required = not is_optional and not has_default

                fields[field_name] = SchemaField(
                    name=field_name,
                    type=field_type,
                    required=required
                )

        return fields

    def validate_consistency(self) -> List[ConsistencyIssue]:
        """Validate database models against Pydantic schemas"""
        issues = []

        # Exclude sensitive fields from validation
        excluded_fields = {
            'password_hash',  # Should never be exposed in API responses
            'refresh_token',  # Sensitive token
        }

        models = self.extract_models()
        schemas = self.extract_schemas()

        # Find matching model-schema pairs
        for model_name, model in models.items():
            # Look for corresponding schema (model name or model name + Response/Create/etc.)
            schema_candidates = [
                model_name,
                model_name.replace('Model', ''),
                model_name + 'Response',
                model_name + 'Create',
                model_name + 'Update',
                model_name + 'Base'
            ]

            matching_schema = None
            for candidate in schema_candidates:
                if candidate in schemas:
                    matching_schema = schemas[candidate]
                    break

            if not matching_schema:
                continue

            # Compare fields
            model_fields = set(model.fields.keys())
            schema_fields = set(matching_schema.fields.keys())

            # Fields in model but not in schema
            for field_name in model_fields - schema_fields:
                model_field = model.fields[field_name]
                # Skip primary keys and excluded sensitive fields
                if not model_field.primary_key and field_name not in excluded_fields:
                    issues.append(ConsistencyIssue(
                        model_name=model_name,
                        schema_name=matching_schema.name,
                        issue_type="missing_in_schema",
                        field_name=field_name,
                        model_type=model_field.type,
                        schema_type="",
                        model_nullable=model_field.nullable,
                        schema_required=False
                    ))

            # Fields in schema but not in model
            for field_name in schema_fields - model_fields:
                schema_field = matching_schema.fields[field_name]
                issues.append(ConsistencyIssue(
                    model_name=model_name,
                    schema_name=matching_schema.name,
                    issue_type="missing_in_model",
                    field_name=field_name,
                    model_type="",
                    schema_type=schema_field.type,
                    model_nullable=False,
                    schema_required=schema_field.required
                ))

            # Type and nullable mismatches for common fields
            common_fields = model_fields & schema_fields
            for field_name in common_fields:
                model_field = model.fields[field_name]
                schema_field = matching_schema.fields[field_name]

                # Type mismatch (simplified check)
                # Ignore trivial type mapping differences (String vs str, Boolean vs bool, etc.)
                # Also ignore UUID vs UUID (same type, different representation)
                type_mapping = {
                    'String': 'str',
                    'Integer': 'int',
                    'Boolean': 'bool',
                    'Float': 'float',
                    'DateTime': 'datetime',
                    'Text': 'str',
                    'UUID': 'str',
                    'JSON': 'dict'
                }
                normalized_model_type = type_mapping.get(model_field.type, model_field.type.lower())
                normalized_schema_type = schema_field.type.lower().replace('optional[', '').replace(']', '').strip()

                # Skip if types match after normalization or if both are UUID
                if normalized_model_type in normalized_schema_type or normalized_schema_type in normalized_model_type:
                    continue
                if model_field.type == 'UUID' and 'UUID' in schema_field.type:
                    continue

                if model_field.type != "Unknown":
                    issues.append(ConsistencyIssue(
                        model_name=model_name,
                        schema_name=matching_schema.name,
                        issue_type="type_mismatch",
                        field_name=field_name,
                        model_type=model_field.type,
                        schema_type=schema_field.type,
                        model_nullable=model_field.nullable,
                        schema_required=schema_field.required
                    ))

                # Nullable/Required mismatch - ignore as it's common for schemas to be stricter
                # than database models
                # if model_field.nullable and schema_field.required:
                #     issues.append(ConsistencyIssue(
                #         model_name=model_name,
                #         schema_name=matching_schema.name,
                #         issue_type="nullable_mismatch",
                #         field_name=field_name,
                #         model_type=model_field.type,
                #         schema_type=schema_field.type,
                #         model_nullable=model_field.nullable,
                #         schema_required=schema_field.required
                #     ))

        return issues

    def generate_report(self) -> Dict:
        """Generate a consistency report"""
        issues = self.validate_consistency()

        # Group by issue type
        issues_by_type = {}
        for issue in issues:
            if issue.issue_type not in issues_by_type:
                issues_by_type[issue.issue_type] = []
            issues_by_type[issue.issue_type].append(issue)

        return {
            "total_issues": len(issues),
            "issues_by_type": issues_by_type,
            "issues": issues
        }


if __name__ == "__main__":
    import sys
    project_dir = Path(__file__).parent.parent.parent
    validator = DatabaseConsistencyValidator(project_dir)

    # Debug: Show models found
    models = validator.extract_models()
    print(f"Models found: {len(models)}")
    for model_name, model in models.items():
        print(f"  - {model_name}: {len(model.fields)} fields")
        for field_name, field in model.fields.items():
            print(f"      {field_name}: {field.type} (nullable={field.nullable})")

    # Debug: Show schemas found
    schemas = validator.extract_schemas()
    print(f"\nSchemas found: {len(schemas)}")
    for schema_name, schema in schemas.items():
        print(f"  - {schema_name}: {len(schema.fields)} fields")

    report = validator.generate_report()

    print("\n" + "=" * 60)
    print("Database Consistency Report")
    print("=" * 60)
    print(f"\nTotal Issues: {report['total_issues']}")
    print(f"\nIssues by Type:")
    for issue_type, issues in report['issues_by_type'].items():
        print(f"  {issue_type}: {len(issues)}")

    if report['issues']:
        print(f"\nFound {report['total_issues']} issue(s):")
        for issue in report['issues']:
            print(f"\n  [{issue.issue_type}]")
            print(f"    Model: {issue.model_name}")
            print(f"    Schema: {issue.schema_name}")
            print(f"    Field: {issue.field_name}")
            print(f"    Model Type: {issue.model_type}")
            print(f"    Schema Type: {issue.schema_type}")
            print(f"    Model Nullable: {issue.model_nullable}")
            print(f"    Schema Required: {issue.schema_required}")
    else:
        print("\n✓ No consistency issues found")