from fastapi import FastAPI, APIRouter, HTTPException, Depends, WebSocket, WebSocketDisconnect, Request, UploadFile, File, Header
from fastapi.responses import JSONResponse
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
from dotenv import load_dotenv
from starlette.middleware.cors import CORSMiddleware
import aiomysql
import os
import logging
import asyncio
import time
import json
import re
import base64
from pathlib import Path
from pydantic import BaseModel
from typing import List, Optional, Literal, Dict, Any, Tuple
from datetime import datetime, timezone, timedelta
import bcrypt
import jwt as pyjwt

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

# ----- Config -----
MYSQL_HOST = os.environ.get('MYSQL_HOST', '127.0.0.1')
MYSQL_PORT = int(os.environ.get('MYSQL_PORT', '3306'))
MYSQL_USER = os.environ.get('MYSQL_USER', 'root')
MYSQL_PASSWORD = os.environ.get('MYSQL_PASSWORD', '')
MYSQL_DB = os.environ.get('MYSQL_DB', 'cariste_api')

JWT_SECRET = os.environ.get('JWT_SECRET', 'change-me')
JWT_ALG = 'HS256'
# 1 year — "reste connecté jusqu'à déconnexion manuelle"
JWT_EXP_HOURS = 24 * 365

# Shared secret with the partner website (GP v3) for SSO. Defaults to JWT_SECRET
# but should be configured to a DIFFERENT random string in production for isolation.
SSO_SECRET = os.environ.get('SSO_SECRET') or JWT_SECRET
SSO_MAX_AGE_SECONDS = 90  # short-lived: PHP signs, user clicks within 90 seconds

# Roles allowed to use My Truck (admin is reserved for the partner website)
MYTRUCK_ROLES = {'cariste', 'bureau'}

PASSWORD_RE = re.compile(r'^\d{6}$')

app = FastAPI()
api_router = APIRouter(prefix="/api")
security = HTTPBearer()

# ----- DB pool -----
_pool: Optional[aiomysql.Pool] = None

async def get_pool() -> aiomysql.Pool:
    global _pool
    if _pool is None:
        _pool = await aiomysql.create_pool(
            host=MYSQL_HOST,
            port=MYSQL_PORT,
            user=MYSQL_USER,
            password=MYSQL_PASSWORD,
            db=MYSQL_DB,
            charset='utf8mb4',
            autocommit=True,
            minsize=1,
            maxsize=10,
        )
    return _pool

async def fetch_one(sql: str, params: tuple = ()) -> Optional[Dict[str, Any]]:
    pool = await get_pool()
    async with pool.acquire() as conn:
        async with conn.cursor(aiomysql.DictCursor) as cur:
            await cur.execute(sql, params)
            return await cur.fetchone()

async def fetch_all(sql: str, params: tuple = ()) -> List[Dict[str, Any]]:
    pool = await get_pool()
    async with pool.acquire() as conn:
        async with conn.cursor(aiomysql.DictCursor) as cur:
            await cur.execute(sql, params)
            return list(await cur.fetchall())

async def execute(sql: str, params: tuple = ()) -> int:
    pool = await get_pool()
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(sql, params)
            return cur.rowcount

async def execute_returning_id(sql: str, params: tuple = ()) -> int:
    pool = await get_pool()
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(sql, params)
            return cur.lastrowid

async def execute_many(sql: str, seq: List[tuple]) -> int:
    if not seq:
        return 0
    pool = await get_pool()
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.executemany(sql, seq)
            return cur.rowcount

# ----- Utils -----
def now_dt() -> datetime:
    return datetime.now(timezone.utc).replace(tzinfo=None)

def dt_to_iso(dt: Optional[datetime]) -> Optional[str]:
    if dt is None:
        return None
    if dt.tzinfo is None:
        return dt.replace(tzinfo=timezone.utc).isoformat()
    return dt.isoformat()

def hash_password(pwd: str) -> str:
    return bcrypt.hashpw(pwd.encode(), bcrypt.gensalt()).decode()

def verify_password(pwd: str, hashed: str) -> bool:
    if not hashed:
        return False
    try:
        # Accept PHP-style $2y$ hashes by converting to $2b$ which python-bcrypt accepts
        if hashed.startswith('$2y$'):
            hashed = '$2b$' + hashed[4:]
        return bcrypt.checkpw(pwd.encode(), hashed.encode())
    except Exception:
        return False

def create_token(user_id: int) -> str:
    return pyjwt.encode(
        {'sub': str(user_id), 'exp': datetime.now(timezone.utc) + timedelta(hours=JWT_EXP_HOURS)},
        JWT_SECRET, algorithm=JWT_ALG,
    )

def normalize_username(s: str) -> str:
    return (s or "").strip().lower()

def effective_role(u: Dict[str, Any]) -> Optional[str]:
    """Compute the role used inside My Truck.

    - If `mytruck_role` is set to cariste/bureau, use it (overrides gp_role).
    - Else, if `role` (gp_role) is cariste/bureau, use it (legacy / pure My Truck users).
    - Else, return None (no access).
    """
    mt = u.get('mytruck_role')
    if mt in MYTRUCK_ROLES:
        return mt
    role = u.get('role')
    if role in MYTRUCK_ROLES:
        return role
    return None

def user_public(u: Dict[str, Any]) -> Dict[str, Any]:
    eff = effective_role(u)
    return {
        "id": u['id'],
        "username": u['username'],
        "fullname": u.get('fullname') or '',
        "role": eff,                       # effective role used by My Truck (cariste|bureau|None)
        "gp_role": u.get('role') or '',    # raw role from the shared `users` table
        "mytruck_role": u.get('mytruck_role'),
        "must_change_password": bool(u.get('must_change_password', 0)),
        "is_active": bool(u.get('is_active', 1)) if u.get('is_active') is not None else True,
        "created_at": dt_to_iso(u.get('created_at')) if u.get('created_at') else None,
        "notif_mode": u.get('notif_mode') or 'always',
        "notif_start": u.get('notif_start') or None,
        "notif_end": u.get('notif_end') or None,
        "email": u.get('email') or '',
        "email_xdock": u.get('email_xdock') or '',
        "service_shift": u.get('service_shift') or '',
        "xdock_name": u.get('xdock_name') or '',
        "can_manage_xdock_scripts": bool(int(u.get('can_manage_xdock_scripts') or 0)),
        "can_manage_folder_email": bool(int(u.get('can_manage_folder_email') or 0)),
    }

async def get_current_user(credentials: HTTPAuthorizationCredentials = Depends(security)):
    try:
        payload = pyjwt.decode(credentials.credentials, JWT_SECRET, algorithms=[JWT_ALG])
        user_id = int(payload.get('sub'))
    except Exception:
        raise HTTPException(status_code=401, detail="Invalid token")
    user = await fetch_one("SELECT * FROM users WHERE id=%s", (user_id,))
    if not user:
        raise HTTPException(status_code=401, detail="User not found")
    if effective_role(user) not in MYTRUCK_ROLES:
        raise HTTPException(status_code=403, detail="Accès refusé à My Truck")
    return user_public(user)

async def require_bureau(user=Depends(get_current_user)):
    if user.get('role') != 'bureau':
        raise HTTPException(status_code=403, detail="Réservé au bureau")
    return user

# ----- Notification quiet-hours helpers -----
_TIME_RE = re.compile(r'^([01]\d|2[0-3]):[0-5]\d$')

def _parse_hhmm(s: Optional[str]) -> Optional[int]:
    """Parse 'HH:MM' to minutes since midnight, or None."""
    if not s or not isinstance(s, str):
        return None
    if not _TIME_RE.match(s):
        return None
    h, m = s.split(':')
    return int(h) * 60 + int(m)

def _should_push_now(user_row: Dict[str, Any], now: Optional[datetime] = None) -> bool:
    """
    Decide whether to deliver a real-time / push notification to this user
    based on their notif_mode/notif_start/notif_end preferences.
    History (DB row) is always written; only real-time delivery is gated.
    """
    mode = (user_row.get('notif_mode') or 'always').lower()
    if mode == 'always':
        return True
    if mode == 'never':
        return False
    # mode == 'range'
    start = _parse_hhmm(user_row.get('notif_start'))
    end = _parse_hhmm(user_row.get('notif_end'))
    if start is None or end is None:
        return True  # incomplete configuration → fail-open
    now_local = (now or datetime.now()).time()
    cur = now_local.hour * 60 + now_local.minute
    if start == end:
        return False  # empty window
    if start < end:
        return start <= cur < end
    # window crosses midnight (e.g. 22:00 → 06:00)
    return cur >= start or cur < end

# ----- Models -----
class LoginIn(BaseModel):
    username: str
    password: str
    legacy: bool = False

class ChangePasswordIn(BaseModel):
    current_password: str
    new_password: str

class SSOIn(BaseModel):
    token: str

class UserCreate(BaseModel):
    username: str
    fullname: Optional[str] = ""
    password: str
    role: Literal['bureau', 'cariste']
    email: Optional[str] = ""
    email_xdock: Optional[str] = ""
    service_shift: Optional[str] = ""  # '', 'MATIN', 'APRES_MIDI', 'NUIT' (ou 'JOURNEE' pour compat ancien)
    xdock_name: Optional[str] = ""

class UserUpdate(BaseModel):
    username: Optional[str] = None
    fullname: Optional[str] = None
    password: Optional[str] = None
    role: Optional[Literal['bureau', 'cariste']] = None
    mytruck_role: Optional[Literal['bureau', 'cariste']] = None  # None + present ⇒ revoke
    is_active: Optional[bool] = None
    must_change_password: Optional[bool] = None
    email: Optional[str] = None
    email_xdock: Optional[str] = None
    service_shift: Optional[str] = None
    xdock_name: Optional[str] = None
    can_manage_xdock_scripts: Optional[bool] = None
    can_manage_folder_email: Optional[bool] = None

class FolderCreate(BaseModel):
    plate: str
    problem: str
    photos: List[str] = []
    numero_em_sm: Optional[str] = ""
    type_em_sm: Optional[str] = ""
    immatriculation_remorque: Optional[str] = ""

class FolderUpdate(BaseModel):
    problem: Optional[str] = None
    photos: Optional[List[str]] = None
    numero_em_sm: Optional[str] = None
    type_em_sm: Optional[str] = None
    immatriculation_remorque: Optional[str] = None

class FolderAppend(BaseModel):
    text: Optional[str] = None
    photos: Optional[List[str]] = None

class StatusUpdate(BaseModel):
    action: Literal['take', 'close', 'reopen']

class PhraseCreate(BaseModel):
    text: str

class ManualNotifIn(BaseModel):
    user_ids: List[int]
    title: str
    body: str

class DeviceTokenIn(BaseModel):
    token: str
    platform: Literal['web', 'android', 'ios'] = 'web'

class NotifSettingsIn(BaseModel):
    mode: Literal['always', 'range', 'never']
    start: Optional[str] = None  # "HH:MM"
    end: Optional[str] = None    # "HH:MM"

class LegacyLoginToggleIn(BaseModel):
    enabled: bool

# ----- Super-admin (single hardcoded username) -----
SUPER_ADMIN_USERNAME = 'mathieu.u'

async def require_super_admin(user=Depends(get_current_user)):
    if (user.get('username') or '').lower() != SUPER_ADMIN_USERNAME:
        raise HTTPException(status_code=403, detail="Réservé au super-admin")
    return user

async def require_can_manage_xdock_scripts(user=Depends(get_current_user)):
    """Le super-admin ET tout user qui a le droit `can_manage_xdock_scripts=1`."""
    if (user.get('username') or '').lower() == SUPER_ADMIN_USERNAME:
        return user
    row = await fetch_one(
        "SELECT can_manage_xdock_scripts FROM users WHERE id=%s",
        (user['id'],),
    )
    if not row or not int(row.get('can_manage_xdock_scripts') or 0):
        raise HTTPException(status_code=403, detail="Droit `Gestion scripts XDock+` requis")
    return user


async def require_can_manage_folder_email(user=Depends(get_current_user)):
    """Le super-admin ET tout user qui a le droit `can_manage_folder_email=1`."""
    if (user.get('username') or '').lower() == SUPER_ADMIN_USERNAME:
        return user
    row = await fetch_one(
        "SELECT can_manage_folder_email FROM users WHERE id=%s",
        (user['id'],),
    )
    if not row or not int(row.get('can_manage_folder_email') or 0):
        raise HTTPException(status_code=403, detail="Droit `Gestion email Dossiers` requis")
    return user

# ----- App-wide settings helpers -----
async def get_setting(key: str, default: str = '') -> str:
    row = await fetch_one("SELECT svalue FROM app_settings WHERE skey=%s", (key,))
    return row['svalue'] if row else default

async def set_setting(key: str, value: str) -> None:
    await execute(
        "INSERT INTO app_settings (skey, svalue) VALUES (%s, %s) "
        "ON DUPLICATE KEY UPDATE svalue=VALUES(svalue)",
        (key, value),
    )

# ----- WebSocket manager -----
class WSManager:
    def __init__(self):
        self.bureau: List[WebSocket] = []
        self.users: Dict[int, List[WebSocket]] = {}

    async def connect_bureau(self, ws: WebSocket):
        self.bureau.append(ws)

    async def connect_user(self, ws: WebSocket, user_id: int):
        self.users.setdefault(user_id, []).append(ws)

    def disconnect(self, ws: WebSocket, user_id: Optional[int] = None):
        if ws in self.bureau:
            self.bureau.remove(ws)
        if user_id is not None and user_id in self.users:
            try:
                self.users[user_id].remove(ws)
                if not self.users[user_id]:
                    del self.users[user_id]
            except ValueError:
                pass

    async def broadcast_bureau(self, message: dict):
        dead = []
        for ws in self.bureau:
            try:
                await ws.send_text(json.dumps(message))
            except Exception:
                dead.append(ws)
        for d in dead:
            if d in self.bureau:
                self.bureau.remove(d)

    async def send_to_user(self, user_id: int, message: dict):
        for ws in list(self.users.get(user_id, [])):
            try:
                await ws.send_text(json.dumps(message))
            except Exception:
                self.disconnect(ws, user_id)

manager = WSManager()

# ----- Login rate limiting (in-memory, per username + per IP) -----
# Goal: slow down brute-force attempts on /api/auth/login without a Redis
# dependency. After MAX_FAILS within WINDOW, the next attempt is rejected for
# COOLDOWN seconds. Successful logins reset the counter.
_LOGIN_FAILS: Dict[str, List[float]] = {}
_LOGIN_LOCKS: Dict[str, float] = {}
_LOGIN_WINDOW_S = 60.0
_LOGIN_MAX_FAILS = 5
_LOGIN_COOLDOWN_S = 60.0

def _login_keys(username: str, ip: Optional[str]) -> List[str]:
    keys = [f"u:{(username or '').strip().lower()}"]
    if ip:
        keys.append(f"i:{ip}")
    return keys

def login_rate_check(username: str, ip: Optional[str]) -> Optional[int]:
    """Return seconds-to-wait if locked, else None."""
    now = time.time()
    for k in _login_keys(username, ip):
        locked_until = _LOGIN_LOCKS.get(k)
        if locked_until and now < locked_until:
            return int(locked_until - now) + 1
    return None

def login_record_fail(username: str, ip: Optional[str]) -> None:
    now = time.time()
    for k in _login_keys(username, ip):
        bucket = [t for t in _LOGIN_FAILS.get(k, []) if now - t < _LOGIN_WINDOW_S]
        bucket.append(now)
        _LOGIN_FAILS[k] = bucket
        if len(bucket) >= _LOGIN_MAX_FAILS:
            _LOGIN_LOCKS[k] = now + _LOGIN_COOLDOWN_S
            _LOGIN_FAILS[k] = []

def login_record_success(username: str, ip: Optional[str]) -> None:
    for k in _login_keys(username, ip):
        _LOGIN_FAILS.pop(k, None)
        _LOGIN_LOCKS.pop(k, None)

# ----- Helpers folder -----
async def load_folder_full(folder_id: int) -> Optional[Dict[str, Any]]:
    f = await fetch_one("SELECT * FROM folders WHERE id=%s", (folder_id,))
    if not f:
        return None
    photos = await fetch_all(
        "SELECT data, event_id FROM folder_photos WHERE folder_id=%s ORDER BY position ASC",
        (folder_id,),
    )
    history = await fetch_all(
        "SELECT id, action, by_id, by_name, at, label FROM folder_history WHERE folder_id=%s ORDER BY at ASC",
        (folder_id,),
    )

    # Build "contributions" — one card per event (creation + each append), with its own text/photos.
    # This is what the frontend uses to render clearly separated rows.
    SEP = "\n---TEXT---\n"
    # Group photos by event_id (or None for legacy photos without event)
    photos_by_event: Dict[Any, List[str]] = {}
    for p in photos:
        eid = p.get('event_id')
        photos_by_event.setdefault(eid, []).append(p['data'])

    contributions: List[Dict[str, Any]] = []
    creation_event_id: Optional[int] = None
    for h in history:
        if h['action'] == 'created':
            creation_event_id = h['id']
            contributions.append({
                "id": h['id'],
                "kind": "created",
                "by_name": h.get('by_name') or '',
                "at": dt_to_iso(h['at']),
                "text": f.get('problem') or '',
                "photos": photos_by_event.get(h['id'], []),
            })
        elif h['action'] == 'appended':
            raw = h.get('label') or ''
            summary, _, text_part = raw.partition(SEP)
            contributions.append({
                "id": h['id'],
                "kind": "appended",
                "by_name": h.get('by_name') or '',
                "at": dt_to_iso(h['at']),
                "summary": summary.strip(),
                "text": text_part.strip(),
                "photos": photos_by_event.get(h['id'], []),
            })

    # Legacy photos with NULL event_id (uploaded before this migration) → attach to creation card
    legacy = photos_by_event.get(None, [])
    if legacy and contributions and creation_event_id is not None:
        contributions[0]['photos'] = legacy + contributions[0].get('photos', [])

    return {
        "id": f['id'],
        "plate": f['plate'],
        "problem": f['problem'],
        "photos": [p['data'] for p in photos],
        "status": f['status'],
        "cariste_id": f['cariste_id'],
        "cariste_name": f['cariste_name'] or '',
        "assigned_to_id": f.get('assigned_to_id'),
        "assigned_to_name": f.get('assigned_to_name'),
        "created_at": dt_to_iso(f['created_at']),
        "updated_at": dt_to_iso(f['updated_at']),
        "date_key": f['date_key'],
        "numero_em_sm": f.get('numero_em_sm') or '',
        "type_em_sm": f.get('type_em_sm') or '',
        "immatriculation_remorque": f.get('immatriculation_remorque') or '',
        "contributions": contributions,
        "history": [
            {
                "id": h['id'],
                "action": h['action'],
                "by_id": h.get('by_id'),
                "by_name": h.get('by_name'),
                "at": dt_to_iso(h['at']),
                # Strip the embedded text from history.label so the timeline stays one-line readable
                "label": ((h['label'] or '').split(SEP)[0]).strip(),
            } for h in history
        ],
    }

async def insert_photos(folder_id: int, photos: List[str], start_pos: int = 0, event_id: Optional[int] = None):
    if not photos:
        return
    rows = [(folder_id, start_pos + i, p, event_id) for i, p in enumerate(photos)]
    await execute_many(
        "INSERT INTO folder_photos (folder_id, position, data, event_id) VALUES (%s, %s, %s, %s)",
        rows,
    )

# ----- Routes -----
@api_router.get("/")
async def root():
    return {"status": "ok", "app": "My Truck (MariaDB)"}

@api_router.post("/auth/login")
async def login(data: LoginIn, request: Request):
    ip = (request.client.host if request.client else None) or request.headers.get('x-forwarded-for', '').split(',')[0].strip() or None

    wait = login_rate_check(data.username, ip)
    if wait:
        raise HTTPException(status_code=429, detail=f"Trop de tentatives. Réessaie dans {wait}s.")

    user = await fetch_one(
        "SELECT * FROM users WHERE LOWER(username)=%s",
        (normalize_username(data.username),),
    )
    if not user or not verify_password(data.password, user.get('password_hash') or ''):
        login_record_fail(data.username, ip)
        raise HTTPException(status_code=401, detail="Identifiants invalides")
    if effective_role(user) not in MYTRUCK_ROLES:
        login_record_fail(data.username, ip)
        raise HTTPException(status_code=403, detail="Ce compte n'a pas accès à My Truck")
    if user.get('is_active') == 0:
        login_record_fail(data.username, ip)
        raise HTTPException(status_code=403, detail="Compte désactivé")

    # Legacy login: gated by a global app setting. The very first time a user
    # opts in, force them to set a brand-new 6-digit code.
    if data.legacy:
        if await get_setting('legacy_login_enabled', '0') != '1':
            raise HTTPException(status_code=403, detail="Connexion ancien mot de passe désactivée")
        if not user.get('must_change_password'):
            await execute(
                "UPDATE users SET must_change_password=1 WHERE id=%s",
                (user['id'],),
            )
            user['must_change_password'] = 1

    login_record_success(data.username, ip)
    return {
        "token": create_token(user['id']),
        "user": user_public(user),
    }

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

@api_router.post("/auth/change-password")
async def change_password(data: ChangePasswordIn, user=Depends(get_current_user)):
    db_user = await fetch_one("SELECT * FROM users WHERE id=%s", (user['id'],))
    if not db_user:
        raise HTTPException(status_code=401, detail="User not found")
    if not verify_password(data.current_password, db_user.get('password_hash') or ''):
        raise HTTPException(status_code=400, detail="Mot de passe actuel incorrect")
    if not PASSWORD_RE.match(data.new_password):
        raise HTTPException(status_code=400, detail="Le nouveau code doit contenir exactement 6 chiffres")
    await execute(
        "UPDATE users SET password_hash=%s, must_change_password=0 WHERE id=%s",
        (hash_password(data.new_password), user['id']),
    )
    return {"ok": True}

@api_router.patch("/me/notif-settings")
async def update_my_notif_settings(data: NotifSettingsIn, user=Depends(get_current_user)):
    mode = data.mode
    start = data.start if mode == 'range' else None
    end = data.end if mode == 'range' else None
    if mode == 'range':
        if _parse_hhmm(start) is None or _parse_hhmm(end) is None:
            raise HTTPException(status_code=400, detail="Heures invalides (format HH:MM)")
    await execute(
        "UPDATE users SET notif_mode=%s, notif_start=%s, notif_end=%s WHERE id=%s",
        (mode, start, end, user['id']),
    )
    fresh = await fetch_one("SELECT * FROM users WHERE id=%s", (user['id'],))
    return user_public(fresh)

# --- Public app settings (no auth) — read-only flags consumed by the login page ---
@api_router.get("/settings/public")
async def get_public_settings():
    return {
        "legacy_login_enabled": (await get_setting('legacy_login_enabled', '0')) == '1',
    }

# --- Super-admin settings ---
@api_router.get("/settings/admin")
async def get_admin_settings(user=Depends(require_super_admin)):
    return {
        "legacy_login_enabled": (await get_setting('legacy_login_enabled', '0')) == '1',
    }

@api_router.patch("/settings/legacy-login")
async def toggle_legacy_login(data: LegacyLoginToggleIn, user=Depends(require_super_admin)):
    await set_setting('legacy_login_enabled', '1' if data.enabled else '0')
    return {"legacy_login_enabled": data.enabled}

# ===================================================================
# Gestion Palettes (GP) — bureau-only
# ===================================================================
class PaletteIn(BaseModel):
    nombre_palettes: int = 1
    produit: Optional[str] = ''
    fournisseur: Optional[str] = ''
    klstb: Optional[str] = ''
    sscc: Optional[str] = ''
    gtin: Optional[str] = ''
    motif: Optional[str] = ''   # CSV string (single or multi)
    zone: Optional[str] = ''
    commentaire: Optional[str] = ''

class SavIn(BaseModel):
    """Legacy flat SAV payload (kept for backward compat)."""
    fournisseur: Optional[str] = ''
    nbre_pal: int = 0
    nbre_colis: int = 0
    ref: Optional[str] = ''
    transporteur: Optional[str] = ''
    destinataire: Optional[str] = ''
    livraison: Optional[str] = ''  # YYYY-MM-DD


class SavItemIn(BaseModel):
    fournisseur: Optional[str] = ''
    nbre_pal: int = 0
    nbre_colis: int = 0
    ref: Optional[str] = ''


class BordereauIn(BaseModel):
    date_bordereau: Optional[str] = None  # YYYY-MM-DD
    livraison: Optional[str] = ''         # date/heure de livraison du bordereau (texte libre)
    immatriculation: Optional[str] = ''
    temperature: Optional[str] = ''
    transporteur: Optional[str] = ''
    destinataire: Optional[str] = ''
    items: List[SavItemIn] = []


class MotifColorIn(BaseModel):
    label: str
    color_hex: str  # "#RRGGBB"


class SavClientIn(BaseModel):
    name: str
    kind: Optional[str] = 'both'  # 'transporteur' | 'destinataire' | 'both'
    address: Optional[str] = ''
    postal_code: Optional[str] = ''
    city: Optional[str] = ''
    country: Optional[str] = ''
    contact_name: Optional[str] = ''
    phone: Optional[str] = ''
    email: Optional[str] = ''
    notes: Optional[str] = ''


def palette_public(row: Dict[str, Any]) -> Dict[str, Any]:
    if not row:
        return None
    return {
        "id": row['id'],
        "nombre_palettes": row.get('nombre_palettes') or 1,
        "produit": row.get('produit') or '',
        "fournisseur": row.get('fournisseur') or '',
        "klstb": row.get('klstb') or '',
        "sscc": row.get('sscc') or '',
        "gtin": row.get('gtin') or '',
        "motif": row.get('motif') or '',
        "zone": row.get('zone') or '',
        "commentaire": row.get('commentaire') or '',
        "created_by": row.get('created_by'),
        "created_by_name": row.get('created_by_name') or '',
        "created_at": dt_to_iso(row.get('created_at')) if row.get('created_at') else None,
        "archived": bool(row.get('archived', 0)),
    }

def sav_public(row: Dict[str, Any]) -> Dict[str, Any]:
    if not row:
        return None
    return {
        "id": row['id'],
        "fournisseur": row.get('fournisseur') or '',
        "nbre_pal": row.get('nbre_pal') or 0,
        "nbre_colis": row.get('nbre_colis') or 0,
        "ref": row.get('ref') or '',
        "transporteur": row.get('transporteur') or '',
        "destinataire": row.get('destinataire') or '',
        "livraison": row.get('livraison') or '',
        "created_by": row.get('created_by'),
        "created_by_name": row.get('created_by_name') or '',
        "created_at": dt_to_iso(row.get('created_at')) if row.get('created_at') else None,
    }

# ----- Reference tables -----
@api_router.get("/gp/motifs")
async def list_motifs(user=Depends(require_bureau)):
    rows = await fetch_all("SELECT id, label FROM motifs ORDER BY label ASC")
    return [{"id": r['id'], "label": r['label']} for r in rows]


class MotifIn(BaseModel):
    label: str


@api_router.post("/gp/motifs")
async def create_motif(data: MotifIn, user=Depends(require_bureau)):
    label = (data.label or '').strip()
    if not label:
        raise HTTPException(status_code=400, detail="Label requis")
    if len(label) > 100:
        raise HTTPException(status_code=400, detail="Label trop long (max 100)")
    existing = await fetch_one("SELECT id FROM motifs WHERE LOWER(label)=%s", (label.lower(),))
    if existing:
        raise HTTPException(status_code=409, detail="Ce motif existe déjà")
    mid = await execute_returning_id("INSERT INTO motifs (label) VALUES (%s)", (label,))
    return {"ok": True, "id": mid, "label": label}


@api_router.delete("/gp/motifs/{motif_id}")
async def delete_motif(motif_id: int, user=Depends(require_bureau)):
    await execute("DELETE FROM motifs WHERE id=%s", (motif_id,))
    return {"ok": True}


