"""
Worktable Service - Business Logic Services.

This module contains the core business logic for:
- Table management (CRUD, schema operations)
- Record management (CRUD, bulk operations)
- View management (custom views, saved filters)
- Data operations (import, export, computed fields)
"""
import csv
import io
from typing import Any
from uuid import UUID

from django.db import transaction
from django.db.models import Q
from django.utils import timezone
from django.utils.text import slugify

from .models import (
    TableDefinition,
    TableColumn,
    TableRecord,
    SavedFilter,
    CustomView,
    RecordAuditLog,
    AuditAction,
    FieldType,
    RecordStatus,
)
from .validators import DataValidator, SchemaValidator, ValidationError, MultiValidationError


class TableService:
    """
    Service for managing table definitions.
    """
    
    @staticmethod
    def create_table(
        tenant,
        name: str,
        slug: str = None,
        description: str = None,
        icon: str = None,
        settings: dict = None,
        columns: list[dict] = None,
    ) -> TableDefinition:
        """
        Create a new table definition with optional columns.
        
        Args:
            tenant: The tenant this table belongs to
            name: Display name for the table
            slug: URL-friendly identifier (auto-generated if not provided)
            description: Optional description
            icon: Optional icon name
            settings: Table settings (page_size, allow_export, etc.)
            columns: List of column definitions
        
        Returns:
            Created TableDefinition instance
        """
        if not slug:
            slug = slugify(name, allow_unicode=True)
            # Ensure unique slug within tenant
            base_slug = slug
            counter = 1
            while TableDefinition.objects.filter(tenant=tenant, slug=slug).exists():
                slug = f"{base_slug}-{counter}"
                counter += 1
        
        with transaction.atomic():
            table = TableDefinition.objects.create(
                tenant=tenant,
                name=name,
                slug=slug,
                description=description,
                icon=icon,
                settings=settings or {},
            )
            
            # Create columns if provided
            if columns:
                ColumnService.bulk_create_columns(table, columns)
        
        return table
    
    @staticmethod
    def update_table(
        table: TableDefinition,
        **kwargs
    ) -> TableDefinition:
        """
        Update table definition.
        
        Supported kwargs: name, description, icon, settings, is_active,
                         enable_audit, enable_workflow, order
        """
        allowed_fields = {
            'name', 'description', 'icon', 'settings', 'is_active',
            'enable_audit', 'enable_workflow', 'order'
        }
        
        for field, value in kwargs.items():
            if field in allowed_fields:
                setattr(table, field, value)
        
        table.save()
        return table
    
    @staticmethod
    def delete_table(table: TableDefinition, hard_delete: bool = False) -> None:
        """
        Delete a table definition.
        
        Args:
            table: Table to delete
            hard_delete: If True, permanently delete. Otherwise, just deactivate.
        """
        if hard_delete:
            table.delete()
        else:
            table.is_active = False
            table.save(update_fields=['is_active'])
    
    @staticmethod
    def get_table_stats(table: TableDefinition) -> dict:
        """Get statistics for a table."""
        records = table.records.all()
        
        return {
            'total_records': records.count(),
            'active_records': records.filter(is_deleted=False).count(),
            'deleted_records': records.filter(is_deleted=True).count(),
            'column_count': table.columns.count(),
            'view_count': table.custom_views.count(),
            'filter_count': table.saved_filters.count(),
        }


