create-db.sql

PRAGMA FOREIGN_KEYS = ON; -- do this for all database connections

-- ============================== Tables =================================

CREATE TABLE products ( -- products available
    id INTEGER PRIMARY KEY NOT NULL,
    sorting_id INTEGER DEFAULT 0, -- for ui sorting
    buy_id INTEGER, -- Systembolagets id, om behov skulle finnas
    bar_code TEXT UNIQUE, -- för framtiden
    name TEXT DEFAULT "CHANGE ME" NOT NULL,
    price INTEGER DEFAULT 0 NOT NULL,
    sale_status INTEGER DEFAULT 1
    -- -1: system, not currently used
    -- 0: For Sale
    -- 1: Not For Sale
    -- 2: Hidden
);

CREATE TABLE stock ( -- how much we have of each product
    product_id INTEGER PRIMARY KEY NOT NULL,
    active INTEGER DEFAULT 0,
    -- extra storage places can be added here
    -- example: (TODO actually remove this column, but check the front end first)
    storage INTEGER DEFAULT 0,

    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE log (
    id INTEGER PRIMARY KEY NOT NULL,
    product_id INTEGER NOT NULL,
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    price INTEGER NOT NULL,

    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE money ( -- variable table
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT UNIQUE,
    amount INTEGER DEFAULT 0
);

CREATE TABLE money_diffs (
    id INTEGER PRIMARY KEY NOT NULL,
    expected INTEGER NOT NULL,
    actual INTEGER NOT NULL,
    account INTEGER NOT NULL DEFAULT 0,
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

    FOREIGN KEY (account) REFERENCES money(id)
);

CREATE TABLE money_transfers (
    id INTEGER PRIMARY KEY NOT NULL,
    change INTEGER NOT NULL,
    -- the two accounts can be set to 0 for the "outside world"
    from_acc INTEGER NOT NULL REFERENCES money(id),
    to_acc INTEGER NOT NULL REFERENCES money(id),
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    note TEXT
);

CREATE TABLE big_buy ( -- table for temporary storage of current items to buy
    id INTEGER PRIMARY KEY NOT NULL,
    product_id INTEGER NOT NULL,

    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- för inköp från leverantör
-- Alla objekt vid ett inköpstillfälle kommer att ha samma 'time'
--     Och formar då ett "kvitto"
CREATE TABLE acquisitions (
    id INTEGER PRIMARY KEY NOT NULL,
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    product_id INTEGER NOT NULL,
    item_price INTEGER DEFAULT 0, -- price of each unit
    amount INTEGER DEFAULT 0,

    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- temp table used in frontend
CREATE TABLE acquisitions_temp (
    id INTEGER PRIMARY KEY NOT NULL,
    product_id INTEGER UNIQUE NOT NULL,
    amount INTEGER DEFAULT 0,
    price INTEGER DEFAULT 0,
    total_price INTEGER DEFAULT 0,

    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE stock_diff (
    id INTEGER PRIMARY KEY NOT NULL,
    product_id INTEGER NOT NULL,
    expected INTEGER NOT NULL DEFAULT 0,
    actual INTEGER NOT NULL DEFAULT 0,
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE diff_help (
    id INTEGER PRIMARY KEY NOT NULL,
    product_id INTEGER NOT NULL,
    amount INTEGER,

    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE drainage (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    end_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    decrease INTEGER DEFAULT 2 NOT NULL,
    how_often_minutes INTEGER DEFAULT 60 NOT NULL,
    min_price INTEGER DEFAULT 5 NOT NULL
);

CREATE TABLE my_db_settings (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    value
);

-- ============================== Views ==================================

CREATE VIEW drainage_change AS -- how prices have changed due to the drainage, or null
SELECT min_price,
        ( (strftime('%s', 'now') - strftime('%s', start_time))
            / (60 * how_often_minutes)
        ) * decrease AS change
FROM drainage
WHERE CURRENT_TIMESTAMP BETWEEN start_time AND end_time;

-- The same layout as products, but with drainage prices when those are applicable
CREATE VIEW current_products AS
SELECT p.id,
       p.sorting_id,
       p.buy_id,
       p.bar_code,
       p.name,
       ifnull(MIN(p.price, MAX(d.min_price, p.price - d.change)), p.price) AS price,
       p.sale_status
FROM products p LEFT JOIN drainage_change d;

-- this is a view because the change labels also use it
CREATE VIEW full_big_buy AS
SELECT p.id AS product_id,
       b.id AS buy_id,
       count(b.product_id) AS amount,
       p.name,
       p.price,
       count(b.product_id) * p.price AS total
FROM big_buy b
LEFT JOIN current_products p
   ON b.product_id = p.id
GROUP BY p.id;

-- this could be replaced with view delegates in the GUI
CREATE VIEW display_big_buy AS
SELECT product_id,
       buy_id,
       amount,
       name,
       price / 100 AS price_kr,
       total / 100 AS total_kr
FROM full_big_buy;

-- this was needed to have price in crown without delegate in product list
-- also, possibly rename this to product_listings
CREATE VIEW product_list AS -- list of products for sale
SELECT p.id, p.bar_code, p.sorting_id, p.name, p.price / 100 AS price, s.active
FROM current_products p
LEFT JOIN stock s ON p.id = s.product_id
WHERE p.sale_status = 0
GROUP BY p.id;

-- views make requerying data so much easier!
CREATE VIEW total_log AS
SELECT count(products.name) AS amount, products.name
FROM log INNER JOIN products
ON log.product_id = products.id
WHERE datetime(log.time, 'localtime') BETWEEN
    datetime('now', 'localtime', '-12 hours', 'start of day', '+12 hours')
AND datetime('now', 'localtime', '-12 hours', 'start of day', '+36 hours')
GROUP BY products.id
ORDER BY products.name;

CREATE VIEW transaction_log AS
SELECT log.id,
        products.name,
        log.price / 100 AS price,
        datetime(log.time, 'localtime') AS sold_time
FROM log INNER JOIN products
ON log.product_id = products.id
WHERE datetime(log.time, 'localtime') BETWEEN
    datetime('now', 'localtime', '-12 hours', 'start of day', '+12 hours')
AND datetime('now', 'localtime', '-12 hours', 'start of day', '+36 hours')
ORDER BY log.time;

CREATE VIEW diff_view AS
SELECT d.id AS id,
       p.id AS product_id,
       p.sale_status AS sale_status,
       -- visible border
       p.name AS name,
       s.active AS expected,
       d.amount AS actual,
       d.amount - s.active AS diff
FROM products p
INNER JOIN stock s ON p.id = s.product_id
INNER JOIN diff_help d ON p.id = d.product_id;
-- WHERE p.sale_status = 0 OR s.active != 0;
-- where p.sale_status in (0, 1);

-- ============================== Triggers ===============================

-- some of these would benefit from being FOR EACH STATEMENT instead of
-- FOR EACH ROW, but sqlite doesn't support that yet.

-- Delete this to enable optional stock
-- Do note that the ui is not really set up for it (yet)
CREATE TRIGGER add_new_stock
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO stock (product_id) VALUES ((SELECT NEW.id));
END;

CREATE TRIGGER before_acquisitions_transfer
BEFORE DELETE ON acquisitions_temp
FOR EACH ROW
WHEN (SELECT value = 1 FROM my_db_settings WHERE name = 'acquisitions_temp_transfer')
BEGIN
    INSERT INTO acquisitions (product_id, item_price, amount)
    SELECT OLD.product_id, OLD.price, OLD.amount;

    UPDATE stock SET active = active + OLD.amount
    WHERE product_id = OLD.product_id;

    UPDATE money SET amount = amount - (OLD.price * OLD.amount)
    WHERE name = "Cash Drawer";
END;

/* This is already done in the before trigger
CREATE TRIGGER after_acquisitions_transfer
AFTER DELETE ON acquisitions_temp
FOR EACH ROW
WHEN (SELECT value = 1 FROM my_db_settings WHERE name = 'acquisitions_temp_transfer')
BEGIN
    UPDATE money SET amount = amount - OLD.price * OLD.amount
    WHERE name = "Cash Drawer";
END;
*/

CREATE TRIGGER after_log_del
AFTER DELETE ON log
FOR EACH ROW
BEGIN
    UPDATE money SET amount = amount - OLD.price WHERE name = 'Cash Drawer';

    UPDATE stock SET active = active + 1 WHERE product_id = OLD.product_id;
END;

CREATE TRIGGER after_log_add
AFTER INSERT ON log
FOR EACH ROW
BEGIN
    UPDATE money SET amount = amount + NEW.price WHERE name = 'Cash Drawer';

    UPDATE stock SET active = active - 1 WHERE product_id = NEW.product_id;
END;

CREATE TRIGGER after_big_buy_delete
AFTER DELETE ON big_buy
FOR EACH ROW
WHEN (SELECT value = 1 FROM my_db_settings WHERE name = 'big_buy_transfer')
BEGIN
    INSERT INTO log (product_id, price)
    SELECT id, price FROM current_products WHERE id = OLD.product_id;
END;

/*
 * delete from the view to transfer,
 * delete from the underlying table (diff_help) if
 * a transfer is not desired.
 */

CREATE TRIGGER after_diff_help_delete_last
AFTER DELETE ON diff_help
FOR EACH ROW
WHEN (SELECT count (1) = 0 FROM diff_help)
BEGIN
    INSERT INTO diff_help (product_id)
    SELECT id FROM products p;
END;

CREATE TRIGGER diff_view_transfer
INSTEAD OF DELETE ON diff_view
BEGIN
    UPDATE stock SET active = OLD.actual
    WHERE OLD.actual IS NOT NULL
      AND product_id = OLD.product_id;

    INSERT INTO stock_diff (product_id, expected, actual)
    SELECT OLD.product_id, OLD.expected, OLD.actual
     WHERE OLD.actual IS NOT NULL;

    DELETE FROM diff_help WHERE id = OLD.id;
END;

CREATE TRIGGER update_view_trigger
INSTEAD OF UPDATE ON diff_view
BEGIN
    UPDATE diff_help SET amount = NEW.actual WHERE id = NEW.id;
END;

CREATE TRIGGER before_money_transfer
BEFORE INSERT ON money_transfers
FOR EACH ROW
BEGIN
    UPDATE money SET amount = amount - NEW.change WHERE id = NEW.from_acc;
    UPDATE money SET amount = amount + NEW.change WHERE id = NEW.to_acc;
END;

CREATE TRIGGER before_money_diff
BEFORE INSERT ON money_diffs
FOR EACH ROW
BEGIN
    UPDATE money SET amount = NEW.actual WHERE id = NEW.account;
END;

CREATE TRIGGER drainage_constraint
BEFORE INSERT ON drainage
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, "Drainages can't overlap")
    WHERE (SELECT SUM(NEW.start_time BETWEEN start_time AND end_time
                   OR NEW.end_time   BETWEEN start_time AND end_time)
               FROM drainage);

    SELECT RAISE(ABORT, "New drainages can't end in the past")
    WHERE (SELECT NEW.end_time < CURRENT_TIMESTAMP);

    SELECT RAISE(ABORT, "Drainages need to have a positive time")
    WHERE (SELECT NEW.start_time > NEW.end_time);
END;

-- ============================== Default Queries =======================

INSERT INTO money (id, name, amount)
VALUES (0, "no account", NULL);

INSERT INTO money (name)
VALUES ("Cash Drawer"), ("Vault");

INSERT INTO my_db_settings (name, value)
VALUES ("big_buy_transfer", 1),
       ("acquisitions_temp_transfer", 1);

-- This is mostly so that diff_help have something to insert
INSERT INTO products (name)
VALUES ("Default Product");

-- diff_help gets it's data when the last record is deleted,
-- so insert *any* data and delete it.

INSERT INTO diff_help (product_id)
SELECT id FROM products LIMIT 1;
DELETE FROM diff_help;