@api_router.get("/gp/zones")
async def list_zones(user=Depends(require_bureau)):
    rows = await fetch_all("SELECT id, label FROM zones ORDER BY LEFT(label,1), CAST(SUBSTRING(label,2) AS UNSIGNED)")
    return [{"id": r['id'], "label": r['label']} for r in rows]


class ZoneIn(BaseModel):
    label: str


@api_router.post("/gp/zones")
async def create_zone(data: ZoneIn, user=Depends(require_bureau)):
    label = (data.label or '').strip().upper()
    if not label:
        raise HTTPException(status_code=400, detail="Label requis")
    if len(label) > 20:
        raise HTTPException(status_code=400, detail="Label trop long (max 20)")
    existing = await fetch_one("SELECT id FROM zones WHERE UPPER(label)=%s", (label,))
    if existing:
        raise HTTPException(status_code=409, detail="Cette zone existe déjà")
    zid = await execute_returning_id("INSERT INTO zones (label) VALUES (%s)", (label,))
    return {"ok": True, "id": zid, "label": label}


@api_router.delete("/gp/zones/{zone_id}")
async def delete_zone(zone_id: int, user=Depends(require_bureau)):
    await execute("DELETE FROM zones WHERE id=%s", (zone_id,))
    return {"ok": True}


# ----- Paramètres Palettes Reliquat (auto-archive) -----
@api_router.get("/gp/reliquat-settings")
async def get_reliquat_settings(user=Depends(require_bureau)):
    h = await get_setting('archive_hour', '06')
    m = await get_setting('archive_minute', '01')
    try:
        h_int = max(0, min(23, int(h)))
    except Exception:
        h_int = 6
    try:
        m_int = max(0, min(59, int(m)))
    except Exception:
        m_int = 1
    return {"archive_hour": h_int, "archive_minute": m_int}


class ReliquatSettingsIn(BaseModel):
    archive_hour: int
    archive_minute: int


@api_router.put("/gp/reliquat-settings")
async def set_reliquat_settings(data: ReliquatSettingsIn, user=Depends(require_bureau)):
    if not (0 <= data.archive_hour <= 23):
        raise HTTPException(status_code=400, detail="archive_hour doit être entre 0 et 23")
    if not (0 <= data.archive_minute <= 59):
        raise HTTPException(status_code=400, detail="archive_minute doit être entre 0 et 59")
    await set_setting('archive_hour', str(data.archive_hour))
    await set_setting('archive_minute', str(data.archive_minute))
    # Re-schedule the cron job with the new time so the change takes effect now
    try:
        await _reschedule_archive_job()
    except Exception as e:
        logging.warning(f"reschedule failed: {e}")
    return {"ok": True, "archive_hour": data.archive_hour, "archive_minute": data.archive_minute}


@api_router.get("/gp/reliquat-settings/scheduler-status")
async def reliquat_scheduler_status(user=Depends(require_bureau)):
    """Diag endpoint — returns the live `next_run_time` of the archive job so the
    bureau can confirm the cron is correctly programmed (used to debug TZ issues)."""
    global _scheduler
    if _scheduler is None:
        return {"running": False, "next_run": None, "timezone": SCHEDULER_TZ}
    try:
        job = _scheduler.get_job('auto_archive_palettes')
        next_run = job.next_run_time.isoformat() if job and job.next_run_time else None
        trig_tz = str(job.trigger.timezone) if job and hasattr(job.trigger, 'timezone') else None
    except Exception:
        next_run = None
        trig_tz = None
    return {
        "running": _scheduler.running,
        "next_run": next_run,
        "timezone": SCHEDULER_TZ,
        "trigger_timezone": trig_tz,
    }


# ----- Reliquats stats (Top 5 fournisseurs / destinations / motifs) -----
@api_router.get("/gp/stats/reliquats")
async def reliquat_stats(
    date_from: Optional[str] = None,   # "YYYY-MM-DD" inclus
    date_to: Optional[str] = None,     # "YYYY-MM-DD" inclus
    scope: str = "all",                # "active" | "archived" | "all"
    user=Depends(require_bureau),
):
    """Stats agrégées des palettes/reliquats pour le dashboard.

    Filtres : période [date_from, date_to] et scope (actives / archivées / les deux).
    Renvoie : totals, top 5 fournisseurs, top 5 destinations (zones), top 5 motifs,
    ainsi qu'une série quotidienne (date → count) pour la période.
    """
    where: List[str] = []
    params: List[Any] = []
    if date_from:
        where.append("DATE(p.created_at) >= %s")
        params.append(date_from)
    if date_to:
        where.append("DATE(p.created_at) <= %s")
        params.append(date_to)
    if scope == "active":
        where.append("p.archived = 0")
    elif scope == "archived":
        where.append("p.archived = 1")
    # scope=="all" => pas de filtre archived
    where_sql = (" WHERE " + " AND ".join(where)) if where else ""

    async def top5(field: str):
        # Special-case "destination" → 3 derniers caractères du KLSTB (cf. PHP legacy).
        select_expr = (
            "RIGHT(COALESCE(p.klstb, ''), 3)"
            if field == "destination"
            else f"TRIM(COALESCE(p.{field}, ''))"
        )
        rows = await fetch_all(
            f"SELECT {select_expr} AS label, "
            f"       COALESCE(SUM(p.nombre_palettes), 0) AS total, "
            f"       COUNT(*) AS lignes "
            f"FROM palettes p {where_sql} "
            f"GROUP BY {select_expr} "
            f"HAVING label <> '' "
            f"ORDER BY total DESC, lignes DESC "
            f"LIMIT 5",
            tuple(params),
        )
        return [
            {"label": r["label"], "total": int(r["total"] or 0), "lignes": int(r["lignes"] or 0)}
            for r in rows
        ]

    totals = await fetch_one(
        f"SELECT COUNT(*) AS lignes, COALESCE(SUM(p.nombre_palettes), 0) AS total "
        f"FROM palettes p {where_sql}",
        tuple(params),
    )

    daily = await fetch_all(
        f"SELECT DATE(p.created_at) AS d, "
        f"       COALESCE(SUM(p.nombre_palettes), 0) AS total, "
        f"       COUNT(*) AS lignes "
        f"FROM palettes p {where_sql} "
        f"GROUP BY DATE(p.created_at) "
        f"ORDER BY d ASC",
        tuple(params),
    )

    top_fournisseurs = await top5("fournisseur")
    top_destinations = await top5("destination")  # ← KLSTB[-3:] (cf. PHP legacy)
    top_motifs = await top5("motif")

    # Couleurs de motifs (utiles côté front pour réutiliser les coloris de la liste)
    color_rows = await fetch_all(
        "SELECT label, color_hex FROM motif_colors", ()
    )
    motif_colors = {r["label"]: r["color_hex"] for r in color_rows}

    return {
        "scope": scope,
        "date_from": date_from,
        "date_to": date_to,
        "totals": {
            "lignes": int((totals or {}).get("lignes") or 0),
            "palettes": int((totals or {}).get("total") or 0),
        },
        "top_fournisseurs": top_fournisseurs,
        "top_destinations": top_destinations,
        "top_motifs": top_motifs,
        "daily": [
            {
                "date": (r["d"].isoformat() if hasattr(r["d"], "isoformat") else str(r["d"])),
                "total": int(r["total"] or 0),
                "lignes": int(r["lignes"] or 0),
            }
            for r in daily
        ],
        "motif_colors": motif_colors,
    }


# ----- Palettes (reliquats) -----
@api_router.get("/gp/palettes")
async def list_palettes(
    q: Optional[str] = None,
    archived: int = 0,
    limit: int = 100,
    offset: int = 0,
    user=Depends(require_bureau),
):
    where = ["p.archived = %s"]
    params: List[Any] = [int(bool(archived))]
    if q:
        like = f"%{q}%"
        where.append("(p.sscc LIKE %s OR p.produit LIKE %s OR p.fournisseur LIKE %s OR p.klstb LIKE %s OR p.gtin LIKE %s OR p.commentaire LIKE %s)")
        params.extend([like, like, like, like, like, like])
    sql = (
        "SELECT p.*, u.fullname AS created_by_name "
        "FROM palettes p LEFT JOIN users u ON u.id = p.created_by "
        f"WHERE {' AND '.join(where)} "
        "ORDER BY p.created_at DESC LIMIT %s OFFSET %s"
    )
    params.extend([int(limit), int(offset)])
    rows = await fetch_all(sql, tuple(params))
    count_row = await fetch_one(
        f"SELECT COUNT(*) AS c FROM palettes p WHERE {' AND '.join(where)}",
        tuple(params[:-2]),
    )
    return {"items": [palette_public(r) for r in rows], "total": int(count_row['c'])}

@api_router.get("/gp/palettes/check-sscc")
async def check_palette_sscc(sscc: str, exclude_id: int = 0, user=Depends(require_bureau)):
    if not sscc.strip():
        return {"exists": False}
    row = await fetch_one(
        "SELECT id FROM palettes WHERE sscc = %s AND id <> %s LIMIT 1",
        (sscc.strip(), exclude_id),
    )
    return {"exists": bool(row), "existing_id": row['id'] if row else None}

@api_router.post("/gp/palettes")
async def create_palette(data: PaletteIn, user=Depends(require_bureau)):
    sscc = (data.sscc or '').strip() or None
    if sscc:
        dup = await fetch_one("SELECT id FROM palettes WHERE sscc=%s LIMIT 1", (sscc,))
        if dup:
            raise HTTPException(status_code=409, detail="Le SSCC existe déjà dans la base.")
    now = now_dt()
    pid = await execute_returning_id(
        "INSERT INTO palettes (nombre_palettes, produit, fournisseur, klstb, sscc, gtin, motif, zone, commentaire, created_by, created_at, archived) "
        "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,0)",
        (
            max(1, int(data.nombre_palettes or 1)),
            (data.produit or '').strip(),
            (data.fournisseur or '').strip(),
            (data.klstb or '').strip(),
            sscc,
            (data.gtin or '').strip(),
            (data.motif or '').strip(),
            (data.zone or '').strip(),
            (data.commentaire or '').strip(),
            user['id'],
            now,
        ),
    )
    row = await fetch_one(
        "SELECT p.*, u.fullname AS created_by_name FROM palettes p LEFT JOIN users u ON u.id=p.created_by WHERE p.id=%s",
        (pid,),
    )
    return palette_public(row)

@api_router.get("/gp/palettes/{pid}")
async def get_palette(pid: int, user=Depends(require_bureau)):
    row = await fetch_one(
        "SELECT p.*, u.fullname AS created_by_name FROM palettes p LEFT JOIN users u ON u.id=p.created_by WHERE p.id=%s",
        (pid,),
    )
    if not row:
        raise HTTPException(status_code=404, detail="Palette introuvable")
    return palette_public(row)

@api_router.patch("/gp/palettes/{pid}")
async def update_palette(pid: int, data: PaletteIn, user=Depends(require_bureau)):
    existing = await fetch_one("SELECT id FROM palettes WHERE id=%s", (pid,))
    if not existing:
        raise HTTPException(status_code=404, detail="Palette introuvable")
    sscc = (data.sscc or '').strip() or None
    if sscc:
        dup = await fetch_one(
            "SELECT id FROM palettes WHERE sscc=%s AND id<>%s LIMIT 1",
            (sscc, pid),
        )
        if dup:
            raise HTTPException(status_code=409, detail="Le SSCC existe déjà sur une autre palette.")
    await execute(
        "UPDATE palettes SET nombre_palettes=%s, produit=%s, fournisseur=%s, klstb=%s, sscc=%s, gtin=%s, motif=%s, zone=%s, commentaire=%s WHERE id=%s",
        (
            max(1, int(data.nombre_palettes or 1)),
            (data.produit or '').strip(),
            (data.fournisseur or '').strip(),
            (data.klstb or '').strip(),
            sscc,
            (data.gtin or '').strip(),
            (data.motif or '').strip(),
            (data.zone or '').strip(),
            (data.commentaire or '').strip(),
            pid,
        ),
    )
    row = await fetch_one(
        "SELECT p.*, u.fullname AS created_by_name FROM palettes p LEFT JOIN users u ON u.id=p.created_by WHERE p.id=%s",
        (pid,),
    )
    return palette_public(row)

@api_router.post("/gp/palettes/{pid}/archive")
async def archive_palette(pid: int, user=Depends(require_bureau)):
    await execute("UPDATE palettes SET archived=1 WHERE id=%s", (pid,))
    return {"ok": True}

@api_router.post("/gp/palettes/{pid}/unarchive")
async def unarchive_palette(pid: int, user=Depends(require_bureau)):
    await execute("UPDATE palettes SET archived=0 WHERE id=%s", (pid,))
    return {"ok": True}

@api_router.delete("/gp/palettes/{pid}")
async def delete_palette(pid: int, user=Depends(require_bureau)):
    await execute("DELETE FROM palettes WHERE id=%s", (pid,))
    return {"ok": True}

# ----- SAV / Bordereau de Chargement (parent + items) -----
def sav_item_public(row: Dict[str, Any]) -> Dict[str, Any]:
    if not row:
        return None
    return {
        "id": row['id'],
        "bordereau_id": row['bordereau_id'],
        "position": row.get('position') or 0,
        "fournisseur": row.get('fournisseur') or '',
        "nbre_pal": row.get('nbre_pal') or 0,
        "nbre_colis": row.get('nbre_colis') or 0,
        "ref": row.get('ref') or '',
    }


def bordereau_public(b: Dict[str, Any], items: List[Dict[str, Any]]) -> Dict[str, Any]:
    if not b:
        return None
    date_b = b.get('date_bordereau')
    if isinstance(date_b, datetime):
        date_b = date_b.date().isoformat()
    elif hasattr(date_b, 'isoformat'):
        date_b = date_b.isoformat()
    return {
        "id": b['id'],
        "date_bordereau": date_b,
        "livraison": b.get('livraison') or '',
        "immatriculation": b.get('immatriculation') or '',
        "temperature": b.get('temperature') or '',
        "transporteur": b.get('transporteur') or '',
        "destinataire": b.get('destinataire') or '',
        "created_by": b.get('created_by'),
        "created_by_name": b.get('created_by_name') or '',
        "created_at": dt_to_iso(b.get('created_at')) if b.get('created_at') else None,
        "archived": bool(b.get('archived')),
        "archived_at": dt_to_iso(b.get('archived_at')) if b.get('archived_at') else None,
        "items": [sav_item_public(it) for it in items],
        "totals": {
            "nbre_pal": sum(int(it.get('nbre_pal') or 0) for it in items),
            "nbre_colis": sum(int(it.get('nbre_colis') or 0) for it in items),
            "lines": len(items),
        },
    }


def _parse_date_str(s: Optional[str]) -> Optional[str]:
    """Return YYYY-MM-DD or None."""
    if not s:
        return None
    s = s.strip()
    if not s:
        return None
    try:
        return datetime.strptime(s[:10], '%Y-%m-%d').date().isoformat()
    except Exception:
        return None


@api_router.get("/gp/sav")
async def list_bordereaux(
    q: Optional[str] = None,
    archived: int = 0,
    limit: int = 100,
    offset: int = 0,
    user=Depends(require_bureau),
):
    where = ["b.archived = %s"]
    params: List[Any] = [1 if int(archived) else 0]
    if q:
        like = f"%{q}%"
        where.append(
            "(b.transporteur LIKE %s OR b.destinataire LIKE %s "
            "OR EXISTS (SELECT 1 FROM sav_items i WHERE i.bordereau_id=b.id "
            "AND (i.fournisseur LIKE %s OR i.ref LIKE %s)))"
        )
        params.extend([like, like, like, like])
    sql = (
        "SELECT b.*, u.fullname AS created_by_name "
        "FROM sav_bordereaux b LEFT JOIN users u ON u.id = b.created_by "
        f"WHERE {' AND '.join(where)} "
        "ORDER BY b.date_bordereau DESC, b.created_at DESC LIMIT %s OFFSET %s"
    )
    params.extend([int(limit), int(offset)])
    rows = await fetch_all(sql, tuple(params))
    cnt = await fetch_one(
        f"SELECT COUNT(*) AS c FROM sav_bordereaux b WHERE {' AND '.join(where)}",
        tuple(params[:-2]),
    )
    # Fetch items for the page in one query
    bord_ids = [r['id'] for r in rows]
    items_by_bord: Dict[int, List[Dict[str, Any]]] = {bid: [] for bid in bord_ids}
    if bord_ids:
        placeholders = ','.join(['%s'] * len(bord_ids))
        item_rows = await fetch_all(
            f"SELECT * FROM sav_items WHERE bordereau_id IN ({placeholders}) ORDER BY bordereau_id, position, id",
            tuple(bord_ids),
        )
        for it in item_rows:
            items_by_bord.setdefault(it['bordereau_id'], []).append(it)
    return {
        "items": [bordereau_public(r, items_by_bord.get(r['id'], [])) for r in rows],
        "total": int(cnt['c']),
    }


@api_router.post("/gp/sav")
async def create_bordereau(data: BordereauIn, user=Depends(require_bureau)):
    now = now_dt()
    date_b = _parse_date_str(data.date_bordereau) or now.date().isoformat()
    bid = await execute_returning_id(
        "INSERT INTO sav_bordereaux (date_bordereau, livraison, immatriculation, temperature, transporteur, destinataire, created_by, created_at) "
        "VALUES (%s,%s,%s,%s,%s,%s,%s,%s)",
        (
            date_b,
            (data.livraison or '').strip(),
            (data.immatriculation or '').strip(),
            (data.temperature or '').strip(),
            (data.transporteur or '').strip(),
            (data.destinataire or '').strip(),
            user['id'],
            now,
        ),
    )
    # Insert items (skip fully-empty lines)
    seq = []
    for idx, it in enumerate(data.items or []):
        fn = (it.fournisseur or '').strip()
        ref = (it.ref or '').strip()
        if not fn and not ref and int(it.nbre_pal or 0) == 0 and int(it.nbre_colis or 0) == 0:
            continue
        seq.append((
            bid, idx, fn, int(it.nbre_pal or 0), int(it.nbre_colis or 0), ref,
        ))
    if seq:
        await execute_many(
            "INSERT INTO sav_items (bordereau_id, position, fournisseur, nbre_pal, nbre_colis, ref) "
            "VALUES (%s,%s,%s,%s,%s,%s)",
            seq,
        )
    b = await fetch_one(
        "SELECT b.*, u.fullname AS created_by_name FROM sav_bordereaux b LEFT JOIN users u ON u.id=b.created_by WHERE b.id=%s",
        (bid,),
    )
    items = await fetch_all(
        "SELECT * FROM sav_items WHERE bordereau_id=%s ORDER BY position, id",
        (bid,),
    )
    return bordereau_public(b, items)


@api_router.get("/gp/sav/{bid}")
async def get_bordereau(bid: int, user=Depends(require_bureau)):
    b = await fetch_one(
        "SELECT b.*, u.fullname AS created_by_name FROM sav_bordereaux b LEFT JOIN users u ON u.id=b.created_by WHERE b.id=%s",
        (bid,),
    )
    if not b:
        raise HTTPException(status_code=404, detail="Bordereau introuvable")
    items = await fetch_all(
        "SELECT * FROM sav_items WHERE bordereau_id=%s ORDER BY position, id",
        (bid,),
    )
    return bordereau_public(b, items)


@api_router.patch("/gp/sav/{bid}")
async def update_bordereau(bid: int, data: BordereauIn, user=Depends(require_bureau)):
    existing = await fetch_one("SELECT id FROM sav_bordereaux WHERE id=%s", (bid,))
    if not existing:
        raise HTTPException(status_code=404, detail="Bordereau introuvable")
    date_b = _parse_date_str(data.date_bordereau) or now_dt().date().isoformat()

    # Build the rows to insert (validated first, before touching DB)
    seq: List[tuple] = []
    for idx, it in enumerate(data.items or []):
        fn = (it.fournisseur or '').strip()
        ref = (it.ref or '').strip()
        if not fn and not ref and int(it.nbre_pal or 0) == 0 and int(it.nbre_colis or 0) == 0:
            continue
        seq.append((
            bid, idx, fn, int(it.nbre_pal or 0), int(it.nbre_colis or 0), ref,
        ))

    # Run UPDATE + DELETE + INSERT atomically on a single connection so we
    # never end up with a bordereau missing its lines if a step fails.
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.begin()
        try:
            async with conn.cursor() as cur:
                await cur.execute(
                    "UPDATE sav_bordereaux SET date_bordereau=%s, livraison=%s, immatriculation=%s, temperature=%s, transporteur=%s, destinataire=%s WHERE id=%s",
                    (
                        date_b,
                        (data.livraison or '').strip(),
                        (data.immatriculation or '').strip(),
                        (data.temperature or '').strip(),
                        (data.transporteur or '').strip(),
                        (data.destinataire or '').strip(),
                        bid,
                    ),
                )
                await cur.execute("DELETE FROM sav_items WHERE bordereau_id=%s", (bid,))
                if seq:
                    await cur.executemany(
                        "INSERT INTO sav_items (bordereau_id, position, fournisseur, nbre_pal, nbre_colis, ref) "
                        "VALUES (%s,%s,%s,%s,%s,%s)",
                        seq,
                    )
            await conn.commit()
        except Exception:
            await conn.rollback()
            raise

    b = await fetch_one(
        "SELECT b.*, u.fullname AS created_by_name FROM sav_bordereaux b LEFT JOIN users u ON u.id=b.created_by WHERE b.id=%s",
        (bid,),
    )
    items = await fetch_all(
        "SELECT * FROM sav_items WHERE bordereau_id=%s ORDER BY position, id",
        (bid,),
    )
    return bordereau_public(b, items)


@api_router.delete("/gp/sav/{bid}")
async def delete_bordereau(bid: int, user=Depends(require_bureau)):
    await execute("DELETE FROM sav_bordereaux WHERE id=%s", (bid,))
    return {"ok": True}


@api_router.patch("/gp/sav/{bid}/archive")
async def archive_bordereau(bid: int, user=Depends(require_bureau)):
    row = await fetch_one("SELECT archived FROM sav_bordereaux WHERE id=%s", (bid,))
    if not row:
        raise HTTPException(status_code=404, detail="Bordereau non trouvé")
    new_val = 0 if row['archived'] else 1
    await execute(
        "UPDATE sav_bordereaux SET archived=%s, archived_at=%s WHERE id=%s",
        (new_val, now_dt() if new_val else None, bid),
    )
    return {"ok": True, "archived": bool(new_val)}


# ----- SAV Clients (Transporteurs / Destinataires réutilisables) -----
def sav_client_public(row: Dict[str, Any]) -> Dict[str, Any]:
    if not row:
        return None
    return {
        "id": row['id'],
        "name": row.get('name') or '',
        "kind": row.get('kind') or 'both',
        "address": row.get('address') or '',
        "postal_code": row.get('postal_code') or '',
        "city": row.get('city') or '',
        "country": row.get('country') or '',
        "contact_name": row.get('contact_name') or '',
        "phone": row.get('phone') or '',
        "email": row.get('email') or '',
        "notes": row.get('notes') or '',
        "created_at": dt_to_iso(row.get('created_at')) if row.get('created_at') else None,
        "updated_at": dt_to_iso(row.get('updated_at')) if row.get('updated_at') else None,
    }


VALID_SAV_CLIENT_KINDS = {'transporteur', 'destinataire', 'both'}


@api_router.get("/gp/sav-clients")
async def list_sav_clients(
    q: Optional[str] = None,
    kind: Optional[str] = None,
    user=Depends(require_bureau),
):
    where = ["1=1"]
    params: List[Any] = []
    if kind and kind in VALID_SAV_CLIENT_KINDS:
        if kind == 'both':
            pass  # no filter
        else:
            where.append("(kind = %s OR kind = 'both')")
            params.append(kind)
    if q:
        like = f"%{q.strip()}%"
        where.append("(name LIKE %s OR city LIKE %s OR contact_name LIKE %s)")
        params.extend([like, like, like])
    sql = f"SELECT * FROM sav_clients WHERE {' AND '.join(where)} ORDER BY name ASC LIMIT 500"
    rows = await fetch_all(sql, tuple(params))
    return {"items": [sav_client_public(r) for r in rows]}


@api_router.post("/gp/sav-clients")
async def create_sav_client(data: SavClientIn, user=Depends(require_bureau)):
    name = (data.name or '').strip()
    if not name:
        raise HTTPException(status_code=400, detail="Le nom est obligatoire")
    kind = (data.kind or 'both').strip().lower()
    if kind not in VALID_SAV_CLIENT_KINDS:
        kind = 'both'
    now = now_dt()
    cid = await execute_returning_id(
        "INSERT INTO sav_clients (name, kind, address, postal_code, city, country, "
        "contact_name, phone, email, notes, created_by, created_at, updated_at) "
        "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
        (
            name,
            kind,
            (data.address or '').strip(),
            (data.postal_code or '').strip(),
            (data.city or '').strip(),
            (data.country or '').strip(),
            (data.contact_name or '').strip(),
            (data.phone or '').strip(),
            (data.email or '').strip(),
            (data.notes or '').strip(),
            user['id'],
            now,
            now,
        ),
    )
    row = await fetch_one("SELECT * FROM sav_clients WHERE id=%s", (cid,))
    return sav_client_public(row)


@api_router.patch("/gp/sav-clients/{cid}")
async def update_sav_client(cid: int, data: SavClientIn, user=Depends(require_bureau)):
    existing = await fetch_one("SELECT id FROM sav_clients WHERE id=%s", (cid,))
    if not existing:
        raise HTTPException(status_code=404, detail="Client introuvable")
    name = (data.name or '').strip()
    if not name:
        raise HTTPException(status_code=400, detail="Le nom est obligatoire")
    kind = (data.kind or 'both').strip().lower()
    if kind not in VALID_SAV_CLIENT_KINDS:
        kind = 'both'
    await execute(
        "UPDATE sav_clients SET name=%s, kind=%s, address=%s, postal_code=%s, city=%s, "
        "country=%s, contact_name=%s, phone=%s, email=%s, notes=%s, updated_at=%s WHERE id=%s",
        (
            name,
            kind,
            (data.address or '').strip(),
            (data.postal_code or '').strip(),
            (data.city or '').strip(),
            (data.country or '').strip(),
            (data.contact_name or '').strip(),
            (data.phone or '').strip(),
            (data.email or '').strip(),
            (data.notes or '').strip(),
            now_dt(),
            cid,
        ),
    )
    row = await fetch_one("SELECT * FROM sav_clients WHERE id=%s", (cid,))
    return sav_client_public(row)


@api_router.delete("/gp/sav-clients/{cid}")
async def delete_sav_client(cid: int, user=Depends(require_bureau)):
    await execute("DELETE FROM sav_clients WHERE id=%s", (cid,))
    return {"ok": True}


# ----- Motif colors (couleurs pastilles dans le PDF Reliquats) -----
def _gp_norm(s: str) -> str:
    import unicodedata
    s = (s or '').strip().lower()
    s = ''.join(c for c in unicodedata.normalize('NFD', s) if unicodedata.category(c) != 'Mn')
    s = re.sub(r'[^a-z0-9]+', ' ', s)
    return s.strip()


@api_router.get("/gp/motif-colors")
async def list_motif_colors(user=Depends(require_bureau)):
    rows = await fetch_all(
        "SELECT id, label, label_norm, color_hex FROM motif_colors ORDER BY label ASC",
    )
    return [{"id": r['id'], "label": r['label'], "color_hex": (r.get('color_hex') or '#E0E0E0').upper()} for r in rows]


@api_router.put("/gp/motif-colors")
async def upsert_motif_color(data: MotifColorIn, user=Depends(require_bureau)):
    label = (data.label or '').strip()
    if not label:
        raise HTTPException(status_code=400, detail="Label requis")
    color = (data.color_hex or '').strip().upper()
    if not re.match(r'^#[0-9A-F]{6}$', color):
        raise HTTPException(status_code=400, detail="color_hex doit être #RRGGBB")
    norm = _gp_norm(label) or 'motif'
    await execute(
        "INSERT INTO motif_colors (label, label_norm, color_hex) VALUES (%s,%s,%s) "
        "ON DUPLICATE KEY UPDATE label=VALUES(label), color_hex=VALUES(color_hex)",
        (label, norm, color),
    )
    return {"ok": True, "label": label, "color_hex": color}


