"""
FILES CSV Importer
"""
from typing import Dict, List, Any
from datetime import date
from sqlalchemy.orm import Session

from .base import BaseCSVImporter, ImportValidationError
from app.models.files import File
from app.models.rolodex import Rolodex


class FilesCSVImporter(BaseCSVImporter):
    """CSV importer for FILES table"""
    
    @property
    def table_name(self) -> str:
        return "files"
        
    @property
    def required_fields(self) -> List[str]:
        return ["file_no", "id", "empl_num", "file_type", "opened", "status", "rate_per_hour"]
        
    @property
    def field_mapping(self) -> Dict[str, str]:
        """Map CSV headers to database field names"""
        return {
            "file_no": "file_no",
            "id": "id",
            "regarding": "regarding",
            "empl_num": "empl_num",
            "file_type": "file_type",
            "opened": "opened",
            "closed": "closed",
            "status": "status",
            "footer_code": "footer_code",
            "opposing": "opposing",
            "rate_per_hour": "rate_per_hour",
            # Financial balance fields (previously billed)
            "trust_bal_p": "trust_bal_p",
            "hours_p": "hours_p",
            "hourly_fees_p": "hourly_fees_p",
            "flat_fees_p": "flat_fees_p",
            "disbursements_p": "disbursements_p",
            "credit_bal_p": "credit_bal_p",
            "total_charges_p": "total_charges_p",
            "amount_owing_p": "amount_owing_p",
            # Financial balance fields (current totals)
            "trust_bal": "trust_bal",
            "hours": "hours",
            "hourly_fees": "hourly_fees",
            "flat_fees": "flat_fees",
            "disbursements": "disbursements",
            "credit_bal": "credit_bal",
            "total_charges": "total_charges",
            "amount_owing": "amount_owing",
            "transferable": "transferable",
            "memo": "memo"
        }
        
    def create_model_instance(self, row_data: Dict[str, Any]) -> File:
        """Create a Files instance from processed row data"""
        
        # Validate required fields
        required_checks = [
            ("file_no", "File number"),
            ("id", "Rolodex ID"),
            ("empl_num", "Employee number"),
            ("file_type", "File type"),
            ("opened", "Opened date"),
            ("status", "Status"),
            ("rate_per_hour", "Rate per hour")
        ]
        
        for field, display_name in required_checks:
            if not row_data.get(field):
                raise ImportValidationError(f"{display_name} is required")
                
        # Check for duplicate file number
        existing = self.db_session.query(File).filter_by(file_no=row_data["file_no"]).first()
        if existing:
            raise ImportValidationError(f"File number '{row_data['file_no']}' already exists")
            
        # Validate foreign key exists (rolodex ID)
        rolodex_exists = self.db_session.query(Rolodex).filter_by(id=row_data["id"]).first()
        if not rolodex_exists:
            raise ImportValidationError(f"Rolodex ID '{row_data['id']}' does not exist")
            
        # Parse dates
        opened_date = None
        closed_date = None
        
        try:
            opened_date = self.parse_date(row_data["opened"])
        except ValueError as e:
            raise ImportValidationError(f"Invalid opened date: {e}")
            
        if row_data.get("closed"):
            try:
                closed_date = self.parse_date(row_data["closed"])
            except ValueError as e:
                raise ImportValidationError(f"Invalid closed date: {e}")
                
        # Parse financial fields
        try:
            rate_per_hour = self.parse_float(row_data["rate_per_hour"])
            if rate_per_hour < 0:
                raise ImportValidationError("Rate per hour cannot be negative")
        except ValueError as e:
            raise ImportValidationError(f"Invalid rate per hour: {e}")
            
        # Parse all financial balance fields
        financial_fields = [
            "trust_bal_p", "hours_p", "hourly_fees_p", "flat_fees_p", 
            "disbursements_p", "credit_bal_p", "total_charges_p", "amount_owing_p",
            "trust_bal", "hours", "hourly_fees", "flat_fees",
            "disbursements", "credit_bal", "total_charges", "amount_owing", "transferable"
        ]
        
        financial_data = {}
        for field in financial_fields:
            try:
                financial_data[field] = self.parse_float(row_data.get(field, "0"))
            except ValueError as e:
                raise ImportValidationError(f"Invalid {field}: {e}")
                
        # Create instance
        files = File(
            file_no=self.normalize_string(row_data["file_no"], 45),
            id=self.normalize_string(row_data["id"], 80),
            regarding=row_data.get("regarding", ""),  # Text field
            empl_num=self.normalize_string(row_data["empl_num"], 10),
            file_type=self.normalize_string(row_data["file_type"], 45),
            opened=opened_date,
            closed=closed_date,
            status=self.normalize_string(row_data["status"], 45),
            footer_code=self.normalize_string(row_data.get("footer_code", ""), 45),
            opposing=self.normalize_string(row_data.get("opposing", ""), 80),
            rate_per_hour=rate_per_hour,
            memo=row_data.get("memo", ""),  # Text field
            **financial_data  # Unpack all financial fields
        )
        
        return files