class ColumnService:
    """
    Service for managing table columns.
    """
    
    @staticmethod
    def create_column(
        table: TableDefinition,
        name: str,
        slug: str,
        field_type: str,
        config: dict = None,
        is_required: bool = False,
        is_unique: bool = False,
        is_searchable: bool = True,
        is_sortable: bool = True,
        is_filterable: bool = True,
        show_in_list: bool = True,
        order: int = None,
    ) -> TableColumn:
        """
        Create a new column in a table.
        """
        # Validate slug
        existing_slugs = set(table.columns.values_list('slug', flat=True))
        SchemaValidator.validate_column_slug(slug, existing_slugs)
        
        # Validate and normalize config
        validated_config = SchemaValidator.validate_column_config(field_type, config or {})
        
        # Auto-set order if not provided
        if order is None:
            max_order = table.columns.aggregate(max_order=models.Max('order'))['max_order']
            order = (max_order or 0) + 1
        
        return TableColumn.objects.create(
            table=table,
            name=name,
            slug=slug,
            field_type=field_type,
            config=validated_config,
            is_required=is_required,
            is_unique=is_unique,
            is_searchable=is_searchable,
            is_sortable=is_sortable,
            is_filterable=is_filterable,
            show_in_list=show_in_list,
            order=order,
        )
    
    @staticmethod
    def bulk_create_columns(table: TableDefinition, columns: list[dict]) -> list[TableColumn]:
        """
        Create multiple columns at once.
        """
        existing_slugs = set(table.columns.values_list('slug', flat=True))
        created_columns = []
        
        with transaction.atomic():
            for idx, col_data in enumerate(columns):
                slug = col_data.get('slug')
                if not slug:
                    slug = slugify(col_data['name'], allow_unicode=True)
                
                SchemaValidator.validate_column_slug(slug, existing_slugs)
                existing_slugs.add(slug)
                
                config = SchemaValidator.validate_column_config(
                    col_data.get('field_type', FieldType.TEXT),
                    col_data.get('config', {})
                )
                
                column = TableColumn.objects.create(
                    table=table,
                    name=col_data['name'],
                    slug=slug,
                    field_type=col_data.get('field_type', FieldType.TEXT),
                    config=config,
                    is_required=col_data.get('is_required', False),
                    is_unique=col_data.get('is_unique', False),
                    is_searchable=col_data.get('is_searchable', True),
                    is_sortable=col_data.get('is_sortable', True),
                    is_filterable=col_data.get('is_filterable', True),
                    show_in_list=col_data.get('show_in_list', True),
                    order=col_data.get('order', idx + 1),
                )
                created_columns.append(column)
        
        return created_columns
    
    @staticmethod
    def update_column(column: TableColumn, **kwargs) -> TableColumn:
        """Update a column definition."""
        allowed_fields = {
            'name', 'config', 'is_required', 'is_unique', 'is_searchable',
            'is_sortable', 'is_filterable', 'show_in_list', 'order'
        }
        
        for field, value in kwargs.items():
            if field in allowed_fields:
                if field == 'config':
                    value = SchemaValidator.validate_column_config(column.field_type, value)
                setattr(column, field, value)
        
        column.save()
        return column
    
    @staticmethod
    def delete_column(column: TableColumn) -> None:
        """
        Delete a column and remove its data from all records.
        """
        slug = column.slug
        table = column.table
        
        with transaction.atomic():
            # Remove field from all records
            records = table.records.all()
            for record in records:
                if slug in record.data:
                    del record.data[slug]
                    record.save(update_fields=['data'])
            
            column.delete()
    
    @staticmethod
    def reorder_columns(table: TableDefinition, column_orders: list[dict]) -> None:
        """
        Reorder columns in a table.
        
        Args:
            table: The table
            column_orders: List of {id: column_id, order: new_order}
        """
        with transaction.atomic():
            for item in column_orders:
                TableColumn.objects.filter(
                    id=item['id'],
                    table=table
                ).update(order=item['order'])


