-- Solution proposal by @zeornelas on github
-- Revised by Gonçalo Gonçalves

PRAGMA foreign_keys = ON;
.headers on
.mode columns


CREATE TABLE Person (
    id INTEGER PRIMARY KEY,
    name TEXT,
    address TEXT
);

CREATE TABLE Waiter (
    id INTEGER PRIMARY KEY REFERENCES Person,
    identifier INTEGER NOT NULL UNIQUE,
    phone_number TEXT
);

CREATE TABLE Client (
    id INTEGER PRIMARY KEY REFERENCES Person,
    tax_id INTEGER NOT NULL UNIQUE
);

CREATE TABLE Meal (
    id INTEGER PRIMARY KEY,
    date TEXT NOT NULL,
    start_time TEXT NOT NULL,
    end_time TEXT,
    client INTEGER REFERENCES Client,
    waiter INTEGER NOT NULL REFERENCES Waiter,
    table_ INTEGER NOT NULL REFERENCES Table_,
    CHECK(end_time IS NULL OR strftime('%s', end_time) > strftime('%s', start_time))
);

CREATE TABLE Table_ (
    identifier INTEGER PRIMARY KEY,
    capacity INTEGER NOT NULL CHECK(capacity > 0)
);

CREATE TABLE Dish (
    identifier INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE Ingredient (
    name TEXT PRIMARY KEY,
    unit TEXT,
    stock INTEGER NOT NULL CHECK(stock >= 0)
);

CREATE TABLE IngredientDish (
    dish INTEGER REFERENCES Dish, -- DEFERRABLE INITIALLY DEFERRED,
    ingredient TEXT REFERENCES Ingredient,
    quantity INTEGER NOT NULL CHECK(quantity > 0),
    PRIMARY KEY (dish, ingredient)
);

CREATE TABLE DishMeal (
    dish INTEGER REFERENCES Dish,
    meal INTEGER REFERENCES Meal,
    quantity INTEGER NOT NULL CHECK(quantity > 0),
    PRIMARY KEY (dish, meal)
);

-- Before insert trigger that guarantees that a dish has at least one ingredient (enforces 1..*)
-- CREATE TRIGGER validate_dish_ingredients 
-- BEFORE INSERT ON Dish
-- WHEN NOT EXISTS (SELECT * FROM IngredientDish WHERE dish = NEW.id) 
-- BEGIN
--     SELECT RAISE (ABORT,'Dish needs at least one ingridient');
-- END;

-- After delete trigger that guarantees that a dish is deleted when it's last ingredient is deleted
-- CREATE TRIGGER delete_dish_after_deleting_last_ingredient
-- AFTER DELETE ON IngredientDish
-- WHEN (SELECT COUNT(*) FROM IngredientDish WHERE dish = OLD.dish) = 0
-- BEGIN
--     DELETE FROM Dish WHERE id = OLD.dish;
-- END;

-- -- Example
-- INSERT INTO Ingredient VALUES ('rice', 'grams', 25);
-- INSERT INTO Ingredient VALUES ('duck', 'grams', 500);
-- -- We need to wrap everything in a transaction in order for the 'DEFERRABLE INITIALLY DEFERRED' to be executed
-- -- We start by inserting the elements in the IngredientDish table and after that we create the dish
-- -- The transaction and 'DEFERRABLE INITIALLY DEFERRED' property allow us to avoid the foreign key rules.
-- BEGIN;
-- INSERT INTO IngredientDish VALUES (1, 'rice', 5);
-- INSERT INTO IngredientDish VALUES (1, 'duck', 100);
-- INSERT INTO Dish VALUES (1, 'Duck rice');
-- COMMIT;
