jetour-bi/app.py

"""Jetour BI Dashboard — FastAPI backend serving CMS BI data from PostgreSQL."""
from datetime import datetime, timedelta, timezone
from fastapi import FastAPI, Query
from fastapi.staticfiles import StaticFiles
import psycopg
from psycopg.rows import dict_row

app = FastAPI(title="Jetour BI Dashboard")

# ── Auth ──────────────────────────────────────────────────────────────────
from datetime import timedelta as td
from jose import JWTError, jwt
from passlib.context import CryptContext
from fastapi import Depends, HTTPException, Security, status
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
from pydantic import BaseModel
from typing import Optional
import secrets

# Config
SECRET_KEY = secrets.token_hex(32)  # regenerates on restart — set env var for stability
ALGORITHM = "HS256"
ACCESS_TOKEN_EXPIRE_MINUTES = 480  # 8 hours
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
security = HTTPBearer()

# Models
class UserCreate(BaseModel):
    username: str
    password: str
    email: Optional[str] = None
    role: str = "standard"

class UserLogin(BaseModel):
    username: str
    password: str

class ForgotPassword(BaseModel):
    email: str

class ResetPassword(BaseModel):
    token: str
    password: str

class UserOut(BaseModel):
    id: int
    username: str
    email: Optional[str] = None
    role: str
    created_at: str

def ensure_users_table(c):
    cur = c.cursor()
    # Add email column if missing (migration-safe)
    cur.execute("SELECT column_name FROM information_schema.columns WHERE table_name='dashboard_users' AND column_name='email'")
    if not cur.fetchone():
        cur.execute("ALTER TABLE dashboard_users ADD COLUMN email VARCHAR(200)")
        c.commit()
    # Create tables if missing
    cur.execute("""
        CREATE TABLE IF NOT EXISTS dashboard_users (
            id BIGSERIAL PRIMARY KEY,
            username VARCHAR(100) UNIQUE NOT NULL,
            password_hash VARCHAR(200) NOT NULL,
            email VARCHAR(200),
            role VARCHAR(20) NOT NULL DEFAULT 'standard',
            created_at TIMESTAMPTZ DEFAULT NOW()
        )
    """)
    cur.execute("""
        CREATE TABLE IF NOT EXISTS password_reset_tokens (
            id BIGSERIAL PRIMARY KEY,
            email VARCHAR(200) NOT NULL,
            token VARCHAR(100) UNIQUE NOT NULL,
            expires_at TIMESTAMPTZ NOT NULL,
            used BOOLEAN DEFAULT false,
            created_at TIMESTAMPTZ DEFAULT NOW()
        )
    """)
    c.commit()

def create_default_admin(c):
    """Create default admin if no users exist."""
    row = c.execute("SELECT count(*) as n FROM dashboard_users").fetchone()
    if row["n"] == 0:
        c.execute(
            "INSERT INTO dashboard_users (username, password_hash, role) VALUES (%s, %s, %s)",
            ("admin", pwd_context.hash("admin123"), "admin"),
        )
        c.commit()
        return True
    return False

def get_user(c, username: str):
    row = c.execute("SELECT * FROM dashboard_users WHERE username = %s", (username,)).fetchone()
    return row

def verify_password(plain, hashed):
    return pwd_context.verify(plain, hashed)

def create_access_token(data: dict, expires_delta: Optional[td] = None):
    to_encode = data.copy()
    expire = datetime.now(timezone.utc) + (expires_delta or td(minutes=ACCESS_TOKEN_EXPIRE_MINUTES))
    to_encode.update({"exp": expire})
    return jwt.encode(to_encode, SECRET_KEY, algorithm=ALGORITHM)

async def get_current_user(credentials: HTTPAuthorizationCredentials = Security(security)):
    """Dependency: validate JWT and return user dict."""
    token = credentials.credentials
    try:
        payload = jwt.decode(token, SECRET_KEY, algorithms=[ALGORITHM])
        username: str = payload.get("sub")
        if username is None:
            raise HTTPException(status_code=401, detail="Invalid token")
        return {"username": username, "role": payload.get("role", "standard")}
    except JWTError:
        raise HTTPException(status_code=401, detail="Invalid token")

def require_admin(user=Depends(get_current_user)):
    """Dependency: require admin role."""
    if user["role"] != "admin":
        raise HTTPException(status_code=403, detail="Admin access required")
    return user

# ── Auth Endpoints ────────────────────────────────────────────────────────
@app.post("/api/auth/login")
def login(body: UserLogin):
    c = conn()
    try:
        ensure_users_table(c)
        create_default_admin(c)
        user = get_user(c, body.username)
        if not user or not verify_password(body.password, user["password_hash"]):
            raise HTTPException(status_code=401, detail="Invalid credentials")
        token = create_access_token({"sub": user["username"], "role": user["role"]})
        return {"token": token, "user": {"username": user["username"], "role": user["role"]}}
    finally:
        c.close()

@app.get("/api/auth/me")
def me(user=Depends(get_current_user)):
    return user

@app.post("/api/auth/change-password")
def change_password(body: dict, user=Depends(get_current_user)):
    current_pw = body.get("current")
    new_pw = body.get("password")
    if not current_pw or not new_pw:
        raise HTTPException(status_code=400, detail="Current and new password required")
    c = conn()
    try:
        db_user = get_user(c, user["username"])
        if not db_user or not verify_password(current_pw, db_user["password_hash"]):
            raise HTTPException(status_code=401, detail="Current password incorrect")
        cur = c.cursor()
        cur.execute("UPDATE dashboard_users SET password_hash = %s WHERE username = %s",
                    (pwd_context.hash(new_pw), user["username"]))
        c.commit()
        return {"status": "ok"}
    finally:
        c.close()

@app.post("/api/auth/forgot-password")
def forgot_password(body: ForgotPassword):
    """Send password reset email if account exists."""
    if not body.email or "@" not in body.email:
        raise HTTPException(status_code=400, detail="Valid email required")
    c = conn()
    try:
        ensure_users_table(c)
        cur = c.cursor()
        cur.execute("SELECT username, email FROM dashboard_users WHERE email = %s", (body.email,))
        user_row = cur.fetchone()
        if not user_row:
            # Don't reveal if email exists — always return ok
            return {"status": "ok", "message": "If the email exists, a reset link has been sent."}
        # Generate token
        token = secrets.token_urlsafe(32)
        expires = datetime.now(timezone.utc) + td(hours=1)
        cur.execute(
            "INSERT INTO password_reset_tokens (email, token, expires_at) VALUES (%s, %s, %s)",
            (body.email, token, expires),
        )
        c.commit()
        # Try to send email via SMTP
        reset_url = f"http://100.91.25.139:8765/reset-password?token={token}"
        try:
            _send_email(
                to=body.email,
                subject="Jetour BI — Password Reset",
                body=f"Click the link below to reset your password:\n\n{reset_url}\n\nThis link expires in 1 hour.",
            )
        except Exception:
            pass  # SMTP may not be configured — token is still in DB
        return {"status": "ok", "message": "If the email exists, a reset link has been sent."}
    finally:
        c.close()

