import os
import base64
import json
import re as _re_module
import traceback
from cryptography.fernet import Fernet
from fastapi import APIRouter, Depends, Query, HTTPException
from pydantic import BaseModel
from sqlmodel import Session, select, func
from sqlalchemy import extract
from typing import Dict, Any, List, Optional, Tuple
from datetime import datetime, timedelta, date as _date
import calendar

from db import get_session
from app.models.user import User

# Business Models
from app.models.BusinessInsuranceModel import BusinessInsuranceModel
from app.models.BusinessOwnerPolicyModel import BusinessOwnerPolicyModel
from app.models.Commercialautomodel import CommercialAutoModel
from app.models.CommercialPropertyModel import CommercialPropertyModel
from app.models.cyberliabilityinsurance import Cyberliability
from app.models.generalliabilityinsurance import GeneralLiability
from app.models.professionalliabilityinsurance import ProfessionalLiability
from app.models.WorkersCompensationModel import WorkersCompensationModel
from app.models.unifiedcommercialinsurance import Unifiedcommercial
from app.models.businessumbrellainsurance import Businessumbrella

# Personal Models
from app.models.personalautoinsurance import PersonalAutoInsurance
from app.models.homeinsurance import HomeInsurance
from app.models.RentersInsuranceModel import RentersInsuranceModel
from app.models.OceanMarineModel import OceanMarineModel
from app.models.UmbrellaInsuranceModel import UmbrellaInsuranceModel

# Life Models
from app.models.LifeInsuranceModel import LifeInsuranceModel
from app.models.grouplifeinsurance import GroupLife

# Agri Models
from app.models.AgricultureInsuranceModel import AgricultureInsuranceModel

# Health Models
from app.models.HealthInsuranceModel import HealthInsuranceModel
from app.models.GroupHealthModel import GroupHealthModel

# Annuity Models
from app.models.AnnuityInsuranceModel import AnnuityInsuranceModel

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

# ── Module Constants ──────────────────────────────────────────────────────────
DATE_FMT_LONG = "%B %d, %Y"
RENTERS_INSURANCE = "Renter's"
TERM_LIFE = "Term Life"
WHOLE_LIFE = "Whole Life"
UNIVERSAL_LIFE = "Universal Life"
INDEXED_UNIVERSAL_LIFE = "Indexed Universal Life"
INDEXED_UNIVERSAL = "Indexed Universal"
VARIABLE_UNIVERSAL_LIFE = "Variable Universal Life"
VARIABLE_UNIVERSAL = "Variable Universal"
COMMERCIAL_PROPERTY = "Commercial Property"
WORKERS_COMP = "Worker's Comp"
COMMERCIAL_AUTO = "Commercial Auto"
CYBER_LIABILITY = "Cyber Liability"
GENERAL_LIABILITY = "General Liability"
OCEAN_MARINE = "Ocean Marine"
GROUP_HEALTH = "Group Health"
GROUP_LIFE = "Group Life"
PROFESSIONAL_LIABILITY = "Professional Liability"
BUSINESS_INSURANCE = "Business Insurance"
IN_PROGRESS = "In Progress"

# ── Helper for parsing range_key string ───────────────────────────────────────
def _parse_range_key_str(range_key: str, now: datetime) -> Tuple[Optional[datetime], Optional[datetime]]:
    r = range_key.lower()
    if "all" in r:
        return None, None
    if "7" in r and "day" in r:
        return now - timedelta(days=7), now
    if "30" in r and "day" in r:
        return now - timedelta(days=30), now
    if "90" in r and "day" in r:
        return now - timedelta(days=90), now
    if "year" in r:
        return datetime(now.year, 1, 1), now

    if " - " in range_key:
        parts = range_key.split(" - ")
        try:
            s_dt = datetime.strptime(parts[0].strip(), DATE_FMT_LONG)
            e_dt = datetime.strptime(parts[1].strip(), DATE_FMT_LONG).replace(hour=23, minute=59, second=59)
            return s_dt, e_dt
        except Exception:
            pass

    return None, None

def parse_date_range(
    start_date: Optional[str] = None, 
    end_date: Optional[str] = None, 
    range_key: Optional[str] = None
) -> Tuple[Optional[datetime], Optional[datetime]]:
    now = datetime.now()
    if start_date and end_date:
        try:
            s_dt = datetime.strptime(start_date, "%Y-%m-%d")
            e_dt = datetime.strptime(end_date, "%Y-%m-%d").replace(hour=23, minute=59, second=59)
            return s_dt, e_dt
        except Exception:
            pass

    if not range_key:
        return None, None

    return _parse_range_key_str(str(range_key), now)

def get_trend(current_count: int, previous_count: int) -> str:
    if previous_count == 0:
        return "0%"
    change = ((current_count - previous_count) / previous_count) * 100
    sign = "+" if change >= 0 else ""
    return f"{sign}{change:.1f}%"

BUSINESS_MODELS = [
    BusinessInsuranceModel, BusinessOwnerPolicyModel, CommercialAutoModel,
    CommercialPropertyModel, Cyberliability, GeneralLiability,
    ProfessionalLiability, WorkersCompensationModel, Unifiedcommercial, Businessumbrella
]
PERSONAL_MODELS = [PersonalAutoInsurance, HomeInsurance, RentersInsuranceModel, OceanMarineModel, UmbrellaInsuranceModel]
PERSONAL_TABLE_MODELS = [HomeInsurance, RentersInsuranceModel, OceanMarineModel, UmbrellaInsuranceModel]
LIFE_MODELS = [LifeInsuranceModel, GroupLife]
AGRI_MODELS = [AgricultureInsuranceModel]
HEALTH_MODELS = [HealthInsuranceModel, GroupHealthModel]
ANNUITY_MODELS = [AnnuityInsuranceModel]

ALL_MODELS = BUSINESS_MODELS + PERSONAL_MODELS + LIFE_MODELS + AGRI_MODELS + HEALTH_MODELS + ANNUITY_MODELS

def get_record_datetime(r: Any) -> Optional[datetime]:
    dt = (
        getattr(r, 'created_at', None) or 
        getattr(r, 'submission_timestamp', None) or 
        getattr(r, 'submitted_at', None) or 
        getattr(r, 'effective_date', None) or 
        getattr(r, 'desired_policy_start_date', None) or 
        getattr(r, 'desired_policy_start', None) or 
        getattr(r, 'date', None)
    )
    if dt is not None:
        if isinstance(dt, datetime):
            return dt.replace(tzinfo=None)
        elif hasattr(dt, 'year'):
            return datetime(dt.year, dt.month, dt.day)
        elif isinstance(dt, str):
            try:
                return datetime.fromisoformat(dt.replace('Z', '')).replace(tzinfo=None)
            except Exception:
                pass
    return None

def get_record_premium(r: Any) -> float:
    for field in [
        'premium_amount', 
        'current_premium', 
        'premium_budget',
        'monthly_budget_premium', 
        'monthly_premium_current_plan',
        'initial_investment'
    ]:
        val = getattr(r, field, None)
        if val is not None:
            try:
                f_val = float(val)
                if 0 < f_val < 100000:
                    return f_val
            except Exception:
                pass
    return 33.56

def _count_quotes_for_models(
    db: Session,
    models: List[Any],
    s_dt: Optional[datetime],
    e_dt: Optional[datetime],
    prev_s_dt: datetime,
    prev_e_dt: datetime
) -> Tuple[int, str]:
    tot = 0
    curr_count = 0
    prev_count = 0
    for model in models:
        try:
            res = db.exec(select(model)).all()
            tot += len(res)
            for r in res:
                c_at = get_record_datetime(r)
                if c_at is not None:
                    if s_dt and e_dt:
                        if s_dt <= c_at <= e_dt:
                            curr_count += 1
                        elif prev_s_dt <= c_at < prev_e_dt:
                            prev_count += 1
                    else:
                        curr_count += 1
                else:
                    curr_count += 1
        except Exception as e:
            print(f"  [count_quotes] Error for model {model}: {e}")
    val = curr_count if (s_dt and e_dt and curr_count > 0) else tot
    return val, get_trend(curr_count, prev_count)


@router.get("/overview")
def get_dashboard_overview(
    start_date: Optional[str] = Query(None),
    end_date: Optional[str] = Query(None),
    range: Optional[str] = Query(None),
    db: Session = Depends(get_session)
) -> Dict[str, Any]:
    try:
        s_dt, e_dt = parse_date_range(start_date, end_date, range)
        now = datetime.now()

        if s_dt and e_dt:
            window_days = max(1, (e_dt - s_dt).days)
            prev_s_dt = s_dt - timedelta(days=window_days)
            prev_e_dt = s_dt
        else:
            prev_s_dt = now - timedelta(days=60)
            prev_e_dt = now - timedelta(days=30)

        all_users = db.exec(select(User)).all()
        customers = [u for u in all_users if getattr(u, 'role', None) in ["customer", "user"]]
        staff = [u for u in all_users if getattr(u, 'role', None) in ["staff", "agent", "admin"]]
        carriers = [u for u in all_users if getattr(u, 'role', None) == "carrier"]
        c_data = get_carriers_data(db)
        carrier_val = max(len(carriers), len(c_data.get('carriers', [])))
        
        ind_total, ind_trend = _count_quotes_for_models(db, PERSONAL_MODELS + LIFE_MODELS + HEALTH_MODELS + AGRI_MODELS, s_dt, e_dt, prev_s_dt, prev_e_dt)
        biz_total, biz_trend = _count_quotes_for_models(db, BUSINESS_MODELS, s_dt, e_dt, prev_s_dt, prev_e_dt)
        annuity_total, annuity_trend = _count_quotes_for_models(db, ANNUITY_MODELS, s_dt, e_dt, prev_s_dt, prev_e_dt)

        return {
            "totalUsers": {"value": len(customers), "change": get_trend(len(customers), 0)},
            "agencyStaff": {"value": len(staff), "change": get_trend(len(staff), 0)},
            "insuranceCarriers": {"value": carrier_val, "change": get_trend(carrier_val, 0)},
            "quotesIndividual": {"value": ind_total, "change": ind_trend},
            "quotesBusiness": {"value": biz_total, "change": biz_trend},
            "quotesAnnuity": {"value": annuity_total, "change": annuity_trend},
        }
    except Exception as e:
        print(f"Error fetching overview: {e}")
        traceback.print_exc()
        return {
            "totalUsers": {"value": 0, "change": "0%"},
            "agencyStaff": {"value": 0, "change": "0%"},
            "insuranceCarriers": {"value": 0, "change": "0%"},
            "quotesIndividual": {"value": 0, "change": "0%"},
            "quotesBusiness": {"value": 0, "change": "0%"},
            "quotesAnnuity": {"value": 0, "change": "0%"},
        }

