"""Local HR portal for Planete (Portail RH Planète).

Compatible with SQLite (local dev & testing) and PostgreSQL (production).
Run with ``python app.py``. Create user with ``python app.py create-user``.
"""
from __future__ import annotations

import argparse
import hmac
import os
import secrets
import math
import sqlite3
from datetime import date, datetime, timedelta, timezone
from functools import wraps
from pathlib import Path

from flask import (Flask, abort, flash, g, redirect, render_template, request,
                   send_from_directory, session, url_for)
from flask_login import (LoginManager, UserMixin, current_user, login_required,
                         login_user, logout_user)
from werkzeug.security import check_password_hash, generate_password_hash
from werkzeug.utils import secure_filename
from werkzeug.middleware.proxy_fix import ProxyFix

from db import DBConnection, get_db_uri, is_postgres_uri
from leave_rules import add_months, entitlement_reference
from schema_upgrade import upgrade_request_types, upgrade_people_contacts, upgrade_access
from platform_settings import initialize_settings, company_holidays, rate_for
from celebrations import initialize_celebrations, events_today, digest, run_worker
from security_support import secret_key, initialize_security, login_attempt_allowed
from upload_security import validate_medical_file

ROOT = Path(__file__).resolve().parent
UPLOAD_DIR = Path(os.environ.get("RH_UPLOAD_DIR", ROOT / "instance" / "justificatifs"))
ALLOWED_EXTENSIONS = {"pdf", "png", "jpg", "jpeg"}
ROLES = {"employee", "manager", "hr", "admin"}
TEAMS = ('Planete', 'Solea', 'Viaxoft', 'Jancartier')
MODULES = {'requests': 'Demandes de congés', 'weekend': 'Week-end et astreintes',
           'personal_payroll': 'Ma rémunération', 'reviews': 'Validation des demandes',
           'planning': 'Planning équipe', 'stats': 'Statistiques', 'people': 'Équipe et soldes',
           'payroll': 'Paie mensuelle', 'medical': 'Justificatifs médicaux'}

app = Flask(__name__)
app.config.update(
    SECRET_KEY=secret_key(ROOT),
    SESSION_COOKIE_HTTPONLY=True,
    SESSION_COOKIE_SAMESITE='Lax',
    SESSION_COOKIE_SECURE=os.getenv('RH_ENV') == 'production',
    MAX_CONTENT_LENGTH=10 * 1024 * 1024,
    TEMPLATES_AUTO_RELOAD=True,
)
if os.getenv('RH_ALLOWED_HOSTS'):
    app.config['TRUSTED_HOSTS'] = [host.strip() for host in os.environ['RH_ALLOWED_HOSTS'].split(',') if host.strip()]
if os.getenv('RH_PROXY_HOPS') == '1':
    # Enable only when the hosting provider confirms one trusted reverse proxy.
    app.wsgi_app = ProxyFix(app.wsgi_app, x_for=1, x_proto=1)
login_manager = LoginManager(app)
login_manager.login_view = "login"
login_manager.login_message = None
DUMMY_PASSWORD_HASH = generate_password_hash(secrets.token_urlsafe(32))


@app.before_request
def csrf_protection():
    g.script_nonce = secrets.token_urlsafe(24)
    if request.method == "POST":
        token = request.form.get("csrf_token", "")
        expected = session.get("csrf_token", "")
        if not token or not expected or not hmac.compare_digest(token, expected):
            abort(400)
    if not session.get("csrf_token"):
        session["csrf_token"] = secrets.token_urlsafe(32)


@app.context_processor
def csrf_context():
    return {"csrf_token": session.get("csrf_token", ""), 'script_nonce': g.script_nonce}


def db() -> DBConnection:
    if "db" not in g:
        g.db = DBConnection()
    return g.db


@app.teardown_appcontext
def close_db(_error=None):
    conn = g.pop("db", None)
    if conn is not None:
        conn.close()


def init_db():
    uri = get_db_uri()
    if is_postgres_uri(uri):
        schema_file = ROOT / "schema_postgres.sql"
    else:
        schema_file = ROOT / "schema.sql"
    if schema_file.exists():
        schema_sql = schema_file.read_text(encoding="utf-8")
        with app.app_context():
            db().executescript(schema_sql)
            upgrade_request_types(db(), schema_sql, uri)
            upgrade_people_contacts(db())
            upgrade_access(db())
            initialize_settings(db(), TEAMS)
            initialize_celebrations(db())
            initialize_security(db())


if os.getenv('RH_ENV') != 'production':
    init_db()


class User(UserMixin):
    def __init__(self, row):
        self.id = row["id"]
        self.username = row["username"]
        self.full_name = row["full_name"]
        self.first_name = row["first_name"] if "first_name" in row.keys() and row["first_name"] else ""
        self.last_name = row["last_name"] if "last_name" in row.keys() and row["last_name"] else ""
        self.role = row["role"]
        self.team = row["team"]
        self.position_title = row["position_title"] if "position_title" in row.keys() and row["position_title"] else ""
        self.cin_nic = row["cin_nic"] if "cin_nic" in row.keys() and row["cin_nic"] else ""
        self.basic_salary = float(row["basic_salary"]) if "basic_salary" in row.keys() and row["basic_salary"] is not None else 0.0
        self.manager_id = row["manager_id"]
        self.payroll_admin = bool(row["payroll_admin"])
        self.active = bool(row["active"])


@login_manager.user_loader
def load_user(user_id):
    row = db().execute("SELECT * FROM users WHERE id=? AND active=TRUE AND account_enabled=1", (user_id,)).fetchone()
    return User(row) if row and session.get('auth_version', 0) == row['auth_version'] else None


def module_allowed(module):
    if not current_user.is_authenticated:
        return False
    role = current_user.role
    eligible = module in {'requests','weekend','personal_payroll','medical'} or (
        module in {'reviews','planning','stats','medical'} and role in {'manager','hr','admin'}) or (
        module == 'people' and role in {'hr','admin'}) or (
        module == 'payroll' and (role in {'hr','admin'} or current_user.payroll_admin))
    return eligible and not db().execute('SELECT 1 FROM module_restrictions WHERE user_id=? AND module=?',
                                        (current_user.id,module)).fetchone()


@app.context_processor
def access_context():
    return {'module_allowed': module_allowed}


@app.before_request
def enforce_access():
    if not current_user.is_authenticated or request.endpoint in {'static', 'pwa_worker'}:
        return
    person = db().execute('SELECT must_change_password FROM users WHERE id=?', (current_user.id,)).fetchone()
    if person['must_change_password'] and request.endpoint not in {'change_password','logout'}:
        return redirect(url_for('change_password'))
    endpoint_modules = {'new_request':'requests','weekend_work_new':'weekend','employee_payroll':'personal_payroll',
        'requests_admin':'reviews','decide_request':'reviews','weekend_admin':'reviews','decide_weekend':'reviews',
        'planning':'planning','stats':'stats','people_admin':'people','save_person_balances':'people',
        'payroll':'payroll','payroll_adjust':'payroll','attachment':'medical'}
    module = endpoint_modules.get(request.endpoint)
    if module and not module_allowed(module):
        abort(403)


def can_review(user_id):
    if int(user_id) == int(current_user.id):
        return False
    if current_user.role in {"admin", "hr"}:
        return True
    if current_user.role == "manager":
        row = db().execute("SELECT manager_id FROM users WHERE id=?", (user_id,)).fetchone()
        return row and row["manager_id"] == int(current_user.id)
    return False


def reviewer_required(fn):
    @wraps(fn)
    @login_required
    def wrapped(*args, **kwargs):
        if current_user.role not in {"manager", "hr", "admin"}:
            abort(403)
        return fn(*args, **kwargs)
    return wrapped


def payroll_required(fn):
    @wraps(fn)
    @login_required
    def wrapped(*args, **kwargs):
        if not current_user.payroll_admin and current_user.role not in {"admin", "hr"}:
            abort(403)
        return fn(*args, **kwargs)
    return wrapped


def audit(action, entity, entity_id=None, details=""):
    try:
        db().execute("INSERT INTO audit_log(actor_id,action,entity,entity_id,details) VALUES(?,?,?,?,?)",
                     (current_user.id, action, entity, entity_id, str(details)[:500]))
        db().commit()
    except Exception:
        db().rollback()
        app.logger.error('Audit entry could not be recorded; action=%s entity=%s', action, entity)


def is_public_holiday(day: date, country: str = "FR") -> bool:
    if country == 'FR':
        return day.isoformat() in company_holidays(db(), day.year)
    row = db().execute('SELECT 1 FROM public_holidays WHERE holiday_date=? AND country=?', (day.isoformat(),country)).fetchone()
    return bool(row)


def weekdays_count(start: date, end: date, country: str = "FR") -> int:
    calendars = {year: company_holidays(db(), year) for year in range(start.year,end.year+1)} if country == 'FR' else {}
    return sum(1 for n in range((end-start).days+1)
               if (day := start + timedelta(days=n)).weekday() < 5
               and (day.isoformat() not in calendars[day.year] if country == 'FR' else not is_public_holiday(day,country)))


def local_today():
    return datetime.now(timezone(timedelta(hours=4))).date()


def parse_service_date(value):
    if isinstance(value, date):
        return value
    if not value:
        return None
    return datetime.strptime(value, '%d/%m/%Y').date() if '/' in value else date.fromisoformat(value)


@app.template_filter('date_fr')
def date_fr(value):
    if not value:
        return '—'
    try:
        return parse_service_date(value).strftime('%d/%m/%Y')
    except (ValueError, TypeError):
        return value


@app.context_processor
def request_rights_context():
    if not current_user.is_authenticated:
        return {}
    balance = balance_for(current_user.id, local_today().year)
    available = balance['annual_opening'] + balance['annual_entitlement'] - balance['annual_used']
    context = {'annual_available': max(0, available)}
    if request.endpoint == 'new_request':
        context['medical_holiday_rules'] = {
            'dates': sorted(day for year in range(local_today().year-1, local_today().year+6)
                            for day in company_holidays(db(),year)),
            'fixed': ['01-01','05-01','05-08','07-14','08-15','11-01','11-11','12-25'],
            'country': 'FR',
            'working': [str(r['holiday_date']) for r in db().execute('SELECT holiday_date FROM calendar_overrides WHERE non_working=0').fetchall()],
            'extra': [str(r['holiday_date']) for r in db().execute('SELECT holiday_date FROM calendar_overrides WHERE non_working=1').fetchall()],
        }
    return context


