delphi-database/app/import_export/qdros_importer.py

"""
QDROS 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.qdro import QDRO
from app.models.files import File


class QdrosCSVImporter(BaseCSVImporter):
    """CSV importer for QDROS table"""
    
    @property
    def table_name(self) -> str:
        return "qdros"
        
    @property
    def required_fields(self) -> List[str]:
        return ["file_no"]  # Only file_no is strictly required
        
    @property
    def field_mapping(self) -> Dict[str, str]:
        """Map CSV headers to database field names"""
        return {
            "file_no": "file_no",
            "version": "version",
            "plan_id": "plan_id",
            # Legacy CSV fields
            "field1": "field1",
            "field2": "field2",
            "part": "part",
            "altp": "altp",
            "pet": "pet",
            "res": "res",
            # Case information
            "case_type": "case_type",
            "case_code": "case_code",
            "section": "section",
            "case_number": "case_number",
            # Dates
            "judgment_date": "judgment_date",
            "valuation_date": "valuation_date",
            "married_on": "married_on",
            # Award and venue
            "percent_awarded": "percent_awarded",
            "ven_city": "ven_city",
            "ven_cnty": "ven_cnty",
            "ven_st": "ven_st",
            # Document status dates
            "draft_out": "draft_out",
            "draft_apr": "draft_apr",
            "final_out": "final_out",
            # Additional fields
            "judge": "judge",
            "form_name": "form_name",
            "status": "status",
            "content": "content",
            "notes": "notes",
            "approval_status": "approval_status",
            "approved_date": "approved_date",
            "filed_date": "filed_date"
        }
        
    def create_model_instance(self, row_data: Dict[str, Any]) -> QDRO:
        """Create a Qdro instance from processed row data"""
        
        # Validate required fields
        if not row_data.get("file_no"):
            raise ImportValidationError("File number is required")
            
        # Validate foreign key exists (file number)
        file_exists = self.db_session.query(File).filter_by(file_no=row_data["file_no"]).first()
        if not file_exists:
            raise ImportValidationError(f"File number '{row_data['file_no']}' does not exist")
            
        # Parse date fields
        date_fields = [
            "judgment_date", "valuation_date", "married_on",
            "draft_out", "draft_apr", "final_out", "approved_date", "filed_date"
        ]
        
        parsed_dates = {}
        for field in date_fields:
            if row_data.get(field):
                try:
                    parsed_dates[field] = self.parse_date(row_data[field])
                except ValueError as e:
                    raise ImportValidationError(f"Invalid {field}: {e}")
            else:
                parsed_dates[field] = None
                
        # Validate state abbreviation length
        ven_st = row_data.get("ven_st", "")
        if ven_st and len(ven_st) > 2:
            self.result.add_warning(f"State abbreviation truncated: {ven_st}")
            ven_st = ven_st[:2]
            
        # Set default status if not provided
        status = row_data.get("status", "DRAFT")
        
        # Create instance
        qdro = QDRO(
            file_no=self.normalize_string(row_data["file_no"], 45),
            version=self.normalize_string(row_data.get("version", "01"), 10),
            plan_id=self.normalize_string(row_data.get("plan_id", ""), 45),
            # Legacy CSV fields
            field1=self.normalize_string(row_data.get("field1", ""), 100),
            field2=self.normalize_string(row_data.get("field2", ""), 100),
            part=self.normalize_string(row_data.get("part", ""), 100),
            altp=self.normalize_string(row_data.get("altp", ""), 100),
            pet=self.normalize_string(row_data.get("pet", ""), 100),
            res=self.normalize_string(row_data.get("res", ""), 100),
            # Case information
            case_type=self.normalize_string(row_data.get("case_type", ""), 45),
            case_code=self.normalize_string(row_data.get("case_code", ""), 45),
            section=self.normalize_string(row_data.get("section", ""), 45),
            case_number=self.normalize_string(row_data.get("case_number", ""), 100),
            # Dates
            **parsed_dates,
            # Award and venue
            percent_awarded=self.normalize_string(row_data.get("percent_awarded", ""), 100),
            ven_city=self.normalize_string(row_data.get("ven_city", ""), 50),
            ven_cnty=self.normalize_string(row_data.get("ven_cnty", ""), 50),
            ven_st=ven_st,
            # Additional fields
            judge=self.normalize_string(row_data.get("judge", ""), 100),
            form_name=self.normalize_string(row_data.get("form_name", ""), 200),
            status=self.normalize_string(status, 45),
            content=row_data.get("content", ""),  # Text field
            notes=row_data.get("notes", ""),  # Text field
            approval_status=self.normalize_string(row_data.get("approval_status", ""), 45)
        )
        
        return qdro