--DDL

-- (1) DROPS

DROP VIEW IF EXISTS liban_artifacts;
DROP VIEW IF EXISTS student_staff;
DROP TABLE IF EXISTS book_loan;
DROP TABLE IF EXISTS slide_loan;
DROP TABLE IF EXISTS slides;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS book_shelf;
DROP TABLE IF EXISTS slide_shelf;
DROP TABLE IF EXISTS artifact_card;
DROP TABLE IF EXISTS location_segment;
DROP TABLE IF EXISTS dig;
DROP TABLE IF EXISTS artifact;
DROP TABLE IF EXISTS staff;

-- (2) CREATE TABLES

CREATE TABLE staff(
		PRIMARY KEY(staff_ID),
		staff_ID		VARCHAR(8),
		staff_role		VARCHAR(20)    NOT NULL,
		staff_name		VARCHAR(30)	   NOT NULL,
		date_registered VARCHAR(10)	   NOT NULL,
);

CREATE TABLE artifact(
		PRIMARY KEY(number, grid, depth),
		number		INT,
		grid		INT,
		depth		INT,
		art_owner	VARCHAR(8),
		art_date    INT,
		FOREIGN KEY (art_owner)
		REFERENCES staff(staff_ID)
);

CREATE TABLE dig(
		dig_name	VARCHAR(50)   NOT NULL,
		dig_no		INT,
		dig_date	VARCHAR(10),
		worker		VARCHAR(8),
		PRIMARY KEY(dig_name, dig_no),
		FOREIGN KEY (worker)
		REFERENCES staff(staff_ID)
);

CREATE TABLE location_segment(
		location_ID		VARCHAR(8),
		PRIMARY KEY(location_ID)
);


CREATE TABLE artifact_card(
		Notes			VARCHAR(140),
		card_ID			INT,
		current_pos		VARCHAR(8),
		dig_origin_no	INT,
		dig_origin_name	VARCHAR(50),
		dig_grid		INT,
		dig_depth		INT,
		FOREIGN KEY (card_ID, dig_grid, dig_depth)
		REFERENCES artifact(number, grid, depth),
		FOREIGN KEY (current_pos)
		REFERENCES location_segment(location_ID),
		FOREIGN KEY (dig_origin_name, dig_origin_no)
		REFERENCES dig(dig_name, dig_no),

);

CREATE TABLE slide_shelf(
		s_shelf_number	VARCHAR(5),
		PRIMARY KEY (s_shelf_number)
);

CREATE TABLE book_shelf(
		b_shelf_number	VARCHAR(5),
		PRIMARY KEY (b_shelf_number)
);


CREATE TABLE books(
		book_number	VARCHAR(6),
		title		VARCHAR(30) NOT NULL,
		author		VARCHAR(30) NOT NULL,
		home_shelf	VARCHAR(5),
		PRIMARY KEY(book_number),
		FOREIGN KEY (home_shelf)
		REFERENCES book_shelf(b_shelf_number)

);

CREATE TABLE slides(
		slide_number	VARCHAR(6),
		title		    VARCHAR(30) NOT NULL,
		author		    VARCHAR(30),
		slide_category  VARCHAR(30),
		home_shelf		VARCHAR(5),
		PRIMARY KEY(slide_number),
		FOREIGN KEY (home_shelf)
		REFERENCES slide_shelf(s_shelf_number)
);

CREATE TABLE slide_loan(
        start_date_loan_s       INT,
        end_date_loan_s     INT,
        slide_borrower      VARCHAR(8),
        slide_ID            VARCHAR(6),
        PRIMARY KEY(start_date_loan_s),
        FOREIGN KEY (slide_borrower)
        REFERENCES staff(staff_ID),
        FOREIGN KEY (slide_ID)
        REFERENCES slides(slide_number),
);

