-- Exo 4
-- à utilser 
-- voir https://www.sqlite.org/foreignkeys.html

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS facture(
       numfacture INTEGER PRIMARY KEY ASC,
       datefacture DATE,
       numoccupation INTEGER,
       montantht INTEGER,
       payee BOOL,
       CONSTRAINT num_occupation_fkey
       FOREIGN KEY (numoccupation)  
       REFERENCES occupation(numoccup)
) ;
 
INSERT INTO facture(datefacture, numoccupation, montantht, payee)
SELECT
date('now'), numoccup,
(julianday(datedepart) - julianday(datearrivee)) * prixnuitht, 0
FROM
occupation NATURAL JOIN chambre
WHERE
date(datedepart) < date('now')
;