def _get_category_stats(
    db: Session,
    models: List[Any],
    name: str,
    s_dt: datetime,
    e_dt: datetime,
    prev_s_dt: datetime,
    prev_e_dt: datetime,
    has_date_filter: bool
) -> Dict[str, Any]:
    tot_p = 0
    curr_p = 0
    prev_p = 0

    for model in models:
        try:
            res = db.exec(select(model)).all()
            for r in res:
                tot_p += 1
                c_at = get_record_datetime(r)
                if c_at is not None:
                    if s_dt <= c_at <= e_dt:
                        curr_p += 1
                    elif prev_s_dt <= c_at < prev_e_dt:
                        prev_p += 1
                else:
                    curr_p += 1
        except Exception as e:
            print(f"  [overview-table] Skipping model {model}: {e}")

    p_trend = get_trend(curr_p, prev_p)
    active_p = curr_p if has_date_filter else tot_p

    return {
        "insurance": name,
        "policies": str(active_p),
        "policiesChange": p_trend,
        "revenue": "$0",
        "revenueChange": "0%"
    }

@router.get("/overview-table")
def get_overview_table(
    start_date: Optional[str] = Query(None),
    end_date: Optional[str] = Query(None),
    range: Optional[str] = Query(None),
    db: Session = Depends(get_session)
) -> List[Dict[str, Any]]:
    try:
        s_dt, e_dt = parse_date_range(start_date, end_date, range)
        now = datetime.now()
        has_date_filter = bool(s_dt and e_dt)

        if has_date_filter:
            window_days = max(1, (e_dt - s_dt).days)
            prev_s_dt = s_dt - timedelta(days=window_days)
            prev_e_dt = s_dt
        else:
            s_dt = now - timedelta(days=30)
            e_dt = now
            prev_s_dt = now - timedelta(days=60)
            prev_e_dt = now - timedelta(days=30)

        return [
            _get_category_stats(db, BUSINESS_MODELS, BUSINESS_INSURANCE, s_dt, e_dt, prev_s_dt, prev_e_dt, has_date_filter),
            _get_category_stats(db, PERSONAL_MODELS, "Personal Insurance", s_dt, e_dt, prev_s_dt, prev_e_dt, has_date_filter),
            _get_category_stats(db, [PersonalAutoInsurance], "Auto Insurance", s_dt, e_dt, prev_s_dt, prev_e_dt, has_date_filter),
            _get_category_stats(db, [HomeInsurance], "Home Insurance", s_dt, e_dt, prev_s_dt, prev_e_dt, has_date_filter),
            _get_category_stats(db, [RentersInsuranceModel], "Renters Insurance", s_dt, e_dt, prev_s_dt, prev_e_dt, has_date_filter),
            _get_category_stats(db, [UmbrellaInsuranceModel], "Individual Umbrella Insurance", s_dt, e_dt, prev_s_dt, prev_e_dt, has_date_filter),
            _get_category_stats(db, LIFE_MODELS, "Life Insurance", s_dt, e_dt, prev_s_dt, prev_e_dt, has_date_filter),
            _get_category_stats(db, AGRI_MODELS, "Agri Insurance", s_dt, e_dt, prev_s_dt, prev_e_dt, has_date_filter),
            _get_category_stats(db, HEALTH_MODELS, "Health Insurance", s_dt, e_dt, prev_s_dt, prev_e_dt, has_date_filter),
            _get_category_stats(db, ANNUITY_MODELS, "Annuity", s_dt, e_dt, prev_s_dt, prev_e_dt, has_date_filter),
        ]
    except Exception as e:
        print(f"[overview-table ERROR] {e}")
        traceback.print_exc()
        return []

def _get_month_stats(db: Session, models: List[Any], month_idx: int, target_year: int) -> Tuple[int, float]:
    p_count = 0
    rev = 0.0
    for model in models:
        try:
            recs = db.exec(select(model)).all()
            for r in recs:
                c_at = get_record_datetime(r)
                if c_at and c_at.month == month_idx and c_at.year == target_year:
                    p_count += 1
                    rev += get_record_premium(r)
        except Exception as e:
            print(f"  [policies-sold] Skipping model {model}: {e}")
    return p_count, rev

def _format_revenue(r_val: float) -> str:
    if r_val >= 1000000:
        return f"${r_val/1000000:.1f}M"
    if r_val >= 1000:
        return f"${r_val/1000:.1f}k"
    return f"${r_val:,.0f}"

@router.get("/policies-sold")
def get_policies_sold(
    year: str = Query("2026"),
    db: Session = Depends(get_session)
) -> List[Dict[str, Any]]:
    try:
        target_year = int(year) if year and year.isdigit() else datetime.now().year
        months = ["JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"]
        result = []

        for i, m in enumerate(months):
            month_idx = i + 1
            b_p, b_r = _get_month_stats(db, BUSINESS_MODELS, month_idx, target_year)
            per_p, per_r = _get_month_stats(db, PERSONAL_MODELS, month_idx, target_year)
            auto_p, auto_r = _get_month_stats(db, [PersonalAutoInsurance], month_idx, target_year)
            home_p, home_r = _get_month_stats(db, [HomeInsurance], month_idx, target_year)
            renters_p, renters_r = _get_month_stats(db, [RentersInsuranceModel], month_idx, target_year)
            umb_p, umb_r = _get_month_stats(db, [UmbrellaInsuranceModel], month_idx, target_year)
            l_p, l_r = _get_month_stats(db, LIFE_MODELS, month_idx, target_year)
            a_p, a_r = _get_month_stats(db, AGRI_MODELS, month_idx, target_year)
            h_p, h_r = _get_month_stats(db, HEALTH_MODELS, month_idx, target_year)
            ann_p, ann_r = _get_month_stats(db, ANNUITY_MODELS, month_idx, target_year)

            tot_p = b_p + per_p + l_p + a_p + h_p + ann_p
            tot_r = b_r + per_r + l_r + a_r + h_r + ann_r

            result.append({
                "month": m,
                "year": str(target_year),
                "details": {
                    "Business": {"policies": b_p, "revenue": _format_revenue(b_r)},
                    "Personal": {"policies": per_p, "revenue": _format_revenue(per_r)},
                    "Auto": {"policies": auto_p, "revenue": _format_revenue(auto_r)},
                    "Home": {"policies": home_p, "revenue": _format_revenue(home_r)},
                    "Renters": {"policies": renters_p, "revenue": _format_revenue(renters_r)},
                    "Individual Umbrella": {"policies": umb_p, "revenue": _format_revenue(umb_r)},
                    "Life": {"policies": l_p, "revenue": _format_revenue(l_r)},
                    "Agricultural": {"policies": a_p, "revenue": _format_revenue(a_r)},
                    "Health": {"policies": h_p, "revenue": _format_revenue(h_r)},
                    "Annuity": {"policies": ann_p, "revenue": _format_revenue(ann_r)}
                },
                "totalPolicies": tot_p,
                "totalRevenue": _format_revenue(tot_r)
            })

        return result
    except Exception as e:
        print(f"[policies-sold ERROR] {e}")
        traceback.print_exc()
        return []

def _count_for_month(db: Session, models: List[Any], month_idx: int, target_year: int) -> int:
    count = 0
    for model in models:
        try:
            recs = db.exec(select(model)).all()
            for r in recs:
                c_at = get_record_datetime(r)
                if c_at and c_at.month == month_idx and c_at.year == target_year:
                    count += 1
        except Exception as e:
            print(f"  [rfq] Skipping model {model}: {e}")
    return count

@router.get("/rfq")
def get_rfq_stats(
    year: str = Query("2026"),
    db: Session = Depends(get_session)
) -> List[Dict[str, Any]]:
    try:
        target_year = int(year) if year and year.isdigit() else datetime.now().year
        months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
        result = []

        for i, m in enumerate(months):
            month_idx = i + 1
            health = _count_for_month(db, HEALTH_MODELS, month_idx, target_year)
            auto = _count_for_month(db, [PersonalAutoInsurance], month_idx, target_year)
            home = _count_for_month(db, [HomeInsurance], month_idx, target_year)
            renters = _count_for_month(db, [RentersInsuranceModel], month_idx, target_year)
            umbrella = _count_for_month(db, [UmbrellaInsuranceModel], month_idx, target_year)
            life = _count_for_month(db, LIFE_MODELS, month_idx, target_year)
            business = _count_for_month(db, BUSINESS_MODELS, month_idx, target_year)
            annuity = _count_for_month(db, ANNUITY_MODELS, month_idx, target_year)

            t_life = life // 5 + (1 if life % 5 > 0 else 0)
            w_life = life // 5 + (1 if life % 5 > 1 else 0)
            u_life = life // 5 + (1 if life % 5 > 2 else 0)
            i_life = life // 5 + (1 if life % 5 > 3 else 0)
            v_life = life // 5

            result.append({
                "month": m,
                "val1": health,
                "val2": auto,
                "val3": home,
                "val4": renters,
                "val5": umbrella,
                "val6": t_life,
                "val7": w_life,
                "val8": u_life,
                "val9": i_life,
                "val10": v_life,
                "val11": business,
                "val12": annuity
            })

        return result
    except Exception as e:
        print(f"[rfq ERROR] {e}")
        traceback.print_exc()
        return []

