"""
3D Printing Pricing & Planning App
====================================
Flask/SQLite application for managing 3D print job costing, scheduling,
and Home Assistant sync for a small print shop.

Architecture:
  - Flask routes (server-rendered Jinja2 templates)
  - SQLite database with WAL mode (concurrent read/write safe)
  - Background thread for Home Assistant polling
  - vis.js Timeline Gantt on the planning page
  - Optional OIDC authentication via Authelia

Key modules:
  calc()           — core pricing engine (per piece, with fiscal charges)
  parse_3mf()      — Bambu Studio .3mf file parser
  find_next_slot() — automatic next available printer slot finder
  _ha_sync_once()  — Home Assistant state → slot status synchronization
  planning()       — Gantt + unscheduled jobs view
"""

from flask import Flask, render_template, request, redirect, url_for, jsonify, Response, session, flash
import sqlite3, csv, io, os, zipfile, re, math, secrets, functools, threading, time
try:
    import requests as _requests
except ImportError:
    _requests = None
from datetime import datetime, timedelta, date as date_cls, timezone
from authlib.integrations.flask_client import OAuth

app = Flask(__name__)
app.secret_key = os.environ.get('SECRET_KEY', secrets.token_hex(32))
DATABASE = os.environ.get('DATABASE_PATH', '/data/pricing.db')

# ─── OIDC ─────────────────────────────────────────────────────────────────────
OIDC_ENABLED = bool(os.environ.get('OIDC_CLIENT_ID'))

oauth = OAuth(app)
if OIDC_ENABLED:
    oauth.register(
        name='authelia',
        client_id=os.environ.get('OIDC_CLIENT_ID'),
        client_secret=os.environ.get('OIDC_CLIENT_SECRET'),
        server_metadata_url=os.environ.get('OIDC_DISCOVERY_URL'),
        client_kwargs={
            'scope': 'openid profile email',
            'code_challenge_method': 'S256',  # PKCE
        },
    )

def login_required(f):
    @functools.wraps(f)
    def decorated(*args, **kwargs):
        if OIDC_ENABLED and 'user' not in session:
            session['next'] = request.url
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated

@app.route('/login')
def login():
    if not OIDC_ENABLED:
        return redirect(url_for('dashboard'))
    redirect_uri = url_for('auth_callback', _external=True)
    return oauth.authelia.authorize_redirect(redirect_uri)

@app.route('/auth/callback')
def auth_callback():
    token = oauth.authelia.authorize_access_token()
    userinfo = token.get('userinfo') or oauth.authelia.userinfo()
    session['user'] = {
        'sub':   userinfo.get('sub', ''),
        'name':  userinfo.get('name') or userinfo.get('preferred_username', 'Utilisateur'),
        'email': userinfo.get('email', ''),
    }
    next_url = session.pop('next', None)
    return redirect(next_url or url_for('dashboard'))

@app.route('/logout')
def logout():
    session.clear()
    if OIDC_ENABLED:
        end_session = os.environ.get('OIDC_END_SESSION_URL', '')
        if end_session:
            post_logout = url_for('login', _external=True)
            return redirect(f"{end_session}?post_logout_redirect_uri={post_logout}")
    return redirect(url_for('login'))

# Routes accessibles sans login
_OPEN_ROUTES = {'login', 'auth_callback', 'logout', 'static', 'client_portal', 'calendar_ics'}

@app.before_request
def require_login_global():
    if OIDC_ENABLED and request.endpoint not in _OPEN_ROUTES and 'user' not in session:
        session['next'] = request.url
        return redirect(url_for('login'))

def get_db():
    """
    Ouvre une connexion SQLite avec WAL mode activé.
    WAL (Write-Ahead Log) permet lectures et écritures simultanées
    sans blocage — critique pour le thread HA en arrière-plan.
    Timeout 30s pour attendre si une autre écriture est en cours.
    """
    conn = sqlite3.connect(DATABASE, timeout=30)
    conn.row_factory = sqlite3.Row
    conn.execute('PRAGMA journal_mode=WAL')
    conn.execute('PRAGMA synchronous=NORMAL')
    return conn

def init_db():
    os.makedirs(os.path.dirname(DATABASE), exist_ok=True)
    conn = get_db()
    conn.executescript('''
        CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT);
        CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
            deadline TEXT DEFAULT NULL,
            notes TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS clients (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL, email TEXT DEFAULT '',
            design_multiplier REAL DEFAULT 1.80, notes TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS materials (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL, brand TEXT DEFAULT '', type TEXT DEFAULT 'PLA',
            color TEXT DEFAULT '', price_per_kg REAL NOT NULL DEFAULT 25.0,
            stock_g REAL DEFAULT 0, low_stock_threshold_g REAL DEFAULT 200,
            notes TEXT DEFAULT '', created_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS material_price_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            material_id INTEGER REFERENCES materials(id) ON DELETE CASCADE,
            price_per_kg REAL NOT NULL, recorded_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS machine_profiles (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            printer_price REAL DEFAULT 400.0,
            printer_lifespan_hours REAL DEFAULT 5000,
            nozzle_price REAL DEFAULT 10.0,
            nozzle_lifespan_hours REAL DEFAULT 250,
            plate_price REAL DEFAULT 25.0,
            plate_lifespan_hours REAL DEFAULT 1000,
            printer_power_kw REAL DEFAULT 0.35,
            electricity_price_kwh REAL DEFAULT 0.18,
            notes TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS handling_profiles (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            handling_rate_per_hour REAL DEFAULT 40.0,
            handling_minutes_per_plate REAL DEFAULT 5,
            notes TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS material_profiles (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            material_margin_pct REAL DEFAULT 20.0,
            notes TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS pricing_profiles (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            design_multiplier REAL DEFAULT 1.80,
            gross_margin_pct REAL DEFAULT 27.5,
            notes TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS jobs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
            material_id INTEGER REFERENCES materials(id) ON DELETE SET NULL,
            machine_profile_id INTEGER REFERENCES machine_profiles(id) ON DELETE SET NULL,
            handling_profile_id INTEGER REFERENCES handling_profiles(id) ON DELETE SET NULL,
            material_profile_id INTEGER REFERENCES material_profiles(id) ON DELETE SET NULL,
            pricing_profile_id INTEGER REFERENCES pricing_profiles(id) ON DELETE SET NULL,
            source_file TEXT DEFAULT '', plate_name TEXT DEFAULT '', name TEXT NOT NULL,
            description TEXT DEFAULT '', weight_g REAL NOT NULL,
            print_time_s INTEGER NOT NULL, design_multiplier REAL NOT NULL,
            gross_margin_pct REAL NOT NULL DEFAULT 27.5, discount_pct REAL DEFAULT 0,
            pieces_per_plate INTEGER DEFAULT 1, order_qty INTEGER DEFAULT 1,
            material_cost REAL, material_margin REAL, design_cost REAL,
            handling_cost REAL, wear_cost REAL, electricity_cost REAL,
            cout_fixe REAL, total_marge REAL,
            subtotal REAL, margin_amount REAL,
            cotisations_amount REAL, vfl_amount REAL, tva_amount REAL,
            other_taxes_amount REAL,
            tax_amount REAL, price_per_piece REAL, final_price REAL,
            notes TEXT DEFAULT '', created_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS printers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            is_active INTEGER DEFAULT 1,
            notes TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS working_schedule (
            day_of_week INTEGER PRIMARY KEY,
            mode TEXT DEFAULT 'onsite',
            tt_window_start TEXT DEFAULT '10:00',
            tt_window_end TEXT DEFAULT '22:00',
            onsite_slot_1 TEXT DEFAULT '07:00',
            onsite_slot_2 TEXT DEFAULT '18:30'
        );
        CREATE TABLE IF NOT EXISTS calendar_blocks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT NOT NULL,
            type TEXT DEFAULT 'off',
            printer_id INTEGER,
            notes TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS print_slots (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            job_id INTEGER REFERENCES jobs(id) ON DELETE CASCADE,
            printer_id INTEGER REFERENCES printers(id) ON DELETE SET NULL,
            planned_start TEXT NOT NULL,
            planned_end TEXT NOT NULL,
            status TEXT DEFAULT 'planned',
            failure_note TEXT DEFAULT '',
            created_at TEXT DEFAULT (datetime('now'))
        );
    ''')

    defaults = [
        ('filament_price_per_kg','25.0'),('electricity_price_kwh','0.18'),
        ('printer_price','400.0'),('printer_lifespan_hours','5000'),
        ('nozzle_price','10.0'),('nozzle_lifespan_hours','250'),
        ('plate_price','25.0'),('plate_lifespan_hours','1000'),
        ('handling_rate_per_hour','40.0'),('handling_minutes_per_plate','5'),
        ('material_margin_pct','20.0'),
        ('cotisations_rate_pct','12.3'),
        ('vfl_rate_pct','1.0'),
        ('tva_rate_pct','20.0'),
        ('other_taxes_rate_pct','1.0'),
        ('printer_power_kw','0.35'),
        ('cooldown_minutes','15'),
        ('failure_buffer_pct','10'),
        ('max_simultaneous_printers','5'),
        ('nc_url',''),
        ('nc_username',''),
        ('nc_app_password',''),
        ('nc_root_path','/3D'),
        ('ical_token',''),
        ('ha_url',''),
        ('ha_token',''),
        ('ha_poll_interval','60'),
    ]
    conn.executemany('INSERT OR IGNORE INTO settings VALUES (?,?)', defaults)

    # Default printers: 6 A1, #1-#5 active, #6 inactive
    for i in range(1, 7):
        conn.execute('INSERT OR IGNORE INTO printers(id,name,is_active) VALUES(?,?,?)',
                     (i, f'A1 #{i}', 1 if i <= 5 else 0))

    # Default working schedule: Mon/Wed/Fri = TT, others = onsite
    tt_days = {0, 2, 4}
    for dow in range(7):
        mode = 'tt' if dow in tt_days else 'onsite'
        conn.execute('''INSERT OR IGNORE INTO working_schedule
            (day_of_week,mode,tt_window_start,tt_window_end,onsite_slot_1,onsite_slot_2)
            VALUES(?,?,?,?,?,?)''', (dow, mode, '10:00', '22:00', '07:00', '18:30'))

    # Migrations
    migrations = [
        'ALTER TABLE jobs ADD COLUMN machine_profile_id INTEGER',
        'ALTER TABLE jobs ADD COLUMN handling_profile_id INTEGER',
        'ALTER TABLE jobs ADD COLUMN material_profile_id INTEGER',
        'ALTER TABLE jobs ADD COLUMN pricing_profile_id INTEGER',
        'ALTER TABLE jobs ADD COLUMN cout_fixe REAL',
        'ALTER TABLE jobs ADD COLUMN total_marge REAL',
        'ALTER TABLE jobs ADD COLUMN cotisations_amount REAL',
        'ALTER TABLE jobs ADD COLUMN vfl_amount REAL',
        'ALTER TABLE jobs ADD COLUMN tva_amount REAL',
        'ALTER TABLE jobs ADD COLUMN other_taxes_amount REAL',
        'ALTER TABLE jobs ADD COLUMN pieces_per_plate INTEGER DEFAULT 1',
        'ALTER TABLE jobs ADD COLUMN order_qty INTEGER DEFAULT 1',
        'ALTER TABLE jobs ADD COLUMN price_per_piece REAL',
        'ALTER TABLE clients ADD COLUMN default_machine_profile_id INTEGER',
        'ALTER TABLE clients ADD COLUMN default_handling_profile_id INTEGER',
        'ALTER TABLE clients ADD COLUMN default_material_profile_id INTEGER',
        'ALTER TABLE clients ADD COLUMN default_pricing_profile_id INTEGER',
        'ALTER TABLE jobs ADD COLUMN project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL',
        'ALTER TABLE print_slots ADD COLUMN wasted_g REAL DEFAULT 0',
        'ALTER TABLE materials ADD COLUMN total_wasted_g REAL DEFAULT 0',
        'ALTER TABLE jobs ADD COLUMN client_token TEXT DEFAULT NULL',
        'ALTER TABLE jobs ADD COLUMN plate_name TEXT DEFAULT ""',
        'ALTER TABLE jobs ADD COLUMN marge_pct_on_ht REAL DEFAULT 0',
        'ALTER TABLE printers ADD COLUMN ha_entity_prefix TEXT DEFAULT ""',
        'ALTER TABLE print_slots ADD COLUMN pieces_ignored INTEGER DEFAULT 0',
        'ALTER TABLE print_slots ADD COLUMN pieces_override INTEGER DEFAULT NULL',
        'ALTER TABLE calendar_blocks ADD COLUMN date_end TEXT DEFAULT NULL',
        'ALTER TABLE jobs ADD COLUMN source_nc_path TEXT DEFAULT ""',
    ]
    for sql in migrations:
        try:
            conn.execute(sql)
        except Exception:
            pass

    conn.commit(); conn.close()

def get_settings():
    conn = get_db()
    rows = conn.execute('SELECT key,value FROM settings').fetchall()
    conn.close()
    result = {}
    for r in rows:
        try:
            result[r['key']] = float(r['value'])
        except (ValueError, TypeError):
            result[r['key']] = r['value'] or ''
    return result