# ----- Bordereau settings (expéditeur, SAV, température, texte IMPORTANT) -----
DEFAULT_BORDEREAU_SETTINGS = {
    "expediteur": "STEF EUROFRISCHFRACHT\n35 RUE DE CALAIS\n67100 STRASBOURG\nFrance",
    "sav_phone": "+33 3 88 24 40 20",
    "sav_email": "STEF-TRP_EUROFRISCHFRACHT_SAV@stef.com",
    "temperature_default": "+2 / +4 °C",
    "important_text": (
        "Ce document est un document interne à STEF EFF : il ne remplace en rien les lettres de "
        "voiture que vous avez établies pour chaque opération de transport. Le transport ne sera "
        "effectué qu'avec un bon à enlever ; destination selon copie de dossier. "
        "STEF EUROFRISCHFRACHT -- Marchandises reçues / Goods received"
    ),
}


class BordereauSettingsIn(BaseModel):
    expediteur: Optional[str] = None
    sav_phone: Optional[str] = None
    sav_email: Optional[str] = None
    temperature_default: Optional[str] = None
    important_text: Optional[str] = None


async def _get_bordereau_settings() -> Dict[str, str]:
    row = await fetch_one("SELECT * FROM bordereau_settings WHERE id=1")
    if not row:
        return dict(DEFAULT_BORDEREAU_SETTINGS)
    return {
        "expediteur": row.get('expediteur') or DEFAULT_BORDEREAU_SETTINGS['expediteur'],
        "sav_phone": row.get('sav_phone') or DEFAULT_BORDEREAU_SETTINGS['sav_phone'],
        "sav_email": row.get('sav_email') or DEFAULT_BORDEREAU_SETTINGS['sav_email'],
        "temperature_default": row.get('temperature_default') or DEFAULT_BORDEREAU_SETTINGS['temperature_default'],
        "important_text": row.get('important_text') or DEFAULT_BORDEREAU_SETTINGS['important_text'],
    }


@api_router.get("/gp/bordereau-settings")
async def get_bordereau_settings(user=Depends(require_bureau)):
    return await _get_bordereau_settings()


@api_router.put("/gp/bordereau-settings")
async def update_bordereau_settings(data: BordereauSettingsIn, user=Depends(require_bureau)):
    current = await _get_bordereau_settings()
    merged = {
        "expediteur":          (data.expediteur if data.expediteur is not None else current['expediteur']).strip(),
        "sav_phone":           (data.sav_phone if data.sav_phone is not None else current['sav_phone']).strip(),
        "sav_email":           (data.sav_email if data.sav_email is not None else current['sav_email']).strip(),
        "temperature_default": (data.temperature_default if data.temperature_default is not None else current['temperature_default']).strip(),
        "important_text":      (data.important_text if data.important_text is not None else current['important_text']).strip(),
    }
    await execute(
        "INSERT INTO bordereau_settings (id, expediteur, sav_phone, sav_email, temperature_default, important_text) "
        "VALUES (1, %s, %s, %s, %s, %s) "
        "ON DUPLICATE KEY UPDATE expediteur=VALUES(expediteur), sav_phone=VALUES(sav_phone), "
        "sav_email=VALUES(sav_email), temperature_default=VALUES(temperature_default), important_text=VALUES(important_text)",
        (merged['expediteur'], merged['sav_phone'], merged['sav_email'], merged['temperature_default'], merged['important_text']),
    )
    return merged


# ----- Hub stats (last 24h) -----
@api_router.get("/gp/stats/24h")
async def gp_stats_24h(user=Depends(require_bureau)):
    since = now_dt() - timedelta(hours=24)
    p_active = await fetch_one("SELECT COUNT(*) AS c FROM palettes WHERE archived=0", ())
    p_24h = await fetch_one("SELECT COUNT(*) AS c FROM palettes WHERE created_at >= %s", (since,))
    p_archived_24h = await fetch_one(
        "SELECT COUNT(*) AS c FROM palettes WHERE archived=1 AND created_at >= %s",
        (since,),
    )
    s_24h = await fetch_one("SELECT COUNT(*) AS c FROM sav_bordereaux WHERE created_at >= %s", (since,))
    s_total = await fetch_one("SELECT COUNT(*) AS c FROM sav_bordereaux", ())
    return {
        "palettes_active": int(p_active['c']),
        "palettes_24h": int(p_24h['c']),
        "palettes_archived_24h": int(p_archived_24h['c']),
        "sav_24h": int(s_24h['c']),
        "sav_total": int(s_total['c']),
    }

# ----- PDF helpers (couleurs, mise en page) -----
STEF_LOGO_PATH = ROOT_DIR / "assets" / "stef_logo.png"

PDF_STYLE = {
    'primary':    (33/255, 93/255, 164/255),     # #215DA4
    'thead_text': (1, 1, 1),
    'border':     (210/255, 210/255, 210/255),
    'row':        (1, 1, 1),
    'row_alt':    (248/255, 250/255, 252/255),
    'group_bg':   (238/255, 243/255, 255/255),
    'group_text': (33/255, 93/255, 164/255),
    'muted':      (90/255, 90/255, 90/255),
}


def _service_label(code: Optional[str]) -> str:
    return {
        'MATIN': 'Matin',
        'JOURNEE': 'Journée',
        'APRES_MIDI': 'Après-midi',
        'NUIT': 'Nuit',
    }.get(code or '', 'Inconnu')


def _hex_to_rgb01(hex_str: str) -> Tuple[float, float, float]:
    h = (hex_str or '#CCCCCC').lstrip('#')
    if len(h) == 3:
        h = ''.join(c * 2 for c in h)
    try:
        r = int(h[0:2], 16) / 255.0
        g = int(h[2:4], 16) / 255.0
        b = int(h[4:6], 16) / 255.0
        return (r, g, b)
    except Exception:
        return (0.8, 0.8, 0.8)


async def _get_motif_color_map() -> Dict[str, str]:
    """Return { normalized_label: '#RRGGBB' } for all known motifs."""
    rows = await fetch_all("SELECT label, label_norm, color_hex FROM motif_colors")
    out: Dict[str, str] = {}
    for r in rows:
        key = (r.get('label_norm') or _gp_norm(r.get('label') or '')).strip()
        if not key:
            continue
        out[key] = (r.get('color_hex') or '#E0E0E0').upper()
    return out


def _color_for_motif(label: str, color_map: Dict[str, str]) -> Tuple[float, float, float]:
    norm = _gp_norm(label)
    if norm in color_map:
        return _hex_to_rgb01(color_map[norm])
    # Pastel stable fallback (same logic as PHP gp_get_motif_color)
    import zlib
    h = zlib.crc32(norm.encode('utf-8'))
    r = 180 + (h & 0x1F)
    g = 180 + ((h >> 5) & 0x1F)
    b = 180 + ((h >> 10) & 0x1F)
    return (r / 255.0, g / 255.0, b / 255.0)


def _split_motifs(raw: str) -> List[str]:
    if not raw:
        return []
    s = raw.replace('\r\n', '\n').replace('\r', '\n')
    parts = re.split(r'\s*-\s*|[,;|\n]+', s)
    out: List[str] = []
    seen = set()
    for p in parts:
        p = (p or '').strip().lstrip('-•').strip()
        if not p:
            continue
        if p in seen:
            continue
        seen.add(p)
        out.append(p)
    return out


@api_router.get("/gp/export-palettes-pdf")
async def export_palettes_pdf(
    archived: int = 0,
    q: Optional[str] = None,
    user=Depends(require_bureau),
):
    """PDF Reliquats — calque exact de l'ancien export_pdf.php (FPDF):
    - Paysage A4, logo STEF en haut à droite
    - Total palettes
    - Tableau Pal | Fournisseur | KLSTB | SSCC | GTIN | Motif | Zone | Commentaire
    - Regroupement par équipe (MATIN/JOURNEE/APRES_MIDI/NUIT)
    - Pastilles couleur par motif
    """
    from reportlab.lib.pagesizes import A4, landscape
    from reportlab.lib.units import mm
    from reportlab.pdfgen import canvas
    from reportlab.pdfbase.pdfmetrics import stringWidth
    import io

    where = ["p.archived = %s"]
    params: List[Any] = [int(bool(archived))]
    if q:
        like = f"%{q}%"
        where.append("(p.sscc LIKE %s OR p.produit LIKE %s OR p.fournisseur LIKE %s OR p.klstb LIKE %s OR p.gtin LIKE %s)")
        params.extend([like, like, like, like, like])

    # Total palettes filtré
    tot = await fetch_one(
        f"SELECT COALESCE(SUM(p.nombre_palettes),0) AS total FROM palettes p WHERE {' AND '.join(where)}",
        tuple(params),
    )
    total_pal = int(tot['total'] or 0) if tot else 0

    # Données triées par équipe
    sql = (
        "SELECT p.*, u.fullname AS created_by_name, u.username AS created_by_username, "
        "u.service_shift AS user_service "
        "FROM palettes p LEFT JOIN users u ON u.id = p.created_by "
        f"WHERE {' AND '.join(where)} "
        "ORDER BY FIELD(COALESCE(u.service_shift,'Z'),'MATIN','JOURNEE','APRES_MIDI','NUIT','Z') ASC, "
        "p.created_at DESC LIMIT 5000"
    )
    rows = await fetch_all(sql, tuple(params))
    color_map = await _get_motif_color_map()

    buf = io.BytesIO()
    page_size = landscape(A4)
    page_w, page_h = page_size
    LEFT = 10 * mm
    RIGHT = page_w - 10 * mm
    BOTTOM_MARGIN = 15 * mm
    TABLE_W = RIGHT - LEFT

    # Colonnes (mm) — totalisent ~TABLE_W (277mm en A4 paysage avec marges 10mm)
    cols = [
        ('produit',         'Fournisseur', 50 * mm),
        ('klstb',           'KLSTB', 28 * mm),
        ('sscc',            'SSCC', 46 * mm),
        ('gtin',            'GTIN', 18 * mm),
        ('motif',           'Motif', 60 * mm),
        ('zone',            'Zone', 16 * mm),
        ('commentaire',     'Commentaire', 60 * mm),
    ]
    # Ajuste la dernière colonne pour remplir TABLE_W
    used = sum(w for _, _, w in cols[:-1])
    cols[-1] = (cols[-1][0], cols[-1][1], max(20 * mm, TABLE_W - used))

    LINE_H = 4.5 * mm
    PAD = 1.5 * mm
    CHIP_W = 2.2 * mm

    c = canvas.Canvas(buf, pagesize=page_size)

    def draw_header(page_num: int):
        # Titre centré
        c.setFont('Helvetica-Bold', 15)
        c.drawCentredString(page_w / 2, page_h - 12 * mm, 'Palettes Reliquat')
        # Logo STEF en haut à droite
        try:
            if STEF_LOGO_PATH.exists():
                logo_w = 40 * mm
                logo_h = 18 * mm
                c.drawImage(
                    str(STEF_LOGO_PATH),
                    page_w - 10 * mm - logo_w, page_h - 24 * mm,
                    width=logo_w, height=logo_h, preserveAspectRatio=True, mask='auto',
                )
        except Exception:
            pass
        # Date
        c.setFont('Helvetica', 9)
        c.drawRightString(
            page_w - 10 * mm, page_h - 28 * mm,
            f"Enregistré le : {datetime.now().strftime('%d/%m/%Y %H:%M:%S')}",
        )
        # Ligne séparation
        c.setStrokeColorRGB(*PDF_STYLE['border'])
        c.setLineWidth(0.4)
        c.line(LEFT, page_h - 30 * mm, RIGHT, page_h - 30 * mm)
        # Total
        c.setFont('Helvetica', 11)
        c.setFillColorRGB(0, 0, 0)
        scope_label = 'Archivées' if archived else 'Actives'
        c.drawString(LEFT, page_h - 36 * mm, f"Nombre total de palettes ({scope_label}) : {total_pal}")
        # Pied de page (n° de page)
        c.setFont('Helvetica', 8)
        c.setFillColorRGB(*PDF_STYLE['muted'])
        c.drawRightString(RIGHT, 8 * mm, f"Page {page_num}")
        c.setFillColorRGB(0, 0, 0)

    def draw_table_header(y: float):
        c.setFillColorRGB(*PDF_STYLE['primary'])
        c.rect(LEFT, y - 7 * mm, TABLE_W, 7 * mm, fill=1, stroke=0)
        c.setFillColorRGB(*PDF_STYLE['thead_text'])
        c.setFont('Helvetica-Bold', 9)
        x = LEFT
        for _, title, w in cols:
            c.drawCentredString(x + w / 2, y - 5 * mm, title)
            x += w
        # bordures
        c.setStrokeColorRGB(*PDF_STYLE['border'])
        c.setLineWidth(0.3)
        x = LEFT
        for _, _, w in cols:
            c.rect(x, y - 7 * mm, w, 7 * mm, fill=0, stroke=1)
            x += w
        c.setFillColorRGB(0, 0, 0)

    # Premier rendu
    page_num = 1
    draw_header(page_num)
    y = page_h - 38 * mm
    draw_table_header(y)
    y -= 7 * mm

    current_service: Optional[str] = None
    alt = False

    if not rows:
        c.setFont('Helvetica-Oblique', 10)
        c.setFillColorRGB(*PDF_STYLE['muted'])
        c.drawCentredString(page_w / 2, y - 10 * mm, 'Aucune palette trouvée.')
    else:
        for r in rows:
            svc = _service_label(r.get('user_service'))

            # ---- Séparateur équipe ----
            if svc != current_service:
                current_service = svc
                sep_h = 6 * mm
                if y - sep_h < BOTTOM_MARGIN + 5 * mm:
                    c.showPage()
                    page_num += 1
                    draw_header(page_num)
                    y = page_h - 38 * mm
                    draw_table_header(y)
                    y -= 7 * mm
                c.setFillColorRGB(*PDF_STYLE['group_bg'])
                c.rect(LEFT, y - sep_h, TABLE_W, sep_h, fill=1, stroke=0)
                c.setFillColorRGB(*PDF_STYLE['group_text'])
                c.setFont('Helvetica-Bold', 9)
                c.drawString(LEFT + 3 * mm, y - sep_h + 1.7 * mm, f"Équipe : {svc}")
                c.setFillColorRGB(0, 0, 0)
                y -= sep_h

            # ---- Préparer cellules motif & commentaire (wrap) ----
            motifs_raw = _split_motifs(str(r.get('motif') or ''))
            # Strip les caractères non rendus par Helvetica (emojis, symboles unicode)
            # qui apparaissent comme des carrés vides dans le PDF (cf. "🟥 Nouveau SSCC")
            def _ascii_safe(s: str) -> str:
                try:
                    return s.encode('latin-1', 'ignore').decode('latin-1').strip()
                except Exception:
                    return s
            motifs = [_ascii_safe(m) for m in motifs_raw]
            motifs = [m for m in motifs if m]
            motif_count = max(1, len(motifs))
            motif_font = 10
            if motif_count >= 13: motif_font = 6
            elif motif_count >= 8: motif_font = 7
            elif motif_count >= 5: motif_font = 8
            elif motif_count >= 3: motif_font = 9

            comment = str(r.get('commentaire') or '')
            if len(comment) > 500:
                comment = comment[:497] + '...'

            # wrap helper
            def _wrap(text: str, w: float, font_name: str, font_size: float) -> List[str]:
                if not text:
                    return ['']
                lines: List[str] = []
                for raw_line in text.split('\n'):
                    words = raw_line.split(' ')
                    cur = ''
                    for word in words:
                        candidate = (cur + ' ' + word).strip() if cur else word
                        if stringWidth(candidate, font_name, font_size) <= w:
                            cur = candidate
                        else:
                            if cur:
                                lines.append(cur)
                            # mot trop long: on coupe
                            while stringWidth(word, font_name, font_size) > w and len(word) > 1:
                                # estimate chars per width
                                lo, hi = 1, len(word)
                                fit = 1
                                while lo <= hi:
                                    mid = (lo + hi) // 2
                                    if stringWidth(word[:mid], font_name, font_size) <= w:
                                        fit = mid; lo = mid + 1
                                    else:
                                        hi = mid - 1
                                lines.append(word[:fit])
                                word = word[fit:]
                            cur = word
                    lines.append(cur)
                return lines or ['']

            # Lookup par clé pour éviter de hardcoder les index (qui changent
            # si on ajoute/retire une colonne)
            _col_widths = {key: w for key, _, w in cols}
            motif_col_w = _col_widths['motif']
            motif_text_w = motif_col_w - 2 * PAD - 4 * mm  # 4mm pour pastilles
            motif_text = '\n'.join(f"- {m}" for m in motifs) if motifs else ''
            motif_lines = _wrap(motif_text, motif_text_w, 'Helvetica', motif_font)

            comment_col_w = _col_widths['commentaire']
            comment_lines = _wrap(comment, comment_col_w - 2 * PAD, 'Helvetica', 9)

            row_lines = max(2, len(motif_lines), len(comment_lines))
            row_h = row_lines * LINE_H + 1 * mm

            if y - row_h < BOTTOM_MARGIN + 5 * mm:
                c.showPage()
                page_num += 1
                draw_header(page_num)
                y = page_h - 38 * mm
                draw_table_header(y)
                y -= 7 * mm

            # Fond alterné
            alt = not alt
            fill = PDF_STYLE['row_alt'] if alt else PDF_STYLE['row']
            c.setFillColorRGB(*fill)
            c.rect(LEFT, y - row_h, TABLE_W, row_h, fill=1, stroke=0)

            # Bordures verticales + horizontales
            c.setStrokeColorRGB(*PDF_STYLE['border'])
            c.setLineWidth(0.2)
            x = LEFT
            for _, _, w in cols:
                c.rect(x, y - row_h, w, row_h, fill=0, stroke=1)
                x += w

            # Texte
            x = LEFT
            for col_key, _, w in cols:
                cx = x + PAD
                cy = y - PAD - LINE_H * 0.7

                if col_key == 'motif':
                    # Dessine pastilles à gauche
                    chip_left = x + PAD
                    text_left = chip_left + CHIP_W + 1.2 * mm
                    c.setFont('Helvetica', motif_font)
                    c.setFillColorRGB(0, 0, 0)
                    for i, m in enumerate(motifs):
                        rgb = _color_for_motif(m, color_map)
                        c.setFillColorRGB(*rgb)
                        chip_y = y - PAD - (i + 1) * LINE_H + 0.6 * mm
                        c.rect(chip_left, chip_y, CHIP_W, LINE_H - 1.2 * mm, fill=1, stroke=0)
                    c.setFillColorRGB(0, 0, 0)
                    ty = y - PAD - LINE_H * 0.7
                    for line in motif_lines:
                        c.drawString(text_left, ty, line)
                        ty -= LINE_H
                elif col_key == 'commentaire':
                    c.setFont('Helvetica', 9)
                    c.setFillColorRGB(*PDF_STYLE['muted'])
                    ty = y - PAD - LINE_H * 0.7
                    for line in comment_lines:
                        c.drawString(cx, ty, line)
                        ty -= LINE_H
                    c.setFillColorRGB(0, 0, 0)
                else:
                    txt = str(r.get(col_key) or '')
                    if col_key == 'produit':
                        txt = (r.get('fournisseur') or r.get('produit') or '')
                    # Strip emojis/unicode non-Latin1 pour éviter les carrés vides
                    try:
                        txt = txt.encode('latin-1', 'ignore').decode('latin-1').strip()
                    except Exception:
                        pass
                    c.setFont('Helvetica', 10)
                    c.setFillColorRGB(0, 0, 0)
                    # Troncature en largeur réelle (et non plus en nb de chars) avec ellipsis
                    max_text_w = w - 2 * PAD
                    if stringWidth(txt, 'Helvetica', 10) > max_text_w:
                        ell = '…'
                        while txt and stringWidth(txt + ell, 'Helvetica', 10) > max_text_w:
                            txt = txt[:-1]
                        txt = txt + ell if txt else ''
                    # centré vertical
                    c.drawCentredString(x + w / 2, y - row_h / 2 - 2, txt)
                x += w

            y -= row_h

    c.showPage()
    c.save()
    pdf = buf.getvalue()
    fname = f"palettes_{'archivees' if archived else 'actives'}_{datetime.now().strftime('%Y%m%d_%H%M')}.pdf"
    from fastapi.responses import Response
    return Response(content=pdf, media_type='application/pdf', headers={
        'Content-Disposition': f'attachment; filename="{fname}"',
    })