@app.post("/api/auth/reset-password")
def reset_password(body: ResetPassword):
    """Reset password using token from email."""
    if len(body.password) < 3:
        raise HTTPException(status_code=400, detail="Password must be at least 3 characters")
    c = conn()
    try:
        ensure_users_table(c)
        cur = c.cursor()
        cur.execute(
            "SELECT email, expires_at, used FROM password_reset_tokens WHERE token = %s",
            (body.token,),
        )
        row = cur.fetchone()
        if not row:
            raise HTTPException(status_code=400, detail="Invalid or expired reset token")
        if row["used"]:
            raise HTTPException(status_code=400, detail="This reset link has already been used")
        if row["expires_at"] < datetime.now(timezone.utc):
            raise HTTPException(status_code=400, detail="This reset link has expired")
        # Update password
        cur.execute(
            "UPDATE dashboard_users SET password_hash = %s WHERE email = %s",
            (pwd_context.hash(body.password), row["email"]),
        )
        cur.execute("UPDATE password_reset_tokens SET used = true WHERE token = %s", (body.token,))
        c.commit()
        return {"status": "ok", "message": "Password has been reset. You can now log in."}
    finally:
        c.close()

# ── Email helper ───────────────────────────────────────────────────────
def _get_smtp_settings(c):
    """Read SMTP settings from app_settings table."""
    try:
        cur = c.cursor()
        cur.execute("SELECT key, value FROM app_settings WHERE key LIKE 'smtp_%'")
        rows = cur.fetchall()
        return {r["key"]: r["value"] for r in rows}
    except Exception:
        return {}

def _send_email(to, subject, body):
    """Send email using configured SMTP. Raises on failure."""
    import smtplib
    from email.mime.text import MIMEText
    c = conn()
    try:
        settings = _get_smtp_settings(c)
        if not settings.get("smtp_host"):
            raise Exception("SMTP not configured")
        msg = MIMEText(body)
        msg["Subject"] = subject
        msg["From"] = settings.get("smtp_from", "reports@jetour.mu")
        msg["To"] = to
        with smtplib.SMTP(settings["smtp_host"], int(settings.get("smtp_port", 587))) as server:
            if settings.get("smtp_use_tls", "true") == "true":
                server.starttls()
            if settings.get("smtp_user"):
                server.login(settings["smtp_user"], settings.get("smtp_pass", ""))
            server.send_message(msg)
    finally:
        c.close()

@app.get("/api/users")
def list_users(user=Depends(require_admin)):
    c = conn()
    try:
        ensure_users_table(c)
        rows = c.execute("SELECT id, username, email, role, created_at FROM dashboard_users ORDER BY id").fetchall()
        return [{"id": r["id"], "username": r["username"], "email": r["email"], "role": r["role"], "created_at": str(r["created_at"])} for r in rows]
    finally:
        c.close()

@app.post("/api/users")
def create_user(body: UserCreate, user=Depends(require_admin)):
    if body.role not in ("admin", "standard"):
        raise HTTPException(status_code=400, detail="Role must be 'admin' or 'standard'")
    c = conn()
    try:
        ensure_users_table(c)
        existing = get_user(c, body.username)
        if existing:
            raise HTTPException(status_code=409, detail="Username already exists")
        c.execute(
            "INSERT INTO dashboard_users (username, password_hash, email, role) VALUES (%s, %s, %s, %s)",
            (body.username, pwd_context.hash(body.password), body.email, body.role),
        )
        c.commit()
        return {"status": "ok", "username": body.username, "role": body.role}
    finally:
        c.close()

@app.delete("/api/users/{username}")
def delete_user(username: str, user=Depends(require_admin)):
    if username == "admin":
        raise HTTPException(status_code=400, detail="Cannot delete default admin")
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("DELETE FROM dashboard_users WHERE username = %s RETURNING id", (username,))
        deleted = cur.fetchone()
        if not deleted:
            raise HTTPException(status_code=404, detail="User not found")
        c.commit()
        return {"status": "deleted", "username": username}
    finally:
        c.close()

@app.put("/api/users/{username}")
def update_user(username: str, body: dict, user=Depends(require_admin)):
    """Update user email, role, and optionally password."""
    c = conn()
    try:
        ensure_users_table(c)
        cur = c.cursor()
        cur.execute("SELECT id FROM dashboard_users WHERE username = %s", (username,))
        if not cur.fetchone():
            raise HTTPException(status_code=404, detail="User not found")
        updates = []
        params = []
        if "email" in body:
            updates.append("email = %s")
            params.append(body["email"])
        if "role" in body and body["role"] in ("admin", "standard"):
            if username == "admin" and body["role"] != "admin":
                raise HTTPException(status_code=400, detail="Cannot demote default admin")
            updates.append("role = %s")
            params.append(body["role"])
        if "password" in body and body["password"]:
            updates.append("password_hash = %s")
            params.append(pwd_context.hash(body["password"]))
        if not updates:
            raise HTTPException(status_code=400, detail="No fields to update")
        params.append(username)
        cur.execute(f"UPDATE dashboard_users SET {', '.join(updates)} WHERE username = %s", params)
        c.commit()
        return {"status": "updated", "username": username}
    finally:
        c.close()

# ── Protected API (example: settings) ─────────────────────────────────────
@app.get("/api/settings")
def get_settings(user=Depends(require_admin)):
    return {"app_name": "Jetour BI Dashboard", "version": "2.0", "features": ["period_selector", "vehicle_orders", "access_control"]}

# ── Existing API endpoints ────────────────────────────────────────────────

# Database connection
import os
from urllib.parse import quote_plus

def get_db_url():
    """Build database URL from env vars or fall back to defaults."""
    url = os.environ.get("DATABASE_URL")
    if url:
        return url
    host = os.environ.get("DB_HOST", "100.91.65.42")
    port = os.environ.get("DB_PORT", "5432")
    db = os.environ.get("DB_NAME", "jetour-bi")
    user = os.environ.get("DB_USER", "postgres")
    password = os.environ.get("DB_PASSWORD", "")
    if not password:
        password = "#AlteraM@01"
    return f"postgresql://{user}:{quote_plus(password)}@{host}:{port}/{db}?sslmode=disable"

DB_URL = get_db_url()

def conn():
    return psycopg.connect(DB_URL, row_factory=dict_row)

def date_filter(period):
    now = datetime.now(timezone.utc)
    if period == "mtd":
        return "WHERE dt_prospect_created >= %(s)s", {"s": now.replace(day=1).strftime("%Y-%m-%d")}
    elif period == "last30":
        return "WHERE dt_prospect_created >= %(s)s", {"s": (now - timedelta(days=30)).strftime("%Y-%m-%d")}
    elif period and period != "all" and period.endswith("d"):
        days = int(period[:-1])
        return "WHERE dt_prospect_created >= %(s)s", {"s": (now - timedelta(days=days)).strftime("%Y-%m-%d")}
    elif period and period != "all" and len(period) == 7:  # YYYY-MM
        return "WHERE dt_prospect_created >= %(s)s AND dt_prospect_created < %(e)s", {
            "s": period + "-01",
            "e": (datetime.strptime(period + "-01", "%Y-%m-%d").replace(day=28) + timedelta(days=4)).replace(day=1).strftime("%Y-%m-%d")
        }
    return "", {}

