"""
Dimensional Code Generation Service (Phase 2)

Handles dynamic generation of dimensional accounting code instances from templates.
Supports pattern-based substitution, validation, and batch operations.
"""

import uuid
import re
from typing import Dict, List, Optional, Any, Tuple
from datetime import datetime, date
from sqlalchemy.orm import Session
from sqlalchemy import text, and_, or_, func

from app.models.accounting import AccountingCode, AccountingCodeInstance, JournalEntry
from app.models.accounting_dimensions import AccountingDimension, AccountingDimensionValue, AccountingDimensionAssignment


class DimensionalCodeService:
    """
    Service for generating and managing dimensional accounting code instances.

    Pattern Syntax:
    - {dimension_name}: Replaced with dimension value code
    - {DIMENSION_NAME}: Replaced with uppercase dimension value code
    - {dimension_name:format}: Apply format (e.g., {branch:3} for 3-char padding)

    Example:
    Base code: "4000" (Sales Revenue)
    Template pattern: {
        "code_pattern": "4000-{branch}-{department}",
        "name_pattern": "Sales Revenue - {branch_name} - {department_name}",
        "required_dimensions": ["branch", "department"]
    }

    Generated instance for Branch=HQ, Dept=IT:
    - Code: "4000-HQ-IT"
    - Name: "Sales Revenue - Headquarters - IT Department"
    """

    def __init__(self, db: Session):
        self.db = db

    def parse_pattern(self, pattern: str, dimension_values: Dict[str, Any]) -> str:
        """
        Parse a pattern string and substitute dimension values.

        Args:
            pattern: Pattern string with {dimension} placeholders
            dimension_values: Dict mapping dimension names to their values

        Returns:
            Parsed string with substitutions applied

        Example:
            pattern = "4000-{branch}-{dept:2}"
            dimension_values = {"branch": "HQ", "dept": "IT"}
            result = "4000-HQ-IT"
        """
        if not pattern:
            return ""

        result = pattern

        # Find all placeholders: {name} or {name:format}
        placeholders = re.findall(r'\{([^}]+)\}', pattern)

        for placeholder in placeholders:
            # Parse placeholder: name or name:format
            parts = placeholder.split(':')
            dim_name = parts[0].strip()
            format_spec = parts[1].strip() if len(parts) > 1 else None

            # Get value from dimension_values (support nested keys)
            value = dimension_values.get(dim_name)
            if value is None:
                # Try with _code suffix
                value = dimension_values.get(f"{dim_name}_code")
            if value is None:
                # Try with _name suffix
                value = dimension_values.get(f"{dim_name}_name")

            if value is None:
                raise ValueError(f"Missing required dimension value: {dim_name}")

            # Apply formatting if specified
            if format_spec:
                if format_spec.isdigit():
                    # Padding: {branch:3} -> pad to 3 chars
                    value = str(value).ljust(int(format_spec), '0')
                elif format_spec.lower() == 'upper':
                    value = str(value).upper()
                elif format_spec.lower() == 'lower':
                    value = str(value).lower()

            # Replace placeholder
            result = result.replace(f"{{{placeholder}}}", str(value))

        return result

    def validate_template(self, template: AccountingCode) -> Tuple[bool, Optional[str]]:
        """
        Validate that an accounting code can be used as a dimensional template.

        Returns:
            (is_valid, error_message)
        """
        if not template.is_dimensional_template:
            return False, "Account is not marked as a dimensional template"

        if not template.dimension_pattern:
            return False, "Template has no dimension pattern defined"

        pattern = template.dimension_pattern

        # Check required fields in pattern
        required_fields = ['code_pattern', 'required_dimensions']
        for field in required_fields:
            if field not in pattern:
                return False, f"Missing required pattern field: {field}"

        # Validate required_dimensions is a list
        if not isinstance(pattern['required_dimensions'], list):
            return False, "required_dimensions must be a list"

        if len(pattern['required_dimensions']) == 0:
            return False, "At least one required dimension must be specified"

        return True, None

    def generate_instance(
        self,
        base_code_id: str,
        dimension_values: Dict[str, Any],
        template_id: Optional[str] = None,
        commit: bool = True
    ) -> AccountingCodeInstance:
        """
        Generate a single dimensional code instance.

        Args:
            base_code_id: ID of the base AccountingCode
            dimension_values: Dict with dimension assignments (e.g., {"branch": "HQ", "department": "IT"})
            template_id: Optional template code ID (defaults to base_code_id)
            commit: Whether to commit the transaction

        Returns:
            Created AccountingCodeInstance

        Raises:
            ValueError: If validation fails or instance already exists
        """
        # Load base code
        base_code = self.db.query(AccountingCode).filter_by(id=base_code_id).first()
        if not base_code:
            raise ValueError(f"Base code not found: {base_code_id}")

        # Load template (default to base code if not specified)
        template = base_code
        if template_id and template_id != base_code_id:
            template = self.db.query(AccountingCode).filter_by(id=template_id).first()
            if not template:
                raise ValueError(f"Template not found: {template_id}")

        # Validate template
        is_valid, error = self.validate_template(template)
        if not is_valid:
            raise ValueError(f"Invalid template: {error}")

        pattern = template.dimension_pattern

        # Check required dimensions
        for dim in pattern['required_dimensions']:
            if dim not in dimension_values and f"{dim}_code" not in dimension_values:
                raise ValueError(f"Missing required dimension: {dim}")

        # Parse patterns
        instance_code = self.parse_pattern(pattern['code_pattern'], dimension_values)
        instance_name = self.parse_pattern(
            pattern.get('name_pattern', f"{base_code.name} ({instance_code})"),
            dimension_values
        )

        # Check for duplicate instance code
        existing = self.db.query(AccountingCodeInstance).filter_by(
            instance_code=instance_code
        ).first()
        if existing:
            raise ValueError(f"Instance with code {instance_code} already exists")

        # Create instance
        instance = AccountingCodeInstance(
            id=str(uuid.uuid4()),
            base_code_id=base_code_id,
            created_from_template_id=template_id or base_code_id,
            instance_code=instance_code,
            name=instance_name,
            account_type=base_code.account_type,
            category=base_code.category,
            reporting_tag=base_code.reporting_tag,
            branch_id=dimension_values.get('branch_id'),
            dimension_values=dimension_values,
            created_at=datetime.utcnow(),
            updated_at=datetime.utcnow()
        )

        self.db.add(instance)

        if commit:
            self.db.commit()
            self.db.refresh(instance)

        return instance

    def generate_instances_batch(
        self,
        base_code_id: str,
        dimension_value_sets: List[Dict[str, Any]],
        template_id: Optional[str] = None
    ) -> List[AccountingCodeInstance]:
        """
        Generate multiple dimensional code instances in a single transaction.

        Args:
            base_code_id: ID of the base AccountingCode
            dimension_value_sets: List of dimension value dicts
            template_id: Optional template code ID

        Returns:
            List of created AccountingCodeInstance objects
        """
        instances = []

        for dimension_values in dimension_value_sets:
            try:
                instance = self.generate_instance(
                    base_code_id=base_code_id,
                    dimension_values=dimension_values,
                    template_id=template_id,
                    commit=False
                )
                instances.append(instance)
            except ValueError as e:
                # Log error but continue with other instances
                print(f"Failed to generate instance for {dimension_values}: {e}")

        if instances:
            self.db.commit()
            for instance in instances:
                self.db.refresh(instance)

        return instances

    def get_template_list(self, include_inactive: bool = False) -> List[AccountingCode]:
        """
        Get all dimensional templates.

        Args:
            include_inactive: Whether to include inactive templates

        Returns:
            List of AccountingCode marked as templates
        """
        query = self.db.query(AccountingCode).filter(
            AccountingCode.is_dimensional_template == True
        )

        # Note: is_active field doesn't exist yet in schema
        # if not include_inactive:
        #     query = query.filter(AccountingCode.is_active == True)

        return query.order_by(AccountingCode.code).all()

    def get_instances_for_base_code(
        self,
        base_code_id: str,
        dimension_filters: Optional[Dict[str, Any]] = None
    ) -> List[AccountingCodeInstance]:
        """
        Get all instances generated from a base code.

        Args:
            base_code_id: ID of the base AccountingCode
            dimension_filters: Optional dict to filter by dimension values (uses JSON path query)

        Returns:
            List of AccountingCodeInstance objects
        """
        query = self.db.query(AccountingCodeInstance).filter(
            AccountingCodeInstance.base_code_id == base_code_id
        )

        # Apply dimension filters using PostgreSQL JSON operators
        if dimension_filters:
            for key, value in dimension_filters.items():
                # Use JSONB containment operator @>
                filter_json = {key: value}
                query = query.filter(
                    AccountingCodeInstance.dimension_values.op('@>')(str(filter_json))
                )

        return query.order_by(AccountingCodeInstance.instance_code).all()

    def get_dimensional_balances(
        self,
        base_code_id: str,
        as_of_date: Optional[date] = None,
        dimension_types: Optional[List[str]] = None
    ) -> List[Dict[str, Any]]:
        """
        Return debit, credit, and net balances grouped by dimension value for a base account.
        """
        if as_of_date is None:
            as_of_date = date.today()

        query = self.db.query(
            AccountingDimension.dimension_type.label('dimension_type'),
            AccountingDimensionValue.id.label('dimension_value_id'),
            AccountingDimensionValue.code.label('dimension_value_code'),
            AccountingDimensionValue.name.label('dimension_value_name'),
            func.coalesce(func.sum(JournalEntry.debit_amount), 0).label('total_debits'),
            func.coalesce(func.sum(JournalEntry.credit_amount), 0).label('total_credits')
        ).select_from(JournalEntry).join(
            AccountingDimensionAssignment, AccountingDimensionAssignment.journal_entry_id == JournalEntry.id
        ).join(
            AccountingDimensionValue, AccountingDimensionValue.id == AccountingDimensionAssignment.dimension_value_id
        ).join(
            AccountingDimension, AccountingDimension.id == AccountingDimensionAssignment.dimension_id
        ).filter(
            JournalEntry.accounting_code_id == base_code_id,
            JournalEntry.date <= as_of_date
        )

        if dimension_types:
            query = query.filter(AccountingDimension.dimension_type.in_(dimension_types))

        query = query.group_by(
            AccountingDimension.dimension_type,
            AccountingDimensionValue.id,
            AccountingDimensionValue.code,
            AccountingDimensionValue.name
        ).order_by(AccountingDimension.dimension_type, AccountingDimensionValue.code)

        results = []
        for row in query.all():
            net_balance = float(row.total_debits or 0) - float(row.total_credits or 0)
            results.append({
                'dimension_type': row.dimension_type,
                'dimension_value_id': row.dimension_value_id,
                'dimension_value_code': row.dimension_value_code,
                'dimension_value_name': row.dimension_value_name,
                'total_debits': float(row.total_debits or 0),
                'total_credits': float(row.total_credits or 0),
                'net_balance': net_balance
            })

        return results

    def get_dimensional_transactions(
        self,
        base_code_id: str,
        dimension_value_id: Optional[str] = None,
        dimension_type: Optional[str] = None,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None,
        limit: int = 100,
        offset: int = 0
    ) -> List[Dict[str, Any]]:
        """
        Get detailed journal entry transactions for a dimensional code with dimension assignments.

        Args:
            base_code_id: Base accounting code ID
            dimension_value_id: Optional filter by specific dimension value
            dimension_type: Optional filter by dimension type
            start_date: Optional start date filter
            end_date: Optional end date filter
            limit: Maximum number of records to return
            offset: Number of records to skip

        Returns:
            List of transaction details with dimension assignments
        """
        from app.models.accounting import JournalEntry, AccountingEntry
        from app.models.accounting_dimensions import (
            AccountingDimensionAssignment,
            AccountingDimensionValue,
            AccountingDimension
        )

        # Build base query
        query = self.db.query(
            JournalEntry,
            AccountingEntry.particulars,
            AccountingCode.code.label('account_code'),
            AccountingCode.name.label('account_name')
        ).outerjoin(
            AccountingEntry,
            JournalEntry.accounting_entry_id == AccountingEntry.id
        ).join(
            AccountingCode,
            JournalEntry.accounting_code_id == AccountingCode.id
        ).filter(
            JournalEntry.accounting_code_id == base_code_id
        )

        # Apply date filters
        if start_date:
            query = query.filter(JournalEntry.date >= start_date)
        if end_date:
            query = query.filter(JournalEntry.date <= end_date)

        # If dimension filtering is requested, join with dimension assignments
        if dimension_value_id or dimension_type:
            query = query.join(
                AccountingDimensionAssignment,
                JournalEntry.id == AccountingDimensionAssignment.journal_entry_id
            )

            if dimension_value_id:
                query = query.filter(
                    AccountingDimensionAssignment.dimension_value_id == dimension_value_id
                )

            if dimension_type:
                query = query.join(
                    AccountingDimensionValue,
                    AccountingDimensionAssignment.dimension_value_id == AccountingDimensionValue.id
                ).join(
                    AccountingDimension,
                    AccountingDimensionValue.dimension_id == AccountingDimension.id
                ).filter(
                    AccountingDimension.dimension_type == dimension_type
                )

        # Order by date descending, apply pagination
        query = query.order_by(JournalEntry.date.desc(), JournalEntry.id.desc())
        query = query.offset(offset).limit(limit)

        results = []
        for row in query.all():
            journal_entry = row[0] if isinstance(row, tuple) else row
            particulars = row[1] if isinstance(row, tuple) and len(row) > 1 else None
            account_code = row[2] if isinstance(row, tuple) and len(row) > 2 else journal_entry.accounting_code.code
            account_name = row[3] if isinstance(row, tuple) and len(row) > 3 else journal_entry.accounting_code.name

            # Get all dimension assignments for this journal entry
            dimension_assignments = self.db.query(
                AccountingDimensionAssignment,
                AccountingDimensionValue.code,
                AccountingDimensionValue.name,
                AccountingDimension.dimension_type,
                AccountingDimension.name.label('dimension_name')
            ).join(
                AccountingDimensionValue,
                AccountingDimensionAssignment.dimension_value_id == AccountingDimensionValue.id
            ).join(
                AccountingDimension,
                AccountingDimensionValue.dimension_id == AccountingDimension.id
            ).filter(
                AccountingDimensionAssignment.journal_entry_id == journal_entry.id
            ).all()

            dimension_data = []
            for assignment, dim_val_code, dim_val_name, dim_type, dim_name in dimension_assignments:
                dimension_data.append({
                    'dimension_type': dim_type,
                    'dimension_name': dim_name,
                    'dimension_value_id': assignment.dimension_value_id,
                    'dimension_value_code': dim_val_code,
                    'dimension_value_name': dim_val_name,
                    'percentage': float(assignment.percentage) if assignment.percentage else 100.0
                })

            results.append({
                'journal_entry_id': journal_entry.id,
                'accounting_entry_id': journal_entry.accounting_entry_id,
                'date': journal_entry.date,
                'reference': journal_entry.reference,
                'description': journal_entry.description,
                'particulars': particulars,
                'debit_amount': float(journal_entry.debit_amount or 0),
                'credit_amount': float(journal_entry.credit_amount or 0),
                'dimension_assignments': dimension_data,
                'account_code': account_code,
                'account_name': account_name
            })

        return results

    def delete_instance(self, instance_id: str) -> bool:
        """
        Delete a dimensional code instance.

        Args:
            instance_id: ID of the instance to delete

        Returns:
            True if deleted, False if not found
        """
        instance = self.db.query(AccountingCodeInstance).filter_by(id=instance_id).first()
        if not instance:
            return False

        self.db.delete(instance)
        self.db.commit()
        return True

    def regenerate_instances_for_template(
        self,
        template_id: str,
        dimension_value_sets: List[Dict[str, Any]],
        clear_existing: bool = False
    ) -> List[AccountingCodeInstance]:
        """
        Regenerate all instances for a template.

        Args:
            template_id: ID of the template AccountingCode
            dimension_value_sets: List of dimension value combinations to generate
            clear_existing: If True, delete all existing instances first

        Returns:
            List of created instances
        """
        template = self.db.query(AccountingCode).filter_by(id=template_id).first()
        if not template:
            raise ValueError(f"Template not found: {template_id}")

        if clear_existing:
            # Delete all instances created from this template
            self.db.query(AccountingCodeInstance).filter_by(
                created_from_template_id=template_id
            ).delete()
            self.db.commit()

        # Generate new instances
        return self.generate_instances_batch(
            base_code_id=template_id,
            dimension_value_sets=dimension_value_sets,
            template_id=template_id
        )

    def get_dimension_combinations(
        self,
        dimension_names: List[str]
    ) -> List[Dict[str, Any]]:
        """
        Generate all possible combinations of dimension values.

        Args:
            dimension_names: List of dimension names to combine

        Returns:
            List of dicts with all combinations

        Example:
            dimension_names = ["branch", "department"]
            Returns: [
                {"branch": "HQ", "branch_id": "...", "department": "IT", "dept_id": "..."},
                {"branch": "HQ", "branch_id": "...", "department": "Sales", "dept_id": "..."},
                ...
            ]
        """
        from itertools import product

        # Get all values for each dimension
        dimension_value_lists = {}

        for dim_name in dimension_names:
            dimension = self.db.query(AccountingDimension).filter_by(
                code=dim_name
            ).first()

            if not dimension:
                raise ValueError(f"Dimension not found: {dim_name}")

            values = self.db.query(AccountingDimensionValue).filter_by(
                dimension_id=dimension.id,
                is_active=True
            ).all()

            dimension_value_lists[dim_name] = values

        # Generate all combinations
        combinations = []
        dimension_keys = list(dimension_value_lists.keys())
        value_lists = [dimension_value_lists[k] for k in dimension_keys]

        for combo in product(*value_lists):
            combo_dict = {}
            for i, dim_name in enumerate(dimension_keys):
                value_obj = combo[i]
                combo_dict[dim_name] = value_obj.code
                combo_dict[f"{dim_name}_code"] = value_obj.code
                combo_dict[f"{dim_name}_name"] = value_obj.name
                combo_dict[f"{dim_name}_id"] = value_obj.id

            combinations.append(combo_dict)

        return combinations

    def preview_instance(
        self,
        base_code_id: str,
        dimension_values: Dict[str, Any],
        template_id: Optional[str] = None
    ) -> Dict[str, Any]:
        """
        Preview what an instance would look like without creating it.

        Args:
            base_code_id: ID of the base AccountingCode
            dimension_values: Dict with dimension assignments
            template_id: Optional template code ID

        Returns:
            Dict with preview data (code, name, etc.)
        """
        base_code = self.db.query(AccountingCode).filter_by(id=base_code_id).first()
        if not base_code:
            raise ValueError(f"Base code not found: {base_code_id}")

        template = base_code
        if template_id and template_id != base_code_id:
            template = self.db.query(AccountingCode).filter_by(id=template_id).first()
            if not template:
                raise ValueError(f"Template not found: {template_id}")

        is_valid, error = self.validate_template(template)
        if not is_valid:
            raise ValueError(f"Invalid template: {error}")

        pattern = template.dimension_pattern

        instance_code = self.parse_pattern(pattern['code_pattern'], dimension_values)
        instance_name = self.parse_pattern(
            pattern.get('name_pattern', f"{base_code.name} ({instance_code})"),
            dimension_values
        )

        return {
            'instance_code': instance_code,
            'name': instance_name,
            'account_type': base_code.account_type,
            'category': base_code.category,
            'reporting_tag': base_code.reporting_tag,
            'dimension_values': dimension_values,
            'base_code_id': base_code_id,
            'template_id': template_id or base_code_id
        }
