
DROP TABLE IF EXISTS resepti
;
DROP TABLE IF EXISTS Ruokalaji
;

CREATE TABLE Ruokalaji (
Nimi VARCHAR(100) NOT NULL,
Kuvaus VARCHAR(250) DEFAULT '-',
RuokalajiID INTEGER PRIMARY KEY AUTOINCREMENT,
CONSTRAINT Ruokalaji_Nimi_UQ   
        UNIQUE (Nimi)
)
;

CREATE TABLE Resepti (
Nimi VARCHAR(100) NOT NULL,
Kuvaus VARCHAR(250) DEFAULT '-',
Henkilomaara INTEGER DEFAULT 2 CHECK ( henkilomaara > 0 ),
ReseptiID INTEGER PRIMARY KEY AUTOINCREMENT,
RuokalajiID INTEGER NOT NULL,
CONSTRAINT Resepti_RuokalajiID 
	FOREIGN KEY (RuokalajiID)
	REFERENCES Ruokalaji (RuokalajiID)
)
;

-- seuraava rivi asettaisi viite-eheydet käyttöön
-- mutta koska täytyy asettaa erikseen jokaiselle tietokantayhteydelle
-- on parempi luoda vastaava toiminnallisuus triggereilla. 
-- Valmiit trigger-määritykset saa automaattisesti komennolla .genfkey
-- PRAGMA foreign_keys = ON;
-- Triggers for foreign key mapping:
--
--     Resepti(RuokalajiID) REFERENCES Ruokalaji(RuokalajiID)
--     on delete NO ACTION
--     on update NO ACTION
--

CREATE TRIGGER genfkey1_insert_referencing BEFORE INSERT ON "Resepti" WHEN
    new."RuokalajiID" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "Ruokalaji" WHERE new."RuokalajiID" == "RuokalajiID")
BEGIN
  SELECT RAISE(ABORT, 'Constraint failed: Ruokalajia ei löydy');
END;
CREATE TRIGGER genfkey1_update_referencing BEFORE
    UPDATE OF "RuokalajiID" ON "Resepti" WHEN
    new."RuokalajiID" IS NOT NULL AND
    NOT EXISTS (SELECT 1 FROM "Ruokalaji" WHERE new."RuokalajiID" == "RuokalajiID")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed: Ruokalajia ei löydy');
END;
CREATE TRIGGER genfkey1_delete_referenced BEFORE DELETE ON "Ruokalaji" WHEN
    EXISTS (SELECT 1 FROM "Resepti" WHERE old."RuokalajiID" == "RuokalajiID")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed: Ruokalajia ei voida poistaa, koska se on käytössä');
END;
CREATE TRIGGER genfkey1_update_referenced AFTER
    UPDATE OF "RuokalajiID" ON "Ruokalaji" WHEN
    EXISTS (SELECT 1 FROM "Resepti" WHERE old."RuokalajiID" == "RuokalajiID")
BEGIN
  SELECT RAISE(ABORT, 'constraint failed: Ruokalajin ID:tä ei voida muuttaa, koska ruokalaji on käytössä');
END;


INSERT INTO ruokalaji (nimi, kuvaus, ruokalajiid) VALUES ('Alkuruoka','-', 1);
INSERT INTO ruokalaji (nimi, kuvaus, ruokalajiid) VALUES ('Pääruoka','-', 2);
INSERT INTO ruokalaji (nimi, kuvaus, ruokalajiid) VALUES ('Lisäkeruoka','-', 3);
INSERT INTO ruokalaji (nimi, kuvaus, ruokalajiid) VALUES ('Väli - tai iltapala','-', 4);
INSERT INTO ruokalaji (nimi, kuvaus, ruokalajiid) VALUES ('Jälkiruoka','-', 5);
INSERT INTO ruokalaji (nimi, kuvaus, ruokalajiid) VALUES ('Suolainen leivonnainen','-', 6);
INSERT INTO ruokalaji (nimi, kuvaus, ruokalajiid) VALUES ('Makea leivonnainen','-', 7);

INSERT INTO resepti VALUES ('Ananas Con Carne','Tulinen',4,1,2);
INSERT INTO resepti VALUES ('Tiramisu','Hieno herkku',6,2,7);
INSERT INTO resepti VALUES ('Banaanicurry','Mainio kasvisruoka',2,3,2);
INSERT INTO resepti VALUES ('Porkkanapiirakka','Suuri herkku',8,4,7);
INSERT INTO resepti VALUES ('Rommikakku','Hyytelökakku, ei tarvi paistaa',6,5,7);
INSERT INTO resepti VALUES ('Kauralastut','Helppo ja nopea',4,6,7);


-- lisätty myös kirjautumista varten taulu
CREATE TABLE User (
Tunnus VARCHAR(100) PRIMARY KEY,
Salasana VARCHAR(1024) NOT NULL
);

INSERT INTO User VALUES ('foo','bar');
INSERT INTO User VALUES ('bar','foo');
INSERT INTO User VALUES ('opiskelija','ties4080');
