-- DDL
-- (1) DROPS
-- (2) CREATE TABLES
-- (3) INSERT VALUES
-- (4) CREATE VIEWS
-- (5) CREATE TRIGGERS/PROCEDURES

DROP TABLE IF EXISTS artifact;
DROP TABLE IF EXISTS journal;
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS slide;
DROP TABLE IF EXISTS dig;
DROP TABLE IF EXISTS lab_shelf;
DROP TABLE IF EXISTS deleted_person;
DROP TABLE IF EXISTS person;

DROP VIEW IF EXISTS Employees;
DROP VIEW IF EXISTS Students;
DROP VIEW IF EXISTS Artifacts_Report;
DROP VIEW IF EXISTS Artifacts_Total;
DROP VIEW IF EXISTS Book_Loans;

-----------------------------------create table-------------------------------------------------

/* Skapar tabell för entitet person */
CREATE TABLE person (
    PRIMARY KEY     (person_id),
    person_id       VARCHAR(11),
    p_firstname     VARCHAR(15) NOT NULL,
    p_lastname      VARCHAR(15) NOT NULL,
    p_faculty       VARCHAR(30) NOT NULL,
    p_role          VARCHAR(30) NOT NULL,
);

/* Skapar tabell för entitet deleted_person */
CREATE TABLE deleted_person (
    PRIMARY KEY     (person_id),
    person_id       VARCHAR(11),
    p_firstname     VARCHAR(15) NOT NULL,
    p_lastname      VARCHAR(15) NOT NULL,
    p_faculty       VARCHAR(30) NOT NULL,
    p_role          VARCHAR(30) NOT NULL,
);

/* Skapar tabell för entitet lab_shelf */
CREATE TABLE lab_shelf (
    PRIMARY KEY     (shelf_id),
    shelf_id        VARCHAR(4),
    s_row           INT NOT NULL,
);

 /* Skapar tabell för entitet dig */
CREATE TABLE dig (
    PRIMARY KEY     (dig_id),
    dig_id          INT,
    d_site          VARCHAR(20) NOT NULL,
    d_equipment     VARCHAR(50),
    documentation   VARCHAR(100),
);

 /* Skapar tabell för entitet slide */