@app.template_filter('month_fr')
def month_fr(value):
    try:
        return datetime.strptime(str(value), '%Y-%m').strftime('%m/%Y')
    except ValueError:
        return value


def calculate_wra_entitlements(start_date, year, user_id=None):
    # This is a dated reference, never an automatic posting to the year ledger.
    as_of = min(local_today(), date(year, 12, 31))
    if as_of.year < year:
        return 0.0, 0.0, "Exercice futur : droits non attribués"
    attendance = "confirmed"
    if user_id and start_date:
        review = db().execute("""SELECT status FROM attendance_reviews
            WHERE user_id=? AND service_start=?""", (user_id, start_date.isoformat())).fetchone()
        if review and review["status"] == "not_met":
            attendance = "not_met"
        # WFH is presence. A recorded absence prevents reliance on an old
        # confirmation and sends the case back to RH for review.
        if attendance == "confirmed":
            absence = db().execute("""SELECT status FROM leave_requests WHERE user_id=?
                AND status IN ('approved','pending') AND leave_type IN ('annual','sick','unplanned','unpaid')
                AND start_date<? AND end_date>=? LIMIT 1""",
                (user_id, add_months(start_date, 6).isoformat(), start_date.isoformat())).fetchone()
            if absence:
                attendance = "unknown"
    return entitlement_reference(start_date, as_of, attendance)


def balance_for(user_id, year):
    row = db().execute("SELECT * FROM balances WHERE user_id=? AND year=?", (user_id, year)).fetchone()
    result = dict(row) if row else dict(user_id=user_id, year=year, annual_opening=0, annual_entitlement=0,
                annual_used=0, sick_opening=0, sick_entitlement=0, sick_used=0,
                unvalidated=True)
    person = db().execute("SELECT start_date FROM users WHERE id=?", (user_id,)).fetchone()
    start = parse_service_date(person['start_date']) if person else None
    as_of = min(local_today(), date(year, 12, 31))
    if not row and year <= local_today().year:
        previous = db().execute('SELECT MAX(year) AS year FROM balances WHERE user_id=? AND year<?',
                                (user_id, year)).fetchone()
        # Only bridge years for which an imported/validated ledger exists.
        # Never infer an employee's historical opening balance from their hire date.
        if previous and previous['year'] is not None:
            prior = balance_for(user_id, int(previous['year']))
            for next_year in range(int(previous['year']) + 1, year + 1):
                annual_remaining = max(0.0, prior['annual_opening'] + prior['annual_entitlement'] - prior['annual_used'])
                sick_remaining = max(0.0, prior['sick_opening'] + prior['sick_entitlement'] - prior['sick_used'])
                mature = start and add_months(start, 12) <= min(local_today(), date(next_year, 12, 31))
                prior = dict(annual_opening=annual_remaining, sick_opening=sick_remaining,
                             annual_entitlement=22.0 if mature else 0.0,
                             sick_entitlement=15.0 if mature else 0.0,
                             annual_used=0.0, sick_used=0.0)
            result.update(prior, automatic_rollover=True, unvalidated=False)
    if start and start <= as_of and year <= as_of.year:
        # Imported entitlements are a floor, not an amount to add again.
        # Only the early monthly rights are automated here; ordinary rights
        # and carryovers remain the validated RH ledger.
        absence = db().execute("""SELECT 1 FROM leave_requests WHERE user_id=?
            AND status IN ('pending','approved') AND leave_type IN ('annual','sick','unplanned','unpaid')
            AND start_date<? AND end_date>=? LIMIT 1""",
            (user_id, add_months(start, 6).isoformat(), start.isoformat())).fetchone()
        review = db().execute("SELECT status FROM attendance_reviews WHERE user_id=? AND service_start=?",
                             (user_id, start.isoformat())).fetchone()
        eligible = not absence and not (review and review['status'] == 'not_met')
        credits = sum(credit.year == year and credit <= as_of
                      for credit in (add_months(start, n) for n in range(6, 12))) if eligible else 0
        # Keep early credits visible after the anniversary until ordinary
        # rights have been entered; additional statutory days are separate.
        result['annual_entitlement'] = max(float(result['annual_entitlement']), 2.0 + credits)
        result['sick_entitlement'] = max(float(result['sick_entitlement']), float(credits))
        result['automatic_early_days'] = credits
        result['unvalidated'] = not row and not result.get('automatic_rollover') and as_of >= add_months(start, 12)
    return result


def annual_request_error(user_id, start, end, fraction, exclude_id=None):
    for year in range(start.year, end.year + 1):
        balance = balance_for(user_id, year)
        pending = db().execute("""SELECT * FROM leave_requests WHERE user_id=? AND leave_type='annual'
            AND status='pending' AND start_date<=? AND end_date>=?""",
            (user_id, f'{year}-12-31', f'{year}-01-01')).fetchall()
        reserved = sum(weekdays_count(max(parse_service_date(r['start_date']), date(year, 1, 1)),
                        min(parse_service_date(r['end_date']), date(year, 12, 31))) * float(r['day_fraction'])
                       for r in pending if r['id'] != exclude_id)
        available = balance['annual_opening'] + balance['annual_entitlement'] - balance['annual_used'] - reserved
        needed = weekdays_count(max(start, date(year, 1, 1)), min(end, date(year, 12, 31))) * fraction
        if needed > available:
            return f'Solde payé disponible insuffisant pour {year} ({max(0, available):g} j). Choisissez un congé sans solde ou attendez l’acquisition des droits.'
    return None


def menstrual_request_error(user_id, start, end, fraction, exclude_id=None):
    if (start.year, start.month) != (end.year, end.month) or weekdays_count(start, end) * fraction != 1:
        return 'Le congé menstruel concerne une journée ouvrable dans un même mois, selon les conditions légales.'
    first = start.replace(day=1)
    following = add_months(first, 1)
    existing = db().execute("""SELECT id FROM leave_requests WHERE user_id=? AND leave_type='menstrual'
        AND status IN ('pending','approved') AND start_date<? AND end_date>=?""",
        (user_id, following.isoformat(), first.isoformat())).fetchall()
    if any(row['id'] != exclude_id for row in existing):
        return 'Une demande de congé menstruel existe déjà pour ce mois.'
    return None


@app.route("/login", methods=["GET", "POST"])
def login():
    if current_user.is_authenticated:
        return redirect(url_for("dashboard"))
    if request.method == "POST":
        username = request.form.get('username', '').strip()[:64]
        if not login_attempt_allowed(db(), app.secret_key, username, request.remote_addr or 'unknown'):
            response = app.make_response((render_template('error.html', code=429, message='Trop de tentatives. Réessayez dans 15 minutes.'), 429))
            response.headers['Retry-After'] = '900'
            return response
        row = db().execute("SELECT * FROM users WHERE username=? AND active=TRUE AND account_enabled=1",
                           (username,)).fetchone()
        valid_password = check_password_hash(row['password_hash'] if row else DUMMY_PASSWORD_HASH, request.form.get('password', ''))
        if row and valid_password:
            session.clear()
            session['csrf_token'] = secrets.token_urlsafe(32)
            login_user(User(row))
            session['auth_version'] = row['auth_version']
            return redirect(url_for("dashboard"))
        app.logger.warning('Login refused')
        flash("Identifiants incorrects.", "danger")
    return render_template("login.html")


@app.post("/logout")
@login_required
def logout():
    logout_user()
    session.clear()
    return redirect(url_for("login"))


@app.get('/sw.js')
def pwa_worker():
    response = send_from_directory(app.static_folder, 'sw.js', mimetype='application/javascript')
    response.headers['Cache-Control'] = 'no-cache'
    return response


@app.get('/staff')
@app.get('/staff/planning')
@app.get('/staff/account')
@login_required
def staff_space():
    today = local_today()
    section = request.path.rsplit('/', 1)[-1]
    if section not in ('planning', 'account'):
        section = 'home'
    try:
        first = date.fromisoformat(request.args.get('month', today.strftime('%Y-%m')) + '-01')
    except ValueError:
        first = today.replace(day=1)
    following = add_months(first, 1)
    leaves = db().execute('SELECT * FROM leave_requests WHERE user_id=? AND start_date<? AND end_date>=? ORDER BY start_date',
                          (current_user.id, following.isoformat(), first.isoformat())).fetchall()
    recent = db().execute('SELECT * FROM leave_requests WHERE user_id=? ORDER BY id DESC LIMIT 8', (current_user.id,)).fetchall()
    weekends = db().execute('SELECT * FROM weekend_work WHERE user_id=? AND work_date>=? AND work_date<? ORDER BY work_date',
                            (current_user.id, first.isoformat(), following.isoformat())).fetchall()
    profile = db().execute('SELECT full_name, team, position_title, start_date, email, phone FROM users WHERE id=?', (current_user.id,)).fetchone()
    return render_template('staff.html', section=section, profile=profile, balance=balance_for(current_user.id, today.year),
                           year=today.year, month=first.strftime('%Y-%m'), leaves=leaves, recent=recent, weekends=weekends)


