-- user table for users.
CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY,
    password TEXT UNIQUE NOT NULL,
    uuid TEXT UNIQUE NOT NULL,
    name TEXT UNIQUE NOT NULL
);

-- statistics table for users to log their consumption
CREATE TABLE IF NOT EXISTS Statistics (
    id INTEGER PRIMARY KEY,
    date DATETIME NOT NULL,
    user_id INT NOT NULL,
    quantity INT
);

-- preferences table for a user.
CREATE TABLE IF NOT EXISTS Preferences (
    id INTEGER PRIMARY KEY,
    color TEXT NOT NULL DEFAULT "#000000",
    user_id INT UNIQUE NOT NULL,
    size_id INT NOT NULL DEFAULT 1,
    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE,
    FOREIGN KEY(size_id) REFERENCES Sizes(id)
);

-- lookup table for sizes.
CREATE TABLE IF NOT EXISTS Sizes (
    id INTEGER PRIMARY KEY,
    size INT NOT NULL,
    unit TEXT DEFAULT "oz"
);

CREATE TABLE IF NOT EXISTS APIToken (
    id INTEGER PRIMARY KEY,
    token TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE
);

-- create default sizes for sizes lookup table.
INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); 

-- create default preferences.
INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2);

CREATE TRIGGER IF NOT EXISTS enforce_size_id
BEFORE INSERT ON Preferences
BEGIN
    SELECT
        CASE
            WHEN (
                SELECT COUNT(*) FROM Sizes WHERE id = new.size_id
            ) = 0
            THEN RAISE(ABORT, 'Size does not exist')
        END;
END;

-- 
CREATE VIEW IF NOT EXISTS aggregated_stats AS
    SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id;

CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS
SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color
FROM users
LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day')
LEFT JOIN preferences ON users.id = preferences.user_id
GROUP BY users.name;


CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS
  WITH DateSequence(Dates) AS
    (
     SELECT Date(CURRENT_DATE, '-7 day')
     UNION ALL
     SELECT Date(Dates, '+1 day')
     FROM DateSequence
     WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE)
    )
SELECT DateSequence.Dates as 'date',
  IFNULL(SUM(statistics.quantity), 0) AS 'total'
FROM DateSequence
LEFT JOIN statistics
ON Date(statistics.date) = DateSequence.Dates
GROUP BY DateSequence.Dates
ORDER BY DateSequence.Dates;