@app.get("/api/months")
def available_months():
    """Return distinct year-months with data, plus year list for dropdown."""
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("""
            SELECT to_char(dt_prospect_created,'YYYY-MM') as m,
                   to_char(dt_prospect_created,'YYYY') as y,
                   count(*) as n
            FROM prospects WHERE dt_prospect_created IS NOT NULL
            GROUP BY m, y ORDER BY m
        """)
        months = [{"month": r["m"], "year": r["y"], "count": r["n"]} for r in cur.fetchall()]
        years = sorted(set(r["year"] for r in months), reverse=True)
        return {"months": months, "years": years}
    finally:
        c.close()

@app.get("/api/summary")
def summary(period: str = Query("all")):
    c = conn()
    try:
        df, params = date_filter(period)
        cur = c.cursor()
        cur.execute(f"SELECT count(*) as n FROM prospects {df}", params)
        tp = cur.fetchone()["n"]
        cur.execute(f"SELECT prospect_status, count(*) as n FROM prospects {df} GROUP BY prospect_status ORDER BY n DESC", params)
        st = {r["prospect_status"]: r["n"] for r in cur.fetchall()}
        cur.execute(f"SELECT count(*) as n FROM prospect_otps o JOIN prospects p ON o.prospect_id=p.id {df.replace('dt_prospect_created','p.dt_prospect_created')}", params)
        to = cur.fetchone()["n"]
        cur.execute("SELECT otp_new_used, count(*) as n FROM prospect_otps GROUP BY otp_new_used")
        ot = {r["otp_new_used"] or "Unknown": r["n"] for r in cur.fetchall()}
        cur.execute(f"SELECT coalesce(sum(amount),0) as t FROM prospect_otps o JOIN prospects p ON o.prospect_id=p.id {df.replace('dt_prospect_created','p.dt_prospect_created')}", params)
        amt = float(cur.fetchone()["t"])
        return {"total_prospects": tp, "total_otps": to, "total_amount": amt, "statuses": st, "otp_by_type": ot, "sold": st.get("Sold", 0), "pending": st.get("Pending", 0), "otp_status": st.get("OTP", 0), "finance": st.get("FinanceApplied", 0), "period": period}
    finally:
        c.close()

@app.get("/api/pipeline")
def pipeline(period: str = Query("all")):
    c = conn()
    try:
        df, params = date_filter(period)
        cur = c.cursor()
        cur.execute(f"SELECT prospect_status, count(*) as n FROM prospects p {df} GROUP BY prospect_status ORDER BY n DESC", params)
        return [{"status": r["prospect_status"] or "Unknown", "count": r["n"]} for r in cur.fetchall()]
    finally:
        c.close()

@app.get("/api/models")
def models(period: str = Query("all")):
    c = conn()
    try:
        cur = c.cursor()
        df, params = date_filter(period)
        df2 = df.replace('dt_prospect_created','p.dt_prospect_created') if df else ''
        where = df2 + " AND" if df2 else "WHERE"
        cur.execute(f"SELECT o.model, count(*) as n, coalesce(sum(o.amount),0) as t FROM prospect_otps o JOIN prospects p ON p.id = o.prospect_id {where} o.model IS NOT NULL AND o.model != '' GROUP BY o.model ORDER BY n DESC LIMIT 10", params)
        return [{"model": r["model"], "count": r["n"], "amount": float(r["t"])} for r in cur.fetchall()]
    finally:
        c.close()

@app.get("/api/sources")
def sources(period: str = Query("all")):
    c = conn()
    try:
        cur = c.cursor()
        df, params = date_filter(period)
        where = df + " AND" if df else "WHERE"
        cur.execute(f"SELECT source_of_initial_contact, count(*) as n FROM prospects {where} source_of_initial_contact IS NOT NULL AND source_of_initial_contact != '' GROUP BY source_of_initial_contact ORDER BY n DESC", params)
        return [{"source": r["source_of_initial_contact"], "count": r["n"]} for r in cur.fetchall()]
    finally:
        c.close()

@app.get("/api/sales")
def sales():
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("SELECT sales_person, count(*) as n, count(*) FILTER (WHERE prospect_status='Sold') as sold, count(*) FILTER (WHERE prospect_status='OTP') as otp FROM prospects WHERE sales_person IS NOT NULL AND sales_person != '' GROUP BY sales_person ORDER BY n DESC LIMIT 10")
        return [{"name": r["sales_person"], "total": r["n"], "sold": r["sold"], "otp": r["otp"]} for r in cur.fetchall()]
    finally:
        c.close()

@app.get("/api/trend")
def trend():
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("SELECT to_char(dt_prospect_created,'YYYY-MM') as m, count(*) as p, count(*) FILTER (WHERE prospect_status='Sold') as s FROM prospects WHERE dt_prospect_created IS NOT NULL GROUP BY m ORDER BY m")
        return [{"month": r["m"], "prospects": r["p"], "sold": r["s"]} for r in cur.fetchall()]
    finally:
        c.close()

@app.get("/api/changes")
def changes(limit: int = Query(20)):
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("SELECT table_name, business_key, field_name, old_value, new_value, changed_at, detected_by FROM change_log ORDER BY changed_at DESC LIMIT %(l)s", {"l": limit})
        return [{"table": r["table_name"], "key": r["business_key"], "field": r["field_name"], "old": str(r["old_value"] or ""), "new": str(r["new_value"] or ""), "when": str(r["changed_at"]), "source": r["detected_by"]} for r in cur.fetchall()]
    finally:
        c.close()