@app.get("/")
@login_required
def dashboard():
    year = local_today().year
    month = request.args.get("month", local_today().strftime("%Y-%m"))
    try:
        first = date.fromisoformat(month + "-01")
        next_month = (first.replace(day=28) + timedelta(days=4)).replace(day=1)
    except ValueError:
        month = local_today().strftime("%Y-%m")
        first = date.fromisoformat(month + "-01")
        next_month = (first.replace(day=28) + timedelta(days=4)).replace(day=1)

    bal = balance_for(current_user.id, year)
    pending = db().execute("SELECT COUNT(*) n FROM leave_requests WHERE user_id=? AND status='pending'",
                           (current_user.id,)).fetchone()["n"]
    requests_recent = db().execute("SELECT * FROM leave_requests WHERE user_id=? ORDER BY id DESC LIMIT 6",
                                   (current_user.id,)).fetchall()

    # Personal monthly planning
    my_leaves = db().execute("""SELECT * FROM leave_requests WHERE user_id=?
        AND start_date<? AND end_date>=? AND status IN ('pending','approved') ORDER BY start_date""",
        (current_user.id, next_month.isoformat(), first.isoformat())).fetchall()

    my_weekends = db().execute("""SELECT * FROM weekend_work WHERE user_id=?
        AND work_date>=? AND work_date<? ORDER BY work_date""",
        (current_user.id, first.isoformat(), next_month.isoformat())).fetchall()

    # WRA Status
    user_row = db().execute("SELECT start_date FROM users WHERE id=?", (current_user.id,)).fetchone()
    start_d = None
    if user_row and user_row["start_date"]:
        try:
            start_d = parse_service_date(user_row["start_date"])
        except ValueError:
            pass
    _, _, wra_status = calculate_wra_entitlements(start_d, year, user_id=current_user.id)

    available_months = [add_months(local_today().replace(day=1), -offset).strftime("%Y-%m") for offset in range(12)]

    admin_pending = None
    if current_user.role in {"manager", "hr", "admin"}:
        if current_user.role == "manager":
            admin_pending = db().execute("""SELECT COUNT(*) n FROM leave_requests r JOIN users u ON u.id=r.user_id
                WHERE u.manager_id=? AND r.status='pending'""", (current_user.id,)).fetchone()["n"]
        else:
            admin_pending = db().execute("SELECT COUNT(*) n FROM leave_requests WHERE status='pending'").fetchone()["n"]
    return render_template("dashboard.html", balance=bal, pending=pending,
                           requests=requests_recent, admin_pending=admin_pending, year=year,
                           month=month, my_leaves=my_leaves, my_weekends=my_weekends,
                           wra_status=wra_status, available_months=available_months)


@app.route("/requests/new", methods=["GET", "POST"])
@login_required
def new_request():
    if request.method == "POST":
        db().lock_person(current_user.id)
        kind = request.form.get("leave_type")
        try:
            start = parse_service_date(request.form.get("start_date", ""))
            end = parse_service_date(request.form.get("end_date", ""))
            if start is None or end is None:
                raise ValueError('Missing date')
            fraction = float(request.form.get("day_fraction", "1"))
        except (TypeError, ValueError):
            flash("Vérifiez les dates et la durée demandée.", "danger")
            return render_template("request_form.html")
        if kind not in {"annual", "sick", "unplanned", "wfh", "unpaid", "menstrual"} or end < start or (end-start).days >= 366 or fraction not in {0.5, 1.0}:
            flash("La demande n’est pas valide.", "danger")
            return render_template("request_form.html")
        if fraction == 0.5 and start != end:
            flash("Une demi-journée doit concerner une seule date.", "danger")
            return render_template("request_form.html")
        person = db().execute('SELECT start_date FROM users WHERE id=?', (current_user.id,)).fetchone()
        service_start = parse_service_date(person['start_date'])
        if service_start and start < service_start:
            flash('La demande ne peut pas précéder la date d’entrée.', 'danger')
            return render_template('request_form.html')
        if kind == 'annual':
            error = annual_request_error(current_user.id, start, end, fraction)
            if error:
                flash(error, 'danger')
                return render_template('request_form.html')
        if kind == 'menstrual':
            error = menstrual_request_error(current_user.id, start, end, fraction)
            if error:
                flash(error, 'danger')
                return render_template('request_form.html')
        conflict = db().execute("""SELECT 1 FROM leave_requests WHERE user_id=? AND status IN ('approved','pending')
            AND start_date<=? AND end_date>=? LIMIT 1""", (current_user.id, end.isoformat(), start.isoformat())).fetchone()
        if conflict:
            flash("Une demande existe déjà sur cette période. Consultez votre planning avant de réessayer.", "danger")
            return render_template("request_form.html")
        if kind == "sick" and weekdays_count(start, end) > 3 and not (request.files.get("attachment") and request.files["attachment"].filename):
            flash("Pour une absence sick de plus de 3 jours ouvrables, ajoutez le certificat médical.", "danger")
            return render_template("request_form.html")
        attachment = None
        uploaded = request.files.get("attachment") if kind == 'sick' else None
        if uploaded and uploaded.filename:
            existing_files = db().execute('SELECT attachment FROM leave_requests WHERE user_id=? AND attachment IS NOT NULL', (current_user.id,)).fetchall()
            used_bytes = sum((UPLOAD_DIR / item['attachment']).stat().st_size for item in existing_files if (UPLOAD_DIR / item['attachment']).is_file())
            if len(existing_files) >= 100 or used_bytes >= 100 * 1024 * 1024:
                flash('Le quota de justificatifs est atteint. Contactez RH.', 'danger')
                return render_template('request_form.html')
            ext = uploaded.filename.rsplit(".", 1)[-1].lower() if "." in uploaded.filename else ""
            if ext not in ALLOWED_EXTENSIONS:
                flash("Justificatif accepté : PDF, PNG ou JPG.", "danger")
                return render_template("request_form.html")
            if not validate_medical_file(uploaded, ext):
                flash('Justificatif illisible ou non accepté. Utilisez une photo PNG/JPG ou un PDF simple non chiffré, sans formulaire ni annotation.', 'danger')
                return render_template('request_form.html')
            UPLOAD_DIR.mkdir(parents=True, exist_ok=True)
            attachment = f"{secrets.token_hex(16)}.{ext}"
            uploaded.save(UPLOAD_DIR / attachment)
        try:
            cur = db().execute("""INSERT INTO leave_requests(user_id,leave_type,start_date,end_date,day_fraction,reason,attachment)
                VALUES(?,?,?,?,?,?,?)""", (current_user.id, kind, start.isoformat(), end.isoformat(), fraction,
                                          request.form.get("reason", "").strip(), attachment))
            db().commit()
        except Exception:
            db().rollback()
            if attachment:
                (UPLOAD_DIR / attachment).unlink(missing_ok=True)
            raise
        audit("created", "leave_request", cur.lastrowid, kind)
        flash("Votre demande a été envoyée pour validation.", "success")
        return redirect(url_for("dashboard"))
    return render_template("request_form.html")


@app.get("/justificatifs/<filename>")
@login_required
def attachment(filename):
    row = db().execute("SELECT user_id FROM leave_requests WHERE attachment=?", (filename,)).fetchone()
    if not row or (row["user_id"] != int(current_user.id) and not can_review(row["user_id"])):
        abort(404)
    return send_from_directory(UPLOAD_DIR, filename, as_attachment=True)


@app.get("/admin/requests")
@reviewer_required
def requests_admin():
    status = request.args.get("status", "pending")
    clauses, params = ["r.status=?"], [status]
    if current_user.role == "manager":
        clauses.append("u.manager_id=?")
        params.append(current_user.id)
    rows = db().execute(f"""SELECT r.*,u.full_name,u.team FROM leave_requests r JOIN users u ON u.id=r.user_id
        WHERE {' AND '.join(clauses)} ORDER BY r.created_at DESC""", params).fetchall()
    return render_template("requests_admin.html", requests=rows, status=status)


