"""Module Formation > Dossiers de plongée (écoles de plongée / instructeurs)."""
import base64
import io
import json
import os
import re
import uuid
from datetime import datetime, date, timedelta

from flask import (Blueprint, render_template, redirect, url_for, request,
                   flash, abort, send_file, current_app)
from flask_login import login_required, current_user
from sqlalchemy import or_
from sqlalchemy.dialects.mysql import DATETIME, MEDIUMTEXT
from werkzeug.utils import secure_filename

from models import db
from pdfsign import can_stamp, stamp

training_bp = Blueprint('training', __name__, url_prefix='/dossiers')

# TEXT est limité à 64 Ko sous MySQL : trop court pour une signature PNG en base64 (~400 Ko max)
# ou pour un snapshot de transfert qui contient toutes les images.
LargeText = db.Text().with_variant(MEDIUMTEXT(), 'mysql')
# DATETIME est à la seconde sous MySQL : on garde les microsecondes, comme SQLite dans OnDive,
# pour que l'historique et les listes triées par date conservent leur ordre.
Timestamp = db.DateTime().with_variant(DATETIME(fsp=6), 'mysql')

STATUS_TRAINING = 'En formation'
STATUS_COMPLETED = 'Complété'
STATUS_CERTIFIED = 'Certifié'
STATUS_CANCELLED = 'Annulé'
STATUS_TRANSFER = 'Transfert'
STATUSES = [STATUS_TRAINING, STATUS_COMPLETED, STATUS_CERTIFIED, STATUS_CANCELLED]
MEDICAL_STATUSES = ['Non requis', 'À vérifier', 'En révision médicale', 'Validé par le médecin']
DOC_KINDS = ['Formulaire médical', 'Décharge / responsabilité', 'Pièce d\'identité',
             'Certification antérieure', 'Autre']
# Documents de l'élève, réutilisables d'un dossier à l'autre. Les deux premiers ont une validité.
KIND_MEDICAL = 'Certificat médical'
KIND_WAIVER = 'Décharge / responsabilité'
STUDENT_DOC_KINDS = [KIND_MEDICAL, KIND_WAIVER, "Pièce d'identité", 'Certification antérieure', 'Autre']
EXPIRY_WARNING_DAYS = 30
ALLOWED_EXT = {'pdf': b'%PDF', 'jpg': b'\xff\xd8\xff', 'jpeg': b'\xff\xd8\xff', 'png': b'\x89PNG'}
MAX_DOC_BYTES = 10 * 1024 * 1024


def _build_program():
    items = []
    for i in range(1, 6):
        items.append(('Théorie', f'THEORY-{i}', f'Section théorique {i}'))
    for i in range(1, 6):
        items.append(('Milieu protégé', f'CW-{i}', f'Milieu protégé {i}'))
    items += [
        ('Aptitudes aquatiques', 'SWIM', 'Test de nage (option au choix)'),
        ('Aptitudes aquatiques', 'FLOAT', 'Flottaison de survie — 10 minutes'),
        ('Aptitudes aquatiques', 'APNEA', "Compétences d'apnée"),
        ('Exercices protégés', 'POOL-GEAR', "Préparation et entretien de l'équipement"),
        ('Exercices protégés', 'POOL-HOSE', 'Déconnexion du tuyau moyenne pression'),
        ('Exercices protégés', 'POOL-STRAP', 'Desserrement de la sangle de bouteille'),
        ('Exercices protégés', 'POOL-WEIGHTS', 'Enlèvement et remise en place du lest en surface'),
        ('Exercices protégés', 'POOL-DITCH', "Largage d'urgence du lest"),
    ]
    for i in range(1, 5):
        items.append(('Milieu naturel', f'OW-{i}', f'Plongée en milieu naturel {i}'))
    items += [
        ('Exercices milieu naturel', 'OW-CRAMP', 'Soulagement de crampe'),
        ('Exercices milieu naturel', 'OW-SWITCH', 'Échange tuba / détendeur'),
        ('Exercices milieu naturel', 'OW-SMB', "Déploiement d'une bouée de surface"),
        ('Exercices milieu naturel', 'OW-WEIGHTS', "Largage d'urgence du lest"),
        ('Exercices milieu naturel', 'OW-COMPASS-SURFACE', 'Nage en surface avec boussole'),
        ('Exercices milieu naturel', 'OW-TOW', "Remorquage d'un plongeur fatigué"),
        ('Exercices milieu naturel', 'OW-GEAR', "Enlèvement et remise en place de l'équipement en surface"),
        ('Exercices milieu naturel', 'OW-WEIGHTS-SURFACE', 'Enlèvement et remise en place du lest en surface'),
        ('Exercices milieu naturel', 'OW-ASCENT', "Remontée d'urgence contrôlée"),
        ('Exercices milieu naturel', 'OW-COMPASS', "Navigation sous l'eau avec boussole"),
    ]
    return items


OPEN_WATER_PROGRAM = _build_program()
PROGRAM_NAME = 'Open Water Diver'

# Advanced Open Water : cinq plongées d'aventure dont la plongée profonde et la navigation (obligatoires).
AOW_PROGRAM = (
    [('Connaissances', f'AOW-K-{i}', f'Section de connaissances {i}') for i in range(1, 6)]
    + [('Plongées obligatoires', 'AOW-DEEP', "Plongée d'aventure : profondeur"),
       ('Plongées obligatoires', 'AOW-NAV', "Plongée d'aventure : navigation sous-marine")]
    + [('Plongées au choix', f'AOW-ELEC-{i}', f"Plongée d'aventure au choix {i}") for i in range(1, 4)]
)
AOW_PROGRAM_NAME = 'Advanced Open Water Diver'
CUSTOM_CATEGORY = 'Jalons'
# Programmes prédéfinis : clé du formulaire → (nom, jalons). « custom » : nom et jalons saisis à la main.
PROGRAMS = {'ow': (PROGRAM_NAME, OPEN_WATER_PROGRAM), 'aow': (AOW_PROGRAM_NAME, AOW_PROGRAM)}
MAX_CUSTOM_MILESTONES = 60

# Exigences PADI Scuba Diver : sections 1 à 3, milieu protégé 1 à 3, milieu naturel 1 et 2
# et exercices marqués d'un astérisque sur le formulaire.
SCUBA_DIVER_CODES = {
    'THEORY-1', 'THEORY-2', 'THEORY-3', 'CW-1', 'CW-2', 'CW-3', 'OW-1', 'OW-2',
    'FLOAT', 'POOL-GEAR', 'POOL-HOSE', 'POOL-WEIGHTS', 'POOL-DITCH',
    'OW-CRAMP', 'OW-SWITCH', 'OW-SMB', 'OW-WEIGHTS',
}
CONFINED_CATEGORIES = ('Milieu protégé', 'Aptitudes aquatiques', 'Exercices protégés')