@app.get("/api/vehicle-orders")
def vehicle_orders(period: str = Query("all")):
    c = conn()
    try:
        cur = c.cursor()
        df, params = date_filter(period)
        dt_where = df.replace("dt_prospect_created", "p.dt_prospect_created") if df else ""
        dt_params = {k: v for k, v in params.items()} if params else {}
        
        rows = []
        offset = 0
        while True:
            cur.execute(f"""
                SELECT o.model, o.colour, o.otp_new_used, count(*) as n,
                       coalesce(sum(o.amount),0) as total_amt, round(avg(o.amount),0) as avg_amt,
                       count(*) FILTER (WHERE EXISTS (
                           SELECT 1 FROM prospects p2 WHERE p2.id=o.prospect_id AND p2.prospect_status='Sold'
                       )) as sold,
                       count(*) FILTER (WHERE o.dt_invoiced IS NOT NULL) as invoiced,
                       count(*) FILTER (WHERE o.stock_no IS NOT NULL AND o.stock_no != '') as in_stock
                FROM prospect_otps o
                JOIN prospects p ON o.prospect_id = p.id
                {dt_where}
                AND o.model IS NOT NULL AND o.model != ''
                GROUP BY o.model, o.colour, o.otp_new_used
                ORDER BY o.model, o.colour
                LIMIT 15 OFFSET %(off)s
            """, {**dt_params, "off": offset})
            chunk = cur.fetchall()
            if not chunk: break
            rows.extend(chunk)
            offset += len(chunk)

        # Group: model → group (CASE logic), model → colors with amounts
        groups = {}
        for r in rows:
            md = r["model"]; col = r["colour"] or "Unspecified"
            typ = r["otp_new_used"] or "Unknown"
            n = r["n"]; amt = float(r["total_amt"]); avg = float(r["avg_amt"]); sold = r["sold"]; inv = r["invoiced"]; istk = r["in_stock"]

            # Model group classification
            if md.startswith("T2"): grp = "T2"
            elif md.startswith("T1"): grp = "T1"
            elif md.upper().startswith("DASHING"): grp = "Dashing"
            elif md.upper().startswith("X70"): grp = "X70"
            else: grp = "Other"

            if grp not in groups:
                groups[grp] = {"group": grp, "total_otps": 0, "total_amount": 0, "sold": 0, "invoiced": 0, "in_stock": 0, "models": {}}
            groups[grp]["total_otps"] += n
            groups[grp]["total_amount"] += amt
            groups[grp]["sold"] += sold
            groups[grp]["invoiced"] += inv
            groups[grp]["in_stock"] += istk

            if md not in groups[grp]["models"]:
                groups[grp]["models"][md] = {"model": md, "total_otps": 0, "total_amount": 0, "sold": 0, "invoiced": 0, "in_stock": 0, "colors": []}
            groups[grp]["models"][md]["total_otps"] += n
            groups[grp]["models"][md]["total_amount"] += amt
            groups[grp]["models"][md]["sold"] += sold
            groups[grp]["models"][md]["invoiced"] += inv
            groups[grp]["models"][md]["in_stock"] += istk
            groups[grp]["models"][md]["colors"].append({
                "color": col, "type": typ, "count": n,
                "amount": amt, "avg_amount": avg, "sold": sold, "invoiced": inv, "in_stock": istk
            })

        result = []
        for g in sorted(groups.values(), key=lambda x: -x["total_otps"]):
            g["models"] = sorted(g["models"].values(), key=lambda m: -m["total_otps"])
            for m in g["models"]:
                m["colors"].sort(key=lambda c: -c["count"])
            result.append(g)
        return result
    finally:
        c.close()

@app.get("/api/health")
def health():
    c = conn()
    try:
        cur = c.cursor(); cur.execute("SELECT count(*) as n FROM prospects"); p = cur.fetchone()["n"]; cur.execute("SELECT count(*) as n FROM prospect_otps"); o = cur.fetchone()["n"]
        return {"status": "ok", "prospects": p, "otps": o}
    finally:
        c.close()

@app.get("/api/setup-tables")
def setup_tables():
    """Create prospect_appointments and prospect_leads tables for n8n."""
    c = conn()
    try:
        cur = c.cursor()
        # Use minimal statements to avoid MTU issues
        cur.execute("CREATE TABLE IF NOT EXISTS appointments (id BIGSERIAL PRIMARY KEY, prospect_id BIGINT, cms_prospect_id INTEGER, dt_scheduled TIMESTAMPTZ, appointment_type VARCHAR(100), appointment_status VARCHAR(50), confirmed BOOLEAN)")
        c.commit()
        cur.execute("CREATE TABLE IF NOT EXISTS leads (id BIGSERIAL PRIMARY KEY, lead_id INTEGER, prospect_id BIGINT, cms_prospect_id INTEGER, name VARCHAR(100), surname VARCHAR(100), cell_number VARCHAR(50), email_addr VARCHAR(200), prospect_status VARCHAR(50), lead_source VARCHAR(50))")
        c.commit()
        # Report tables
        cur.execute("CREATE TABLE IF NOT EXISTS report_daily_kpis (id BIGSERIAL PRIMARY KEY, report_date DATE NOT NULL UNIQUE, total_prospects INT DEFAULT 0, new_prospects INT DEFAULT 0, total_otps INT DEFAULT 0, new_otps INT DEFAULT 0, sold_count INT DEFAULT 0, pipeline_value NUMERIC(18,2) DEFAULT 0, avg_otp_value NUMERIC(18,2) DEFAULT 0, pending_count INT DEFAULT 0, finance_count INT DEFAULT 0, lost_count INT DEFAULT 0, conversion_rate NUMERIC(5,2) DEFAULT 0, generated_at TIMESTAMPTZ DEFAULT NOW())")
        c.commit()
        cur.execute("CREATE TABLE IF NOT EXISTS report_model_performance (id BIGSERIAL PRIMARY KEY, report_date DATE NOT NULL, model_group VARCHAR(100) NOT NULL, model VARCHAR(200) NOT NULL, color VARCHAR(100), otp_type VARCHAR(20), otp_count INT DEFAULT 0, total_amount NUMERIC(18,2) DEFAULT 0, avg_amount NUMERIC(18,2) DEFAULT 0, sold_count INT DEFAULT 0, generated_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE(report_date, model_group, model, color, otp_type))")
        c.commit()
        cur.execute("CREATE TABLE IF NOT EXISTS report_salesperson_performance (id BIGSERIAL PRIMARY KEY, report_date DATE NOT NULL, sales_person VARCHAR(150) NOT NULL, total_prospects INT DEFAULT 0, otp_count INT DEFAULT 0, sold_count INT DEFAULT 0, finance_count INT DEFAULT 0, lost_count INT DEFAULT 0, total_pipeline_value NUMERIC(18,2) DEFAULT 0, generated_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE(report_date, sales_person))")
        c.commit()
        return {"status": "ok", "tables": ["report_daily_kpis","report_model_performance","report_salesperson_performance"]}
    finally:
        c.close()

# ── Reports ─────────────────────────────────────────────────────

@app.get("/api/reports/kpis")
def report_kpis(days: int = Query(30, description="Days to include in report")):
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("""
            WITH daily AS (
                SELECT date_trunc('day', dt_prospect_created) as day,
                       count(*) as new_prospects,
                       count(*) FILTER (WHERE prospect_status='Sold') as new_sold
                FROM prospects WHERE dt_prospect_created >= NOW() - (%(d)s || ' days')::INTERVAL
                GROUP BY day
            ),
            otp_daily AS (
                SELECT date_trunc('day', o.dt_otp_created) as day, count(*) as new_otps,
                       coalesce(sum(o.amount),0) as pipeline
                FROM prospect_otps o WHERE o.dt_otp_created >= NOW() - (%(d)s || ' days')::INTERVAL
                GROUP BY day
            )
            SELECT to_char(d.day, 'YYYY-MM-DD') as date, d.new_prospects, d.new_sold,
                   coalesce(od.new_otps,0) as new_otps, coalesce(od.pipeline,0) as pipeline_value
            FROM daily d LEFT JOIN otp_daily od ON d.day = od.day
            ORDER BY d.day
        """, {"d": days})
        daily = [{"date": r["date"], "prospects": r["new_prospects"], "sold": r["new_sold"],
                  "otps": r["new_otps"], "pipeline": float(r["pipeline_value"])} for r in cur.fetchall()]

        # Totals for period
        cur.execute("""
            SELECT count(*) as total_p, count(*) FILTER (WHERE prospect_status='Sold') as sold_p,
                   count(*) FILTER (WHERE prospect_status='OTP') as otp_p,
                   count(*) FILTER (WHERE prospect_status='FinanceApplied') as fin_p,
                   count(*) FILTER (WHERE prospect_status='Pending') as pen_p
            FROM prospects WHERE dt_prospect_created >= NOW() - (%(d)s || ' days')::INTERVAL
        """, {"d": days})
        totals = cur.fetchone()

        cur.execute("""
            SELECT count(*) as total_o, coalesce(sum(amount),0) as total_amt, round(avg(amount),0) as avg_amt
            FROM prospect_otps WHERE dt_otp_created >= NOW() - (%(d)s || ' days')::INTERVAL
        """, {"d": days})
        otp_totals = cur.fetchone()

        return {
            "period_days": days,
            "daily": daily,
            "totals": {
                "prospects": totals["total_p"], "sold": totals["sold_p"],
                "otp_status": totals["otp_p"], "finance": totals["fin_p"], "pending": totals["pen_p"],
                "total_otps": otp_totals["total_o"], "pipeline_value": float(otp_totals["total_amt"]),
                "avg_otp": float(otp_totals["avg_amt"])
            },
            "conversion": {
                "lead_to_otp": round(totals["otp_p"] / max(totals["total_p"], 1) * 100, 1),
                "otp_to_finance": round(totals["fin_p"] / max(totals["otp_p"], 1) * 100, 1),
                "finance_to_sold": round(totals["sold_p"] / max(totals["fin_p"] + totals["otp_p"], 1) * 100, 1)
            }
        }
    finally:
        c.close()