@app.route("/admin/people", methods=["GET", "POST"])
@reviewer_required
def people_admin():
    if current_user.role not in {"hr", "admin"}:
        abort(403)
    if request.method == "POST":
        user_id = request.form.get("user_id", "").strip()
        existing = db().execute('SELECT * FROM users WHERE id=?', (user_id,)).fetchone() if user_id else None
        if user_id and not existing:
            abort(404)
        if existing:
            db().lock_person(existing['id'])
        username = existing['username'] if existing else 'pending.' + secrets.token_hex(8)
        full_name = request.form.get("full_name", "").strip()
        first_name = request.form.get("first_name", "").strip()
        last_name = request.form.get("last_name", "").strip()
        if first_name and last_name:
            full_name = f'{first_name} {last_name}'
        team = request.form.get("team", "").strip()
        position_title = request.form.get("position_title", "").strip()
        cin_nic = request.form.get("cin_nic", "").strip()
        if any(len(value) > limit for value, limit in ((first_name,64),(last_name,64),(full_name,128),(team,64),(position_title,128))):
            flash('Les informations d’identité ou de poste sont trop longues.', 'danger')
            return redirect(url_for('people_admin', edit=user_id or None, create='1' if not user_id else None))
        if len(cin_nic)>32:
            flash('Le numéro d’identité doit comporter au maximum 32 caractères.', 'danger')
            return redirect(url_for('people_admin', edit=user_id or None, create='1' if not user_id else None))
        start_date = request.form.get("start_date", "").strip() or None
        manager_id = request.form.get("manager_id", "").strip() or None
        contact_fields = ('phone', 'email', 'postal_address', 'emergency_contact_name', 'emergency_contact_phone',
                          'address_line1', 'address_line2', 'postal_code', 'city', 'country')
        contacts = {field: request.form.get(field, '').strip() for field in contact_fields}
        try:
            birth_date = parse_service_date(request.form.get('birth_date',''))
            if birth_date and birth_date >= local_today():
                raise ValueError()
        except ValueError:
            flash('La date de naissance doit être valide et antérieure à aujourd’hui.', 'danger')
            return redirect(url_for('people_admin',edit=user_id or None,create='1' if not user_id else None))
        bank = [request.form.get(field,'').strip() for field in ('bank_name','bank_account','bank_account_holder')]
        if any(len(value)>limit for value,limit in zip(bank,(64,64,128))):
            abort(400)
        contacts['postal_address'] = '\n'.join(filter(None, (contacts['address_line1'], contacts['address_line2'],
                                                ' '.join(filter(None, (contacts['postal_code'], contacts['city']))), contacts['country'])))
        limits = {'phone': 32, 'email': 128, 'postal_address': 1000, 'emergency_contact_name': 254, 'emergency_contact_phone': 254,
                  'address_line1': 254, 'address_line2': 254, 'postal_code': 32, 'city': 128, 'country': 128}
        if any(len(value) > limits[field] for field, value in contacts.items()):
            flash('Les coordonnées saisies sont trop longues.', 'danger')
            return redirect(url_for('people_admin', edit=user_id or None, create='1' if not user_id else None))
        if contacts['email'] and ('@' not in contacts['email'] or any(ch.isspace() for ch in contacts['email'])):
            flash('Veuillez saisir une adresse e-mail valide.', 'danger')
            return redirect(url_for('people_admin', edit=user_id or None, create='1' if not user_id else None))
        try:
            year = int(request.form.get("year", local_today().year))
            if not 1900 <= year <= 2099:
                raise ValueError()
        except ValueError:
            abort(400)

        try:
            basic_salary = float(request.form['basic_salary'])
            if not math.isfinite(basic_salary) or basic_salary < 0:
                raise ValueError()
        except (ValueError, KeyError):
            flash('Le salaire de base doit être un montant positif ou nul.', 'danger')
            return redirect(url_for('people_admin', edit=user_id or None, year=year, create='1' if not user_id else None))

        valid_function = db().execute('SELECT 1 FROM job_functions WHERE name=? AND enabled=1', (position_title,)).fetchone()
        valid_team = db().execute('SELECT 1 FROM company_teams WHERE name=? AND enabled=1', (team,)).fetchone()
        if (not valid_team and (not existing or existing['team'] != team)) or not team or not position_title or (not valid_function and (not existing or existing['position_title'] != position_title)):
            flash('Sélectionnez une équipe et une fonction configurée.', 'danger')
            return redirect(url_for('people_admin', edit=user_id or None, create='1' if not user_id else None))
        if not first_name or not last_name or not start_date or not cin_nic:
            flash("Prénom, nom, date d’entrée et NIC / identité sont obligatoires.", "danger")
            return redirect(url_for("people_admin"))
        if db().execute("SELECT id FROM users WHERE UPPER(TRIM(cin_nic))=? AND id!=?", (cin_nic.upper(), int(user_id or 0))).fetchone():
            flash('Ce NIC / numéro d’identité est déjà utilisé.', 'danger')
            return redirect(url_for('people_admin', edit=user_id or None, create='1' if not user_id else None))
        existing = db().execute('SELECT * FROM users WHERE id=?', (user_id,)).fetchone() if user_id else None
        if user_id and not existing:
            abort(404)
        if existing and current_user.role == 'hr' and existing['role'] in {'hr','admin'}:
            abort(403)
        role = existing['role'] if existing else 'employee'
        if start_date:
            try:
                start_date = parse_service_date(start_date).isoformat()
            except ValueError:
                flash("La date d’entrée n’est pas valide.", "danger")
                return redirect(url_for("people_admin"))
        duplicate = db().execute("SELECT id FROM users WHERE username=? AND id!=COALESCE(NULLIF(?,''),'0')",
                                 (username, user_id)).fetchone()
        if duplicate:
            flash("Cet identifiant est déjà utilisé.", "danger")
            return redirect(url_for("people_admin"))
        if manager_id and (not manager_id.isdigit() or not db().execute(
                "SELECT 1 FROM users WHERE id=? AND role IN ('manager','hr','admin') AND active=TRUE",
                (manager_id,)).fetchone()):
            flash("Choisissez un manager actif.", "danger")
            return redirect(url_for("people_admin"))
        if user_id and manager_id and int(manager_id) == int(user_id):
            flash("Un salarié ne peut pas être son propre manager.", "danger")
            return redirect(url_for("people_admin"))
        try:
            annual_opening = float(request.form.get("annual_opening", "0") or 0)
            annual_entitlement = float(request.form.get("annual_entitlement", "0") or 0)
            annual_used = float(request.form.get("annual_used", "0") or 0)
            sick_opening = float(request.form.get("sick_opening", "0") or 0)
            sick_entitlement = float(request.form.get("sick_entitlement", "0") or 0)
            sick_used = float(request.form.get("sick_used", "0") or 0)
        except ValueError:
            flash("Les soldes et droits doivent être des nombres.", "danger")
            return redirect(url_for("people_admin"))
        conn = db()
        if user_id:
            conn.execute("""UPDATE users SET username=?, full_name=?, first_name=?, last_name=?,
                         team=?, position_title=?, cin_nic=?, basic_salary=?, start_date=?, manager_id=?, role=?
                         WHERE id=?""",
                         (username, full_name, first_name, last_name, team, position_title, cin_nic,
                          basic_salary, start_date, manager_id, role, user_id))
            target_id = int(user_id)
        else:
            password = secrets.token_urlsafe(32)
            cur = conn.execute("""INSERT INTO users(username,full_name,first_name,last_name,password_hash,role,team,position_title,cin_nic,basic_salary,start_date,manager_id)
                VALUES(?,?,?,?,?,?,?,?,?,?,?,?)""", (username, full_name, first_name, last_name,
                                                               generate_password_hash(password), role, team, position_title, cin_nic,
                                                               basic_salary, start_date, manager_id))
            target_id = cur.lastrowid
            conn.execute('UPDATE users SET account_enabled=0 WHERE id=?', (target_id,))
        conn.execute('UPDATE users SET phone=?,email=?,postal_address=?,emergency_contact_name=?,emergency_contact_phone=?,address_line1=?,address_line2=?,postal_code=?,city=?,country=? WHERE id=?',
                     (*[contacts[field] for field in contact_fields], target_id))
        flags = [int(request.form.get('birthday_public')=='on'),0,
                 int(request.form.get('birthday_email')=='on'),int(request.form.get('anniversary_public')=='on')]
        conn.execute('UPDATE users SET birth_date=?,bank_name=?,bank_account=?,bank_account_holder=?,birthday_public=?,birthday_show_age=?,birthday_email=?,anniversary_public=? WHERE id=?',
                     (birth_date.isoformat() if birth_date else '',*bank,*flags,target_id))
        if user_id:
            saved_balance = db().execute('SELECT * FROM balances WHERE user_id=? AND year=?', (target_id, year)).fetchone()
            saved_balance = saved_balance or balance_for(target_id, year)
            annual_opening, annual_entitlement, annual_used = (saved_balance[k] for k in ('annual_opening','annual_entitlement','annual_used'))
            sick_opening, sick_entitlement, sick_used = (saved_balance[k] for k in ('sick_opening','sick_entitlement','sick_used'))
        conn.execute("""INSERT INTO balances(user_id,year,annual_opening,annual_entitlement,annual_used,sick_opening,sick_entitlement,sick_used)
            VALUES(?,?,?,?,?,?,?,?) ON CONFLICT(user_id,year) DO UPDATE SET annual_opening=excluded.annual_opening,
            annual_entitlement=excluded.annual_entitlement,annual_used=excluded.annual_used,
            sick_opening=excluded.sick_opening,sick_entitlement=excluded.sick_entitlement,sick_used=excluded.sick_used""",
                     (target_id, year, annual_opening, annual_entitlement, annual_used, sick_opening,
                      sick_entitlement, sick_used))
        attendance_status = request.form.get("attendance_status", "unknown")
        attendance_note = request.form.get("attendance_note", "").strip()
        if attendance_status not in {"unknown", "confirmed", "not_met"}:
            abort(400)
        if start_date and 'attendance_status' in request.form:
            if attendance_status != "unknown" and (not attendance_note or
                    local_today() < add_months(date.fromisoformat(start_date), 6)):
                conn.rollback()
                flash("Vérifiez les six mois complets et indiquez la source de la vérification d’assiduité.", "danger")
                return redirect(url_for("people_admin", edit=target_id, year=year))
            conn.execute("""INSERT INTO attendance_reviews(user_id,service_start,status,note,reviewed_by)
                VALUES(?,?,?,?,?) ON CONFLICT(user_id,service_start) DO UPDATE SET
                status=excluded.status,note=excluded.note,reviewed_by=excluded.reviewed_by,
                reviewed_at=CURRENT_TIMESTAMP""",
                (target_id, start_date, attendance_status, attendance_note, current_user.id))
        conn.commit()
        audit("updated" if user_id else "created", "user", target_id,
              f"balance year={year}; attendance={attendance_status}; source={attendance_note}")
        flash("Fiche collaborateur enregistrée avec succès.", "success")
        return redirect(url_for("people_admin"))
    year = request.args.get("year", local_today().year, type=int)
    people = db().execute("""SELECT u.*,m.full_name manager_name,b.annual_opening,b.annual_entitlement,b.annual_used,
        b.sick_opening,b.sick_entitlement,b.sick_used FROM users u LEFT JOIN users m ON m.id=u.manager_id
        LEFT JOIN balances b ON b.user_id=u.id AND b.year=?
        WHERE u.active=TRUE ORDER BY u.full_name""", (year,)).fetchall()
    edit_id = request.args.get("edit", type=int)
    edit_person = next((p for p in people if p["id"] == edit_id), None)
    if edit_person:
        edit_person = dict(edit_person)
        if edit_person.get('postal_address') and not any(edit_person.get(field) for field in ('address_line1','address_line2','postal_code','city','country')):
            edit_person['address_line1'] = edit_person['postal_address']
            edit_person['address_legacy'] = True
        if not edit_person['first_name'] and not edit_person['last_name']:
            parts = edit_person['full_name'].strip().split(maxsplit=1)
            if len(parts) == 2:
                edit_person.update(first_name=parts[0], last_name=parts[1], name_inferred=True)
        # Number inputs require a decimal point, including with PostgreSQL Decimal values.
        edit_person['basic_salary'] = format(float(edit_person['basic_salary'] or 0), '.2f')
        if edit_person['annual_opening'] is None:
            edit_person.update(balance_for(edit_id, year))
    people = [dict(p, **{key: value for key, value in balance_for(p['id'], year).items()
                        if key in {'annual_opening','annual_entitlement','annual_used',
                                   'sick_opening','sick_entitlement','sick_used'}}) for p in people]
    managers = db().execute("SELECT id,full_name FROM users WHERE active=TRUE AND role IN ('manager','hr','admin') ORDER BY full_name").fetchall()

    wra_status_map = {}
    for p in people:
        start_d = None
        if p["start_date"]:
            try:
                start_d = parse_service_date(p["start_date"])
            except ValueError:
                pass
        _, _, msg = calculate_wra_entitlements(start_d, year, user_id=p["id"])
        wra_status_map[p["id"]] = msg

    attendance_review = None
    if edit_person and edit_person["start_date"]:
        attendance_review = db().execute("SELECT * FROM attendance_reviews WHERE user_id=? AND service_start=?",
            (edit_person["id"], edit_person["start_date"])).fetchone()
    functions = db().execute('SELECT * FROM job_functions WHERE enabled=1 ORDER BY name').fetchall()
    return render_template("people_admin.html", teams=[r['name'] for r in db().execute('SELECT name FROM company_teams WHERE enabled=1 ORDER BY name').fetchall()], functions=functions, attendance_review=attendance_review, people=people, managers=managers, year=year,
                           edit_person=edit_person, wra_status_map=wra_status_map)