# Lignes de signature du formulaire PADI, dans l'ordre d'affichage.
# covers : jalons que la signature atteste (tous doivent être validés pour signer).
SIGNATURE_LINES = {
    'identification': {
        'role': 'instructor', 'title': "Identification de l'instructeur",
        'statement': "Je certifie que l'élève a réalisé avec succès les performances requises pour chaque "
                     "manœuvre, section ou plongée que j'ai paraphée, telles qu'indiquées dans le PADI "
                     "Instructor Manual. Je suis PADI Instructor avec statut d'enseignant actif pour "
                     "l'année en cours.",
        'covers': lambda m: False},
    'knowledge': {
        'role': 'instructor', 'title': 'Développement des connaissances',
        'statement': 'Toutes les sessions de développement des connaissances ont été suivies, '
                     'les exercices de contrôle/examens réussis.',
        'covers': lambda m: m.category == 'Théorie'},
    'confined': {
        'role': 'instructor', 'title': "Milieu protégé et aptitude dans l'eau",
        'statement': "Toutes les plongées en milieu protégé et l'évaluation de l'aptitude dans l'eau "
                     "ont été effectuées.",
        'covers': lambda m: m.category in CONFINED_CATEGORIES},
    'ow_skills': {
        'role': 'instructor', 'title': 'Exercices de plongée flexibles',
        'statement': 'Tous les exercices de plongée flexibles ont été effectués.',
        'covers': lambda m: m.category == 'Exercices milieu naturel'},
    'student': {
        'role': 'student', 'title': "Déclaration de l'élève",
        'statement': "Je comprends les exigences d'entraînement de ce cours et j'ai satisfait à toutes les "
                     "conditions d'obtention de certification. Je suis suffisamment préparé(e) pour plonger "
                     "dans des conditions similaires à celles dans lesquelles j'ai été formé(e). Je reconnais "
                     "qu'une formation supplémentaire est recommandée avant de participer à des activités de "
                     "plongée spécialisée, avant de plonger dans d'autres secteurs ou après une période "
                     "d'inactivité de plus de six mois. J'accepte de suivre les Consignes de sécurité en "
                     "plongée de PADI.",
        'covers': lambda m: m.code in SCUBA_DIVER_CODES},
    'scuba_diver': {
        'role': 'instructor', 'title': 'Certification PADI Scuba Diver', 'optional': True,
        'requires_student': True,
        'statement': 'Toutes les exigences de certification PADI Scuba Diver ont été satisfaites (sessions de '
                     'développement des connaissances 1, 2 et 3, plongées en milieu protégé 1, 2 et 3, '
                     'plongées en milieu naturel 1, 2 et tous les exercices de plongée flexibles marqués '
                     'par un astérisque *).',
        'covers': lambda m: m.code in SCUBA_DIVER_CODES},
    'open_water': {
        'role': 'instructor', 'title': 'Certification PADI Open Water Diver', 'requires_student': True,
        'statement': 'Toutes les exigences de certification PADI Open Water Diver ont été satisfaites.',
        'covers': lambda m: True},
}
SIGNATURE_PREFIX = 'data:image/png;base64,'
MAX_SIGNATURE_BYTES = 300 * 1024
# Onglets de la fiche : « tab » est injecté dans une expression Alpine, seules ces valeurs passent.
RECORD_TABS = ('overview', 'instructors', 'milestones', 'signatures', 'forms', 'documents', 'transfer', 'history')


# Formulaires PADI du cours Open Water (PDF officiels dans Doc/OW). Le texte légal n'est pas recopié :
# l'application affiche le PDF d'origine et conserve la signature, la date et le signataire.
FORMS_SUBDIR = os.path.join('Doc', 'OW')
ROLE_LABELS = {'student': 'Élève', 'guardian': 'Parent ou tuteur', 'minor': 'Participant mineur'}
QUIZ_TOTALS = (21, 25)  # 21 questions avec ordinateur de plongée, 25 avec les tables
ADULT_AGE = 18
OW_FORMS = [
    {'code': '10060F', 'title': "Déclaration d'adhésion aux règles de plongée en toute sécurité",
     'kind': 'sign', 'roles': ('student',), 'required': True},
    {'code': '10072F', 'title': "Indépendance du prestataire, non-responsabilité et acceptation des risques",
     'kind': 'sign', 'roles': ('student',), 'required': True},
    {'code': '10346F', 'title': 'Examen médical du plongeur', 'kind': 'scan', 'roles': (), 'required': True,
     'hint': "Questionnaire rempli par l'élève ; l'évaluation du médecin n'est nécessaire que si une réponse est « oui »."},
    {'code': '10615F', 'title': 'Plongée enfant : responsabilités et acceptation des risques', 'kind': 'sign',
     'roles': ('guardian', 'minor'), 'required': True, 'minors_only': True},
    {'code': '381DT', 'title': 'Révision rapide Open Water Diver', 'kind': 'quiz', 'roles': ('student',),
     'required': True, 'prefix': '381DT'},
    {'code': '10062F', 'title': 'Déclaration du PADI Scuba Diver', 'kind': 'sign', 'roles': ('student',),
     'required': False, 'hint': 'Seulement si l\'élève vise la certification Scuba Diver (12 m maximum).'},
]
FORMS_BY_CODE = {f['code']: f for f in OW_FORMS}


# ── Models ────────────────────────────────────────────────────────────────────

class TrainingStudent(db.Model):
    __tablename__ = 'training_student'
    id = db.Column(db.Integer, primary_key=True)
    owner_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False, index=True)
    first_name = db.Column(db.String(80), nullable=False)
    last_name = db.Column(db.String(80), nullable=False)
    email = db.Column(db.String(120))
    phone = db.Column(db.String(40))
    birth_date = db.Column(db.Date)
    created_at = db.Column(Timestamp, default=datetime.utcnow)

    @property
    def full_name(self):
        return f'{self.first_name} {self.last_name}'.strip()

    def latest_document(self, kind):
        """Document le plus récent de ce type (la relation les trie du plus récent au plus ancien)."""
        return next((d for d in self.documents if d.kind == kind), None)

    @property
    def document_alerts(self):
        """Pièces à régler : certificat médical ou décharge absent, périmé ou bientôt périmé."""
        alerts = []
        for kind in (KIND_MEDICAL, KIND_WAIVER):
            doc = self.latest_document(kind)
            if not doc:
                alerts.append(f'{kind} manquant')
            elif doc.expired:
                alerts.append(f'{kind} expiré')
            elif doc.expires_soon:
                alerts.append(f'{kind} expire bientôt')
        return alerts


INSTRUCTOR_FIELDS = ('name', 'padi_number', 'center_number', 'phone', 'fax', 'email')


class TrainingInstructor(db.Model):
    """Répertoire des instructeurs du compte, réutilisables d'un dossier à l'autre."""
    __tablename__ = 'training_instructor'
    id = db.Column(db.Integer, primary_key=True)
    owner_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False, index=True)
    name = db.Column(db.String(120), nullable=False)
    padi_number = db.Column(db.String(40), nullable=False)
    center_number = db.Column(db.String(40))  # N° du centre de plongée / villégiature
    phone = db.Column(db.String(40))
    fax = db.Column(db.String(40))
    email = db.Column(db.String(120))
    created_at = db.Column(Timestamp, default=datetime.utcnow)


class TrainingRecordInstructor(db.Model):
    """Instructeur ayant paraphé le dossier (section d'identification du formulaire PADI)."""
    __tablename__ = 'training_record_instructor'
    __table_args__ = (db.UniqueConstraint('record_id', 'instructor_id', name='uq_training_record_instructor'),)
    id = db.Column(db.Integer, primary_key=True)
    record_id = db.Column(db.Integer, db.ForeignKey('training_record.id'), nullable=False, index=True)
    instructor_id = db.Column(db.Integer, db.ForeignKey('training_instructor.id'), nullable=False)
    signed_on = db.Column(db.Date, default=date.today)

    instructor = db.relationship('TrainingInstructor')


class TrainingRecord(db.Model):
    __tablename__ = 'training_record'
    id = db.Column(db.Integer, primary_key=True)
    owner_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False, index=True)
    student_id = db.Column(db.Integer, db.ForeignKey('training_student.id'), nullable=False)
    program_name = db.Column(db.String(80), default=PROGRAM_NAME)
    status = db.Column(db.String(30), default=STATUS_TRAINING, index=True)
    medical_status = db.Column(db.String(40), default='À vérifier')
    start_date = db.Column(db.Date, default=date.today)
    locked = db.Column(db.Boolean, default=False)
    created_at = db.Column(Timestamp, default=datetime.utcnow)
    updated_at = db.Column(Timestamp, default=datetime.utcnow, onupdate=datetime.utcnow)

    student = db.relationship('TrainingStudent', backref='records')
    milestones = db.relationship('TrainingMilestone', backref='record', cascade='all, delete-orphan',
                                 order_by='TrainingMilestone.sort_order')
    audit = db.relationship('TrainingAudit', backref='record', cascade='all, delete-orphan',
                            order_by='TrainingAudit.created_at.desc(), TrainingAudit.id.desc()')
    documents = db.relationship('TrainingDocument', backref='record', cascade='all, delete-orphan',
                                order_by='TrainingDocument.uploaded_at.desc(), TrainingDocument.id.desc()')
    transfer = db.relationship('TrainingTransfer', backref='record', uselist=False,
                               cascade='all, delete-orphan')
    instructor_links = db.relationship('TrainingRecordInstructor', backref='record',
                                       cascade='all, delete-orphan', order_by='TrainingRecordInstructor.id')
    signatures = db.relationship('TrainingSignature', backref='record', cascade='all, delete-orphan',
                                 order_by='TrainingSignature.signed_at')

    def signature_for(self, kind, instructor_id=None):
        """Signature d'une ligne du formulaire (par instructeur pour l'identification)."""
        return next((s for s in self.signatures if s.kind == kind
                     and (kind != 'identification' or s.instructor_id == instructor_id)), None)

    @property
    def is_open_water(self):
        """Seul l'Open Water a le formulaire PADI à signatures ; les autres programmes se valident par jalons."""
        return self.program_name == PROGRAM_NAME

    @property
    def unsigned_instructors(self):
        return [link.instructor for link in self.instructor_links
                if not self.signature_for('identification', link.instructor_id)]

    @property
    def done_count(self):
        return sum(1 for m in self.milestones if m.completed)

    @property
    def progress(self):
        total = len(self.milestones)
        return round(self.done_count * 100 / total) if total else 0