@api_router.get("/gp/export-sav-pdf")
async def export_sav_pdf(
    bid: Optional[int] = None,
    q: Optional[str] = None,
    user=Depends(require_bureau),
):
    """PDF Bordereau de Chargement (SAV / Retours fournisseurs).

    - Si `bid` est fourni : génère 1 page = 1 bordereau (présentation document).
    - Sinon : liste paginée filtrée par `q`.
    """
    from reportlab.lib.pagesizes import A4
    from reportlab.lib.units import mm
    from reportlab.pdfgen import canvas
    from reportlab.pdfbase.pdfmetrics import stringWidth
    import io

    # Récupère le(s) bordereau(x) demandé(s)
    where = ["1=1"]
    params: List[Any] = []
    if bid is not None:
        where.append("b.id = %s")
        params.append(int(bid))
    elif q:
        like = f"%{q}%"
        where.append(
            "(b.transporteur LIKE %s OR b.destinataire LIKE %s "
            "OR EXISTS (SELECT 1 FROM sav_items i WHERE i.bordereau_id=b.id "
            "AND (i.fournisseur LIKE %s OR i.ref LIKE %s)))"
        )
        params.extend([like, like, like, like])

    sql = (
        "SELECT b.*, u.fullname AS created_by_name "
        "FROM sav_bordereaux b LEFT JOIN users u ON u.id = b.created_by "
        f"WHERE {' AND '.join(where)} "
        "ORDER BY b.date_bordereau DESC, b.created_at DESC LIMIT 500"
    )
    bords = await fetch_all(sql, tuple(params))
    bord_ids = [r['id'] for r in bords]
    items_by_bord: Dict[int, List[Dict[str, Any]]] = {bid_: [] for bid_ in bord_ids}
    if bord_ids:
        placeholders = ','.join(['%s'] * len(bord_ids))
        item_rows = await fetch_all(
            f"SELECT * FROM sav_items WHERE bordereau_id IN ({placeholders}) ORDER BY bordereau_id, position, id",
            tuple(bord_ids),
        )
        for it in item_rows:
            items_by_bord.setdefault(it['bordereau_id'], []).append(it)

    buf = io.BytesIO()
    page_w, page_h = A4
    LEFT = 15 * mm
    RIGHT = page_w - 15 * mm
    TABLE_W = RIGHT - LEFT
    c = canvas.Canvas(buf, pagesize=A4)

    # Charge les paramètres du bordereau (expéditeur, SAV, température, IMPORTANT)
    settings = await _get_bordereau_settings()
    expediteur_lines = [ln for ln in (settings['expediteur'] or '').split('\n') if ln.strip()]
    sav_line = f"Service Après Vente : Tél : {settings['sav_phone']}  {settings['sav_email']}"
    important_text = settings['important_text']
    temperature_default = settings['temperature_default']

    def fmt_date(d) -> str:
        if not d:
            return ''
        if isinstance(d, datetime):
            return d.strftime('%d/%m/%Y')
        try:
            return datetime.strptime(str(d)[:10], '%Y-%m-%d').strftime('%d/%m/%Y')
        except Exception:
            return str(d)

    # Couleurs spécifiques au modèle STEF officiel
    NAVY = (0.12, 0.31, 0.55)  # bleu navy STEF
    GRAY_LBL = (0.30, 0.30, 0.30)

    def _wrap_text(txt: str, max_w: float, font: str, fs: float) -> List[str]:
        lines: List[str] = []
        for raw in (txt or '').split('\n'):
            words = raw.split(' ')
            cur = ''
            for word in words:
                cand = (cur + ' ' + word).strip() if cur else word
                if stringWidth(cand, font, fs) <= max_w:
                    cur = cand
                else:
                    if cur:
                        lines.append(cur)
                    cur = word
            if cur:
                lines.append(cur)
        return lines or ['']

    def rounded_box(x, y, w, h, radius=2.5*mm, stroke_w=0.8):
        c.setStrokeColorRGB(*NAVY)
        c.setLineWidth(stroke_w)
        c.roundRect(x, y, w, h, radius, fill=0, stroke=1)

    def labeled_box(x, y, w, h, label, content_lines, label_size=8, content_size=10, content_font='Helvetica'):
        """Cadre arrondi navy avec un label en haut et du contenu en dessous."""
        rounded_box(x, y, w, h)
        c.setFillColorRGB(*GRAY_LBL)
        c.setFont('Helvetica', label_size)
        c.drawString(x + 3*mm, y + h - 4.5*mm, label)
        c.setFillColorRGB(0, 0, 0)
        c.setFont(content_font, content_size)
        line_h = 4.5*mm
        ty = y + h - 9*mm  # première ligne sous le label
        for line in content_lines:
            if ty < y + 2*mm:
                break
            c.drawString(x + 3*mm, ty, line)
            ty -= line_h

    def draw_bordereau(b: Dict[str, Any], items: List[Dict[str, Any]]):
        # ===== 1. EN-TÊTE =====
        # Logo STEF en haut à gauche
        try:
            if STEF_LOGO_PATH.exists():
                c.drawImage(
                    str(STEF_LOGO_PATH), LEFT, page_h - 32*mm,
                    width=40*mm, height=18*mm, preserveAspectRatio=True, mask='auto',
                )
        except Exception:
            pass

        # Titre "BORDEREAU DE CHARGEMENT" centré dans un cadre arrondi
        title_w = 100*mm
        title_h = 11*mm
        title_x = (page_w - title_w) / 2
        title_y = page_h - 25*mm
        rounded_box(title_x, title_y, title_w, title_h)
        c.setFont('Helvetica-Bold', 13)
        c.setFillColorRGB(0, 0, 0)
        c.drawCentredString(page_w / 2, title_y + 4*mm, 'BORDEREAU DE CHARGEMENT')

        # ===== 2. DATE (gauche) + PRISE A QUAI (droite) =====
        y_row = page_h - 50*mm
        date_w = 70*mm
        prise_w = 55*mm
        row_h = 10*mm

        rounded_box(LEFT, y_row, date_w, row_h)
        c.setFont('Helvetica-Bold', 10)
        c.setFillColorRGB(0, 0, 0)
        date_txt = f"DATE : {fmt_date(b.get('date_bordereau'))}"
        c.drawString(LEFT + 4*mm, y_row + 3.5*mm, date_txt)

        rounded_box(RIGHT - prise_w, y_row, prise_w, row_h)
        c.setFont('Helvetica-Bold', 10)
        c.drawCentredString(RIGHT - prise_w/2, y_row + 3.5*mm, 'PRISE A QUAI')

        # ===== 3. EXPÉDITEUR (cadre large, depuis bordereau_settings) =====
        y_exp = y_row - 4*mm
        # Hauteur adaptative : 5mm marge haute + 4.5mm par ligne + 3mm marge basse
        exp_h = max(22*mm, 8*mm + len(expediteur_lines) * 4.5*mm + 3*mm)
        labeled_box(
            LEFT, y_exp - exp_h, TABLE_W, exp_h,
            'Expéditeur (nom, adresse, pays)',
            expediteur_lines,
            label_size=8, content_size=10,
        )

        # ===== 4. TRANSPORTEUR (large) + DESTINATAIRE (étroit) =====
        y_t = y_exp - exp_h - 4*mm
        trp_w = (TABLE_W * 0.60)
        dest_w = TABLE_W - trp_w - 4*mm
        td_h = 22*mm

        # Transporteur (avec sous-titre EN)
        rounded_box(LEFT, y_t - td_h, trp_w, td_h)
        c.setFillColorRGB(*GRAY_LBL)
        c.setFont('Helvetica', 8)
        c.drawString(LEFT + 3*mm, y_t - 4.5*mm, 'Transporteur (nom, adresse, pays) / Carrier (name, address, country)')
        c.setFillColorRGB(0, 0, 0)
        c.setFont('Helvetica', 10)
        trp_lines = _wrap_text(b.get('transporteur') or '', trp_w - 6*mm, 'Helvetica', 10)
        ty = y_t - 10*mm
        for line in trp_lines:
            if ty < y_t - td_h + 2*mm: break
            c.drawString(LEFT + 3*mm, ty, line)
            ty -= 4.5*mm

        # Destinataire
        dx = LEFT + trp_w + 4*mm
        rounded_box(dx, y_t - td_h, dest_w, td_h)
        c.setFillColorRGB(*GRAY_LBL)
        c.setFont('Helvetica', 8)
        c.drawString(dx + 3*mm, y_t - 4.5*mm, 'Destinataire (nom, adresse, pays)')
        c.drawString(dx + 3*mm, y_t - 8*mm, 'Consignee (name, address, country)')
        c.setFillColorRGB(0, 0, 0)
        c.setFont('Helvetica', 10)
        dest_lines = _wrap_text(b.get('destinataire') or '', dest_w - 6*mm, 'Helvetica', 10)
        ty = y_t - 13*mm
        for line in dest_lines:
            if ty < y_t - td_h + 2*mm: break
            c.drawString(dx + 3*mm, ty, line)
            ty -= 4.5*mm

        # ===== 5. SAV bandeau (depuis settings) =====
        y_sav = y_t - td_h - 4*mm
        sav_h = 7*mm
        rounded_box(LEFT, y_sav - sav_h, TABLE_W, sav_h)
        c.setFillColorRGB(*GRAY_LBL)
        c.setFont('Helvetica', 8)
        c.drawString(LEFT + 3*mm, y_sav - 4.5*mm, sav_line)
        c.setFillColorRGB(0, 0, 0)

        # ===== 6. IMMATRICULATION (gauche) + DATE/HEURE LIVRAISON (droite) =====
        y_imm = y_sav - sav_h - 4*mm
        imm_h = 10*mm
        half_w = (TABLE_W - 4*mm) / 2

        # Cadre gauche : IMMATRICULATION (label en haut-gauche, valeur centrée)
        rounded_box(LEFT, y_imm - imm_h, half_w, imm_h)
        c.setFillColorRGB(*GRAY_LBL)
        c.setFont('Helvetica', 7.5)
        c.drawString(LEFT + 3*mm, y_imm - 3.5*mm, 'IMMATRICULATION')
        c.setFillColorRGB(0, 0, 0)
        c.setFont('Helvetica-Bold', 11)
        c.drawCentredString(LEFT + half_w / 2, y_imm - 7.5*mm, (b.get('immatriculation') or '').strip())

        # Cadre droit : DATE / HEURE DE LIVRAISON
        rounded_box(LEFT + half_w + 4*mm, y_imm - imm_h, half_w, imm_h)
        c.setFillColorRGB(*GRAY_LBL)
        c.setFont('Helvetica', 7.5)
        c.drawString(LEFT + half_w + 4*mm + 3*mm, y_imm - 3.5*mm, 'DATE / HEURE DE LIVRAISON')
        c.setFillColorRGB(0, 0, 0)
        c.setFont('Helvetica-Bold', 11)
        c.drawCentredString(LEFT + half_w + 4*mm + half_w / 2, y_imm - 7.5*mm, (b.get('livraison') or '').strip())

        # ===== 7. TABLEAU (cadre arrondi, N lignes équi-réparties qui tiennent en 1 page) =====
        y_tab = y_imm - imm_h - 5*mm
        cols = [
            ('fournisseur', 'Fournisseur',  TABLE_W * 0.42),
            ('nbre_pal',    'Nbre DE PAL',  TABLE_W * 0.13),
            ('nbre_colis',  'Nbre DE COLIS', TABLE_W * 0.14),
            ('ref',         'Commentaire',  0),
        ]
        used = sum(w for _, _, w in cols if w > 0)
        cols[3] = (cols[3][0], cols[3][1], TABLE_W - used)

        # ---- Calcul du bas du tableau pour TOUT tenir sur 1 page ----
        # Layout fixe en bas (mm) : TOTAL/TEMP (9) + 5 + IMPORTANT (22) + 5 + SIGNATURES (22) + 15 marge
        BOTTOM_RESERVED = 9*mm + 5*mm + 22*mm + 5*mm + 22*mm + 15*mm  # = 78 mm
        max_table_h = y_tab - BOTTOM_RESERVED
        if max_table_h < 30*mm:
            max_table_h = 30*mm

        header_h = 8*mm
        n_items = max(1, len(items))
        # Hauteur naturelle d'une ligne (compacte si peu d'items, large si beaucoup)
        natural_row_h = 14*mm
        natural_table_h = header_h + n_items * natural_row_h

        if natural_table_h <= max_table_h:
            # Le tableau a sa taille naturelle (compact)
            total_table_h = natural_table_h
            row_h = natural_row_h
        else:
            # Sinon on remplit toute la place dispo et on compresse les lignes
            total_table_h = max_table_h
            row_h = (total_table_h - header_h) / n_items

        # ---- Cadre arrondi extérieur du tableau ----
        rounded_box(LEFT, y_tab - total_table_h, TABLE_W, total_table_h, radius=4*mm, stroke_w=0.9)

        # ---- Header (fond bleu très clair) ----
        # On dessine le fond du header en évitant les coins (inset)
        c.setFillColorRGB(0.93, 0.95, 1.0)
        c.rect(LEFT + 1, y_tab - header_h, TABLE_W - 2, header_h - 1, fill=1, stroke=0)
        # ligne horizontale sous le header
        c.setStrokeColorRGB(*NAVY)
        c.setLineWidth(0.6)
        c.line(LEFT, y_tab - header_h, LEFT + TABLE_W, y_tab - header_h)
        # séparateurs verticaux du header
        x = LEFT
        for _, _, w in cols[:-1]:
            x += w
            c.line(x, y_tab, x, y_tab - header_h)
        # textes du header
        c.setFillColorRGB(0, 0, 0)
        c.setFont('Helvetica-Bold', 10)
        x = LEFT
        for _, title, w in cols:
            c.drawCentredString(x + w / 2, y_tab - header_h + 2.5*mm, title)
            x += w

        # ---- Lignes du corps (équi-réparties) ----
        c.setStrokeColorRGB(*NAVY)
        c.setLineWidth(0.5)
        y_cursor = y_tab - header_h

        # Séparateurs verticaux qui traversent tout le corps
        # (légèrement en retrait du bas pour préserver le coin arrondi)
        x = LEFT
        body_bottom = y_tab - total_table_h + 1*mm
        for _, _, w in cols[:-1]:
            x += w
            c.line(x, y_cursor, x, body_bottom)

        # Lignes horizontales + contenu
        # Taille de police adaptée si beaucoup de lignes
        base_font = 10
        if row_h < 8*mm: base_font = 8
        if row_h < 6*mm: base_font = 7
        if row_h < 4.5*mm: base_font = 6

        for i in range(n_items):
            row_top = y_cursor - i * row_h
            row_bottom = row_top - row_h
            # ligne horizontale séparatrice (sauf après la dernière)
            if i > 0:
                c.setStrokeColorRGB(*NAVY)
                c.setLineWidth(0.3)
                c.line(LEFT, row_top, LEFT + TABLE_W, row_top)

            if i < len(items):
                it = items[i]
                # Fournisseur
                c.setFillColorRGB(0, 0, 0)
                c.setFont('Helvetica', base_font)
                f_lines = _wrap_text(it.get('fournisseur') or '', cols[0][2] - 4*mm, 'Helvetica', base_font)
                # commentaire wrap
                r_lines = _wrap_text(it.get('ref') or '', cols[3][2] - 4*mm, 'Helvetica', base_font - 1)

                # Fournisseur : centré verticalement
                f_block_h = len(f_lines) * (base_font + 2) * 0.353*mm
                ty = row_top - (row_h - f_block_h) / 2 - (base_font * 0.353*mm)
                for line in f_lines:
                    if ty < row_bottom + 1*mm: break
                    c.drawString(LEFT + 3*mm, ty, line)
                    ty -= (base_font + 2) * 0.353*mm

                # Pal / Colis (centrés)
                xp = LEFT + cols[0][2]
                c.drawCentredString(xp + cols[1][2] / 2, row_top - row_h / 2 - 1, str(it.get('nbre_pal') or 0))
                xp += cols[1][2]
                c.drawCentredString(xp + cols[2][2] / 2, row_top - row_h / 2 - 1, str(it.get('nbre_colis') or 0))

                # Commentaire
                xp += cols[2][2]
                c.setFont('Helvetica', max(6, base_font - 1))
                r_block_h = len(r_lines) * (base_font + 1) * 0.353*mm
                ty = row_top - (row_h - r_block_h) / 2 - ((base_font - 1) * 0.353*mm)
                for line in r_lines:
                    if ty < row_bottom + 1*mm: break
                    c.drawString(xp + 3*mm, ty, line)
                    ty -= (base_font + 1) * 0.353*mm

        # Re-trace le contour arrondi par-dessus tout, pour que les coins restent bien arrondis
        # (les fills/lignes internes peuvent avoir recouvert légèrement les coins)
        c.setStrokeColorRGB(*NAVY)
        c.setLineWidth(0.9)
        c.roundRect(LEFT, y_tab - total_table_h, TABLE_W, total_table_h, 4*mm, fill=0, stroke=1)

        # Le bas du tableau est à y_tab - total_table_h
        y_tab = y_tab - total_table_h

        # ===== 8. TOTAL (gauche) + TEMPERATURE (droite) =====
        y_tot = y_tab - 5*mm
        tot_h = 9*mm
        rounded_box(LEFT, y_tot - tot_h, half_w, tot_h)
        total_pal = sum(int(i.get('nbre_pal') or 0) for i in items)
        total_col = sum(int(i.get('nbre_colis') or 0) for i in items)
        c.setFont('Helvetica-Bold', 10)
        c.setFillColorRGB(0, 0, 0)
        c.drawString(LEFT + 4*mm, y_tot - 5.5*mm, f"TOTAL : PAL={total_pal}  |  COLIS={total_col}")

        rounded_box(LEFT + half_w + 4*mm, y_tot - tot_h, half_w, tot_h)
        temp = (b.get('temperature') or '').strip() or temperature_default
        c.drawString(LEFT + half_w + 4*mm + 4*mm, y_tot - 5.5*mm,
                     f"TEMPERATURE : {temp}" if temp else 'TEMPERATURE :')

        # ===== 9. IMPORTANT =====
        y_imp = y_tot - tot_h - 5*mm
        imp_h = 22*mm
        rounded_box(LEFT, y_imp - imp_h, TABLE_W, imp_h)
        c.setFont('Helvetica-Bold', 10)
        c.setFillColorRGB(0, 0, 0)
        c.drawString(LEFT + 4*mm, y_imp - 5*mm, 'IMPORTANT')
        c.setFont('Helvetica', 8.5)
        c.setFillColorRGB(*GRAY_LBL)
        imp_lines = _wrap_text(important_text, TABLE_W - 8*mm, 'Helvetica', 8.5)
        ty = y_imp - 9*mm
        for line in imp_lines:
            if ty < y_imp - imp_h + 2*mm: break
            c.drawString(LEFT + 4*mm, ty, line)
            ty -= 3.8*mm
        c.setFillColorRGB(0, 0, 0)

        # ===== 10. SIGNATURES (3 cadres côte à côte) =====
        y_sig = y_imp - imp_h - 5*mm
        sig_h = 22*mm
        sig_w = (TABLE_W - 8*mm) / 3
        sigs = [
            ('expéditeur', 'sender'),
            ('transporteur', 'carrier'),
            ('destinataire', 'consignee'),
        ]
        for i, (fr, en) in enumerate(sigs):
            xs = LEFT + i * (sig_w + 4*mm)
            rounded_box(xs, y_sig - sig_h, sig_w, sig_h)
            c.setFillColorRGB(*GRAY_LBL)
            c.setFont('Helvetica', 7.5)
            c.drawString(xs + 3*mm, y_sig - 4*mm, f'Signature et timbre du {fr}')
            c.drawString(xs + 3*mm, y_sig - 7.5*mm, f'Signature and stamp of the {en}')
            c.setFillColorRGB(0, 0, 0)

    if not bords:
        # Page vide informative
        c.setFont('Helvetica', 12)
        c.setFillColorRGB(*PDF_STYLE['muted'])
        c.drawCentredString(page_w / 2, page_h / 2, 'Aucun bordereau trouvé.')
    else:
        for i, b in enumerate(bords):
            if i > 0:
                c.showPage()
            draw_bordereau(b, items_by_bord.get(b['id'], []))

    c.showPage()
    c.save()
    pdf = buf.getvalue()
    if bid is not None:
        fname = f"bordereau_{bid}_{datetime.now().strftime('%Y%m%d')}.pdf"
    else:
        fname = f"bordereaux_{datetime.now().strftime('%Y%m%d_%H%M')}.pdf"
    from fastapi.responses import Response
    return Response(content=pdf, media_type='application/pdf', headers={
        'Content-Disposition': f'attachment; filename="{fname}"',
    })

@api_router.post("/auth/sso")
async def sso_exchange(data: SSOIn):
    try:
        payload = pyjwt.decode(
            data.token,
            SSO_SECRET,
            algorithms=[JWT_ALG],
            options={'require': ['exp', 'iat']},
        )
    except pyjwt.ExpiredSignatureError:
        raise HTTPException(status_code=401, detail="Lien SSO expiré")
    except Exception:
        raise HTTPException(status_code=401, detail="Token SSO invalide")

    if not payload.get('sso'):
        raise HTTPException(status_code=401, detail="Token SSO invalide")

    # Reject tokens that were issued too long ago (defense in depth even if exp is set)
    try:
        iat = int(payload.get('iat'))
    except Exception:
        raise HTTPException(status_code=401, detail="Token SSO invalide")
    if int(datetime.now(timezone.utc).timestamp()) - iat > SSO_MAX_AGE_SECONDS:
        raise HTTPException(status_code=401, detail="Lien SSO expiré")

    uid = payload.get('uid')
    if not isinstance(uid, int):
        try:
            uid = int(uid)
        except Exception:
            raise HTTPException(status_code=401, detail="Token SSO invalide")

    user = await fetch_one("SELECT * FROM users WHERE id=%s", (uid,))
    if not user:
        raise HTTPException(status_code=404, detail="Utilisateur introuvable")
    if effective_role(user) not in MYTRUCK_ROLES:
        raise HTTPException(status_code=403, detail="Ce compte n'a pas accès à My Truck")
    if user.get('is_active') == 0:
        raise HTTPException(status_code=403, detail="Compte désactivé")

    return {
        "token": create_token(user['id']),
        "user": user_public(user),
    }

# Optional URL of the GP partner site. Falls back to the production domain
# the user mentioned. Override via the GP_SSO_URL environment variable.
GP_BASE_URL = os.environ.get('GP_BASE_URL', 'https://www.gp-eff.fr').rstrip('/')
GP_SSO_PATH = os.environ.get('GP_SSO_PATH', '/sso_mytruck.php')

@api_router.post("/auth/sso-gp")
async def sso_to_gp(user=Depends(get_current_user)):
    """
    Outbound SSO: My Truck → GP.
    Generates a short-lived (90 s) JWT signed with SSO_SECRET and returns
    the full URL the frontend should redirect the user to.
    """
    now = int(datetime.now(timezone.utc).timestamp())
    payload = {
        "sso": True,
        "from": "mytruck",
        "uid": int(user['id']),
        "iat": now,
        "exp": now + SSO_MAX_AGE_SECONDS,
    }
    token = pyjwt.encode(payload, SSO_SECRET, algorithm=JWT_ALG)
    if isinstance(token, bytes):
        token = token.decode('utf-8')
    return {
        "url": f"{GP_BASE_URL}{GP_SSO_PATH}?t={token}",
        "expires_in": SSO_MAX_AGE_SECONDS,
    }

# --- Users (bureau only) ---
@api_router.get("/users")
async def list_users(grantable: int = 0, user=Depends(require_bureau)):
    """List users.
    - Default: only users who currently have My Truck access (effective role set).
    - `?grantable=1`: list all users in DB (so bureau can grant My Truck access to a GP v3 admin).
    """
    rows = await fetch_all(
        "SELECT id, username, fullname, role, mytruck_role, is_active, must_change_password, created_at, "
        "email, email_xdock, service_shift, xdock_name "
        "FROM users ORDER BY username ASC"
    )
    out = [user_public(r) for r in rows]
    if not grantable:
        out = [u for u in out if u['role'] in MYTRUCK_ROLES]
    # Sort: bureau first, then cariste, then no-access
    rank = {'bureau': 0, 'cariste': 1, None: 2}
    out.sort(key=lambda u: (rank.get(u['role'], 3), (u['username'] or '').lower()))
    return out

@api_router.post("/users")
async def create_user(data: UserCreate, user=Depends(require_bureau)):
    if not PASSWORD_RE.match(data.password):
        raise HTTPException(status_code=400, detail="Le code doit contenir exactement 6 chiffres")
    uname = normalize_username(data.username)
    if not uname:
        raise HTTPException(status_code=400, detail="Identifiant requis")
    existing = await fetch_one(
        "SELECT id FROM users WHERE LOWER(username)=%s", (uname,)
    )
    if existing:
        raise HTTPException(status_code=400, detail="Cet identifiant existe déjà")
    shift = (data.service_shift or '').strip().upper() or None
    if shift and shift not in ('MATIN', 'JOURNEE', 'APRES_MIDI', 'NUIT'):
        shift = None
    new_id = await execute_returning_id(
        "INSERT INTO users (username, fullname, password_hash, role, email, email_xdock, service_shift, xdock_name, is_active, must_change_password, created_at) "
        "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 1, 1, %s)",
        (
            data.username.strip(),
            (data.fullname or '').strip(),
            hash_password(data.password),
            data.role,
            (data.email or '').strip() or None,
            (data.email_xdock or '').strip() or None,
            shift,
            (data.xdock_name or '').strip() or None,
            now_dt(),
        ),
    )
    created = await fetch_one("SELECT * FROM users WHERE id=%s", (new_id,))
    return user_public(created)

@api_router.patch("/users/{user_id}")
async def update_user(user_id: int, data: UserUpdate, user=Depends(require_bureau)):
    target = await fetch_one("SELECT * FROM users WHERE id=%s", (user_id,))
    if not target:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")

    target_gp_native = (target.get('role') or '') in MYTRUCK_ROLES

    sets, params = [], []
    if data.username is not None:
        new_un = data.username.strip()
        if not new_un:
            raise HTTPException(status_code=400, detail="Identifiant requis")
        if normalize_username(new_un) != normalize_username(target.get('username') or ''):
            collision = await fetch_one(
                "SELECT id FROM users WHERE LOWER(username)=%s AND id<>%s",
                (normalize_username(new_un), user_id),
            )
            if collision:
                raise HTTPException(status_code=400, detail="Cet identifiant existe déjà")
        sets += ["username=%s"]
        params += [new_un]
    if data.fullname is not None:
        sets += ["fullname=%s"]
        params += [data.fullname.strip()]
    if data.password is not None and data.password != "":
        if not PASSWORD_RE.match(data.password):
            raise HTTPException(status_code=400, detail="Le code doit contenir exactement 6 chiffres")
        # When bureau resets a password, default to force-change unless explicitly disabled
        force = True if data.must_change_password is None else bool(data.must_change_password)
        sets += ["password_hash=%s", "must_change_password=%s"]
        params += [hash_password(data.password), 1 if force else 0]
    elif data.must_change_password is not None:
        # Allow toggling the flag without changing the password
        sets += ["must_change_password=%s"]
        params += [1 if data.must_change_password else 0]
    # `role` field updates the effective My Truck role.
    # - For pure My Truck users (role in cariste/bureau), write to `users.role`.
    # - For GP v3 users (role='admin' or other), write to `users.mytruck_role` (don't touch GP v3 role).
    if data.role is not None:
        if user_id == user['id'] and data.role != 'bureau':
            raise HTTPException(status_code=400, detail="Vous ne pouvez pas changer votre propre rôle")
        if target_gp_native:
            sets += ["role=%s"]
            params += [data.role]
        else:
            sets += ["mytruck_role=%s"]
            params += [data.role]
    # Explicit mytruck_role field (frontend can send null to revoke access)
    if 'mytruck_role' in data.model_fields_set:
        if user_id == user['id'] and data.mytruck_role is None:
            raise HTTPException(status_code=400, detail="Vous ne pouvez pas révoquer votre propre accès My Truck")
        sets += ["mytruck_role=%s"]
        params += [data.mytruck_role]  # None ⇒ NULL in DB (aiomysql converts)
    if data.is_active is not None:
        sets += ["is_active=%s"]
        params += [1 if data.is_active else 0]
    if data.email is not None:
        v = (data.email or '').strip()
        sets += ["email=%s"]
        params += [v or None]
    if data.email_xdock is not None:
        v = (data.email_xdock or '').strip()
        sets += ["email_xdock=%s"]
        params += [v or None]
    if data.service_shift is not None:
        v = (data.service_shift or '').strip().upper()
        if v and v not in ('MATIN', 'JOURNEE', 'APRES_MIDI', 'NUIT'):
            v = ''
        sets += ["service_shift=%s"]
        params += [v or None]
    if data.xdock_name is not None:
        v = (data.xdock_name or '').strip()
        sets += ["xdock_name=%s"]
        params += [v or None]
    if data.can_manage_xdock_scripts is not None:
        # Réservé au super-admin (Mathieu.u) — pour donner le droit à n'importe quel user
        if (user.get('username') or '').lower() != SUPER_ADMIN_USERNAME:
            raise HTTPException(status_code=403, detail="Seul le super-admin peut accorder ce droit")
        sets += ["can_manage_xdock_scripts=%s"]
        params += [1 if data.can_manage_xdock_scripts else 0]
    if data.can_manage_folder_email is not None:
        if (user.get('username') or '').lower() != SUPER_ADMIN_USERNAME:
            raise HTTPException(status_code=403, detail="Seul le super-admin peut accorder ce droit")
        sets += ["can_manage_folder_email=%s"]
        params += [1 if data.can_manage_folder_email else 0]

    if sets:
        params.append(user_id)
        await execute(f"UPDATE users SET {', '.join(sets)} WHERE id=%s", tuple(params))

    updated = await fetch_one("SELECT * FROM users WHERE id=%s", (user_id,))
    return user_public(updated)

@api_router.delete("/users/{user_id}")
async def delete_user(user_id: int, user=Depends(require_bureau)):
    if user_id == user['id']:
        raise HTTPException(status_code=400, detail="Impossible de supprimer son propre compte")
    target = await fetch_one("SELECT id, role, mytruck_role FROM users WHERE id=%s", (user_id,))
    if not target:
        raise HTTPException(status_code=404, detail="Utilisateur non trouvé")
    if (target.get('role') or '') in MYTRUCK_ROLES:
        # Pure My Truck user — full delete (cascades to folders via ON DELETE? No, we don't have CASCADE on cariste_id)
        await execute("DELETE FROM users WHERE id=%s", (user_id,))
        return {"ok": True, "action": "deleted"}
    # GP v3 native user — only revoke My Truck access, keep the row for GP v3
    await execute("UPDATE users SET mytruck_role=NULL WHERE id=%s", (user_id,))
    return {"ok": True, "action": "revoked"}


# --- XDock Plus integration (Edge extension) ---
@api_router.get("/me/xdock")
async def me_xdock(user=Depends(get_current_user)):
    """Return the current user's XDock identity for the Edge extension bridge.
    Mirrors the legacy /api/xdock-user.php endpoint."""
    row = await fetch_one(
        "SELECT xdock_name FROM users WHERE id=%s",
        (user['id'],),
    )
    if not row or not (row.get('xdock_name') or '').strip():
        return {
            "success": False,
            "message": "Nom XDock non configuré",
            "xdock_name": "",
        }
    return {
        "success": True,
        "xdock_name": row.get('xdock_name') or '',
    }


# --- XDock Plus server-side cache (extension Edge → backend → frontend) ---
XDOCKPLUS_API_KEY = os.environ.get("XDOCKPLUS_API_KEY", "")


class XDockCurrentTaskIn(BaseModel):
    xdock_name: str
    tour_number: Optional[str] = ""
    tour_type: Optional[str] = ""
    tracteur: Optional[str] = ""
    remorque: Optional[str] = ""


@api_router.post("/xdock/current-task/update")
async def xdock_current_task_update(data: XDockCurrentTaskIn, request: Request):
    """Called by the Edge XDock Plus extension to push the cariste's current
    truck/task to the server cache. Protected by a shared API key header."""
    if not XDOCKPLUS_API_KEY:
        raise HTTPException(status_code=503, detail="XDock integration not configured")
    if request.headers.get("X-XDockPlus-Key") != XDOCKPLUS_API_KEY:
        raise HTTPException(status_code=403, detail="Forbidden")
    name = (data.xdock_name or '').strip()
    if not name:
        raise HTTPException(status_code=400, detail="xdock_name requis")
    await execute(
        "INSERT INTO xdock_current_tasks "
        "(xdock_name, tour_number, tour_type, tracteur, remorque, last_seen, updated_at) "
        "VALUES (%s, %s, %s, %s, %s, %s, %s) "
        "ON DUPLICATE KEY UPDATE "
        "tour_number=VALUES(tour_number), tour_type=VALUES(tour_type), "
        "tracteur=VALUES(tracteur), remorque=VALUES(remorque), "
        "last_seen=VALUES(last_seen), updated_at=VALUES(updated_at)",
        (
            name,
            (data.tour_number or '').strip() or None,
            (data.tour_type or '').strip().upper() or None,
            (data.tracteur or '').strip().upper() or None,
            (data.remorque or '').strip().upper() or None,
            now_dt(),
            now_dt(),
        ),
    )
    return {"ok": True}


class XDockBulkIn(BaseModel):
    trucks: List[XDockCurrentTaskIn]


@api_router.post("/xdock/current-task/upsert-bulk")
async def xdock_current_task_upsert_bulk(data: XDockBulkIn, request: Request):
    """Called every ~30s by the XDock Plus extension background service worker.
    Receives the FULL list of trucks currently visible in XDock+ "En cours" page.
    Each row is upserted by `xdock_name` so when a cariste switches from truck A
    to truck B on the same dock, B simply overwrites A (single row per dock)."""
    if not XDOCKPLUS_API_KEY:
        raise HTTPException(status_code=503, detail="XDock integration not configured")
    if request.headers.get("X-XDockPlus-Key") != XDOCKPLUS_API_KEY:
        raise HTTPException(status_code=403, detail="Forbidden")
    if not data.trucks:
        return {"ok": True, "upserted": 0}

    now = now_dt()
    upserted = 0
    seen_names: List[str] = []
    for t in data.trucks:
        name = (t.xdock_name or '').strip()
        if not name:
            continue
        await execute(
            "INSERT INTO xdock_current_tasks "
            "(xdock_name, tour_number, tour_type, tracteur, remorque, last_seen, updated_at) "
            "VALUES (%s, %s, %s, %s, %s, %s, %s) "
            "ON DUPLICATE KEY UPDATE "
            "tour_number=VALUES(tour_number), tour_type=VALUES(tour_type), "
            "tracteur=VALUES(tracteur), remorque=VALUES(remorque), "
            "last_seen=VALUES(last_seen), updated_at=VALUES(updated_at)",
            (
                name,
                (t.tour_number or '').strip() or None,
                (t.tour_type or '').strip().upper() or None,
                (t.tracteur or '').strip().upper() or None,
                (t.remorque or '').strip().upper() or None,
                now,
                now,
            ),
        )
        upserted += 1
        seen_names.append(name)

    # Purge stale rows: any dock that wasn't in this bulk push for >2 minutes
    # is considered free → we wipe its content (but keep the row so the cariste
    # gets an empty result instead of a stale truck).
    if seen_names:
        placeholders = ",".join(["%s"] * len(seen_names))
        await execute(
            f"UPDATE xdock_current_tasks SET "
            f"  tour_number=NULL, tour_type=NULL, tracteur=NULL, remorque=NULL, "
            f"  updated_at=%s "
            f"WHERE xdock_name NOT IN ({placeholders}) "
            f"  AND last_seen < %s",
            (now, *seen_names, now - timedelta(minutes=2)),
        )
    return {"ok": True, "upserted": upserted}


