# -*- coding: utf-8 -*-
"""
Corrigé Python - Bac NSI 2026 - Épreuve pratique - Sujet 15
Thème : cabinet vétérinaire, chaînes, SQL et dictionnaires
"""

from __future__ import annotations

import sqlite3

DB_PATH = "cabinet.sqlite"


def normalisation_tel(tel: str) -> str:
    """Supprime tous les caractères non numériques d'un numéro de téléphone."""
    numero = ""
    for caractere in tel:
        if caractere.isdigit():
            numero += caractere
    return numero


def validation_tel(tel: str) -> bool:
    """Valide un numéro portable français normalisé."""
    return len(tel) == 10 and tel[0] == "0" and tel[1] in ["6", "7"]


def test_normalisation_tel() -> None:
    assert normalisation_tel("06 12 99 90 12") == "0612999012"
    assert normalisation_tel("02.12.99.90.12") == "0212999012"
    assert normalisation_tel("0.6.12.99.90.12") == "0612999012"
    assert normalisation_tel("06-12-99-90-12") == "0612999012"
    assert normalisation_tel("(0)6.12.99-90-12 gilbert") == "0612999012"


def test_validation_tel() -> None:
    assert validation_tel("0612999012") is True
    assert validation_tel("0712999012") is True
    assert validation_tel("0212999012") is False
    assert validation_tel("061299901") is False
    assert validation_tel("06129990123") is False


def proprietaires_animaux_nes_apres(date: str) -> list[tuple[str, str]]:
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    resultat = cursor.execute(
        """
        SELECT proprietaire.nom, proprietaire.prenom
        FROM proprietaire
        JOIN animal ON proprietaire.id = animal.id_proprietaire
        WHERE animal.date_naissance > ?
        ORDER BY proprietaire.nom, proprietaire.prenom;
        """,
        (date,),
    )
    valeurs = list(resultat)
    conn.close()
    return valeurs


def consultation_vaccination_chat(date: str) -> list[tuple[int, str, str, str]]:
    """Renvoie les consultations de vaccination de chats après la date donnée."""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    resultat = cursor.execute(
        """
        SELECT animal.id, animal.nom, proprietaire.telephone, consultation.date
        FROM consultation
        JOIN animal ON consultation.id_animal = animal.id
        JOIN proprietaire ON animal.id_proprietaire = proprietaire.id
        WHERE animal.espece = 'chat'
          AND consultation.motif = 'vaccination'
          AND consultation.date > ?
        ORDER BY animal.id, consultation.date;
        """,
        (date,),
    )
    valeurs = list(resultat)
    conn.close()
    return valeurs


def derniere_vaccination(consultations: list[tuple[int, str, str, str]]) -> dict[int, tuple[int, str, str, str]]:
    """Renvoie, pour chaque animal, sa consultation de vaccination la plus récente."""
    derniere = {}
    for consult in consultations:
        id_animal = consult[0]
        date = consult[3]
        if id_animal not in derniere or date > derniere[id_animal][3]:
            derniere[id_animal] = consult
    return derniere


def tests_sans_base() -> None:
    test_normalisation_tel()
    test_validation_tel()
    consultations = [
        (16, "Plume", "0.6.36.96.89.83", "20241024"),
        (16, "Plume", "0.6.36.96.89.83", "20251125"),
        (32, "Chopin", "06.37.97.66.64", "20241201"),
        (32, "Chopin", "06.37.97.66.64", "20251119"),
    ]
    assert derniere_vaccination(consultations)[16][3] == "20251125"
    assert derniere_vaccination(consultations)[32][3] == "20251119"
    print("Les tests simples du sujet 15 sont passés.")


if __name__ == "__main__":
    tests_sans_base()
    # Avec le fichier cabinet.sqlite dans le même dossier :
    # vaccinations = consultation_vaccination_chat("20240923")
    # print(len(vaccinations))
    # print(derniere_vaccination(vaccinations))