@app.post('/admin/people/<int:user_id>/balances')
@reviewer_required
def save_person_balances(user_id):
    if current_user.role not in {'hr', 'admin'}:
        abort(403)
    db().lock_person(user_id)
    person = db().execute('SELECT * FROM users WHERE id=?', (user_id,)).fetchone()
    if not person:
        abort(404)
    try:
        year = int(request.form['year'])
        if not 1900 <= year <= 2099:
            raise ValueError()
        columns = ('annual_opening','annual_entitlement','annual_used','sick_opening','sick_entitlement','sick_used')
        values = [float(request.form[name]) for name in columns]
        if any(not math.isfinite(value) or value < 0 for value in values):
            raise ValueError()
    except (KeyError, ValueError):
        flash('Renseignez l’année et les six montants avec des nombres positifs ou nuls.', 'danger')
        return redirect(url_for('people_admin', edit=user_id, section='balances', year=local_today().year))
    status = request.form.get('attendance_status', 'unknown')
    note = request.form.get('attendance_note', '').strip()
    if status not in {'unknown', 'confirmed', 'not_met'}:
        abort(400)
    start = parse_service_date(person['start_date'])
    if status != 'unknown' and (not start or local_today() < add_months(start, 6) or not note):
        flash('Une correction d’assiduité nécessite six mois de service et un commentaire de justification.', 'danger')
        return redirect(url_for('people_admin', edit=user_id, section='balances', year=year))
    db().execute("""INSERT INTO balances(user_id,year,annual_opening,annual_entitlement,annual_used,sick_opening,sick_entitlement,sick_used)
        VALUES(?,?,?,?,?,?,?,?) ON CONFLICT(user_id,year) DO UPDATE SET annual_opening=excluded.annual_opening,
        annual_entitlement=excluded.annual_entitlement,annual_used=excluded.annual_used,
        sick_opening=excluded.sick_opening,sick_entitlement=excluded.sick_entitlement,sick_used=excluded.sick_used""", (user_id, year, *values))
    if start:
        db().execute("""INSERT INTO attendance_reviews(user_id,service_start,status,note,reviewed_by)
            VALUES(?,?,?,?,?) ON CONFLICT(user_id,service_start) DO UPDATE SET status=excluded.status,
            note=excluded.note,reviewed_by=excluded.reviewed_by,reviewed_at=CURRENT_TIMESTAMP""",
            (user_id, start.isoformat(), status, note, current_user.id))
    db().commit()
    audit('updated', 'balance', user_id, f'year={year}; values={values}; attendance={status}; note={note}')
    flash('Soldes enregistrés.', 'success')
    return redirect(url_for('people_admin', year=year))


@app.post("/admin/requests/<int:request_id>/decision")
@reviewer_required
def decide_request(request_id):
    row = db().execute("SELECT * FROM leave_requests WHERE id=?", (request_id,)).fetchone()
    if not row or not can_review(row["user_id"]):
        abort(404)
    db().lock_person(row['user_id'])
    row = db().execute('SELECT * FROM leave_requests WHERE id=?', (request_id,)).fetchone()
    decision = request.form.get("decision")
    if row["status"] != "pending" or decision not in {"approved", "rejected"}:
        abort(400)
    if decision == 'approved' and row['leave_type'] == 'annual':
        error = annual_request_error(row['user_id'], parse_service_date(row['start_date']),
                                     parse_service_date(row['end_date']), float(row['day_fraction']), row['id'])
        if error:
            flash(error, 'danger')
            return redirect(url_for('requests_admin'))
    if decision == 'approved' and row['leave_type'] == 'menstrual':
        error = menstrual_request_error(row['user_id'], parse_service_date(row['start_date']),
                                        parse_service_date(row['end_date']), float(row['day_fraction']), row['id'])
        if error:
            flash(error, 'danger')
            return redirect(url_for('requests_admin'))
    db().execute("UPDATE leave_requests SET status=?,decided_by=?,decision_note=? WHERE id=?",
                 (decision, current_user.id, request.form.get("note", "").strip(), request_id))
    if decision == "approved":
        if row["leave_type"] in {"sick", "unplanned"}:
            start_month = date.fromisoformat(row["start_date"]).replace(day=1)
            end_month = date.fromisoformat(row["end_date"]).replace(day=1)
            month = start_month
            while month <= end_month:
                db().execute("UPDATE payroll_adjustments SET presence_bonus=0 WHERE user_id=? AND month=?",
                             (row["user_id"], month.strftime("%Y-%m")))
                month = (month.replace(day=28) + timedelta(days=4)).replace(day=1)
        if row["leave_type"] in {"annual", "sick"}:
            start = date.fromisoformat(row["start_date"])
            end = date.fromisoformat(row["end_date"])
            col = "annual_used" if row["leave_type"] == "annual" else "sick_used"
            for year in range(start.year, end.year + 1):
                y_start, y_end = max(start, date(year, 1, 1)), min(end, date(year, 12, 31))
                amount = weekdays_count(y_start, y_end) * float(row["day_fraction"])
                opening = balance_for(row['user_id'], year)
                db().execute("""INSERT INTO balances(user_id,year,annual_opening,annual_entitlement,sick_opening,sick_entitlement)
                    VALUES(?,?,?,?,?,?) ON CONFLICT(user_id,year) DO NOTHING""",
                    (row['user_id'], year, opening['annual_opening'], opening['annual_entitlement'],
                     opening['sick_opening'], opening['sick_entitlement']))
                db().execute(f"UPDATE balances SET {col}={col}+? WHERE user_id=? AND year=?",
                             (amount, row["user_id"], year))
    db().commit()
    audit(decision, "leave_request", request_id)
    flash("Décision enregistrée.", "success")
    return redirect(url_for("requests_admin"))


@app.get("/admin/weekend-work")
@reviewer_required
def weekend_admin():
    status = request.args.get("status", "pending")
    params = [status]
    where = "w.status=?"
    if current_user.role == "manager":
        where += " AND u.manager_id=?"
        params.append(current_user.id)
    rows = db().execute(f"""SELECT w.*,u.full_name,u.team FROM weekend_work w JOIN users u ON u.id=w.user_id
        WHERE {where} ORDER BY w.work_date DESC""", params).fetchall()
    return render_template("weekend_admin.html", rows=rows, status=status)


@app.post("/admin/weekend-work/<int:work_id>/decision")
@reviewer_required
def decide_weekend(work_id):
    row = db().execute("SELECT * FROM weekend_work WHERE id=?", (work_id,)).fetchone()
    decision = request.form.get("decision")
    if not row or not can_review(row["user_id"]):
        abort(404)
    db().lock_person(row['user_id'])
    row = db().execute('SELECT * FROM weekend_work WHERE id=?', (work_id,)).fetchone()
    if row["status"] != "pending" or decision not in {"approved", "rejected"}:
        abort(400)
    db().execute("UPDATE weekend_work SET status=?,decided_by=? WHERE id=?",
                 (decision, current_user.id, work_id))
    db().commit()
    audit(decision, "weekend_work", work_id)
    flash("Validation du week-end enregistrée.", "success")
    return redirect(url_for("weekend_admin"))


@app.route("/weekend-work/new", methods=["GET", "POST"])
@login_required
def weekend_work_new():
    if request.method == "POST":
        try:
            day = parse_service_date(request.form.get("work_date", ""))
            if day is None:
                raise ValueError('Missing date')
            hours = float(request.form.get("intervention_hours", "0") or 0)
        except ValueError:
            flash("Date ou durée d’intervention invalide.", "danger")
            return render_template("weekend_form.html")
        kind = request.form.get("work_type")
        if kind not in {"saturday", "sunday", "on_call"} or day.weekday() not in {5, 6} or not math.isfinite(hours) or not 0 <= hours <= 24 or (kind == 'saturday' and day.weekday() != 5) or (kind == 'sunday' and day.weekday() != 6):
            flash("La saisie doit concerner un samedi ou un dimanche.", "danger")
            return render_template("weekend_form.html")
        db().lock_person(current_user.id)
        if db().execute("SELECT 1 FROM weekend_work WHERE user_id=? AND work_date=? AND status IN ('pending','approved')", (current_user.id, day.isoformat())).fetchone():
            flash('Une déclaration existe déjà pour cette journée.', 'danger')
            return render_template('weekend_form.html')
        cur = db().execute("INSERT INTO weekend_work(user_id,work_date,work_type,intervention_hours,notes) VALUES(?,?,?,?,?)",
                           (current_user.id, day.isoformat(), kind, hours, request.form.get("notes", "").strip()))
        db().commit()
        audit("created", "weekend_work", cur.lastrowid, kind)
        flash("Votre saisie a été envoyée pour validation.", "success")
        return redirect(url_for("dashboard"))
    return render_template("weekend_form.html")