@api_router.get("/xdock/current-task/by-name")
async def xdock_current_task_by_name(xdock_name: str, request: Request):
    """Lookup by xdock_name with X-XDockPlus-Key auth (no JWT required).
    Useful for diagnostics and for the cariste app if it ever needs to query
    without being logged in (e.g. on a public TV dashboard)."""
    if not XDOCKPLUS_API_KEY:
        raise HTTPException(status_code=503, detail="XDock integration not configured")
    if request.headers.get("X-XDockPlus-Key") != XDOCKPLUS_API_KEY:
        raise HTTPException(status_code=403, detail="Forbidden")
    name = (xdock_name or '').strip()
    if not name:
        raise HTTPException(status_code=400, detail="xdock_name requis")
    row = await fetch_one(
        "SELECT tour_number, tour_type, tracteur, remorque, last_seen "
        "FROM xdock_current_tasks WHERE xdock_name=%s LIMIT 1",
        (name,),
    )
    if not row or not row.get("tracteur"):
        return {"ok": True, "found": False}
    return {
        "ok": True,
        "found": True,
        "data": {
            "tour_number": row.get("tour_number") or "",
            "tour_type": row.get("tour_type") or "",
            "tracteur": row.get("tracteur") or "",
            "remorque": row.get("remorque") or "",
            "last_seen": dt_to_iso(row.get("last_seen")) if row.get("last_seen") else None,
        },
    }


@api_router.get("/xdock/current-task/me")
async def xdock_current_task_me(user=Depends(get_current_user)):
    """Frontend calls this when opening 'Nouveau dossier' to fetch the cariste's
    current truck/task from the server cache (fed by the Edge extension)."""
    xdock_name = (user.get("xdock_name") or "").strip()
    if not xdock_name:
        return {"ok": True, "found": False, "error": "xdock_name_missing"}
    row = await fetch_one(
        "SELECT tour_number, tour_type, tracteur, remorque, last_seen "
        "FROM xdock_current_tasks WHERE xdock_name=%s LIMIT 1",
        (xdock_name,),
    )
    if not row:
        return {"ok": True, "found": False}
    return {
        "ok": True,
        "found": True,
        "data": {
            "tour_number": row.get("tour_number") or "",
            "tour_type": row.get("tour_type") or "",
            "tracteur": row.get("tracteur") or "",
            "remorque": row.get("remorque") or "",
            "last_seen": dt_to_iso(row.get("last_seen")) if row.get("last_seen") else None,
        },
    }


# ===== XDock Plus → Reliquats (extension Edge) =====
# Public endpoints for the XDock Plus browser extension to push reliquats
# directly from XDock pages. Authenticated by shared header X-XDockPlus-Key.

def _require_xdock_key(request: Request):
    if not XDOCKPLUS_API_KEY:
        raise HTTPException(status_code=503, detail="XDock integration not configured")
    if request.headers.get("X-XDockPlus-Key") != XDOCKPLUS_API_KEY:
        raise HTTPException(status_code=403, detail="Forbidden")


class XDPReliquatIn(BaseModel):
    fournisseur: Optional[str] = ""
    gtin: Optional[str] = ""
    sscc: Optional[str] = ""
    klstb: Optional[str] = ""
    motif: Optional[str] = ""
    motifs: Optional[List[str]] = None
    zone: Optional[str] = ""
    commentaire: Optional[str] = ""
    no_sscc: Optional[bool] = False
    created_by_username: Optional[str] = ""
    nombre_palettes: Optional[int] = 1
    produit: Optional[str] = ""


@api_router.get("/gp/xdock-plus/motifs")
async def xdp_list_motifs(request: Request):
    _require_xdock_key(request)
    rows = await fetch_all("SELECT label FROM motifs ORDER BY label ASC")
    return {"ok": True, "motifs": [r['label'] for r in rows]}


@api_router.get("/gp/xdock-plus/users")
async def xdp_list_users(request: Request):
    """Return labels for the extension's dropdown (fullname si dispo, sinon username).
    Ne renvoie QUE les utilisateurs effective_role='bureau' (ceux qui valident
    les reliquats). Exclut explicitement les caristes et les comptes désactivés."""
    _require_xdock_key(request)
    # effective_role = mytruck_role if set, sinon role
    rows = await fetch_all(
        "SELECT username, fullname FROM users "
        "WHERE COALESCE(NULLIF(mytruck_role, ''), role) = 'bureau' "
        "AND COALESCE(is_active, 1) <> 0 "
        "ORDER BY COALESCE(NULLIF(fullname, ''), username) ASC"
    )
    labels = []
    seen = set()
    for r in rows:
        lbl = (r.get('fullname') or '').strip() or (r.get('username') or '').strip()
        if lbl and lbl.lower() not in seen:
            seen.add(lbl.lower())
            labels.append(lbl)
    return {"ok": True, "users": labels}


@api_router.get("/gp/xdock-plus/resolve-user")
async def xdp_resolve_user(email: str, request: Request):
    """Map an XDock email (users.email_xdock) → user label (fullname || username).
    Used by the extension to auto-fill the 'Nom' dropdown."""
    _require_xdock_key(request)
    em = (email or '').strip().lower()
    if not em:
        return {"ok": True, "username": ""}
    row = await fetch_one(
        "SELECT username, fullname FROM users "
        "WHERE LOWER(email_xdock)=%s "
        "AND COALESCE(NULLIF(mytruck_role, ''), role) = 'bureau' "
        "AND COALESCE(is_active, 1) <> 0 LIMIT 1",
        (em,),
    )
    if not row:
        return {"ok": True, "username": ""}
    label = (row.get('fullname') or '').strip() or (row.get('username') or '').strip()
    return {"ok": True, "username": label}


@api_router.post("/gp/xdock-plus/reliquat")
async def xdp_create_reliquat(data: XDPReliquatIn, request: Request):
    """Create a palette (reliquat) from the XDock Plus extension.
    - Anti-doublon: refuses with 409 if SSCC already exists (when not no_sscc).
    - Resolves the cariste via `created_by_username` (matched against
      users.fullname first then users.username); falls back to NULL if unknown.
    - motifs[] joined with ' - ' for storage in palettes.motif.
    """
    _require_xdock_key(request)

    sscc_raw = (data.sscc or '').strip()
    sscc = sscc_raw or None
    if data.no_sscc:
        sscc = None  # SSCC explicitement absent
    elif sscc:
        dup = await fetch_one("SELECT id FROM palettes WHERE sscc=%s LIMIT 1", (sscc,))
        if dup:
            raise HTTPException(status_code=409, detail=f"Le SSCC {sscc} existe déjà dans la base.")

    # Build motif string (prefer motifs[] list if provided)
    motifs_list = [m.strip() for m in (data.motifs or []) if (m or '').strip()]
    motif_str = ' - '.join(motifs_list) if motifs_list else (data.motif or '').strip()
    if not motif_str:
        raise HTTPException(status_code=400, detail="motif requis")

    # Resolve user by display label (fullname) then username
    created_by = None
    label = (data.created_by_username or '').strip()
    if label:
        u = await fetch_one(
            "SELECT id FROM users "
            "WHERE (LOWER(fullname)=%s OR LOWER(username)=%s) "
            "AND COALESCE(is_active, 1) <> 0 LIMIT 1",
            (label.lower(), label.lower()),
        )
        if u:
            created_by = u['id']

    now = now_dt()
    pid = await execute_returning_id(
        "INSERT INTO palettes (nombre_palettes, produit, fournisseur, klstb, sscc, gtin, motif, zone, commentaire, created_by, created_at, archived) "
        "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,0)",
        (
            max(1, int(data.nombre_palettes or 1)),
            (data.produit or '').strip(),
            (data.fournisseur or '').strip(),
            (data.klstb or '').strip(),
            sscc,
            (data.gtin or '').strip(),
            motif_str,
            (data.zone or '').strip(),
            (data.commentaire or '').strip(),
            created_by,
            now,
        ),
    )
    return {"ok": True, "id": pid, "sscc": sscc or "", "created_by_resolved": bool(created_by)}


# --- Folders ---
@api_router.get("/folders")
async def list_folders(date: Optional[str] = None, user=Depends(get_current_user)):
    where = []
    params: List[Any] = []
    if user['role'] == 'cariste':
        where.append("cariste_id=%s")
        params.append(user['id'])
    if date:
        where.append("date_key=%s")
        params.append(date)
    sql = "SELECT * FROM folders"
    if where:
        sql += " WHERE " + " AND ".join(where)
    sql += " ORDER BY created_at DESC LIMIT 2000"
    rows = await fetch_all(sql, tuple(params))
    out = []
    for f in rows:
        preview = await fetch_one(
            "SELECT data FROM folder_photos WHERE folder_id=%s ORDER BY position ASC LIMIT 1",
            (f['id'],),
        )
        count_row = await fetch_one(
            "SELECT COUNT(*) AS c FROM folder_photos WHERE folder_id=%s",
            (f['id'],),
        )
        out.append({
            "id": f['id'],
            "plate": f['plate'],
            "problem": f['problem'],
            "status": f['status'],
            "cariste_id": f['cariste_id'],
            "cariste_name": f['cariste_name'] or '',
            "assigned_to_id": f.get('assigned_to_id'),
            "assigned_to_name": f.get('assigned_to_name'),
            "created_at": dt_to_iso(f['created_at']),
            "updated_at": dt_to_iso(f['updated_at']),
            "date_key": f['date_key'],
            "numero_em_sm": f.get('numero_em_sm') or '',
            "type_em_sm": f.get('type_em_sm') or '',
            "immatriculation_remorque": f.get('immatriculation_remorque') or '',
            "preview": preview['data'] if preview else None,
            "photos_count": int(count_row['c']) if count_row else 0,
        })
    return out

@api_router.get("/folders/stats")
async def folder_stats(date: Optional[str] = None, user=Depends(get_current_user)):
    where = []
    params: List[Any] = []
    if user['role'] == 'cariste':
        where.append("cariste_id=%s")
        params.append(user['id'])
    if date:
        where.append("date_key=%s")
        params.append(date)
    base_where = (" WHERE " + " AND ".join(where)) if where else ""

    async def cnt(extra: Optional[tuple] = None) -> int:
        if extra is None:
            row = await fetch_one(f"SELECT COUNT(*) AS c FROM folders{base_where}", tuple(params))
        else:
            extra_clause, extra_param = extra
            sep = " AND " if where else " WHERE "
            row = await fetch_one(
                f"SELECT COUNT(*) AS c FROM folders{base_where}{sep}{extra_clause}",
                tuple(params) + (extra_param,),
            )
        return int(row['c']) if row else 0

    return {
        "total": await cnt(),
        "waiting": await cnt(("status=%s", "en_attente")),
        "in_progress": await cnt(("status=%s", "en_cours")),
        "closed": await cnt(("status=%s", "cloture")),
    }

@api_router.get("/folders/dates")
async def folders_dates(user=Depends(get_current_user)):
    if user['role'] == 'cariste':
        rows = await fetch_all(
            "SELECT DISTINCT date_key FROM folders WHERE cariste_id=%s",
            (user['id'],),
        )
    else:
        rows = await fetch_all("SELECT DISTINCT date_key FROM folders")
    return {"dates": [r['date_key'] for r in rows]}

@api_router.get("/folders/check-existing")
async def folders_check_existing(
    plate: Optional[str] = None,
    numero_em_sm: Optional[str] = None,
    user=Depends(get_current_user),
):
    """Check if an OPEN folder already exists for this plate (and/or tour number).
    Returns the existing folder if found so the frontend can propose to reopen/append
    instead of creating a duplicate."""
    plate_n = (plate or '').strip().upper()
    num_n = (numero_em_sm or '').strip()
    if not plate_n and not num_n:
        return {"found": False}

    where = ["status IN ('en_attente', 'en_cours')"]
    params = []
    if plate_n:
        where.append("UPPER(plate) = %s")
        params.append(plate_n)
    if num_n:
        where.append("(numero_em_sm = %s OR numero_em_sm IS NULL OR numero_em_sm = '')")
        params.append(num_n)
    row = await fetch_one(
        f"SELECT id, plate, problem, status, cariste_name, created_at, numero_em_sm, type_em_sm "
        f"FROM folders WHERE {' AND '.join(where)} ORDER BY created_at DESC LIMIT 1",
        tuple(params),
    )
    if not row:
        return {"found": False}
    return {
        "found": True,
        "folder": {
            "id": row['id'],
            "plate": row['plate'],
            "problem": (row['problem'] or '')[:150],
            "status": row['status'],
            "cariste_name": row['cariste_name'] or '',
            "created_at": dt_to_iso(row['created_at']),
            "numero_em_sm": row.get('numero_em_sm') or '',
            "type_em_sm": row.get('type_em_sm') or '',
        },
    }


@api_router.get("/folders/{folder_id}")
async def get_folder(folder_id: int, user=Depends(get_current_user)):
    folder = await load_folder_full(folder_id)
    if not folder:
        raise HTTPException(status_code=404, detail="Dossier non trouvé")
    if user['role'] == 'cariste' and folder['cariste_id'] != user['id']:
        raise HTTPException(status_code=403, detail="Accès refusé")
    return folder


@api_router.post("/folders")
async def create_folder(data: FolderCreate, user=Depends(get_current_user)):
    if not data.plate.strip():
        raise HTTPException(status_code=400, detail="Plaque requise")
    if not data.problem.strip():
        raise HTTPException(status_code=400, detail="Problème requis")
    if len(data.photos) > 30:
        raise HTTPException(status_code=400, detail="Maximum 30 photos")

    # Anti-duplicate: refuse if an OPEN folder already exists for the same plate
    plate_n = data.plate.strip().upper()
    existing = await fetch_one(
        "SELECT id, plate FROM folders WHERE UPPER(plate)=%s AND status IN ('en_attente','en_cours') ORDER BY created_at DESC LIMIT 1",
        (plate_n,),
    )
    if existing:
        raise HTTPException(
            status_code=409,
            detail={
                "code": "duplicate_open_folder",
                "message": f"Un dossier ouvert existe déjà pour le camion {existing['plate']}.",
                "folder_id": existing['id'],
            },
        )

    now = datetime.now(timezone.utc)
    iso = now.isoformat()
    # date_key = date LOCALE Paris (sinon dossier créé à 00h27 Paris finit au
    # 08/06 car UTC est encore à 22h27 la veille).
    # Fallback offset si zoneinfo/tzdata manque dans le conteneur.
    paris_now = None
    try:
        from zoneinfo import ZoneInfo
        paris_now = now.astimezone(ZoneInfo(os.environ.get('TZ', 'Europe/Paris')))
    except Exception:
        try:
            import pytz
            paris_now = now.astimezone(pytz.timezone('Europe/Paris'))
        except Exception:
            # Dernier recours : décalage manuel CEST/CET basé sur le mois
            m = now.month
            offset_hours = 2 if 3 < m < 11 else 1  # CEST entre avril et oct, CET sinon
            paris_now = now + timedelta(hours=offset_hours)
    date_key = paris_now.strftime("%Y-%m-%d")
    cariste_name = user.get('fullname') or user['username']

    fid = await execute_returning_id(
        "INSERT INTO folders (plate, problem, status, cariste_id, cariste_name, "
        "created_at, updated_at, date_key, numero_em_sm, type_em_sm, immatriculation_remorque) "
        "VALUES (%s, %s, 'en_attente', %s, %s, %s, %s, %s, %s, %s, %s)",
        (
            data.plate.strip().upper(), data.problem.strip(),
            user['id'], cariste_name, now_dt(), now_dt(), date_key,
            (data.numero_em_sm or '').strip() or None,
            (data.type_em_sm or '').strip().upper() or None,
            (data.immatriculation_remorque or '').strip().upper() or None,
        ),
    )
    label = f"Dossier créé par {cariste_name}"
    hist_id = await execute_returning_id(
        "INSERT INTO folder_history (folder_id, action, by_id, by_name, at, label) "
        "VALUES (%s, 'created', %s, %s, %s, %s)",
        (fid, user['id'], cariste_name, now_dt(), label),
    )
    await insert_photos(fid, data.photos, event_id=hist_id)

    folder = await load_folder_full(fid)
    notif = {
        "type": "new_folder",
        "at": iso,
        "folder": {
            "id": fid,
            "plate": folder['plate'],
            "problem": folder['problem'][:120],
            "cariste_name": cariste_name,
            "created_at": iso,
            "photos_count": len(data.photos),
        }
    }
    asyncio.create_task(manager.broadcast_bureau(notif))
    asyncio.create_task(push_to_bureau({
        "type": "new_folder",
        "title": f"Nouveau dossier · {folder['plate']}",
        "body": f"{folder['problem'][:120]}\npar {cariste_name}",
        "tag": f"folder-{fid}",
        "folder_id": fid,
        "url": f"/folders/{fid}",
    }))
    return folder

@api_router.patch("/folders/{folder_id}")
async def update_folder(folder_id: int, data: FolderUpdate, user=Depends(get_current_user)):
    folder = await load_folder_full(folder_id)
    if not folder:
        raise HTTPException(status_code=404, detail="Dossier non trouvé")
    if user['role'] == 'cariste' and folder['cariste_id'] != user['id']:
        raise HTTPException(status_code=403, detail="Accès refusé")

    changed = False
    if data.problem is not None and data.problem.strip() != folder.get('problem'):
        await execute("UPDATE folders SET problem=%s, updated_at=%s WHERE id=%s",
                      (data.problem.strip(), now_dt(), folder_id))
        changed = True
    if data.numero_em_sm is not None:
        v = (data.numero_em_sm or '').strip() or None
        if v != (folder.get('numero_em_sm') or None):
            await execute("UPDATE folders SET numero_em_sm=%s, updated_at=%s WHERE id=%s",
                          (v, now_dt(), folder_id))
            changed = True
    if data.type_em_sm is not None:
        v = (data.type_em_sm or '').strip().upper() or None
        if v != (folder.get('type_em_sm') or None):
            await execute("UPDATE folders SET type_em_sm=%s, updated_at=%s WHERE id=%s",
                          (v, now_dt(), folder_id))
            changed = True
    if data.immatriculation_remorque is not None:
        v = (data.immatriculation_remorque or '').strip().upper() or None
        if v != (folder.get('immatriculation_remorque') or None):
            await execute("UPDATE folders SET immatriculation_remorque=%s, updated_at=%s WHERE id=%s",
                          (v, now_dt(), folder_id))
            changed = True
    if data.photos is not None:
        if len(data.photos) > 30:
            raise HTTPException(status_code=400, detail="Maximum 30 photos")
        await execute("DELETE FROM folder_photos WHERE folder_id=%s", (folder_id,))
        await insert_photos(folder_id, data.photos)
        await execute("UPDATE folders SET updated_at=%s WHERE id=%s", (now_dt(), folder_id))
        changed = True

    if changed:
        full_name = user.get('fullname') or user['username']
        await execute(
            "INSERT INTO folder_history (folder_id, action, by_id, by_name, at, label) "
            "VALUES (%s, 'updated', %s, %s, %s, %s)",
            (folder_id, user['id'], full_name, now_dt(),
             f"Dossier mis à jour par {full_name}"),
        )

    return await load_folder_full(folder_id)

@api_router.patch("/folders/{folder_id}/status")
async def update_status(folder_id: int, data: StatusUpdate, user=Depends(get_current_user)):
    folder = await load_folder_full(folder_id)
    if not folder:
        raise HTTPException(status_code=404, detail="Dossier non trouvé")
    # Caristes can only reopen their own closed folders. Bureau can do everything.
    if user['role'] == 'cariste':
        if data.action != 'reopen':
            raise HTTPException(status_code=403, detail="Action réservée au bureau")
        if folder['cariste_id'] != user['id']:
            raise HTTPException(status_code=403, detail="Tu ne peux rouvrir que tes propres dossiers")
        if folder['status'] != 'cloture':
            raise HTTPException(status_code=400, detail="Ce dossier n'est pas clôturé")
    full_name = user.get('fullname') or user['username']
    if data.action == 'take':
        new_status, label = "en_cours", f"{full_name} traite le dossier"
        await execute(
            "UPDATE folders SET status=%s, assigned_to_id=%s, assigned_to_name=%s, updated_at=%s WHERE id=%s",
            (new_status, user['id'], full_name, now_dt(), folder_id),
        )
    elif data.action == 'close':
        new_status, label = "cloture", f"{full_name} a clôturé le dossier"
        await execute(
            "UPDATE folders SET status=%s, assigned_to_id=%s, assigned_to_name=%s, updated_at=%s WHERE id=%s",
            (new_status, user['id'], full_name, now_dt(), folder_id),
        )
    else:
        new_status, label = "en_attente", f"{full_name} a rouvert le dossier"
        await execute(
            "UPDATE folders SET status=%s, assigned_to_id=NULL, assigned_to_name=NULL, updated_at=%s WHERE id=%s",
            (new_status, now_dt(), folder_id),
        )
    await execute(
        "INSERT INTO folder_history (folder_id, action, by_id, by_name, at, label) "
        "VALUES (%s, %s, %s, %s, %s, %s)",
        (folder_id, data.action, user['id'], full_name, now_dt(), label),
    )
    updated = await load_folder_full(folder_id)
    notif = {
        "type": "status_change",
        "at": dt_to_iso(now_dt()),
        "folder": {
            "id": folder_id,
            "plate": folder['plate'],
            "status": new_status,
            "label": label,
            "by_name": full_name,
        }
    }
    # Notify the cariste owner about bureau actions; notify bureau about cariste reopen
    if user['role'] == 'bureau':
        asyncio.create_task(manager.send_to_user(folder['cariste_id'], notif))
        asyncio.create_task(push_to_user(folder['cariste_id'], {
            "type": "status_change",
            "title": f"Dossier {folder['plate']}",
            "body": label,
            "tag": f"status-{folder_id}",
            "folder_id": folder_id,
            "url": f"/folders/{folder_id}",
        }))
    else:  # cariste reopen → ping bureau
        asyncio.create_task(manager.broadcast_bureau(notif))
        asyncio.create_task(push_to_bureau({
            "type": "status_change",
            "title": f"Dossier ré-ouvert · {folder['plate']}",
            "body": label,
            "tag": f"reopen-{folder_id}",
            "folder_id": folder_id,
            "url": f"/folders/{folder_id}",
        }))
    return updated

@api_router.delete("/folders/{folder_id}")
async def delete_folder(folder_id: int, user=Depends(require_bureau)):
    n = await execute("DELETE FROM folders WHERE id=%s", (folder_id,))
    if n == 0:
        raise HTTPException(status_code=404, detail="Dossier non trouvé")
    return {"ok": True}

@api_router.post("/folders/{folder_id}/append")
async def append_folder(folder_id: int, data: FolderAppend, user=Depends(get_current_user)):
    folder = await load_folder_full(folder_id)
    if not folder:
        raise HTTPException(status_code=404, detail="Dossier non trouvé")
    if user['role'] == 'cariste' and folder['cariste_id'] != user['id']:
        raise HTTPException(status_code=403, detail="Accès refusé")

    text = (data.text or "").strip()
    new_photos = data.photos or []
    if not text and not new_photos:
        raise HTTPException(status_code=400, detail="Ajoutez du texte ou des photos")
    existing_count = len(folder['photos'])
    if existing_count + len(new_photos) > 30:
        raise HTTPException(status_code=400, detail="Maximum 30 photos au total")

    full_name = user.get('fullname') or user['username']

    # Build a clean label that contains the contributor name + summary + (optional) added text.
    # Text is stored in label using a unique separator so the frontend can split it cleanly,
    # instead of being mixed into folder.problem (which previously created the "tout mélangé" UX problem).
    SEP = "\n---TEXT---\n"
    if text and new_photos:
        summary = f"{full_name} a ajouté un commentaire et {len(new_photos)} photo(s)"
    elif text:
        summary = f"{full_name} a ajouté un commentaire"
    else:
        summary = f"{full_name} a ajouté {len(new_photos)} photo(s)"
    label = summary + (SEP + text if text else "")

    await execute("UPDATE folders SET updated_at=%s WHERE id=%s", (now_dt(), folder_id))

    hist_id = await execute_returning_id(
        "INSERT INTO folder_history (folder_id, action, by_id, by_name, at, label) "
        "VALUES (%s, 'appended', %s, %s, %s, %s)",
        (folder_id, user['id'], full_name, now_dt(), label),
    )

    if new_photos:
        await insert_photos(folder_id, new_photos, start_pos=existing_count, event_id=hist_id)

    updated = await load_folder_full(folder_id)

    if user['role'] == 'cariste':
        notif = {
            "type": "folder_appended",
            "at": dt_to_iso(now_dt()),
            "folder": {
                "id": folder_id,
                "plate": folder['plate'],
                "cariste_name": full_name,
                "label": label,
            }
        }
        asyncio.create_task(manager.broadcast_bureau(notif))
        asyncio.create_task(push_to_bureau({
            "type": "folder_appended",
            "title": f"Mise à jour · {folder['plate']}",
            "body": label,
            "tag": f"appended-{folder_id}",
            "folder_id": folder_id,
            "url": f"/folders/{folder_id}",
        }))
    return updated

# --- Phrases ---
@api_router.get("/phrases")
async def list_phrases(user=Depends(get_current_user)):
    rows = await fetch_all("SELECT id, text, created_at FROM phrases ORDER BY created_at ASC")
    return [{"id": r['id'], "text": r['text'], "created_at": dt_to_iso(r['created_at'])} for r in rows]

@api_router.post("/phrases")
async def create_phrase(data: PhraseCreate, user=Depends(require_bureau)):
    if not data.text.strip():
        raise HTTPException(status_code=400, detail="Texte requis")
    pid = await execute_returning_id(
        "INSERT INTO phrases (text, created_at) VALUES (%s, %s)",
        (data.text.strip(), now_dt()),
    )
    row = await fetch_one("SELECT id, text, created_at FROM phrases WHERE id=%s", (pid,))
    return {"id": row['id'], "text": row['text'], "created_at": dt_to_iso(row['created_at'])}

@api_router.delete("/phrases/{phrase_id}")
async def delete_phrase(phrase_id: int, user=Depends(require_bureau)):
    n = await execute("DELETE FROM phrases WHERE id=%s", (phrase_id,))
    if n == 0:
        raise HTTPException(status_code=404, detail="Phrase non trouvée")
    return {"ok": True}

# --- Manual notifications (bureau -> caristes) ---
@api_router.post("/notifications/send")
async def send_manual_notification(data: ManualNotifIn, user=Depends(require_bureau)):
    title = (data.title or '').strip()
    body = (data.body or '').strip()
    if not title:
        raise HTTPException(status_code=400, detail="Titre requis")
    if not body:
        raise HTTPException(status_code=400, detail="Message requis")
    if not data.user_ids:
        raise HTTPException(status_code=400, detail="Sélectionnez au moins un destinataire")

    # Validate target users exist and have access to My Truck (effective role)
    placeholders = ",".join(["%s"] * len(data.user_ids))
    targets_raw = await fetch_all(
        f"SELECT id, username, fullname, role, mytruck_role, notif_mode, notif_start, notif_end FROM users WHERE id IN ({placeholders})",
        tuple(data.user_ids),
    )
    targets = [t for t in targets_raw if effective_role(t) in MYTRUCK_ROLES]
    if not targets:
        raise HTTPException(status_code=400, detail="Aucun destinataire valide")

    sender = user.get('fullname') or user['username']
    now = now_dt()
    notif_id = await execute_returning_id(
        "INSERT INTO manual_notifications (sender_id, sender_name, title, body, created_at, recipients_count) "
        "VALUES (%s, %s, %s, %s, %s, %s)",
        (user['id'], sender, title, body, now, len(targets)),
    )
    recipients_rows = [(notif_id, t['id']) for t in targets]
    await execute_many(
        "INSERT INTO manual_notification_recipients (notification_id, user_id) VALUES (%s, %s)",
        recipients_rows,
    )

    payload = {
        "type": "manual_message",
        "message": {
            "id": notif_id,
            "title": title,
            "body": body,
            "from": sender,
            "at": dt_to_iso(now),
        }
    }
    # Real-time push gated by each recipient's quiet-hours preference.
    # History row is always written above, so muted users still see it on next login.
    pushed = 0
    for t in targets:
        if _should_push_now(t, now):
            asyncio.create_task(manager.send_to_user(t['id'], payload))
            pushed += 1

    return {
        "ok": True,
        "id": notif_id,
        "recipients": len(targets),
        "pushed": pushed,
    }

