from datetime import datetime
from typing import List, Optional
from fastapi import APIRouter, Query, Depends
from sqlalchemy.orm import Session
from sqlalchemy import or_

from app.schemas.tender import Tender
from app.database import get_db
from app.models.tender import TenderModel
from app.services.sync import sync_tenders_to_db, clean_expired_tenders
from app.services.mercado_publico import (
    fetch_tenders,
    get_tender_by_code,
    get_tenders_by_date,
)
from app.models.company import CompanyProfileModel
import json

router = APIRouter()

@router.get("/tenders", response_model=List[Tender])
async def search_tender_opportunities(
    keyword: Optional[str] = None,
    buyer: Optional[str] = None,
    region: Optional[str] = None,
    provider_code: Optional[str] = Query(None, alias="provider_code"),
    org_code: Optional[str] = Query(None, alias="org_code"),
    status: Optional[str] = None,
    code: Optional[str] = None,
    date: Optional[str] = None,
    type_code: Optional[str] = Query(None, alias="type_code"),
    skip: int = 0,
    limit: int = 50,
    db: Session = Depends(get_db)
):
    # If a Mercado Público-specific query is requested, fetch live from the external API.
    if code:
        tender = await get_tender_by_code(code)
        return [tender] if tender else []

    if any([provider_code, org_code, status, date, type_code]) and not keyword:
        from app.services.mercado_publico import get_tenders_by_filters
        return await get_tenders_by_filters(
            date=date,
            status=status,
            type_code=type_code,
            org_code=org_code,
            provider_code=provider_code
        )

    if keyword:
        from app.services.mercado_publico import fetch_tenders
        return await fetch_tenders(keyword=keyword, date=date, type_code=type_code)

    # 1. Búsqueda en DB con paginación
    query = db.query(TenderModel)
    
    if keyword:
        search_filter = f"%{keyword}%"
        query = query.filter(
            or_(
                TenderModel.name.ilike(search_filter),
                TenderModel.code.ilike(search_filter),
                TenderModel.description.ilike(search_filter),
                TenderModel.buyer.ilike(search_filter),
                TenderModel.sector.ilike(search_filter),
                TenderModel.region.ilike(search_filter)
            )
        )
    
    if buyer:
        query = query.filter(TenderModel.buyer.ilike(f"%{buyer}%"))
    
    if region:
        query = query.filter(TenderModel.region.ilike(f"%{region}%"))
        
    # Ordenar por fecha de cierre (más próximas primero)
    results = query.order_by(TenderModel.closing_date.asc()).offset(skip).limit(limit).all()
    
    # 2. Si la DB está vacía o no hay resultados con los filtros actuales, 
    # y el usuario está haciendo una búsqueda general (sin keyword específica larga),
    # hacemos un intento de sincronización de las "activas de hoy".
    if not results:
        print(f"[Tenders] No results in DB. Triggering sync. keyword={keyword}")
        await sync_tenders_to_db(db, keyword=keyword)
        # Re-ejecutar consulta
        results = query.offset(skip).limit(limit).all()
        
    return results

@router.get("/tenders/count")
def get_tenders_count(db: Session = Depends(get_db)):
    """Devuelve el total de licitaciones en la base de datos."""
    return {"total": db.query(TenderModel).count()}

@router.post("/tenders/sync")
async def manual_sync(keyword: Optional[str] = None, db: Session = Depends(get_db)):
    return await sync_tenders_to_db(db, keyword=keyword)

@router.get("/tenders/scrape", response_model=List[Tender])
async def live_scrape(keyword: str):
    from app.services.scraper import scrape_compra_agil
    return await scrape_compra_agil(keyword)

@router.get("/tenders/recommendations", response_model=List[Tender])
async def get_recommended_tenders(db: Session = Depends(get_db)):
    """Busca licitaciones locales que coincidan con las keywords del perfil de empresa."""
    print("!!! RECOMMENDATION ENDPOINT CALLED !!!")
    profile = db.query(CompanyProfileModel).first()
    
    # Fallback absolute: if no profile or no data, just return the latest 10
    if not profile or not profile.keywords:
        print("No profile or keywords found, returning latest 10")
        return db.query(TenderModel).order_by(TenderModel.closing_date.desc()).limit(10).all()
    
    try:
        # Handle JSON or Comma-separated
        if profile.keywords.startswith("[") or profile.keywords.startswith("{"):
            keywords = json.loads(profile.keywords)
        else:
            keywords = [kw.strip() for kw in profile.keywords.split(",") if kw.strip()]
    except Exception as e:
        print(f"Keyword parse error: {e}")
        keywords = [profile.keywords] if profile.keywords else []

    print(f"Processing keywords: {keywords}")

    # Build filters (Case-insensitive)
    filters = []
    for kw in keywords:
        if not kw or len(kw) < 2: continue
        search_term = f"%{kw}%"
        filters.append(TenderModel.name.ilike(search_term))
        filters.append(TenderModel.description.ilike(search_term))
        filters.append(TenderModel.buyer.ilike(search_term))
        filters.append(TenderModel.sector.ilike(search_term))

    # If no valid filters, return latest
    if not filters:
        print("No valid filters generated, returning latest 10")
        return db.query(TenderModel).order_by(TenderModel.closing_date.desc()).limit(10).all()

    # Query with filters
    try:
        recommended = db.query(TenderModel).filter(or_(*filters)).order_by(TenderModel.closing_date.desc()).limit(15).all()
        print(f"Found {len(recommended)} recommended matches")
    except Exception as e:
        print(f"Query error: {e}")
        recommended = []
    
    # GUARANTEED FALLBACK: If nothing found or error, return the newest 10 tenders from DB
    if not recommended:
        print("No matches found, executing fallback to latest 10")
        recommended = db.query(TenderModel).order_by(TenderModel.closing_date.desc()).limit(10).all()
    elif len(recommended) < 5:
        print(f"Only {len(recommended)} found, padding with latest")
        existing_ids = [r.id for r in recommended]
        more = db.query(TenderModel).filter(TenderModel.id.not_in(existing_ids)).order_by(TenderModel.closing_date.desc()).limit(5).all()
        recommended.extend(more)

    return recommended