class TrainingMilestone(db.Model):
    __tablename__ = 'training_milestone'
    __table_args__ = (db.UniqueConstraint('record_id', 'code', name='uq_training_milestone'),)
    id = db.Column(db.Integer, primary_key=True)
    record_id = db.Column(db.Integer, db.ForeignKey('training_record.id'), nullable=False, index=True)
    category = db.Column(db.String(60), nullable=False)
    code = db.Column(db.String(40), nullable=False)
    name = db.Column(db.String(200), nullable=False)
    sort_order = db.Column(db.Integer, default=0)
    completed = db.Column(db.Boolean, default=False)
    completed_on = db.Column(db.Date)
    instructor_id = db.Column(db.Integer, db.ForeignKey('user.id'))  # compte ayant saisi la validation
    instructor_name = db.Column(db.String(120))
    training_instructor_id = db.Column(db.Integer, db.ForeignKey('training_instructor.id'))
    notes = db.Column(db.Text)

    training_instructor = db.relationship('TrainingInstructor')

    @property
    def signed_by(self):
        i = self.training_instructor
        return f'{i.name} · PADI #{i.padi_number}' if i else self.instructor_name


class TrainingAudit(db.Model):
    __tablename__ = 'training_audit'
    id = db.Column(db.Integer, primary_key=True)
    record_id = db.Column(db.Integer, db.ForeignKey('training_record.id'), nullable=False, index=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user_name = db.Column(db.String(120))
    action = db.Column(db.String(60), nullable=False)
    details = db.Column(db.Text)
    created_at = db.Column(Timestamp, default=datetime.utcnow)


class TrainingDocument(db.Model):
    __tablename__ = 'training_document'
    id = db.Column(db.Integer, primary_key=True)
    record_id = db.Column(db.Integer, db.ForeignKey('training_record.id'), nullable=False, index=True)
    kind = db.Column(db.String(60))
    original_name = db.Column(db.String(255))
    stored_name = db.Column(db.String(80), nullable=False)
    size = db.Column(db.Integer)
    uploaded_by = db.Column(db.String(120))
    uploaded_at = db.Column(Timestamp, default=datetime.utcnow)


class TrainingStudentDocument(db.Model):
    """Pièce de l'élève (certificat médical, décharge…), partagée par tous ses dossiers."""
    __tablename__ = 'training_student_document'
    id = db.Column(db.Integer, primary_key=True)
    student_id = db.Column(db.Integer, db.ForeignKey('training_student.id'), nullable=False, index=True)
    kind = db.Column(db.String(60))
    valid_until = db.Column(db.Date)
    original_name = db.Column(db.String(255))
    stored_name = db.Column(db.String(80), nullable=False)
    size = db.Column(db.Integer)
    uploaded_by = db.Column(db.String(120))
    uploaded_at = db.Column(Timestamp, default=datetime.utcnow)

    student = db.relationship('TrainingStudent', backref=db.backref(
        'documents', cascade='all, delete-orphan', order_by='TrainingStudentDocument.uploaded_at.desc(), TrainingStudentDocument.id.desc()'))

    @property
    def expired(self):
        return bool(self.valid_until and self.valid_until < date.today())

    @property
    def expires_soon(self):
        return bool(self.valid_until and not self.expired
                    and self.valid_until <= date.today() + timedelta(days=EXPIRY_WARNING_DAYS))


class TrainingFollowup(db.Model):
    """Document de suivi : instantané imprimable d'un dossier, sans verrouillage, refaisable à volonté."""
    __tablename__ = 'training_followup'
    id = db.Column(db.Integer, primary_key=True)
    record_id = db.Column(db.Integer, db.ForeignKey('training_record.id'), nullable=False, index=True)
    number = db.Column(db.String(40), unique=True, nullable=False)
    issued_on = db.Column(db.Date, default=date.today)
    note = db.Column(db.String(200))
    snapshot = db.Column(LargeText)
    created_at = db.Column(Timestamp, default=datetime.utcnow)

    record = db.relationship('TrainingRecord', backref=db.backref(
        'followups', cascade='all, delete-orphan', order_by='TrainingFollowup.id.desc()'))


class TrainingFormEntry(db.Model):
    """Suivi d'un formulaire PADI pour un dossier : scan papier et, pour la révision rapide, le score."""
    __tablename__ = 'training_form_entry'
    __table_args__ = (db.UniqueConstraint('record_id', 'form_code', name='uq_training_form_entry'),)
    id = db.Column(db.Integer, primary_key=True)
    record_id = db.Column(db.Integer, db.ForeignKey('training_record.id'), nullable=False, index=True)
    form_code = db.Column(db.String(20), nullable=False)
    scan_name = db.Column(db.String(80))
    scan_original = db.Column(db.String(255))
    scan_size = db.Column(db.Integer)
    score = db.Column(db.Integer)
    total = db.Column(db.Integer)
    done_on = db.Column(db.Date)

    record = db.relationship('TrainingRecord', backref=db.backref(
        'form_entries', cascade='all, delete-orphan'))


class TrainingFormSignature(db.Model):
    """Signature à l'écran d'un formulaire PADI, par rôle (élève, parent ou tuteur, participant mineur)."""
    __tablename__ = 'training_form_signature'
    __table_args__ = (db.UniqueConstraint('record_id', 'form_code', 'role', name='uq_training_form_signature'),)
    id = db.Column(db.Integer, primary_key=True)
    record_id = db.Column(db.Integer, db.ForeignKey('training_record.id'), nullable=False, index=True)
    form_code = db.Column(db.String(20), nullable=False)
    role = db.Column(db.String(20), nullable=False)
    signer_name = db.Column(db.String(120), nullable=False)
    image = db.Column(LargeText, nullable=False)  # PNG en base64
    signed_at = db.Column(Timestamp, default=datetime.utcnow)
    created_by = db.Column(db.Integer, db.ForeignKey('user.id'))

    record = db.relationship('TrainingRecord', backref=db.backref(
        'form_signatures', cascade='all, delete-orphan', order_by='TrainingFormSignature.signed_at'))

    @property
    def data_url(self):
        return SIGNATURE_PREFIX + self.image


class TrainingTransfer(db.Model):
    __tablename__ = 'training_transfer'
    id = db.Column(db.Integer, primary_key=True)
    record_id = db.Column(db.Integer, db.ForeignKey('training_record.id'), nullable=False, unique=True)
    number = db.Column(db.String(40), unique=True, nullable=False)
    issued_on = db.Column(db.Date, default=date.today)
    valid_until = db.Column(db.Date)
    destination = db.Column(db.String(200))
    snapshot = db.Column(LargeText)  # JSON figé au moment du transfert


class TrainingSignature(db.Model):
    """Signature manuscrite d'une ligne du formulaire (instructeur ou élève)."""
    __tablename__ = 'training_signature'
    id = db.Column(db.Integer, primary_key=True)
    record_id = db.Column(db.Integer, db.ForeignKey('training_record.id'), nullable=False, index=True)
    kind = db.Column(db.String(30), nullable=False)  # clé de SIGNATURE_LINES
    instructor_id = db.Column(db.Integer, db.ForeignKey('training_instructor.id'))  # None = élève
    signer_name = db.Column(db.String(120), nullable=False)
    padi_number = db.Column(db.String(40))
    statement = db.Column(db.Text)  # déclaration signée, figée
    image = db.Column(LargeText, nullable=False)  # PNG en base64
    signed_at = db.Column(Timestamp, default=datetime.utcnow)
    created_by = db.Column(db.Integer, db.ForeignKey('user.id'))
    signer_user_id = db.Column(db.Integer, db.ForeignKey('user.id'))  # compte de l'instructeur s'il était connecté

    @property
    def title(self):
        return SIGNATURE_LINES[self.kind]['title']

    @property
    def data_url(self):
        return SIGNATURE_PREFIX + self.image


# ── Helpers ───────────────────────────────────────────────────────────────────

def _display_name():
    return current_user.display_name


def _log(record, action, details=None):
    db.session.add(TrainingAudit(record_id=record.id, user_id=current_user.id,
                                 user_name=_display_name(), action=action, details=details))


def _parse_date(value):
    try:
        return datetime.strptime((value or '').strip(), '%Y-%m-%d').date()
    except ValueError:
        return None


def _own_record(record_id):
    record = TrainingRecord.query.get_or_404(record_id)
    if record.owner_id != current_user.id:
        abort(404)
    return record


def _editable_record(record_id):
    record = _own_record(record_id)
    if record.locked:
        flash('Ce dossier est verrouillé (transfert émis) : modification impossible.', 'error')
        return record, False
    return record, True


def _upload_dir():
    path = os.path.join(current_app.config['DATA_DIR'], 'training_docs')
    os.makedirs(path, exist_ok=True)
    return path


def _back(record_id, tab=None):
    return redirect(url_for('training.record', record_id=record_id, tab=tab))


def _instructor_form():
    """Champs instructeur du formulaire, ou None (avec message) si invalides."""
    fields = {k: (request.form.get(k) or '').strip() or None for k in INSTRUCTOR_FIELDS}
    if not fields['name'] or not fields['padi_number']:
        flash("Nom et n° PADI de l'instructeur requis.", 'error')
        return None
    return fields


def _signed_on_form(default):
    signed_on = _parse_date(request.form.get('signed_on')) or default
    if signed_on > date.today():
        flash('La date ne peut pas être dans le futur.', 'error')
        return None
    return signed_on


def _missing(rec, kind):
    """Prérequis non remplis pour signer cette ligne du formulaire."""
    line = SIGNATURE_LINES[kind]
    missing = [m.name for m in rec.milestones if line['covers'](m) and not m.completed]
    if line.get('requires_student') and not rec.signature_for('student'):
        missing.insert(0, "Déclaration de l'élève")
    return missing


def _missing_text(missing):
    text = ', '.join(missing[:3])
    return text + (f' et {len(missing) - 3} autre(s)' if len(missing) > 3 else '')


def _signature_png(data_url):
    """Signature dessinée (data URL PNG) ré-encodée en base64, ou None si invalide."""
    if not (data_url or '').startswith(SIGNATURE_PREFIX):
        return None
    try:
        raw = base64.b64decode(data_url[len(SIGNATURE_PREFIX):], validate=True)
    except ValueError:
        return None
    if len(raw) > MAX_SIGNATURE_BYTES or not raw.startswith(ALLOWED_EXT['png']):
        return None
    return base64.b64encode(raw).decode('ascii')


def _norm_padi(number):
    return re.sub(r'[\s#]', '', number or '').upper()


def _is_account_of(user, instructor):
    """Le compte porte-t-il le n° PADI de cet instructeur (dans ses paramètres) ?"""
    padi = _norm_padi(instructor.padi_number)
    return bool(padi) and user.is_active and user.certification_agency in (None, 'PADI') \
        and _norm_padi(user.certification_number) == padi


def _signature_sections(rec):
    sections = []
    for kind, line in SIGNATURE_LINES.items():
        if kind == 'identification':
            rows = [(link.instructor, rec.signature_for(kind, link.instructor_id))
                    for link in rec.instructor_links]
        else:
            rows = [(None, rec.signature_for(kind))]
        missing = _missing(rec, kind)
        sections.append({'kind': kind, 'line': line, 'rows': rows, 'missing': missing,
                         'missing_text': _missing_text(missing)})
    return sections


# ── Routes ────────────────────────────────────────────────────────────────────

@training_bp.route('/')
@login_required
def dashboard():
    q = (request.args.get('q') or '').strip()
    status = request.args.get('status') or ''
    base = TrainingRecord.query.filter_by(owner_id=current_user.id)
    counts = {s: base.filter(TrainingRecord.status == s).count()
              for s in (STATUS_TRAINING, STATUS_COMPLETED, STATUS_CERTIFIED, STATUS_TRANSFER)}
    total = base.count()
    query = base.join(TrainingStudent)
    if status:
        query = query.filter(TrainingRecord.status == status)
    if q:
        like = f'%{q}%'
        query = query.filter(or_(TrainingStudent.first_name.ilike(like),
                                 TrainingStudent.last_name.ilike(like),
                                 TrainingStudent.email.ilike(like)))
    records = query.order_by(TrainingRecord.updated_at.desc()).all()
    students = TrainingStudent.query.filter_by(owner_id=current_user.id) \
        .order_by(TrainingStudent.last_name).all()
    return render_template('training/dashboard.html', records=records, students=students,
                           counts=counts, total=total, q=q, status=status,
                           statuses=STATUSES + [STATUS_TRANSFER], today=date.today())


def _program_form():
    """(nom du programme, jalons) du formulaire, ou None (avec message) si invalide."""
    key = request.form.get('program') or 'ow'
    if key in PROGRAMS:
        return PROGRAMS[key]
    if key != 'custom':
        flash('Programme inconnu.', 'error')
        return None
    name = (request.form.get('program_name') or '').strip()[:80]
    lines = [l.strip()[:200] for l in (request.form.get('milestones') or '').splitlines() if l.strip()]
    if not name or any(name.lower() == known.lower() for known, _ in PROGRAMS.values()):
        flash('Donnez un nom au programme libre, différent des programmes Open Water et AOW.', 'error')
        return None
    if not lines or len(lines) > MAX_CUSTOM_MILESTONES:
        flash(f'Indiquez de 1 à {MAX_CUSTOM_MILESTONES} jalons, un par ligne.', 'error')
        return None
    return name, [(CUSTOM_CATEGORY, f'CUSTOM-{i}', line) for i, line in enumerate(lines, 1)]


def _new_record(student, program):
    name, items = program
    record = TrainingRecord(owner_id=current_user.id, student_id=student.id, program_name=name,
                            start_date=_parse_date(request.form.get('start_date')) or date.today())
    for order, (category, code, label) in enumerate(items):
        record.milestones.append(TrainingMilestone(category=category, code=code, name=label,
                                                   sort_order=order))
    db.session.add(record)
    db.session.flush()
    _log(record, 'Dossier créé', f'Élève : {student.full_name} — {name}')
    db.session.commit()
    flash('Dossier créé.', 'success')
    return record


def _own_student(student_id):
    student = TrainingStudent.query.get_or_404(student_id)
    if student.owner_id != current_user.id:
        abort(404)
    return student


@training_bp.route('/new', methods=['POST'])
@login_required
def create_record():
    program = _program_form()
    if not program:
        return redirect(url_for('training.dashboard'))
    student_id = request.form.get('student_id')
    if student_id:
        student = TrainingStudent.query.filter_by(id=student_id, owner_id=current_user.id).first()
        if not student:
            flash('Élève introuvable.', 'error')
            return redirect(url_for('training.dashboard'))
    else:
        first = (request.form.get('first_name') or '').strip()
        last = (request.form.get('last_name') or '').strip()
        if not first or not last:
            flash("Prénom et nom de l'élève requis.", 'error')
            return redirect(url_for('training.dashboard'))
        student = TrainingStudent(owner_id=current_user.id, first_name=first, last_name=last,
                                  email=(request.form.get('email') or '').strip() or None,
                                  phone=(request.form.get('phone') or '').strip() or None,
                                  birth_date=_parse_date(request.form.get('birth_date')))
        db.session.add(student)
        db.session.flush()
    return _back(_new_record(student, program).id)


# ── Élèves : une fiche par personne, avec ses dossiers et ses pièces ──────────

@training_bp.route('/eleves')
@login_required
def students():
    q = (request.args.get('q') or '').strip()
    query = TrainingStudent.query.filter_by(owner_id=current_user.id)
    if q:
        like = f'%{q}%'
        query = query.filter(or_(TrainingStudent.first_name.ilike(like), TrainingStudent.last_name.ilike(like),
                                 TrainingStudent.email.ilike(like)))
    rows = query.order_by(TrainingStudent.last_name, TrainingStudent.first_name).all()
    return render_template('training/students.html', students=rows, q=q)


@training_bp.route('/eleves/<int:student_id>')
@login_required
def student(student_id):
    stu = _own_student(student_id)
    records = sorted(stu.records, key=lambda r: r.created_at or datetime.min, reverse=True)
    return render_template('training/student.html', stu=stu, records=records, doc_kinds=STUDENT_DOC_KINDS,
                           kind_medical=KIND_MEDICAL, kind_waiver=KIND_WAIVER, today=date.today())


@training_bp.route('/eleves/<int:student_id>', methods=['POST'])
@login_required
def update_student(student_id):
    stu = _own_student(student_id)
    first = (request.form.get('first_name') or '').strip()
    last = (request.form.get('last_name') or '').strip()
    if not first or not last:
        flash("Prénom et nom de l'élève requis.", 'error')
        return redirect(url_for('training.student', student_id=stu.id))
    stu.first_name, stu.last_name = first, last
    stu.email = (request.form.get('email') or '').strip() or None
    stu.phone = (request.form.get('phone') or '').strip() or None
    stu.birth_date = _parse_date(request.form.get('birth_date'))
    db.session.commit()
    flash('Fiche élève mise à jour.', 'success')
    return redirect(url_for('training.student', student_id=stu.id))


@training_bp.route('/eleves/<int:student_id>/dossiers', methods=['POST'])
@login_required
def create_student_record(student_id):
    stu = _own_student(student_id)
    program = _program_form()
    if not program:
        return redirect(url_for('training.student', student_id=stu.id))
    return _back(_new_record(stu, program).id)


def _store_upload():
    """Enregistre le fichier envoyé ; renvoie (nom stocké, nom d'origine, taille) ou None avec un message."""
    f = request.files.get('file')
    if not f or not f.filename:
        flash('Aucun fichier sélectionné.', 'error')
        return None
    ext = f.filename.rsplit('.', 1)[-1].lower() if '.' in f.filename else ''
    data = f.read(MAX_DOC_BYTES + 1)
    if ext not in ALLOWED_EXT:
        flash('Format refusé (PDF, JPG, JPEG ou PNG uniquement).', 'error')
    elif len(data) > MAX_DOC_BYTES:
        flash('Fichier trop volumineux (10 Mo maximum).', 'error')
    elif not data.startswith(ALLOWED_EXT[ext]):
        flash("Le contenu du fichier ne correspond pas à son extension.", 'error')
    else:
        stored = f'{uuid.uuid4().hex}.{ext}'
        with open(os.path.join(_upload_dir(), stored), 'wb') as out:
            out.write(data)
        return stored, secure_filename(f.filename) or f'document.{ext}', len(data)
    return None


def _remove_file(stored_name):
    try:
        os.remove(os.path.join(_upload_dir(), stored_name))
    except OSError:
        pass


@training_bp.route('/eleves/<int:student_id>/documents', methods=['POST'])
@login_required
def upload_student_document(student_id):
    stu = _own_student(student_id)
    back = redirect(url_for('training.student', student_id=stu.id) + '#documents')
    valid_raw = (request.form.get('valid_until') or '').strip()
    valid_until = _parse_date(valid_raw)
    if valid_raw and not valid_until:
        flash('Date de validité invalide.', 'error')
        return back
    stored = _store_upload()
    if stored:
        kind = request.form.get('kind')
        db.session.add(TrainingStudentDocument(
            student_id=stu.id, kind=kind if kind in STUDENT_DOC_KINDS else 'Autre', valid_until=valid_until,
            stored_name=stored[0], original_name=stored[1], size=stored[2], uploaded_by=_display_name()))
        db.session.commit()
        flash('Document ajouté à la fiche de l\'élève.', 'success')
    return back


@training_bp.route('/eleves/<int:student_id>/documents/<int:doc_id>')
@login_required
def download_student_document(student_id, doc_id):
    stu = _own_student(student_id)
    doc = TrainingStudentDocument.query.filter_by(id=doc_id, student_id=stu.id).first_or_404()
    path = os.path.join(_upload_dir(), doc.stored_name)
    if not os.path.isfile(path):
        abort(404)
    return send_file(path, download_name=doc.original_name)


@training_bp.route('/eleves/<int:student_id>/documents/<int:doc_id>/delete', methods=['POST'])
@login_required
def delete_student_document(student_id, doc_id):
    stu = _own_student(student_id)
    doc = TrainingStudentDocument.query.filter_by(id=doc_id, student_id=stu.id).first_or_404()
    _remove_file(doc.stored_name)
    db.session.delete(doc)
    db.session.commit()
    return redirect(url_for('training.student', student_id=stu.id) + '#documents')


@training_bp.route('/<int:record_id>')
@login_required
def record(record_id):
    rec = _own_record(record_id)
    categories = []
    for m in rec.milestones:
        if not categories or categories[-1][0] != m.category:
            categories.append((m.category, []))
        categories[-1][1].append(m)
    linked = {link.instructor_id for link in rec.instructor_links}
    available = [i for i in TrainingInstructor.query.filter_by(owner_id=current_user.id)
                 .order_by(TrainingInstructor.name) if i.id not in linked]
    # Présélectionne l'instructeur du dernier jalon validé, sinon le premier du dossier
    last = max((m for m in rec.milestones if m.training_instructor_id),
               key=lambda m: (m.completed_on, m.sort_order), default=None)
    default_instructor_id = last.training_instructor_id if last else \
        (rec.instructor_links[0].instructor_id if rec.instructor_links else None)
    tab = request.args.get('tab')
    return render_template('training/record.html', rec=rec, categories=categories,
                           statuses=STATUSES, medical_statuses=MEDICAL_STATUSES,
                           doc_kinds=DOC_KINDS, tab=tab if tab in RECORD_TABS else 'overview',
                           kind_medical=KIND_MEDICAL, kind_waiver=KIND_WAIVER,
                           forms=form_states(rec) if rec.is_open_water else [],
                           available_instructors=available, signature_sections=_signature_sections(rec),
                           default_instructor_id=default_instructor_id, today=date.today())


@training_bp.route('/<int:record_id>/milestone/<int:milestone_id>', methods=['POST'])
@login_required
def update_milestone(record_id, milestone_id):
    rec, ok = _editable_record(record_id)
    if not ok:
        return _back(record_id)
    m = TrainingMilestone.query.filter_by(id=milestone_id, record_id=rec.id).first_or_404()
    notes = (request.form.get('notes') or '').strip() or None
    if request.form.get('action') == 'undo':
        covering = next((s for s in rec.signatures if SIGNATURE_LINES[s.kind]['covers'](m)), None)
        if covering:
            flash(f'Ce jalon est attesté par la signature « {covering.title} » : retirez-la d\'abord.', 'error')
            return _back(record_id, 'milestones')
        m.completed, m.completed_on, m.instructor_id, m.instructor_name = False, None, None, None
        m.training_instructor_id = None
        m.notes = notes
        _log(rec, 'Jalon annulé', m.name)
    else:
        done_on = _parse_date(request.form.get('completed_on')) or date.today()
        if done_on > date.today():
            flash('La date de validation ne peut pas être dans le futur.', 'error')
            return _back(record_id, 'milestones')
        instructor = next((link.instructor for link in rec.instructor_links
                           if str(link.instructor_id) == request.form.get('instructor_id')), None)
        if not instructor:
            flash("Choisissez l'instructeur du dossier qui valide ce jalon.", 'error')
            return _back(record_id, 'milestones')
        m.completed, m.completed_on = True, done_on
        m.instructor_id = current_user.id
        m.training_instructor_id, m.instructor_name = instructor.id, instructor.name
        m.notes = notes
        _log(rec, 'Jalon validé', f'{m.name} — {done_on.isoformat()} — {instructor.name}')
    db.session.commit()
    return redirect(url_for('training.record', record_id=record_id, tab='milestones') + f'#m{m.id}')


@training_bp.route('/<int:record_id>/status', methods=['POST'])
@login_required
def update_status(record_id):
    rec, ok = _editable_record(record_id)
    if not ok:
        return _back(record_id)
    new = request.form.get('status')
    if new not in STATUSES:
        flash('Statut invalide.', 'error')
        return _back(record_id)
    if new in (STATUS_COMPLETED, STATUS_CERTIFIED) and rec.progress < 100:
        flash('Tous les jalons doivent être validés avant ce statut.', 'error')
        return _back(record_id)
    if new == STATUS_CERTIFIED and rec.is_open_water and not rec.signature_for('open_water'):
        flash('La signature « Certification PADI Open Water Diver » est requise pour le statut Certifié.', 'error')
        return _back(record_id, 'signatures')
    old, rec.status = rec.status, new
    _log(rec, 'Statut modifié', f'{old} → {new}')
    db.session.commit()
    flash('Statut mis à jour.', 'success')
    return _back(record_id)


@training_bp.route('/<int:record_id>/medical', methods=['POST'])
@login_required
def update_medical(record_id):
    rec, ok = _editable_record(record_id)
    if not ok:
        return _back(record_id)
    new = request.form.get('medical_status')
    if new not in MEDICAL_STATUSES:
        flash('Statut médical invalide.', 'error')
        return _back(record_id)
    old, rec.medical_status = rec.medical_status, new
    _log(rec, 'Statut médical modifié', f'{old} → {new}')
    db.session.commit()
    flash('Statut médical mis à jour.', 'success')
    return _back(record_id)


@training_bp.route('/<int:record_id>/instructors', methods=['POST'])
@login_required
def add_instructor(record_id):
    rec, ok = _editable_record(record_id)
    if not ok:
        return _back(record_id, 'instructors')
    signed_on = _signed_on_form(date.today())
    if not signed_on:
        return _back(record_id, 'instructors')
    instructor_id = request.form.get('instructor_id')
    if instructor_id:
        instructor = TrainingInstructor.query.filter_by(id=instructor_id, owner_id=current_user.id).first()
        if not instructor:
            flash('Instructeur introuvable.', 'error')
            return _back(record_id, 'instructors')
    else:
        fields = _instructor_form()
        if not fields:
            return _back(record_id, 'instructors')
        if TrainingInstructor.query.filter_by(owner_id=current_user.id,
                                              padi_number=fields['padi_number']).first():
            flash(f"L'instructeur PADI #{fields['padi_number']} existe déjà : choisissez-le dans la liste.",
                  'error')
            return _back(record_id, 'instructors')
        instructor = TrainingInstructor(owner_id=current_user.id, **fields)
        db.session.add(instructor)
        db.session.flush()
    if any(link.instructor_id == instructor.id for link in rec.instructor_links):
        flash('Cet instructeur figure déjà au dossier.', 'error')
        return _back(record_id, 'instructors')
    rec.instructor_links.append(TrainingRecordInstructor(instructor_id=instructor.id, signed_on=signed_on))
    _log(rec, 'Instructeur ajouté', f'{instructor.name} — PADI #{instructor.padi_number}')
    db.session.commit()
    flash('Instructeur ajouté au dossier.', 'success')
    return _back(record_id, 'instructors')


@training_bp.route('/<int:record_id>/instructors/<int:link_id>', methods=['POST'])
@login_required
def update_instructor(record_id, link_id):
    rec, ok = _editable_record(record_id)
    if not ok:
        return _back(record_id, 'instructors')
    link = TrainingRecordInstructor.query.filter_by(id=link_id, record_id=rec.id).first_or_404()
    fields = _instructor_form()
    signed_on = _signed_on_form(link.signed_on or date.today()) if fields else None
    if not signed_on:
        return _back(record_id, 'instructors')
    if TrainingInstructor.query.filter(TrainingInstructor.owner_id == current_user.id,
                                       TrainingInstructor.padi_number == fields['padi_number'],
                                       TrainingInstructor.id != link.instructor_id).first():
        flash(f"Un autre instructeur porte déjà le n° PADI #{fields['padi_number']}.", 'error')
        return _back(record_id, 'instructors')
    for key, value in fields.items():
        setattr(link.instructor, key, value)
    link.signed_on = signed_on
    _log(rec, 'Instructeur modifié', f'{link.instructor.name} — PADI #{link.instructor.padi_number}')
    db.session.commit()
    flash('Instructeur mis à jour.', 'success')
    return _back(record_id, 'instructors')


@training_bp.route('/<int:record_id>/instructors/<int:link_id>/remove', methods=['POST'])
@login_required
def remove_instructor(record_id, link_id):
    rec, ok = _editable_record(record_id)
    if not ok:
        return _back(record_id, 'instructors')
    link = TrainingRecordInstructor.query.filter_by(id=link_id, record_id=rec.id).first_or_404()
    if any(m.training_instructor_id == link.instructor_id for m in rec.milestones) or \
            any(s.instructor_id == link.instructor_id for s in rec.signatures):
        flash('Impossible de retirer cet instructeur : il a validé des jalons ou signé ce dossier.', 'error')
        return _back(record_id, 'instructors')
    _log(rec, 'Instructeur retiré', f'{link.instructor.name} — PADI #{link.instructor.padi_number}')
    db.session.delete(link)
    db.session.commit()
    flash('Instructeur retiré du dossier.', 'success')
    return _back(record_id, 'instructors')


@training_bp.route('/<int:record_id>/signatures', methods=['POST'])
@login_required
def sign(record_id):
    rec, ok = _editable_record(record_id)
    if not ok:
        return _back(record_id, 'signatures')
    kind = request.form.get('kind')
    line = SIGNATURE_LINES.get(kind)
    if not line:
        abort(400)
    image = _signature_png(request.form.get('image'))
    if not image:
        flash('Signature vide ou invalide : signez dans le cadre.', 'error')
        return _back(record_id, 'signatures')
    instructor = None
    if line['role'] == 'instructor':
        instructor = next((link.instructor for link in rec.instructor_links
                           if str(link.instructor_id) == request.form.get('instructor_id')), None)
        if not instructor:
            flash("Choisissez l'instructeur du dossier qui signe.", 'error')
            return _back(record_id, 'signatures')
    if rec.signature_for(kind, instructor.id if instructor else None):
        flash('Cette ligne est déjà signée.', 'error')
        return _back(record_id, 'signatures')
    missing = _missing(rec, kind)
    if missing:
        flash(f'Signature impossible, il manque : {_missing_text(missing)}.', 'error')
        return _back(record_id, 'signatures')
    # Signature au doigt seule pour l'instant : on note le compte si c'est celui de l'instructeur
    own_account = instructor and _is_account_of(current_user, instructor)
    signer = instructor.name if instructor else rec.student.full_name
    rec.signatures.append(TrainingSignature(
        kind=kind, instructor_id=instructor.id if instructor else None, signer_name=signer,
        padi_number=instructor.padi_number if instructor else None, statement=line['statement'],
        image=image, created_by=current_user.id, signer_user_id=current_user.id if own_account else None))
    _log(rec, 'Signature ajoutée', f"{line['title']} — {signer}")
    db.session.commit()
    flash('Signature enregistrée.', 'success')
    return _back(record_id, 'signatures')


@training_bp.route('/<int:record_id>/signatures/<int:signature_id>/delete', methods=['POST'])
@login_required
def delete_signature(record_id, signature_id):
    rec, ok = _editable_record(record_id)
    if not ok:
        return _back(record_id, 'signatures')
    sig = TrainingSignature.query.filter_by(id=signature_id, record_id=rec.id).first_or_404()
    if sig.kind == 'student' and any(SIGNATURE_LINES[s.kind].get('requires_student') for s in rec.signatures):
        flash("Retirez d'abord les signatures de certification, qui reposent sur la déclaration de l'élève.",
              'error')
        return _back(record_id, 'signatures')
    if sig.kind == 'open_water' and rec.status == STATUS_CERTIFIED:
        flash('Le dossier est au statut Certifié : changez d\'abord le statut avant de retirer cette signature.',
              'error')
        return _back(record_id, 'signatures')
    _log(rec, 'Signature retirée', f'{sig.title} — {sig.signer_name}')
    db.session.delete(sig)
    db.session.commit()
    flash('Signature retirée.', 'success')
    return _back(record_id, 'signatures')


@training_bp.route('/<int:record_id>/documents', methods=['POST'])
@login_required
def upload_document(record_id):
    rec, ok = _editable_record(record_id)
    if not ok:
        return _back(record_id, 'documents')
    stored = _store_upload()
    if stored:
        kind = request.form.get('kind')
        db.session.add(TrainingDocument(record_id=rec.id, kind=kind if kind in DOC_KINDS else 'Autre',
                                        stored_name=stored[0], original_name=stored[1], size=stored[2],
                                        uploaded_by=_display_name()))
        _log(rec, 'Document ajouté', stored[1])
        db.session.commit()
        flash('Document ajouté.', 'success')
    return _back(record_id, 'documents')


@training_bp.route('/<int:record_id>/documents/<int:doc_id>')
@login_required
def download_document(record_id, doc_id):
    rec = _own_record(record_id)
    doc = TrainingDocument.query.filter_by(id=doc_id, record_id=rec.id).first_or_404()
    path = os.path.join(_upload_dir(), doc.stored_name)
    if not os.path.isfile(path):
        abort(404)
    return send_file(path, download_name=doc.original_name)


@training_bp.route('/<int:record_id>/documents/<int:doc_id>/delete', methods=['POST'])
@login_required
def delete_document(record_id, doc_id):
    rec, ok = _editable_record(record_id)
    if not ok:
        return _back(record_id, 'documents')
    doc = TrainingDocument.query.filter_by(id=doc_id, record_id=rec.id).first_or_404()
    _remove_file(doc.stored_name)
    _log(rec, 'Document supprimé', doc.original_name)
    db.session.delete(doc)
    db.session.commit()
    return _back(record_id, 'documents')


def _snapshot(rec):
    order = list(SIGNATURE_LINES)
    identification = {s.instructor_id: s for s in rec.signatures if s.kind == 'identification'}
    return {
        'student': {'name': rec.student.full_name, 'email': rec.student.email,
                    'birth_date': rec.student.birth_date.isoformat() if rec.student.birth_date else None},
        'program': rec.program_name,
        'start_date': rec.start_date.isoformat() if rec.start_date else None,
        'progress': rec.progress,
        'medical_status': rec.medical_status,
        'forms': [{'code': st['code'], 'title': st['title'], 'label': st['label'], 'required': st['required'],
                   'score': f"{st['entry'].score}/{st['entry'].total}"
                   if st['entry'] and st['entry'].score is not None else None}
                  for st in form_states(rec) if st['state'] != 'na'] if rec.is_open_water else [],
        'instructors': [{'name': link.instructor.name, 'padi_number': link.instructor.padi_number,
                         'center_number': link.instructor.center_number, 'phone': link.instructor.phone,
                         'fax': link.instructor.fax, 'email': link.instructor.email,
                         'signed_on': link.signed_on.isoformat() if link.signed_on else None,
                         'signature': identification[link.instructor_id].image
                         if link.instructor_id in identification else None}
                        for link in rec.instructor_links],
        'signatures': [{'title': s.title, 'statement': s.statement, 'signer': s.signer_name,
                        'padi_number': s.padi_number, 'signed_on': s.signed_at.strftime('%Y-%m-%d'),
                        'image': s.image}
                       for s in sorted(rec.signatures, key=lambda s: order.index(s.kind))
                       if s.kind != 'identification'],
        'milestones': [{'category': m.category, 'name': m.name, 'completed': m.completed,
                        'date': m.completed_on.isoformat() if m.completed_on else None,
                        'instructor': m.training_instructor.name if m.training_instructor
                        else m.instructor_name,
                        'instructor_padi': m.training_instructor.padi_number if m.training_instructor
                        else None} for m in rec.milestones],
    }


def is_minor(rec):
    """Élève mineur au début de la formation ; None si la date de naissance est inconnue."""
    born = rec.student.birth_date
    if not born:
        return None
    ref = rec.start_date or date.today()
    age = ref.year - born.year - ((ref.month, ref.day) < (born.month, born.day))
    return age < ADULT_AGE


def _form_roles(rec, form):
    roles = form['roles']
    if form['kind'] == 'sign' and not form.get('minors_only') and is_minor(rec):
        roles = roles + ('guardian',)
    return roles


def form_states(rec):
    """État de chaque formulaire du cours pour ce dossier, dans l'ordre du catalogue."""
    minor = is_minor(rec)
    entries = {e.form_code: e for e in rec.form_entries}
    sigs = {(g.form_code, g.role): g for g in rec.form_signatures}
    medical = rec.student.latest_document(KIND_MEDICAL)
    out = []
    for f in OW_FORMS:
        code, entry = f['code'], entries.get(f['code'])
        roles = _form_roles(rec, f)
        rows = [(r, ROLE_LABELS[r], sigs.get((code, r))) for r in roles]
        signed = sum(1 for _, _, g in rows if g)
        scanned = bool(entry and entry.scan_name)
        state, label = 'todo', 'À faire'
        if f.get('minors_only') and not minor:
            state, label = 'na', ('Non requis' if minor is False else 'Date de naissance manquante')
        elif f['kind'] == 'scan':
            if scanned:
                state, label = 'done', 'Scan reçu'
            elif medical and not medical.expired:
                state, label = 'done', "Pièce de l'élève valide"
            else:
                label = 'Scan à joindre'
        elif f['kind'] == 'quiz':
            has_score = bool(entry and entry.score is not None)
            if has_score and (scanned or signed == len(rows)):
                state, label = 'done', 'Complété'
            elif has_score or scanned or signed:
                state, label = 'partial', 'Incomplet'
        elif scanned or (rows and signed == len(rows)):
            state, label = 'done', 'Scan reçu' if scanned else 'Signé'
        elif signed:
            state, label = 'partial', f'{signed}/{len(rows)} signatures'
        out.append({'form': f, 'code': code, 'title': f['title'], 'required': f['required'], 'state': state,
                    'label': label, 'rows': rows, 'entry': entry})
    return out


def _form_or_404(code):
    form = FORMS_BY_CODE.get(code)
    if not form:
        abort(404)
    return form


def _form_pdf_path(form):
    """PDF officiel du formulaire (fichier de Doc/OW dont le nom commence par le code)."""
    folder = os.path.join(current_app.root_path, FORMS_SUBDIR)
    prefix = form.get('prefix', form['code'])
    if os.path.isdir(folder):
        for name in sorted(os.listdir(folder)):
            if name.startswith(prefix) and name.lower().endswith('.pdf'):
                return os.path.join(folder, name)
    return None


def _form_back(record_id, code=None):
    if code:
        return redirect(url_for('training.form_page', record_id=record_id, code=code))
    return _back(record_id, 'forms')


@training_bp.route('/formulaires/<code>.pdf')
@login_required
def form_pdf(code):
    path = _form_pdf_path(_form_or_404(code))
    if not path:
        abort(404)
    return send_file(path, mimetype='application/pdf', download_name=os.path.basename(path))


@training_bp.route('/<int:record_id>/formulaires/<code>')
@login_required
def form_page(record_id, code):
    rec = _own_record(record_id)
    if not rec.is_open_water:
        abort(404)
    _form_or_404(code)
    state = next(st for st in form_states(rec) if st['code'] == code)
    return render_template('training/form.html', rec=rec, st=state, has_pdf=bool(_form_pdf_path(state['form'])),
                           totals=QUIZ_TOTALS, today=date.today())


@training_bp.route('/<int:record_id>/formulaires/<code>/signer', methods=['POST'])
@login_required
def sign_form(record_id, code):
    rec, ok = _editable_record(record_id)
    form = _form_or_404(code)
    if not ok or not rec.is_open_water:
        return _form_back(record_id, code if ok else None)
    role = request.form.get('role')
    if role not in _form_roles(rec, form):
        abort(400)
    image = _signature_png(request.form.get('image'))
    if not image:
        flash('Signature vide ou invalide : signez dans le cadre.', 'error')
        return _form_back(record_id, code)
    if any(g.form_code == code and g.role == role for g in rec.form_signatures):
        flash('Cette signature existe déjà.', 'error')
        return _form_back(record_id, code)
    name = rec.student.full_name if role in ('student', 'minor') \
        else (request.form.get('signer_name') or '').strip()[:120]
    if not name:
        flash('Indiquez le nom du parent ou du tuteur.', 'error')
        return _form_back(record_id, code)
    rec.form_signatures.append(TrainingFormSignature(form_code=code, role=role, signer_name=name, image=image,
                                                     created_by=current_user.id))
    _log(rec, 'Formulaire signé', f"{code} — {ROLE_LABELS[role]} : {name}")
    db.session.commit()
    flash('Signature enregistrée.', 'success')
    return _form_back(record_id, code)


@training_bp.route('/<int:record_id>/formulaires/<code>/signatures/<int:signature_id>/delete', methods=['POST'])
@login_required
def delete_form_signature(record_id, code, signature_id):
    rec, ok = _editable_record(record_id)
    _form_or_404(code)
    if not ok:
        return _form_back(record_id)
    sig = TrainingFormSignature.query.filter_by(id=signature_id, record_id=rec.id, form_code=code).first_or_404()
    _log(rec, 'Signature de formulaire retirée', f'{code} — {ROLE_LABELS.get(sig.role, sig.role)} : {sig.signer_name}')
    db.session.delete(sig)
    db.session.commit()
    flash('Signature retirée.', 'success')
    return _form_back(record_id, code)


def _entry(rec, code):
    entry = next((e for e in rec.form_entries if e.form_code == code), None)
    if not entry:
        entry = TrainingFormEntry(form_code=code)
        rec.form_entries.append(entry)
    return entry


@training_bp.route('/<int:record_id>/formulaires/<code>/scan', methods=['POST'])
@login_required
def upload_form_scan(record_id, code):
    rec, ok = _editable_record(record_id)
    _form_or_404(code)
    if not ok or not rec.is_open_water:
        return _form_back(record_id)
    stored = _store_upload()
    if stored:
        entry = _entry(rec, code)
        if entry.scan_name:
            _remove_file(entry.scan_name)
        entry.scan_name, entry.scan_original, entry.scan_size = stored
        _log(rec, 'Scan de formulaire ajouté', f'{code} — {stored[1]}')
        db.session.commit()
        flash('Scan enregistré.', 'success')
    return _form_back(record_id, code)


@training_bp.route('/<int:record_id>/formulaires/<code>/scan/delete', methods=['POST'])
@login_required
def delete_form_scan(record_id, code):
    rec, ok = _editable_record(record_id)
    _form_or_404(code)
    entry = next((e for e in rec.form_entries if e.form_code == code), None)
    if ok and entry and entry.scan_name:
        _remove_file(entry.scan_name)
        _log(rec, 'Scan de formulaire supprimé', f'{code} — {entry.scan_original}')
        entry.scan_name = entry.scan_original = entry.scan_size = None
        db.session.commit()
    return _form_back(record_id, code if ok else None)


@training_bp.route('/<int:record_id>/formulaires/<code>/scan/file')
@login_required
def download_form_scan(record_id, code):
    rec = _own_record(record_id)
    _form_or_404(code)
    entry = next((e for e in rec.form_entries if e.form_code == code), None)
    if not entry or not entry.scan_name:
        abort(404)
    path = os.path.join(_upload_dir(), entry.scan_name)
    if not os.path.isfile(path):
        abort(404)
    return send_file(path, download_name=entry.scan_original)


@training_bp.route('/<int:record_id>/formulaires/<code>/score', methods=['POST'])
@login_required
def save_quiz_score(record_id, code):
    rec, ok = _editable_record(record_id)
    if _form_or_404(code)['kind'] != 'quiz':
        abort(404)
    if not ok or not rec.is_open_water:
        return _form_back(record_id)
    try:
        total, score = int(request.form.get('total')), int(request.form.get('score'))
    except (TypeError, ValueError):
        total = score = -1
    done_on = _parse_date(request.form.get('done_on')) or date.today()
    if total not in QUIZ_TOTALS or not 0 <= score <= total:
        flash(f'Score invalide : indiquez un nombre de bonnes réponses de 0 à {total if total in QUIZ_TOTALS else 21}.',
              'error')
    elif done_on > date.today():
        flash('La date ne peut pas être dans le futur.', 'error')
    else:
        entry = _entry(rec, code)
        entry.score, entry.total, entry.done_on = score, total, done_on
        _log(rec, 'Révision rapide notée', f'{score}/{total} — {done_on.isoformat()}')
        db.session.commit()
        flash('Score enregistré.', 'success')
    return _form_back(record_id, code)


@training_bp.route('/<int:record_id>/formulaires/<code>/signe.pdf')
@login_required
def form_signed_pdf(record_id, code):
    """PDF officiel avec les signatures à l'écran superposées (généré à la demande, rien n'est stocké)."""
    rec = _own_record(record_id)
    form = _form_or_404(code)
    path = _form_pdf_path(form)
    sigs = {g.role: {'image': base64.b64decode(g.image), 'date': g.signed_at.date(), 'name': g.signer_name}
            for g in rec.form_signatures if g.form_code == code}
    if not rec.is_open_water or not can_stamp(code) or not sigs or not path:
        abort(404)
    data = stamp(path, code, sigs)
    return send_file(io.BytesIO(data), mimetype='application/pdf',
                     download_name=f'{code}-{secure_filename(rec.student.full_name) or "eleve"}-signe.pdf')


@training_bp.route('/<int:record_id>/formulaires/<code>/attestation')
@login_required
def form_attestation(record_id, code):
    rec = _own_record(record_id)
    form = _form_or_404(code)
    st = next((x for x in form_states(rec) if x['code'] == code), None)
    if not st or not any(g for _, _, g in st['rows']):
        abort(404)
    return render_template('training/form_sheet.html', rec=rec, st=st, form=form)


def _document_number(prefix):
    return f'{prefix}-{date.today():%Y%m%d}-{uuid.uuid4().hex[:6].upper()}'


@training_bp.route('/<int:record_id>/suivi', methods=['POST'])
@login_required
def create_followup(record_id):
    # Un dossier transféré reste consultable : le suivi n'y change rien, on peut donc en émettre un.
    rec = _own_record(record_id)
    note = (request.form.get('note') or '').strip()[:200] or None
    followup = TrainingFollowup(number=_document_number('SU'), issued_on=date.today(), note=note,
                                snapshot=json.dumps(_snapshot(rec), ensure_ascii=False))
    rec.followups.append(followup)
    _log(rec, 'Document de suivi émis', followup.number)
    db.session.commit()
    flash('Document de suivi émis : le dossier reste modifiable.', 'success')
    return _back(record_id, 'transfer')


@training_bp.route('/<int:record_id>/suivi/<int:followup_id>')
@login_required
def print_followup(record_id, followup_id):
    rec = _own_record(record_id)
    f = TrainingFollowup.query.filter_by(id=followup_id, record_id=rec.id).first_or_404()
    return render_template('training/transfer.html', rec=rec, snap=json.loads(f.snapshot),
                           title='Document de suivi de formation', number=f.number, issued_on=f.issued_on,
                           valid_until=None, destination=None, note=f.note)


@training_bp.route('/<int:record_id>/transfer', methods=['POST'])
@login_required
def create_transfer(record_id):
    rec, ok = _editable_record(record_id)
    if not ok:
        return _back(record_id, 'transfer')
    if rec.status == STATUS_CANCELLED:
        flash('Un dossier annulé ne peut pas être transféré.', 'error')
        return _back(record_id, 'transfer')
    number = _document_number('TR')
    rec.transfer = TrainingTransfer(number=number, issued_on=date.today(),
                                    valid_until=date.today() + timedelta(days=365),
                                    destination=(request.form.get('destination') or '').strip() or None,
                                    snapshot=json.dumps(_snapshot(rec), ensure_ascii=False))
    rec.status, rec.locked = STATUS_TRANSFER, True
    _log(rec, 'Transfert émis', number)
    db.session.commit()
    flash('Transfert émis : le dossier est maintenant verrouillé.', 'success')
    return _back(record_id, 'transfer')


@training_bp.route('/<int:record_id>/transfer/print')
@login_required
def print_transfer(record_id):
    rec = _own_record(record_id)
    if not rec.transfer:
        abort(404)
    t = rec.transfer
    return render_template('training/transfer.html', rec=rec, snap=json.loads(t.snapshot),
                           title='Attestation de transfert de formation', number=t.number,
                           issued_on=t.issued_on, valid_until=t.valid_until, destination=t.destination,
                           note=None)