from fastapi import APIRouter, Depends, HTTPException
from pydantic import BaseModel
from typing import Optional
from sqlmodel import Session, text
from sqlalchemy import inspect
from db import get_session

router = APIRouter(prefix="/api/v1", tags=["Referral"])

class SaveReferralSourceRequest(BaseModel):
    reference_number: Optional[str] = None
    quote_id: Optional[str] = None
    lead_source: str
    quote_type: Optional[str] = None

@router.post("/save-referral-source")
def save_referral_source(
    payload: SaveReferralSourceRequest,
    session: Session = Depends(get_session)
):
    ref = payload.reference_number
    qid = payload.quote_id
    source = payload.lead_source

    if not ref and not qid:
        raise HTTPException(status_code=400, detail="Either reference_number or quote_id must be provided")

    updated = False
    
    # Get all tables that contain lead_source column dynamically
    inspector = inspect(session.bind or session.get_bind())
    all_table_names = inspector.get_table_names()

    for table_name in all_table_names:
        cols = [c['name'] for c in inspector.get_columns(table_name)]
        if 'lead_source' not in cols:
            continue

        try:
            # 1. Update by transaction_no if column exists and ref is provided
            if ref and 'transaction_no' in cols:
                stmt = text(f"UPDATE {table_name} SET lead_source = :source WHERE transaction_no = :ref")
                res = session.execute(stmt, {"source": source, "ref": ref})
                if res.rowcount and res.rowcount > 0:
                    updated = True

            # 2. Update by quote_reference_id if column exists and ref is provided
            if ref and not updated and 'quote_reference_id' in cols:
                stmt = text(f"UPDATE {table_name} SET lead_source = :source WHERE quote_reference_id = :ref")
                res = session.execute(stmt, {"source": source, "ref": ref})
                if res.rowcount and res.rowcount > 0:
                    updated = True

            # 3. Update by id if numeric ID provided or extracted
            target_id = qid if (qid and str(qid).isdigit()) else (ref if (ref and str(ref).isdigit()) else None)
            if target_id and not updated and 'id' in cols:
                stmt = text(f"UPDATE {table_name} SET lead_source = :source WHERE id = :qid")
                res = session.execute(stmt, {"source": source, "qid": int(target_id)})
                if res.rowcount and res.rowcount > 0:
                    updated = True
        except Exception as e:
            print(f"[save_referral_source] Table {table_name} error: {e}")
            continue

    if updated:
        session.commit()
        return {"status": "success", "message": "Referral source updated successfully", "lead_source": source}

    return {"status": "acknowledged", "message": "Referral source received", "lead_source": source}
