DROP TABLE resepti
;
DROP TABLE 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,
ReseptiID INTEGER PRIMARY KEY AUTOINCREMENT,
RuokalajiID INTEGER NOT NULL,
CONSTRAINT Resepti_RuokalajiID 
	FOREIGN KEY (RuokalajiID)
	REFERENCES Ruokalaji (RuokalajiID)
)
;

-- Koska SQLite ei toteuta viite-eheyksiä toteutetaan ne itse
-- triggereiden avulla
CREATE TRIGGER fki_resepti_ruokalaji_id
BEFORE INSERT ON resepti
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT ruokalajiid FROM ruokalaji WHERE ruokalajiid = NEW.ruokalajiId) IS NULL)
     THEN RAISE(ABORT, 'insert on table "resepti" violates foreign key constraint "fk_resepti_ruokalaji_id"')
  END;
END;

CREATE TRIGGER fku_resepti_ruokalaji_id
BEFORE UPDATE ON resepti
FOR EACH ROW BEGIN 
   SELECT CASE
     WHEN ((SELECT ruokalajiid FROM ruokalaji WHERE ruokalajiid = NEW.ruokalajiId) IS NULL)
     THEN RAISE(ABORT, 'update on table "resepti" violates foreign key constraint "fk_resepti_ruokalaji_id"')
  END;
END;

CREATE TRIGGER fkd_resepti_ruokalaji_id
BEFORE DELETE ON ruokalaji
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT ruokalajiid FROM resepti WHERE ruokalajiid = OLD.ruokalajiId) IS NOT NULL)
     THEN RAISE(ABORT, 'delete on table "ruokalaji" violates foreign key constraint "fk_resepti_ruokalaji_id"')
  END;
END;


INSERT INTO ruokalaji (nimi, kuvaus) VALUES ('Alkuruoka','-');
INSERT INTO ruokalaji (nimi, kuvaus) VALUES ('Pääruoka','-');
INSERT INTO ruokalaji (nimi, kuvaus) VALUES ('Lisäkeruoka','-');
INSERT INTO ruokalaji (nimi, kuvaus) VALUES ('Väli - tai iltapala','-');
INSERT INTO ruokalaji (nimi, kuvaus) VALUES ('Jälkiruoka','-');
INSERT INTO ruokalaji (nimi, kuvaus) VALUES ('Suolainen leivonnainen','-');
INSERT INTO ruokalaji (nimi, kuvaus) VALUES ('Makea leivonnainen','-');

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);

delete from ruokalaji;