def parse_3mf(file_stream):
    """
    Parse un fichier .3mf Bambu Studio.
    Retourne un dict avec :
      - plates : liste de plaques [{index, weight_g, print_time_s, filaments:[{type,color,used_g}]}]
      - fallback : {weight_g, hours, minutes} si slice_info absent
      - warning : message si le fichier n'a pas été slicé
    """
    result = {'plates': [], 'weight_g': None, 'hours': None, 'minutes': None, 'warning': None}
    try:
        with zipfile.ZipFile(file_stream) as z:
            names = z.namelist()

            # ── 0. Lire les noms custom depuis model_settings.config ────────
            # Bambu stocke le nom de plateau dans plater_name (pas dans slice_info)
            plater_names = {}  # {plate_index: name}
            model_settings_candidates = [n for n in names if 'model_settings' in n.lower() and n.endswith('.config')]
            if model_settings_candidates:
                try:
                    ms_raw = z.read(model_settings_candidates[0]).decode('utf-8', errors='ignore')
                    ms_root = ET.fromstring(ms_raw)
                    for plate_el in ms_root.findall('plate'):
                        ms_meta = {m.get('key'): m.get('value') for m in plate_el.findall('metadata')}
                        pid = int(ms_meta.get('plater_id', 0))
                        pname = ms_meta.get('plater_name', '') or ''
                        if pid and pname:
                            plater_names[pid] = pname
                except Exception:
                    pass

            # ── 1. Bambu slice_info.config (format XML par plaque) ──────────
            slice_info_candidates = [n for n in names if 'slice_info' in n.lower() and n.endswith('.config')]
            if slice_info_candidates:
                raw = z.read(slice_info_candidates[0]).decode('utf-8', errors='ignore')
                try:
                    root = ET.fromstring(raw)
                    for plate_el in root.findall('plate'):
                        plate = {}
                        meta = {m.get('key'): m.get('value') for m in plate_el.findall('metadata')}
                        plate['index']        = int(meta.get('index', 0))
                        plate['print_time_s'] = int(meta.get('prediction', 0))
                        # Nom custom : priorité à model_settings, fallback sur slice_info
                        plate['name'] = plater_names.get(plate['index'], '') or meta.get('name', '') or ''

                        filaments = []
                        total_g = 0.0
                        for fil in plate_el.findall('filament'):
                            used_g = float(fil.get('used_g', 0) or 0)
                            total_g += used_g
                            filaments.append({
                                'id':     fil.get('id', ''),
                                'type':   fil.get('type', ''),
                                'color':  fil.get('color', ''),
                                'used_g': round(used_g, 2),
                                'used_m': round(float(fil.get('used_m', 0) or 0), 2),
                            })
                        plate['weight_g']  = round(total_g, 2)
                        plate['filaments'] = filaments
                        # Pièces : compter les <object> non skippés
                        objs = [o for o in plate_el.findall('object') if o.get('skipped') != 'true']
                        plate['pieces'] = len(objs)
                        # Noms uniques des modèles (sans extension)
                        plate['object_names'] = list(dict.fromkeys(
                            o.get('name', '').rsplit('.', 1)[0]
                            for o in objs if o.get('name')
                        ))
                        # Libellé lisible
                        h  = plate['print_time_s'] // 3600
                        mn = (plate['print_time_s'] % 3600) // 60
                        name_part = f" «{plate['name']}»" if plate['name'] else ''
                        plate['label'] = (
                            f"Plateau {plate['index']}{name_part}"
                            f" — {plate['weight_g']} g"
                            f" — {h}h{mn:02d}m"
                            f" — {plate['pieces']} pce(s)"
                        )
                        result['plates'].append(plate)
                    result['plates'].sort(key=lambda p: p['index'])
                except Exception:
                    pass

            # ── 2. Fallback regex si slice_info absent ──────────────────────
            if not result['plates']:
                for name in names:
                    if not any(name.endswith(e) for e in ['.xml', '.model', '.config', '.json', '.txt']):
                        continue
                    try:
                        raw = z.read(name).decode('utf-8', errors='ignore')
                    except Exception:
                        continue
                    if result['weight_g'] is None:
                        for pat in [
                            r'filament[_\s]*weight[^>]*?["\s](\d+[\.,]\d+)',
                            r'used\s*\[g\][^"]*"\s*value\s*=\s*"(\d+[\.,]\d+)',
                            r'"weight"\s*[:=]\s*"?(\d+[\.,]\d+)',
                            r'(\d+[\.,]\d+)\s*g\b(?!\s*[/])',
                        ]:
                            m = re.search(pat, raw, re.IGNORECASE)
                            if m:
                                try:
                                    result['weight_g'] = float(m.group(1).replace(',', '.'))
                                    break
                                except ValueError:
                                    pass
                    if result['hours'] is None:
                        for pat in [
                            r'(\d+)h\s*(\d+)m',
                            r'(\d+)\s*hours?\s*(\d+)\s*min',
                            r'printing.time[^"]*"\s*value\s*=\s*"(\d+)h\s*(\d+)',
                        ]:
                            m = re.search(pat, raw, re.IGNORECASE)
                            if m:
                                try:
                                    h, mn = int(m.group(1)), int(m.group(2))
                                    if 0 <= h <= 500 and 0 <= mn <= 59:
                                        result['hours']   = h
                                        result['minutes'] = mn
                                        break
                                except (ValueError, IndexError):
                                    pass
    except Exception:
        pass
    # Si aucune donnée trouvée, ajouter un avertissement
    if not result['plates'] and result['weight_g'] is None:
        result['warning'] = (
            "Aucune donnée de découpe dans ce fichier. "
            "Dans Bambu Studio : slicez vos plateaux puis utilisez "
            "Fichier → Exporter → Exporter le fichier découpé (pas Sauvegarder le projet). "
            "Le fichier exporté contiendra poids, temps et consommation filament."
        )
    return result

def calc(weight_g, print_time_s, design_mult, gross_margin_pct, discount_pct,
         price_per_kg_override=None, s=None, pieces_per_plate=1):
    """
    Moteur de pricing principal — calcule le prix de vente d'une pièce.

    Flux de calcul (toutes les valeurs sont PAR PIÈCE) :
      1. Coût matière       = prix_kg/1000 × poids_plateau / pièces_plateau
      2. Marge matière      = coût_matière × material_margin_pct
      3. Design             = coût_matière × design_multiplier
      4. Manutention        = taux_horaire × minutes_plateau / 60 / pièces
      5. Usure machine      = (prix_machine / durée_vie) × temps_impression / pièces
      6. Électricité        = puissance_kw × heures × prix_kwh / pièces
      7. Coût fixe          = matière + marge_matière + usure + élec
      8. Sous-total         = coût_fixe + manutention + design
      9. Marge brute        = sous-total × gross_margin_pct / 100
      10. Total marge       = manutention + design + marge_brute
      11. Prix HT           = coût_fixe + total_marge
      12. Prix HT net       = prix_ht / (1 - charges_fiscales)
          (cotisations + VFL + autres taxes sont self-contained dans le prix)
      13. Prix TTC          = prix_ht_net × (1 + tva)
      14. Prix final        = prix_ttc × (1 - remise)

    Args:
      weight_g:              poids total du plateau Bambu (en g)
      print_time_s:          durée d'impression plateau en secondes
      design_mult:           multiplicateur design (ex: 0.8 ou 1.8)
      gross_margin_pct:      marge brute en % (ex: 27.5)
      discount_pct:          remise en % (ex: 10.0)
      price_per_kg_override: prix filament override (sinon settings)
      s:                     dict settings (sinon get_settings() appelé)
      pieces_per_plate:      nb pièces par plateau (divise les coûts fixes)

    Returns: dict avec tous les postes de coût et prix final.
    """
    if s is None:
        s = get_settings()
    if pieces_per_plate < 1:
        pieces_per_plate = 1

    # weight_g et print_time_s = valeurs PLATEAU complet (Bambu Studio)
    # Tous les coûts sont ramenés à la pièce en divisant par pieces_per_plate
    price_kg        = price_per_kg_override if price_per_kg_override is not None else s['filament_price_per_kg']
    weight_per_piece = weight_g / pieces_per_plate
    mat        = (price_kg / 1000) * weight_per_piece
    mat_margin = mat * (s['material_margin_pct'] / 100)
    design     = mat * design_mult
    # Manutention, usure et élec divisées par pièces/plateau
    handling   = (s['handling_rate_per_hour'] / 60) * s['handling_minutes_per_plate'] / pieces_per_plate
    printer_w  = (s['printer_price'] / (s['printer_lifespan_hours'] * 3600)) * print_time_s / pieces_per_plate
    nozzle_w   = (s['nozzle_price']  / (s['nozzle_lifespan_hours']  * 3600)) * print_time_s / pieces_per_plate
    plate_w    = (s['plate_price']   / (s['plate_lifespan_hours']   * 3600)) * print_time_s / pieces_per_plate
    wear       = printer_w + nozzle_w + plate_w
    elec       = s['printer_power_kw'] * (print_time_s / 3600) * s['electricity_price_kwh'] / pieces_per_plate

    cout_fixe  = mat + mat_margin + wear + elec
    sous_total = cout_fixe + handling + design
    margin     = sous_total * (gross_margin_pct / 100)
    total_marge= handling + design + margin
    prix_ht    = cout_fixe + total_marge

    cot_rate         = s.get('cotisations_rate_pct', 12.3) / 100
    vfl_rate         = s.get('vfl_rate_pct', 0.0)          / 100
    tva_rate         = s.get('tva_rate_pct', 0.0)          / 100
    other_taxes_rate = s.get('other_taxes_rate_pct', 1.0)  / 100
    total_charges    = cot_rate + vfl_rate + other_taxes_rate

    if total_charges < 1.0:
        prix_ht_net = prix_ht / (1 - total_charges)
    else:
        prix_ht_net = prix_ht

    cot_amount         = prix_ht_net * cot_rate
    vfl_amount         = prix_ht_net * vfl_rate
    other_taxes_amount = prix_ht_net * other_taxes_rate
    tva_amount         = prix_ht_net * tva_rate
    prix_ttc           = prix_ht_net + tva_amount
    final              = prix_ttc * (1 - discount_pct / 100)
    marge_pct_on_ht    = (total_marge / prix_ht_net * 100) if prix_ht_net > 0 else 0

    return {
        'material_cost':      round(mat, 4),
        'material_margin':    round(mat_margin, 4),
        'wear_cost':          round(wear, 4),
        'printer_wear':       round(printer_w, 4),
        'nozzle_wear':        round(nozzle_w, 4),
        'plate_wear':         round(plate_w, 4),
        'electricity_cost':   round(elec, 4),
        'kwh_used':           round(s['printer_power_kw'] * (print_time_s / 3600), 4),
        'cout_fixe':          round(cout_fixe, 4),
        'handling_cost':      round(handling, 4),
        'design_cost':        round(design, 4),
        'subtotal':           round(sous_total, 4),
        'margin_amount':      round(margin, 4),
        'total_marge':        round(total_marge, 4),
        'marge_pct_on_ht':    round(marge_pct_on_ht, 1),
        'prix_ht':            round(prix_ht, 4),
        'cotisations_amount': round(cot_amount, 4),
        'vfl_amount':         round(vfl_amount, 4),
        'other_taxes_amount': round(other_taxes_amount, 4),
        'tva_amount':         round(tva_amount, 4),
        'prix_ht_net':        round(prix_ht_net, 4),
        'prix_ttc':           round(prix_ttc, 4),
        'tax_amount':         round(cot_amount + vfl_amount + other_taxes_amount, 4),
        'final_price':        round(final, 2),
        '_cot_pct':              round(cot_rate * 100, 2),
        '_vfl_pct':              round(vfl_rate * 100, 2),
        '_tva_pct':              round(tva_rate * 100, 2),
        '_other_taxes_pct':      round(other_taxes_rate * 100, 2),
        '_material_margin_pct':  s['material_margin_pct'],
        '_handling_rate':        s['handling_rate_per_hour'],
        '_handling_minutes':     s['handling_minutes_per_plate'],
        '_elec_price':           s['electricity_price_kwh'],
        '_pieces_per_plate':     pieces_per_plate,
    }

# ── Planning helpers ──────────────────────────────────────────────────────────

def get_schedule_dict():
    conn = get_db()
    rows = conn.execute('SELECT * FROM working_schedule ORDER BY day_of_week').fetchall()
    conn.close()
    return {r['day_of_week']: dict(r) for r in rows}

def get_blocks_dict(from_date=None, to_date=None):
    conn = get_db()
    if from_date and to_date:
        # A block overlaps [from_date, to_date] when:
        # block.date <= to_date  AND  COALESCE(block.date_end, block.date) >= from_date
        rows = conn.execute(
            """SELECT * FROM calendar_blocks
               WHERE date <= ?
               AND COALESCE(date_end, date) >= ?
               ORDER BY date""",
            (str(to_date), str(from_date))).fetchall()
    else:
        rows = conn.execute('SELECT * FROM calendar_blocks ORDER BY date').fetchall()
    conn.close()
    return [dict(r) for r in rows]

def get_day_launches(check_date, schedule, blocks, cycle_min):
    """Returns number of possible launches on a given date (per machine)."""
    date_str = str(check_date)
    dow = check_date.weekday()
    block = next((b for b in blocks
                 if b['date'] <= date_str
                 and (b['date_end'] or b['date']) >= date_str
                 and b['printer_id'] is None), None)
    mode = block['type'] if block else schedule[dow]['mode']

    if mode == 'off':
        return 0, mode
    elif mode == 'tt':
        sched = schedule[dow]
        h0, m0 = [int(x) for x in sched['tt_window_start'].split(':')]
        h1, m1 = [int(x) for x in sched['tt_window_end'].split(':')]
        window_min = (h1 * 60 + m1) - (h0 * 60 + m0)
        launches = max(1, int(window_min / cycle_min)) if cycle_min > 0 else 1
        return launches, mode
    elif mode == 'onsite':
        return 2, mode
    return 0, mode

def calc_capacity(print_time_s, pieces_per_plate, days_ahead=30):
    """Calcule la capacité de production sur N jours à venir."""
    s = get_settings()
    cooldown_min    = s.get('cooldown_minutes', 15)
    max_printers    = int(s.get('max_simultaneous_printers', 5))
    failure_buf     = s.get('failure_buffer_pct', 10) / 100

    cycle_min = print_time_s / 60 + cooldown_min
    if cycle_min <= 0:
        cycle_min = 1

    conn = get_db()
    active_count = conn.execute('SELECT COUNT(*) FROM printers WHERE is_active=1').fetchone()[0]
    conn.close()
    effective = min(active_count, max_printers)

    schedule = get_schedule_dict()
    today = date_cls.today()
    blocks = get_blocks_dict(today, today + timedelta(days=days_ahead))

    pieces_per_day_first = None
    week_total  = 0
    month_total = 0

    for offset in range(days_ahead):
        d = today + timedelta(days=offset)
        launches, _ = get_day_launches(d, schedule, blocks, cycle_min)
        pieces = launches * pieces_per_plate * effective
        if pieces_per_day_first is None and pieces > 0:
            pieces_per_day_first = pieces
        if offset < 7:
            week_total += pieces
        month_total += pieces

    # Nb plateaux pour 1 commande (calculé côté appelant)
    return {
        'pieces_per_day':      pieces_per_day_first or 0,
        'pieces_per_week':     week_total,
        'pieces_per_month':    month_total,
        'effective_printers':  effective,
        'cycle_minutes':       round(cycle_min, 1),
        'failure_buffer_pct':  round(failure_buf * 100, 1),
    }