def _tally_policy_statuses(
    db: Session,
    models: List[Any],
    s_dt: Optional[datetime],
    e_dt: Optional[datetime]
) -> Tuple[Dict[str, int], int]:
    status_counts = {"Approved": 0, "Renewal": 0, "Cancelled": 0, "Rejected": 0, "Draft": 0, "Active": 0}
    total = 0
    for model in models:
        try:
            res = db.exec(select(model)).all()
            for r in res:
                c_at = get_record_datetime(r)
                if c_at is not None:
                    if s_dt and c_at < s_dt: continue
                    if e_dt and c_at > e_dt: continue

                raw_s = str(getattr(r, 'quote_status', None) or getattr(r, 'status', None) or getattr(r, 'application_status', None) or '').strip().capitalize()
                if raw_s in status_counts:
                    status_counts[raw_s] += 1
                else:
                    status_counts["Draft"] += 1
                total += 1
        except Exception as e:
            print(f"  [policies-status] Skipping model {model}: {e}")
    return status_counts, total

@router.get("/policies-status")
def get_policies_status(
    timeframe: str = "All Time",
    start_date: Optional[str] = Query(None),
    end_date: Optional[str] = Query(None),
    range: Optional[str] = Query(None),
    db: Session = Depends(get_session)
) -> List[Dict[str, Any]]:
    try:
        s_dt, e_dt = parse_date_range(start_date, end_date, range or timeframe)
        status_counts, total = _tally_policy_statuses(db, ALL_MODELS, s_dt, e_dt)

        if total == 0:
            return [
                { "name": 'Approved', "percentage": '0%', "count": 0, "colorBg": 'bg-purple-500', "hex": '#8b5cf6' },
                { "name": 'Renewal', "percentage": '0%', "count": 0, "colorBg": 'bg-blue-600', "hex": '#2563eb' },
                { "name": 'Cancelled', "percentage": '0%', "count": 0, "colorBg": 'bg-orange-500', "hex": '#f97316' },
                { "name": 'Rejected', "percentage": '0%', "count": 0, "colorBg": 'bg-yellow-500', "hex": '#eab308' },
                { "name": 'Draft', "percentage": '100%', "count": 0, "colorBg": 'bg-slate-400', "hex": '#94a3b8' }
            ]

        def get_pct(count: int) -> str:
            return f"{int(round((count / total) * 100))}%" if total > 0 else "0%"

        return [
            { "name": 'Approved', "percentage": get_pct(status_counts["Approved"]), "count": status_counts["Approved"], "colorBg": 'bg-purple-500', "hex": '#8b5cf6' },
            { "name": 'Renewal', "percentage": get_pct(status_counts["Renewal"]), "count": status_counts["Renewal"], "colorBg": 'bg-blue-600', "hex": '#2563eb' },
            { "name": 'Cancelled', "percentage": get_pct(status_counts["Cancelled"]), "count": status_counts["Cancelled"], "colorBg": 'bg-orange-500', "hex": '#f97316' },
            { "name": 'Rejected', "percentage": get_pct(status_counts["Rejected"]), "count": status_counts["Rejected"], "colorBg": 'bg-yellow-500', "hex": '#eab308' },
            { "name": 'Draft', "percentage": get_pct(status_counts["Draft"] + status_counts["Active"]), "count": status_counts["Draft"] + status_counts["Active"], "colorBg": 'bg-slate-400', "hex": '#94a3b8' }
        ]
    except Exception as e:
        print(f"[policies-status ERROR] {e}")
        traceback.print_exc()
        return []

def _extract_record_display_name(r: Any) -> str:
    f_n = getattr(r, 'first_name', None)
    l_n = getattr(r, 'last_name', None)
    if f_n or l_n:
        first = safe_decrypt_val(f_n)
        last = safe_decrypt_val(l_n)
        return f"{first} {last}".strip()
    
    raw_name = getattr(r, 'full_name', None) or getattr(r, 'contact_name', None) or getattr(r, 'contact_person_name', None) or getattr(r, 'business_name', None) or getattr(r, 'applicant_name', None)
    if raw_name:
        return safe_decrypt_val(raw_name)
    return 'Customer'

def _format_time_ago(seconds: int) -> str:
    if seconds < 60:
        return "Just now"
    if seconds < 3600:
        return f"{seconds // 60} mins ago"
    if seconds < 86400:
        return f"{seconds // 3600} hours ago"
    return f"{seconds // 86400} days ago"

@router.get("/recent-activity")
def get_recent_activity(
    start_date: Optional[str] = Query(None),
    end_date: Optional[str] = Query(None),
    range: Optional[str] = Query(None),
    db: Session = Depends(get_session)
) -> List[Dict[str, Any]]:
    try:
        s_dt, e_dt = parse_date_range(start_date, end_date, range)
        activity_list = []
        recent_quotes = []

        for model in ALL_MODELS:
            try:
                res = db.exec(select(model)).all()
                for r in res:
                    c_at = get_record_datetime(r) or datetime.now()
                    if s_dt and c_at < s_dt: continue
                    if e_dt and c_at > e_dt: continue
                    
                    name = _extract_record_display_name(r)
                    product = model.__tablename__.replace('_', ' ').title()
                    status = str(getattr(r, 'quote_status', None) or getattr(r, 'status', None) or getattr(r, 'application_status', None) or 'Active').strip().capitalize()
                    recent_quotes.append((c_at, name, product, status))
            except Exception:
                pass
        
        recent_quotes.sort(key=lambda x: x[0], reverse=True)
        top_quotes = recent_quotes[:10]
        colors = ['bg-purple-100 text-purple-600', 'bg-blue-100 text-blue-600', 'bg-orange-100 text-orange-600', 'bg-green-100 text-green-600']
        now = datetime.now()

        for idx, (c_at, name, product, status) in enumerate(top_quotes):
            diff = now - c_at if isinstance(c_at, datetime) else timedelta(0)
            seconds = int(diff.total_seconds())
            time_str = _format_time_ago(seconds)
            initials = "".join([part[0].upper() for part in str(name).split()[:2]]) if name else "US"

            activity_list.append({
                "id": str(idx + 1),
                "name": str(name),
                "isNew": idx < 3,
                "action": f"Submitted {product} quote ({status})",
                "time": time_str,
                "type": "avatar",
                "initials": initials,
                "color": colors[idx % len(colors)],
                "status": status.upper()
            })

        return activity_list
    except Exception as e:
        print(f"[recent-activity ERROR] {e}")
        traceback.print_exc()
        return []

def _get_customers_data(db: Session) -> List[Dict[str, Any]]:
    users = db.exec(select(User).where(User.role == "customer").order_by(User.created_at.desc()).limit(50)).all()
    return [{"id": str(u.id), "name": u.name, "email": u.email, "phone": u.mobile or "N/A", "dob": "1990-01-01", "status": "Active" if u.is_email_verified else "Pending", "dateJoined": u.created_at.strftime("%b %d, %Y") if u.created_at else "N/A"} for u in users]

def _get_staff_data(db: Session) -> List[Dict[str, Any]]:
    users = db.exec(select(User).where(User.role.in_(["staff", "agent", "admin"])).order_by(User.created_at.desc()).limit(50)).all()
    return [{
        "id": str(u.id),
        "firstName": u.name.split()[0] if u.name else "Agent",
        "lastName": ' '.join(u.name.split()[1:]) if u.name and len(u.name.split()) > 1 else "",
        "email": u.email,
        "phone": u.mobile or "N/A",
        "state": u.state or "USA",
        "npn": f"*******{str(u.id)[-4:]}" if len(str(u.id)) >= 4 else "*******9012",
        "date": u.created_at.strftime("%b %d, %Y") if u.created_at else "N/A",
        "status": "Active" if u.is_email_verified else "Pending"
    } for u in users]

def _get_carriers_data_simple(db: Session) -> List[Dict[str, Any]]:
    users = db.exec(select(User).where(User.role == "carrier").order_by(User.created_at.desc()).limit(50)).all()
    return [{"id": str(u.id), "carrierName": u.name, "contactPerson": "Admin", "email": u.email, "phone": u.mobile or "N/A", "state": "TX", "status": "Active"} for u in users]

def _get_quotes_data(db: Session) -> List[Dict[str, Any]]:
    quotes = []
    for m in [LifeInsuranceModel, BusinessInsuranceModel, PersonalAutoInsurance]:
        try:
            res = db.exec(select(m).order_by(m.created_at.desc()).limit(10)).all()
            for r in res:
                quotes.append({
                    "id": f"{m.__tablename__}-{r.id}",
                    "name": getattr(r, 'full_name', getattr(r, 'business_name', 'Client')),
                    "insuranceType": m.__tablename__.replace('_', ' ').title(),
                    "date": r.created_at.strftime("%b %d, %Y") if r.created_at else "N/A",
                    "status": getattr(r, 'quote_status', 'Draft')
                })
        except Exception as e:
            print(f"  [data/quotes] Skipping model {m}: {e}")
    quotes.sort(key=lambda x: x["date"], reverse=True)
    return quotes

@router.get("/data/{entity_type}")
def get_dashboard_data(entity_type: str, db: Session = Depends(get_session)) -> List[Dict[str, Any]]:
    try:
        if entity_type == "customers":
            return _get_customers_data(db)
        if entity_type == "staff":
            return _get_staff_data(db)
        if entity_type == "carriers":
            return _get_carriers_data_simple(db)
        if entity_type == "quotes":
            return _get_quotes_data(db)
        return []
    except Exception as e:
        print(f"[data/{entity_type} ERROR] {e}")
        traceback.print_exc()
        return []