@app.get("/admin/planning")
@reviewer_required
def planning():
    month = request.args.get("month", local_today().strftime("%Y-%m"))
    try:
        first = date.fromisoformat(month + "-01")
        next_month = (first.replace(day=28) + timedelta(days=4)).replace(day=1)
    except ValueError:
        month = local_today().strftime("%Y-%m")
        first = date.fromisoformat(month + "-01")
        next_month = (first.replace(day=28) + timedelta(days=4)).replace(day=1)

    year = first.year
    working_days = max(1, weekdays_count(first, next_month - timedelta(days=1), country="FR"))

    if current_user.role == "manager":
        where, params = "u.manager_id=?", (current_user.id,)
    else:
        where, params = "u.active=TRUE", ()
    people = db().execute(f"SELECT u.id,u.full_name,u.team,u.start_date FROM users u WHERE {where} ORDER BY u.full_name", params).fetchall()

    calendar = []
    tot_cp = 0.0
    tot_sick = 0.0
    tot_unplanned = 0.0
    tot_wfh = 0.0
    tot_sat = 0
    tot_sun = 0
    tot_oncall = 0
    tot_hours = 0.0

    for person in people:
        leaves = db().execute("""SELECT leave_type,start_date,end_date,day_fraction,status FROM leave_requests
            WHERE user_id=? AND start_date<? AND end_date>=? AND status IN ('pending','approved')""",
                              (person["id"], next_month.isoformat(), first.isoformat())).fetchall()
        weekends = db().execute("""SELECT work_date,work_type,intervention_hours,status FROM weekend_work
            WHERE user_id=? AND work_date>=? AND work_date<?""",
                              (person["id"], first.isoformat(), next_month.isoformat())).fetchall()

        # Monthly counts
        cp = sum(float(l["day_fraction"] or 1) for l in leaves if l["leave_type"] == "annual" and l["status"] == "approved")
        sick = sum(float(l["day_fraction"] or 1) for l in leaves if l["leave_type"] == "sick" and l["status"] == "approved")
        unplanned = sum(float(l["day_fraction"] or 1) for l in leaves if l["leave_type"] == "unplanned" and l["status"] == "approved")
        wfh = sum(float(l["day_fraction"] or 1) for l in leaves if l["leave_type"] == "wfh" and l["status"] == "approved")

        sat = sum(1 for w in weekends if w["work_type"] == "saturday" and w["status"] == "approved")
        sun = sum(1 for w in weekends if w["work_type"] == "sunday" and w["status"] == "approved")
        oncall = sum(1 for w in weekends if w["work_type"] == "on_call" and w["status"] == "approved")
        hours = sum(float(w["intervention_hours"] or 0) for w in weekends if w["status"] == "approved")

        tot_cp += cp
        tot_sick += sick
        tot_unplanned += unplanned
        tot_wfh += wfh
        tot_sat += sat
        tot_sun += sun
        tot_oncall += oncall
        tot_hours += hours

        # Balances for current year
        bal = balance_for(person["id"], year)
        cp_rem = (bal["annual_opening"] or 0) + (bal["annual_entitlement"] or 0) - (bal["annual_used"] or 0)
        sick_rem = (bal["sick_opening"] or 0) + (bal["sick_entitlement"] or 0) - (bal["sick_used"] or 0)

        # Absenteeism rate for person in this month
        abs_rate = round(((sick + unplanned) / working_days) * 100, 1)

        calendar.append({
            "person": person,
            "leaves": leaves,
            "weekends": weekends,
            "cp": cp,
            "sick": sick,
            "unplanned": unplanned,
            "wfh": wfh,
            "saturdays": sat,
            "sundays": sun,
            "on_call": oncall,
            "hours": hours,
            "abs_rate": abs_rate,
            "cp_rem": cp_rem,
            "sick_rem": sick_rem,
        })

    summary = {
        "count": len(people),
        "working_days": working_days,
        "tot_cp": tot_cp,
        "tot_sick": tot_sick,
        "tot_unplanned": tot_unplanned,
        "tot_wfh": tot_wfh,
        "tot_sat": tot_sat,
        "tot_sun": tot_sun,
        "tot_oncall": tot_oncall,
        "tot_hours": tot_hours,
        "global_abs_rate": round(((tot_sick + tot_unplanned) / (working_days * max(len(people), 1))) * 100, 1)
    }

    available_months = [add_months(local_today().replace(day=1), -offset).strftime("%Y-%m") for offset in range(12)]
    if month not in available_months:
        available_months.insert(0, month)

    return render_template("planning.html", month=month, calendar=calendar, summary=summary,
                           available_months=available_months)


@app.get("/admin/stats")
@reviewer_required
def stats():
    year = request.args.get("year", local_today().year, type=int)
    if current_user.role == "manager":
        params = (current_user.id,)
        w = "AND u.manager_id=?"
    else:
        params, w = (), ""
    if not 1900 <= year <= 2099:
        abort(400)
    first, following = date(year, 1, 1), date(year+1, 1, 1)
    totals = dict(absences=0.0, annual=0.0, sick=0.0, wfh=0.0)
    leaves = db().execute(f"""SELECT r.* FROM leave_requests r JOIN users u ON u.id=r.user_id
        WHERE r.status='approved' AND r.start_date<? AND r.end_date>=? {w}""",
        (following.isoformat(),first.isoformat(),*params)).fetchall()
    for item in leaves:
        days = weekdays_count(max(first,parse_service_date(item['start_date'])),
                              min(following-timedelta(days=1),parse_service_date(item['end_date']))) * float(item['day_fraction'])
        kind = item['leave_type']
        if kind in totals:
            totals[kind] += days
        if kind in {'sick','unplanned'}:
            totals['absences'] += days
    work = dict(n=0, saturdays=0, sundays=0, on_call=0, hours=0.0)
    weekends = db().execute(f"""SELECT w.* FROM weekend_work w JOIN users u ON u.id=w.user_id
        WHERE w.work_date>=? AND w.work_date<? AND w.status='approved' {w}""",
        (first.isoformat(),following.isoformat(),*params)).fetchall()
    for item in weekends:
        work['n'] += 1
        work['saturdays' if parse_service_date(item['work_date']).weekday()==5 else 'sundays'] += 1
        work['on_call'] += int(item['work_type']=='on_call')
        work['hours'] += float(item['intervention_hours'])
    return render_template("stats.html", year=year, totals=totals, work=work)


@app.get("/admin/payroll")
@payroll_required
def payroll():
    month = request.args.get("month", local_today().strftime("%Y-%m"))
    try:
        first = date.fromisoformat(month + "-01")
        next_month = (first.replace(day=28) + timedelta(days=4)).replace(day=1)
    except ValueError:
        abort(400)

    people = db().execute("SELECT id, username, full_name, team, basic_salary FROM users WHERE active=TRUE ORDER BY full_name").fetchall()

    # Load salary records for the selected month
    sal_rows = db().execute("SELECT * FROM monthly_salaries WHERE month=?", (month,)).fetchall()
    sal_by_user = {s["user_id"]: s for s in sal_rows}

    # Available months in monthly_salaries
    available_months_raw = db().execute("SELECT DISTINCT month FROM monthly_salaries ORDER BY month DESC").fetchall()
    available_months = [r["month"] for r in available_months_raw] or ["2026-10", "2026-09", "2026-08"]
    if month not in available_months:
        available_months.insert(0, month)

    rows = []
    total_base = 0.0
    total_presence = 0.0
    total_overtime = 0.0

    for p in people:
        # Absences in month
        sick = db().execute("""SELECT COUNT(*) n FROM leave_requests WHERE user_id=? AND status='approved'
            AND leave_type='sick' AND start_date<? AND end_date>=?""",
                            (p["id"], next_month.isoformat(), first.isoformat())).fetchone()["n"]
        unplanned = db().execute("""SELECT COUNT(*) n FROM leave_requests WHERE user_id=? AND status='approved'
            AND leave_type='unplanned' AND start_date<? AND end_date>=?""",
                                 (p["id"], next_month.isoformat(), first.isoformat())).fetchone()["n"]

        # Weekend days & intervention hours in month
        wk_stats = db().execute("""SELECT
            SUM(CASE WHEN work_type='saturday' AND status='approved' THEN 1 ELSE 0 END) sat,
            SUM(CASE WHEN work_type='sunday' AND status='approved' THEN 1 ELSE 0 END) sun,
            SUM(CASE WHEN work_type='on_call' AND status='approved' THEN 1 ELSE 0 END) on_call,
            SUM(CASE WHEN status='approved' THEN intervention_hours ELSE 0 END) hours
            FROM weekend_work WHERE user_id=? AND work_date>=? AND work_date<?""",
            (p["id"], first.isoformat(), next_month.isoformat())).fetchone()

        saturdays = (wk_stats["sat"] or 0) if wk_stats else 0
        sundays = (wk_stats["sun"] or 0) if wk_stats else 0
        on_call = (wk_stats["on_call"] or 0) if wk_stats else 0
        hours = float((wk_stats["hours"] or 0) if wk_stats else 0)
        weekend_total = saturdays + sundays + on_call

        # Dated company rates; no monetary default until configured.
        suggested_ot = 0.0
        missing_rates = False
        for work in db().execute("SELECT work_date,work_type,intervention_hours FROM weekend_work WHERE user_id=? AND status='approved' AND work_date>=? AND work_date<?", (p['id'],first.isoformat(),next_month.isoformat())).fetchall():
            rates = rate_for(db(),parse_service_date(work['work_date']))
            if not rates:
                missing_rates = True
                continue
            key = {'saturday':'saturday','sunday':'sunday','on_call':'standby'}[work['work_type']]
            suggested_ot += float(rates[key]) + float(work['intervention_hours'] or 0) * float(rates['intervention_hour'])
        suggested_ot = round(suggested_ot, 2)
        presence_rate = rate_for(db(),first)

        adjustment = db().execute("SELECT presence_bonus,overtime_adjustment,note FROM payroll_adjustments WHERE user_id=? AND month=?",
                                  (p["id"], month)).fetchone()
        sal_rec = sal_by_user.get(p["id"])

        basic_sal = float(sal_rec["basic_salary"]) if sal_rec else float(p["basic_salary"] or 0)

        # RÈGLE PRIMES DE PRÉSENCE (Demande Client : enlever les primes de présence à ceux qui ont eu des sicks) :
        is_eligible = (sick == 0 and unplanned == 0)
        if not is_eligible:
            presence_b = 0.0
        elif adjustment and adjustment["presence_bonus"] is not None:
            presence_b = float(adjustment["presence_bonus"])
        elif sal_rec and sal_rec["presence_bonus"] is not None:
            presence_b = float(sal_rec["presence_bonus"])
        else:
            presence_b = float(presence_rate['presence']) if presence_rate else 0.0

        # RÈGLE OVERTIME (Demande Client : mettre les primes d'overtime à ceux qui ont travaillé samedis et/ou dimanches) :
        if adjustment and adjustment["overtime_adjustment"] is not None:
            overtime_val = float(adjustment["overtime_adjustment"])
        elif sal_rec and sal_rec["overtime_adjustment"] is not None and float(sal_rec["overtime_adjustment"]) > 0:
            overtime_val = float(sal_rec["overtime_adjustment"])
        else:
            overtime_val = suggested_ot

        comm = float(sal_rec["commission"] or 0) if sal_rec else 0.0
        special = float(sal_rec["special_bonus"] or 0) if sal_rec else 0.0
        transport = float(sal_rec["transport"] or 0) if sal_rec else 0.0
        loan = float(sal_rec["loan_refund"] or 0) if sal_rec else 0.0
        med = float(sal_rec["medical_deduction"] or 0) if sal_rec else 0.0
        note = adjustment["note"] if adjustment else (sal_rec["notes"] if sal_rec else "")

        net_estimate = basic_sal + comm + presence_b + overtime_val + special + transport - loan - med

        total_base += basic_sal
        total_presence += presence_b
        total_overtime += overtime_val

        rows.append({
            "person": p,
            "sick": sick,
            "unplanned": unplanned,
            "saturdays": saturdays,
            "sundays": sundays,
            "on_call": on_call,
            "hours": hours,
            "weekend": weekend_total,
            "basic_salary": basic_sal,
            "commission": comm,
            "special_bonus": special,
            "transport": transport,
            "loan_refund": loan,
            "medical_deduction": med,
            "net_estimate": net_estimate,
            "presence_eligible": is_eligible,
            "presence_bonus": presence_b,
            "suggested_ot": suggested_ot,
            "rates_missing": missing_rates or not presence_rate,
            "overtime_adjustment": overtime_val,
            "note": note or "",
        })

    kpis = {
        "total_base": total_base,
        "total_presence": total_presence,
        "total_overtime": total_overtime,
        "count": len(people)
    }

    return render_template("payroll.html", month=month, rows=rows, kpis=kpis,
                           available_months=available_months)