def find_next_slot(print_time_s):
    """
    Trouve le prochain créneau libre sur une imprimante disponible.
    Parcourt jusqu'à 60 jours, en tenant compte du planning horaire
    (TT vs onsite) et des indisponibilités (calendar_blocks).
    Retourne un dict {printer_id, printer_name, planned_start, planned_end}
    ou None si aucun créneau trouvé dans les 60 jours.
    """
    s = get_settings()
    cooldown_min = int(s.get('cooldown_minutes', 15))
    max_printers = int(s.get('max_simultaneous_printers', 5))
    total_sec    = print_time_s + cooldown_min * 60

    conn = get_db()
    printers = conn.execute(
        'SELECT * FROM printers WHERE is_active=1 ORDER BY id LIMIT ?', (max_printers,)
    ).fetchall()
    existing = conn.execute(
        "SELECT printer_id, planned_start, planned_end FROM print_slots WHERE status NOT IN ('cancelled','failed')"
    ).fetchall()
    conn.close()

    schedule = get_schedule_dict()
    today = date_cls.today()
    blocks = get_blocks_dict(today, today + timedelta(days=60))

    # Build busy intervals per printer
    busy = {}
    for sl in existing:
        pid = sl['printer_id']
        if pid not in busy:
            busy[pid] = []
        busy[pid].append((sl['planned_start'], sl['planned_end']))

    for offset in range(60):
        d = today + timedelta(days=offset)
        launches, mode = get_day_launches(d, schedule, blocks, (print_time_s / 60) + cooldown_min)
        if launches == 0:
            continue

        date_str = str(d)
        sched = schedule[d.weekday()]

        # Build candidate launch times
        candidates = []
        if mode == 'tt':
            h0, m0 = [int(x) for x in sched['tt_window_start'].split(':')]
            candidates.append(datetime(d.year, d.month, d.day, h0, m0))
            # Add slots every cycle within window
            h1, m1 = [int(x) for x in sched['tt_window_end'].split(':')]
            window_end = datetime(d.year, d.month, d.day, h1, m1)
            t = candidates[0]
            while True:
                t = t + timedelta(seconds=total_sec)
                if t >= window_end:
                    break
                candidates.append(t)
        else:
            h0, m0 = [int(x) for x in sched['onsite_slot_1'].split(':')]
            h1, m1 = [int(x) for x in sched['onsite_slot_2'].split(':')]
            candidates.append(datetime(d.year, d.month, d.day, h0, m0))
            candidates.append(datetime(d.year, d.month, d.day, h1, m1))

        now = datetime.now()
        for start_dt in candidates:
            if start_dt <= now:  # ignorer les créneaux déjà passés
                continue
            end_dt = start_dt + timedelta(seconds=total_sec)
            for printer in printers:
                pid = printer['id']
                printer_busy = busy.get(pid, [])
                conflict = any(
                    not (end_dt.isoformat() <= bs or start_dt.isoformat() >= be)
                    for bs, be in printer_busy
                )
                if not conflict:
                    return {
                        'printer_id':   pid,
                        'printer_name': printer['name'],
                        'planned_start': start_dt.strftime('%Y-%m-%dT%H:%M'),
                        'planned_end':   end_dt.strftime('%Y-%m-%dT%H:%M'),
                    }
    return None

def fmt_time(s):
    return f"{s//3600}h{(s%3600)//60:02d}m"
app.jinja_env.filters['fmt_time'] = fmt_time

_FRENCH_COLORS = {
    'blanc': '#ffffff', 'noir': '#111111', 'gris': '#6b7280', 'argent': '#9ca3af',
    'rouge': '#ef4444', 'rose': '#ec4899', 'fuchsia': '#d946ef',
    'orange': '#f97316', 'jaune': '#eab308', 'or': '#d97706',
    'vert': '#22c55e', 'olive': '#65a30d', 'emeraude': '#10b981',
    'bleu': '#3b82f6', 'marine': '#1e3a8a', 'ciel': '#38bdf8', 'cyan': '#06b6d4',
    'violet': '#8b5cf6', 'mauve': '#a855f7', 'indigo': '#6366f1',
    'marron': '#92400e', 'beige': '#d4b896', 'creme': '#fef3c7',
    'turquoise': '#14b8a6', 'or rose': '#fb7185',
    'transparent': 'rgba(200,200,200,0.3)', 'naturel': '#f5f0e8',
    'translucide': 'rgba(200,200,200,0.3)',
}

def css_color(value):
    if not value:
        return value
    return _FRENCH_COLORS.get(value.strip().lower(), value)
app.jinja_env.filters['css_color'] = css_color

# ─── Dashboard ────────────────────────────────────────────────────────────────

@app.route('/')
def dashboard():
    conn = get_db()
    recent = conn.execute('''
        SELECT j.*, c.name as client_name, m.name as material_name
        FROM jobs j LEFT JOIN clients c ON j.client_id=c.id
        LEFT JOIN materials m ON j.material_id=m.id
        ORDER BY j.created_at DESC LIMIT 10''').fetchall()
    stats = conn.execute('''
        SELECT COUNT(*) as total_jobs, ROUND(SUM(final_price),2) as total_revenue,
               ROUND(AVG(gross_margin_pct),1) as avg_margin,
               ROUND(SUM(CASE WHEN created_at>=date('now','-30 days') THEN final_price ELSE 0 END),2) as month_revenue
        FROM jobs''').fetchone()
    low_stock = conn.execute(
        'SELECT * FROM materials WHERE stock_g<=low_stock_threshold_g ORDER BY stock_g ASC').fetchall()
    # Restock alerts
    planned_consumption = conn.execute('''
        SELECT j.material_id, SUM(j.weight_g * j.order_qty) as total_g
        FROM print_slots ps
        JOIN jobs j ON ps.job_id=j.id
        WHERE ps.status='planned' AND j.material_id IS NOT NULL
        GROUP BY j.material_id''').fetchall()
    conn.close()
    return render_template('dashboard.html', jobs=recent, stats=stats,
                           low_stock=low_stock, planned_consumption=planned_consumption)

# ─── Clients ──────────────────────────────────────────────────────────────────

@app.route('/clients')
def clients():
    conn = get_db()
    clients = conn.execute('''
        SELECT c.*, COUNT(j.id) as job_count,
               ROUND(COALESCE(SUM(j.final_price),0),2) as total_revenue
        FROM clients c LEFT JOIN jobs j ON j.client_id=c.id
        GROUP BY c.id ORDER BY c.name''').fetchall()
    conn.close()
    return render_template('clients.html', clients=clients)

def _get_all_profiles(conn):
    return {
        'machine_profiles':   conn.execute('SELECT id,name FROM machine_profiles ORDER BY name').fetchall(),
        'handling_profiles':  conn.execute('SELECT id,name FROM handling_profiles ORDER BY name').fetchall(),
        'material_profiles':  conn.execute('SELECT id,name FROM material_profiles ORDER BY name').fetchall(),
        'pricing_profiles':   conn.execute('SELECT id,name FROM pricing_profiles ORDER BY name').fetchall(),
    }

@app.route('/clients/new', methods=['GET','POST'])
def new_client():
    conn = get_db()
    if request.method == 'POST':
        def _int(k): return int(request.form[k]) if request.form.get(k) else None
        conn.execute('''INSERT INTO clients
            (name,email,notes,default_machine_profile_id,default_handling_profile_id,
             default_material_profile_id,default_pricing_profile_id)
            VALUES(?,?,?,?,?,?,?)''',
            (request.form['name'], request.form.get('email',''), request.form.get('notes',''),
             _int('default_machine_profile_id'), _int('default_handling_profile_id'),
             _int('default_material_profile_id'), _int('default_pricing_profile_id')))
        conn.commit(); conn.close()
        return redirect(url_for('clients'))
    profiles = _get_all_profiles(conn)
    conn.close()
    return render_template('client_form.html', client=None, **profiles)

@app.route('/clients/<int:id>/edit', methods=['GET','POST'])
def edit_client(id):
    conn = get_db()
    client = conn.execute('SELECT * FROM clients WHERE id=?',(id,)).fetchone()
    if request.method == 'POST':
        def _int(k): return int(request.form[k]) if request.form.get(k) else None
        conn.execute('''UPDATE clients SET name=?,email=?,notes=?,
            default_machine_profile_id=?,default_handling_profile_id=?,
            default_material_profile_id=?,default_pricing_profile_id=? WHERE id=?''',
            (request.form['name'], request.form.get('email',''), request.form.get('notes',''),
             _int('default_machine_profile_id'), _int('default_handling_profile_id'),
             _int('default_material_profile_id'), _int('default_pricing_profile_id'), id))
        conn.commit(); conn.close()
        return redirect(url_for('clients'))
    profiles = _get_all_profiles(conn)
    conn.close()
    return render_template('client_form.html', client=client, **profiles)

@app.route('/api/client/<int:id>/defaults')
def api_client_defaults(id):
    conn = get_db()
    c = conn.execute('SELECT default_machine_profile_id, default_handling_profile_id, \
        default_material_profile_id, default_pricing_profile_id FROM clients WHERE id=?',(id,)).fetchone()
    conn.close()
    if not c:
        return jsonify({})
    return jsonify({
        'machine_profile_id':  c['default_machine_profile_id'],
        'handling_profile_id': c['default_handling_profile_id'],
        'material_profile_id': c['default_material_profile_id'],
        'pricing_profile_id':  c['default_pricing_profile_id'],
    })

@app.route('/clients/<int:id>/delete', methods=['POST'])
def delete_client(id):
    conn = get_db()
    conn.execute('DELETE FROM clients WHERE id=?',(id,))
    conn.commit(); conn.close()
    return redirect(url_for('clients'))

# ─── Projets ──────────────────────────────────────────────────────────────────

@app.route('/projects')
def projects():
    conn = get_db()
    rows = conn.execute('''
        SELECT p.*, c.name as client_name,
               COUNT(j.id) as job_count,
               COALESCE(SUM(j.order_qty), 0) as total_pieces,
               COALESCE(SUM(j.final_price * j.order_qty), 0) as total_revenue
        FROM projects p
        LEFT JOIN clients c ON c.id = p.client_id
        LEFT JOIN jobs j ON j.project_id = p.id
        GROUP BY p.id ORDER BY p.created_at DESC
    ''').fetchall()
    conn.close()
    return render_template('projects.html', projects=rows)

@app.route('/projects/new', methods=['GET','POST'])
def new_project():
    conn = get_db()
    if request.method == 'POST':
        conn.execute('INSERT INTO projects(name,client_id,deadline,notes) VALUES(?,?,?,?)',
            (request.form['name'],
             int(request.form['client_id']) if request.form.get('client_id') else None,
             request.form.get('deadline') or None,
             request.form.get('notes','')))
        conn.commit(); conn.close()
        return redirect(url_for('projects'))
    clients = conn.execute('SELECT id,name FROM clients ORDER BY name').fetchall()
    conn.close()
    return render_template('project_form.html', project=None, clients=clients)

@app.route('/projects/<int:id>', methods=['GET'])
def project_detail(id):
    conn = get_db()
    project = conn.execute('''
        SELECT p.*, c.name as client_name
        FROM projects p LEFT JOIN clients c ON c.id=p.client_id
        WHERE p.id=?''', (id,)).fetchone()
    if not project:
        conn.close(); return redirect(url_for('projects'))
    jobs = conn.execute('''
        SELECT j.*, c.name as client_name, m.name as material_name, m.type as material_type
        FROM jobs j
        LEFT JOIN clients c ON c.id=j.client_id
        LEFT JOIN materials m ON m.id=j.material_id
        WHERE j.project_id=?
        ORDER BY j.created_at''', (id,)).fetchall()
    conn.close()
    total_pieces  = sum((j['order_qty'] or 1) for j in jobs)
    total_revenue = sum((j['final_price'] or 0) * (j['order_qty'] or 1) for j in jobs)
    return render_template('project_detail.html', project=project, jobs=jobs,
                           total_pieces=total_pieces, total_revenue=total_revenue)

@app.route('/projects/<int:id>/edit', methods=['GET','POST'])
def edit_project(id):
    conn = get_db()
    project = conn.execute('SELECT * FROM projects WHERE id=?',(id,)).fetchone()
    if request.method == 'POST':
        conn.execute('UPDATE projects SET name=?,client_id=?,deadline=?,notes=? WHERE id=?',
            (request.form['name'],
             int(request.form['client_id']) if request.form.get('client_id') else None,
             request.form.get('deadline') or None,
             request.form.get('notes',''), id))
        conn.commit(); conn.close()
        return redirect(url_for('project_detail', id=id))
    clients = conn.execute('SELECT id,name FROM clients ORDER BY name').fetchall()
    conn.close()
    return render_template('project_form.html', project=project, clients=clients)

@app.route('/projects/<int:id>/delete', methods=['POST'])
def delete_project(id):
    conn = get_db()
    conn.execute('UPDATE jobs SET project_id=NULL WHERE project_id=?',(id,))
    conn.execute('DELETE FROM projects WHERE id=?',(id,))
    conn.commit(); conn.close()
    return redirect(url_for('projects'))

# ─── Matières ─────────────────────────────────────────────────────────────────

@app.route('/materials')
def materials():
    conn = get_db()
    mats = conn.execute('''
        SELECT m.*, COUNT(j.id) as job_count,
               ROUND(COALESCE(SUM(j.weight_g),0),1) as total_used_g
        FROM materials m LEFT JOIN jobs j ON j.material_id=m.id
        GROUP BY m.id ORDER BY m.type, m.name''').fetchall()
    types = sorted(set(m['type'] for m in mats if m['type']))
    conn.close()
    return render_template('materials.html', materials=mats, types=types)

@app.route('/materials/new', methods=['GET','POST'])
def new_material():
    if request.method == 'POST':
        price = float(request.form['price_per_kg'])
        conn = get_db()
        conn.execute('''INSERT INTO materials(name,brand,type,color,price_per_kg,stock_g,low_stock_threshold_g,notes)
            VALUES(?,?,?,?,?,?,?,?)''',
            (request.form['name'], request.form.get('brand',''), request.form.get('type','PLA'),
             request.form.get('color',''), price, float(request.form.get('stock_g',0)),
             float(request.form.get('low_stock_threshold_g',200)), request.form.get('notes','')))
        mat_id = conn.execute('SELECT last_insert_rowid()').fetchone()[0]
        conn.execute('INSERT INTO material_price_history(material_id,price_per_kg) VALUES(?,?)', (mat_id, price))
        conn.commit(); conn.close()
        return redirect(url_for('materials'))
    return render_template('material_form.html', material=None, history=None)

