backend/app/services/sync.py

from sqlalchemy.orm import Session
from datetime import datetime
from app.models.tender import TenderModel
from app.models.oc import OCModel
from app.services.mercado_publico import fetch_tenders, get_tender_by_code
from app.services.mercado_publico_oc import get_ocs_by_date
import json

async def sync_tenders_to_db(db: Session, keyword: str = None):
    """
    Fetches real tenders from Mercado Público API and saves them.
    """
    print(f"[Sync] Starting REAL synchronization... keyword={keyword}")
    
    try:
        api_tenders = await fetch_tenders(keyword=keyword)
        if not api_tenders:
            print("[Sync] No active tenders found for today in the API.")
            return {"new": 0, "updated": 0, "message": "No new tenders found"}
        
        print(f"[Sync] API returned {len(api_tenders)} real tenders for processing.")
    except Exception as e:
        print(f"[Sync] API error: {e}")
        return {"new": 0, "updated": 0, "message": f"API Error: {str(e)}"}
    
    count_new = 0
    count_updated = 0
    
    # Deduplicate API results by code to avoid IntegrityError within the same batch
    seen_codes = set()
    unique_tenders = []
    for t in api_tenders:
        if t.code not in seen_codes:
            seen_codes.add(t.code)
            unique_tenders.append(t)
    
    for api_t in unique_tenders:
        # Check if exists
        db_tender = db.query(TenderModel).filter(TenderModel.code == api_t.code).first()
        
        # Helper to parse dates
        def parse_dt(dt_str):
            if not dt_str: return None
            try:
                # Handle Z and other common formats
                clean_str = dt_str.replace("Z", "").split(".")[0]
                return datetime.fromisoformat(clean_str)
            except:
                return None

        # Convert Pydantic model to dict for DB
        tender_data = {
            "code": api_t.code,
            "name": api_t.name,
            "buyer": api_t.buyer,
            "buyer_region": api_t.buyer_region,
            "status": api_t.status,
            "status_code": str(api_t.status_code) if api_t.status_code else None,
            "type": api_t.type,
            "currency": api_t.currency,
            "closing_date": parse_dt(api_t.closing_date) if isinstance(api_t.closing_date, str) else api_t.closing_date,
            "publication_date": parse_dt(api_t.publication_date) if isinstance(api_t.publication_date, str) else api_t.publication_date,
            "description": api_t.description,
            "estimated_amount": api_t.estimated_amount,
            "source": api_t.source,
            "region": api_t.region,
            "sector": api_t.sector,
            "items": [item.model_dump() for item in api_t.items] if api_t.items else [],
            "attachments": api_t.attachments,
            "evaluation_criteria": api_t.evaluation_criteria,
            "contract_duration": api_t.contract_duration
        }
        
        if db_tender:
            # Update existing
            for key, value in tender_data.items():
                setattr(db_tender, key, value)
            count_updated += 1
        else:
            # Create new
            new_tender = TenderModel(**tender_data)
            db.add(new_tender)
            count_new += 1
            
    db.commit()
    print(f"[Sync] Finished. New: {count_new}, Updated: {count_updated}")
    return {"new": count_new, "updated": count_updated}

async def sync_purchase_orders_to_db(db: Session, date: str = None, status: str = "todos"):
    """
    Fetches purchase orders from Mercado Público and saves them in the local database.
    """
    if not date:
        date = datetime.now().strftime("%d%m%Y")

    try:
        api_orders = await get_ocs_by_date(date, status)
        if not api_orders:
            print(f"[Sync OC] No purchase orders found for date={date} status={status}")
            return {"new": 0, "updated": 0, "message": "No purchase orders found"}
    except Exception as e:
        print(f"[Sync OC] API error: {e}")
        return {"new": 0, "updated": 0, "message": f"API Error: {str(e)}"}

    count_new = 0
    count_updated = 0
    seen_codes = set()
    for oc in api_orders:
        if oc.code in seen_codes:
            continue
        seen_codes.add(oc.code)

        db_oc = db.query(OCModel).filter(OCModel.code == oc.code).first()

        oc_data = {
            "code": oc.code,
            "name": oc.name,
            "status": oc.status,
            "status_code": oc.status_code,
            "buyer": oc.buyer,
            "buyer_rut": oc.buyer_rut,
            "provider": oc.provider,
            "provider_rut": oc.provider_rut,
            "date_creation": oc.date_creation,
            "total_amount": oc.total_amount,
            "currency": oc.currency,
            "type": oc.type,
            "items": [item.model_dump() for item in oc.items] if oc.items else [],
            "raw_data": oc.raw_data,
        }

        if db_oc:
            for key, value in oc_data.items():
                setattr(db_oc, key, value)
            count_updated += 1
        else:
            new_oc = OCModel(**oc_data)
            db.add(new_oc)
            count_new += 1

    db.commit()
    print(f"[Sync OC] Finished. New: {count_new}, Updated: {count_updated}")
    return {"new": count_new, "updated": count_updated}


def clean_expired_tenders(db: Session):
    """
    Removes tenders where closing_date is in the past.
    """
    now = datetime.now()
    expired = db.query(TenderModel).filter(TenderModel.closing_date < now).delete()
    db.commit()
    print(f"[Sync] Cleaned {expired} expired tenders.")
    return expired