class RecordService:
    """
    Service for managing table records.
    """
    
    @staticmethod
    def create_record(
        table: TableDefinition,
        data: dict,
        owner=None,
        status: str = RecordStatus.DRAFT,
        request=None,
    ) -> TableRecord:
        """
        Create a new record in a table.
        """
        columns = list(table.columns.all())
        validator = DataValidator(columns)
        
        # Validate and clean data
        validated_data = validator.validate(data)
        
        # Apply default values for missing fields
        for column in columns:
            if column.slug not in validated_data and column.config.get('default') is not None:
                validated_data[column.slug] = column.config['default']
        
        record = TableRecord(
            tenant=table.tenant,  # Use tenant from table
            table=table,
            data=validated_data,
            owner=owner,
            status=status,
        )
        
        # Attach request for audit logging
        if request:
            record._request = request
        
        record.save()
        return record
    
    @staticmethod
    def update_record(
        record: TableRecord,
        data: dict,
        partial: bool = True,
        request=None,
    ) -> TableRecord:
        """
        Update a record.
        
        Args:
            record: Record to update
            data: New data (full or partial)
            partial: If True, merge with existing data
            request: Request object for audit
        """
        columns = list(record.table.columns.all())
        validator = DataValidator(columns)
        
        # Validate new data
        validated_data = validator.validate(data, partial=partial)
        
        # Merge or replace
        if partial:
            record.data = {**record.data, **validated_data}
        else:
            record.data = validated_data
        
        # Increment version
        record.version += 1
        
        # Attach request for audit logging
        if request:
            record._request = request
        
        record.save()
        return record
    
    @staticmethod
    def delete_record(record: TableRecord, hard_delete: bool = False, request=None) -> None:
        """
        Delete a record.
        
        Args:
            record: Record to delete
            hard_delete: If True, permanently delete
            request: Request object for audit
        """
        if hard_delete:
            record.delete()
        else:
            record.is_deleted = True
            record.deleted_at = timezone.now()
            if request:
                record._request = request
            record.save(update_fields=['is_deleted', 'deleted_at'])
    
    @staticmethod
    def restore_record(record: TableRecord, request=None) -> TableRecord:
        """Restore a soft-deleted record."""
        record.is_deleted = False
        record.deleted_at = None
        if request:
            record._request = request
        record.save(update_fields=['is_deleted', 'deleted_at'])
        return record
    
    @staticmethod
    def bulk_create(
        table: TableDefinition,
        data_list: list[dict],
        owner=None,
        request=None,
    ) -> tuple[list[TableRecord], list[dict]]:
        """
        Create multiple records at once.
        
        Returns:
            Tuple of (created_records, errors)
        """
        columns = list(table.columns.all())
        validator = DataValidator(columns)
        
        created_records = []
        errors = []
        
        with transaction.atomic():
            for idx, data in enumerate(data_list):
                try:
                    validated_data = validator.validate(data)
                    record = TableRecord.objects.create(
                        table=table,
                        data=validated_data,
                        owner=owner,
                    )
                    record._skip_audit = True  # Skip individual audit
                    created_records.append(record)
                except (ValidationError, MultiValidationError) as e:
                    errors.append({
                        'index': idx,
                        'data': data,
                        'error': str(e)
                    })
        
        # Create single bulk audit log
        if created_records and table.enable_audit:
            from .signals import create_bulk_audit_log
            create_bulk_audit_log(
                table=table,
                record_ids=[r.id for r in created_records],
                action=AuditAction.BULK_CREATE,
                user=owner,
                request=request,
                metadata={'count': len(created_records)}
            )
        
        return created_records, errors
    
    @staticmethod
    def bulk_update(
        table: TableDefinition,
        updates: list[dict],
        user=None,
        request=None,
    ) -> tuple[int, list[dict]]:
        """
        Update multiple records.
        
        Args:
            table: The table
            updates: List of {id: record_id, data: update_data}
            user: User performing the update
            request: Request for audit
        
        Returns:
            Tuple of (updated_count, errors)
        """
        columns = list(table.columns.all())
        validator = DataValidator(columns)
        
        updated_ids = []
        errors = []
        
        with transaction.atomic():
            for update in updates:
                record_id = update.get('id')
                data = update.get('data', {})
                
                try:
                    record = TableRecord.objects.get(
                        id=record_id,
                        table=table,
                        is_deleted=False
                    )
                    validated_data = validator.validate(data, partial=True)
                    record.data = {**record.data, **validated_data}
                    record.version += 1
                    record._skip_audit = True
                    record.save(update_fields=['data', 'version', 'updated_at'])
                    updated_ids.append(record_id)
                except TableRecord.DoesNotExist:
                    errors.append({
                        'id': record_id,
                        'error': 'Record not found'
                    })
                except (ValidationError, MultiValidationError) as e:
                    errors.append({
                        'id': record_id,
                        'error': str(e)
                    })
        
        # Create bulk audit log
        if updated_ids and table.enable_audit:
            from .signals import create_bulk_audit_log
            create_bulk_audit_log(
                table=table,
                record_ids=updated_ids,
                action=AuditAction.BULK_UPDATE,
                user=user,
                request=request,
                metadata={'count': len(updated_ids)}
            )
        
        return len(updated_ids), errors
    
    @staticmethod
    def bulk_delete(
        table: TableDefinition,
        record_ids: list[UUID],
        hard_delete: bool = False,
        user=None,
        request=None,
    ) -> int:
        """
        Delete multiple records.
        
        Returns the number of deleted records.
        """
        queryset = TableRecord.objects.filter(
            id__in=record_ids,
            table=table,
        )
        
        if hard_delete:
            count = queryset.count()
            queryset.delete()
        else:
            count = queryset.filter(is_deleted=False).update(
                is_deleted=True,
                deleted_at=timezone.now()
            )
        
        # Create bulk audit log
        if count > 0 and table.enable_audit:
            from .signals import create_bulk_audit_log
            create_bulk_audit_log(
                table=table,
                record_ids=list(record_ids),
                action=AuditAction.BULK_DELETE,
                user=user,
                request=request,
                metadata={'count': count, 'hard_delete': hard_delete}
            )
        
        return count