@api_router.get("/notifications/history")
async def notifications_history(user=Depends(require_bureau)):
    rows = await fetch_all(
        "SELECT id, sender_name, title, body, created_at, recipients_count "
        "FROM manual_notifications ORDER BY created_at DESC LIMIT 50"
    )
    return [
        {
            "id": r['id'],
            "from": r['sender_name'],
            "title": r['title'],
            "body": r['body'],
            "at": dt_to_iso(r['created_at']),
            "recipients_count": r['recipients_count'],
        }
        for r in rows
    ]

@api_router.get("/notifications/unread")
async def notifications_unread(user=Depends(get_current_user)):
    """Return unread manual notifications for the current user.

    Used as a polling fallback when the WebSocket cannot reach the backend
    (e.g. behind a reverse proxy that doesn't upgrade to wss://).
    """
    rows = await fetch_all(
        """SELECT mn.id, mn.sender_name, mn.title, mn.body, mn.created_at
             FROM manual_notification_recipients r
             JOIN manual_notifications mn ON mn.id = r.notification_id
            WHERE r.user_id = %s AND r.read_at IS NULL
            ORDER BY mn.created_at ASC
            LIMIT 50""",
        (user['id'],),
    )
    return [
        {
            "id": r['id'],
            "from": r['sender_name'],
            "title": r['title'],
            "body": r['body'],
            "at": dt_to_iso(r['created_at']),
        }
        for r in rows
    ]


@api_router.get("/notifications/events-since")
async def notifications_events_since(
    since: Optional[str] = None,
    user=Depends(get_current_user),
):
    """Replay folder events newer than `since` (ISO timestamp).

    Used as a polling fallback for clients whose WebSocket can't reach the
    backend (e.g. Cloudflare Tunnel + Nginx without WS upgrade). The frontend
    initializes `since` to "now" on first login, then advances it on each tick.

    Bureau receives: `new_folder` + `folder_appended` (by caristes).
    Cariste receives: `status_change` (take/close/reopen on their own folders).
    """
    server_now = now_dt()
    if not since:
        # No cursor yet → just hand back current server time so the client can
        # start polling from this moment forward.
        return {"events": [], "now": dt_to_iso(server_now)}

    try:
        # Handle URL edge case where '+' in '+00:00' got decoded as space.
        cursor = since.strip().replace(' ', '+').replace('Z', '+00:00')
        since_dt = datetime.fromisoformat(cursor)
        if since_dt.tzinfo is not None:
            since_dt = since_dt.astimezone(timezone.utc).replace(tzinfo=None)
    except Exception:
        raise HTTPException(status_code=400, detail="Invalid `since` timestamp")

    events: List[Dict[str, Any]] = []

    if user['role'] == 'bureau':
        rows = await fetch_all(
            """SELECT h.id, h.folder_id, h.action, h.by_id, h.by_name, h.at, h.label,
                      f.plate, f.problem
                 FROM folder_history h
                 JOIN folders f ON f.id = h.folder_id
                WHERE h.at > %s
                  AND (h.action = 'created'
                       OR (h.action = 'appended' AND h.by_id <> %s))
                ORDER BY h.at ASC LIMIT 100""",
            (since_dt, user['id']),
        )
        for r in rows:
            if r['action'] == 'created':
                events.append({
                    "type": "new_folder",
                    "at": dt_to_iso(r['at']),
                    "folder": {
                        "id": r['folder_id'],
                        "plate": r['plate'],
                        "problem": (r['problem'] or '')[:120],
                        "cariste_name": r['by_name'],
                        "created_at": dt_to_iso(r['at']),
                    },
                })
            else:  # appended
                events.append({
                    "type": "folder_appended",
                    "at": dt_to_iso(r['at']),
                    "folder": {
                        "id": r['folder_id'],
                        "plate": r['plate'],
                        "cariste_name": r['by_name'],
                        "label": r['label'],
                    },
                })
    else:  # cariste — own folders only, status changes only
        rows = await fetch_all(
            """SELECT h.id, h.folder_id, h.action, h.by_id, h.by_name, h.at, h.label,
                      f.plate, f.status
                 FROM folder_history h
                 JOIN folders f ON f.id = h.folder_id
                WHERE h.at > %s
                  AND f.cariste_id = %s
                  AND h.action IN ('take', 'close', 'reopen')
                ORDER BY h.at ASC LIMIT 100""",
            (since_dt, user['id']),
        )
        for r in rows:
            events.append({
                "type": "status_change",
                "at": dt_to_iso(r['at']),
                "folder": {
                    "id": r['folder_id'],
                    "plate": r['plate'],
                    "status": r['status'],
                    "label": r['label'],
                    "by_name": r['by_name'],
                },
            })

    return {"events": events, "now": dt_to_iso(server_now)}

@api_router.post("/notifications/{notif_id}/mark-read")
async def notification_mark_read(notif_id: int, user=Depends(get_current_user)):
    await execute(
        "UPDATE manual_notification_recipients SET read_at=%s "
        "WHERE notification_id=%s AND user_id=%s AND read_at IS NULL",
        (now_dt(), notif_id, user['id']),
    )
    return {"ok": True}

@api_router.post("/notifications/mark-all-read")
async def notification_mark_all_read(user=Depends(get_current_user)):
    await execute(
        "UPDATE manual_notification_recipients SET read_at=%s "
        "WHERE user_id=%s AND read_at IS NULL",
        (now_dt(), user['id']),
    )
    return {"ok": True}

# --- Device tokens (for future push notifications) ---
@api_router.post("/devices/register")
async def register_device(data: DeviceTokenIn, user=Depends(get_current_user)):
    if not data.token.strip():
        raise HTTPException(status_code=400, detail="Token requis")
    await execute(
        "INSERT INTO device_tokens (user_id, token, platform, created_at) VALUES (%s, %s, %s, %s) "
        "ON DUPLICATE KEY UPDATE platform=VALUES(platform), created_at=VALUES(created_at)",
        (user['id'], data.token.strip(), data.platform, now_dt()),
    )
    return {"ok": True}

@api_router.delete("/devices/unregister")
async def unregister_device(data: DeviceTokenIn, user=Depends(get_current_user)):
    await execute(
        "DELETE FROM device_tokens WHERE user_id=%s AND token=%s",
        (user['id'], data.token.strip()),
    )
    return {"ok": True}


# --- Web Push (VAPID) — real OS notifications even when tab is closed ---
# Keys are read from env first; if missing, they are auto-generated and persisted
# in app_settings on first startup (so Portainer users don't need shell access).
VAPID_PUBLIC_KEY = os.environ.get("VAPID_PUBLIC_KEY", "")
VAPID_PRIVATE_KEY = os.environ.get("VAPID_PRIVATE_KEY", "")
VAPID_SUBJECT = os.environ.get("VAPID_SUBJECT", "mailto:admin@example.com")


async def ensure_vapid_keys():
    """Auto-generate VAPID keys on first startup if not configured via env.
    Stores them in app_settings so they persist across container restarts.
    No-op if env vars are already set."""
    global VAPID_PUBLIC_KEY, VAPID_PRIVATE_KEY
    if VAPID_PUBLIC_KEY and VAPID_PRIVATE_KEY:
        return
    # Try DB first
    pub = await get_setting('vapid_public_key', '')
    priv = await get_setting('vapid_private_key', '')
    if pub and priv:
        VAPID_PUBLIC_KEY = pub
        VAPID_PRIVATE_KEY = priv
        logger.info("[push] Loaded VAPID keys from app_settings")
        return
    # Generate fresh ones
    from cryptography.hazmat.primitives.asymmetric import ec
    from cryptography.hazmat.primitives import serialization

    def _b64url(b):
        return base64.urlsafe_b64encode(b).rstrip(b'=').decode()

    private_key = ec.generate_private_key(ec.SECP256R1())
    pub_bytes = private_key.public_key().public_bytes(
        serialization.Encoding.X962,
        serialization.PublicFormat.UncompressedPoint,
    )
    priv_bytes = private_key.private_numbers().private_value.to_bytes(32, 'big')
    VAPID_PUBLIC_KEY = _b64url(pub_bytes)
    VAPID_PRIVATE_KEY = _b64url(priv_bytes)
    await set_setting('vapid_public_key', VAPID_PUBLIC_KEY)
    await set_setting('vapid_private_key', VAPID_PRIVATE_KEY)
    logger.info("[push] Generated new VAPID keys and stored them in app_settings")


class PushSubscribeIn(BaseModel):
    endpoint: str
    p256dh: str
    auth: str
    user_agent: Optional[str] = ""


class PushUnsubscribeIn(BaseModel):
    endpoint: str


@api_router.get("/push/public-key")
async def push_public_key():
    """Return the VAPID public key used by the frontend to subscribe."""
    return {"key": VAPID_PUBLIC_KEY}


@api_router.post("/push/subscribe")
async def push_subscribe(data: PushSubscribeIn, user=Depends(get_current_user)):
    if not data.endpoint or not data.p256dh or not data.auth:
        raise HTTPException(status_code=400, detail="Souscription invalide")
    await execute(
        "INSERT INTO push_subscriptions (user_id, endpoint, p256dh, auth, user_agent, created_at, last_used_at) "
        "VALUES (%s, %s, %s, %s, %s, %s, %s) "
        "ON DUPLICATE KEY UPDATE user_id=VALUES(user_id), p256dh=VALUES(p256dh), "
        "auth=VALUES(auth), user_agent=VALUES(user_agent), last_used_at=VALUES(last_used_at)",
        (
            user['id'],
            data.endpoint,
            data.p256dh,
            data.auth,
            (data.user_agent or '')[:255],
            now_dt(),
            now_dt(),
        ),
    )
    return {"ok": True}


@api_router.post("/push/unsubscribe")
async def push_unsubscribe(data: PushUnsubscribeIn, user=Depends(get_current_user)):
    await execute(
        "DELETE FROM push_subscriptions WHERE user_id=%s AND endpoint=%s",
        (user['id'], data.endpoint),
    )
    return {"ok": True}


async def _send_web_push_to_user_ids(user_ids: List[int], payload: dict):
    """Send a Web Push notification to all registered subscriptions for the given users.
    Runs in a thread to avoid blocking the event loop (pywebpush is sync)."""
    if not VAPID_PRIVATE_KEY or not user_ids:
        return
    rows = await fetch_all(
        f"SELECT id, endpoint, p256dh, auth FROM push_subscriptions "
        f"WHERE user_id IN ({','.join(['%s'] * len(user_ids))})",
        tuple(user_ids),
    )
    if not rows:
        return

    def _send_one(sub):
        from pywebpush import webpush, WebPushException
        try:
            webpush(
                subscription_info={
                    "endpoint": sub['endpoint'],
                    "keys": {"p256dh": sub['p256dh'], "auth": sub['auth']},
                },
                data=json.dumps(payload),
                vapid_private_key=VAPID_PRIVATE_KEY,
                vapid_claims={"sub": VAPID_SUBJECT},
                ttl=3600,
            )
            return ('ok', sub['id'])
        except WebPushException as e:
            # 404/410 → subscription is gone, prune it
            code = getattr(getattr(e, 'response', None), 'status_code', None)
            if code in (404, 410):
                return ('expired', sub['id'])
            return ('error', sub['id'])
        except Exception:
            return ('error', sub['id'])

    loop = asyncio.get_event_loop()
    results = await asyncio.gather(*[
        loop.run_in_executor(None, _send_one, sub) for sub in rows
    ])
    expired_ids = [rid for status, rid in results if status == 'expired']
    if expired_ids:
        await execute(
            f"DELETE FROM push_subscriptions WHERE id IN ({','.join(['%s']*len(expired_ids))})",
            tuple(expired_ids),
        )


async def push_to_bureau(payload: dict):
    """Send a Web Push to all bureau users with active subscriptions."""
    if not VAPID_PRIVATE_KEY:
        return
    rows = await fetch_all(
        "SELECT id FROM users WHERE COALESCE(mytruck_role, role) = 'bureau' AND is_active = 1"
    )
    user_ids = [r['id'] for r in rows]
    await _send_web_push_to_user_ids(user_ids, payload)


async def push_to_user(user_id: int, payload: dict):
    """Send a Web Push to one specific user."""
    await _send_web_push_to_user_ids([user_id], payload)

# --- WebSocket ---
@app.websocket("/api/ws/notifications")
async def ws_notifications(ws: WebSocket, token: Optional[str] = None):
    if not token:
        await ws.close(code=1008)
        return
    try:
        payload = pyjwt.decode(token, JWT_SECRET, algorithms=[JWT_ALG])
        user = await fetch_one("SELECT * FROM users WHERE id=%s", (int(payload.get('sub')),))
        role = effective_role(user) if user else None
        if not user or role not in MYTRUCK_ROLES:
            await ws.close(code=1008)
            return
    except Exception:
        await ws.close(code=1008)
        return

    # Accept the WS once, then register against the right groups
    await ws.accept()
    user_id = int(user['id'])
    if role == 'bureau':
        await manager.connect_bureau(ws)
    await manager.connect_user(ws, user_id)
    try:
        while True:
            await ws.receive_text()
    except WebSocketDisconnect:
        manager.disconnect(ws, user_id)
    except Exception:
        manager.disconnect(ws, user_id)