@app.route('/materials/<int:id>/edit', methods=['GET','POST'])
def edit_material(id):
    conn = get_db()
    mat = conn.execute('SELECT * FROM materials WHERE id=?',(id,)).fetchone()
    history = conn.execute(
        'SELECT * FROM material_price_history WHERE material_id=? ORDER BY recorded_at DESC LIMIT 10',
        (id,)).fetchall()
    if request.method == 'POST':
        new_price = float(request.form['price_per_kg'])
        old_price = mat['price_per_kg']
        conn.execute('''UPDATE materials SET name=?,brand=?,type=?,color=?,price_per_kg=?,
            stock_g=?,low_stock_threshold_g=?,notes=? WHERE id=?''',
            (request.form['name'], request.form.get('brand',''), request.form.get('type','PLA'),
             request.form.get('color',''), new_price, float(request.form.get('stock_g', mat['stock_g'])),
             float(request.form.get('low_stock_threshold_g',200)), request.form.get('notes',''), id))
        if abs(new_price - old_price) > 0.001:
            conn.execute('INSERT INTO material_price_history(material_id,price_per_kg) VALUES(?,?)', (id, new_price))
        conn.commit(); conn.close()
        return redirect(url_for('materials'))
    conn.close()
    return render_template('material_form.html', material=mat, history=history)

@app.route('/materials/<int:id>/restock', methods=['POST'])
def restock_material(id):
    conn = get_db()
    add_g = float(request.form.get('add_g', 0))
    conn.execute('UPDATE materials SET stock_g=stock_g+? WHERE id=?', (add_g, id))
    new_price = request.form.get('new_price_per_kg')
    if new_price:
        new_price = float(new_price)
        mat = conn.execute('SELECT price_per_kg FROM materials WHERE id=?',(id,)).fetchone()
        conn.execute('UPDATE materials SET price_per_kg=? WHERE id=?', (new_price, id))
        if abs(new_price - mat['price_per_kg']) > 0.001:
            conn.execute('INSERT INTO material_price_history(material_id,price_per_kg) VALUES(?,?)', (id, new_price))
    conn.commit(); conn.close()
    return redirect(url_for('materials'))

@app.route('/materials/<int:id>/duplicate', methods=['POST'])
def duplicate_material(id):
    conn = get_db()
    m = conn.execute('SELECT * FROM materials WHERE id=?',(id,)).fetchone()
    if m:
        conn.execute('''INSERT INTO materials(name,brand,type,color,price_per_kg,stock_g,low_stock_threshold_g,notes)
            VALUES(?,?,?,?,?,?,?,?)''',
            ('Copie de ' + m['name'], m['brand'], m['type'], m['color'],
             m['price_per_kg'], 0, m['low_stock_threshold_g'], m['notes']))
        new_id = conn.execute('SELECT last_insert_rowid()').fetchone()[0]
        conn.execute('INSERT INTO material_price_history(material_id,price_per_kg) VALUES(?,?)',
                     (new_id, m['price_per_kg']))
        conn.commit(); conn.close()
        return redirect(url_for('edit_material', id=new_id))
    conn.close()
    return redirect(url_for('materials'))

@app.route('/materials/<int:id>/delete', methods=['POST'])
def delete_material(id):
    conn = get_db()
    conn.execute('DELETE FROM materials WHERE id=?',(id,))
    conn.commit(); conn.close()
    return redirect(url_for('materials'))

# ─── Profils / Templates ──────────────────────────────────────────────────────

@app.route('/profiles')
def profiles():
    conn = get_db()
    machine   = conn.execute('SELECT * FROM machine_profiles ORDER BY name').fetchall()
    handling  = conn.execute('SELECT * FROM handling_profiles ORDER BY name').fetchall()
    material  = conn.execute('SELECT * FROM material_profiles ORDER BY name').fetchall()
    pricing   = conn.execute('SELECT * FROM pricing_profiles ORDER BY name').fetchall()
    conn.close()
    return render_template('profiles.html',
        machine_profiles=machine, handling_profiles=handling,
        material_profiles=material, pricing_profiles=pricing)

@app.route('/profiles/machine/new', methods=['GET','POST'])
def new_machine_profile():
    if request.method == 'POST':
        conn = get_db()
        conn.execute('''INSERT INTO machine_profiles
            (name,printer_price,printer_lifespan_hours,nozzle_price,nozzle_lifespan_hours,
             plate_price,plate_lifespan_hours,printer_power_kw,electricity_price_kwh,notes)
            VALUES(?,?,?,?,?,?,?,?,?,?)''', (
            request.form['name'],
            float(request.form['printer_price']),
            float(request.form['printer_lifespan_hours']),
            float(request.form['nozzle_price']),
            float(request.form['nozzle_lifespan_hours']),
            float(request.form['plate_price']),
            float(request.form['plate_lifespan_hours']),
            float(request.form['printer_power_kw']),
            float(request.form['electricity_price_kwh']),
            request.form.get('notes','')
        ))
        conn.commit(); conn.close()
        return redirect(url_for('profiles') + '#machine')
    return render_template('machine_profile_form.html', profile=None, settings=get_settings())

@app.route('/profiles/machine/<int:id>/edit', methods=['GET','POST'])
def edit_machine_profile(id):
    conn = get_db()
    profile = conn.execute('SELECT * FROM machine_profiles WHERE id=?',(id,)).fetchone()
    if request.method == 'POST':
        conn.execute('''UPDATE machine_profiles SET
            name=?,printer_price=?,printer_lifespan_hours=?,nozzle_price=?,nozzle_lifespan_hours=?,
            plate_price=?,plate_lifespan_hours=?,printer_power_kw=?,electricity_price_kwh=?,notes=?
            WHERE id=?''', (
            request.form['name'],
            float(request.form['printer_price']),
            float(request.form['printer_lifespan_hours']),
            float(request.form['nozzle_price']),
            float(request.form['nozzle_lifespan_hours']),
            float(request.form['plate_price']),
            float(request.form['plate_lifespan_hours']),
            float(request.form['printer_power_kw']),
            float(request.form['electricity_price_kwh']),
            request.form.get('notes',''), id
        ))
        conn.commit(); conn.close()
        return redirect(url_for('profiles') + '#machine')
    conn.close()
    return render_template('machine_profile_form.html', profile=profile, settings=get_settings())

@app.route('/profiles/machine/<int:id>/delete', methods=['POST'])
def delete_machine_profile(id):
    conn = get_db()
    conn.execute('DELETE FROM machine_profiles WHERE id=?',(id,))
    conn.commit(); conn.close()
    return redirect(url_for('profiles') + '#machine')

@app.route('/profiles/handling/new', methods=['GET','POST'])
def new_handling_profile():
    if request.method == 'POST':
        conn = get_db()
        conn.execute('INSERT INTO handling_profiles(name,handling_rate_per_hour,handling_minutes_per_plate,notes) VALUES(?,?,?,?)', (
            request.form['name'],
            float(request.form['handling_rate_per_hour']),
            float(request.form['handling_minutes_per_plate']),
            request.form.get('notes','')
        ))
        conn.commit(); conn.close()
        return redirect(url_for('profiles') + '#handling')
    return render_template('handling_profile_form.html', profile=None, settings=get_settings())

@app.route('/profiles/handling/<int:id>/edit', methods=['GET','POST'])
def edit_handling_profile(id):
    conn = get_db()
    profile = conn.execute('SELECT * FROM handling_profiles WHERE id=?',(id,)).fetchone()
    if request.method == 'POST':
        conn.execute('UPDATE handling_profiles SET name=?,handling_rate_per_hour=?,handling_minutes_per_plate=?,notes=? WHERE id=?', (
            request.form['name'],
            float(request.form['handling_rate_per_hour']),
            float(request.form['handling_minutes_per_plate']),
            request.form.get('notes',''), id
        ))
        conn.commit(); conn.close()
        return redirect(url_for('profiles') + '#handling')
    conn.close()
    return render_template('handling_profile_form.html', profile=profile, settings=get_settings())

@app.route('/profiles/handling/<int:id>/delete', methods=['POST'])
def delete_handling_profile(id):
    conn = get_db()
    conn.execute('DELETE FROM handling_profiles WHERE id=?',(id,))
    conn.commit(); conn.close()
    return redirect(url_for('profiles') + '#handling')

@app.route('/profiles/material/new', methods=['GET','POST'])
def new_material_profile():
    if request.method == 'POST':
        conn = get_db()
        conn.execute('INSERT INTO material_profiles(name,material_margin_pct,notes) VALUES(?,?,?)', (
            request.form['name'],
            float(request.form['material_margin_pct']),
            request.form.get('notes','')
        ))
        conn.commit(); conn.close()
        return redirect(url_for('profiles') + '#material')
    return render_template('material_profile_form.html', profile=None, settings=get_settings())

@app.route('/profiles/material/<int:id>/edit', methods=['GET','POST'])
def edit_material_profile(id):
    conn = get_db()
    profile = conn.execute('SELECT * FROM material_profiles WHERE id=?',(id,)).fetchone()
    if request.method == 'POST':
        conn.execute('UPDATE material_profiles SET name=?,material_margin_pct=?,notes=? WHERE id=?', (
            request.form['name'],
            float(request.form['material_margin_pct']),
            request.form.get('notes',''), id
        ))
        conn.commit(); conn.close()
        return redirect(url_for('profiles') + '#material')
    conn.close()
    return render_template('material_profile_form.html', profile=profile, settings=get_settings())

@app.route('/profiles/material/<int:id>/delete', methods=['POST'])
def delete_material_profile(id):
    conn = get_db()
    conn.execute('DELETE FROM material_profiles WHERE id=?',(id,))
    conn.commit(); conn.close()
    return redirect(url_for('profiles') + '#material')

@app.route('/profiles/pricing/new', methods=['GET','POST'])
def new_pricing_profile():
    if request.method == 'POST':
        conn = get_db()
        conn.execute('INSERT INTO pricing_profiles(name,design_multiplier,gross_margin_pct,notes) VALUES(?,?,?,?)', (
            request.form['name'],
            float(request.form['design_multiplier']),
            float(request.form['gross_margin_pct']),
            request.form.get('notes','')
        ))
        conn.commit(); conn.close()
        return redirect(url_for('profiles') + '#pricing')
    return render_template('pricing_profile_form.html', profile=None, settings=get_settings())

@app.route('/profiles/pricing/<int:id>/edit', methods=['GET','POST'])
def edit_pricing_profile(id):
    conn = get_db()
    profile = conn.execute('SELECT * FROM pricing_profiles WHERE id=?',(id,)).fetchone()
    if request.method == 'POST':
        conn.execute('UPDATE pricing_profiles SET name=?,design_multiplier=?,gross_margin_pct=?,notes=? WHERE id=?', (
            request.form['name'],
            float(request.form['design_multiplier']),
            float(request.form['gross_margin_pct']),
            request.form.get('notes',''), id
        ))
        conn.commit(); conn.close()
        return redirect(url_for('profiles') + '#pricing')
    conn.close()
    return render_template('pricing_profile_form.html', profile=profile, settings=get_settings())

@app.route('/profiles/pricing/<int:id>/delete', methods=['POST'])
def delete_pricing_profile(id):
    conn = get_db()
    conn.execute('DELETE FROM pricing_profiles WHERE id=?',(id,))
    conn.commit(); conn.close()
    return redirect(url_for('profiles') + '#pricing')

# ─── Commandes ────────────────────────────────────────────────────────────────

@app.route('/jobs')
def jobs():
    conn = get_db()
    jobs = conn.execute('''
        SELECT j.*, c.name as client_name, m.name as material_name,
               ps.status as slot_status, ps.planned_start
        FROM jobs j LEFT JOIN clients c ON j.client_id=c.id
        LEFT JOIN materials m ON j.material_id=m.id
        LEFT JOIN print_slots ps ON ps.job_id=j.id AND ps.id=(
            SELECT id FROM print_slots WHERE job_id=j.id ORDER BY planned_start LIMIT 1)
        ORDER BY j.created_at DESC''').fetchall()
    conn.close()
    return render_template('jobs.html', jobs=jobs)