class FilterService:
    """
    Service for building and applying filters.
    """
    
    OPERATORS = {
        'eq': lambda field, val: Q(**{field: val}),
        'ne': lambda field, val: ~Q(**{field: val}),
        'gt': lambda field, val: Q(**{f'{field}__gt': val}),
        'gte': lambda field, val: Q(**{f'{field}__gte': val}),
        'lt': lambda field, val: Q(**{f'{field}__lt': val}),
        'lte': lambda field, val: Q(**{f'{field}__lte': val}),
        'contains': lambda field, val: Q(**{f'{field}__icontains': val}),
        'startswith': lambda field, val: Q(**{f'{field}__istartswith': val}),
        'endswith': lambda field, val: Q(**{f'{field}__iendswith': val}),
        'in': lambda field, val: Q(**{f'{field}__in': val}),
        'isnull': lambda field, val: Q(**{f'{field}__isnull': val}),
    }
    
    @classmethod
    def apply_filters(cls, queryset, filters: dict, table: TableDefinition):
        """
        Apply filters to a queryset.
        
        Args:
            queryset: Base queryset
            filters: Dict of {field: {operator: value}} or {field: value}
            table: Table definition for field metadata
        """
        columns = {col.slug: col for col in table.columns.all()}
        
        for field, condition in filters.items():
            if field in ('_search', '_sort', '_page', '_limit'):
                continue
            
            column = columns.get(field)
            if not column:
                continue
            
            if isinstance(condition, dict):
                for operator, value in condition.items():
                    if operator in cls.OPERATORS:
                        q = cls._build_json_filter(field, operator, value)
                        queryset = queryset.filter(q)
            else:
                q = cls._build_json_filter(field, 'eq', condition)
                queryset = queryset.filter(q)
        
        return queryset
    
    @classmethod
    def _build_json_filter(cls, field: str, operator: str, value: Any) -> Q:
        """
        Build a Q object for filtering JSONB data.
        """
        json_path = f'data__{field}'
        
        if operator == 'eq':
            return Q(**{json_path: value})
        elif operator == 'ne':
            return ~Q(**{json_path: value})
        elif operator == 'gt':
            return Q(**{f'{json_path}__gt': value})
        elif operator == 'gte':
            return Q(**{f'{json_path}__gte': value})
        elif operator == 'lt':
            return Q(**{f'{json_path}__lt': value})
        elif operator == 'lte':
            return Q(**{f'{json_path}__lte': value})
        elif operator == 'contains':
            return Q(**{f'{json_path}__icontains': value})
        elif operator == 'startswith':
            return Q(**{f'{json_path}__istartswith': value})
        elif operator == 'endswith':
            return Q(**{f'{json_path}__iendswith': value})
        elif operator == 'in':
            # For JSON arrays, use __contains
            return Q(**{f'{json_path}__in': value}) if not isinstance(value, list) else Q(**{f'{json_path}__in': value})
        elif operator == 'isnull':
            return Q(**{f'{json_path}__isnull': value})
        
        return Q()
    
    @classmethod
    def apply_search(cls, queryset, search: str, table: TableDefinition):
        """
        Apply full-text search across searchable columns.
        """
        if not search:
            return queryset
        
        searchable_columns = table.columns.filter(is_searchable=True)
        
        q = Q()
        for column in searchable_columns:
            q |= Q(**{f'data__{column.slug}__icontains': search})
        
        return queryset.filter(q)
    
    @classmethod
    def apply_sort(cls, queryset, sort: list[dict]):
        """
        Apply sorting to queryset.
        
        Args:
            sort: List of {field: str, order: 'asc'|'desc'}
        """
        if not sort:
            return queryset.order_by('-created_at')
        
        order_by = []
        for item in sort:
            field = item.get('field')
            order = item.get('order', 'asc')
            
            if field in ('created_at', 'updated_at', 'owner'):
                order_field = field
            else:
                order_field = f"data__{field}"
            
            if order == 'desc':
                order_field = f"-{order_field}"
            
            order_by.append(order_field)
        
        return queryset.order_by(*order_by)