# --- Schema bootstrap (dev / fresh installs) ---
SCHEMA_SQL = [
    """CREATE TABLE IF NOT EXISTS users (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL,
        email VARCHAR(255) NULL,
        fullname VARCHAR(100) NOT NULL DEFAULT '',
        password_hash VARCHAR(255) NOT NULL,
        role VARCHAR(20) NOT NULL DEFAULT 'cariste',
        mytruck_role VARCHAR(20) NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        must_change_password TINYINT(1) NOT NULL DEFAULT 1,
        created_at DATETIME(6) NULL,
        UNIQUE KEY uq_username (username)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS folders (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        plate VARCHAR(50) NOT NULL,
        problem MEDIUMTEXT NOT NULL,
        status ENUM('en_attente','en_cours','cloture') NOT NULL DEFAULT 'en_attente',
        cariste_id INT NOT NULL,
        cariste_name VARCHAR(255) NOT NULL DEFAULT '',
        assigned_to_id INT NULL,
        assigned_to_name VARCHAR(255) NULL,
        created_at DATETIME(6) NOT NULL,
        updated_at DATETIME(6) NOT NULL,
        date_key VARCHAR(10) NOT NULL,
        KEY ix_date_key (date_key),
        KEY ix_cariste (cariste_id),
        KEY ix_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS folder_photos (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        folder_id INT NOT NULL,
        position INT NOT NULL,
        data LONGTEXT NOT NULL,
        KEY ix_folder_pos (folder_id, position),
        CONSTRAINT fk_photos_folder FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS folder_history (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        folder_id INT NOT NULL,
        action VARCHAR(50) NOT NULL,
        by_id INT NULL,
        by_name VARCHAR(255) NULL,
        at DATETIME(6) NOT NULL,
        label TEXT NOT NULL,
        KEY ix_folder_at (folder_id, at),
        CONSTRAINT fk_history_folder FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS phrases (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        text VARCHAR(500) NOT NULL,
        created_at DATETIME(6) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS manual_notifications (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        sender_id INT NOT NULL,
        sender_name VARCHAR(255) NOT NULL,
        title VARCHAR(255) NOT NULL,
        body TEXT NOT NULL,
        created_at DATETIME(6) NOT NULL,
        recipients_count INT NOT NULL DEFAULT 0,
        KEY ix_sender (sender_id),
        KEY ix_created (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS manual_notification_recipients (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        notification_id INT NOT NULL,
        user_id INT NOT NULL,
        read_at DATETIME(6) NULL,
        KEY ix_notif (notification_id),
        KEY ix_user (user_id),
        CONSTRAINT fk_recip_notif FOREIGN KEY (notification_id) REFERENCES manual_notifications(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS device_tokens (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        token VARCHAR(512) NOT NULL,
        platform VARCHAR(20) NOT NULL DEFAULT 'web',
        created_at DATETIME(6) NOT NULL,
        UNIQUE KEY uq_token (token),
        KEY ix_user (user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS app_settings (
        skey VARCHAR(50) NOT NULL PRIMARY KEY,
        svalue VARCHAR(255) NOT NULL DEFAULT ''
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    # ------- Gestion Palettes (GP V3 — shared schema with the PHP app) -------
    """CREATE TABLE IF NOT EXISTS motifs (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        label VARCHAR(100) NOT NULL UNIQUE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS zones (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        label VARCHAR(50) NOT NULL UNIQUE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS palettes (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        nombre_palettes INT NOT NULL DEFAULT 1,
        produit VARCHAR(255) NULL,
        fournisseur VARCHAR(255) NULL,
        klstb VARCHAR(100) NULL,
        sscc VARCHAR(50) NULL,
        gtin VARCHAR(50) NULL,
        motif VARCHAR(500) NULL,
        zone VARCHAR(50) NULL,
        commentaire TEXT NULL,
        created_by INT NULL,
        created_at DATETIME(6) NOT NULL,
        archived TINYINT(1) NOT NULL DEFAULT 0,
        KEY ix_archived (archived),
        KEY ix_created_at (created_at),
        KEY ix_sscc (sscc)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS sav_palettes (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        fournisseur VARCHAR(255) NULL,
        nbre_pal INT NOT NULL DEFAULT 0,
        nbre_colis INT NOT NULL DEFAULT 0,
        ref VARCHAR(255) NULL,
        transporteur VARCHAR(255) NULL,
        destinataire VARCHAR(255) NULL,
        livraison VARCHAR(50) NULL,
        created_by INT NULL,
        created_at DATETIME(6) NOT NULL,
        KEY ix_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS sav_bordereaux (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        date_bordereau DATE NULL,
        livraison VARCHAR(50) NULL,
        transporteur TEXT NULL,
        destinataire TEXT NULL,
        commentaire TEXT NULL,
        created_by INT NULL,
        created_at DATETIME(6) NOT NULL,
        KEY ix_date (date_bordereau),
        KEY ix_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS sav_items (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        bordereau_id INT NOT NULL,
        position INT NOT NULL DEFAULT 0,
        fournisseur VARCHAR(255) NULL,
        nbre_pal INT NOT NULL DEFAULT 0,
        nbre_colis INT NOT NULL DEFAULT 0,
        ref TEXT NULL,
        KEY ix_bordereau (bordereau_id),
        CONSTRAINT fk_sav_items_bord FOREIGN KEY (bordereau_id)
            REFERENCES sav_bordereaux(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS motif_colors (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        label VARCHAR(255) NOT NULL,
        label_norm VARCHAR(255) NOT NULL,
        color_hex CHAR(7) NOT NULL DEFAULT '#E0E0E0',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_label_norm (label_norm)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS bordereau_settings (
        id INT NOT NULL PRIMARY KEY DEFAULT 1,
        expediteur TEXT NULL,
        sav_phone VARCHAR(64) NULL,
        sav_email VARCHAR(255) NULL,
        temperature_default VARCHAR(64) NULL,
        important_text TEXT NULL,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS xdock_current_tasks (
        xdock_name VARCHAR(255) NOT NULL PRIMARY KEY,
        tour_number VARCHAR(50) NULL,
        tour_type VARCHAR(20) NULL,
        tracteur VARCHAR(50) NULL,
        remorque VARCHAR(50) NULL,
        last_seen DATETIME NULL,
        updated_at DATETIME NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS push_subscriptions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        endpoint VARCHAR(512) NOT NULL,
        p256dh VARCHAR(255) NOT NULL,
        auth VARCHAR(255) NOT NULL,
        user_agent VARCHAR(255) NULL,
        created_at DATETIME NOT NULL,
        last_used_at DATETIME NULL,
        UNIQUE KEY uk_push_endpoint (endpoint(255)),
        KEY ix_push_user (user_id),
        CONSTRAINT fk_push_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    # XDock+ scripts admin — table legacy `user_scripts` (clé user_id, identique à
    # l'ancien backend PHP alwaysdata/gp-eff.fr). On NE CRÉE PAS de table dédiée:
    # on réutilise la table existante pour récupérer immédiatement les droits déjà saisis.
    """CREATE TABLE IF NOT EXISTS user_scripts (
        user_id INT NOT NULL,
        script_key VARCHAR(80) NOT NULL,
        enabled TINYINT(1) NOT NULL DEFAULT 1,
        PRIMARY KEY (user_id, script_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",

    # Admin updates — historique des MAJ uploadées
    """CREATE TABLE IF NOT EXISTS update_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        filename VARCHAR(255) NOT NULL,
        zip_path VARCHAR(500),
        backup_path VARCHAR(500),
        size_bytes BIGINT NOT NULL DEFAULT 0,
        files_count INT NOT NULL DEFAULT 0,
        uploaded_by VARCHAR(100) NOT NULL,
        uploaded_at DATETIME NOT NULL,
        started_at DATETIME NULL,
        finished_at DATETIME NULL,
        status VARCHAR(30) NOT NULL DEFAULT 'uploaded',
        error VARCHAR(500) NULL,
        INDEX idx_uh_status (status),
        INDEX idx_uh_uploaded_at (uploaded_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",

    # Admin updates — mémo des idées pour les MAJ à venir (couleur = priorité)
    """CREATE TABLE IF NOT EXISTS update_memos (
        id INT AUTO_INCREMENT PRIMARY KEY,
        content TEXT NOT NULL,
        color VARCHAR(20) NOT NULL DEFAULT 'slate',
        pinned TINYINT(1) NOT NULL DEFAULT 0,
        created_by VARCHAR(100) NOT NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME NOT NULL,
        INDEX idx_um_created (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS sav_clients (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(200) NOT NULL,
        kind VARCHAR(20) NOT NULL DEFAULT 'both',
        address TEXT NULL,
        postal_code VARCHAR(20) NULL,
        city VARCHAR(120) NULL,
        country VARCHAR(80) NULL,
        contact_name VARCHAR(150) NULL,
        phone VARCHAR(60) NULL,
        email VARCHAR(150) NULL,
        notes TEXT NULL,
        created_by INT NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME NOT NULL,
        INDEX idx_sc_name (name),
        INDEX idx_sc_kind (kind)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS folder_email_templates (
        id INT AUTO_INCREMENT PRIMARY KEY,
        label VARCHAR(200) NOT NULL,
        body TEXT NOT NULL,
        sort_order INT NOT NULL DEFAULT 0,
        created_by INT NULL,
        created_at DATETIME NOT NULL,
        updated_at DATETIME NOT NULL,
        INDEX idx_fet_sort (sort_order)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS xdock_cs_status (
        tour_id VARCHAR(50) NOT NULL PRIMARY KEY,
        has_cs TINYINT(1) NOT NULL DEFAULT 0,
        updated_at DATETIME NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
    """CREATE TABLE IF NOT EXISTS xdock_ab_status (
        tour_id VARCHAR(50) NOT NULL PRIMARY KEY,
        has_ab TINYINT(1) NOT NULL DEFAULT 0,
        updated_at DATETIME NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci""",
]

ALTERS_SQL = [
    # Add must_change_password if missing on existing users tables
    ("users", "must_change_password", "ADD COLUMN must_change_password TINYINT(1) NOT NULL DEFAULT 1"),
    ("users", "is_active", "ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1"),
    ("users", "fullname", "ADD COLUMN fullname VARCHAR(100) NOT NULL DEFAULT ''"),
    ("users", "mytruck_role", "ADD COLUMN mytruck_role VARCHAR(20) NULL"),
    # Notification preferences per user
    ("users", "notif_mode", "ADD COLUMN notif_mode VARCHAR(10) NOT NULL DEFAULT 'always'"),
    ("users", "notif_start", "ADD COLUMN notif_start VARCHAR(5) NULL"),
    ("users", "notif_end", "ADD COLUMN notif_end VARCHAR(5) NULL"),
    # Service / équipe — used by the Reliquats PDF to group rows
    ("users", "service_shift", "ADD COLUMN service_shift VARCHAR(20) NULL"),
    # Email principal de l'utilisateur (facultatif)
    ("users", "email", "ADD COLUMN email VARCHAR(255) NULL"),
    # Email XDOCK (facultatif) — colonne nommée `email_xdock` pour matcher la DB GP existante
    ("users", "email_xdock", "ADD COLUMN email_xdock VARCHAR(255) NULL"),
    # XDock Plus integration (Edge extension) — identité dans XDock pour pré-remplissage auto
    ("users", "xdock_user_id", "ADD COLUMN xdock_user_id INT NULL"),
    ("users", "xdock_name", "ADD COLUMN xdock_name VARCHAR(255) NULL"),
    # Move livraison from sav_items (per-line) up to sav_bordereaux (per-bordereau)
    ("sav_bordereaux", "livraison", "ADD COLUMN livraison VARCHAR(50) NULL AFTER date_bordereau"),
    # New fields requested for the legacy "Bordereau de Chargement" PDF
    ("sav_bordereaux", "immatriculation", "ADD COLUMN immatriculation VARCHAR(50) NULL"),
    ("sav_bordereaux", "temperature", "ADD COLUMN temperature VARCHAR(50) NULL"),
    # XDock Plus auto-fill on folder creation (numéro de tournée + type EM/SM + plaque remorque)
    ("folders", "numero_em_sm", "ADD COLUMN numero_em_sm VARCHAR(50) NULL"),
    ("folders", "type_em_sm", "ADD COLUMN type_em_sm VARCHAR(20) NULL"),
    ("folders", "immatriculation_remorque", "ADD COLUMN immatriculation_remorque VARCHAR(50) NULL"),
    # SAV bordereau archive flag + per-event grouping for folder photos
    ("sav_bordereaux", "archived", "ADD COLUMN archived TINYINT(1) NOT NULL DEFAULT 0"),
    ("sav_bordereaux", "archived_at", "ADD COLUMN archived_at DATETIME NULL"),
    ("folder_photos", "event_id", "ADD COLUMN event_id INT NULL"),
    # Droits admin scripts XDock+ (donné par superadmin à n'importe quel user)
    ("users", "can_manage_xdock_scripts", "ADD COLUMN can_manage_xdock_scripts TINYINT(1) NOT NULL DEFAULT 0"),
    # Droit de gérer la config email Dossiers Camion (super-admin peut le déléguer)
    ("users", "can_manage_folder_email", "ADD COLUMN can_manage_folder_email TINYINT(1) NOT NULL DEFAULT 0"),
]

# Indexes added after the table already exists. Idempotent thanks to the
# helper below which checks information_schema before issuing ALTER TABLE.
EXTRA_INDEXES = [
    ("folders", "ix_assigned_to", "ADD INDEX ix_assigned_to (assigned_to_id)"),
    ("folders", "ix_plate", "ADD INDEX ix_plate (plate)"),
]

# =============================================================================
# === XDock+ scripts admin ====================================================
# =============================================================================
# Catalogue des modules visibles dans l'UI (affichés en switches dans la page admin).
# `groups` = scripts liés (ex. map_pack active 3 clés à la fois côté extension).
XDOCK_SCRIPT_CATALOG = [
    {"key": "map_pack", "label": "MAP (BadgeMap + Historique + Autoblock)",
     "group": ["badgemap", "histo_map_porte", "autoblock_daemon"]},
    {"key": "synthese", "label": "Synthese (beta)"},
    {"key": "jabber", "label": "Cisco Jabber"},
    {"key": "contacts", "label": "Contacts"},
    {"key": "icone_cs", "label": "Icônes CS"},
    {"key": "pastille_em", "label": "Pastilles EM"},
    {"key": "recherche_em_sm", "label": "Recherche EM/SM (F8)"},
    {"key": "reliquats", "label": "Reliquats GP"},
    {"key": "abcolor", "label": "AB Color"},
    {"key": "park_stats", "label": "Compteurs voyants"},
    {"key": "destinations", "label": "Destinations cliquables"},
    {"key": "pdf_sm", "label": "PDF SM trié"},
    {"key": "barcode_zone_porte", "label": "Code-barres Zone/Porte"},
    {"key": "barcode_p", "label": "Code-barres P"},
    {"key": "auto_resume", "label": "Auto reprise"},
    {"key": "panneau_controle", "label": "Panneau contrôle"},
    {"key": "controle", "label": "Contrôle Portail"},
    {"key": "correcteur", "label": "Correcteur (proxy IA)"},
    {"key": "emdestination", "label": "EM Destination"},
    {"key": "portail", "label": "Portail"},
    {"key": "nb_palf", "label": "Nombre PAL Fournisseurs"},
    {"key": "em_mode0", "label": "Pastille EM V/J/R"},
    {"key": "printA3", "label": "Impression Semaine"},
]

XDOCK_PRESET_RECO = [
    "map_pack", "synthese", "jabber", "abcolor", "park_stats", "reliquats",
    "destinations", "pdf_sm", "barcode_zone_porte", "barcode_p",
    "panneau_controle", "nb_palf", "portail",
]


def _xs_expand(key: str) -> List[str]:
    for s in XDOCK_SCRIPT_CATALOG:
        if s["key"] == key:
            return s.get("group", [key])
    return [key]


def _xs_all_real_keys() -> List[str]:
    out: List[str] = []
    for s in XDOCK_SCRIPT_CATALOG:
        out.extend(s.get("group", [s["key"]]))
    return list(set(out))


class XdockToggleIn(BaseModel):
    script_key: str
    enabled: bool


class XdockBulkSetIn(BaseModel):
    mode: str  # "all_on" | "all_off" | "preset_reco"


class XdockUserCreateIn(BaseModel):
    email: str
    fullname: Optional[str] = ""
    role: Optional[str] = "user"


class XdockUserPatchIn(BaseModel):
    fullname: Optional[str] = None
    role: Optional[str] = None
    is_active: Optional[bool] = None


@api_router.get("/xdock-scripts/catalog")
async def xs_catalog(_user=Depends(require_can_manage_xdock_scripts)):
    return {
        "catalog": [
            {"key": s["key"], "label": s["label"], "group": s.get("group", [s["key"]])}
            for s in XDOCK_SCRIPT_CATALOG
        ],
        "preset_reco": XDOCK_PRESET_RECO,
    }


@api_router.get("/xdock-scripts/users")
async def xs_list_users(_user=Depends(require_can_manage_xdock_scripts)):
    """Liste les utilisateurs MyTruck avec statut **bureau** (les caristes n'utilisent
    pas l'extension XDock+). Sont inclus : `users.role='bureau'` (purs MyTruck)
    OU `users.mytruck_role='bureau'` (users GP v3 avec accès bureau)."""
    rows = await fetch_all(
        "SELECT id, username, fullname, role, mytruck_role, is_active, email_xdock "
        "FROM users "
        "WHERE role='bureau' OR mytruck_role='bureau' "
        "ORDER BY fullname, username",
        (),
    )
    return {
        "users": [
            {
                "id": int(r["id"]),
                "email": r.get("email_xdock") or "",
                "username": r.get("username") or "",
                "fullname": r.get("fullname") or "",
                "role": r.get("role") or r.get("mytruck_role") or "user",
                "is_active": bool(int(r.get("is_active") or 0)),
            }
            for r in rows
        ],
    }


@api_router.get("/xdock-scripts/users/{user_id}/features")
async def xs_user_features(user_id: int, _user=Depends(require_can_manage_xdock_scripts)):
    rows = await fetch_all(
        "SELECT script_key FROM user_scripts WHERE user_id=%s AND enabled=1",
        (user_id,),
    )
    return {"features": [r["script_key"] for r in rows]}


@api_router.post("/xdock-scripts/users/{user_id}/toggle")
async def xs_toggle(user_id: int, data: XdockToggleIn,
                    _user=Depends(require_can_manage_xdock_scripts)):
    real_keys = _xs_expand(data.script_key)
    for rk in real_keys:
        if data.enabled:
            await execute(
                "INSERT INTO user_scripts (user_id, script_key, enabled) "
                "VALUES (%s, %s, 1) "
                "ON DUPLICATE KEY UPDATE enabled=1",
                (user_id, rk),
            )
        else:
            await execute(
                "DELETE FROM user_scripts WHERE user_id=%s AND script_key=%s",
                (user_id, rk),
            )
    return {"ok": True}


@api_router.post("/xdock-scripts/users/{user_id}/bulk-set")
async def xs_bulk_set(user_id: int, data: XdockBulkSetIn,
                      _user=Depends(require_can_manage_xdock_scripts)):
    await execute("DELETE FROM user_scripts WHERE user_id=%s", (user_id,))
    if data.mode == "all_off":
        return {"ok": True}
    if data.mode == "all_on":
        to_enable = [s["key"] for s in XDOCK_SCRIPT_CATALOG]
    elif data.mode == "preset_reco":
        to_enable = XDOCK_PRESET_RECO[:]
    else:
        raise HTTPException(status_code=400, detail="mode inconnu")
    expanded: List[str] = []
    for k in to_enable:
        expanded.extend(_xs_expand(k))
    expanded = list(set(expanded))
    for rk in expanded:
        await execute(
            "INSERT INTO user_scripts (user_id, script_key, enabled) "
            "VALUES (%s, %s, 1) "
            "ON DUPLICATE KEY UPDATE enabled=1",
            (user_id, rk),
        )
    return {"ok": True, "enabled": len(expanded)}


# Liste des clés réelles connues côté extension XDock+ (cohérent avec scripts_get.php legacy)
XDOCK_LEGACY_FEATURE_KEYS = [
    'printA3', 'ui_theme', 'nb_palf', 'em_mode0', 'abcolor', 'badgemap',
    'barcode_zone_porte', 'barcode_p', 'auto_resume', 'panneau_controle',
    'jabber', 'park_stats', 'controle', 'correcteur', 'destinations',
    'pdf_sm', 'emdestination', 'histo_map_porte', 'contacts', 'icone_cs',
    'pastille_em', 'recherche_em_sm', 'reliquats', 'autoblock_daemon',
    'portail', 'synthese',
]


@api_router.get("/xdock-scripts/by-email")
async def xs_by_email(xdock_email: str, request: Request):
    """Remplaçant 1:1 de `scripts_get.php` (alwaysdata legacy). Renvoie le **même
    JSON** que l'ancien endpoint pour que l'extension Edge XDock+ continue à
    fonctionner sans modification :

        {
          "ok": true,
          "user_id": 1,
          "xdock_email": "stb_maur@xdock.de",
          "features": { "jabber": true, "abcolor": true, "nb_palf": false, ... }
        }

    L'auth se fait via le header `X-API-Key` (la valeur attendue est
    `XDOCKPLUS_API_KEY` côté `.env`, par défaut `xdockplus` pour matcher le PHP).
    """
    expected_key = os.environ.get("XDOCKPLUS_API_KEY", "xdockplus")
    if request.headers.get("X-API-Key") != expected_key:
        return JSONResponse(
            status_code=401,
            content={"ok": False, "error": "bad_api_key",
                     "features": {k: False for k in XDOCK_LEGACY_FEATURE_KEYS}},
        )
    email = (xdock_email or "").strip().lower()
    if not email:
        return JSONResponse(
            status_code=401,
            content={"ok": False, "error": "not_authenticated",
                     "features": {k: False for k in XDOCK_LEGACY_FEATURE_KEYS}},
        )
    u = await fetch_one(
        "SELECT id, fullname, role, is_active FROM users WHERE LOWER(email_xdock)=%s LIMIT 1",
        (email,),
    )
    if not u:
        return JSONResponse(
            status_code=403,
            content={"ok": False, "error": "unknown_xdock_email",
                     "xdock_email": email,
                     "features": {k: False for k in XDOCK_LEGACY_FEATURE_KEYS}},
        )
    user_id = int(u["id"])
    rows = await fetch_all(
        "SELECT script_key, enabled FROM user_scripts WHERE user_id=%s",
        (user_id,),
    )
    features = {k: False for k in XDOCK_LEGACY_FEATURE_KEYS}
    for r in rows:
        k = (r.get("script_key") or "")
        if k in features:
            features[k] = (int(r.get("enabled") or 0) == 1)
    return {
        "ok": True,
        "user_id": user_id,
        "xdock_email": email,
        "features": features,
    }


# Toggle du droit `can_manage_xdock_scripts` sur un user MyTruck (super-admin only)
class GrantAdminIn(BaseModel):
    can_manage: bool

@api_router.post("/admin/users/{user_id}/grant-xdock-scripts-admin")
async def grant_xdock_scripts_admin(user_id: int, data: GrantAdminIn,
                                    _user=Depends(require_super_admin)):
    await execute(
        "UPDATE users SET can_manage_xdock_scripts=%s WHERE id=%s",
        (1 if data.can_manage else 0, user_id),
    )
    return {"ok": True}


# =============================================================================
# === ensure_schema (DB migrations) ==========================================
# =============================================================================
async def index_exists(table: str, index_name: str) -> bool:
    row = await fetch_one(
        "SELECT COUNT(*) AS c FROM information_schema.statistics "
        "WHERE table_schema=DATABASE() AND table_name=%s AND index_name=%s",
        (table, index_name),
    )
    return bool(row and int(row['c']) > 0)

async def column_exists(table: str, column: str) -> bool:
    row = await fetch_one(
        "SELECT COUNT(*) AS c FROM information_schema.columns "
        "WHERE table_schema=DATABASE() AND table_name=%s AND column_name=%s",
        (table, column),
    )
    return bool(row and int(row['c']) > 0)

async def ensure_schema():
    for stmt in SCHEMA_SQL:
        await execute(stmt)
    for table, col, alter in ALTERS_SQL:
        if not await column_exists(table, col):
            try:
                await execute(f"ALTER TABLE {table} {alter}")
            except Exception as e:
                logging.warning(f"ALTER {table}.{col} failed: {e}")
    for table, idx, alter in EXTRA_INDEXES:
        if not await index_exists(table, idx):
            try:
                await execute(f"ALTER TABLE {table} {alter}")
            except Exception as e:
                logging.warning(f"INDEX {table}.{idx} failed: {e}")

# =============================================================================
# === Admin Updates panel (super-admin only) =================================
# =============================================================================
# Upload d'un ZIP → backup → extract → restart containers via Docker socket.
# Tout est tracé en DB (`update_history`) + log en mémoire (polling frontend).
# Réservé strictement à `mathieu.u`.
# =============================================================================

UPDATE_HOST_DEPLOY = os.environ.get("HOST_DEPLOY_DIR", "/host-deploy")
UPDATE_UPLOAD_DIR = os.path.join(UPDATE_HOST_DEPLOY, "updates")
UPDATE_BACKUP_DIR = os.path.join(UPDATE_HOST_DEPLOY, "backups")
DOCKER_SOCKET = os.environ.get("DOCKER_SOCK", "/var/run/docker.sock")

# Fichiers/dossiers que l'on accepte d'extraire (whitelist par sécurité)
UPDATE_WHITELIST = (
    "backend/server.py",
    "backend/requirements.txt",
    "frontend/build/",
    "nginx/default.conf",
)

# Containers à redémarrer après une mise à jour (dans l'ordre)
UPDATE_RESTART_CONTAINERS = ["cariste-nginx", "cariste-backend"]

# Logs en mémoire — clé = update_id, valeur = list[str] (lignes)
_update_logs: dict[int, list[str]] = {}


def _update_log(uid: int, line: str) -> None:
    ts = datetime.now(timezone.utc).strftime("%H:%M:%S")
    msg = f"[{ts}] {line}"
    _update_logs.setdefault(uid, []).append(msg)
    logging.info(f"[admin-update #{uid}] {line}")


async def require_mathieu(user=Depends(get_current_user)):
    if (user.get("username") or "").lower() != SUPER_ADMIN_USERNAME:
        raise HTTPException(status_code=403, detail="Accès super-admin requis")
    return user


def _is_safe_member(name: str) -> bool:
    """Vérifie qu'un chemin de ZIP est sûr (whitelist + pas de path traversal)."""
    n = name.replace("\\", "/").lstrip("./")
    if ".." in n.split("/"):
        return False
    if not any(n.startswith(p) for p in UPDATE_WHITELIST):
        return False
    return True


def _safe_join(base: str, rel: str) -> str:
    """Empêche les sorties hors de `base` (path traversal)."""
    full = os.path.realpath(os.path.join(base, rel))
    if not full.startswith(os.path.realpath(base) + os.sep) and full != os.path.realpath(base):
        raise HTTPException(status_code=400, detail=f"Chemin invalide: {rel}")
    return full


@api_router.post("/admin/updates/upload")
async def admin_update_upload(
    file: UploadFile = File(...),
    user=Depends(require_mathieu),
):
    """Upload un ZIP de mise à jour. Stocke sur disque + crée une ligne en DB."""
    if not file.filename.lower().endswith(".zip"):
        raise HTTPException(status_code=400, detail="Fichier ZIP attendu")
    os.makedirs(UPDATE_UPLOAD_DIR, exist_ok=True)
    ts = datetime.now(timezone.utc).strftime("%Y%m%d_%H%M%S")
    safe_name = re.sub(r"[^a-zA-Z0-9._-]", "_", file.filename)
    dest = os.path.join(UPDATE_UPLOAD_DIR, f"{ts}__{safe_name}")
    content = await file.read()
    if len(content) < 1024:
        raise HTTPException(status_code=400, detail="ZIP trop petit / vide")
    with open(dest, "wb") as f:
        f.write(content)
    # Sanity check: vérifier que c'est bien un ZIP valide
    import zipfile
    try:
        with zipfile.ZipFile(dest, "r") as zf:
            names = zf.namelist()
    except zipfile.BadZipFile:
        os.remove(dest)
        raise HTTPException(status_code=400, detail="Fichier ZIP invalide")
    safe_members = [n for n in names if _is_safe_member(n) or _is_safe_member(n.split("/", 1)[1] if "/" in n else n)]
    now = now_dt()
    new_uid = await execute_returning_id(
        "INSERT INTO update_history "
        "(filename, zip_path, size_bytes, files_count, uploaded_by, uploaded_at, status) "
        "VALUES (%s, %s, %s, %s, %s, %s, 'uploaded')",
        (safe_name, dest, len(content), len(safe_members), user["username"], now),
    )
    return {
        "ok": True,
        "id": new_uid,
        "filename": safe_name,
        "size": len(content),
        "files": len(safe_members),
        "preview": safe_members[:30],
    }


async def _run_install(uid: int):
    """Background task: backup + extract + restart."""
    import zipfile, shutil
    try:
        row = await fetch_one("SELECT * FROM update_history WHERE id=%s", (uid,))
        if not row:
            _update_log(uid, "❌ Update introuvable en DB")
            return
        zip_path = row.get("zip_path") or ""
        # Fallback : si zip_path est vide (lignes uploadées avant le fix LAST_INSERT_ID),
        # on cherche le fichier le plus récent qui matche le filename dans uploads/
        if not zip_path or not os.path.exists(zip_path):
            fname = row.get("filename") or ""
            if fname and os.path.isdir(UPDATE_UPLOAD_DIR):
                candidates = sorted(
                    [f for f in os.listdir(UPDATE_UPLOAD_DIR) if f.endswith(fname)],
                    reverse=True,
                )
                if candidates:
                    zip_path = os.path.join(UPDATE_UPLOAD_DIR, candidates[0])
                    await execute("UPDATE update_history SET zip_path=%s WHERE id=%s",
                                  (zip_path, uid))
                    _update_log(uid, f"📎 ZIP retrouvé via fallback: {candidates[0]}")
        if not zip_path or not os.path.exists(zip_path):
            _update_log(uid, "❌ Fichier ZIP introuvable sur le disque — ré-upload nécessaire")
            await execute("UPDATE update_history SET status='failed', error=%s WHERE id=%s",
                          ("zip_path manquant", uid))
            return
        await execute("UPDATE update_history SET status='installing', started_at=%s WHERE id=%s",
                      (now_dt(), uid))
        _update_log(uid, f"🚀 Démarrage de l'installation de {row['filename']}")

        # 1) Backup
        backup_ts = datetime.now(timezone.utc).strftime("%Y%m%d_%H%M%S")
        backup_dir = os.path.join(UPDATE_BACKUP_DIR, f"{backup_ts}_before_{uid}")
        os.makedirs(backup_dir, exist_ok=True)
        _update_log(uid, f"📦 Backup vers {backup_dir}")
        for target in UPDATE_WHITELIST:
            src = os.path.join(UPDATE_HOST_DEPLOY, target.rstrip("/"))
            if not os.path.exists(src):
                continue
            dst = os.path.join(backup_dir, target.rstrip("/"))
            os.makedirs(os.path.dirname(dst), exist_ok=True)
            if os.path.isdir(src):
                shutil.copytree(src, dst, dirs_exist_ok=True)
            else:
                shutil.copy2(src, dst)
            _update_log(uid, f"  ✓ backup {target}")
        await execute("UPDATE update_history SET backup_path=%s WHERE id=%s",
                      (backup_dir, uid))

        # 2) Extraction
        extracted = 0
        with zipfile.ZipFile(zip_path, "r") as zf:
            for member in zf.namelist():
                # Le ZIP a une racine `deploy/` qu'il faut retirer
                rel = member
                if rel.startswith("deploy/"):
                    rel = rel[len("deploy/"):]
                if not rel or rel.endswith("/"):
                    continue
                if not _is_safe_member(rel):
                    continue
                target_path = _safe_join(UPDATE_HOST_DEPLOY, rel)
                os.makedirs(os.path.dirname(target_path), exist_ok=True)
                with zf.open(member) as src, open(target_path, "wb") as dst:
                    shutil.copyfileobj(src, dst)
                extracted += 1
        _update_log(uid, f"📂 Extraction OK — {extracted} fichier(s) écrit(s)")

        # 3) Restart containers via Docker socket
        try:
            import docker as docker_lib
            client = docker_lib.DockerClient(base_url=f"unix://{DOCKER_SOCKET}")
            for cname in UPDATE_RESTART_CONTAINERS:
                try:
                    c = client.containers.get(cname)
                    _update_log(uid, f"🔄 Restart {cname}…")
                    c.restart(timeout=10)
                    _update_log(uid, f"  ✓ {cname} redémarré")
                except docker_lib.errors.NotFound:
                    _update_log(uid, f"  ⚠ container {cname} introuvable — ignoré")
                except Exception as e:
                    _update_log(uid, f"  ❌ restart {cname}: {e}")
            client.close()
        except Exception as e:
            _update_log(uid, f"⚠ Docker socket inaccessible ({e}) — restart manuel requis")

        await execute("UPDATE update_history SET status='installed', finished_at=%s WHERE id=%s",
                      (now_dt(), uid))
        _update_log(uid, "✅ Installation terminée")
    except Exception as e:
        _update_log(uid, f"❌ ERREUR: {e}")
        await execute("UPDATE update_history SET status='failed', finished_at=%s, error=%s WHERE id=%s",
                      (now_dt(), str(e)[:500], uid))


@api_router.post("/admin/updates/{uid}/install")
async def admin_update_install(uid: int, user=Depends(require_mathieu)):
    row = await fetch_one("SELECT status FROM update_history WHERE id=%s", (uid,))
    if not row:
        raise HTTPException(status_code=404, detail="Mise à jour introuvable")
    if row["status"] not in ("uploaded", "failed"):
        raise HTTPException(status_code=409, detail=f"Statut invalide: {row['status']}")
    _update_logs[uid] = []
    asyncio.create_task(_run_install(uid))
    return {"ok": True, "id": uid, "started": True}


@api_router.post("/admin/updates/{uid}/rollback")
async def admin_update_rollback(uid: int, user=Depends(require_mathieu)):
    import shutil
    row = await fetch_one("SELECT * FROM update_history WHERE id=%s", (uid,))
    if not row or not row.get("backup_path"):
        raise HTTPException(status_code=404, detail="Backup introuvable pour cette MAJ")
    backup_dir = row["backup_path"]
    if not os.path.isdir(backup_dir):
        raise HTTPException(status_code=410, detail="Backup supprimé du disque")
    _update_logs[uid] = []
    _update_log(uid, f"⏪ Rollback depuis {backup_dir}")
    for target in UPDATE_WHITELIST:
        src = os.path.join(backup_dir, target.rstrip("/"))
        dst = os.path.join(UPDATE_HOST_DEPLOY, target.rstrip("/"))
        if not os.path.exists(src):
            continue
        try:
            if os.path.isdir(src):
                if os.path.exists(dst):
                    shutil.rmtree(dst)
                shutil.copytree(src, dst)
            else:
                os.makedirs(os.path.dirname(dst), exist_ok=True)
                shutil.copy2(src, dst)
            _update_log(uid, f"  ✓ restored {target}")
        except Exception as e:
            _update_log(uid, f"  ❌ restore {target}: {e}")
    # Restart
    try:
        import docker as docker_lib
        client = docker_lib.DockerClient(base_url=f"unix://{DOCKER_SOCKET}")
        for cname in UPDATE_RESTART_CONTAINERS:
            try:
                client.containers.get(cname).restart(timeout=10)
                _update_log(uid, f"🔄 {cname} redémarré")
            except Exception as e:
                _update_log(uid, f"  ❌ restart {cname}: {e}")
        client.close()
    except Exception as e:
        _update_log(uid, f"⚠ Docker: {e}")
    await execute("UPDATE update_history SET status='rolled_back', finished_at=%s WHERE id=%s",
                  (now_dt(), uid))
    _update_log(uid, "✅ Rollback terminé")
    return {"ok": True}


@api_router.get("/admin/updates/{uid}/log")
async def admin_update_log(uid: int, user=Depends(require_mathieu)):
    lines = _update_logs.get(uid, [])
    row = await fetch_one("SELECT status FROM update_history WHERE id=%s", (uid,))
    return {"lines": lines, "status": row["status"] if row else "unknown"}


@api_router.get("/admin/updates/history")
async def admin_update_history(user=Depends(require_mathieu)):
    rows = await fetch_all(
        "SELECT id, filename, size_bytes, files_count, uploaded_by, uploaded_at, "
        "       status, started_at, finished_at, backup_path, error "
        "FROM update_history ORDER BY id DESC LIMIT 50",
        (),
    )
    return {
        "items": [
            {
                "id": int(r["id"]),
                "filename": r.get("filename") or "",
                "size_bytes": int(r.get("size_bytes") or 0),
                "files_count": int(r.get("files_count") or 0),
                "uploaded_by": r.get("uploaded_by") or "",
                "uploaded_at": dt_to_iso(r.get("uploaded_at")),
                "started_at": dt_to_iso(r.get("started_at")),
                "finished_at": dt_to_iso(r.get("finished_at")),
                "status": r.get("status") or "unknown",
                "has_backup": bool(r.get("backup_path") and os.path.isdir(r.get("backup_path") or "")),
                "error": r.get("error") or "",
            }
            for r in rows
        ]
    }


@api_router.delete("/admin/updates/{uid}")
async def admin_update_delete(uid: int, user=Depends(require_mathieu)):
    row = await fetch_one("SELECT * FROM update_history WHERE id=%s", (uid,))
    if not row:
        raise HTTPException(status_code=404, detail="Introuvable")
    # Cleanup zip + backup
    for key in ("zip_path", "backup_path"):
        p = row.get(key)
        if p and os.path.exists(p):
            try:
                if os.path.isdir(p):
                    import shutil
                    shutil.rmtree(p)
                else:
                    os.remove(p)
            except Exception:
                pass
    await execute("DELETE FROM update_history WHERE id=%s", (uid,))
    return {"ok": True}


# --- Mémo des idées pour les futures mises à jour (super-admin) ---------------
MEMO_ALLOWED_COLORS = {"slate", "red", "orange", "yellow", "green", "blue", "purple"}


class MemoIn(BaseModel):
    content: str
    color: Optional[str] = "slate"
    pinned: Optional[bool] = False


class MemoUpdate(BaseModel):
    content: Optional[str] = None
    color: Optional[str] = None
    pinned: Optional[bool] = None


@api_router.get("/admin/memos")
async def memo_list(user=Depends(require_mathieu)):
    rows = await fetch_all(
        "SELECT id, content, color, pinned, created_by, created_at, updated_at "
        "FROM update_memos ORDER BY pinned DESC, updated_at DESC",
        (),
    )
    return {
        "items": [
            {
                "id": int(r["id"]),
                "content": r.get("content") or "",
                "color": r.get("color") or "slate",
                "pinned": bool(int(r.get("pinned") or 0)),
                "created_by": r.get("created_by") or "",
                "created_at": dt_to_iso(r.get("created_at")),
                "updated_at": dt_to_iso(r.get("updated_at")),
            }
            for r in rows
        ]
    }


@api_router.post("/admin/memos")
async def memo_create(data: MemoIn, user=Depends(require_mathieu)):
    content = (data.content or "").strip()
    if not content:
        raise HTTPException(status_code=400, detail="Contenu requis")
    color = (data.color or "slate").lower()
    if color not in MEMO_ALLOWED_COLORS:
        color = "slate"
    now = now_dt()
    new_id = await execute_returning_id(
        "INSERT INTO update_memos (content, color, pinned, created_by, created_at, updated_at) "
        "VALUES (%s, %s, %s, %s, %s, %s)",
        (content, color, 1 if data.pinned else 0, user["username"], now, now),
    )
    return {"ok": True, "id": new_id}


@api_router.patch("/admin/memos/{mid}")
async def memo_update(mid: int, data: MemoUpdate, user=Depends(require_mathieu)):
    row = await fetch_one("SELECT id FROM update_memos WHERE id=%s", (mid,))
    if not row:
        raise HTTPException(status_code=404, detail="Memo introuvable")
    sets, vals = [], []
    if data.content is not None:
        c = (data.content or "").strip()
        if not c:
            raise HTTPException(status_code=400, detail="Contenu vide")
        sets.append("content=%s"); vals.append(c)
    if data.color is not None:
        col = (data.color or "slate").lower()
        if col not in MEMO_ALLOWED_COLORS:
            col = "slate"
        sets.append("color=%s"); vals.append(col)
    if data.pinned is not None:
        sets.append("pinned=%s"); vals.append(1 if data.pinned else 0)
    if not sets:
        return {"ok": True}
    sets.append("updated_at=%s"); vals.append(now_dt())
    vals.append(mid)
    await execute(f"UPDATE update_memos SET {', '.join(sets)} WHERE id=%s", tuple(vals))
    return {"ok": True}


@api_router.delete("/admin/memos/{mid}")
async def memo_delete(mid: int, user=Depends(require_mathieu)):
    await execute("DELETE FROM update_memos WHERE id=%s", (mid,))
    return {"ok": True}


# --- Daily auto-archive job (06:01 local time every day) ---
_scheduler = None

async def _auto_archive_palettes_job():
    """
    Archive every active palette every morning at 06:01.
    The user's process: each day the day's reliquats are saisied automatically,
    and at 06:01 everything still active is moved to 'archived' so the active
    list starts clean for the new day.
    """
    try:
        n = await fetch_one("SELECT COUNT(*) AS c FROM palettes WHERE archived=0", ())
        before = int(n['c']) if n else 0
        await execute("UPDATE palettes SET archived=1 WHERE archived=0")
        logging.info(f"[scheduler] Auto-archive 06:01 — {before} palettes archivées.")
    except Exception as e:
        logging.error(f"[scheduler] auto-archive failed: {e}")

SCHEDULER_TZ = os.environ.get('TZ', 'Europe/Paris')


def _start_scheduler():
    global _scheduler
    if _scheduler is not None:
        return
    from apscheduler.schedulers.asyncio import AsyncIOScheduler
    _scheduler = AsyncIOScheduler(timezone=SCHEDULER_TZ)
    _scheduler.start()
    # Add the job synchronously here using the default 06:01; if app_settings has
    # a different time, _reschedule_archive_job will update it just after startup.
    # IMPORTANT: pass timezone explicitly to CronTrigger — it defaults to UTC,
    # NOT to the scheduler's timezone (this caused the 03:41 bug previously).
    from apscheduler.triggers.cron import CronTrigger
    _scheduler.add_job(
        _auto_archive_palettes_job,
        CronTrigger(hour=6, minute=1, timezone=SCHEDULER_TZ),
        id='auto_archive_palettes',
        replace_existing=True,
        misfire_grace_time=600,
    )
    logging.info(f"[scheduler] Started — auto-archive default 06:01 ({SCHEDULER_TZ}).")


async def _reschedule_archive_job():
    """Re-create the cron trigger using current archive_hour / archive_minute
    stored in app_settings. Called at startup and after user changes the time."""
    global _scheduler
    if _scheduler is None:
        return
    from apscheduler.triggers.cron import CronTrigger
    try:
        h = int(await get_setting('archive_hour', '6'))
        m = int(await get_setting('archive_minute', '1'))
    except Exception:
        h, m = 6, 1
    h = max(0, min(23, h))
    m = max(0, min(59, m))
    # IMPORTANT: pass timezone explicitly — CronTrigger defaults to UTC otherwise.
    trigger = CronTrigger(hour=h, minute=m, timezone=SCHEDULER_TZ)
    _scheduler.reschedule_job('auto_archive_palettes', trigger=trigger)
    try:
        job = _scheduler.get_job('auto_archive_palettes')
        next_run = job.next_run_time if job else None
    except Exception:
        next_run = None
    logging.info(f"[scheduler] Auto-archive scheduled at {h:02d}:{m:02d} {SCHEDULER_TZ}. Next run: {next_run}")

# ============================================================================
# Folder Email — bouton "Envoyer par email" depuis un dossier camion
# ----------------------------------------------------------------------------
# - Templates de texte gérables par mathieu.u (ou délégué via can_manage_folder_email)
# - Destinataires SAV + Quai stockés dans app_settings
# - Photos servies via lien signé HMAC public (pas besoin d'être connecté)
#   → expire 30 jours, format : /api/public/folder-photo?fid=&pid=&exp=&sig=
# ============================================================================
import hmac as _hmac
import hashlib as _hashlib

FOLDER_EMAIL_PHOTO_TTL_DAYS = 30


def _folder_photo_sign(fid: int, pid: int, exp: int) -> str:
    msg = f"{fid}:{pid}:{exp}".encode()
    return _hmac.new(JWT_SECRET.encode(), msg, _hashlib.sha256).hexdigest()


def _folder_photo_verify(fid: int, pid: int, exp: int, sig: str) -> bool:
    expected = _folder_photo_sign(fid, pid, exp)
    try:
        if not _hmac.compare_digest(expected, (sig or '').lower()):
            return False
    except Exception:
        return False
    return int(time.time()) <= int(exp)


class FolderEmailSettingsIn(BaseModel):
    to_sav: Optional[str] = None
    to_quai: Optional[str] = None
    subject_prefix: Optional[str] = None


class FolderEmailTemplateIn(BaseModel):
    label: str
    body: str
    sort_order: Optional[int] = 0


class FolderEmailSignIn(BaseModel):
    positions: List[int] = []


def _folder_email_template_public(row: Dict[str, Any]) -> Dict[str, Any]:
    return {
        "id": row['id'],
        "label": row.get('label') or '',
        "body": row.get('body') or '',
        "sort_order": int(row.get('sort_order') or 0),
        "created_at": dt_to_iso(row.get('created_at')) if row.get('created_at') else None,
        "updated_at": dt_to_iso(row.get('updated_at')) if row.get('updated_at') else None,
    }


@api_router.get("/folder-email/config")
async def folder_email_config(user=Depends(require_bureau)):
    """Renvoie config + templates + flag can_manage pour le user courant."""
    to_sav = await get_setting('folder_email_to_sav', '')
    to_quai = await get_setting('folder_email_to_quai', '')
    subject_prefix = await get_setting('folder_email_subject_prefix', 'EM')
    rows = await fetch_all(
        "SELECT * FROM folder_email_templates ORDER BY sort_order ASC, id ASC",
    )
    can_manage = (user.get('username') or '').lower() == SUPER_ADMIN_USERNAME
    if not can_manage:
        r = await fetch_one(
            "SELECT can_manage_folder_email FROM users WHERE id=%s", (user['id'],)
        )
        can_manage = bool(r and int(r.get('can_manage_folder_email') or 0))
    return {
        "to_sav": to_sav,
        "to_quai": to_quai,
        "subject_prefix": subject_prefix or 'EM',
        "templates": [_folder_email_template_public(r) for r in rows],
        "can_manage": can_manage,
    }


@api_router.put("/folder-email/settings")
async def folder_email_set_settings(
    data: FolderEmailSettingsIn,
    _user=Depends(require_can_manage_folder_email),
):
    if data.to_sav is not None:
        await set_setting('folder_email_to_sav', (data.to_sav or '').strip())
    if data.to_quai is not None:
        await set_setting('folder_email_to_quai', (data.to_quai or '').strip())
    if data.subject_prefix is not None:
        await set_setting('folder_email_subject_prefix',
                          (data.subject_prefix or 'EM').strip() or 'EM')
    return {
        "to_sav": await get_setting('folder_email_to_sav', ''),
        "to_quai": await get_setting('folder_email_to_quai', ''),
        "subject_prefix": await get_setting('folder_email_subject_prefix', 'EM'),
    }


@api_router.post("/folder-email/templates")
async def folder_email_create_template(
    data: FolderEmailTemplateIn,
    user=Depends(require_can_manage_folder_email),
):
    label = (data.label or '').strip()
    body = (data.body or '').strip()
    if not label or not body:
        raise HTTPException(status_code=400, detail="Libellé et contenu requis")
    now = now_dt()
    tid = await execute_returning_id(
        "INSERT INTO folder_email_templates (label, body, sort_order, created_by, created_at, updated_at) "
        "VALUES (%s,%s,%s,%s,%s,%s)",
        (label, body, int(data.sort_order or 0), user['id'], now, now),
    )
    row = await fetch_one("SELECT * FROM folder_email_templates WHERE id=%s", (tid,))
    return _folder_email_template_public(row)


@api_router.patch("/folder-email/templates/{tid}")
async def folder_email_update_template(
    tid: int,
    data: FolderEmailTemplateIn,
    _user=Depends(require_can_manage_folder_email),
):
    existing = await fetch_one("SELECT id FROM folder_email_templates WHERE id=%s", (tid,))
    if not existing:
        raise HTTPException(status_code=404, detail="Modèle introuvable")
    label = (data.label or '').strip()
    body = (data.body or '').strip()
    if not label or not body:
        raise HTTPException(status_code=400, detail="Libellé et contenu requis")
    await execute(
        "UPDATE folder_email_templates SET label=%s, body=%s, sort_order=%s, updated_at=%s WHERE id=%s",
        (label, body, int(data.sort_order or 0), now_dt(), tid),
    )
    row = await fetch_one("SELECT * FROM folder_email_templates WHERE id=%s", (tid,))
    return _folder_email_template_public(row)


@api_router.delete("/folder-email/templates/{tid}")
async def folder_email_delete_template(
    tid: int, _user=Depends(require_can_manage_folder_email),
):
    await execute("DELETE FROM folder_email_templates WHERE id=%s", (tid,))
    return {"ok": True}


@api_router.post("/folders/{fid}/email/sign-photos")
async def folder_email_sign_photos(
    fid: int, data: FolderEmailSignIn, user=Depends(require_bureau),
):
    """Génère des URLs publiques signées (HMAC, valides 30 jours) pour les photos
    sélectionnées d'un dossier. Le destinataire (SAV / Quai externe) peut alors
    cliquer sans s'authentifier."""
    folder = await fetch_one("SELECT id FROM folders WHERE id=%s", (fid,))
    if not folder:
        raise HTTPException(status_code=404, detail="Dossier introuvable")
    positions = data.positions or []
    if not positions:
        return {"photos": []}
    placeholders = ','.join(['%s'] * len(positions))
    rows = await fetch_all(
        f"SELECT id, position FROM folder_photos WHERE folder_id=%s AND position IN ({placeholders}) "
        f"ORDER BY position ASC",
        tuple([fid] + list(positions)),
    )
    exp = int(time.time()) + FOLDER_EMAIL_PHOTO_TTL_DAYS * 86400
    out = []
    for r in rows:
        pid = int(r['id'])
        sig = _folder_photo_sign(fid, pid, exp)
        url = f"/api/public/folder-photo?fid={fid}&pid={pid}&exp={exp}&sig={sig}"
        out.append({"position": int(r['position']), "pid": pid, "url": url})
    return {"photos": out, "ttl_days": FOLDER_EMAIL_PHOTO_TTL_DAYS}


@api_router.get("/public/folder-photo")
async def folder_email_public_photo(
    fid: int, pid: int, exp: int, sig: str,
):
    """Endpoint PUBLIC (pas d'auth) : sert une photo de dossier camion via
    signature HMAC à durée limitée. Utilisé par les liens collés dans les emails
    SAV/Quai pour ne pas obliger les destinataires à se connecter."""
    from fastapi.responses import Response
    if not _folder_photo_verify(fid, pid, exp, sig):
        raise HTTPException(status_code=403, detail="Lien invalide ou expiré")
    row = await fetch_one(
        "SELECT data, folder_id FROM folder_photos WHERE id=%s", (pid,)
    )
    if not row or int(row.get('folder_id') or 0) != int(fid):
        raise HTTPException(status_code=404, detail="Photo introuvable")
    data_url = row['data'] or ''
    # data URLs format : data:image/jpeg;base64,xxxxx
    m = re.match(r'^data:([\w/+\-.]+);base64,(.+)$', data_url, re.S)
    if not m:
        raise HTTPException(status_code=500, detail="Format photo invalide")
    mime = m.group(1) or 'image/jpeg'
    try:
        raw = base64.b64decode(m.group(2))
    except Exception:
        raise HTTPException(status_code=500, detail="Décodage photo impossible")
    return Response(
        content=raw,
        media_type=mime,
        headers={
            "Cache-Control": "private, max-age=86400",
            "Content-Disposition": f'inline; filename="dossier-{fid}-{pid}.jpg"',
        },
    )


# ============================================================================
# XDock+ CS Status — migration depuis alwaysdata
# ----------------------------------------------------------------------------
# Réplique 1:1 le comportement de l'ancien cs_status_backend.php :
#   GET  /api/xdock/cs-status         → renvoie {tour_id: bool, ...}
#   POST /api/xdock/cs-status         → body {tournee, cs} → upsert
# Pas d'authentification (comme le PHP) — l'extension XDock+ a déjà
# `www.gp-eff.fr` dans ses host_permissions MV3.
# ============================================================================
class CsStatusIn(BaseModel):
    tournee: str
    cs: bool


@api_router.get("/xdock/cs-status")
async def xdock_cs_status_get():
    rows = await fetch_all("SELECT tour_id, has_cs FROM xdock_cs_status")
    return {str(r['tour_id']): bool(int(r.get('has_cs') or 0)) for r in rows}


@api_router.post("/xdock/cs-status")
async def xdock_cs_status_post(data: CsStatusIn):
    tour = (data.tournee or '').strip()
    if not tour:
        raise HTTPException(status_code=400, detail="tournee invalide")
    cs = 1 if data.cs else 0
    await execute(
        "INSERT INTO xdock_cs_status (tour_id, has_cs, updated_at) "
        "VALUES (%s, %s, %s) "
        "ON DUPLICATE KEY UPDATE has_cs=VALUES(has_cs), updated_at=VALUES(updated_at)",
        (tour, cs, now_dt()),
    )
    return {"success": True, "tournee": tour, "cs": bool(cs)}


# Même principe pour AB (migration depuis ab_status_backend.php)
class AbStatusIn(BaseModel):
    tournee: str
    ab: bool


@api_router.get("/xdock/ab-status")
async def xdock_ab_status_get():
    rows = await fetch_all("SELECT tour_id, has_ab FROM xdock_ab_status")
    return {str(r['tour_id']): bool(int(r.get('has_ab') or 0)) for r in rows}


@api_router.post("/xdock/ab-status")
async def xdock_ab_status_post(data: AbStatusIn):
    tour = (data.tournee or '').strip()
    if not tour:
        raise HTTPException(status_code=400, detail="tournee invalide")
    ab = 1 if data.ab else 0
    await execute(
        "INSERT INTO xdock_ab_status (tour_id, has_ab, updated_at) "
        "VALUES (%s, %s, %s) "
        "ON DUPLICATE KEY UPDATE has_ab=VALUES(has_ab), updated_at=VALUES(updated_at)",
        (tour, ab, now_dt()),
    )
    return {"success": True, "tournee": tour, "ab": bool(ab)}


# ============================================================================
# XDock+ Contacts — migration depuis contacts/api.php (alwaysdata)
# ----------------------------------------------------------------------------
#  Utilise les TABLES EXISTANTES dans la DB `gp-eff_num` :
#    - `gp-eff_num`.`contacts_xdock` (id, name, phone (chiffré AES-256-CBC),
#      grp ENUM, group_id, sort_order, created_at, updated_at)
#    - `gp-eff_num`.`gp_groups` (id, name, sort_order, created_at)
#  Chiffrement des téléphones conservé pour rester compatible avec les
#  enregistrements existants. Format identique au PHP : openssl_encrypt
#  (AES-256-CBC) + base64 standard.
# ----------------------------------------------------------------------------
#  GET  /api/xdock/contacts            → { "GroupeName": [contacts...], ... }
#  POST /api/xdock/contacts            → body {action, ...}
# ============================================================================

# Schéma cross-DB pleinement qualifié pour les requêtes ci-dessous
CONTACTS_DB = os.environ.get('XDOCK_CONTACTS_DB', 'gp-eff_num')
CONTACTS_TBL = f"`{CONTACTS_DB}`.`contacts_xdock`"
GROUPS_TBL = f"`{CONTACTS_DB}`.`gp_groups`"

# Crypto : valeurs reprises du PHP legacy. Override possible via env vars.
CONTACTS_ENC_KEY = os.environ.get(
    'XDOCK_CONTACTS_ENC_KEY',
    'change_me_with_a_very_long_random_key_32+_chars_________',
).encode()[:32]
CONTACTS_ENC_IV = os.environ.get(
    'XDOCK_CONTACTS_ENC_IV', '1234567890ABCDEF',
).encode()[:16]
LEGACY_GRP_ENUM = {'Matin', 'Après-midi', 'Nuit', 'Prestation', 'Groupage'}


def _phone_encrypt(plain: str) -> str:
    if not plain:
        return ''
    from cryptography.hazmat.primitives.ciphers import Cipher, algorithms, modes
    from cryptography.hazmat.primitives import padding as _padding
    padder = _padding.PKCS7(128).padder()
    pt = padder.update(plain.encode('utf-8')) + padder.finalize()
    cipher = Cipher(algorithms.AES(CONTACTS_ENC_KEY), modes.CBC(CONTACTS_ENC_IV))
    enc = cipher.encryptor()
    ct = enc.update(pt) + enc.finalize()
    return base64.b64encode(ct).decode('ascii')


def _phone_decrypt_maybe(stored: str) -> str:
    if not stored:
        return ''
    # déjà en clair (compat ancien)
    if re.match(r'^\+\d{6,20}$', stored):
        return stored
    try:
        from cryptography.hazmat.primitives.ciphers import Cipher, algorithms, modes
        from cryptography.hazmat.primitives import padding as _padding
        ct = base64.b64decode(stored)
        cipher = Cipher(algorithms.AES(CONTACTS_ENC_KEY), modes.CBC(CONTACTS_ENC_IV))
        dec = cipher.decryptor()
        pt_padded = dec.update(ct) + dec.finalize()
        unp = _padding.PKCS7(128).unpadder()
        pt = unp.update(pt_padded) + unp.finalize()
        s = pt.decode('utf-8', errors='replace')
        if re.match(r'^\+\d{6,20}$', s):
            return s
    except Exception:
        pass
    return ''


def _norm_phone(p: str) -> str:
    digits = re.sub(r'[^\d+]', '', p or '')
    if not digits:
        return ''
    return digits if digits.startswith('+') else '+' + digits.lstrip('+')


def _clamp_legacy_enum(name: str) -> str:
    return name if name in LEGACY_GRP_ENUM else 'Matin'


async def _xdock_contacts_get_or_create_default_group() -> int:
    row = await fetch_one(
        f"SELECT id FROM {GROUPS_TBL} ORDER BY sort_order, name LIMIT 1"
    )
    if row:
        return int(row['id'])
    gid = await execute_returning_id(
        f"INSERT INTO {GROUPS_TBL} (name, sort_order, created_at) VALUES (%s, %s, %s)",
        ('Matin', 1, now_dt()),
    )
    return int(gid)


async def _xdock_contacts_normalize_group_id(group_id: Optional[int]) -> int:
    if group_id:
        row = await fetch_one(
            f"SELECT id FROM {GROUPS_TBL} WHERE id=%s", (group_id,)
        )
        if row:
            return int(row['id'])
    return await _xdock_contacts_get_or_create_default_group()


@api_router.get("/xdock/contacts")
async def xdock_contacts_get():
    # On retourne le même format que le PHP : objet groupé par nom de groupe
    try:
        groups = await fetch_all(
            f"SELECT id, name, sort_order FROM {GROUPS_TBL} ORDER BY sort_order, name"
        )
    except Exception as e:
        logging.warning(f"xdock_contacts_get groups query failed: {e}")
        return {}
    if not groups:
        await _xdock_contacts_get_or_create_default_group()
        groups = await fetch_all(
            f"SELECT id, name, sort_order FROM {GROUPS_TBL} ORDER BY sort_order, name"
        )
    out: Dict[str, List[Dict[str, Any]]] = {g['name']: [] for g in groups}
    id_to_name = {int(g['id']): g['name'] for g in groups}

    contacts = await fetch_all(
        f"SELECT c.id, c.name, c.phone, c.group_id, c.sort_order, g.sort_order AS g_sort "
        f"FROM {CONTACTS_TBL} c "
        f"LEFT JOIN {GROUPS_TBL} g ON g.id = c.group_id "
        f"ORDER BY g.sort_order IS NULL, g_sort, c.sort_order, c.name"
    )
    for c in contacts:
        gname = id_to_name.get(int(c.get('group_id') or 0), 'Matin')
        if gname not in out:
            out[gname] = []
        out[gname].append({
            "id": int(c['id']),
            "name": c.get('name') or '',
            "phone": _phone_decrypt_maybe(c.get('phone') or ''),
            "group": gname,
            "group_id": int(c.get('group_id') or 0),
            "sort_order": int(c.get('sort_order') or 0),
        })
    return out


@api_router.post("/xdock/contacts")
async def xdock_contacts_post(request: Request):
    """Dispatcher d'actions (mêmes noms que l'ancien api.php)."""
    try:
        body = await request.json()
    except Exception:
        body = {}
    action = (body.get('action') or '').strip()

    if action == 'upsert':
        c = body.get('contact') or {}
        cid = int(c.get('id') or 0)
        name = (c.get('name') or '').strip()
        phone = _norm_phone(c.get('phone') or '')
        gid_req = c.get('group_id')
        gid = int(gid_req) if gid_req else None
        if not name or not phone:
            return {"ok": False, "error": "invalid_fields"}
        gid = await _xdock_contacts_normalize_group_id(gid)
        enc_phone = _phone_encrypt(phone)
        # doublon téléphone (hors même id) — comparaison sur valeur chiffrée
        # (déterministe car IV fixe, identique au PHP legacy)
        if cid > 0:
            dup = await fetch_one(
                f"SELECT id FROM {CONTACTS_TBL} WHERE phone=%s AND id<>%s",
                (enc_phone, cid),
            )
        else:
            dup = await fetch_one(
                f"SELECT id FROM {CONTACTS_TBL} WHERE phone=%s", (enc_phone,)
            )
        if dup:
            return {"ok": False, "error": "duplicate_phone"}
        # nom du groupe pour la colonne legacy `grp` (ENUM)
        grow = await fetch_one(f"SELECT name FROM {GROUPS_TBL} WHERE id=%s", (gid,))
        grp_legacy = _clamp_legacy_enum((grow or {}).get('name') or 'Matin')
        if cid > 0:
            await execute(
                f"UPDATE {CONTACTS_TBL} SET name=%s, phone=%s, grp=%s, group_id=%s WHERE id=%s",
                (name, enc_phone, grp_legacy, gid, cid),
            )
            return {"ok": True}
        # nouveau : sort_order = fin du groupe
        max_row = await fetch_one(
            f"SELECT COALESCE(MAX(sort_order),0) AS m FROM {CONTACTS_TBL} WHERE group_id=%s",
            (gid,),
        )
        max_so = int((max_row or {}).get('m') or 0)
        new_id = await execute_returning_id(
            f"INSERT INTO {CONTACTS_TBL} (name, phone, grp, group_id, sort_order) "
            f"VALUES (%s, %s, %s, %s, %s)",
            (name, enc_phone, grp_legacy, gid, max_so + 1),
        )
        return {"ok": True, "id": int(new_id)}

    if action == 'delete':
        cid = int(body.get('id') or 0)
        if cid <= 0:
            return {"ok": False, "error": "invalid_id"}
        await execute(f"DELETE FROM {CONTACTS_TBL} WHERE id=%s", (cid,))
        return {"ok": True}

    if action == 'contacts.reorder':
        gid = int(body.get('group_id') or 0)
        ids = body.get('ids') or []
        if gid <= 0 or not isinstance(ids, list) or not ids:
            return {"ok": False, "error": "invalid_params"}
        pos = 1
        for cid in ids:
            try:
                cid_int = int(cid)
            except (TypeError, ValueError):
                continue
            await execute(
                f"UPDATE {CONTACTS_TBL} SET sort_order=%s WHERE id=%s AND group_id=%s",
                (pos, cid_int, gid),
            )
            pos += 1
        return {"ok": True}

    if action == 'contacts.move':
        cid = int(body.get('id') or 0)
        to_gid_req = int(body.get('to_group_id') or 0)
        if cid <= 0 or to_gid_req <= 0:
            return {"ok": False, "error": "invalid_params"}
        to_gid = await _xdock_contacts_normalize_group_id(to_gid_req)
        max_row = await fetch_one(
            f"SELECT COALESCE(MAX(sort_order),0) AS m FROM {CONTACTS_TBL} WHERE group_id=%s",
            (to_gid,),
        )
        max_so = int((max_row or {}).get('m') or 0)
        grow = await fetch_one(f"SELECT name FROM {GROUPS_TBL} WHERE id=%s", (to_gid,))
        grp_legacy = _clamp_legacy_enum((grow or {}).get('name') or 'Matin')
        await execute(
            f"UPDATE {CONTACTS_TBL} SET group_id=%s, grp=%s, sort_order=%s WHERE id=%s",
            (to_gid, grp_legacy, max_so + 1, cid),
        )
        return {"ok": True}

    if action == 'groups.list':
        rows = await fetch_all(
            f"SELECT id, name, sort_order, created_at FROM {GROUPS_TBL} "
            f"ORDER BY sort_order, name"
        )
        return {
            "ok": True,
            "data": [
                {
                    "id": int(r['id']),
                    "name": r.get('name') or '',
                    "sort_order": int(r.get('sort_order') or 0),
                    "created_at": dt_to_iso(r.get('created_at')) if r.get('created_at') else None,
                }
                for r in rows
            ],
        }

    if action == 'groups.create':
        name = (body.get('name') or '').strip()
        if not name:
            return {"ok": False, "error": "invalid_name"}
        existing = await fetch_one(
            f"SELECT id FROM {GROUPS_TBL} WHERE name=%s", (name,)
        )
        if existing:
            return {"ok": False, "error": "duplicate"}
        max_row = await fetch_one(
            f"SELECT COALESCE(MAX(sort_order),0) AS m FROM {GROUPS_TBL}"
        )
        max_so = int((max_row or {}).get('m') or 0)
        gid = await execute_returning_id(
            f"INSERT INTO {GROUPS_TBL} (name, sort_order, created_at) VALUES (%s, %s, %s)",
            (name, max_so + 1, now_dt()),
        )
        return {"ok": True, "id": int(gid)}

    if action == 'groups.rename':
        gid = int(body.get('id') or 0)
        name = (body.get('name') or '').strip()
        if gid <= 0 or not name:
            return {"ok": False, "error": "invalid_params"}
        dup = await fetch_one(
            f"SELECT id FROM {GROUPS_TBL} WHERE name=%s AND id<>%s",
            (name, gid),
        )
        if dup:
            return {"ok": False, "error": "duplicate"}
        await execute(
            f"UPDATE {GROUPS_TBL} SET name=%s WHERE id=%s", (name, gid)
        )
        # met à jour la colonne legacy `grp` des contacts du groupe
        new_grp = _clamp_legacy_enum(name)
        await execute(
            f"UPDATE {CONTACTS_TBL} SET grp=%s WHERE group_id=%s",
            (new_grp, gid),
        )
        return {"ok": True}

    if action == 'groups.delete':
        gid = int(body.get('id') or 0)
        if gid <= 0:
            return {"ok": False, "error": "invalid_id"}
        fallback = await _xdock_contacts_get_or_create_default_group()
        if fallback == gid:
            other = await fetch_one(
                f"SELECT id FROM {GROUPS_TBL} WHERE id<>%s ORDER BY sort_order LIMIT 1",
                (gid,),
            )
            if other:
                fallback = int(other['id'])
            else:
                new_id = await execute_returning_id(
                    f"INSERT INTO {GROUPS_TBL} (name, sort_order, created_at) "
                    f"VALUES (%s, %s, %s)",
                    ('Matin', 1, now_dt()),
                )
                fallback = int(new_id)
        # noms pour grp legacy
        grow = await fetch_one(f"SELECT name FROM {GROUPS_TBL} WHERE id=%s", (fallback,))
        grp_legacy = _clamp_legacy_enum((grow or {}).get('name') or 'Matin')
        await execute(
            f"UPDATE {CONTACTS_TBL} SET group_id=%s, grp=%s WHERE group_id=%s",
            (fallback, grp_legacy, gid),
        )
        await execute(f"DELETE FROM {GROUPS_TBL} WHERE id=%s", (gid,))
        return {"ok": True}

    if action == 'groups.reorder':
        ids = body.get('ids') or []
        if not isinstance(ids, list) or not ids:
            return {"ok": False, "error": "invalid_ids"}
        pos = 1
        for gid in ids:
            try:
                gid_int = int(gid)
            except (TypeError, ValueError):
                continue
            await execute(
                f"UPDATE {GROUPS_TBL} SET sort_order=%s WHERE id=%s",
                (pos, gid_int),
            )
            pos += 1
        return {"ok": True}

    return {"ok": False, "error": "unknown_action"}


# --- Startup ---
@app.on_event("startup")
async def startup_tasks():
    # Wait briefly for DB to be reachable
    for attempt in range(30):
        try:
            await fetch_one("SELECT 1")
            break
        except Exception as e:
            logging.warning(f"DB not ready (attempt {attempt + 1}/30): {e}")
            await asyncio.sleep(2)

    await ensure_schema()
    await ensure_vapid_keys()
    _start_scheduler()
    # Apply saved archive time (if user previously customised it)
    try:
        await _reschedule_archive_job()
    except Exception as e:
        logging.warning(f"initial reschedule failed: {e}")

@app.on_event("shutdown")
async def shutdown_db():
    global _pool, _scheduler
    if _scheduler is not None:
        try:
            _scheduler.shutdown(wait=False)
        except Exception:
            pass
        _scheduler = None
    if _pool is not None:
        _pool.close()
        await _pool.wait_closed()
        _pool = None

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

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