@app.post("/admin/payroll/<int:user_id>")
@payroll_required
def payroll_adjust(user_id):
    month = request.form.get("month", "")
    try:
        first = date.fromisoformat(month + "-01")
        next_month = (first.replace(day=28) + timedelta(days=4)).replace(day=1)
        presence_bonus = float(request.form.get("presence_bonus", "0") or 0)
        overtime = float(request.form.get("overtime_adjustment", "0") or 0)
        if not all(math.isfinite(v) and abs(v) <= 10000000 for v in (presence_bonus, overtime)):
            raise ValueError('Invalid amount')
        presence_bonus = max(0, presence_bonus)
    except (ValueError, TypeError):
        abort(400)
    if not db().execute("SELECT 1 FROM users WHERE id=? AND active=TRUE", (user_id,)).fetchone():
        abort(404)
    has_weekend = db().execute("""SELECT 1 FROM weekend_work WHERE user_id=? AND status='approved'
        AND work_date>=? AND work_date<? LIMIT 1""", (user_id, first.isoformat(), next_month.isoformat())).fetchone()
    if not has_weekend and overtime < 0:
        overtime = 0
    disqualifying_absence = db().execute("""SELECT 1 FROM leave_requests WHERE user_id=? AND status='approved'
        AND leave_type IN ('sick','unplanned') AND start_date<? AND end_date>=? LIMIT 1""",
                                        (user_id, next_month.isoformat(), first.isoformat())).fetchone()
    if disqualifying_absence:
        presence_bonus = 0
    db().execute("""INSERT INTO payroll_adjustments(month,user_id,presence_bonus,overtime_adjustment,note,updated_by)
        VALUES(?,?,?,?,?,?) ON CONFLICT(month,user_id) DO UPDATE SET presence_bonus=excluded.presence_bonus,
        overtime_adjustment=excluded.overtime_adjustment,note=excluded.note,updated_by=excluded.updated_by,
        created_at=CURRENT_TIMESTAMP""",
                 (month, user_id, presence_bonus, overtime, request.form.get("note", "").strip(), current_user.id))
    db().commit()
    audit("updated", "payroll_adjustment", user_id, month)
    flash("Ajustement de paie enregistré.", "success")
    return redirect(url_for("payroll", month=month))


@app.get("/espace/paie")
@login_required
def employee_payroll():
    """Employee self-service payroll & compensation overview."""
    history = db().execute("""
        SELECT * FROM monthly_salaries WHERE user_id=? ORDER BY month DESC
    """, (current_user.id,)).fetchall()
    adjustments = db().execute("""
        SELECT * FROM payroll_adjustments WHERE user_id=?
    """, (current_user.id,)).fetchall()
    adj_map = {a["month"]: a for a in adjustments}

    records = []
    for h in history:
        m = h["month"]
        adj = adj_map.get(m)
        pres = float(adj["presence_bonus"]) if adj and adj["presence_bonus"] is not None else float(h["presence_bonus"] or 0)
        ot = float(adj["overtime_adjustment"]) if adj and adj["overtime_adjustment"] is not None else float(h["overtime_adjustment"] or 0)
        base = float(h["basic_salary"] or 0)
        comm = float(h["commission"] or 0)
        spec = float(h["special_bonus"] or 0)
        trans = float(h["transport"] or 0)
        loan = float(h["loan_refund"] or 0)
        med = float(h["medical_deduction"] or 0)
        net_estimate = base + comm + pres + ot + spec + trans - loan - med
        records.append({
            "month": m,
            "basic_salary": base,
            "commission": comm,
            "presence_bonus": pres,
            "overtime": ot,
            "special_bonus": spec,
            "transport": trans,
            "loan_refund": loan,
            "medical_deduction": med,
            "net_estimate": net_estimate,
            "notes": adj["note"] if adj and adj["note"] else (h["notes"] or ""),
        })

    return render_template("employee_payroll.html", records=records)


@app.errorhandler(403)
def forbidden(_error):
    return render_template("error.html", code=403, message="Vous n’avez pas accès à cette page."), 403


def constraint_conflict(_error):
    db().rollback()
    return render_template('error.html', code=409, message='Cette modification entre en conflit avec des données existantes. Vérifiez les identifiants et rechargez la fiche.'), 409


app.register_error_handler(sqlite3.IntegrityError, constraint_conflict)
try:
    from psycopg2 import IntegrityError as PostgreSQLIntegrityError
    app.register_error_handler(PostgreSQLIntegrityError, constraint_conflict)
except ImportError:
    pass


@app.route('/account/password', methods=['GET','POST'])
@login_required
def change_password():
    if request.method == 'POST':
        person = db().execute('SELECT * FROM users WHERE id=?', (current_user.id,)).fetchone()
        password = request.form.get('password','')
        if not check_password_hash(person['password_hash'], request.form.get('current_password','')):
            flash('Le mot de passe actuel est incorrect.', 'danger')
        elif len(password) < 12 or password != request.form.get('confirmation') or check_password_hash(person['password_hash'], password):
            flash('Choisissez un nouveau mot de passe différent, de 12 caractères minimum, et confirmez-le.', 'danger')
        else:
            db().execute('UPDATE users SET password_hash=?,must_change_password=0,auth_version=auth_version+1 WHERE id=?',
                         (generate_password_hash(password), current_user.id))
            db().commit()
            session['auth_version'] = person['auth_version'] + 1
            session['csrf_token'] = secrets.token_urlsafe(32)
            audit('password_changed','access',current_user.id)
            flash('Mot de passe mis à jour.', 'success')
            return redirect(url_for('dashboard'))
    return render_template('change_password.html')


@app.after_request
def protect_private_pages(response):
    response.headers['X-Content-Type-Options'] = 'nosniff'
    response.headers['X-Frame-Options'] = 'DENY'
    response.headers['Referrer-Policy'] = 'same-origin'
    nonce = getattr(g, 'script_nonce', '')
    response.headers['Content-Security-Policy'] = (
        f"default-src 'self'; script-src 'self' 'nonce-{nonce}'; "
        "style-src 'self' 'unsafe-inline' https://fonts.googleapis.com; "
        "font-src 'self' https://fonts.gstatic.com; img-src 'self' data:; "
        "connect-src 'self'; worker-src 'self'; manifest-src 'self'; "
        "frame-ancestors 'none'; object-src 'none'; base-uri 'self'; form-action 'self'")
    if os.getenv('RH_ENV') == 'production':
        response.headers['Strict-Transport-Security'] = 'max-age=31536000'
    if request.endpoint != 'static':
        response.headers['Cache-Control'] = 'no-store'
        response.headers['Pragma'] = 'no-cache'
    return response