@app.route('/jobs/new', methods=['GET','POST'])
def new_job():
    conn = get_db()
    clients           = conn.execute('SELECT * FROM clients ORDER BY name').fetchall()
    mats              = conn.execute('SELECT * FROM materials ORDER BY type,name').fetchall()
    machine_profiles  = conn.execute('SELECT * FROM machine_profiles ORDER BY name').fetchall()
    handling_profiles = conn.execute('SELECT * FROM handling_profiles ORDER BY name').fetchall()
    material_profiles = conn.execute('SELECT * FROM material_profiles ORDER BY name').fetchall()
    pricing_profiles  = conn.execute('SELECT * FROM pricing_profiles ORDER BY name').fetchall()
    projects          = conn.execute('SELECT id,name FROM projects ORDER BY name').fetchall()
    settings          = get_settings()

    # Pré-remplissage depuis un job existant
    prefill = None
    clone_from = request.args.get('clone_from')
    if clone_from:
        prefill = conn.execute('SELECT * FROM jobs WHERE id=?', (clone_from,)).fetchone()
        if prefill:
            prefill = dict(prefill)
            prefill['hours']   = prefill['print_time_s'] // 3600
            prefill['minutes'] = (prefill['print_time_s'] % 3600) // 60
            # source_nc_path transmis au template pour auto-parse Nextcloud

    if request.method == 'POST':
        weight_g       = float(request.form['weight_g'])
        hours          = int(request.form.get('hours',0))
        minutes        = int(request.form.get('minutes',0))
        print_time_s   = hours*3600 + minutes*60
        design_mult    = float(request.form['design_multiplier'])
        gross_margin   = float(request.form['gross_margin_pct'])
        discount       = float(request.form.get('discount_pct',0))
        client_id      = request.form.get('client_id') or None
        material_id    = request.form.get('material_id') or None
        source_file    = request.form.get('source_file','').strip()
        machine_pid    = request.form.get('machine_profile_id') or None
        handling_pid   = request.form.get('handling_profile_id') or None
        material_pid   = request.form.get('material_profile_id') or None
        pricing_pid    = request.form.get('pricing_profile_id') or None
        project_id     = request.form.get('project_id') or None
        pieces_per_plate = max(1, int(request.form.get('pieces_per_plate', 1)))
        order_qty        = max(1, int(request.form.get('order_qty', 1)))
        plate_name       = request.form.get('plate_name', '').strip()
        source_nc_path   = request.form.get('source_nc_path', '').strip()

        s = dict(settings)
        if machine_pid:
            mp = conn.execute('SELECT * FROM machine_profiles WHERE id=?',(machine_pid,)).fetchone()
            if mp:
                s['printer_price']          = mp['printer_price']
                s['printer_lifespan_hours'] = mp['printer_lifespan_hours']
                s['nozzle_price']           = mp['nozzle_price']
                s['nozzle_lifespan_hours']  = mp['nozzle_lifespan_hours']
                s['plate_price']            = mp['plate_price']
                s['plate_lifespan_hours']   = mp['plate_lifespan_hours']
                s['printer_power_kw']       = mp['printer_power_kw']
                s['electricity_price_kwh']  = mp['electricity_price_kwh']
        if handling_pid:
            hp = conn.execute('SELECT * FROM handling_profiles WHERE id=?',(handling_pid,)).fetchone()
            if hp:
                s['handling_rate_per_hour']     = hp['handling_rate_per_hour']
                s['handling_minutes_per_plate'] = hp['handling_minutes_per_plate']
        if material_pid:
            marp = conn.execute('SELECT * FROM material_profiles WHERE id=?',(material_pid,)).fetchone()
            if marp:
                s['material_margin_pct'] = marp['material_margin_pct']

        price_kg = None
        if material_id:
            mat = conn.execute('SELECT price_per_kg FROM materials WHERE id=?',(material_id,)).fetchone()
            if mat:
                price_kg = mat['price_per_kg']

        r = calc(weight_g, print_time_s, design_mult, gross_margin, discount,
                 price_kg, s, pieces_per_plate)
        conn.execute('''INSERT INTO jobs(client_id,material_id,machine_profile_id,handling_profile_id,
            material_profile_id,pricing_profile_id,project_id,source_file,source_nc_path,plate_name,name,description,
            weight_g,print_time_s,design_multiplier,gross_margin_pct,discount_pct,
            pieces_per_plate,order_qty,
            material_cost,material_margin,design_cost,handling_cost,wear_cost,electricity_cost,
            cout_fixe,total_marge,subtotal,margin_amount,marge_pct_on_ht,
            cotisations_amount,vfl_amount,tva_amount,other_taxes_amount,tax_amount,
            price_per_piece,final_price,notes)
            VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',
            (client_id,material_id,machine_pid,handling_pid,material_pid,pricing_pid,project_id,
             source_file,source_nc_path,plate_name,request.form['name'],request.form.get('description',''),
             weight_g,print_time_s,design_mult,gross_margin,discount,
             pieces_per_plate,order_qty,
             r['material_cost'],r['material_margin'],r['design_cost'],r['handling_cost'],
             r['wear_cost'],r['electricity_cost'],
             r['cout_fixe'],r['total_marge'],r['subtotal'],r['margin_amount'],r['marge_pct_on_ht'],
             r['cotisations_amount'],r['vfl_amount'],r['tva_amount'],r['other_taxes_amount'],
             r['tax_amount'],r['final_price'],round(r['final_price'],2),
             request.form.get('notes','')))
        new_id = conn.execute('SELECT last_insert_rowid()').fetchone()[0]
        if material_id:
            conn.execute('UPDATE materials SET stock_g=MAX(0,stock_g-?) WHERE id=?', (weight_g, material_id))
        conn.commit(); conn.close()
        if request.form.get('action') == 'save_and_new':
            return redirect(url_for('new_job', clone_from=new_id))
        return redirect(url_for('jobs'))

    conn.close()
    return render_template('new_job.html', clients=clients, materials=mats, settings=settings,
        machine_profiles=machine_profiles, handling_profiles=handling_profiles,
        material_profiles=material_profiles, pricing_profiles=pricing_profiles,
        projects=projects, prefill=prefill)

@app.route('/jobs/<int:id>')
def job_detail(id):
    conn = get_db()
    job = conn.execute('''
        SELECT j.*, c.name as client_name,
               m.name as material_name, m.color as material_color, m.type as material_type,
               mp.name as machine_profile_name, hp.name as handling_profile_name,
               marp.name as material_profile_name, pp.name as pricing_profile_name
        FROM jobs j
        LEFT JOIN clients c ON j.client_id=c.id
        LEFT JOIN materials m ON j.material_id=m.id
        LEFT JOIN machine_profiles mp ON j.machine_profile_id=mp.id
        LEFT JOIN handling_profiles hp ON j.handling_profile_id=hp.id
        LEFT JOIN material_profiles marp ON j.material_profile_id=marp.id
        LEFT JOIN pricing_profiles pp ON j.pricing_profile_id=pp.id
        WHERE j.id=?''', (id,)).fetchone()
    slots = conn.execute('''
        SELECT ps.*, p.name as printer_name, p.ha_entity_prefix,
               COALESCE(ps.pieces_override, j.pieces_per_plate) as effective_pieces,
               COALESCE(ps.pieces_ignored, 0) as pieces_ignored
        FROM print_slots ps
        LEFT JOIN printers p ON ps.printer_id=p.id
        JOIN jobs j ON ps.job_id=j.id
        WHERE ps.job_id=? ORDER BY ps.planned_start''', (id,)).fetchall()
    conn.close()
    if not job:
        return redirect(url_for('jobs'))
    import math as _math
    total_plates_needed = _math.ceil((job['order_qty'] or 1) / max(1, job['pieces_per_plate'] or 1))
    _s = get_settings()
    ha_poll_interval = int(_s.get('ha_poll_interval', 60))
    return render_template('job_detail.html', job=job, slots=slots,
                           total_plates_needed=total_plates_needed,
                           ha_poll_interval=ha_poll_interval)

@app.route('/jobs/<int:id>/delete', methods=['POST'])
def delete_job(id):
    conn = get_db()
    conn.execute('DELETE FROM jobs WHERE id=?',(id,))
    conn.commit(); conn.close()
    return redirect(url_for('jobs'))

# ─── Planning ─────────────────────────────────────────────────────────────────

@app.route('/planning')
def planning():
    conn = get_db()
    printers  = [dict(r) for r in conn.execute('SELECT * FROM printers ORDER BY id').fetchall()]
    unscheduled = conn.execute('''
        SELECT j.*, c.name as client_name,
               COALESCE((SELECT COUNT(*) FROM print_slots ps
                         WHERE ps.job_id=j.id
                         AND ps.status NOT IN ('cancelled','failed')), 0) as scheduled_plates,
               (COALESCE(j.order_qty,1) + COALESCE(j.pieces_per_plate,1) - 1)
               / COALESCE(j.pieces_per_plate,1) as total_plates_needed
        FROM jobs j LEFT JOIN clients c ON j.client_id=c.id
        WHERE (COALESCE(j.order_qty,1) + COALESCE(j.pieces_per_plate,1) - 1)
              / COALESCE(j.pieces_per_plate,1)
              > COALESCE((SELECT COUNT(*) FROM print_slots ps
                          WHERE ps.job_id=j.id
                          AND ps.status NOT IN ('cancelled','failed')), 0)
        ORDER BY j.created_at DESC LIMIT 20''').fetchall()
    blocks = conn.execute(
        "SELECT * FROM calendar_blocks"
        " WHERE COALESCE(date_end, date) >= date('now') ORDER BY date LIMIT 60"
    ).fetchall()
    schedule = conn.execute('SELECT * FROM working_schedule ORDER BY day_of_week').fetchall()
    conn.close()
    settings = get_settings()
    return render_template('planning.html', printers=printers,
                           unscheduled=unscheduled, blocks=blocks, schedule=schedule,
                           settings=settings)

@app.route('/printers', methods=['GET','POST'])
def printers_page():
    conn = get_db()
    if request.method == 'POST':
        name      = request.form['name']
        active    = 1 if request.form.get('is_active') else 0
        notes     = request.form.get('notes','')
        ha_prefix = request.form.get('ha_entity_prefix','').strip().lower()
        conn.execute('INSERT INTO printers(name,is_active,notes,ha_entity_prefix) VALUES(?,?,?,?)',
                     (name, active, notes, ha_prefix))
        conn.commit()
    printers = conn.execute('SELECT * FROM printers ORDER BY id').fetchall()
    conn.close()
    return render_template('printers.html', printers=printers)

@app.route('/printers/<int:id>/toggle', methods=['POST'])
def toggle_printer(id):
    conn = get_db()
    conn.execute('UPDATE printers SET is_active=1-is_active WHERE id=?', (id,))
    conn.commit(); conn.close()
    return redirect(url_for('printers_page'))

@app.route('/printers/<int:id>/delete', methods=['POST'])
def delete_printer(id):
    conn = get_db()
    conn.execute('DELETE FROM printers WHERE id=?', (id,))
    conn.commit(); conn.close()
    return redirect(url_for('printers_page'))

@app.route('/printers/<int:id>/edit', methods=['POST'])
@login_required
def edit_printer(id):
    name      = request.form.get('name','').strip()
    ha_prefix = request.form.get('ha_entity_prefix','').strip().lower()
    notes     = request.form.get('notes','').strip()
    if name:
        conn = get_db()
        conn.execute('UPDATE printers SET name=?, ha_entity_prefix=?, notes=? WHERE id=?',
                     (name, ha_prefix, notes, id))
        conn.commit(); conn.close()
    return redirect(url_for('printers_page'))

@app.route('/working-schedule', methods=['GET','POST'])
def working_schedule():
    if request.method == 'POST':
        conn = get_db()
        for dow in range(7):
            mode          = request.form.get(f'mode_{dow}', 'off')
            tt_start      = request.form.get(f'tt_start_{dow}', '10:00')
            tt_end        = request.form.get(f'tt_end_{dow}', '22:00')
            onsite_slot1  = request.form.get(f'onsite_slot1_{dow}', '07:00')
            onsite_slot2  = request.form.get(f'onsite_slot2_{dow}', '18:30')
            conn.execute('''UPDATE working_schedule SET mode=?,tt_window_start=?,tt_window_end=?,
                onsite_slot_1=?,onsite_slot_2=? WHERE day_of_week=?''',
                (mode, tt_start, tt_end, onsite_slot1, onsite_slot2, dow))
        conn.commit(); conn.close()
        return redirect(url_for('working_schedule'))
    conn = get_db()
    schedule = conn.execute('SELECT * FROM working_schedule ORDER BY day_of_week').fetchall()
    conn.close()
    return render_template('working_schedule.html', schedule=schedule)

@app.route('/calendar-blocks', methods=['GET','POST'])
def calendar_blocks():
    conn = get_db()
    if request.method == 'POST':
        date_start = request.form['date_start']
        date_end   = request.form.get('date_end','').strip() or None
        # If date_end < date_start, ignore it
        if date_end and date_end < date_start:
            date_end = None
        conn.execute(
            'INSERT INTO calendar_blocks(date,date_end,type,printer_id,notes) VALUES(?,?,?,?,?)',
            (date_start, date_end,
             request.form.get('type','off'),
             request.form.get('printer_id') or None,
             request.form.get('notes',''))
        )
        conn.commit()
    blocks   = conn.execute("SELECT cb.*, p.name as printer_name FROM calendar_blocks cb LEFT JOIN printers p ON cb.printer_id=p.id ORDER BY cb.date DESC LIMIT 50").fetchall()
    printers = conn.execute('SELECT * FROM printers ORDER BY id').fetchall()
    conn.close()
    return render_template('calendar_blocks.html', blocks=blocks, printers=printers)

@app.route('/calendar-blocks/<int:id>/delete', methods=['POST'])
def delete_calendar_block(id):
    conn = get_db()
    conn.execute('DELETE FROM calendar_blocks WHERE id=?', (id,))
    conn.commit(); conn.close()
    return redirect(url_for('calendar_blocks'))

# ─── Page mobile saisie rapide ────────────────────────────────────────────────

@app.route('/mobile', methods=['GET', 'POST'])
def mobile_quick():
    conn = get_db()
    if request.method == 'POST':
        action = request.form.get('action')
        if action == 'block':
            conn.execute(
                'INSERT INTO calendar_blocks(date,type,printer_id,notes) VALUES(?,?,?,?)',
                (request.form['date'], request.form.get('type', 'off'),
                 request.form.get('printer_id') or None,
                 request.form.get('notes', '')))
            conn.commit()
        elif action == 'shift':
            slot_id = request.form.get('slot_id')
            new_start = request.form.get('new_start')
            if slot_id and new_start:
                slot = conn.execute('SELECT * FROM print_slots WHERE id=?', (slot_id,)).fetchone()
                if slot:
                    try:
                        orig_s = datetime.fromisoformat(slot['planned_start'])
                        orig_e = datetime.fromisoformat(slot['planned_end'])
                        new_s  = datetime.fromisoformat(new_start)
                        delta  = new_s - orig_s
                        new_e  = (orig_e + delta).isoformat()
                        conn.execute('UPDATE print_slots SET planned_start=?,planned_end=? WHERE id=?',
                                     (new_s.isoformat(), new_e, slot_id))
                        conn.commit()
                    except Exception:
                        pass
        elif action == 'fail':
            slot_id  = request.form.get('slot_id')
            note     = request.form.get('failure_note', '')
            wasted_g = float(request.form.get('wasted_g', 0) or 0)
            if slot_id:
                slot = conn.execute('''
                    SELECT ps.weight_g as plate_g, j.material_id, j.weight_g
                    FROM print_slots ps JOIN jobs j ON ps.job_id=j.id
                    WHERE ps.id=?''', (slot_id,)).fetchone()
                if slot:
                    if wasted_g == 0:
                        wasted_g = slot['weight_g'] or 0
                    conn.execute(
                        "UPDATE print_slots SET status='failed', failure_note=?, wasted_g=? WHERE id=?",
                        (note, wasted_g, slot_id))
                    if slot['material_id'] and wasted_g > 0:
                        conn.execute(
                            'UPDATE materials SET stock_g=MAX(0,stock_g-?), total_wasted_g=COALESCE(total_wasted_g,0)+? WHERE id=?',
                            (wasted_g, wasted_g, slot['material_id']))
                    conn.commit()
        elif action == 'done':
            slot_id  = request.form.get('slot_id')
            actual_g = float(request.form.get('actual_g', 0) or 0)
            if slot_id:
                slot = conn.execute(
                    'SELECT j.material_id FROM print_slots ps JOIN jobs j ON ps.job_id=j.id WHERE ps.id=?',
                    (slot_id,)).fetchone()
                conn.execute("UPDATE print_slots SET status='done' WHERE id=?", (slot_id,))
                if slot and slot['material_id'] and actual_g > 0:
                    conn.execute('UPDATE materials SET stock_g=MAX(0,stock_g-?) WHERE id=?',
                                 (actual_g, slot['material_id']))
                conn.commit()
        conn.close()
        return redirect(url_for('mobile_quick'))

    printers = conn.execute('SELECT * FROM printers WHERE is_active=1 ORDER BY id').fetchall()
    today = date_cls.today().isoformat()
    upcoming = conn.execute('''
        SELECT ps.*, j.name as job_name, p.name as printer_name,
               c.name as client_name
        FROM print_slots ps
        JOIN jobs j ON ps.job_id=j.id
        JOIN printers p ON ps.printer_id=p.id
        LEFT JOIN clients c ON j.client_id=c.id
        WHERE ps.status='planned' AND date(ps.planned_start) >= ?
        ORDER BY ps.planned_start LIMIT 20''', (today,)).fetchall()
    conn.close()
    return render_template('mobile.html', printers=printers,
                           upcoming=upcoming, today=today)

# ─── Gestion échecs impression ────────────────────────────────────────────────

@app.route('/api/slots/<int:id>/fail', methods=['POST'])
def fail_slot(id):
    """Marque un créneau comme échoué, log le gaspillage filament."""
    conn = get_db()
    slot = conn.execute('''
        SELECT ps.*, j.weight_g, j.material_id, j.pieces_per_plate
        FROM print_slots ps JOIN jobs j ON ps.job_id=j.id
        WHERE ps.id=?''', (id,)).fetchone()
    if not slot:
        conn.close()
        return jsonify({'error': 'Créneau introuvable'}), 404

    note     = request.json.get('note', '') if request.is_json else request.form.get('note', '')
    wasted_g = float(request.json.get('wasted_g', 0) if request.is_json else request.form.get('wasted_g', 0) or 0)

    # Si wasted_g non précisé, estimer = poids plateau complet
    if wasted_g == 0 and slot['weight_g']:
        wasted_g = slot['weight_g']

    conn.execute("""UPDATE print_slots
                    SET status='failed', failure_note=?, wasted_g=?
                    WHERE id=?""", (note, wasted_g, id))

    # Déduire du stock matière si lié
    if slot['material_id'] and wasted_g > 0:
        conn.execute('UPDATE materials SET stock_g = MAX(0, stock_g - ?), total_wasted_g = COALESCE(total_wasted_g,0) + ? WHERE id=?',
                     (wasted_g, wasted_g, slot['material_id']))

    conn.commit(); conn.close()
    return jsonify({'ok': True, 'wasted_g': wasted_g})

@app.route('/api/slots/<int:id>/done', methods=['POST'])
def done_slot(id):
    """Marque un créneau comme terminé avec le poids réel consommé."""
    conn = get_db()
    slot = conn.execute('''
        SELECT ps.*, j.material_id FROM print_slots ps
        JOIN jobs j ON ps.job_id=j.id WHERE ps.id=?''', (id,)).fetchone()
    if not slot:
        conn.close()
        return jsonify({'error': 'Créneau introuvable'}), 404

    actual_g = float(request.json.get('actual_g', 0) if request.is_json else request.form.get('actual_g', 0) or 0)
    conn.execute("UPDATE print_slots SET status='done' WHERE id=?", (id,))

    # Déduire le poids réel consommé du stock
    if slot['material_id'] and actual_g > 0:
        conn.execute('UPDATE materials SET stock_g = MAX(0, stock_g - ?) WHERE id=?',
                     (actual_g, slot['material_id']))

    conn.commit(); conn.close()
    return jsonify({'ok': True})

# ─── API ──────────────────────────────────────────────────────────────────────

@app.route('/api/calculate', methods=['POST'])
def api_calculate():
    d = request.json
    s = dict(get_settings())
    conn = get_db()

    if d.get('machine_profile_id'):
        mp = conn.execute('SELECT * FROM machine_profiles WHERE id=?',(d['machine_profile_id'],)).fetchone()
        if mp:
            s['printer_price']          = mp['printer_price']
            s['printer_lifespan_hours'] = mp['printer_lifespan_hours']
            s['nozzle_price']           = mp['nozzle_price']
            s['nozzle_lifespan_hours']  = mp['nozzle_lifespan_hours']
            s['plate_price']            = mp['plate_price']
            s['plate_lifespan_hours']   = mp['plate_lifespan_hours']
            s['printer_power_kw']       = mp['printer_power_kw']
            s['electricity_price_kwh']  = mp['electricity_price_kwh']

    if d.get('handling_profile_id'):
        hp = conn.execute('SELECT * FROM handling_profiles WHERE id=?',(d['handling_profile_id'],)).fetchone()
        if hp:
            s['handling_rate_per_hour']     = hp['handling_rate_per_hour']
            s['handling_minutes_per_plate'] = hp['handling_minutes_per_plate']

    if d.get('material_profile_id'):
        marp = conn.execute('SELECT * FROM material_profiles WHERE id=?',(d['material_profile_id'],)).fetchone()
        if marp:
            s['material_margin_pct'] = marp['material_margin_pct']

    price_kg = None
    if d.get('material_id'):
        mat = conn.execute('SELECT price_per_kg FROM materials WHERE id=?',(d['material_id'],)).fetchone()
        if mat:
            price_kg = mat['price_per_kg']
    conn.close()

    pieces_per_plate = max(1, int(d.get('pieces_per_plate', 1)))
    order_qty        = max(1, int(d.get('order_qty', 1)))
    print_time_s     = int(d.get('hours',0))*3600 + int(d.get('minutes',0))*60

    result = calc(
        weight_g=float(d['weight_g']),
        print_time_s=print_time_s,
        design_mult=float(d.get('design_multiplier',1.80)),
        gross_margin_pct=float(d.get('gross_margin_pct',27.5)),
        discount_pct=float(d.get('discount_pct',0)),
        price_per_kg_override=price_kg, s=s,
        pieces_per_plate=pieces_per_plate)

    # Multi-piece extras
    nb_plateaux     = math.ceil(order_qty / pieces_per_plate)
    price_order     = round(result['final_price'] * order_qty, 2)
    last_plate_qty  = order_qty % pieces_per_plate
    plate_full      = last_plate_qty == 0

    cooldown_min    = s.get('cooldown_minutes', 15)
    total_print_min = (print_time_s / 60 + cooldown_min) * nb_plateaux - cooldown_min
    capacity        = calc_capacity(print_time_s, pieces_per_plate)

    result['order_qty']      = order_qty
    result['nb_plateaux']    = nb_plateaux
    result['price_order']    = price_order
    result['plate_full']     = plate_full
    result['last_plate_qty'] = last_plate_qty
    result['total_print_min']= round(total_print_min, 0)
    result['capacity']       = capacity
    return jsonify(result)

@app.route('/api/clients/<int:id>')
def api_client(id):
    conn = get_db()
    c = conn.execute('SELECT * FROM clients WHERE id=?',(id,)).fetchone()
    conn.close()
    if not c:
        return jsonify({}), 404
    return jsonify({'design_multiplier': c['design_multiplier']})

@app.route('/api/parse-3mf', methods=['POST'])
def api_parse_3mf():
    if 'file' not in request.files:
        return jsonify({'error': 'Aucun fichier'}), 400
    f = request.files['file']
    if not f.filename.lower().endswith('.3mf'):
        return jsonify({'error': 'Format .3mf requis'}), 400
    result = parse_3mf(f.stream)
    # Nettoyer .gcode.3mf ou .3mf du nom
    clean = re.sub(r'\.gcode\.3mf$', '', f.filename, flags=re.IGNORECASE)
    clean = re.sub(r'\.3mf$', '', clean, flags=re.IGNORECASE)
    result['filename'] = clean
    # Si une seule plaque, pré-sélectionner automatiquement
    if result['plates'] and len(result['plates']) == 1:
        p = result['plates'][0]
        result['weight_g'] = p['weight_g']
        result['hours']    = p['print_time_s'] // 3600
        result['minutes']  = (p['print_time_s'] % 3600) // 60
    return jsonify(result)

@app.route('/api/slots')
def api_slots():
    conn = get_db()
    slots = conn.execute('''
        SELECT ps.id, ps.job_id, ps.printer_id, ps.planned_start, ps.planned_end,
               ps.status, ps.failure_note,
               COALESCE(ps.pieces_ignored, 0) as pieces_ignored,
               ps.pieces_override,
               COALESCE(ps.pieces_override, j.pieces_per_plate) as effective_pieces,
               (SELECT COUNT(*) FROM print_slots ps2
                WHERE ps2.job_id = ps.job_id
                AND ps2.planned_start <= ps.planned_start
                AND ps2.id <= ps.id) as plate_num,
               (SELECT COUNT(*) FROM print_slots WHERE job_id = ps.job_id) as total_plates,
               (SELECT COALESCE(SUM(COALESCE(pieces_ignored,0)), 0) FROM print_slots
                WHERE job_id = ps.job_id) as job_total_ignored,
               j.name as job_name, j.print_time_s, j.final_price,
               j.pieces_per_plate, j.order_qty, j.weight_g,
               j.plate_name,
               p.name as printer_name,
               c.name as client_name,
               m.name as material_name, m.type as material_type, m.color as material_color
        FROM print_slots ps
        JOIN jobs j ON ps.job_id=j.id
        JOIN printers p ON ps.printer_id=p.id
        LEFT JOIN clients c ON j.client_id=c.id
        LEFT JOIN materials m ON j.material_id=m.id
        WHERE ps.planned_start >= datetime('now', '-7 days')
        ORDER BY ps.planned_start''').fetchall()
    conn.close()
    return jsonify([dict(s) for s in slots])

@app.route('/api/slots/<int:id>/pieces_override', methods=['POST'])
@login_required
def api_slot_pieces_override(id):
    d = request.get_json(silent=True) or {}
    pieces = d.get('pieces')
    conn = get_db()
    if pieces is None:
        conn.execute('UPDATE print_slots SET pieces_override=NULL WHERE id=?', (id,))
    else:
        conn.execute('UPDATE print_slots SET pieces_override=? WHERE id=?', (max(1, int(pieces)), id))
    conn.commit()
    conn.close()
    return jsonify({'ok': True})


def _check_capacity(conn, start_dt, end_dt, max_printers, exclude_id=None):
    """
    Vérifie que le nombre max d'imprimantes simultanées ne sera pas dépassé.

    Méthode event-point :
      1. Récupère tous les slots actifs qui chevauchent la fenêtre demandée
      2. Si < max_printers, retourne False immédiatement
      3. Sinon, teste le nb de slots actifs à chaque heure de début
         (points d'événement) et retourne True si le pic atteint max_printers.

    Note: n'inclut que les slots dont planned_end > now (évite de bloquer
    sur des impressions physiquement terminées mais non clôturées).

    Returns: (bool over_capacity, int peak_count)
    """
    q = """SELECT planned_start, planned_end FROM print_slots
           WHERE status NOT IN ('cancelled','failed','done')
           AND planned_start < ? AND planned_end > ?
           AND planned_end > datetime('now')"""
    params = [end_dt.isoformat(), start_dt.isoformat()]
    if exclude_id:
        q += ' AND id != ?'
        params.append(exclude_id)
    overlapping = conn.execute(q, params).fetchall()

    if len(overlapping) < max_printers:
        return False, len(overlapping)   # pas assez de chevauchements pour atteindre la limite

    # Points à tester : le début du créneau proposé + début de chaque slot existant dans la fenêtre
    event_times = [start_dt]
    for sl in overlapping:
        t = datetime.fromisoformat(sl['planned_start'])
        if start_dt <= t < end_dt:
            event_times.append(t)

    peak = 0
    for t in event_times:
        count = sum(
            1 for sl in overlapping
            if datetime.fromisoformat(sl['planned_start']) <= t
            < datetime.fromisoformat(sl['planned_end'])
        )
        if count > peak:
            peak = count
        if count >= max_printers:
            return True, count
    return False, peak

def _check_overlap(conn, printer_id, start_dt, end_dt, exclude_id=None):
    """Retourne le slot en conflit s'il existe, sinon None."""
    q = """SELECT ps.id, j.name as job_name, ps.planned_start, ps.planned_end
           FROM print_slots ps JOIN jobs j ON ps.job_id=j.id
           WHERE ps.printer_id=?
           AND ps.status NOT IN ('cancelled','failed','done')
           AND ps.planned_start < ? AND ps.planned_end > ?
           AND ps.planned_end > datetime('now')"""
    params = [printer_id, end_dt.isoformat(), start_dt.isoformat()]
    if exclude_id:
        q += ' AND ps.id != ?'
        params.append(exclude_id)
    return conn.execute(q, params).fetchone()

@app.route('/api/slots/new', methods=['POST'])
def api_new_slot():
    d = request.json
    job_id      = int(d['job_id'])
    printer_id  = int(d['printer_id'])
    start_str   = d['planned_start']   # ISO string
    conn = get_db()
    job = conn.execute('SELECT print_time_s FROM jobs WHERE id=?', (job_id,)).fetchone()
    s = get_settings()
    if not job:
        conn.close()
        return jsonify({'error': 'Job introuvable'}), 404
    cooldown_s  = int(s.get('cooldown_minutes', 15)) * 60
    start_dt    = datetime.fromisoformat(start_str)
    end_dt      = start_dt + timedelta(seconds=job['print_time_s'] + cooldown_s)
    already_done = bool(d.get('already_done', False))
    if already_done:
        # Retroactive — skip capacity/overlap checks, insert as done
        conn.execute(
            'INSERT INTO print_slots(job_id,printer_id,planned_start,planned_end,status) VALUES(?,?,?,?,?)',
            (job_id, printer_id, start_dt.isoformat(), end_dt.isoformat(), 'done'))
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    conflict = _check_overlap(conn, printer_id, start_dt, end_dt)
    if conflict:
        conn.close()
        end_conf = conflict['planned_end'][:16].replace('T', ' ')
        return jsonify({'error': f"Chevauchement avec « {conflict['job_name']} » (fin prévue {end_conf}). Choisissez un autre horaire ou une autre imprimante."}), 409
    max_printers = int(s.get('max_simultaneous_printers', 5))
    over, peak = _check_capacity(conn, start_dt, end_dt, max_printers)
    if over:
        conn.close()
        return jsonify({'error': f"Capacité maximale atteinte : {peak} impression(s) déjà en cours sur ce créneau (limite : {max_printers} simultanées). Décalez l'horaire ou attendez qu'une imprimante se libère."}), 409
    conn.execute('INSERT INTO print_slots(job_id,printer_id,planned_start,planned_end,status) VALUES(?,?,?,?,?)',
                 (job_id, printer_id, start_dt.isoformat(), end_dt.isoformat(), 'planned'))
    conn.commit(); conn.close()
    return jsonify({'ok': True})

@app.route('/api/slots/<int:id>/move', methods=['POST'])
def api_move_slot(id):
    d = request.json
    start_str = d['planned_start']
    conn = get_db()
    slot = conn.execute('SELECT ps.*, j.print_time_s FROM print_slots ps JOIN jobs j ON ps.job_id=j.id WHERE ps.id=?', (id,)).fetchone()
    if not slot:
        conn.close()
        return jsonify({'error': 'Slot introuvable'}), 404
    s = get_settings()
    cooldown_s = int(s.get('cooldown_minutes', 15)) * 60
    start_dt   = datetime.fromisoformat(start_str)
    end_dt     = start_dt + timedelta(seconds=slot['print_time_s'] + cooldown_s)
    conflict = _check_overlap(conn, slot['printer_id'], start_dt, end_dt, exclude_id=id)
    if conflict:
        conn.close()
        end_conf = conflict['planned_end'][:16].replace('T', ' ')
        return jsonify({'error': f"Chevauchement avec « {conflict['job_name']} » (fin prévue {end_conf})."}), 409
    max_printers = int(s.get('max_simultaneous_printers', 5))
    over, peak = _check_capacity(conn, start_dt, end_dt, max_printers, exclude_id=id)
    if over:
        conn.close()
        return jsonify({'error': f"Capacité maximale atteinte : {peak} impression(s) en cours sur ce créneau (limite : {max_printers} simultanées)."}), 409
    conn.execute('UPDATE print_slots SET planned_start=?,planned_end=? WHERE id=?',
                 (start_dt.isoformat(), end_dt.isoformat(), id))
    conn.commit(); conn.close()
    return jsonify({'ok': True})

@app.route('/api/slots/<int:id>/status', methods=['POST'])
def api_slot_status(id):
    d = request.json
    conn = get_db()
    conn.execute('UPDATE print_slots SET status=?,failure_note=? WHERE id=?',
                 (d.get('status','planned'), d.get('note',''), id))
    conn.commit(); conn.close()
    return jsonify({'ok': True})

@app.route('/api/slots/<int:id>/delete', methods=['POST'])
def api_delete_slot(id):
    conn = get_db()
    conn.execute('DELETE FROM print_slots WHERE id=?', (id,))
    conn.commit(); conn.close()
    return jsonify({'ok': True})

@app.route('/api/suggest-slot', methods=['POST'])
def api_suggest_slot():
    d = request.json
    print_time_s = int(d.get('print_time_s', 0))
    result = find_next_slot(print_time_s)
    if result:
        return jsonify(result)
    return jsonify({'error': 'Aucun créneau disponible dans les 60 prochains jours'}), 404

@app.route('/api/capacity', methods=['POST'])
def api_capacity():
    d = request.json
    print_time_s    = int(d.get('print_time_s', 0))
    pieces_per_plate = max(1, int(d.get('pieces_per_plate', 1)))
    result = calc_capacity(print_time_s, pieces_per_plate)
    return jsonify(result)

@app.route('/api/availability')
def api_availability():
    """Returns unavailable periods for the next 4 weeks as JSON for vis.js background items."""
    schedule = get_schedule_dict()
    today = date_cls.today()
    blocks = get_blocks_dict(today, today + timedelta(days=28))
    result = []
    for offset in range(28):
        d = today + timedelta(days=offset)
        date_str = str(d)
        block = next((b for b in blocks
                      if b['date'] <= date_str
                      and (b.get('date_end') or b['date']) >= date_str
                      and b['printer_id'] is None), None)
        mode = block['type'] if block else schedule[d.weekday()]['mode']
        if mode == 'off':
            result.append({
                'start': date_str + 'T00:00:00',
                'end':   date_str + 'T23:59:59',
                'type':  'off'
            })
        elif mode == 'tt':
            sched = schedule[d.weekday()]
            # Unavailable before tt_window_start
            result.append({'start': date_str+'T00:00:00', 'end': date_str+'T'+sched['tt_window_start']+':00', 'type':'unavailable'})
            # Unavailable after tt_window_end
            result.append({'start': date_str+'T'+sched['tt_window_end']+':00', 'end': date_str+'T23:59:59', 'type':'unavailable'})
    return jsonify(result)

# ─── iCal Export ──────────────────────────────────────────────────────────────

def _ical_escape(s):
    return str(s).replace('\\','\\\\').replace('\n','\\n').replace(',','\\,').replace(';','\\;')

def _ical_dt(dt_str):
    return dt_str.replace('-','').replace(':','').replace(' ','T').split('.')[0]

@app.route('/calendar.ics')
def calendar_ics():
    # Token optionnel : si ical_token est défini dans les settings, vérifier ?token=xxx
    conn = get_db()
    s_row = conn.execute("SELECT value FROM settings WHERE key='ical_token'").fetchone()
    ical_token = s_row['value'] if s_row else ''
    if ical_token and request.args.get('token', '') != ical_token:
        conn.close()
        return Response('Non autorisé', status=401)

    slots = conn.execute('''
        SELECT ps.*, j.name as job_name, j.order_qty, j.pieces_per_plate,
               j.weight_g, j.notes as job_notes,
               p.name as printer_name, c.name as client_name
        FROM print_slots ps
        JOIN jobs j ON ps.job_id=j.id
        JOIN printers p ON ps.printer_id=p.id
        LEFT JOIN clients c ON j.client_id=c.id
        WHERE ps.status NOT IN ('cancelled')
        ORDER BY ps.planned_start''').fetchall()

    blocks = conn.execute('''
        SELECT cb.*, p.name as printer_name
        FROM calendar_blocks cb
        LEFT JOIN printers p ON cb.printer_id=p.id
        ORDER BY cb.date''').fetchall()
    conn.close()

    now_str = datetime.now(timezone.utc).strftime('%Y%m%dT%H%M%SZ')
    lines = [
        'BEGIN:VCALENDAR',
        'VERSION:2.0',
        'PRODID:-//3D Pricing//Planning//FR',
        'CALSCALE:GREGORIAN',
        'METHOD:PUBLISH',
        'X-WR-CALNAME:Planning Impression 3D',
        'X-WR-TIMEZONE:Europe/Paris',
        'REFRESH-INTERVAL;VALUE=DURATION:PT1H',
    ]

    # Créneaux d'impression
    for s in slots:
        start = _ical_dt(s['planned_start'])
        end   = _ical_dt(s['planned_end'])
        client_part = f" — {s['client_name']}" if s['client_name'] else ''
        desc_parts = [
            f"Qty: {s['order_qty']} pcs | {s['pieces_per_plate']} pcs/plateau",
            f"Statut: {s['status']}",
        ]
        if s['failure_note']:
            desc_parts.append(f"Note: {s['failure_note']}")
        if s['job_notes']:
            desc_parts.append(s['job_notes'])
        lines += [
            'BEGIN:VEVENT',
            f"DTSTART:{start}",
            f"DTEND:{end}",
            f"DTSTAMP:{now_str}",
            f"SUMMARY:{_ical_escape(s['job_name'] + client_part + ' [' + s['printer_name'] + ']')}",
            f"DESCRIPTION:{_ical_escape(' | '.join(desc_parts))}",
            f"UID:slot-{s['id']}@3d-pricing",
            f"STATUS:{'CONFIRMED' if s['status']=='done' else 'TENTATIVE'}",
            f"CATEGORIES:{'Terminé' if s['status']=='done' else 'Planifié'}",
            'END:VEVENT',
        ]

    # Jours indisponibles / bloqués
    for b in blocks:
        date_val = b['date'].replace('-', '')
        # For DTEND: use date_end+1 if set, else date+1 (all-day exclusive end)
        end_src = b['date_end'] if b['date_end'] else b['date']
        d_obj   = datetime.strptime(end_src, '%Y-%m-%d') + timedelta(days=1)
        end_val = d_obj.strftime('%Y%m%d')
        label_map = {'off': '🔴 Jour off', 'maintenance': '🔧 Maintenance', 'other': '📌 Bloqué'}
        label = label_map.get(b['type'], b['type'])
        if b['printer_name']:
            label += f" — {b['printer_name']}"
        notes = b['notes'] or ''
        lines += [
            'BEGIN:VEVENT',
            f"DTSTART;VALUE=DATE:{date_val}",
            f"DTEND;VALUE=DATE:{end_val}",
            f"DTSTAMP:{now_str}",
            f"SUMMARY:{_ical_escape(label)}",
            f"DESCRIPTION:{_ical_escape(notes)}",
            f"UID:block-{b['id']}@3d-pricing",
            'TRANSP:TRANSPARENT',
            'STATUS:CONFIRMED',
            'END:VEVENT',
        ]

    lines.append('END:VCALENDAR')
    ical = '\r\n'.join(lines)
    return Response(ical, mimetype='text/calendar; charset=utf-8',
                    headers={'Content-Disposition': 'inline; filename=planning_3d.ics'})


# ─── Home Assistant — Sync ────────────────────────────────────────────────────

def _ha_fetch_all_states(ha_url, ha_token):
    """Retourne dict {entity_id: state_str} depuis l'API HA."""
    if not _requests:
        return {}
    headers = {'Authorization': f'Bearer {ha_token}'}
    r = _requests.get(f'{ha_url}/api/states', headers=headers, timeout=15)
    if r.status_code != 200:
        return {}
    return {s['entity_id']: s['state'] for s in r.json()}


def _ha_sync_once():
    """Poll HA et met a jour les print_slots actifs."""
    try:
        # Use direct connection with timeout — avoids locking conflicts with Flask request threads
        _conn = sqlite3.connect(DATABASE, timeout=15)
        _conn.row_factory = sqlite3.Row
        try:
            _s = _conn.execute("SELECT key, value FROM settings").fetchall()
        finally:
            _conn.close()
        s = {r['key']: r['value'] for r in _s}
        ha_url   = str(s.get('ha_url',   '')).rstrip('/')
        ha_token = str(s.get('ha_token', ''))
        if not ha_url or not ha_token:
            return
        states = _ha_fetch_all_states(ha_url, ha_token)
        if not states:
            return
        # Lire les imprimantes avec une connexion courte (lecture seule)
        _rc = sqlite3.connect(DATABASE, timeout=15)
        _rc.row_factory = sqlite3.Row
        printers = _rc.execute(
            "SELECT id, name, ha_entity_prefix FROM printers "
            "WHERE ha_entity_prefix IS NOT NULL AND ha_entity_prefix != ''"
        ).fetchall()
        _rc.close()

        now_iso = datetime.now(timezone.utc).replace(tzinfo=None).isoformat()
        window  = (datetime.now(timezone.utc).replace(tzinfo=None) + timedelta(minutes=30)).isoformat()
        for p in printers:
            prefix = p['ha_entity_prefix']
            ha_status = states.get(f'sensor.{prefix}_etat_de_l_impression', 'unknown')
            ha_error  = states.get(f'binary_sensor.{prefix}_erreur_d_impression', 'off')
            # Connexion courte par imprimante : on lit, on écrit, on commit, on ferme
            wc = sqlite3.connect(DATABASE, timeout=15, isolation_level=None)
            wc.row_factory = sqlite3.Row
            try:
                slot = wc.execute(
                    "SELECT id, status FROM print_slots "
                    "WHERE printer_id=? AND status IN ('planned','running') "
                    "AND planned_start <= ? AND planned_end >= ? "
                    "ORDER BY planned_start DESC LIMIT 1",
                    (p['id'], window, now_iso)
                ).fetchone()
                if not slot:
                    continue
                new_status = None
                if ha_error == 'on':
                    new_status = 'failed'
                elif ha_status == 'finish':
                    new_status = 'done'
                elif ha_status == 'running' and slot['status'] == 'planned':
                    new_status = 'running'
                if new_status:
                    wc.execute('UPDATE print_slots SET status=? WHERE id=?',
                               (new_status, slot['id']))
                if ha_ignored > 0:
                    wc.execute('UPDATE print_slots SET pieces_ignored=? WHERE id=?',
                               (ha_ignored, slot['id']))
            finally:
                wc.close()
    except Exception as e:
        print(f'[HA sync] {e}')


def _start_ha_poll():
    """Lance le thread de polling HA en arriere-plan."""
    def _loop():
        time.sleep(10)
        while True:
            _ha_sync_once()
            try:
                _c = sqlite3.connect(DATABASE, timeout=5)
                _c.row_factory = sqlite3.Row
                _interval = int(_c.execute("SELECT value FROM settings WHERE key='ha_poll_interval'").fetchone()['value'])
                _c.close()
            except Exception:
                _interval = 60
            time.sleep(max(10, _interval))
    t = threading.Thread(target=_loop, daemon=True, name='ha-poll')
    t.start()


@app.route('/api/ha/status')
@login_required
def api_ha_status():
    """Retourne le statut live HA pour toutes les imprimantes configurees."""
    s = get_settings()
    ha_url   = str(s.get('ha_url',   '')).rstrip('/')
    ha_token = str(s.get('ha_token', ''))
    if not ha_url or not ha_token:
        return jsonify({'error': 'HA non configure', 'printers': {}})
    try:
        if not _requests:
            return jsonify({'error': 'requests non installe', 'printers': {}})
        headers = {'Authorization': f'Bearer {ha_token}'}
        r = _requests.get(f'{ha_url}/api/states', headers=headers, timeout=10)
        if r.status_code != 200:
            return jsonify({'error': f'HA HTTP {r.status_code}', 'printers': {}})
        raw_states = r.json()
        states = {st['entity_id']: st['state'] for st in raw_states}
        conn = get_db()
        printers = conn.execute(
            "SELECT id, name, ha_entity_prefix FROM printers "
            "WHERE ha_entity_prefix IS NOT NULL AND ha_entity_prefix != ''"
        ).fetchall()
        conn.close()
        result = {}
        for p in printers:
            pfx = p['ha_entity_prefix']
            def gs(suf, default='unknown', _p=pfx):
                return states.get(f'sensor.{_p}_{suf}', default)
            def gb(suf, _p=pfx):
                return states.get(f'binary_sensor.{_p}_{suf}', 'off')
            try:   progress = int(float(gs('progression_de_l_impression', '0')))
            except: progress = 0
            try:   layer = int(float(gs('couche_actuelle', '0')))
            except: layer = 0
            try:   layers_total = int(float(gs('nombre_total_de_couches', '0')))
            except: layers_total = 0
            try:   pieces_ignored = int(float(gs('objets_ignores', '0')))
            except: pieces_ignored = 0
            try:   pieces_total = int(float(gs('objets_imprimables', '0')))
            except: pieces_total = 0
            result[str(p['id'])] = {
                'printer_name':   p['name'],
                'status':         gs('etat_de_l_impression'),
                'progress':       progress,
                'task_name':      gs('nom_de_la_tache'),
                'heure_fin':      gs('heure_de_fin'),
                'layer':          layer,
                'layers_total':   layers_total,
                'error':          gb('erreur_d_impression') == 'on',
                'pieces_ignored': pieces_ignored,
                'pieces_total':   pieces_total,
            }
        return jsonify({'printers': result})
    except Exception as e:
        return jsonify({'error': str(e), 'printers': {}})


# ─── Nextcloud WebDAV ────────────────────────────────────────────────────────

import xml.etree.ElementTree as ET
import requests as _requests
from urllib.parse import quote, unquote

def _nc_session():
    """Retourne (session_requests, base_url, username) ou None si non configuré."""
    s = get_settings()
    url  = str(s.get('nc_url', '')).rstrip('/')
    user = str(s.get('nc_username', ''))
    pwd  = str(s.get('nc_app_password', ''))
    if not url or not user or not pwd:
        return None, None, None
    sess = _requests.Session()
    sess.auth = (user, pwd)
    sess.headers['OCS-APIRequest'] = 'true'
    return sess, url, user

def _dav_url(base_url, username, path):
    """Construit l'URL WebDAV pour un chemin Nextcloud."""
    clean = path.lstrip('/')
    return f"{base_url}/remote.php/dav/files/{quote(username)}/{quote(clean, safe='/')}"

def _parse_propfind(xml_bytes, base_url, username, current_path):
    """Parse la réponse PROPFIND et retourne liste de dicts {name, path, is_dir, size, mtime}."""
    ns = {'d': 'DAV:'}
    root = ET.fromstring(xml_bytes)
    items = []
    dav_prefix = f"/remote.php/dav/files/{username}/"

    for resp in root.findall('d:response', ns):
        href = resp.findtext('d:href', '', ns)
        # Enlever le préfixe DAV pour obtenir le chemin relatif
        rel = unquote(href).replace(f"/remote.php/dav/files/{username}", '').rstrip('/')
        if not rel:
            rel = '/'
        # Ignorer l'entrée courante elle-même
        norm_current = ('/' + current_path.strip('/')).rstrip('/')
        if rel == norm_current or (rel + '/') == norm_current:
            continue

        propstat = resp.find('d:propstat/d:prop', ns)
        is_dir   = propstat.find('d:resourcetype/d:collection', ns) is not None if propstat is not None else False
        size     = int(propstat.findtext('d:getcontentlength', '0', ns) or 0) if propstat is not None else 0
        mtime    = propstat.findtext('d:getlastmodified', '', ns) if propstat is not None else ''
        name     = rel.split('/')[-1] or rel

        # Filtrer : dossiers ou extensions 3D
        # Gérer .gcode.3mf avant le split simple
        name_lower = name.lower()
        if name_lower.endswith('.gcode.3mf'):
            ext = '3mf'
        elif '.' in name:
            ext = name.rsplit('.', 1)[-1].lower()
        else:
            ext = ''
        if not is_dir and ext not in ('3mf', 'stl', 'step', 'stp', 'obj', 'gcode'):
            continue

        items.append({'name': name, 'path': rel, 'is_dir': is_dir,
                      'size': size, 'mtime': mtime, 'ext': ext if not is_dir else 'dir'})

    def _mtime_ts(m):
        try:
            from email.utils import parsedate_to_datetime
            return parsedate_to_datetime(m).timestamp()
        except Exception:
            return 0

    items.sort(key=lambda x: (0 if x['is_dir'] else 1, -_mtime_ts(x['mtime'])))
    return items

@app.route('/api/nextcloud/browse')
def nc_browse():
    path = request.args.get('path', '')
    sess, base_url, username = _nc_session()
    if not sess:
        return jsonify({'error': 'Nextcloud non configuré'}), 503

    # Si path vide, partir du nc_root_path
    if not path:
        s = get_settings()
        path = str(s.get('nc_root_path', '/3D'))

    dav_url = _dav_url(base_url, username, path)
    try:
        r = sess.request('PROPFIND', dav_url,
                         headers={'Depth': '1', 'Content-Type': 'application/xml'},
                         data='<?xml version="1.0"?><d:propfind xmlns:d="DAV:"><d:prop>'
                              '<d:resourcetype/><d:getcontentlength/><d:getlastmodified/>'
                              '</d:prop></d:propfind>',
                         timeout=10)
        if r.status_code == 404:
            return jsonify({'error': f'Chemin introuvable : {path}'}), 404
        if r.status_code not in (207, 200):
            return jsonify({'error': f'Erreur WebDAV {r.status_code}'}), 502
        items = _parse_propfind(r.content, base_url, username, path)
        # Construire le breadcrumb
        parts = [p for p in path.split('/') if p]
        breadcrumb = [{'name': 'Nextcloud', 'path': '/'}]
        for i, p in enumerate(parts):
            breadcrumb.append({'name': p, 'path': '/' + '/'.join(parts[:i+1])})
        return jsonify({'path': path, 'items': items, 'breadcrumb': breadcrumb})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/nextcloud/link')
def nc_link():
    """Génère un lien de partage Nextcloud pour un fichier via l'API OCS."""
    path = request.args.get('path', '')
    sess, base_url, username = _nc_session()
    if not sess or not path:
        return jsonify({'error': 'Paramètre manquant ou Nextcloud non configuré'}), 400
    try:
        r = sess.post(
            f"{base_url}/ocs/v2.php/apps/files_sharing/api/v1/shares",
            data={'path': path, 'shareType': 3, 'permissions': 1},  # 3=public link, 1=read
            headers={'Accept': 'application/json'},
            timeout=10)
        data = r.json()
        token = data.get('ocs', {}).get('data', {}).get('token', '')
        if token:
            return jsonify({'url': f"{base_url}/s/{token}"})
        return jsonify({'error': 'Impossible de créer le lien'}), 500
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# ─── Portail client ───────────────────────────────────────────────────────────

@app.route('/jobs/<int:id>/generate-token', methods=['POST'])
def generate_client_token(id):
    """Génère ou regénère le token de partage client pour une commande."""
    token = secrets.token_urlsafe(20)
    conn = get_db()
    conn.execute('UPDATE jobs SET client_token=? WHERE id=?', (token, id))
    conn.commit(); conn.close()
    return jsonify({'token': token,
                    'url': url_for('client_portal', token=token, _external=True)})

@app.route('/jobs/<int:id>/revoke-token', methods=['POST'])
def revoke_client_token(id):
    conn = get_db()
    conn.execute('UPDATE jobs SET client_token=NULL WHERE id=?', (id,))
    conn.commit(); conn.close()
    return jsonify({'ok': True})

@app.route('/portal/<token>')
def client_portal(token):
    """Vue publique (sans prix) pour le client."""
    conn = get_db()
    job = conn.execute('''
        SELECT j.*, c.name as client_name, m.name as material_name, m.type as material_type,
               m.color as material_color
        FROM jobs j
        LEFT JOIN clients c ON j.client_id=c.id
        LEFT JOIN materials m ON j.material_id=m.id
        WHERE j.client_token=?''', (token,)).fetchone()
    if not job:
        conn.close()
        return render_template('portal_404.html'), 404
    slots = conn.execute('''
        SELECT ps.planned_start, ps.planned_end, ps.status, ps.failure_note,
               p.name as printer_name
        FROM print_slots ps
        JOIN printers p ON ps.printer_id=p.id
        WHERE ps.job_id=? ORDER BY ps.planned_start''', (job['id'],)).fetchall()
    conn.close()
    return render_template('client_portal.html', job=job, slots=slots)

@app.route('/api/nextcloud/parse')
def nc_parse():
    """Fetch un fichier .3mf depuis Nextcloud et retourne les données parsées (JSON).
    Utilisé lors de la duplication d'un job pour recharger les plateaux sans re-sélectionner le fichier."""
    path = request.args.get('path', '')
    if not path:
        return jsonify({'error': 'Paramètre path manquant'}), 400
    sess, base_url, username = _nc_session()
    if not sess:
        return jsonify({'error': 'Nextcloud non configuré'}), 503
    dav_url = _dav_url(base_url, username, path)
    try:
        r = sess.get(dav_url, timeout=30)
        if r.status_code != 200:
            return jsonify({'error': f'Erreur WebDAV {r.status_code}'}), 502
        import io as _io
        result = parse_3mf(_io.BytesIO(r.content))
        filename = path.split('/')[-1]
        clean = re.sub(r'\.gcode\.3mf$', '', filename, flags=re.IGNORECASE)
        clean = re.sub(r'\.3mf$', '', clean, flags=re.IGNORECASE)
        result['filename'] = clean
        return jsonify(result)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/nextcloud/file')
def nc_file():
    """Proxy WebDAV : télécharge un fichier Nextcloud et le sert au navigateur."""
    path = request.args.get('path', '')
    if not path:
        return Response('Paramètre path manquant', status=400)
    sess, base_url, username = _nc_session()
    if not sess:
        return Response('Nextcloud non configuré', status=503)
    dav_url = _dav_url(base_url, username, path)
    try:
        r = sess.get(dav_url, timeout=30, stream=True)
        if r.status_code != 200:
            return Response(f'Erreur WebDAV {r.status_code}', status=502)
        filename = path.split('/')[-1]
        ext = filename.rsplit('.', 1)[-1].lower()
        mime_map = {
            'stl':  'model/stl',
            '3mf':  'model/3mf',
            'step': 'application/step',
            'stp':  'application/step',
            'obj':  'model/obj',
        }
        mime = mime_map.get(ext, 'application/octet-stream')
        return Response(r.content, mimetype=mime,
                        headers={'Content-Disposition': f'inline; filename="{filename}"',
                                 'Access-Control-Allow-Origin': '*'})
    except Exception as e:
        return Response(str(e), status=500)

# ─── Paramètres ───────────────────────────────────────────────────────────────

@app.route('/settings', methods=['GET','POST'])
def settings():
    if request.method == 'POST':
        conn = get_db()
        for key in request.form:
            conn.execute('INSERT OR REPLACE INTO settings VALUES (?,?)', (key, request.form[key]))
        conn.commit(); conn.close()
        return redirect(url_for('settings'))
    return render_template('settings.html', settings=get_settings())

# ─── Statistiques ─────────────────────────────────────────────────────────────

@app.route('/stats')
def stats():
    conn = get_db()
    by_client = conn.execute('''
        SELECT COALESCE(c.name,'Sans client') as name, COUNT(j.id) as jobs,
               ROUND(SUM(j.final_price),2) as revenue, ROUND(AVG(j.gross_margin_pct),1) as avg_margin
        FROM jobs j LEFT JOIN clients c ON j.client_id=c.id
        GROUP BY j.client_id ORDER BY revenue DESC''').fetchall()
    by_month = conn.execute('''
        SELECT strftime('%Y-%m',created_at) as month, COUNT(*) as jobs,
               ROUND(SUM(final_price),2) as revenue
        FROM jobs GROUP BY month ORDER BY month DESC LIMIT 12''').fetchall()
    by_material = conn.execute('''
        SELECT COALESCE(m.name,'Non defini') as name, m.type, COUNT(j.id) as jobs,
               ROUND(SUM(j.weight_g),1) as total_g, ROUND(SUM(j.final_price),2) as revenue
        FROM jobs j LEFT JOIN materials m ON j.material_id=m.id
        GROUP BY j.material_id ORDER BY total_g DESC''').fetchall()
    waste_by_material = conn.execute('''
        SELECT COALESCE(m.name,'Non défini') as name, m.type,
               COALESCE(m.color,'') as color,
               ROUND(COALESCE(m.total_wasted_g,0),1) as wasted_g,
               ROUND(COALESCE(m.stock_g,0),1) as stock_g
        FROM materials m
        WHERE COALESCE(m.total_wasted_g,0) > 0
        ORDER BY wasted_g DESC''').fetchall()
    failure_by_printer = conn.execute('''
        SELECT COALESCE(p.name,'Inconnu') as name,
               COUNT(ps.id) as total_slots,
               SUM(CASE WHEN ps.status='failed' THEN 1 ELSE 0 END) as failed_slots,
               ROUND(SUM(COALESCE(ps.wasted_g,0)),1) as wasted_g
        FROM print_slots ps
        LEFT JOIN printers p ON ps.printer_id=p.id
        GROUP BY ps.printer_id
        HAVING total_slots > 0
        ORDER BY failed_slots DESC''').fetchall()
    conn.close()
    return render_template('stats.html', by_client=by_client, by_month=by_month,
                           by_material=by_material, waste_by_material=waste_by_material,
                           failure_by_printer=failure_by_printer)

# ─── Export CSV ───────────────────────────────────────────────────────────────

@app.route('/export/csv')
def export_csv():
    conn = get_db()
    jobs = conn.execute('''
        SELECT j.created_at, COALESCE(c.name,'') as client,
               COALESCE(m.name,'') as material, m.type as material_type,
               j.source_file, j.name, j.weight_g, j.print_time_s,
               j.pieces_per_plate, j.order_qty,
               j.design_multiplier, j.gross_margin_pct, j.discount_pct,
               j.cout_fixe, j.handling_cost, j.design_cost, j.total_marge,
               j.margin_amount, j.cotisations_amount, j.vfl_amount,
               j.other_taxes_amount, j.tva_amount,
               j.price_per_piece, j.final_price
        FROM jobs j LEFT JOIN clients c ON j.client_id=c.id
        LEFT JOIN materials m ON j.material_id=m.id ORDER BY j.created_at DESC''').fetchall()
    conn.close()
    out = io.StringIO()
    w = csv.writer(out, delimiter=';')
    w.writerow(['Date','Client','Matiere','Type','Fichier source','Nom',
                'Poids(g)','Temps(s)','Pièces/plateau','Qté commande',
                'Coeff Design','Marge(%)','Remise(%)',
                'Cout fixe','Manutention','Design','Total marge',
                'Marge brute','Cotisations','VFL','Autres taxes','TVA',
                'Prix/pièce','Prix Final'])
    for j in jobs:
        w.writerow(list(j))
    return Response(out.getvalue(), mimetype='text/csv',
                    headers={'Content-Disposition': 'attachment; filename=tarifs_3d.csv'})

if __name__ == '__main__':
    init_db()
    _start_ha_poll()
    app.run(host='0.0.0.0', port=5000, debug=False)