def _compute_ai_insights(db: Session, models_to_query: List[Any], total_leads: int) -> Dict[str, Any]:
    day_hour_counts: Dict[Tuple[str, int], int] = {}
    pending_draft_stale = 0
    for model in models_to_query:
        try:
            records = db.exec(select(model)).all()
            for r in records:
                st = str(getattr(r, 'quote_status', '') or '').lower()
                if st in ['draft', 'pending', 'cancelled', 'deactivate']:
                    pending_draft_stale += 1
                dt = get_record_datetime(r)
                if dt:
                    day_name = dt.strftime('%a')
                    hr = dt.hour
                    key = (day_name, hr)
                    day_hour_counts[key] = day_hour_counts.get(key, 0) + 1
        except Exception:
            pass

    best_time_str = "Fri 2 PM - 4 PM"
    if day_hour_counts:
        sorted_times = sorted(day_hour_counts.items(), key=lambda x: x[1], reverse=True)
        top_day, top_hr = sorted_times[0][0]
        end_hr = (top_hr + 2) % 24
        fmt_start = datetime.strptime(str(top_hr), "%H").strftime("%I %p").lstrip("0")
        fmt_end = datetime.strptime(str(end_hr), "%H").strftime("%I %p").lstrip("0")
        best_time_str = f"{top_day} {fmt_start} - {fmt_end}"

    forecasted_leads = int(round(total_leads * 1.15)) if total_leads > 0 else 0
    churn_risk_leads = pending_draft_stale

    return {
        "bestTimeToContact": best_time_str,
        "forecastedLeads": forecasted_leads,
        "churnRiskLeads": churn_risk_leads
    }

@router.get("/leads-insights")
def get_leads_insights(
    year: str = Query("2026"),
    insurance: str = Query("All Insurances"),
    channel: str = Query("All Channels"),
    segment: str = Query("All Segment"),
    start_date: Optional[str] = Query(None),
    end_date: Optional[str] = Query(None),
    range: Optional[str] = Query(None),
    db: Session = Depends(get_session)
) -> Dict[str, Any]:
    _SOURCE_COLORS = {
        "Google Ads": "#10b981",
        "Facebook": "#3b82f6",
        "Online Research": "#f59e0b",
        "Referral": "#facc15",
        "SEO": "#8b5cf6",
        "Others": "#f43f5e",
        "Direct / Organic": "#64748b",
    }
    _DEFAULT_COLORS = ["#06b6d4", "#f43f5e", "#84cc16", "#ec4899", "#14b8a6", "#f97316"]

    _INSURANCE_MODEL_MAP = {
        "Health": [HealthInsuranceModel, GroupHealthModel],
        "Auto": [PersonalAutoInsurance, CommercialAutoModel],
        "Home": [HomeInsurance],
        RENTERS_INSURANCE: [RentersInsuranceModel],
        "Umbrella for Individuals": [UmbrellaInsuranceModel],
        TERM_LIFE: [LifeInsuranceModel],
        WHOLE_LIFE: [LifeInsuranceModel],
        UNIVERSAL_LIFE: [LifeInsuranceModel],
        INDEXED_UNIVERSAL_LIFE: [LifeInsuranceModel],
        VARIABLE_UNIVERSAL_LIFE: [LifeInsuranceModel],
        "Business": BUSINESS_MODELS,
        "Annuity": [AnnuityInsuranceModel],
        "BOP": [BusinessOwnerPolicyModel],
        COMMERCIAL_AUTO: [CommercialAutoModel],
        COMMERCIAL_PROPERTY: [CommercialPropertyModel],
        GENERAL_LIABILITY: [GeneralLiability],
        CYBER_LIABILITY: [Cyberliability],
        PROFESSIONAL_LIABILITY: [ProfessionalLiability],
        WORKERS_COMP: [WorkersCompensationModel],
        "Workers Compensation": [WorkersCompensationModel],
        GROUP_HEALTH: [GroupHealthModel],
        GROUP_LIFE: [GroupLife],
        OCEAN_MARINE: [OceanMarineModel],
        "Business Umbrella": [Businessumbrella],
        "Agricultural": [AgricultureInsuranceModel],
    }

    _SEGMENT_MODEL_MAP = {
        "Individual": PERSONAL_MODELS + LIFE_MODELS + HEALTH_MODELS,
        "Business": BUSINESS_MODELS,
        "Annuity": ANNUITY_MODELS,
    }

    try:
        s_dt, e_dt = parse_date_range(start_date, end_date, range)
        target_year = int(year) if year and year.isdigit() else datetime.now().year

        models_to_query = list(ALL_MODELS)
        if insurance and insurance != "All Insurances" and insurance in _INSURANCE_MODEL_MAP:
            models_to_query = _INSURANCE_MODEL_MAP[insurance]
        elif segment and segment != "All Segment" and segment in _SEGMENT_MODEL_MAP:
            models_to_query = _SEGMENT_MODEL_MAP[segment]

        total_leads = 0
        conversions = 0
        total_premium = 0.0
        source_counts: Dict[str, int] = {}
        source_conversions: Dict[str, int] = {}
        city_counts: Dict[str, int] = {}
        state_counts: Dict[str, int] = {}
        months_arr = ["JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"]
        monthly_counts = dict.fromkeys(months_arr, 0)

        now = datetime.now()
        cur_month_start = datetime(now.year, now.month, 1)
        if now.month == 1:
            prev_month_start = datetime(now.year - 1, 12, 1)
        else:
            prev_month_start = datetime(now.year, now.month - 1, 1)
        prev_month_end = cur_month_start - timedelta(seconds=1)

        cur_leads = 0
        cur_conversions = 0
        cur_premium = 0.0
        prev_leads = 0
        prev_conversions = 0
        prev_premium = 0.0

        for model in models_to_query:
            try:
                records = db.exec(select(model)).all()
                for r in records:
                    c_at = getattr(r, 'created_at', None) or getattr(r, 'submitted_at', None)
                    if isinstance(c_at, datetime):
                        if s_dt and c_at < s_dt:
                            continue
                        if e_dt and c_at > e_dt:
                            continue

                    src_raw = getattr(r, 'lead_source', None) or 'Direct / Organic'
                    src = str(src_raw).strip() if src_raw else 'Direct / Organic'
                    s_lower = src.lower()
                    if s_lower == 'other' or s_lower == 'others' or s_lower.startswith('other') or 'whats' in s_lower:
                        src = 'Others'

                    if channel and channel != "All Channels":
                        ch_lower = channel.lower()
                        is_other_ch = ch_lower in ('other', 'others')
                        if (is_other_ch and src != "Others") or (not is_other_ch and src.lower() != ch_lower):
                            continue

                    total_leads += 1
                    premium = get_record_premium(r)
                    total_premium += premium

                    is_converted = False
                    st = str(getattr(r, 'quote_status', '') or '').lower()
                    if st in ['approved', 'bound', 'policy', 'active']:
                        conversions += 1
                        is_converted = True

                    source_counts[src] = source_counts.get(src, 0) + 1
                    if is_converted:
                        source_conversions[src] = source_conversions.get(src, 0) + 1

                    city = getattr(r, 'city', None) or getattr(r, 'business_address', None) or getattr(r, 'address_line1', None)
                    if city:
                        city_clean = str(city).split(',')[0].strip()
                        if city_clean:
                            city_counts[city_clean] = city_counts.get(city_clean, 0) + 1

                    state = getattr(r, 'state', None)
                    if state and len(str(state)) == 2:
                        state_code = str(state).upper()
                        state_counts[state_code] = state_counts.get(state_code, 0) + 1

                    if c_at and isinstance(c_at, datetime) and c_at.year == target_year:
                        m_name = months_arr[c_at.month - 1]
                        monthly_counts[m_name] += 1

                    if c_at and isinstance(c_at, datetime):
                        if cur_month_start <= c_at <= now:
                            cur_leads += 1
                            cur_premium += premium
                            if is_converted:
                                cur_conversions += 1
                        elif prev_month_start <= c_at <= prev_month_end:
                            prev_leads += 1
                            prev_premium += premium
                            if is_converted:
                                prev_conversions += 1
            except Exception as e:
                print(f"[leads-insights model error] {e}")

        conversion_rate = round((conversions / total_leads * 100), 1) if total_leads > 0 else 0
        avg_cpl = round(total_premium / total_leads, 2) if total_leads > 0 else 0

        total_leads_change = get_trend(cur_leads, prev_leads)
        conversions_change = get_trend(cur_conversions, prev_conversions)
        cur_conv_rate = (cur_conversions / cur_leads * 100) if cur_leads > 0 else 0
        prev_conv_rate = (prev_conversions / prev_leads * 100) if prev_leads > 0 else 0
        conversion_rate_change = get_trend(int(cur_conv_rate), int(prev_conv_rate))
        cur_avg_cpl = (cur_premium / cur_leads) if cur_leads > 0 else 0
        prev_avg_cpl = (prev_premium / prev_leads) if prev_leads > 0 else 0
        avg_cpl_change = get_trend(int(cur_avg_cpl), int(prev_avg_cpl))

        leads_over_time = [{"month": m, "leads": monthly_counts[m]} for m in months_arr]

        color_idx = 0
        source_dist = []
        for name, value in source_counts.items():
            color = _SOURCE_COLORS.get(name, _DEFAULT_COLORS[color_idx % len(_DEFAULT_COLORS)])
            if name not in _SOURCE_COLORS:
                color_idx += 1
            source_dist.append({"name": name, "value": value, "color": color})

        top_cities = [{"city": k, "leads": v} for k, v in sorted(city_counts.items(), key=lambda x: x[1], reverse=True)[:12]]

        conversion_by_channel = []
        for src_name, src_count in source_counts.items():
            conv_count = source_conversions.get(src_name, 0)
            rate = round((conv_count / src_count * 100), 0) if src_count > 0 else 0
            conversion_by_channel.append({"channel": src_name, "rate": int(rate)})

        ai_insights = _compute_ai_insights(db, models_to_query, total_leads)

        return {
            "totalLeads": total_leads,
            "conversions": conversions,
            "conversionRate": conversion_rate,
            "avgCpl": avg_cpl,
            "totalLeadsChange": total_leads_change,
            "conversionsChange": conversions_change,
            "conversionRateChange": conversion_rate_change,
            "avgCplChange": avg_cpl_change,
            "leadsOverTime": leads_over_time,
            "sourceDistribution": source_dist,
            "conversionByChannel": conversion_by_channel,
            "topCities": top_cities,
            "stateDensities": state_counts,
            "aiInsights": ai_insights
        }
    except Exception as e:
        print(f"[leads-insights ERROR] {e}")
        traceback.print_exc()
        return {
            "totalLeads": 0,
            "conversions": 0,
            "conversionRate": 0,
            "avgCpl": 0,
            "totalLeadsChange": "0%",
            "conversionsChange": "0%",
            "conversionRateChange": "0%",
            "avgCplChange": "0%",
            "leadsOverTime": [],
            "sourceDistribution": [],
            "conversionByChannel": [],
            "topCities": [],
            "stateDensities": {}
        }


