PRAGMA FOREIGN_KEYS = OFF;

CREATE TABLE money_account_type (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT UNQIUE NOT NULL
);

INSERT OR IGNORE INTO money_account_type (name) VALUES
('system'),
('trusted'),
('other');


DROP TRIGGER add_new_stock;
DROP TRIGGER before_acquisitions_transfer;
DROP TRIGGER after_log_del;
DROP TRIGGER after_log_add;
DROP TRIGGER before_money_transfer;
DROP TRIGGER before_money_diff;
DROP TRIGGER propagate_products_with_stock;

DROP TABLE stock;
DROP TABLE money;

-- DELETE FROM my_db_settings WHERE name = 'acquisitions_temp_transfer';
DELETE FROM my_db_settings WHERE name = 'active_money_account';

--------------------------------------------------

CREATE TABLE money (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT UNIQUE,
    type INTEGER NOT NULL
);

INSERT INTO money (id, name, type) VALUES
(0, 'no account', 1),
(1, 'Cash Drawer', 1),
(2, 'Vault', 1),
(3, 'Skuld', 1);

CREATE TRIGGER initialize_money_account
AFTER INSERT ON money
FOR EACH ROW
    BEGIN
        INSERT INTO money_transfers (change, from_acc, to_acc, note)
        VALUES (0, NEW.id, NEW.id, 'Initialize account');
    END;

--------------------------------------------------

CREATE VIEW full_money_log AS
SELECT time AS time, -(item_price*amount) AS change, account AS account
FROM acquisitions
UNION ALL
SELECT time, price, account FROM log
UNION ALL
SELECT time, - change, from_acc FROM money_transfers
UNION ALL
SELECT time, change, to_acc FROM money_transfers
UNION ALL
SELECT time, actual - expected, account FROM money_diffs
ORDER BY time ASC;

CREATE VIEW account_balances AS
SELECT account AS account_id
     , SUM(change) AS total
FROM full_money_log
GROUP BY account;


DROP VIEW product_list;
CREATE VIEW product_list AS
SELECT p.id, p.bar_code, p.sorting_id, p.name, p.price / 100 as price, s.total
FROM current_products p
LEFT JOIN stock_balance s ON p.id = s.product_id
WHERE p.sale_status = 0;

--------------------------------------------------

CREATE VIEW full_stock_log AS
SELECT time AS time, product_id AS product_id, amount AS amount
FROM acquisitions
UNION ALL
SELECT time, product_id, -1 FROM log
UNION ALL
SELECT time, product_id, actual - expected FROM stock_diff
ORDER BY time ASC;

CREATE VIEW stock_balance AS
SELECT SUM(amount) AS total
, product_id
FROM full_stock_log
GROUP BY product_id;

DROP VIEW money_transfers_simple;
CREATE VIEW money_transfers_simple AS
SELECT
    t.time AS time,
    m.name AS from_acc,
    n.name AS to_acc,
    printf('%.2f', t.change / 100.0) AS amount,
    t.note AS note
FROM money_transfers t
LEFT JOIN money m ON t.from_acc = m.id
LEFT JOIN money n ON t.to_acc = n.id
ORDER by time DESC;


PRAGMA FOREIGN_KEYS = ON;