/*CREATE DATABASE netflix*/
/*Bij eerste gebruik kan het voorkomen dat het de database 'netflix' niet gevonden kan worden, haal dan de '/* */' weg hierboven*/
USE netflix;

DROP TABLE IF EXISTS views;
DROP TABLE IF EXISTS profiles;
DROP TABLE IF EXISTS subscribers;
DROP TABLE IF EXISTS episodes;
DROP TABLE IF EXISTS movies;
DROP TABLE IF EXISTS programs;
DROP TABLE IF EXISTS LANGUAGE;

CREATE TABLE subscribers
(
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    address VARCHAR(100) NOT NULL
)

INSERT INTO subscribers(id, name, city, address) VALUES('1215426', 'Fam. van Raalte','Houten',  'Schopenhauerdijkje 5');
INSERT INTO subscribers(id, name, city, address) VALUES('5602533', 'J. van Betlehem','Breda',  'Nietzschestraat 99');
INSERT INTO subscribers(id, name, city, address) VALUES('5285824', 'Fam. van Raalte','Breda',  'Kantlaan 11');

CREATE TABLE profiles
(
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,

    CONSTRAINT profilesFK1
        FOREIGN KEY (id)
            REFERENCES subscribers(id)
            ON DELETE CASCADE
            ON UPDATE CASCADE

)

INSERT INTO profiles(id, name, birthday) VALUES('1215426', 'Frank', '1968-1-25')
INSERT INTO profiles(id, name, birthday) VALUES('5602533', 'Petrus', '1999-6-26')
INSERT INTO profiles(id, name, birthday) VALUES('5285824', 'Fritz', '1968-8-19')


CREATE TABLE languages
(
    code VARCHAR(2) NOT NULL PRIMARY KEY,
    name VARCHAR(32) NOT NULL
)

INSERT INTO languages(code, name) VALUES('en', 'Engels');
INSERT INTO languages(code, name) VALUES('nl', 'Nederlands');
INSERT INTO languages(code, name) VALUES('fr', 'Frans');
INSERT INTO languages(code, name) VALUES('de', 'Duits');

CREATE TABLE programs
(
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    genre VARCHAR NOT NULL CHECK (genre IN('action', 'documentary', 'comedy', 'horror', 'thriller')),
    LANGUAGE VARCHAR(2) NOT NULL,
    ageminimum INT NOT NULL,
    ismovie INT NOT NULL CHECK (ismovie IN(0, 1))
    /* De lengte van een programma wordt in movies & episodes bijgehouden, en in het Java systeem berekend */
)

INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie)	VALUES('1010', 'The Abominable Bride', 'action', 'en', '12', '1')
INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie)	VALUES('8002', 'Pulp Fiction', 'comedy', 'en', '16', '1')
INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie)	VALUES('8010', 'The Good, the Bad and the Ugly', 'thriller', 'en', '16', '1')
INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie)	VALUES('8014', 'Der Untergang', 'action', 'en', '6', '1')

INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie)	VALUES('1004', 'Sherlock', 'action', 'en', '16', '0')
INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie)	VALUES('2004', 'Breaking Bad', 'action', 'en', '16', '0')
INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie)	VALUES('2014', 'Breaking Bad', 'action', 'en', '16', '0')
INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie)	VALUES('3002', 'Fargo', 'action', 'en', '16', '0')
INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie)	VALUES('3103', 'Fargo', 'action', 'en', '16', '0')

CREATE TABLE episodes
(
    program INT NOT NULL,
    episodeid INT NOT NULL,
    title VARCHAR(50) NOT NULL,
    LENGTH INT NOT NULL, /* length in minutes */


    CONSTRAINT episodesFK
        FOREIGN KEY (program)
            REFERENCES programs(id),
    CONSTRAINT episodesPK
        PRIMARY KEY (program, episodeId)
)

INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('Sherlock', 'S02E01', 'A Scandal in Belgravia', '88')
INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('Breaking Bad', 'S01E05', 'Gray Matter', '48')
INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('Breaking Bad', 'S02E08', 'Better Call Saul', '48')
INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('Fargo', 'S01E02', 'The Rooster Prince', '68')
INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('Fargo', 'S02E03', 'The Myth of Sisyphus', '68')

CREATE TABLE movies
(
    program INT NOT NULL,
    LENGTH INT NOT NULL,


    CONSTRAINT moviesFK
        FOREIGN KEY (program)
            REFERENCES programs(id)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)

INSERT INTO movies(program, LENGTH) VALUES('The Abominable Bride', '109')
INSERT INTO movies(program, LENGTH) VALUES('Pulp Fiction', '154')
INSERT INTO movies(program, LENGTH) VALUES('The Good, the Bad and the Ugly', '161')
INSERT INTO movies(program, LENGTH) VALUES('Der Untergang', '178')


CREATE TABLE views
(
    id INT NOT NULL PRIMARY KEY,
    profile INT NOT NULL,
    program INT NOT NULL,
    percentage INT NOT NULL,

    CONSTRAINT viewsFK1
        FOREIGN KEY (profile)
            REFERENCES profiles(id)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)

INSERT INTO views(id, profile, program, percentage) VALUES('1215426', '1215426', 'Sherlock', '100')
INSERT INTO views(id, profile, program, percentage) VALUES('5602533', '5602533', 'Fargo', '74')
INSERT INTO views(id, profile, program, percentage) VALUES('5285824', '5285824', 'The Abominable Bride', '5')