_key = os.getenv('ENCRYPTION_KEY', 'NdvONsVnlXwjfZl5hHzRAQnBr77dH86RDr84gOLo4jU=')
try:
    _fernet = Fernet(_key.encode('utf-8') if isinstance(_key, str) else _key)
except Exception:
    _fernet = None

try:
    from Crypto.Cipher import AES as _PyCryptoAES
    from Crypto.Util.Padding import unpad as _pycrypto_unpad
    _aes_key_raw = os.getenv('KEY', '1234567890123456').encode('utf-8')[:16]
    _aes_iv_raw  = os.getenv('IV',  '1234567890123456').encode('utf-8')[:16]
    _aes_available = True
except ImportError:
    _aes_available = False

def _try_aes_decrypt(val: str) -> str:
    """Try to decrypt an AES-CBC base64-encoded value using legacy encryption_service parameters."""
    if not _aes_available:
        return val
    try:
        raw = base64.b64decode(val)
        if len(raw) == 0 or len(raw) % 16 != 0:
            return val
        # Uses standard AES CBC decryption with PKCS7 unpadding for compatibility with model fields
        cipher = _PyCryptoAES.new(_aes_key_raw, _PyCryptoAES.MODE_CBC, _aes_iv_raw)  # noqa: S301, S413
        decrypted = cipher.decrypt(raw)
        unpadded = _pycrypto_unpad(decrypted, _PyCryptoAES.block_size)
        result = unpadded.decode('utf-8')
        if result and all(ord(c) >= 32 or c in ('\n', '\t', '\r') for c in result):
            return result
    except Exception:
        pass
    return val

def safe_decrypt_val(val: Any) -> str:
    if not val or not isinstance(val, str):
        return str(val) if val is not None else ""
    if _fernet:
        try:
            if val.startswith('Z0FB'):
                raw = base64.b64decode(val)
                return _fernet.decrypt(raw).decode('utf-8')
            elif val.startswith('gAAAAA'):
                return _fernet.decrypt(val.encode('utf-8')).decode('utf-8')
        except Exception:
            pass
    if _aes_available and _re_module.match(r'^[A-Za-z0-9+/]+=*$', val) and len(val) % 4 == 0 and len(val) >= 24:
        decrypted = _try_aes_decrypt(val)
        if decrypted != val:
            return decrypted
    return val

def _extract_name_from_record(r: Any) -> Tuple[str, str]:
    f_name = getattr(r, 'first_name', None)
    l_name = getattr(r, 'last_name', None)
    if f_name or l_name:
        return safe_decrypt_val(f_name), safe_decrypt_val(l_name)

    fn = getattr(r, 'full_name', None) or getattr(r, 'contact_name', None) or getattr(r, 'contact_person_name', None) or getattr(r, 'applicant_name', None)
    if fn:
        parts = safe_decrypt_val(fn).strip().split()
        return parts[0] if parts else '', ' '.join(parts[1:]) if len(parts) > 1 else ''

    bn = getattr(r, 'business_name', None) or getattr(r, 'company_name', None) or getattr(r, 'farm_name', None)
    if bn:
        parts = safe_decrypt_val(bn).strip().split()
        return parts[0] if parts else '', ' '.join(parts[1:]) if len(parts) > 1 else ''

    return 'Client', ''

def extract_customer_record(r: Any, insurance_label: str) -> Dict[str, Any]:
    first, last = _extract_name_from_record(r)
    email = safe_decrypt_val(getattr(r, 'email', None) or getattr(r, 'email_address', None) or getattr(r, 'contact_email', None))
    phone = safe_decrypt_val(getattr(r, 'phone_number', None) or getattr(r, 'phone', None) or getattr(r, 'mobile_phone', None) or getattr(r, 'contact_phone', None) or getattr(r, 'mobile', None))
    state = safe_decrypt_val(getattr(r, 'state', None) or getattr(r, 'state_of_residence', None) or getattr(r, 'license_state', None)) or 'USA'
    
    dt = getattr(r, 'created_at', None) or getattr(r, 'submission_timestamp', None) or getattr(r, 'submitted_at', None) or getattr(r, 'date', None)
    ts_val = dt if isinstance(dt, datetime) else datetime(2025, 7, 14)
    date_str = ts_val.strftime(DATE_FMT_LONG)
    
    raw_status = getattr(r, 'quote_status', None) or getattr(r, 'status', None) or getattr(r, 'application_status', None)
    status = str(raw_status).strip().capitalize() if raw_status else 'Active'

    tbl_name = getattr(r, '__tablename__', 'rec')
    rec_id = getattr(r, 'id', '0')

    return {
        'id': f"{tbl_name}-{rec_id}",
        'firstName': first or 'Customer',
        'lastName': last or '',
        'insuranceType': insurance_label,
        'email': email or 'N/A',
        'phone': phone or 'N/A',
        'state': state,
        'date': date_str,
        'status': status,
        '_ts': ts_val
    }

def _process_individual_customers(db: Session) -> Tuple[Dict[str, int], List[Dict[str, Any]]]:
    ind_cats = {
        'Health': 0, 'Auto': 0, 'Home': 0, RENTERS_INSURANCE: 0,
        WHOLE_LIFE: 0, TERM_LIFE: 0, UNIVERSAL_LIFE: 0,
        INDEXED_UNIVERSAL: 0, VARIABLE_UNIVERSAL: 0,
        'Umbrella for Individual': 0, 'Annuity': 0
    }
    ind_customers = []

    for r in db.exec(select(HealthInsuranceModel)).all():
        ind_cats['Health'] += 1
        ind_customers.append(extract_customer_record(r, 'Health'))

    for r in db.exec(select(PersonalAutoInsurance)).all():
        ind_cats['Auto'] += 1
        ind_customers.append(extract_customer_record(r, 'Auto'))

    for r in db.exec(select(HomeInsurance)).all():
        ind_cats['Home'] += 1
        ind_customers.append(extract_customer_record(r, 'Home'))

    for r in db.exec(select(RentersInsuranceModel)).all():
        ind_cats[RENTERS_INSURANCE] += 1
        ind_customers.append(extract_customer_record(r, RENTERS_INSURANCE))

    for r in db.exec(select(UmbrellaInsuranceModel)).all():
        ind_cats['Umbrella for Individual'] += 1
        ind_customers.append(extract_customer_record(r, 'Umbrella'))

    for r in db.exec(select(LifeInsuranceModel)).all():
        ptype = (str(getattr(r, 'product_type', '') or '') + ' ' + str(getattr(r, 'coverage_type', '') or '')).lower()
        if 'whole' in ptype:
            ind_cats[WHOLE_LIFE] += 1
            itype = WHOLE_LIFE
        elif 'universal' in ptype:
            if 'indexed' in ptype:
                ind_cats[INDEXED_UNIVERSAL] += 1
                itype = INDEXED_UNIVERSAL
            elif 'variable' in ptype:
                ind_cats[VARIABLE_UNIVERSAL] += 1
                itype = VARIABLE_UNIVERSAL
            else:
                ind_cats[UNIVERSAL_LIFE] += 1
                itype = UNIVERSAL_LIFE
        else:
            ind_cats[TERM_LIFE] += 1
            itype = TERM_LIFE
        ind_customers.append(extract_customer_record(r, itype))

    for r in db.exec(select(AnnuityInsuranceModel)).all():
        ind_cats['Annuity'] += 1
        ind_customers.append(extract_customer_record(r, 'Annuity'))

    for u in db.exec(select(User).where(User.role == 'customer')).all():
        parts = (u.name or 'Customer').split()
        f_n = parts[0]
        l_n = ' '.join(parts[1:]) if len(parts) > 1 else ''
        ts_u = u.created_at if isinstance(u.created_at, datetime) else datetime(2025, 7, 14)
        ind_customers.append({
            'id': f"user-{u.id}",
            'firstName': f_n,
            'lastName': l_n,
            'insuranceType': 'Registered Member',
            'email': u.email or 'N/A',
            'phone': u.mobile or 'N/A',
            'state': 'USA',
            'date': ts_u.strftime(DATE_FMT_LONG),
            'status': 'Active' if u.is_email_verified else 'Pending',
            '_ts': ts_u
        })

    return ind_cats, ind_customers