@app.route('/admin/access', methods=['GET','POST'])
@login_required
def access_admin():
    if current_user.role != 'admin':
        abort(403)
    if request.method == 'POST':
        user_id = request.form.get('user_id', type=int)
        person = db().execute('SELECT * FROM users WHERE id=?', (user_id,)).fetchone()
        if not person:
            abort(404)
        if user_id == int(current_user.id):
            flash('Pour préserver votre accès administrateur, faites modifier vos droits par un autre administrateur. Votre mot de passe se change depuis Mon mot de passe.', 'warning')
            return redirect(url_for('access_admin'))
        username = request.form.get('username','').strip()
        role = request.form.get('role')
        enabled = int(request.form.get('account_enabled') == 'on')
        if not username or len(username) > 64 or any(c.isspace() for c in username) or role not in ROLES:
            flash('Choisissez un identifiant sans espace (64 caractères maximum) et un rôle valide.', 'danger')
            return redirect(url_for('access_admin',edit=user_id))
        if db().execute('SELECT id FROM users WHERE LOWER(username)=LOWER(?) AND id<>?', (username,user_id)).fetchone():
            flash('Cet identifiant est déjà utilisé.', 'danger')
            return redirect(url_for('access_admin',edit=user_id))
        if enabled and not person['active']:
            flash('Le collaborateur doit être actif dans les effectifs pour activer son compte.', 'danger')
            return redirect(url_for('access_admin',edit=user_id))
        reset = request.form.get('reset_password') == 'on'
        if enabled and not person['account_enabled'] and not reset:
            flash('Initialisez un mot de passe temporaire lors de l’activation du compte.', 'danger')
            return redirect(url_for('access_admin',edit=user_id))
        allowed = set(request.form.getlist('modules'))
        if allowed - set(MODULES):
            abort(400)
        db().execute('UPDATE users SET username=?,role=?,account_enabled=?,auth_version=auth_version+1 WHERE id=?',
                     (username,role,enabled,user_id))
        db().execute('DELETE FROM module_restrictions WHERE user_id=?', (user_id,))
        for module in MODULES:
            if module not in allowed:
                db().execute('INSERT INTO module_restrictions(user_id,module) VALUES(?,?)', (user_id,module))
        if reset:
            temporary = secrets.token_urlsafe(15)
            db().execute('UPDATE users SET password_hash=?,must_change_password=1 WHERE id=?',
                         (generate_password_hash(temporary),user_id))
        db().commit()
        audit('updated','access',user_id, f'role={role}; enabled={enabled}; modules={sorted(allowed)}; reset={reset}')
        flash('Accès enregistrés. Les sessions précédentes de ce compte sont invalidées.', 'success')
        # Render directly: the temporary secret is never stored in the session, URL or audit log.
        if reset:
            return render_template('access_secret.html',person=person,username=username,temporary=temporary)
        return redirect(url_for('access_admin'))
    people = db().execute('SELECT id,full_name,username,role,account_enabled,must_change_password FROM users ORDER BY full_name').fetchall()
    edit_id = request.args.get('edit',type=int)
    person = db().execute('SELECT * FROM users WHERE id=?', (edit_id,)).fetchone() if edit_id else None
    denied = {r['module'] for r in db().execute('SELECT module FROM module_restrictions WHERE user_id=?', (edit_id,)).fetchall()} if person else set()
    return render_template('access_admin.html', people=people, person=person, denied=denied, modules=MODULES)


@app.route('/admin/settings/functions', methods=['GET','POST'])
@login_required
def functions_admin():
    if current_user.role != 'admin':
        abort(403)
    if request.method == 'POST':
        name = request.form.get('name','').strip()
        if not name or len(name)>128:
            flash('Indiquez une fonction de 128 caractères maximum.', 'danger')
        else:
            enabled = int(request.form.get('enabled') == 'on')
            table = 'company_teams' if request.form.get('category') == 'team' else 'job_functions'
            db().execute(f'INSERT INTO {table}(name,enabled) VALUES(?,?) ON CONFLICT(name) DO UPDATE SET enabled=excluded.enabled', (name,enabled))
            db().commit()
            audit('updated',table,details=f'{name}; enabled={enabled}')
            flash('Paramètre enregistré.', 'success')
        return redirect(url_for('functions_admin'))
    return render_template('functions_admin.html', functions=db().execute('SELECT * FROM job_functions ORDER BY name').fetchall(), teams=db().execute('SELECT * FROM company_teams ORDER BY name').fetchall())


@app.route('/admin/settings/calendar', methods=['GET','POST'])
@login_required
def calendar_settings():
    if current_user.role != 'admin':
        abort(403)
    year = request.values.get('year',local_today().year,type=int)
    if year is None or not 1900<=year<=2099:
        abort(400)
    if request.method == 'POST':
        try:
            day = parse_service_date(request.form.get('holiday_date',''))
            if not day or day.year != year:
                raise ValueError()
            name = request.form.get('name','').strip()
            if not name or len(name)>128:
                raise ValueError()
        except ValueError:
            flash('Indiquez une date dans l’année sélectionnée et un libellé valide.', 'danger')
            return redirect(url_for('calendar_settings',year=year))
        non_working = int(request.form.get('non_working') == 'on')
        db().execute('INSERT INTO calendar_overrides(holiday_date,name,non_working) VALUES(?,?,?) ON CONFLICT(holiday_date) DO UPDATE SET name=excluded.name,non_working=excluded.non_working', (day.isoformat(),name,non_working))
        db().commit()
        audit('updated','calendar',details=f'{day.isoformat()}; {name}; non_working={non_working}')
        flash('Calendrier enregistré. Les jours déjà déduits des soldes ne sont pas recalculés.', 'success')
        return redirect(url_for('calendar_settings',year=year))
    return render_template('calendar_settings.html',year=year,holidays=sorted(company_holidays(db(),year).items()),
        overrides=db().execute('SELECT * FROM calendar_overrides WHERE holiday_date>=? AND holiday_date<=? ORDER BY holiday_date', (f'{year}-01-01',f'{year}-12-31')).fetchall())


@app.route('/admin/settings/rates', methods=['GET','POST'])
@login_required
def rates_settings():
    if current_user.role != 'admin':
        abort(403)
    if request.method == 'POST':
        try:
            effective = parse_service_date(request.form.get('effective_date',''))
            # New versions apply only from a future month: past/current pay is preserved.
            if not effective or effective.day != 1 or effective <= local_today():
                raise ValueError()
            fields = ('presence','saturday','sunday','standby','intervention_hour')
            values = [float(request.form[field]) for field in fields]
            if any(not math.isfinite(value) or value<0 or value>10000000 for value in values):
                raise ValueError()
        except (ValueError,KeyError):
            flash('Choisissez le premier jour d’un mois futur et des montants positifs ou nuls.', 'danger')
            return redirect(url_for('rates_settings'))
        if db().execute('SELECT 1 FROM payroll_rates WHERE effective_date=?', (effective.isoformat(),)).fetchone():
            flash('Un barème existe déjà à cette date. Les versions enregistrées sont conservées.', 'danger')
            return redirect(url_for('rates_settings'))
        db().execute('INSERT INTO payroll_rates(effective_date,presence,saturday,sunday,standby,intervention_hour,created_by) VALUES(?,?,?,?,?,?,?)',
                     (effective.isoformat(),*values,current_user.id))
        db().commit()
        audit('created','payroll_rate',details=f'effective={effective.isoformat()}; values={values}')
        flash('Barème enregistré pour les périodes futures.', 'success')
        return redirect(url_for('rates_settings'))
    return render_template('rates_settings.html',rates=db().execute('SELECT * FROM payroll_rates ORDER BY effective_date DESC').fetchall())


@app.context_processor
def celebration_context():
    return {'celebrations': events_today(db(),local_today(),public=True)} if request.endpoint=='dashboard' and current_user.is_authenticated else {}


@app.route('/admin/settings/celebrations',methods=['GET','POST'])
@login_required
def celebration_settings():
    if current_user.role!='admin':
        abort(403)
    if request.method=='POST':
        recipient = request.form.get('recipient','').strip()
        send_time = request.form.get('send_time','')
        enabled = int(request.form.get('enabled')=='on')
        try:
            datetime.strptime(send_time,'%H:%M')
            if len(recipient)>254 or (recipient and ('@' not in recipient or any(c.isspace() for c in recipient))) or (enabled and not recipient):
                raise ValueError()
        except ValueError:
            flash('Indiquez une heure valide et une adresse destinataire pour activer les notifications.', 'danger')
            return redirect(url_for('celebration_settings'))
        db().execute('UPDATE celebration_settings SET recipient=?,send_time=?,enabled=? WHERE id=1',(recipient,send_time,enabled))
        db().commit()
        audit('updated','celebration_settings',details=f'enabled={enabled}; time={send_time}')
        flash('Paramètres de notification enregistrés.', 'success')
        return redirect(url_for('celebration_settings'))
    config = db().execute('SELECT * FROM celebration_settings WHERE id=1').fetchone()
    return render_template('celebration_settings.html',config=config,
        smtp_ready=bool(os.getenv('RH_SMTP_HOST') and os.getenv('RH_SMTP_FROM')),
        preview=digest(events_today(db(),local_today()),local_today()),
        deliveries=db().execute('SELECT * FROM celebration_deliveries ORDER BY send_date DESC LIMIT 10').fetchall())


def create_user(username, name, role, team="", password=None, payroll_admin=False, start_date=""):
    if role not in ROLES:
        raise ValueError("Rôle invalide")
    password = password or secrets.token_urlsafe(16)
    cur = db().execute("""INSERT INTO users(username,full_name,password_hash,role,team,payroll_admin,start_date)
        VALUES(?,?,?,?,?,?,?)""", (username, name, generate_password_hash(password), role, team,
                                      bool(payroll_admin), start_date or None))
    db().commit()
    return cur.lastrowid, password


def cli():
    parser = argparse.ArgumentParser()
    sub = parser.add_subparsers(dest="command")
    user_parser = sub.add_parser("create-user", help="Créer un compte utilisateur")
    user_parser.add_argument("--username", required=True)
    user_parser.add_argument("--name", required=True)
    user_parser.add_argument("--role", choices=sorted(ROLES), default="employee")
    user_parser.add_argument("--team", default="")
    user_parser.add_argument("--payroll-admin", action="store_true")

    sub.add_parser("import-salaries", help="Importer les salaires depuis Salary Planete.xlsx")
    sub.add_parser('init-db', help='Initialiser ou mettre à niveau le schéma avant démarrage')
    args = parser.parse_args()

    if args.command == 'init-db':
        init_db()
        print('Database schema initialized.')
    elif args.command == "create-user":
        import getpass
        password = getpass.getpass("Mot de passe initial (laisser vide pour en générer un) : ")
        with app.app_context():
            _user_id, generated = create_user(args.username, args.name, args.role, args.team,
                                               password or None, args.payroll_admin)
        if not password:
            print(f"Mot de passe temporaire : {generated}")
        print("Compte créé. Transmettez les identifiants au salarié par un canal sûr.")
    elif args.command == "import-salaries":
        import import_salary_planete
        import_salary_planete.main()
    else:
        import threading
        threading.Thread(target=run_worker,name='rh-anniversary-mail',daemon=True).start()
        app.run(host="127.0.0.1", port=int(os.environ.get("PORT", "5000")), debug=False)


if __name__ == "__main__":
    cli()