CREATE TABLE book_loan(

        start_date_loan     INT,
        end_date_loan       INT,
        book_borrower       VARCHAR(8),
        book_ID             VARCHAR(6),
        PRIMARY KEY(start_date_loan),
        FOREIGN KEY (book_borrower)
        REFERENCES staff(staff_ID),
        FOREIGN KEY (book_ID)
        REFERENCES books(book_number)
);


-- (3) INSERT VALUES

INSERT INTO slide_shelf(s_shelf_number)
VALUES ('O351'),
       ('C123'),
       ('K362'),
       ('I994'),
       ('Q122'),
       ('P125'),
       ('Y156');

INSERT INTO book_shelf(b_shelf_number)
VALUES ('W126'),
       ('J563'),
       ('U898'),
       ('H759'),
       ('O340'),
       ('L998'),
       ('E127'),
       ('H784');

INSERT INTO staff(staff_ID, staff_role, staff_name, date_registered)
VALUES('libra180','Proffessor', 'Liban Rask', 19450505),
      ('vserf495', 'Excavator', 'Vsera Rfoman', 20080905),
	  ('lesar427', 'Proffessor', 'Lesman Armondol', 19951202),
	  ('babol666', 'Excavator', 'Barbro Olev', 19970706),
	  ('Jndio123', 'Excavator', 'Jndiana Iones', 19420713),
      ('johdo321', 'Student', 'John Doe', 19650621),
      ('namna146', 'Student', 'Name Nameson', 19990214),
      ('bombe534', 'Professor', 'Bom Betty', 1986042);

INSERT INTO slides(slide_number, title, author, slide_category, home_shelf)
VALUES('23-001', '15-century Swords', 'Barlos Cardos', 'Science', 'O351'),
      ('33-001', '18-century Dagger', 'Brevor Tartly', 'Art', 'C123'),
      ('23-002', 'Old rock formation', 'Jarl Carl', 'Religion', 'K362'),
      ('33-002', 'legacy grimstroke', 'Timon Simon', 'History', 'P125');

INSERT INTO books(book_number, title, author)
VALUES('JO5678', 'How to train your dragon', 'Carlos Bardos'),
      ('BO1337', 'The good life', 'Trevor Bartly'),
	  ('PB7583', 'Burning birdges', 'Carl Jarl'),
	  ('AS7548', 'Relation list', 'Simon Timon'),
	  ('BO1234', 'The Lord Of The Rings', 'J.R.R Tolkien'),
      ('GS1536', 'Lord of the Flies', 'William Golding'),
      ('PO6845', 'SQL For Dummies', 'Robinson Crusoe');


INSERT INTO book_loan(start_date_loan, end_date_loan, book_borrower, book_ID)
VALUES (20181203, NULL, 'libra180', 'JO5678'),
       (20111107, 20181224, 'libra180', 'BO1337'),
       (20140514, 20180713, 'vserf495', 'PB7583' ),
       (20050315, 20180305, 'babol666', 'AS7548'),
       (20130325, 20181203, 'lesar427', 'JO5678');

INSERT INTO slide_loan(start_date_loan_s, end_date_loan_s, slide_borrower, slide_ID)
VALUES (20180302 , NULL, 'libra180', '23-001'),
       (20161207, 20181224, 'vserf495', '33-001'),
       (20180514, NULL, 'babol666', '23-002'),
       (20060315, 20180305, 'lesar427', '33-002');


INSERT INTO artifact(number, grid, depth, art_owner, art_date)
VALUES (25, 45.46, 256, 'libra180', 19450505),
       (45, 23.27, 322, 'vserf495', 20080905),
       (38, 10.12, 800, 'lesar427', 19951202),
       (10, 32.30, 300, 'babol666', 19970706),
	   (13, 55.76, 450, 'johdo321', 20090507),
	   (43, 32.56, 145, 'namna146', 20070928),
	   (66, 45.87, 201, 'jndio123', 20120128),
	   (23, 13.67, 384, 'bombe534', 20070717),
	   (43, 54.98, 158, 'babol666', 20010101),
	   (24, 43.11, 168, 'libra180', 19770325),
       (67, 85.96, 631, 'vserf495', 20060725),
       (85, 99.94, 351, 'lesar427', 20020523),
       (54, 24.62, 842, 'babol666', 20100505),
	   (70, 17.63, 471, 'johdo321', 20130704),
	   (20, 52.73, 279, 'namna146', 19940808),
	   (36, 66.27, 417, 'jndio123', 20170313),
	   (17, 81.47, 749, 'bombe534', 20140224),
	   (63, 53.33, 826, 'babol666', 20021224);