CREATE TABLE slide (
    PRIMARY KEY (slide_id),
    slide_id        VARCHAR(20),
    s_description   VARCHAR(500),
    dig_number      INT NOT NULL,
    seq_number      INT NOT NULL,
    person_id       VARCHAR(11),
    FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

 /* Skapar tabell för entitet book */
CREATE TABLE book (
    PRIMARY KEY (book_id),
    book_id         INT,
    book_name       VARCHAR(50) NOT NULL,
    author_name     VARCHAR(30) NOT NULL,
    publisher       VARCHAR(30) NOT NULL,
    barcode         INT NOT NULL,
    loaner          VARCHAR(11),
    FOREIGN KEY     (loaner)
    REFERENCES      person(person_id),
    );

/* Skapar tabell för entitet journal */
CREATE TABLE journal (
    PRIMARY KEY (journal_id),
    journal_id      INT,
    author_name     VARCHAR(30) NOT NULL,
    publisher       VARCHAR(30) NOT NULL,
    person_id       VARCHAR(11),
    FOREIGN KEY     (person_id)
    REFERENCES      person(person_id),
);

/* Skapar tabell för entitet artifact */
CREATE TABLE artifact (
    PRIMARY KEY     (artifact_id),
    artifact_id     VARCHAR(5),
    a_description   VARCHAR(100) NOT NULL,
    person_id       VARCHAR(11),
    shelf_id        VARCHAR(4),
    dig_id          INT,
    FOREIGN KEY     (person_id)
    REFERENCES      person(person_id),
    FOREIGN KEY     (shelf_id)
    REFERENCES      lab_shelf(shelf_id),
    FOREIGN KEY     (dig_id)
    REFERENCES      dig(dig_id),
    );

-----------------------------------insert into-------------------------------------------------

/* Sätter in värden i person */
INSERT INTO person (person_id, p_firstname, p_lastname, p_faculty, p_role)
    VALUES  ('930521-5543', 'Markus', 'Johansson', 'Archeological Faculty', 'Student'),
            ('860523-1314', 'Truls', 'Joelsson', 'Archeological faculty', 'Field Researcher'),
            ('870523-2487', 'Kruls', 'Poelsson', 'Archeological faculty', 'Slide Librarian'),
            ('681011-9965', 'Erik', 'Babbensson', 'Archeological faculty', 'Student'),
            ('050528-6541', 'Keno', 'Lettovinne', 'Archeological faculty', 'Director'),
            ('760526-4343', 'Flemming', 'Dansk', 'Archeological faculty', 'Museum Librarian'),
            ('370522-5442', 'Indiana', 'John', 'Archeological faculty', 'Lab Supervisor'),
            ('860523-5465', 'Petder', 'Pikatchu', 'Archeological faculty', 'Field Researcher'),
            ('800523-7815', 'Toel',  'Dillonsson', 'Archeological faculty', 'Field Researcher'),
            ('840524-1214', 'Hannes', 'Haggarmannen', 'Archeological faculty', 'Student'),
            ('960205-6216', 'Hannes', 'Hackersson', 'Archeological faculty', 'Student'),
            ('830625-2519', 'Oskar', 'Von Sfvinhufvud', 'Archeological faculty', 'Field Researcher'),
            ('821223-8921', 'Masse', 'Mattsson', 'Archeological faculty', 'Field Researcher'),
            ('831027-5234', 'Johanna', 'Isakasson', 'Archeological faculty', 'Student'),
            ('810921-5516', 'Johan', 'Svensson', 'Archeological faculty', 'Student'),
            ('750317-8212', 'Joakim', 'Storboga', 'Archeological faculty', 'Student');

/* Sätter in värden i lab_shelf */
INSERT INTO lab_shelf (shelf_id, s_row)
    VALUES  ('77B', 1),
            ('28C', 1),
            ('69B', 2),
            ('69C', 2),
            ('70A', 2),
            ('124B', 2),
            ('1A', 1),
            ('2A', 1),
            ('3A', 1),
            ('4A', 2),
            ('5A', 2),
            ('5B', 2),
            ('6A', 2),
            ('7A', 3),
            ('7B', 3),
            ('7C', 3),
            ('8A', 3),
            ('8B', 3),
            ('9A', 3),
            ('10A', 1),
            ('10B', 1),
            ('10C', 1),
            ('11A', 1),
            ('11B', 1),
            ('11C', 1),
            ('12A', 1),
            ('12B', 1),
            ('13B', 2),
            ('14B', 2),
            ('15B', 2),
            ('16B', 3),
            ('17B', 3),
            ('18B', 3),
            ('19C', 1),
            ('20C', 1),
            ('21A', 1),
            ('21B', 1),
            ('21C', 1),
            ('22C', 2),
            ('23C', 2),
            ('24C', 2),
            ('25A', 3),
            ('26C', 3),
            ('27D', 1),
            ('288D', 1),
            ('29D', 2),
            ('30D', 2);

/* Sätter in värden i dig */
INSERT INTO dig (dig_id, documentation, d_equipment, d_site)
    VALUES  (300, 'Digital Documentation', 'Shovel', 'Hannes backyard'),
            (301, 'Digital Documentation', 'Drill', 'Flemmingsberg'),
            (302, 'Digital Documentation', 'Shovel and Drill', 'Ängelholm'),
            (303, 'Digital Documentation', 'Plastic Shovel & Plastic Bucket', 'Tofta Beachclub'),
            (304, 'Digital Documentation', 'Jack Vegas Machine', 'Säffle Bar&Krog'),
            (305, 'Digital Documentation', 'Nose', 'Näs'),
            (306, 'Digital Documentation', 'Rope & Sledgehammer', 'Jungle Toilet'),
            (307, 'Digital Documentation', 'Drill', 'Bifrost'),
            (308, 'Digital Documentation', 'Falukorv', 'Falun'),
            (309, 'Digital Documentation', 'Hammer', 'Copenhagen'),
            (310, 'Digital Documentation', 'Axe', 'Colonia'),
            (311, 'Digital Documentation', 'Chain Saw', 'Valla');

/* Sätter in värden i artifact */
INSERT INTO artifact (artifact_id, a_description, person_id, shelf_id, dig_id)
    VALUES  ('00001', 'A small rauk from Fårö', '860523-1314', '10B', 302),
            ('00002', 'Old rocket ship from Mars', '800523-7815', '69B', 300),
            ('00003', 'Koenigsegg', '860523-5465', '124B', 305),
            ('00004', 'Koenigsegg', '860523-5465', '288D', 303),
            ('00005', 'Emerald stone', '860523-1314', '10C', 300),
            ('00006', 'Gold coin', '830625-2519', '16B', 304),
            ('00007', 'A faded flower', '821223-8921', '24C', 305),
            ('00008', 'Palm leaf', '830625-2519', '5A', 306),
            ('00009', 'Tarzans hair', '830625-2519', '5B', 306),
            ('00010', 'T-Rex', '821223-8921', '8A', 307),
            ('00011', 'Stegosaurus', '821223-8921', '8B', 307),
            ('00012', 'Wasa crispbread', '800523-7815', '1A', 308),
            ('00013', 'Cigarette butt', '860523-1314', '10A', 300),
            ('00014', 'T-Rex favorite toy', '821223-8921', '9A', 307),
            ('00015', 'Viking sword', '800523-7815', '69C', 301),
            ('00016', 'Runestone', '800523-7815', '70A', 301),
            ('00017', 'Slot machine', '830625-2519', '18B', 307),
            ('00018', 'Shark necklace', '830625-2519', '6A', 306),
            ('00019', 'Ancient Tuborg', '821223-8921', '11A', 309),
            ('00020', 'Wienerbrö', '821223-8921', '11B', 309),
            ('00021', 'Mads Mikkelsen', '821223-8921', '11C', 309),
            ('00022', 'A crashed SAS-plane', '821223-8921', '12B', 309),
            ('00023', 'Lord Bendtner', '821223-8921', '12A', 309),
            ('00024', 'Yung Nyllas bike', '860523-1314', '21C', 310),
            ('00025', 'An old sauna', '860523-1314', '21B', 310),
            ('00026', 'A key', '860523-1314', '21A', 310),
            ('00027', 'A book about ancient Colonia', '860523-1314', '25A', 310),
            ('00028', 'Diamond', '830625-2519', '7A', 311),
            ('00029', 'A dollar bill', '830625-2519', '7B', 311),
            ('00030', 'A coffee cup', '830625-2519', '7C', 311);

INSERT INTO journal (journal_id, author_name, publisher, person_id)
        VALUES (13, 'oskar', 'bengan', '840524-1214');

INSERT INTO book (book_id, book_name, author_name, publisher, barcode, loaner)
        VALUES   (1337, 'How to be vegan', 'Mange the munk', 'Vegan Publisher', 478194672, '840524-1214'),
                 (1338, 'How to be gey', 'Hanký the pank', 'Christian Publisher', 675487214, '681011-9965');

INSERT INTO slide (slide_id, s_description, dig_number, seq_number, person_id)
     VALUES ('309-01', 'In the deep concrete jungle of norreport we found an ancient can of the world famous Tuborg Grön. It has a few scratches but is still in good condition', 309, 01, '821223-8921'),
            ('309-02', 'After two months of hard work I found queen Margrethe the seconds old Winerbrö, it still has sprinkles of her favourite topping, gold.', 309, 02, '821223-8921'),
            ('309-03', 'On an crispy December morning we found a drunk Mads Mikkelsen in a ditch near Ströget, he couldnt explain how he got there but he was in a good mood.', 309, 03,  '821223-8921'),
            ('309-04', 'In the third month of our journey we found a boeing 747 from SAS called "The Viking" in the beautiful inlet of Öresund', 309, 04, '821223-8921');

-----------------------------------create view-------------------------------------------------

/* Vy över museets anställda. */
GO
CREATE VIEW Employees AS
     SELECT P.p_firstname AS 'Firstname', P.p_lastname AS 'Lastname', P.p_role AS 'Role'
       FROM person AS P
      WHERE P.p_role != 'Student'
GO

/* Vy över museets studenter. */
GO
CREATE VIEW Students AS
     SELECT P.p_firstname AS 'Firstname', P.p_lastname AS 'Lastname', P.p_role AS 'Role'
       FROM person AS P
      WHERE P.p_role = 'Student'
GO

/* Vy över artefakter i museet. */
GO
CREATE VIEW Artifacts_Report AS
     SELECT A.artifact_id AS 'ID',
            A.a_description AS 'Description',
            P.p_lastname + ', ' + P.p_firstname AS 'Found By',
            D.d_site AS 'Dig Site',
            L.shelf_id AS 'Shelf Location'
       FROM artifact AS A
        INNER JOIN person AS P
                ON P.person_id = A.person_id
        INNER JOIN lab_shelf AS L
                ON L.shelf_id = A.shelf_id
        INNER JOIN dig AS D
                ON D.dig_id = A.dig_id
GO

/* Här räknas alla artefakter och grupperas ihop om de hittats på samma ställe */
GO
CREATE VIEW Artifacts_Total AS
     SELECT D.d_site AS 'Dig Site',
      COUNT(D.dig_id) as 'Amount of Artifacts'
       FROM dig AS D
 INNER JOIN artifact AS A
         ON A.dig_id = D.dig_id
   GROUP BY D.d_site;
GO

GO
CREATE VIEW Book_Loans AS
     SELECT B.book_name AS 'Book'
       FROM book AS B
GO

-----------------------------------triggers------------------------------------------

/*Bra vid boklån*/
/*
CREATE TRIGGER new_trigger
            ON person
         AFTER INSERT
            AS BEGIN
               UPDATE deleted_person (person_id, p_firstname, p_lastname, p_faculty, p_role)
                    SELECT D.person_id, D.p_firstname, D.p_lastname, D.p_faculty, D.p_role
                      FROM deleted AS D
END;*/