class ViewService:
    """
    Service for managing custom views and saved filters.
    """
    
    @staticmethod
    def create_view(
        table: TableDefinition,
        user,
        name: str,
        columns: list = None,
        sort: list = None,
        filters: dict = None,
        is_default: bool = False,
        is_shared: bool = False,
    ) -> CustomView:
        """Create a custom view."""
        if is_default:
            # Remove default flag from other views
            CustomView.objects.filter(
                table=table,
                user=user,
                is_default=True
            ).update(is_default=False)
        
        return CustomView.objects.create(
            table=table,
            user=user,
            name=name,
            columns=columns or [],
            sort=sort or [],
            filters=filters or {},
            is_default=is_default,
            is_shared=is_shared,
        )
    
    @staticmethod
    def save_filter(
        table: TableDefinition,
        user,
        name: str,
        filter_config: dict,
        is_default: bool = False,
        is_shared: bool = False,
    ) -> SavedFilter:
        """Save a filter configuration."""
        if is_default:
            SavedFilter.objects.filter(
                table=table,
                user=user,
                is_default=True
            ).update(is_default=False)
        
        return SavedFilter.objects.create(
            table=table,
            user=user,
            name=name,
            filters=filter_config,
            is_default=is_default,
            is_shared=is_shared,
        )