@app.get("/api/reports/performance")
def report_performance():
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("""
            WITH mg AS (
                SELECT CASE
                    WHEN model_range IS NOT NULL AND model_range != '' THEN initcap(model_range)
                    WHEN model ~ '^T[12]' THEN regexp_replace(model, '^(T[12])\\s.*', '\\1')
                    WHEN model ~ '^(DASHING|Dashing)' THEN 'Dashing'
                    WHEN model ~ '^(X70)' THEN 'X70'
                    ELSE 'Other' END as model_group,
                    o.*, p.prospect_status
                FROM prospect_otps o JOIN prospects p ON o.prospect_id = p.id
                WHERE o.model IS NOT NULL AND o.model != ''
            )
            SELECT model_group, count(*) as total_otps,
                   coalesce(sum(amount),0) as total_value,
                   round(avg(amount),0) as avg_value,
                   count(*) FILTER (WHERE prospect_status='Sold') as sold,
                   count(*) FILTER (WHERE prospect_status='OTP') as otp_active,
                   count(*) FILTER (WHERE prospect_status='FinanceApplied') as finance
            FROM mg GROUP BY model_group ORDER BY total_otps DESC
        """)
        groups = []
        for r in cur.fetchall():
            groups.append({
                "group": r["model_group"], "total_otps": r["total_otps"],
                "total_value": float(r["total_value"]), "avg_value": float(r["avg_value"]),
                "sold": r["sold"], "otp_active": r["otp_active"], "finance": r["finance"],
                "conversion": round(r["sold"] / max(r["total_otps"], 1) * 100, 1)
            })
        return groups
    finally:
        c.close()

