## Placez cinema.sql dans le même dossier que ce fichier.
from os import chdir, path, remove
import sqlite3

chdir(r"C:\Users\Prenom\Documents\TP17")  # adapter le chemin

if path.exists("cinema.db"):
    remove("cinema.db")

conn = sqlite3.connect("cinema.db")
with open("cinema.sql", encoding="utf8") as f:
    conn.executescript(f.read().split("ALTER TABLE")[0])
conn.commit()
conn.execute("CREATE VIEW IF NOT EXISTS films AS SELECT * FROM film_1_table")
conn.commit()

def requete(sql):
    curseur = conn.execute(sql)
    for ligne in curseur:
        print(ligne)

# requete("PRAGMA table_info(films)")


## Requêtes sur une seule table

requetes_une_table = [
    "SELECT * FROM films",
    "SELECT titre, annee, categorie, popularite FROM films",
    "SELECT * FROM films LIMIT 20",
    "SELECT * FROM films LIMIT 30 OFFSET 20",
    "SELECT acteur_principal AS Acteurs, actrice_principale AS Actrices FROM films",
    "SELECT * FROM films WHERE recompense = 1",
    "SELECT * FROM films WHERE annee > 1980",
    "SELECT * FROM films WHERE annee < 1960",
    "SELECT * FROM films WHERE duree BETWEEN 100 AND 120",
    "SELECT * FROM films WHERE duree < 60 OR duree >= 120",
    'SELECT * FROM films WHERE realisateur = "Steven Spielberg"',
    'SELECT * FROM films WHERE acteur_principal = "Gérard Depardieu"',
    'SELECT * FROM films WHERE categorie <> "Guerre" AND categorie <> "Action"',
    "SELECT * FROM films ORDER BY annee ASC",
    "SELECT * FROM films ORDER BY annee DESC",
    "SELECT * FROM films ORDER BY categorie",
    "SELECT * FROM films ORDER BY popularite DESC",
    "SELECT * FROM films ORDER BY popularite DESC, recompense DESC, annee ASC",
    "SELECT DISTINCT categorie FROM films",
    "SELECT DISTINCT realisateur FROM films",
]


## Agrégations

requetes_agregations = [
    "SELECT COUNT(*) FROM films",
    'SELECT COUNT(*) FROM films WHERE categorie = "Action"',
    'SELECT COUNT(*) FROM films WHERE realisateur = "Alfred Hitchcock"',
    'SELECT COUNT(*) FROM films WHERE realisateur = "Alfred Hitchcock" AND categorie = "Drame"',
    """SELECT COUNT(DISTINCT acteur_principal),
              COUNT(DISTINCT actrice_principale),
              COUNT(DISTINCT realisateur)
       FROM films""",
    "SELECT MIN(popularite) FROM films",
    "SELECT * FROM films WHERE popularite = (SELECT MIN(popularite) FROM films)",
    "SELECT MAX(popularite) FROM films",
    "SELECT * FROM films WHERE popularite = (SELECT MAX(popularite) FROM films)",
    "SELECT * FROM films WHERE duree = (SELECT MIN(duree) FROM films)",
    "SELECT * FROM films WHERE duree = (SELECT MAX(duree) FROM films)",
    "SELECT COUNT(*) FROM films WHERE recompense = 1",
    "SELECT SUM(duree), AVG(duree), AVG(popularite) FROM films",
    """SELECT categorie, AVG(popularite) AS moy
       FROM films GROUP BY categorie
       ORDER BY moy DESC LIMIT 1""",
    """SELECT realisateur, AVG(popularite) AS moy
       FROM films GROUP BY realisateur
       ORDER BY moy DESC""",
]


## Jointure : table normalisée film (pas la vue films)

requetes_jointure = [
    """SELECT film.titre, categorie.nom
       FROM film JOIN categorie ON film.categorie_id = categorie.id
       LIMIT 10""",
    """SELECT categorie.nom, COUNT(*)
       FROM film JOIN categorie ON film.categorie_id = categorie.id
       GROUP BY categorie.nom""",
]

# for sql in requetes_une_table + requetes_agregations + requetes_jointure:
#     print("---")
#     print(sql)
#     requete(sql)