class ExportService:
    """
    Service for exporting table data.
    """
    
    @staticmethod
    def export_csv(table: TableDefinition, records: list[TableRecord], columns: list[str] = None) -> str:
        """
        Export records to CSV format.
        
        Args:
            table: Table definition
            records: Records to export
            columns: Optional list of column slugs to include
        
        Returns:
            CSV string
        """
        table_columns = table.columns.all()
        if columns:
            table_columns = table_columns.filter(slug__in=columns)
        
        output = io.StringIO()
        writer = csv.writer(output)
        
        # Header row
        header = ['id', 'created_at', 'updated_at', 'owner']
        header.extend([col.name for col in table_columns])
        writer.writerow(header)
        
        # Data rows
        for record in records:
            row = [
                str(record.id),
                record.created_at.isoformat(),
                record.updated_at.isoformat(),
                str(record.owner_id) if record.owner_id else '',
            ]
            for col in table_columns:
                value = record.data.get(col.slug, '')
                if isinstance(value, (list, dict)):
                    import json
                    value = json.dumps(value, ensure_ascii=False)
                row.append(str(value) if value is not None else '')
            writer.writerow(row)
        
        return output.getvalue()
    
    @staticmethod
    def export_json(table: TableDefinition, records: list[TableRecord], columns: list[str] = None) -> list[dict]:
        """
        Export records to JSON format.
        """
        table_columns = table.columns.all()
        if columns:
            table_columns = table_columns.filter(slug__in=columns)
        
        column_slugs = [col.slug for col in table_columns]
        result = []
        
        for record in records:
            data = {
                'id': str(record.id),
                'created_at': record.created_at.isoformat(),
                'updated_at': record.updated_at.isoformat(),
                'owner': str(record.owner_id) if record.owner_id else None,
            }
            for slug in column_slugs:
                data[slug] = record.data.get(slug)
            result.append(data)
        
        return result


class ImportService:
    """
    Service for importing data into tables.
    """
    
    @staticmethod
    def import_csv(
        table: TableDefinition,
        csv_content: str,
        user,
        column_mapping: dict = None,
    ) -> tuple[list[TableRecord], list[dict]]:
        """
        Import records from CSV.
        
        Args:
            table: Target table
            csv_content: CSV string content
            user: User performing the import
            column_mapping: Optional {csv_column: table_column_slug}
        
        Returns:
            Tuple of (created_records, errors)
        """
        reader = csv.DictReader(io.StringIO(csv_content))
        data_list = []
        
        table_columns = {col.slug: col for col in table.columns.all()}
        
        for row in reader:
            record_data = {}
            for csv_col, value in row.items():
                # Apply column mapping if provided
                if column_mapping:
                    target_slug = column_mapping.get(csv_col)
                else:
                    target_slug = csv_col
                
                if target_slug and target_slug in table_columns:
                    record_data[target_slug] = value
            
            if record_data:
                data_list.append(record_data)
        
        # Use bulk create
        return RecordService.bulk_create(
            table=table,
            data_list=data_list,
            owner=user,
        )


class ComputedFieldService:
    """
    Service for computing field values based on expressions.
    """
    
    SAFE_FUNCTIONS = {
        'abs': abs,
        'round': round,
        'min': min,
        'max': max,
        'sum': sum,
        'len': len,
        'str': str,
        'int': int,
        'float': float,
        'bool': bool,
    }
    
    @classmethod
    def compute_field(cls, expression: str, data: dict, dependencies: list[str] = None) -> Any:
        """
        Compute a field value based on expression.
        
        Args:
            expression: Python expression (e.g., "price * quantity")
            data: Record data dict
            dependencies: List of field slugs the expression depends on
        
        Returns:
            Computed value
        """
        # Build safe namespace
        namespace = dict(cls.SAFE_FUNCTIONS)
        
        # Add record data to namespace
        for key, value in data.items():
            namespace[key] = value
        
        try:
            result = eval(expression, {"__builtins__": {}}, namespace)
            return result
        except Exception as e:
            return f"#ERROR: {str(e)}"
    
    @classmethod
    def compute_all_fields(cls, table: TableDefinition, record: TableRecord) -> dict:
        """
        Compute all computed fields for a record.
        
        Returns dict of {field_slug: computed_value}
        """
        computed = {}
        computed_columns = table.columns.filter(field_type=FieldType.COMPUTED)
        
        for column in computed_columns:
            config = column.config or {}
            expression = config.get('expression')
            if expression:
                computed[column.slug] = cls.compute_field(
                    expression=expression,
                    data=record.data,
                    dependencies=config.get('dependencies', [])
                )
        
        return computed


# Add missing import
from django.db import models