"""
API endpoints for CSV import functionality.
Allows uploading daily CSV files to append new invoices to the database.
"""
from fastapi import APIRouter, UploadFile, File, Depends, HTTPException
from sqlalchemy.orm import Session
from app.database import get_db
from app.models import Invoice
from app.services.csv_import_service import CSVImportService
from datetime import datetime
import tempfile
import logging
from typing import Dict

logger = logging.getLogger(__name__)

router = APIRouter(prefix="/api/csv", tags=["csv-import"])

@router.post("/import")
async def import_csv(
    file: UploadFile = File(...),
    db: Session = Depends(get_db)
) -> Dict:
    """
    Import invoices from a CSV file.
    Appends new invoices to the database (no duplicates by Invoice No.).
    
    Args:
        file: CSV file upload
        db: Database session
        
    Returns:
        Import summary with statistics
    """
    logger.info(f"📥 Received CSV upload: {file.filename}")
    
    # Validate file type
    if not file.filename.endswith('.csv'):
        raise HTTPException(status_code=400, detail="File must be a CSV")
    
    try:
        # Save uploaded file to temporary location
        with tempfile.NamedTemporaryFile(mode='wb', delete=False, suffix='.csv') as temp_file:
            content = await file.read()
            temp_file.write(content)
            temp_path = temp_file.name
        
        # Parse CSV
        csv_service = CSVImportService(branch_id="branch2")
        invoices = csv_service.parse_csv(temp_path)
        
        if not invoices:
            return {
                "success": True,
                "message": "No invoices found in CSV",
                "new_invoices": 0,
                "skipped_duplicates": 0,
                "errors": 0
            }
        
        # Get statistics
        stats = csv_service.get_statistics(invoices)
        
        # Check for existing invoices to avoid duplicates
        # Get the highest invoice_id currently in database
        max_id_result = db.query(db.func.max(Invoice.invoice_id)).scalar()
        next_id = (max_id_result or 0) + 1
        
        new_count = 0
        duplicate_count = 0
        error_count = 0
        
        for inv_data in invoices:
            try:
                # Check if invoice already exists (by invoice_no for this CSV-based approach)
                # Note: We can't use invoice_no as unique constraint because CSV doesn't have it
                # Instead, we'll check date + amount + company as a simple duplicate check
                existing = db.query(Invoice).filter(
                    Invoice.branch_id == inv_data["branch_id"],
                    Invoice.company_id == inv_data["company_id"],
                    Invoice.date_issued == inv_data["date_issued"],
                    Invoice.total_ex_tax == inv_data["total_ex_tax"]
                ).first()
                
                if existing:
                    duplicate_count += 1
                    continue
                
                # Insert new invoice
                invoice = Invoice(
                    invoice_id=next_id,
                    branch_id=inv_data["branch_id"],
                    company_id=inv_data["company_id"],
                    company_name=inv_data["company_name"],
                    date_issued=inv_data["date_issued"],
                    total_ex_tax=inv_data["total_ex_tax"],
                    is_paid=inv_data["is_paid"],
                    status=inv_data["status"],
                    customer=inv_data.get("customer"),
                    jobs=None,
                    stage=None
                )
                
                db.add(invoice)
                new_count += 1
                next_id += 1
                
                # Commit in batches
                if new_count % 100 == 0:
                    db.commit()
                    logger.info(f"  ✓ Imported {new_count} new invoices...")
                    
            except Exception as e:
                logger.error(f"❌ Error importing invoice: {e}")
                error_count += 1
                continue
        
        # Final commit
        db.commit()
        
        logger.info(f"✅ CSV import complete: {new_count} new, {duplicate_count} duplicates, {error_count} errors")
        
        return {
            "success": True,
            "message": f"Successfully imported {new_count} new invoices",
            "new_invoices": new_count,
            "skipped_duplicates": duplicate_count,
            "errors": error_count,
            "csv_stats": {
                "total_in_csv": stats["total_count"],
                "total_revenue": stats["total_revenue"],
                "date_range": stats["date_range"],
                "by_company": {
                    company_id: {
                        "name": data["name"],
                        "count": data["count"],
                        "revenue": data["revenue"]
                    }
                    for company_id, data in stats["by_company"].items()
                }
            }
        }
        
    except Exception as e:
        logger.error(f"❌ CSV import failed: {e}", exc_info=True)
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Import failed: {str(e)}")