def _process_business_customers(db: Session) -> Tuple[Dict[str, int], List[Dict[str, Any]]]:
    biz_cats = {
        'BOP': 0, COMMERCIAL_PROPERTY: 0, COMMERCIAL_AUTO: 0,
        CYBER_LIABILITY: 0, GENERAL_LIABILITY: 0, OCEAN_MARINE: 0,
        GROUP_HEALTH: 0, GROUP_LIFE: 0, PROFESSIONAL_LIABILITY: 0,
        'Umbrella(for business)': 0, WORKERS_COMP: 0,
        'Agriculture': 0, BUSINESS_INSURANCE: 0
    }
    biz_customers = []

    model_mapping = [
        (BusinessOwnerPolicyModel, 'BOP', 'BOP'),
        (CommercialPropertyModel, COMMERCIAL_PROPERTY, COMMERCIAL_PROPERTY),
        (CommercialAutoModel, COMMERCIAL_AUTO, COMMERCIAL_AUTO),
        (Cyberliability, CYBER_LIABILITY, CYBER_LIABILITY),
        (GeneralLiability, GENERAL_LIABILITY, GENERAL_LIABILITY),
        (OceanMarineModel, OCEAN_MARINE, OCEAN_MARINE),
        (GroupHealthModel, GROUP_HEALTH, GROUP_HEALTH),
        (GroupLife, GROUP_LIFE, GROUP_LIFE),
        (ProfessionalLiability, PROFESSIONAL_LIABILITY, PROFESSIONAL_LIABILITY),
        (Businessumbrella, 'Umbrella(for business)', 'Business Umbrella'),
        (WorkersCompensationModel, WORKERS_COMP, WORKERS_COMP),
        (BusinessInsuranceModel, BUSINESS_INSURANCE, BUSINESS_INSURANCE),
        (AgricultureInsuranceModel, 'Agriculture', 'Agriculture'),
    ]

    for model, cat_key, label in model_mapping:
        for r in db.exec(select(model)).all():
            biz_cats[cat_key] += 1
            biz_customers.append(extract_customer_record(r, label))

    return biz_cats, biz_customers

@router.get("/customers-data")
def get_customers_dashboard_data(db: Session = Depends(get_session)) -> Dict[str, Any]:
    try:
        ind_cats, ind_customers = _process_individual_customers(db)
        biz_cats, biz_customers = _process_business_customers(db)

        ind_customers.sort(key=lambda x: x.get('_ts', datetime.min), reverse=True)
        biz_customers.sort(key=lambda x: x.get('_ts', datetime.min), reverse=True)

        now = datetime.now()
        cur_month_start = datetime(now.year, now.month, 1)
        if now.month == 1:
            prev_month_start = datetime(now.year - 1, 12, 1)
        else:
            prev_month_start = datetime(now.year, now.month - 1, 1)

        def calc_trend_str(records: List[Dict[str, Any]]) -> str:
            cur_cnt = 0
            prev_cnt = 0
            for r in records:
                ts = r.get('_ts')
                if isinstance(ts, datetime):
                    if ts >= cur_month_start:
                        cur_cnt += 1
                    elif prev_month_start <= ts < cur_month_start:
                        prev_cnt += 1
            if prev_cnt == 0:
                return "0% from last month"
            chg = ((cur_cnt - prev_cnt) / prev_cnt) * 100
            sign = "+" if chg >= 0 else ""
            return f"{sign}{chg:.1f}% from last month"

        ind_change_str = calc_trend_str(ind_customers)
        biz_change_str = calc_trend_str(biz_customers)

        for c in ind_customers:
            c.pop('_ts', None)
        for c in biz_customers:
            c.pop('_ts', None)

        ind_categories_list = [{'count': f"{cnt:,}", 'label': lbl} for lbl, cnt in ind_cats.items()]
        biz_categories_list = [{'count': f"{cnt:,}", 'label': lbl} for lbl, cnt in biz_cats.items()]

        return {
            'individualTotalCount': len(ind_customers),
            'individualChange': ind_change_str,
            'individualCategories': ind_categories_list,
            'individualCustomers': ind_customers,
            'businessTotalCount': len(biz_customers),
            'businessChange': biz_change_str,
            'businessCategories': biz_categories_list,
            'businessCustomers': biz_customers
        }
    except Exception as e:
        print(f"[customers-data ERROR] {e}")
        traceback.print_exc()
        return {
            'individualTotalCount': 0,
            'individualChange': '0%',
            'individualCategories': [],
            'individualCustomers': [],
            'businessTotalCount': 0,
            'businessChange': '0%',
            'businessCategories': [],
            'businessCustomers': []
        }

TABLE_MAP = { m.__tablename__: m for m in ALL_MODELS }
TABLE_MAP['user'] = User
TABLE_MAP['health_insurance'] = HealthInsuranceModel
TABLE_MAP['renters_insurance'] = RentersInsuranceModel
TABLE_MAP['umbrella_insurance'] = UmbrellaInsuranceModel
TABLE_MAP['annuity_insurance'] = AnnuityInsuranceModel
TABLE_MAP['business_owner_policies'] = BusinessOwnerPolicyModel
TABLE_MAP['unifiedcommercial'] = CommercialPropertyModel
TABLE_MAP['group_life'] = GroupLife
TABLE_MAP['agriculture_insurance'] = AgricultureInsuranceModel
TABLE_MAP['business_insurance'] = BusinessInsuranceModel

class StatusUpdateRequest(BaseModel):
    status: str

@router.patch("/customers/{customer_id}/status")
def update_customer_status(customer_id: str, payload: StatusUpdateRequest, db: Session = Depends(get_session)):
    try:
        parts = customer_id.split('-', 1)
        if len(parts) != 2:
            raise HTTPException(status_code=400, detail="Invalid customer ID format")
        
        tbl_name, rec_id = parts[0], parts[1]
        
        if tbl_name == 'user':
            user = db.get(User, int(rec_id))
            if not user:
                raise HTTPException(status_code=404, detail="User not found")
            db.commit()
            return {"status": "success", "message": f"User status updated to {payload.status}"}

        model_cls = TABLE_MAP.get(tbl_name)
        if not model_cls:
            raise HTTPException(status_code=400, detail=f"Unknown table {tbl_name}")
        
        rec = db.get(model_cls, int(rec_id))
        if not rec:
            raise HTTPException(status_code=404, detail=f"Record not found in {tbl_name}")

        if hasattr(rec, 'quote_status'):
            setattr(rec, 'quote_status', payload.status)
        elif hasattr(rec, 'status'):
            setattr(rec, 'status', payload.status)
        elif hasattr(rec, 'application_status'):
            setattr(rec, 'application_status', payload.status)

        db.add(rec)
        db.commit()
        return {"status": "success", "message": f"Status updated to {payload.status}"}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))

@router.delete("/customers/{customer_id}")
def delete_customer_record(customer_id: str, db: Session = Depends(get_session)):
    try:
        parts = customer_id.split('-', 1)
        if len(parts) != 2:
            raise HTTPException(status_code=400, detail="Invalid customer ID format")
        
        tbl_name, rec_id = parts[0], parts[1]
        
        if tbl_name == 'user':
            user = db.get(User, int(rec_id))
            if user:
                db.delete(user)
                db.commit()
            return {"status": "success", "message": "User deleted successfully"}

        model_cls = TABLE_MAP.get(tbl_name)
        if not model_cls:
            raise HTTPException(status_code=400, detail=f"Unknown table {tbl_name}")
        
        rec = db.get(model_cls, int(rec_id))
        if rec:
            db.delete(rec)
            db.commit()
        return {"status": "success", "message": "Record deleted successfully"}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))

def _collect_carrier_users(db: Session) -> Dict[str, Dict[str, Any]]:
    carrier_users = db.exec(select(User).where(User.role == 'carrier')).all()
    carrier_map = {}
    for u in carrier_users:
        name = u.name or 'Registered Carrier'
        carrier_map[name.lower()] = {
            'id': f'user-{u.id}',
            'carrierName': name,
            'offerings': [],
            'emails': [u.email] if u.email else [],
            'phones': [u.mobile] if u.mobile else [],
            'state': 'USA',
            'date': u.created_at.strftime(DATE_FMT_LONG) if u.created_at else 'N/A',
            '_ts': u.created_at if isinstance(u.created_at, datetime) else datetime.min
        }
    return carrier_map

def _resolve_carrier_offering(tbl: str) -> Tuple[str, str]:
    if 'bop' in tbl or 'businessowner' in tbl: return 'BOP', 'BOP Carrier'
    if 'commercialproperty' in tbl or 'commercial_property' in tbl: return COMMERCIAL_PROPERTY, 'Commercial Property Carrier'
    if 'commercialauto' in tbl or 'commercial_auto' in tbl: return COMMERCIAL_AUTO, 'Commercial Auto Carrier'
    if 'cyber' in tbl: return 'Cyber Insurance', 'Cyber Liability Carrier'
    if 'generalliability' in tbl or 'general_liability' in tbl: return GENERAL_LIABILITY, 'General Liability Carrier'
    if 'ocean' in tbl or 'marine' in tbl: return OCEAN_MARINE, 'Ocean Marine Carrier'
    if 'grouphealth' in tbl or 'group_health' in tbl: return GROUP_HEALTH, 'Group Health Provider'
    if 'grouplife' in tbl or 'group_life' in tbl: return GROUP_LIFE, 'Group Life Carrier'
    if 'professionalliability' in tbl or 'professional_liability' in tbl: return PROFESSIONAL_LIABILITY, 'Professional Liability Carrier'
    if 'workers' in tbl or 'compensation' in tbl: return "Workers' Comp", "Workers' Comp Carrier"
    if 'agri' in tbl or 'crop' in tbl or 'farm' in tbl: return 'Crop Insurance', 'Agri Insurance Carrier'
    if 'health' in tbl: return 'Health', 'Health Care Carrier'
    if 'termlife' in tbl or 'term_life' in tbl: return TERM_LIFE, 'Life Insurance Carrier'
    if 'wholelife' in tbl or 'whole_life' in tbl: return WHOLE_LIFE, 'Life Insurance Carrier'
    if 'life' in tbl: return 'Life Insurance', 'Life Assurance Carrier'
    if 'annuity' in tbl: return 'Annuity', 'Annuity Provider'
    if 'personalauto' in tbl or 'personal_auto' in tbl or 'auto' in tbl: return 'Personal Auto', 'Auto Insurance Carrier'
    if 'renters' in tbl: return 'Renters', 'Renters Insurance Carrier'
    if 'home' in tbl: return 'Homeowners', 'Home Insurance Carrier'
    if 'umbrella' in tbl: return 'Umbrella', 'Umbrella Insurance Carrier'
    if 'business' in tbl or 'commercial' in tbl: return 'Commercial Insurance', 'Commercial Insurance Carrier'
    return 'Personal Insurance', 'Direct Carrier'

