from dotenv import load_dotenv
from pathlib import Path

ROOT_DIR = Path(__file__).parent
load_dotenv(ROOT_DIR / ".env")

import os
import io
import uuid
import random
import logging
import calendar
from datetime import datetime, timezone, timedelta, date
from typing import List, Optional, Dict, Any

import bcrypt
import jwt
import pandas as pd
from fastapi import FastAPI, APIRouter, HTTPException, Depends, Request, UploadFile, File
from fastapi.responses import JSONResponse
from starlette.middleware.cors import CORSMiddleware
from motor.motor_asyncio import AsyncIOMotorClient
from pydantic import BaseModel, EmailStr, Field

# ---------------- Constants ----------------
JWT_ALGORITHM = "HS256"
ACCESS_TOKEN_EXPIRE_MINUTES = 60 * 12  # 12h for convenience

ROLES = {"admin", "service_advisor", "manager"}

PASSENGER_CARS = ["Xpander", "Xpander Cross", "Xforce", "Pajero Sport", "Destinator"]
LCV = ["Triton", "L300", "L100 EV"]
ALL_MODELS = PASSENGER_CARS + LCV

SA_NAMES = ["Budi Santoso", "Rina Putri", "Andi Wijaya", "Dewi Lestari", "Hendro Saputra", "Maya Anggraini", "Fajar Pratama"]

PART_CATALOG = [
    ("OIL-001", "Oli Mesin Mitsubishi Genuine 4L", 285000),
    ("FLT-AIR", "Air Filter Xpander", 145000),
    ("FLT-OIL", "Oil Filter Universal", 65000),
    ("BRK-PAD", "Brake Pad Set Pajero Sport", 875000),
    ("SPK-PLG", "Spark Plug Iridium Set", 320000),
    ("BAT-12V", "Battery 12V 65AH", 1450000),
    ("TIRE-17", "Ban Triton 17 inch", 1850000),
    ("WIP-XPD", "Wiper Blade Xpander", 185000),
    ("CLT-DSC", "Clutch Disc L300", 1250000),
    ("RAD-CLT", "Radiator Coolant 4L", 165000),
    ("SHK-ABS", "Shock Absorber Depan", 1650000),
    ("FUE-FLT", "Fuel Filter L300", 235000),
    ("BLT-TIM", "Timing Belt Pajero", 950000),
    ("LMP-HEAD", "Headlamp Bulb H4", 95000),
    ("FUS-SET", "Fuse Set", 45000),
]

# ---------------- DB ----------------
mongo_url = os.environ["MONGO_URL"]
client = AsyncIOMotorClient(mongo_url)
db = client[os.environ["DB_NAME"]]

# ---------------- App ----------------
app = FastAPI(title="SDM Pekanbaru After Sales Dashboard")
api = APIRouter(prefix="/api")

logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s")
logger = logging.getLogger(__name__)


# ---------------- Models ----------------
class UserPublic(BaseModel):
    id: str
    email: EmailStr
    name: str
    role: str


class RegisterReq(BaseModel):
    email: EmailStr
    password: str
    name: str
    role: str = "service_advisor"


class LoginReq(BaseModel):
    email: EmailStr
    password: str


class TargetReq(BaseModel):
    month: str  # YYYY-MM
    wo: int = 0
    unit_entry: int = 0
    revenue_jasa: float = 0
    revenue_sparepart: float = 0
    total_revenue: float = 0
    service_order: int = 0
    profit_jasa: float = 0
    profit_sparepart: float = 0
    customer_retention: float = 0  # percent


class DailyReportReq(BaseModel):
    date: str  # YYYY-MM-DD
    wo: int
    unit_entry: int
    service_order: int
    revenue_jasa: float
    revenue_sparepart: float
    profit_jasa: float
    profit_sparepart: float
    sa_breakdown: List[Dict[str, Any]] = []  # {name, wo, revenue}
    parts_sold: List[Dict[str, Any]] = []    # {sku, name, qty, revenue}
    model_entries: List[Dict[str, Any]] = []  # {model, count}
    new_customers: int = 0
    returning_customers: int = 0