INSERT INTO dig(dig_name, dig_no, dig_date, worker)
VALUES ('Tomb of Qin Shi Huangdi', 23, 19970904, 'vserf495'),
       ('Ziggurat of Ur', 33, 18859394, 'babol666'),
       ('Moche huaca', 55, 19330506, 'lesar427'),
       ('Pyramids of Giza', 11, 20050304, 'libra180'),
       ('the Great Pyramid of Khufu', 77, 20130207, 'libra180'),
       ('Teotihuacan', 88, 20010709, 'babol666'),
       ('Pyramid of the Sun at Teotihuacan', 84, 20020305, 'lesar427'),
       ('Sunken temple of Caral', 67, 20070707, 'vserf495'),
       ('Karazhan', 51, 20031203, 'vserf495'),
       ('Razorfen downs', 09, 20010112, 'libra180'),
       ('Uldaman', 99, 20060112, 'libra180'),
       ('Uldaman', 77, 20000203, 'libra180');

INSERT INTO location_segment(location_ID)
VALUES ('SH01'),
       ('SH02'),
	   ('SH03'),
	   ('SH04'),
	   ('MU01'),
       ('MU02'),
	   ('MU03'),
	   ('MU04'),
	   ('ST01'),
	   ('ST02'),
	   ('ST03'),
	   ('ST04');

INSERT INTO artifact_card(Notes, dig_origin_no, current_pos, card_ID, dig_grid, dig_depth, dig_origin_name)
VALUES ('epic instance!' , 77, 'SH01', 25, 45.46, 256,  'Uldaman'),
	   ('great big dig', 99, 'ST01', 45, 23.27, 322, 'Uldaman'),
	   ('Amazing place!', 55, 'MU01', 85, 99.94, 351, 'Moche huaca'),
	   ('impressive artwork', 67, 'ST04',  10, 32.30, 300, 'Sunken temple of Caral');

-- (4) CREATE VIEWS
/*Påvisa databasens funktionalitet med SQL. a.Skapa tre vyer (views) som sammanställer data från era tabeller.
b.Skapa fyra frågor (queries) för att demonstrera era tabeller i användning
i.Alla frågor ska använda sig av ORDER BY eller GROUP BY
ii.Max en simpelfråga (Fråga utan join, aggregatfunktion och villkor)
iii.Minst två frågor som ska använda Villkor (WHERE)
iv.En fråga som använder aggregatfunktioner (MAX, SUM, AVG, etc.). c.Tänk på att koppla ihop tabeller med INNER JOIN */

GO
CREATE VIEW student_staff AS
SELECT staff_name, staff_role
FROM staff
WHERE staff_role = 'Student'
GO

SELECT * FROM student_staff

GO

CREATE VIEW liban_artifacts AS
SELECT number, art_owner
FROM artifact
WHERE art_owner = 'libra180'
GO


SELECT * FROM liban_artifacts

SELECT slide_number
FROM slides
ORDER BY slide_number ASC;

SELECT AVG (depth) AS average_depth
FROM artifact


SELECT book_borrower, book_ID
FROM staff
INNER JOIN book_loan ON book_loan.book_borrower = staff.staff_ID
ORDER BY book_borrower

SELECT staff_name, book_ID
FROM staff
LEFT JOIN book_loan ON book_borrower = staff_ID
WHERE book_ID IS NOT NULL





-- (5) CREATE TRIGGERS/PROCEDURES

-- foreign keys ska släppas först?