def _process_record_carriers(db: Session, carrier_map: Dict[str, Dict[str, Any]]):
    for m in ALL_MODELS:
        try:
            recs = db.exec(select(m)).all()
            for r in recs:
                raw_c = (
                    getattr(r, 'carrier', None) or getattr(r, 'carrier_name', None) or
                    getattr(r, 'insurance_company', None) or getattr(r, 'current_carrier', None) or
                    getattr(r, 'previous_carrier', None) or getattr(r, 'prior_carrier', None) or
                    getattr(r, 'previous_insurance_carrier', None) or getattr(r, 'prior_insurance_carrier', None) or
                    getattr(r, 'current_insurance_carrier', None) or getattr(r, 'current_group_life_carrier', None) or
                    getattr(r, 'preferred_carrier', None) or getattr(r, 'preferred_insurance_carriers', None)
                )
                raw_carriers = []
                if raw_c:
                    if isinstance(raw_c, list):
                        raw_carriers = [safe_decrypt_val(x) for x in raw_c]
                    else:
                        dec = safe_decrypt_val(raw_c)
                        if dec and (dec.startswith('[') or ',' in dec):
                            try:
                                parsed = json.loads(dec)
                                raw_carriers = [str(x) for x in parsed] if isinstance(parsed, list) else [x.strip() for x in dec.split(',')]
                            except Exception:
                                raw_carriers = [x.strip() for x in dec.split(',')]
                        elif dec:
                            raw_carriers = [dec.strip()]

                raw_email = getattr(r, 'email', None) or getattr(r, 'contact_email', None) or getattr(r, 'applicant_email', None)
                email = safe_decrypt_val(raw_email) if raw_email else ''
                
                raw_phone = getattr(r, 'phone', None) or getattr(r, 'phone_number', None) or getattr(r, 'contact_phone', None)
                phone = safe_decrypt_val(raw_phone) if raw_phone else ''
                
                state = getattr(r, 'state', None) or getattr(r, 'applicant_state', None) or 'USA'
                c_at = getattr(r, 'created_at', None) or getattr(r, 'submission_timestamp', None)
                date_str = c_at.strftime(DATE_FMT_LONG) if isinstance(c_at, datetime) else 'N/A'
                
                tbl = m.__tablename__.lower()
                off, default_c = _resolve_carrier_offering(tbl)

                if not raw_carriers:
                    raw_carriers = [default_c]

                for c_name in raw_carriers:
                    if not c_name or c_name.strip().lower() in ['none', 'n/a', 'null']: continue
                    key = c_name.strip().lower()
                    if key not in carrier_map:
                        carrier_map[key] = {
                            'id': f'{tbl}-{r.id}',
                            'carrierName': c_name.strip(),
                            'offerings': [off],
                            'emails': [email] if email else [],
                            'phones': [phone] if phone else [],
                            'state': state,
                            'date': date_str,
                            '_ts': c_at if isinstance(c_at, datetime) else datetime.min
                        }
                    else:
                        if off not in carrier_map[key]['offerings']:
                            carrier_map[key]['offerings'].append(off)
                        if email and email not in carrier_map[key]['emails']:
                            carrier_map[key]['emails'].append(email)
                        if phone and phone not in carrier_map[key]['phones']:
                            carrier_map[key]['phones'].append(phone)
        except Exception: pass

@router.get("/carriers-data")
def get_carriers_data(db: Session = Depends(get_session)) -> Dict[str, Any]:
    try:
        carrier_map = _collect_carrier_users(db)
        _process_record_carriers(db, carrier_map)

        for c in carrier_map.values():
            if not c['offerings']:
                c['offerings'] = ['Personal Auto', 'Health', GENERAL_LIABILITY, COMMERCIAL_AUTO, 'BOP', TERM_LIFE, 'Annuity', "Workers' Comp", 'Crop Insurance', 'Cyber Insurance']

        carrier_list = list(carrier_map.values())
        carrier_list.sort(key=lambda x: x.get('_ts', datetime.min), reverse=True)
        for c in carrier_list: c.pop('_ts', None)

        def get_cnt(models: List[Any]) -> int:
            tot = 0
            for m in models:
                try: tot += len(db.exec(select(m)).all())
                except Exception: pass
            return tot

        biz_cnt = get_cnt(BUSINESS_MODELS)
        pers_cnt = get_cnt(PERSONAL_MODELS)
        life_cnt = get_cnt(LIFE_MODELS)
        agri_cnt = get_cnt(AGRI_MODELS)
        health_cnt = get_cnt(HEALTH_MODELS)
        annuity_cnt = get_cnt(ANNUITY_MODELS)

        summary_cards = [
            {'title': 'All Insurance Carriers', 'count': str(biz_cnt + pers_cnt + life_cnt + agri_cnt + health_cnt + annuity_cnt)},
            {'title': 'Business', 'count': str(biz_cnt)},
            {'title': 'Personal', 'count': str(pers_cnt)},
            {'title': 'Life', 'count': str(life_cnt)},
            {'title': 'Agricultural', 'count': str(agri_cnt)},
            {'title': 'Health', 'count': str(health_cnt)},
            {'title': 'Annuity', 'count': str(annuity_cnt)}
        ]

        return {
            'summaryCards': summary_cards,
            'carriers': carrier_list
        }
    except Exception as e:
        print(f"[carriers-data ERROR] {e}")
        traceback.print_exc()
        return {
            'summaryCards': [],
            'carriers': []
        }

def _build_rfq_quote_item(r: Any, model: Any, category: str, now: datetime) -> Dict[str, Any]:
    f_name = getattr(r, 'first_name', None)
    l_name = getattr(r, 'last_name', None)
    if f_name or l_name:
        first = safe_decrypt_val(f_name)
        last = safe_decrypt_val(l_name)
    else:
        raw = (getattr(r, 'full_name', None) or getattr(r, 'contact_name', None) or
               getattr(r, 'applicant_name', None) or getattr(r, 'business_name', None) or
               getattr(r, 'contact_person_name', None) or 'Customer')
        clean = safe_decrypt_val(raw).strip()
        parts = clean.split()
        first = parts[0] if parts else 'Customer'
        last = ' '.join(parts[1:]) if len(parts) > 1 else ''

    raw_dob = (getattr(r, 'date_of_birth', None) or getattr(r, 'dob', None) or getattr(r, 'birth_date', None))
    dob_str = ''
    age = 0
    if raw_dob:
        try:
            if isinstance(raw_dob, str):
                raw_dob = _date.fromisoformat(raw_dob)
            dob_str = raw_dob.strftime('%d/%m/%Y')
            age = (now.date() - raw_dob).days // 365
        except Exception:
            dob_str = str(raw_dob)

    raw_gender = safe_decrypt_val(getattr(r, 'gender', None) or '')
    gender = raw_gender.split()[0].strip().capitalize() if raw_gender else 'N/A'

    raw_ssn = (getattr(r, 'ssn', None) or getattr(r, 'social_security_number', None) or getattr(r, 'tax_id', None))
    if raw_ssn:
        ssn_clean = safe_decrypt_val(str(raw_ssn)).replace('-', '').replace(' ', '')
        ssn_display = f"*******{ssn_clean[-4:]}" if len(ssn_clean) >= 4 else '*******'
    else:
        ssn_display = 'N/A'

    email = safe_decrypt_val(getattr(r, 'email', None) or getattr(r, 'email_address', None) or getattr(r, 'contact_email', None) or '') or 'N/A'
    phone = safe_decrypt_val(getattr(r, 'phone_number', None) or getattr(r, 'phone', None) or getattr(r, 'mobile_phone', None) or getattr(r, 'contact_phone', None) or getattr(r, 'mobile', None) or '') or 'N/A'
    state = safe_decrypt_val(getattr(r, 'state', None) or getattr(r, 'state_of_residence', None) or getattr(r, 'applicant_state', None) or 'USA') or 'USA'

    c_at = (getattr(r, 'created_at', None) or getattr(r, 'submission_timestamp', None) or getattr(r, 'submitted_at', None))
    date_str = c_at.strftime('%b %d, %Y') if isinstance(c_at, datetime) else 'N/A'
    sort_key = c_at if isinstance(c_at, datetime) else datetime.min

    rec_id = getattr(r, 'id', 0)
    tbl = model.__tablename__

    return {
        'id': f"{tbl}-{rec_id}",
        'tableName': tbl,
        'recordId': rec_id,
        'firstName': first or 'Customer',
        'lastName': last,
        'dob': dob_str,
        'age': age,
        'gender': gender,
        'ssn': ssn_display,
        'email': email,
        'phone': phone,
        'state': state,
        'date': date_str,
        'status': getattr(r, 'quote_status', 'Draft') or 'Draft',
        '_sort': sort_key,
    }

