CREATE TABLE movie(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  numero VARCHAR(8) NOT NULL,
  title VARCHAR(256) NOT NULL,
  imdb VARCHAR(32),
  about VARCHAR(1024)
);

CREATE TABLE people(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(64) NOT NULL,
  about VARCHAR(1024)
);

CREATE TABLE borrow(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  movie_id INT NOT NULL,
  people_id INT NOT NULL,
  borrowed DATETIME NOT NULL,
  returned DATETIME,
  FOREIGN KEY(movie_id) REFERENCES movie(id),
  FOREIGN KEY(people_id) REFERENCES people(id)
);

CREATE VIEW denormalisedborrow AS
SELECT movie_id,
       people_id,
       numero AS movie_numero,
       title AS movie_title,
       name AS borrower,
       borrowed,
       returned
FROM borrow
INNER JOIN movie ON movie.id=movie_id
INNER JOIN people ON people.id=people_id;

CREATE VIEW notreturnedmovie AS
SELECT movie_id,people_id,movie_numero,movie_title,borrower,borrowed
FROM denormalisedborrow
WHERE returned IS NULL;

CREATE VIEW archive AS
SELECT * FROM denormalisedborrow
WHERE returned IS NOT NULL;

CREATE VIEW disponiblemovie AS
SELECT movie.id AS id, numero, title, imdb,about FROM movie
LEFT JOIN borrow ON movie.id=movie_id
WHERE returned IS NOT NULL;