# ---------------- Auth Helpers ----------------
def hash_password(pw: str) -> str:
    return bcrypt.hashpw(pw.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")


def verify_password(pw: str, hashed: str) -> bool:
    try:
        return bcrypt.checkpw(pw.encode("utf-8"), hashed.encode("utf-8"))
    except Exception:
        return False


def create_access_token(user_id: str, email: str, role: str) -> str:
    payload = {
        "sub": user_id,
        "email": email,
        "role": role,
        "exp": datetime.now(timezone.utc) + timedelta(minutes=ACCESS_TOKEN_EXPIRE_MINUTES),
        "type": "access",
    }
    return jwt.encode(payload, os.environ["JWT_SECRET"], algorithm=JWT_ALGORITHM)


async def get_current_user(request: Request) -> dict:
    token = None
    auth = request.headers.get("Authorization", "")
    if auth.startswith("Bearer "):
        token = auth[7:]
    if not token:
        token = request.cookies.get("access_token")
    if not token:
        raise HTTPException(status_code=401, detail="Not authenticated")
    try:
        payload = jwt.decode(token, os.environ["JWT_SECRET"], algorithms=[JWT_ALGORITHM])
    except jwt.ExpiredSignatureError:
        raise HTTPException(status_code=401, detail="Token expired")
    except jwt.InvalidTokenError:
        raise HTTPException(status_code=401, detail="Invalid token")
    user = await db.users.find_one({"id": payload["sub"]}, {"_id": 0, "password_hash": 0})
    if not user:
        raise HTTPException(status_code=401, detail="User not found")
    return user


def require_role(*roles):
    async def checker(user: dict = Depends(get_current_user)):
        if user["role"] not in roles:
            raise HTTPException(status_code=403, detail="Forbidden")
        return user
    return checker


# ---------------- Seeders ----------------
async def seed_users():
    seeds = [
        (os.environ["ADMIN_EMAIL"], os.environ["ADMIN_PASSWORD"], "Administrator", "admin"),
        (os.environ["SA_EMAIL"], os.environ["SA_PASSWORD"], "Budi Santoso", "service_advisor"),
        (os.environ["MANAGER_EMAIL"], os.environ["MANAGER_PASSWORD"], "Pak Heru Manager", "manager"),
    ]
    for email, pw, name, role in seeds:
        existing = await db.users.find_one({"email": email})
        if existing is None:
            await db.users.insert_one({
                "id": str(uuid.uuid4()),
                "email": email,
                "password_hash": hash_password(pw),
                "name": name,
                "role": role,
                "created_at": datetime.now(timezone.utc).isoformat(),
            })
        elif not verify_password(pw, existing["password_hash"]):
            await db.users.update_one({"email": email}, {"$set": {"password_hash": hash_password(pw)}})


def _gen_daily(d: date) -> dict:
    # randomize but realistic for workshop
    is_weekend = d.weekday() >= 5
    factor = 0.55 if is_weekend else 1.0
    wo = int(random.randint(28, 48) * factor)
    unit_entry = int(wo + random.randint(2, 6))
    so = int(unit_entry + random.randint(0, 5))
    revenue_jasa = int(random.randint(18_000_000, 32_000_000) * factor)
    revenue_sparepart = int(random.randint(22_000_000, 45_000_000) * factor)
    profit_jasa = int(revenue_jasa * random.uniform(0.42, 0.55))
    profit_sparepart = int(revenue_sparepart * random.uniform(0.18, 0.28))

    # SA breakdown
    pick = random.sample(SA_NAMES, k=5)
    sa_break = []
    remaining_wo = wo
    for i, n in enumerate(pick):
        portion = remaining_wo if i == len(pick) - 1 else random.randint(2, max(3, remaining_wo // 2))
        portion = min(portion, max(1, remaining_wo))
        remaining_wo -= portion
        sa_break.append({
            "name": n,
            "wo": portion,
            "revenue": int((revenue_jasa + revenue_sparepart) * (portion / max(wo, 1))),
        })

    # Parts sold
    parts_pick = random.sample(PART_CATALOG, k=10)
    parts_sold = []
    for sku, nm, price in parts_pick:
        qty = random.randint(1, 12)
        parts_sold.append({"sku": sku, "name": nm, "qty": qty, "revenue": qty * price})

    # Model entries
    model_entries = []
    remaining_units = unit_entry
    pick_models = random.sample(ALL_MODELS, k=6)
    for i, m in enumerate(pick_models):
        portion = remaining_units if i == len(pick_models) - 1 else random.randint(1, max(2, remaining_units // 2))
        portion = min(portion, max(1, remaining_units))
        remaining_units -= portion
        model_entries.append({"model": m, "count": portion})
        if remaining_units <= 0:
            break

    new_cust = random.randint(3, 10)
    returning_cust = unit_entry - new_cust
    if returning_cust < 0:
        returning_cust = 0

    return {
        "id": str(uuid.uuid4()),
        "date": d.isoformat(),
        "wo": wo,
        "unit_entry": unit_entry,
        "service_order": so,
        "revenue_jasa": revenue_jasa,
        "revenue_sparepart": revenue_sparepart,
        "profit_jasa": profit_jasa,
        "profit_sparepart": profit_sparepart,
        "sa_breakdown": sa_break,
        "parts_sold": parts_sold,
        "model_entries": model_entries,
        "new_customers": new_cust,
        "returning_customers": returning_cust,
        "created_at": datetime.now(timezone.utc).isoformat(),
    }


async def seed_dummy_data(force: bool = False):
    count = await db.daily_reports.count_documents({})
    if count > 0 and not force:
        return

    if force:
        await db.daily_reports.delete_many({})
        await db.monthly_targets.delete_many({})
        await db.vehicle_population.delete_many({})

    today = datetime.now(timezone.utc).date()
    # 60 days of history
    for i in range(60, -1, -1):
        d = today - timedelta(days=i)
        await db.daily_reports.insert_one(_gen_daily(d))

    # Monthly targets for current & previous month
    for off in range(2):
        m_date = (today.replace(day=1) - timedelta(days=off * 30)).replace(day=1)
        m_key = m_date.strftime("%Y-%m")
        if await db.monthly_targets.find_one({"month": m_key}):
            continue
        await db.monthly_targets.insert_one({
            "id": str(uuid.uuid4()),
            "month": m_key,
            "wo": 1100,
            "unit_entry": 1250,
            "revenue_jasa": 750_000_000,
            "revenue_sparepart": 1_100_000_000,
            "total_revenue": 1_850_000_000,
            "service_order": 1300,
            "profit_jasa": 360_000_000,
            "profit_sparepart": 240_000_000,
            "customer_retention": 75.0,
            "updated_at": datetime.now(timezone.utc).isoformat(),
        })

    # Vehicle population
    populations = [
        ("Xpander", 4520), ("Xpander Cross", 1840), ("Xforce", 980),
        ("Pajero Sport", 2150), ("Destinator", 320),
        ("Triton", 1670), ("L300", 1290), ("L100 EV", 85),
    ]
    for m, c in populations:
        if await db.vehicle_population.find_one({"model": m}):
            continue
        await db.vehicle_population.insert_one({
            "id": str(uuid.uuid4()), "model": m, "count": c,
            "updated_at": datetime.now(timezone.utc).isoformat(),
        })


@app.on_event("startup")
async def on_startup():
    await db.users.create_index("email", unique=True)
    await db.daily_reports.create_index("date", unique=True)
    await db.monthly_targets.create_index("month", unique=True)
    await db.vehicle_population.create_index("model", unique=True)
    await seed_users()
    await seed_dummy_data(force=False)


@app.on_event("shutdown")
async def on_shutdown():
    client.close()


# ---------------- Auth Endpoints ----------------
@api.post("/auth/login")
async def login(body: LoginReq):
    user = await db.users.find_one({"email": body.email.lower()})
    if not user or not verify_password(body.password, user["password_hash"]):
        raise HTTPException(status_code=401, detail="Email atau password salah")
    token = create_access_token(user["id"], user["email"], user["role"])
    return {
        "access_token": token,
        "user": {"id": user["id"], "email": user["email"], "name": user["name"], "role": user["role"]},
    }


@api.get("/auth/me", response_model=UserPublic)
async def me(user: dict = Depends(get_current_user)):
    return UserPublic(id=user["id"], email=user["email"], name=user["name"], role=user["role"])


@api.post("/auth/register")
async def register(body: RegisterReq, _admin: dict = Depends(require_role("admin"))):
    if body.role not in ROLES:
        raise HTTPException(status_code=400, detail="Role tidak valid")
    if await db.users.find_one({"email": body.email.lower()}):
        raise HTTPException(status_code=400, detail="Email sudah terdaftar")
    user_id = str(uuid.uuid4())
    await db.users.insert_one({
        "id": user_id,
        "email": body.email.lower(),
        "password_hash": hash_password(body.password),
        "name": body.name,
        "role": body.role,
        "created_at": datetime.now(timezone.utc).isoformat(),
    })
    return {"id": user_id, "email": body.email.lower(), "name": body.name, "role": body.role}


# ---------------- Helper queries ----------------
async def get_report_for_date(d: str) -> Optional[dict]:
    return await db.daily_reports.find_one({"date": d}, {"_id": 0})


async def get_reports_in_range(start: date, end: date) -> List[dict]:
    cursor = db.daily_reports.find(
        {"date": {"$gte": start.isoformat(), "$lte": end.isoformat()}},
        {"_id": 0},
    ).sort("date", 1)
    return await cursor.to_list(length=400)


async def get_current_month_target() -> dict:
    m = datetime.now(timezone.utc).date().strftime("%Y-%m")
    t = await db.monthly_targets.find_one({"month": m}, {"_id": 0})
    return t or {}


def sum_field(reports: list, field: str) -> float:
    return float(sum(r.get(field, 0) for r in reports))


# ---------------- Dashboard Endpoints ----------------
@api.get("/dashboard/home")
async def dashboard_home(_user: dict = Depends(get_current_user)):
    today = datetime.now(timezone.utc).date()
    today_rep = await get_report_for_date(today.isoformat())
    if not today_rep:
        # find latest
        latest = await db.daily_reports.find_one({}, {"_id": 0}, sort=[("date", -1)])
        today_rep = latest or {}

    # 7-day trend
    last7 = await get_reports_in_range(today - timedelta(days=6), today)
    trend7 = [
        {
            "date": r["date"],
            "wo": r["wo"],
            "unit_entry": r["unit_entry"],
            "revenue": r["revenue_jasa"] + r["revenue_sparepart"],
        }
        for r in last7
    ]

    # Month-to-date
    start_month = today.replace(day=1)
    mtd = await get_reports_in_range(start_month, today)
    mtd_chart = [
        {
            "date": r["date"],
            "revenue_jasa": r["revenue_jasa"],
            "revenue_sparepart": r["revenue_sparepart"],
        }
        for r in mtd
    ]
    mtd_total_rev = sum_field(mtd, "revenue_jasa") + sum_field(mtd, "revenue_sparepart")
    target = await get_current_month_target()
    target_total = target.get("total_revenue", 0)
    achievement = (mtd_total_rev / target_total * 100) if target_total else 0

    # Top 5 Service Advisor (this month)
    sa_agg: Dict[str, dict] = {}
    for r in mtd:
        for s in r.get("sa_breakdown", []):
            entry = sa_agg.setdefault(s["name"], {"name": s["name"], "wo": 0, "revenue": 0})
            entry["wo"] += s.get("wo", 0)
            entry["revenue"] += s.get("revenue", 0)
    top_sa = sorted(sa_agg.values(), key=lambda x: x["revenue"], reverse=True)[:5]

    # Top 10 sparepart this month
    parts_agg: Dict[str, dict] = {}
    for r in mtd:
        for p in r.get("parts_sold", []):
            entry = parts_agg.setdefault(p["sku"], {"sku": p["sku"], "name": p["name"], "qty": 0, "revenue": 0})
            entry["qty"] += p.get("qty", 0)
            entry["revenue"] += p.get("revenue", 0)
    top_parts = sorted(parts_agg.values(), key=lambda x: x["qty"], reverse=True)[:10]

    # Top 5 model this month
    model_agg: Dict[str, int] = {}
    for r in mtd:
        for m in r.get("model_entries", []):
            model_agg[m["model"]] = model_agg.get(m["model"], 0) + m.get("count", 0)
    top_models = sorted(
        [{"model": k, "count": v} for k, v in model_agg.items()],
        key=lambda x: x["count"],
        reverse=True,
    )[:5]

    today_total_rev = today_rep.get("revenue_jasa", 0) + today_rep.get("revenue_sparepart", 0)
    today_target_daily = (target.get("total_revenue", 0) / 30) if target else 0
    today_achievement = (today_total_rev / today_target_daily * 100) if today_target_daily else 0

    return {
        "today": {
            "date": today_rep.get("date", today.isoformat()),
            "wo": today_rep.get("wo", 0),
            "unit_entry": today_rep.get("unit_entry", 0),
            "revenue_jasa": today_rep.get("revenue_jasa", 0),
            "revenue_sparepart": today_rep.get("revenue_sparepart", 0),
            "revenue_total": today_total_rev,
            "service_order": today_rep.get("service_order", 0),
            "achievement_percent": round(today_achievement, 1),
        },
        "trend_7days": trend7,
        "mtd_chart": mtd_chart,
        "mtd_total_revenue": mtd_total_rev,
        "month_target_total": target_total,
        "month_achievement_percent": round(achievement, 1),
        "top_service_advisors": top_sa,
        "top_parts": top_parts,
        "top_models": top_models,
    }


@api.get("/dashboard/daily")
async def dashboard_daily(_user: dict = Depends(get_current_user)):
    today = datetime.now(timezone.utc).date()
    last30 = await get_reports_in_range(today - timedelta(days=29), today)
    chart = [
        {
            "date": r["date"],
            "wo": r["wo"],
            "unit_entry": r["unit_entry"],
            "service_order": r["service_order"],
            "revenue": r["revenue_jasa"] + r["revenue_sparepart"],
        }
        for r in last30
    ]
    today_rep = await get_report_for_date(today.isoformat()) or (last30[-1] if last30 else {})
    return {"chart": chart, "today": today_rep}


@api.get("/dashboard/service-advisor")
async def dashboard_sa(_user: dict = Depends(get_current_user)):
    today = datetime.now(timezone.utc).date()
    mtd = await get_reports_in_range(today.replace(day=1), today)
    sa_agg: Dict[str, dict] = {}
    for r in mtd:
        for s in r.get("sa_breakdown", []):
            entry = sa_agg.setdefault(s["name"], {"name": s["name"], "wo": 0, "revenue": 0, "days": 0})
            entry["wo"] += s.get("wo", 0)
            entry["revenue"] += s.get("revenue", 0)
            entry["days"] += 1
    ranking = sorted(sa_agg.values(), key=lambda x: x["revenue"], reverse=True)
    for r in ranking:
        r["avg_revenue_per_wo"] = round(r["revenue"] / r["wo"]) if r["wo"] else 0
    return {"ranking": ranking, "month": today.strftime("%Y-%m")}


@api.get("/dashboard/sparepart")
async def dashboard_sparepart(_user: dict = Depends(get_current_user)):
    today = datetime.now(timezone.utc).date()
    mtd = await get_reports_in_range(today.replace(day=1), today)
    parts_agg: Dict[str, dict] = {}
    for r in mtd:
        for p in r.get("parts_sold", []):
            entry = parts_agg.setdefault(p["sku"], {"sku": p["sku"], "name": p["name"], "qty": 0, "revenue": 0})
            entry["qty"] += p.get("qty", 0)
            entry["revenue"] += p.get("revenue", 0)
    all_parts = sorted(parts_agg.values(), key=lambda x: x["qty"], reverse=True)
    fast_moving = all_parts[:10]
    slow_moving = sorted(parts_agg.values(), key=lambda x: x["qty"])[:10]
    total_revenue = sum(p["revenue"] for p in all_parts)
    total_qty = sum(p["qty"] for p in all_parts)
    return {
        "fast_moving": fast_moving,
        "slow_moving": slow_moving,
        "all_parts": all_parts,
        "total_revenue_sparepart": total_revenue,
        "total_qty_sold": total_qty,
    }


@api.get("/dashboard/vehicle-population")
async def dashboard_vehicle_pop(_user: dict = Depends(get_current_user)):
    pop = await db.vehicle_population.find({}, {"_id": 0}).to_list(length=50)
    pop_sorted = sorted(pop, key=lambda x: x["count"], reverse=True)
    passenger = [p for p in pop_sorted if p["model"] in PASSENGER_CARS]
    lcv = [p for p in pop_sorted if p["model"] in LCV]
    total = sum(p["count"] for p in pop_sorted)
    return {
        "all": pop_sorted,
        "passenger_car": passenger,
        "light_commercial": lcv,
        "total": total,
        "passenger_total": sum(p["count"] for p in passenger),
        "lcv_total": sum(p["count"] for p in lcv),
    }


@api.get("/dashboard/target")
async def dashboard_target(_user: dict = Depends(get_current_user)):
    today = datetime.now(timezone.utc).date()
    target = await get_current_month_target()
    mtd = await get_reports_in_range(today.replace(day=1), today)
    actual = {
        "wo": int(sum_field(mtd, "wo")),
        "unit_entry": int(sum_field(mtd, "unit_entry")),
        "service_order": int(sum_field(mtd, "service_order")),
        "revenue_jasa": sum_field(mtd, "revenue_jasa"),
        "revenue_sparepart": sum_field(mtd, "revenue_sparepart"),
        "total_revenue": sum_field(mtd, "revenue_jasa") + sum_field(mtd, "revenue_sparepart"),
        "profit_jasa": sum_field(mtd, "profit_jasa"),
        "profit_sparepart": sum_field(mtd, "profit_sparepart"),
    }
    # Customer retention
    new_c = sum(r.get("new_customers", 0) for r in mtd)
    ret_c = sum(r.get("returning_customers", 0) for r in mtd)
    total_c = new_c + ret_c
    actual["customer_retention"] = round(ret_c / total_c * 100, 1) if total_c else 0

    rows = []
    fields = [
        ("WO", "wo", ""),
        ("Unit Entry", "unit_entry", ""),
        ("Service Order", "service_order", ""),
        ("Revenue Jasa", "revenue_jasa", "Rp"),
        ("Revenue Sparepart", "revenue_sparepart", "Rp"),
        ("Total Revenue", "total_revenue", "Rp"),
        ("Profit Jasa", "profit_jasa", "Rp"),
        ("Profit Sparepart", "profit_sparepart", "Rp"),
        ("Customer Retention", "customer_retention", "%"),
    ]
    for label, key, unit in fields:
        t = target.get(key, 0)
        a = actual.get(key, 0)
        ach = (a / t * 100) if t else 0
        rows.append({
            "label": label,
            "key": key,
            "unit": unit,
            "target": t,
            "actual": a,
            "achievement": round(ach, 1),
        })
    days_in_month = calendar.monthrange(today.year, today.month)[1]
    today_day = today.day
    elapsed_pct = round((today_day / days_in_month) * 100, 1)
    return {
        "month": today.strftime("%Y-%m"),
        "rows": rows,
        "elapsed_percent": elapsed_pct,
        "today_day": today_day,
    }


@api.get("/dashboard/morning-meeting")
async def dashboard_morning(_user: dict = Depends(get_current_user)):
    today = datetime.now(timezone.utc).date()
    yesterday = today - timedelta(days=1)
    y_rep = await get_report_for_date(yesterday.isoformat())
    target = await get_current_month_target()
    daily_target_wo = target.get("wo", 0) / 30 if target else 0
    daily_target_rev = target.get("total_revenue", 0) / 30 if target else 0
    # 7 day average
    last7 = await get_reports_in_range(today - timedelta(days=7), today - timedelta(days=1))
    avg7_wo = sum_field(last7, "wo") / max(len(last7), 1)
    avg7_rev = (sum_field(last7, "revenue_jasa") + sum_field(last7, "revenue_sparepart")) / max(len(last7), 1)
    # Top SA yesterday
    top_sa_yest = sorted(y_rep.get("sa_breakdown", []), key=lambda x: x["revenue"], reverse=True)[:5] if y_rep else []
    return {
        "yesterday": y_rep or {},
        "today_target": {
            "wo": round(daily_target_wo),
            "revenue": round(daily_target_rev),
        },
        "avg_7days": {
            "wo": round(avg7_wo, 1),
            "revenue": round(avg7_rev),
        },
        "top_sa_yesterday": top_sa_yest,
    }


@api.get("/dashboard/revenue")
async def dashboard_revenue(_user: dict = Depends(get_current_user)):
    today = datetime.now(timezone.utc).date()
    mtd = await get_reports_in_range(today.replace(day=1), today)
    chart = [
        {
            "date": r["date"],
            "revenue_jasa": r["revenue_jasa"],
            "revenue_sparepart": r["revenue_sparepart"],
            "total": r["revenue_jasa"] + r["revenue_sparepart"],
            "profit_jasa": r["profit_jasa"],
            "profit_sparepart": r["profit_sparepart"],
        }
        for r in mtd
    ]
    total_jasa = sum_field(mtd, "revenue_jasa")
    total_part = sum_field(mtd, "revenue_sparepart")
    total_rev = total_jasa + total_part
    profit_jasa = sum_field(mtd, "profit_jasa")
    profit_part = sum_field(mtd, "profit_sparepart")
    total_ue = int(sum_field(mtd, "unit_entry"))
    total_wo = int(sum_field(mtd, "wo"))
    return {
        "chart": chart,
        "total_revenue_jasa": total_jasa,
        "total_revenue_sparepart": total_part,
        "total_revenue": total_rev,
        "profit_jasa": profit_jasa,
        "profit_sparepart": profit_part,
        "total_profit": profit_jasa + profit_part,
        "avg_revenue_per_unit_entry": round(total_rev / total_ue) if total_ue else 0,
        "avg_revenue_per_ro": round(total_rev / total_wo) if total_wo else 0,
        "breakdown_pie": [
            {"name": "Revenue Jasa", "value": total_jasa},
            {"name": "Revenue Sparepart", "value": total_part},
        ],
    }


@api.get("/dashboard/unit-entry")
async def dashboard_unit_entry(_user: dict = Depends(get_current_user)):
    today = datetime.now(timezone.utc).date()
    last30 = await get_reports_in_range(today - timedelta(days=29), today)
    chart = [{"date": r["date"], "unit_entry": r["unit_entry"], "wo": r["wo"]} for r in last30]
    total_ue = int(sum_field(last30, "unit_entry"))
    total_wo = int(sum_field(last30, "wo"))
    # By model
    model_agg: Dict[str, int] = {}
    for r in last30:
        for m in r.get("model_entries", []):
            model_agg[m["model"]] = model_agg.get(m["model"], 0) + m.get("count", 0)
    by_model = sorted([{"model": k, "count": v} for k, v in model_agg.items()], key=lambda x: x["count"], reverse=True)
    return {
        "chart": chart,
        "total_unit_entry_30d": total_ue,
        "total_wo_30d": total_wo,
        "avg_unit_entry_daily": round(total_ue / max(len(last30), 1), 1),
        "by_model": by_model,
    }


@api.get("/dashboard/customer-retention")
async def dashboard_retention(_user: dict = Depends(get_current_user)):
    today = datetime.now(timezone.utc).date()
    last30 = await get_reports_in_range(today - timedelta(days=29), today)
    chart = []
    total_new = 0
    total_ret = 0
    for r in last30:
        new_c = r.get("new_customers", 0)
        ret_c = r.get("returning_customers", 0)
        total_new += new_c
        total_ret += ret_c
        tot = new_c + ret_c
        rate = (ret_c / tot * 100) if tot else 0
        chart.append({"date": r["date"], "new": new_c, "returning": ret_c, "retention_rate": round(rate, 1)})
    grand_tot = total_new + total_ret
    overall_rate = (total_ret / grand_tot * 100) if grand_tot else 0
    return {
        "chart": chart,
        "total_new": total_new,
        "total_returning": total_ret,
        "overall_retention_rate": round(overall_rate, 1),
    }


# ---------------- Settings (Targets) ----------------
@api.get("/targets")
async def list_targets(_user: dict = Depends(get_current_user)):
    items = await db.monthly_targets.find({}, {"_id": 0}).sort("month", -1).to_list(length=24)
    return items


@api.get("/targets/current")
async def current_target(_user: dict = Depends(get_current_user)):
    m = datetime.now(timezone.utc).date().strftime("%Y-%m")
    t = await db.monthly_targets.find_one({"month": m}, {"_id": 0})
    return t or {"month": m}


@api.post("/targets")
async def upsert_target(body: TargetReq, _admin: dict = Depends(require_role("admin"))):
    payload = body.model_dump()
    payload["updated_at"] = datetime.now(timezone.utc).isoformat()
    if payload.get("total_revenue", 0) == 0:
        payload["total_revenue"] = payload.get("revenue_jasa", 0) + payload.get("revenue_sparepart", 0)
    await db.monthly_targets.update_one(
        {"month": body.month},
        {"$set": payload, "$setOnInsert": {"id": str(uuid.uuid4())}},
        upsert=True,
    )
    return {"ok": True, "month": body.month}


# ---------------- Upload Data ----------------
@api.post("/upload/daily-report")
async def upload_daily(file: UploadFile = File(...), _admin: dict = Depends(require_role("admin"))):
    """Upload Excel/CSV with daily report rows.
    Required columns: date, wo, unit_entry, service_order, revenue_jasa, revenue_sparepart, profit_jasa, profit_sparepart
    Optional columns: new_customers, returning_customers
    """
    content = await file.read()
    fname = (file.filename or "").lower()
    try:
        if fname.endswith(".csv"):
            df = pd.read_csv(io.BytesIO(content))
        elif fname.endswith(".xlsx") or fname.endswith(".xls"):
            df = pd.read_excel(io.BytesIO(content))
        else:
            raise HTTPException(status_code=400, detail="Format file harus .csv atau .xlsx")
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Gagal membaca file: {str(e)}")

    required = {"date", "wo", "unit_entry", "service_order", "revenue_jasa", "revenue_sparepart", "profit_jasa", "profit_sparepart"}
    missing = required - set(df.columns)
    if missing:
        raise HTTPException(status_code=400, detail=f"Kolom wajib hilang: {', '.join(missing)}")

    inserted = 0
    updated = 0
    errors = []
    for idx, row in df.iterrows():
        try:
            d_val = row["date"]
            if isinstance(d_val, str):
                d_iso = pd.to_datetime(d_val).date().isoformat()
            else:
                d_iso = pd.to_datetime(d_val).date().isoformat()
            doc = {
                "date": d_iso,
                "wo": int(row["wo"]),
                "unit_entry": int(row["unit_entry"]),
                "service_order": int(row["service_order"]),
                "revenue_jasa": float(row["revenue_jasa"]),
                "revenue_sparepart": float(row["revenue_sparepart"]),
                "profit_jasa": float(row["profit_jasa"]),
                "profit_sparepart": float(row["profit_sparepart"]),
                "new_customers": int(row["new_customers"]) if "new_customers" in df.columns and not pd.isna(row.get("new_customers")) else 0,
                "returning_customers": int(row["returning_customers"]) if "returning_customers" in df.columns and not pd.isna(row.get("returning_customers")) else 0,
                "sa_breakdown": [],
                "parts_sold": [],
                "model_entries": [],
                "updated_at": datetime.now(timezone.utc).isoformat(),
            }
            existing = await db.daily_reports.find_one({"date": d_iso})
            if existing:
                await db.daily_reports.update_one({"date": d_iso}, {"$set": doc})
                updated += 1
            else:
                doc["id"] = str(uuid.uuid4())
                doc["created_at"] = datetime.now(timezone.utc).isoformat()
                await db.daily_reports.insert_one(doc)
                inserted += 1
        except Exception as e:
            errors.append({"row": int(idx) + 2, "error": str(e)})

    return {"inserted": inserted, "updated": updated, "errors": errors, "total_rows": int(len(df))}


@api.get("/upload/template")
async def download_template(_user: dict = Depends(get_current_user)):
    template = (
        "date,wo,unit_entry,service_order,revenue_jasa,revenue_sparepart,profit_jasa,profit_sparepart,new_customers,returning_customers\n"
        "2025-01-15,35,40,42,22500000,32000000,11000000,7500000,5,35\n"
        "2025-01-16,38,43,45,24800000,34500000,12100000,8200000,6,37\n"
    )
    return JSONResponse(content={"filename": "daily_report_template.csv", "content": template})


# ---------------- Reset Data ----------------
@api.post("/admin/reset-data")
async def reset_data(_admin: dict = Depends(require_role("admin"))):
    await db.daily_reports.delete_many({})
    await db.monthly_targets.delete_many({})
    await db.vehicle_population.delete_many({})
    return {"ok": True, "message": "Semua data operasional telah dihapus"}


@api.post("/admin/seed-dummy")
async def reseed(_admin: dict = Depends(require_role("admin"))):
    await seed_dummy_data(force=True)
    return {"ok": True, "message": "Data dummy berhasil dimuat ulang"}


@api.get("/")
async def root():
    return {"name": "SDM Pekanbaru After Sales Dashboard API", "status": "ok"}


app.include_router(api)

app.add_middleware(
    CORSMiddleware,
    allow_credentials=True,
    allow_origins=os.environ.get("CORS_ORIGINS", "*").split(","),
    allow_methods=["*"],
    allow_headers=["*"],
)