@app.get("/api/pipeline-velocity")
def pipeline_velocity(period: str = Query("all")):
    """Days between pipeline stages: OTP→Invoice, OTP→Delivery, Invoice→Delivery."""
    c = conn()
    try:
        cur = c.cursor()
        df, params = date_filter(period)
        df2 = df.replace('dt_prospect_created', 'p.dt_prospect_created') if df else ''
        df2 = df2.replace('WHERE ', '', 1) if df2.startswith('WHERE') else df2
        where = (df2 + ' AND ') if df2 else ''

        # OTP → Invoice
        cur.execute(f"""
            SELECT extract(epoch FROM (o.dt_invoiced - o.dt_otp_created))/86400 as days
            FROM prospect_otps o
            JOIN prospects p ON p.id = o.prospect_id
            WHERE {where}o.dt_invoiced IS NOT NULL AND o.dt_otp_created IS NOT NULL
              AND o.dt_invoiced > o.dt_otp_created
        """, params)
        otp_to_invoice = [round(r["days"], 1) for r in cur.fetchall()]

        # OTP → Delivery
        cur.execute(f"""
            SELECT extract(epoch FROM (a.dt_created - o.dt_otp_created))/86400 as days
            FROM prospect_otps o
            JOIN prospects p ON p.id = o.prospect_id
            JOIN appointments a ON a.prospect_id = o.prospect_id
            WHERE {where}a.appointment_type = 'Delivery'
              AND a.dt_created IS NOT NULL AND o.dt_otp_created IS NOT NULL
              AND a.dt_created > o.dt_otp_created
        """, params)
        otp_to_delivery = [round(r["days"], 1) for r in cur.fetchall()]

        # Invoice → Delivery
        cur.execute(f"""
            SELECT extract(epoch FROM (a.dt_created - o.dt_invoiced))/86400 as days
            FROM prospect_otps o
            JOIN prospects p ON p.id = o.prospect_id
            JOIN appointments a ON a.prospect_id = o.prospect_id
            WHERE {where}a.appointment_type = 'Delivery'
              AND o.dt_invoiced IS NOT NULL AND a.dt_created IS NOT NULL
              AND a.dt_created > o.dt_invoiced
        """, params)
        inv_to_delivery = [round(r["days"], 1) for r in cur.fetchall()]

        def stats(vals):
            if not vals: return {"count": 0, "avg": 0, "median": 0, "min": 0, "max": 0, "values": []}
            sv = sorted(vals)
            n = len(sv)
            med = sv[n//2] if n % 2 == 1 else (sv[n//2-1] + sv[n//2]) / 2
            return {"count": n, "avg": round(sum(sv)/n, 1), "median": round(med, 1),
                    "min": sv[0], "max": sv[-1], "values": sv}

        return {
            "stages": [
                {"label": "OTP → Invoice", "metric": "days_otp_to_invoice", **stats(otp_to_invoice)},
                {"label": "OTP → Delivery", "metric": "days_otp_to_delivery", **stats(otp_to_delivery)},
                {"label": "Invoice → Delivery", "metric": "days_invoice_to_delivery", **stats(inv_to_delivery)}
            ]
        }
    finally:
        c.close()

@app.get("/api/audit")
def db_audit():
    """Return DB data audit for dashboard gap analysis."""
    c = conn()
    try:
        cur = c.cursor()
        result = {}

        cur.execute("SELECT prospect_status, count(*) as n FROM prospects GROUP BY prospect_status ORDER BY n DESC")
        result["status_dist"] = [{"status": r["prospect_status"], "count": r["n"]} for r in cur.fetchall()]

        cur.execute("SELECT lost_reason, count(*) as n FROM prospects WHERE lost_reason IS NOT NULL AND lost_reason != '' GROUP BY lost_reason ORDER BY n DESC")
        result["lost_reasons"] = [{"reason": r["lost_reason"], "count": r["n"]} for r in cur.fetchall()]

        cur.execute("SELECT otp_new_used, count(*) as n FROM prospect_otps GROUP BY otp_new_used ORDER BY n DESC")
        result["new_used"] = [{"type": r["otp_new_used"], "count": r["n"]} for r in cur.fetchall()]

        cur.execute("SELECT appointment_type, count(*) as n FROM appointments GROUP BY appointment_type ORDER BY n DESC")
        result["appointments"] = [{"type": r["appointment_type"], "count": r["n"]} for r in cur.fetchall()]

        cur.execute("SELECT referral_source, count(*) as n FROM prospects WHERE referral_source IS NOT NULL AND referral_source != '' GROUP BY referral_source ORDER BY n DESC LIMIT 8")
        result["referrals"] = [{"source": r["referral_source"], "count": r["n"]} for r in cur.fetchall()]

        return result
    finally:
        c.close()

@app.get("/api/lost-reasons")
def lost_reasons():
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("SELECT lost_reason as reason, count(*) as n FROM prospects WHERE lost_reason IS NOT NULL AND lost_reason != '' GROUP BY lost_reason ORDER BY n DESC")
        return [{"reason": r["reason"], "count": r["n"]} for r in cur.fetchall()]
    finally:
        c.close()

@app.get("/api/new-used")
def new_used():
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("SELECT coalesce(otp_new_used,'Unknown') as type, count(*) as n, coalesce(sum(amount),0) as value FROM prospect_otps GROUP BY otp_new_used ORDER BY n DESC")
        return [{"type": r["type"], "count": r["n"], "value": float(r["value"])} for r in cur.fetchall()]
    finally:
        c.close()

# ── Recipient Management ──────────────────────────────────────────

@app.get("/api/recipients")
def list_recipients(user=Depends(require_admin)):
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS report_recipients (id SERIAL PRIMARY KEY, report_type VARCHAR(20) NOT NULL, email VARCHAR(200) NOT NULL, name VARCHAR(100), active BOOLEAN DEFAULT true, added_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE(report_type, email))")
        c.commit()
        cur.execute("SELECT id, report_type, email, name, active, added_at FROM report_recipients ORDER BY report_type, email")
        return [{"id": r["id"], "type": r["report_type"], "email": r["email"], "name": r["name"], "active": r["active"], "added": str(r["added_at"])} for r in cur.fetchall()]
    finally:
        c.close()

@app.post("/api/recipients")
async def add_recipient(data: dict, user=Depends(require_admin)):
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("INSERT INTO report_recipients (report_type, email, name) VALUES (%(t)s, %(e)s, %(n)s) ON CONFLICT (report_type, email) DO UPDATE SET active=true, name=COALESCE(%(n)s, report_recipients.name) RETURNING id",
            {"t": data["type"], "e": data["email"], "n": data.get("name")})
        c.commit()
        return {"id": cur.fetchone()["id"], "status": "added"}
    finally:
        c.close()

@app.delete("/api/recipients/{rid}")
def remove_recipient(rid: int, user=Depends(require_admin)):
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("DELETE FROM report_recipients WHERE id=%(i)s", {"i": rid})
        c.commit()
        return {"status": "removed"}
    finally:
        c.close()

@app.put("/api/recipients/{rid}")
async def toggle_recipient(rid: int, user=Depends(require_admin)):
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("UPDATE report_recipients SET active=NOT active WHERE id=%(i)s RETURNING active", {"i": rid})
        c.commit()
        r = cur.fetchone()
        return {"active": r["active"]} if r else {"error": "not found"}
    finally:
        c.close()

# ── SMTP Settings ─────────────────────────────────────────────────

@app.get("/api/settings/smtp")
def get_smtp_settings(user=Depends(require_admin)):
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS app_settings (key VARCHAR(100) PRIMARY KEY, value TEXT, updated_at TIMESTAMPTZ DEFAULT NOW())")
        c.commit()
        cur.execute("SELECT key, value FROM app_settings WHERE key LIKE 'smtp_%'")
        cfg = {r["key"]: r["value"] for r in cur.fetchall()}
        return {"smtp_host": cfg.get("smtp_host",""), "smtp_port": cfg.get("smtp_port","587"),
                "smtp_user": cfg.get("smtp_user",""), "smtp_pass": "", "smtp_from": cfg.get("smtp_from",""),
                "smtp_use_tls": cfg.get("smtp_use_tls","true")}
    finally:
        c.close()

@app.put("/api/settings/smtp")
async def save_smtp_settings(data: dict, user=Depends(require_admin)):
    c = conn()
    try:
        cur = c.cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS app_settings (key VARCHAR(100) PRIMARY KEY, value TEXT, updated_at TIMESTAMPTZ DEFAULT NOW())")
        c.commit()
        for key in ["smtp_host","smtp_port","smtp_user","smtp_from","smtp_use_tls"]:
            if key in data:
                cur.execute("INSERT INTO app_settings (key, value) VALUES (%(k)s, %(v)s) ON CONFLICT (key) DO UPDATE SET value=%(v)s, updated_at=NOW()",
                    {"k": key, "v": data[key]})
        if data.get("smtp_pass") and data["smtp_pass"].strip():
            cur.execute("INSERT INTO app_settings (key, value) VALUES ('smtp_pass', %(v)s) ON CONFLICT (key) DO UPDATE SET value=%(v)s, updated_at=NOW()",
                {"v": data["smtp_pass"]})
        c.commit()
        return {"status": "saved"}
    finally:
        c.close()

@app.post("/api/settings/smtp/test")
async def test_smtp(data: dict, user=Depends(require_admin)):
    """Send a test email to verify SMTP config."""
    import smtplib, ssl
    from email.mime.text import MIMEText
    from email.mime.multipart import MIMEMultipart

    test_email = data.get("email", "").strip()
    if not test_email or "@" not in test_email:
        return {"error": "Valid email address required"}

    c = conn()
    try:
        cur = c.cursor()
        cur.execute("SELECT key, value FROM app_settings WHERE key LIKE 'smtp_%'")
        cfg = {r["key"]: r["value"] for r in cur.fetchall()}
        if not cfg.get("smtp_host"):
            return {"error": "SMTP not configured — save settings first"}

        port = int(cfg.get("smtp_port", 587))
        use_tls = cfg.get("smtp_use_tls", "true") == "true"

        html = f"""<!DOCTYPE html><html><body style="font-family:sans-serif;padding:20px">
        <h2 style="color:#2563eb">✅ SMTP Test Successful</h2>
        <p>Your Jetour BI email configuration is working correctly.</p>
        <p style="color:#64748b;font-size:12px">Host: {cfg['smtp_host']}:{port} · TLS: {use_tls}</p>
        </body></html>"""

        msg = MIMEMultipart("alternative")
        msg["Subject"] = "Jetour BI — SMTP Test"
        msg["From"] = cfg["smtp_from"]
        msg["To"] = test_email
        msg.attach(MIMEText(html, "html"))

        if use_tls:
            ctx = ssl.create_default_context()
            with smtplib.SMTP(cfg["smtp_host"], port, timeout=15) as server:
                server.starttls(context=ctx)
                server.login(cfg["smtp_user"], cfg["smtp_pass"])
                server.sendmail(cfg["smtp_from"], [test_email], msg.as_string())
        else:
            with smtplib.SMTP(cfg["smtp_host"], port, timeout=15) as server:
                server.login(cfg["smtp_user"], cfg["smtp_pass"])
                server.sendmail(cfg["smtp_from"], [test_email], msg.as_string())

        return {"status": "sent", "to": test_email}
    except smtplib.SMTPAuthenticationError:
        return {"error": "Authentication failed — check username/password"}
    except smtplib.SMTPConnectError:
        return {"error": f"Could not connect to {cfg.get('smtp_host')}:{port} — check host/port"}
    except Exception as e:
        return {"error": str(e)}
    finally:
        if c: c.close()

# ── Report Dispatch ───────────────────────────────────────────────

def send_email_smtp(settings, to_list, subject, html_body):
    import smtplib, ssl
    from email.mime.text import MIMEText
    from email.mime.multipart import MIMEMultipart

    msg = MIMEMultipart("alternative")
    msg["Subject"] = subject
    msg["From"] = settings["smtp_from"]
    msg["To"] = to_list
    msg.attach(MIMEText(html_body, "html"))

    port = int(settings.get("smtp_port", 587))
    use_tls = settings.get("smtp_use_tls", "true") == "true"
    
    if use_tls:
        ctx = ssl.create_default_context()
        with smtplib.SMTP(settings["smtp_host"], port) as server:
            server.starttls(context=ctx)
            server.login(settings["smtp_user"], settings["smtp_pass"])
            server.sendmail(settings["smtp_from"], to_list.split(","), msg.as_string())
    else:
        with smtplib.SMTP(settings["smtp_host"], port) as server:
            server.login(settings["smtp_user"], settings["smtp_pass"])
            server.sendmail(settings["smtp_from"], to_list.split(","), msg.as_string())
    return True

def build_report_html(report_type, period_label, kpis, sales, models):
    def fm(v): return f'MUR {(float(v or 0)/1e6):.1f}M'
    sales_html = ''.join([f'<tr><td style="padding:4px 28px;font-size:12px;color:#cbd5e1">• <b>{s.get("sales_person","")}</b>: {s.get("total",0)} prospects, {s.get("sold",0)} sold</td></tr>' for s in sales[:3]]) if sales else ''
    models_html = ''.join([f'<tr><td style="padding:4px 28px;font-size:12px;color:#cbd5e1">• <b>{m.get("grp","")}</b>: {m.get("n",0)} OTPs, {fm(m.get("amt",0))}</td></tr>' for m in models[:3]]) if models else ''
    changes = int(kpis.get("changes", 0))
    
    return f'''<!DOCTYPE html>
<html><head><meta charset="utf-8"></head>
<body style="margin:0;padding:0;background:#0b1120;font-family:-apple-system,BlinkMacSystemFont,sans-serif">
<table width="100%" cellpadding="0" cellspacing="0" style="background:#0b1120;padding:24px"><tr><td align="center">
<table width="600" cellpadding="0" cellspacing="0" style="background:#1e293b;border-radius:10px;overflow:hidden">

<tr><td style="background:linear-gradient(135deg,#1a1a2e,#16213e,#0f3460);padding:24px 28px">
  <div style="font-size:11px;color:#64748b;text-transform:uppercase;letter-spacing:2px">{report_type} Report</div>
  <h1 style="margin:4px 0 0;font-size:20px;color:#f1f5f9;font-weight:700">Jetour Mauritius BI</h1>
  <div style="margin-top:6px;font-size:13px;color:#94a3b8">{period_label}</div>
</td></tr>

<tr><td style="padding:20px 28px">
<table width="100%" cellpadding="0" cellspacing="0"><tr>
  <td width="25%" style="padding:0 8px 0 0"><table width="100%" cellpadding="0" cellspacing="0" style="background:#0f172a;border-radius:8px;padding:14px"><tr><td style="font-size:10px;color:#64748b;text-transform:uppercase;letter-spacing:1px">New Prospects</td></tr><tr><td style="font-size:22px;color:#f1f5f9;font-weight:700;padding-top:4px">{kpis.get("new_prospects",0)}</td></tr></table></td>
  <td width="25%" style="padding:0 4px"><table width="100%" cellpadding="0" cellspacing="0" style="background:#0f172a;border-radius:8px;padding:14px"><tr><td style="font-size:10px;color:#64748b;text-transform:uppercase;letter-spacing:1px">New OTPs</td></tr><tr><td style="font-size:22px;color:#60a5fa;font-weight:700;padding-top:4px">{kpis.get("new_otps",0)}</td></tr></table></td>
  <td width="25%" style="padding:0 4px"><table width="100%" cellpadding="0" cellspacing="0" style="background:#0f172a;border-radius:8px;padding:14px"><tr><td style="font-size:10px;color:#64748b;text-transform:uppercase;letter-spacing:1px">Sold</td></tr><tr><td style="font-size:22px;color:#34d399;font-weight:700;padding-top:4px">{kpis.get("new_sold",0)}</td></tr></table></td>
  <td width="25%" style="padding:0 0 0 8px"><table width="100%" cellpadding="0" cellspacing="0" style="background:#0f172a;border-radius:8px;padding:14px"><tr><td style="font-size:10px;color:#64748b;text-transform:uppercase;letter-spacing:1px">Pipeline</td></tr><tr><td style="font-size:18px;color:#fbbf24;font-weight:700;padding-top:4px">{fm(kpis.get("pipeline",0))}</td></tr></table></td>
</tr></table>
</td></tr>

<tr><td style="padding:4px 28px 16px;font-size:11px;color:#94a3b8">
  All-time: <b style="color:#e2e8f0">{kpis.get("total_prospects",0)}</b> prospects · <b style="color:#e2e8f0">{kpis.get("total_otps",0)}</b> OTPs · <b style="color:#e2e8f0">{kpis.get("total_sold",0)}</b> sold · <b style="color:#e2e8f0">{fm(kpis.get("total_pipeline",0))}</b>
</td></tr>
<tr><td style="padding:4px 28px 16px;font-size:12px;color:#fbbf24">⚡ {changes} field changes detected</td></tr>
{sales_html}
{models_html}
<tr><td style="padding:20px 28px;border-top:1px solid #334155">
<table width="100%" cellpadding="0" cellspacing="0"><tr>
  <td style="font-size:11px;color:#475569">Jetour BI · Auto-generated</td>
  <td align="right"><a href="http://100.91.25.139:8765" style="color:#60a5fa;font-size:11px;text-decoration:none">📊 Full Dashboard →</a></td>
</tr></table>
</td></tr>
</table></td></tr></table></body></html>'''

@app.post("/api/send-report/{report_type}")
def send_report(report_type: str):
    """Trigger email report: daily, weekly, or monthly. Called by n8n."""
    import smtplib, ssl
    from email.mime.text import MIMEText
    from email.mime.multipart import MIMEMultipart

    c = conn()
    try:
        cur = c.cursor()
        
        # Get SMTP settings
        cur.execute("SELECT key, value FROM app_settings WHERE key LIKE 'smtp_%'")
        cfg = {r["key"]: r["value"] for r in cur.fetchall()}
        if not cfg.get("smtp_host"):
            return {"error": "SMTP not configured", "url": "/settings.html"}

        # Get active recipients
        cur.execute("SELECT string_agg(email, ',') as to_list FROM report_recipients WHERE report_type=%s AND active=true", (report_type,))
        row = cur.fetchone()
        if not row or not row["to_list"]:
            return {"error": f"No active recipients for {report_type}"}
        to_list = row["to_list"]

        # Build SQL based on report type
        if report_type == "daily":
            period_clause = "CURRENT_DATE - 1"
            period_label_query = "SELECT to_char(CURRENT_DATE - 1, 'DD Mon YYYY') as label"
        elif report_type == "weekly":
            period_clause = "CURRENT_DATE - 7"
            period_label_query = "SELECT to_char(CURRENT_DATE - 7, 'DD Mon') || ' – ' || to_char(CURRENT_DATE - 1, 'DD Mon YYYY') as label"
        else:  # monthly
            period_label_query = "SELECT to_char(date_trunc('month', NOW()) - INTERVAL '1 month', 'Month YYYY') as label"

        # Run period-specific queries
        if report_type == "monthly":
            cur.execute("""
                SELECT (SELECT count(*) FROM prospects WHERE dt_prospect_created >= date_trunc('month', NOW()) - INTERVAL '1 month' AND dt_prospect_created < date_trunc('month', NOW())) as new_prospects,
                       (SELECT count(*) FROM prospects WHERE prospect_status='Sold' AND dt_prospect_created >= date_trunc('month', NOW()) - INTERVAL '1 month' AND dt_prospect_created < date_trunc('month', NOW())) as new_sold,
                       (SELECT count(*) FROM prospect_otps WHERE dt_otp_created >= date_trunc('month', NOW()) - INTERVAL '1 month' AND dt_otp_created < date_trunc('month', NOW())) as new_otps,
                       (SELECT coalesce(sum(amount),0) FROM prospect_otps WHERE dt_otp_created >= date_trunc('month', NOW()) - INTERVAL '1 month' AND dt_otp_created < date_trunc('month', NOW())) as pipeline,
                       (SELECT count(*) FROM prospects) as total_prospects, (SELECT count(*) FROM prospect_otps) as total_otps,
                       (SELECT count(*) FROM prospects WHERE prospect_status='Sold') as total_sold, (SELECT coalesce(sum(amount),0) FROM prospect_otps) as total_pipeline,
                       (SELECT count(*) FROM change_log WHERE changed_at >= date_trunc('month', NOW()) - INTERVAL '1 month' AND changed_at < date_trunc('month', NOW())) as changes
            """)
            kpis = dict(cur.fetchone())
            cur.execute("SELECT sales_person, count(*) as total, count(*) FILTER (WHERE prospect_status='Sold') as sold FROM prospects WHERE sales_person IS NOT NULL AND sales_person != '' AND dt_prospect_created >= date_trunc('month', NOW()) - INTERVAL '1 month' AND dt_prospect_created < date_trunc('month', NOW()) GROUP BY sales_person ORDER BY sold DESC LIMIT 5")
            sales = [dict(r) for r in cur.fetchall()]
            cur.execute("SELECT coalesce(nullif(model_range,''),model) as grp, count(*) as n, coalesce(sum(amount),0) as amt FROM prospect_otps WHERE dt_otp_created >= date_trunc('month', NOW()) - INTERVAL '1 month' AND dt_otp_created < date_trunc('month', NOW()) AND model IS NOT NULL AND model != '' GROUP BY coalesce(nullif(model_range,''),model) ORDER BY n DESC LIMIT 5")
            models = [dict(r) for r in cur.fetchall()]
        else:
            cur.execute(f"""SELECT (SELECT count(*) FROM prospects WHERE dt_prospect_created::date = {period_clause}) as new_prospects,
                (SELECT count(*) FROM prospects WHERE prospect_status='Sold' AND dt_prospect_created::date = {period_clause}) as new_sold,
                (SELECT count(*) FROM prospect_otps WHERE dt_otp_created::date = {period_clause}) as new_otps,
                (SELECT coalesce(sum(amount),0) FROM prospect_otps WHERE dt_otp_created::date = {period_clause}) as pipeline,
                (SELECT count(*) FROM prospects) as total_prospects, (SELECT count(*) FROM prospect_otps) as total_otps,
                (SELECT count(*) FROM prospects WHERE prospect_status='Sold') as total_sold, (SELECT coalesce(sum(amount),0) FROM prospect_otps) as total_pipeline,
                (SELECT count(*) FROM change_log WHERE changed_at::date = {period_clause}) as changes""")
            kpis = dict(cur.fetchone())
            cur.execute(f"SELECT sales_person, count(*) as total, count(*) FILTER (WHERE prospect_status='Sold') as sold FROM prospects WHERE sales_person IS NOT NULL AND sales_person != '' AND dt_prospect_created::date = {period_clause} GROUP BY sales_person ORDER BY sold DESC LIMIT 5")
            sales = [dict(r) for r in cur.fetchall()]
            cur.execute(f"SELECT coalesce(nullif(model_range,''),model) as grp, count(*) as n, coalesce(sum(amount),0) as amt FROM prospect_otps WHERE dt_otp_created::date = {period_clause} AND model IS NOT NULL AND model != '' GROUP BY coalesce(nullif(model_range,''),model) ORDER BY n DESC LIMIT 5")
            models = [dict(r) for r in cur.fetchall()]

        # Get period label
        cur.execute(period_label_query)
        period_label = cur.fetchone()["label"].strip()

        # Build and send email
        html = build_report_html(report_type.title(), period_label, kpis, sales, models)
        
        # Send via SMTP
        port = int(cfg.get("smtp_port", 587))
        use_tls = cfg.get("smtp_use_tls", "true") == "true"
        msg = MIMEMultipart("alternative")
        msg["Subject"] = f"Jetour BI {report_type.title()} · {period_label}"
        msg["From"] = cfg["smtp_from"]
        msg["To"] = to_list
        msg.attach(MIMEText(html, "html"))

        if use_tls:
            ctx = ssl.create_default_context()
            with smtplib.SMTP(cfg["smtp_host"], port, timeout=30) as server:
                server.starttls(context=ctx)
                server.login(cfg["smtp_user"], cfg["smtp_pass"])
                server.sendmail(cfg["smtp_from"], [e.strip() for e in to_list.split(",")], msg.as_string())
        else:
            with smtplib.SMTP(cfg["smtp_host"], port, timeout=30) as server:
                server.login(cfg["smtp_user"], cfg["smtp_pass"])
                server.sendmail(cfg["smtp_from"], [e.strip() for e in to_list.split(",")], msg.as_string())

        return {"status": "sent", "to": to_list, "period": period_label}
    except Exception as e:
        return {"error": str(e)}
    finally:
        c.close()



@app.get("/reset-password")
async def reset_password_page():
    from fastapi.responses import HTMLResponse
    import os
    path = os.path.join(os.path.dirname(__file__), "static", "reset-password.html")
    with open(path) as f:
        return HTMLResponse(f.read())

app.mount("/", StaticFiles(directory="static", html=True), name="static")