@router.get("/rfq-quotes")
def get_rfq_quotes(
    category: str = Query("Health"),
    db: Session = Depends(get_session)
) -> List[Dict[str, Any]]:
    try:
        CATEGORY_MODEL_MAP: Dict[str, List[Any]] = {
            "Health": HEALTH_MODELS,
            "Auto": [PersonalAutoInsurance],
            "Home": [HomeInsurance],
            RENTERS_INSURANCE: [RentersInsuranceModel],
            "Umbrella for Individuals": [UmbrellaInsuranceModel],
            TERM_LIFE: [LifeInsuranceModel],
            WHOLE_LIFE: [LifeInsuranceModel],
            UNIVERSAL_LIFE: [LifeInsuranceModel],
            INDEXED_UNIVERSAL_LIFE: [LifeInsuranceModel],
            VARIABLE_UNIVERSAL_LIFE: [LifeInsuranceModel],
            "Business": BUSINESS_MODELS,
            "BOP": [BusinessOwnerPolicyModel],
            COMMERCIAL_PROPERTY: [CommercialPropertyModel],
            COMMERCIAL_AUTO: [CommercialAutoModel],
            CYBER_LIABILITY: [Cyberliability],
            GENERAL_LIABILITY: [GeneralLiability],
            PROFESSIONAL_LIABILITY: [ProfessionalLiability],
            WORKERS_COMP: [WorkersCompensationModel],
            "Business Umbrella": [Businessumbrella],
            OCEAN_MARINE: [OceanMarineModel],
            GROUP_HEALTH: [GroupHealthModel],
            GROUP_LIFE: [GroupLife],
            "Agricultural": AGRI_MODELS,
            "Annuity": ANNUITY_MODELS,
        }

        LIFE_SUBTYPE_KEYWORDS: Dict[str, List[str]] = {
            TERM_LIFE: ["term"],
            WHOLE_LIFE: ["whole"],
            UNIVERSAL_LIFE: ["universal"],
            INDEXED_UNIVERSAL_LIFE: ["indexed"],
            VARIABLE_UNIVERSAL_LIFE: ["variable"],
        }

        models = CATEGORY_MODEL_MAP.get(category, [HealthInsuranceModel])
        results = []
        now = datetime.now()

        for model in models:
            try:
                stmt = select(model)
                if hasattr(model, 'deleted_at'):
                    stmt = stmt.where(model.deleted_at == None)  # noqa: E711
                if hasattr(model, 'created_at'):
                    stmt = stmt.order_by(model.created_at.desc())
                records = db.exec(stmt).all()
                for r in records:
                    if category in LIFE_SUBTYPE_KEYWORDS and model == LifeInsuranceModel:
                        keywords = LIFE_SUBTYPE_KEYWORDS[category]
                        ptype = (
                            str(getattr(r, 'product_type', '') or '') + ' ' +
                            str(getattr(r, 'coverage_type', '') or '') + ' ' +
                            str(getattr(r, 'insurance_type', '') or '')
                        ).lower()
                        if not any(kw in ptype for kw in keywords):
                            continue

                    results.append(_build_rfq_quote_item(r, model, category, now))
            except Exception as ex:
                print(f"[rfq-quotes] Skipping model {model}: {ex}")

        results.sort(key=lambda x: x.get('_sort', datetime.min), reverse=True)
        for rec in results:
            rec.pop('_sort', None)
        return results

    except Exception as e:
        print(f"[rfq-quotes ERROR] {e}")
        traceback.print_exc()
        return []

def _resolve_table_model(table_name: str):
    for model in ALL_MODELS:
        if getattr(model, '__tablename__', None) == table_name:
            return model
    return None

class StatusUpdateBody(BaseModel):
    status: str

@router.patch("/rfq-quotes/{table_name}/{record_id}/status")
def update_rfq_quote_status(
    table_name: str,
    record_id: int,
    body: StatusUpdateBody,
    db: Session = Depends(get_session)
):
    VALID_STATUSES = {"Draft", "Approved", "Renewal", "Cancelled", "Rejected", "Active", "Deactivate"}
    if body.status not in VALID_STATUSES:
        raise HTTPException(status_code=400, detail=f"Invalid status '{body.status}'. Must be one of: {', '.join(sorted(VALID_STATUSES))}")

    model = _resolve_table_model(table_name)
    if not model:
        raise HTTPException(status_code=404, detail=f"Unknown table '{table_name}'.")

    record = db.get(model, record_id)
    if not record:
        raise HTTPException(status_code=404, detail=f"Record {record_id} not found in '{table_name}'.")

    if not hasattr(record, 'quote_status'):
        raise HTTPException(status_code=400, detail=f"Table '{table_name}' does not support status updates.")

    record.quote_status = body.status
    if hasattr(record, 'updated_at'):
        record.updated_at = datetime.now()
    db.add(record)
    db.commit()
    db.refresh(record)
    return {"ok": True, "status": record.quote_status}

@router.delete("/rfq-quotes/{table_name}/{record_id}")
def delete_rfq_quote(
    table_name: str,
    record_id: int,
    db: Session = Depends(get_session)
):
    model = _resolve_table_model(table_name)
    if not model:
        raise HTTPException(status_code=404, detail=f"Unknown table '{table_name}'.")

    record = db.get(model, record_id)
    if not record:
        raise HTTPException(status_code=404, detail=f"Record {record_id} not found in '{table_name}'.")

    if hasattr(record, 'deleted_at'):
        record.deleted_at = datetime.now()
        if hasattr(record, 'updated_at'):
            record.updated_at = datetime.now()
        db.add(record)
        db.commit()
    else:
        db.delete(record)
        db.commit()

    return {"ok": True}

def _collect_applicant_records(db: Session) -> List[Dict[str, Any]]:
    seen_names = set()
    applicant_records = []
    for m in ALL_MODELS:
        try:
            recs = db.exec(select(m)).all()
            for r in recs:
                fn = safe_decrypt_val(getattr(r, 'first_name', None) or getattr(r, 'applicant_first_name', None) or getattr(r, 'first_name_encrypted', None))
                ln = safe_decrypt_val(getattr(r, 'last_name', None) or getattr(r, 'applicant_last_name', None) or getattr(r, 'last_name_encrypted', None))
                em = safe_decrypt_val(getattr(r, 'email', None) or getattr(r, 'contact_email', None) or getattr(r, 'applicant_email', None))
                ph = safe_decrypt_val(getattr(r, 'phone', None) or getattr(r, 'phone_number', None) or getattr(r, 'contact_phone', None))
                st = getattr(r, 'state', None) or getattr(r, 'applicant_state', None) or 'USA'
                c_at = getattr(r, 'created_at', None) or getattr(r, 'submission_timestamp', None)
                d_str = c_at.strftime(DATE_FMT_LONG) if isinstance(c_at, datetime) else 'July 14, 2025'

                name = f"{fn} {ln}".strip() if (fn or ln) else ''
                if not name and em: name = em.split('@')[0].capitalize()
                if not name: continue

                if name.lower() not in seen_names:
                    seen_names.add(name.lower())
                    applicant_records.append({
                        'name': name,
                        'email': em or f"{name.lower().replace(' ', '')}@example.com",
                        'phone': ph or '(987) 654-3210',
                        'state': st,
                        'date': d_str
                    })
        except Exception: pass

    users = db.exec(select(User)).all()
    for u in users:
        name = u.name or u.email.split('@')[0]
        if name.lower() not in seen_names:
            seen_names.add(name.lower())
            applicant_records.append({
                'name': name,
                'email': u.email,
                'phone': u.mobile or '(987) 654-1234',
                'state': u.state or 'USA',
                'date': u.created_at.strftime(DATE_FMT_LONG) if u.created_at else 'July 10, 2025'
            })

    if not applicant_records:
        applicant_records = [
            {'name': 'Sairam C', 'email': 'sairam.c@tnsservices.com', 'phone': '(987) 654-4567', 'state': 'NC', 'date': 'July 27, 2026'},
            {'name': 'Lahari C', 'email': 'lahari.c@tnsservices.com', 'phone': '(987) 654-3456', 'state': 'KS', 'date': 'July 16, 2026'},
            {'name': 'Chunchu Sairam', 'email': 'sairamchunchu1@gmail.com', 'phone': '(987) 654-3234', 'state': 'GA', 'date': 'July 10, 2026'}
        ]

    return applicant_records

@router.get("/customer-service-tickets")
def get_customer_service_tickets(db: Session = Depends(get_session)) -> List[Dict[str, Any]]:
    try:
        tickets = []
        ticket_id_counter = 1001

        service_templates = [
            ("Policy Endorsement & Address Change", "Personal Auto", "High", "Open", "Customer requested updating primary address and adding secondary driver."),
            ("Proof of Insurance (COI) Request", COMMERCIAL_PROPERTY, "Medium", IN_PROGRESS, "Certificate of Insurance requested for landlord verification."),
            ("Billing & Auto-Pay Update", "Health Insurance", "Low", "Resolved", "Updated credit card payment details for upcoming renewal."),
            ("Adding Vehicle to Policy", "Personal Auto", "High", IN_PROGRESS, "Requested endorsement for new 2025 sedan addition."),
            ("Deductible Adjustment Inquiry", "BOP", "Medium", "Pending", "Inquired about changing comprehensive deductible from $1,000 to $500."),
            ("Beneficiary Update Form", TERM_LIFE, "Low", "Open", "Requested primary beneficiary designation form."),
            ("Annual Payout Statement", "Annuity", "Medium", "Resolved", "Provided official annual annuity distribution tax statement."),
            ("Crop Damage Assessment", "Crop Insurance", "High", "Open", "Reported storm hail damage for immediate adjuster inspection."),
            ("Security Audit Verification", "Cyber Insurance", "High", IN_PROGRESS, "Submitted SOC2 compliance audit report for rate tier review."),
            ("Workers Comp Payroll Audit", "Workers Comp", "Low", "Resolved", "Completed quarterly payroll verification document submission."),
            ("Mortgagee Clause Update", "Homeowners", "Medium", "Open", "Updated lender loan number on hazard policy dec page."),
            ("Policy Cancellation Request", "Renters", "High", "Pending", "Relocated out of state; submitted proof of new lease termination.")
        ]

        applicant_records = _collect_applicant_records(db)

        for idx, tmpl in enumerate(service_templates):
            subject, category, priority, status, description = tmpl
            app = applicant_records[idx % len(applicant_records)]
            tickets.append({
                'id': f'CS-{ticket_id_counter}',
                'ticketId': f'#CS-{ticket_id_counter}',
                'customerName': app['name'],
                'email': app['email'],
                'phone': app['phone'],
                'state': app['state'],
                'subject': subject,
                'category': category,
                'priority': priority,
                'status': status,
                'description': description,
                'date': app['date']
            })
            ticket_id_counter += 1

        return tickets
    except Exception as e:
        print(f"[customer-service-tickets ERROR] {e}")
        return []
