100 lines
3.8 KiB
SQL
100 lines
3.8 KiB
SQL
CREATE DATABASE IF NOT EXISTS argent_shard_01
|
|
CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
|
|
USE argent_shard_01;
|
|
|
|
CREATE TABLE ledger_statuses (
|
|
status_id TINYINT PRIMARY KEY,
|
|
name VARCHAR(20) UNIQUE NOT NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE ledger_entry_types (
|
|
type_id TINYINT PRIMARY KEY,
|
|
name VARCHAR(20) UNIQUE NOT NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE ledger (
|
|
entry_id BINARY(16) NOT NULL,
|
|
batch_id BINARY(16) NOT NULL,
|
|
user_id BINARY(16) NOT NULL,
|
|
group_id BINARY(16) DEFAULT NULL,
|
|
amount DECIMAL(15, 2) NOT NULL,
|
|
currency_code CHAR(3) NOT NULL,
|
|
description VARCHAR(255),
|
|
status_id TINYINT DEFAULT 1,
|
|
type_id TINYINT NOT NULL,
|
|
created_at DATETIME NOT NULL,
|
|
PRIMARY KEY (user_id, entry_id),
|
|
FOREIGN KEY (status_id) REFERENCES ledger_statuses(status_id),
|
|
FOREIGN KEY (type_id) REFERENCES ledger_entry_types(type_id),
|
|
INDEX (batch_id),
|
|
INDEX (group_id),
|
|
INDEX (status_id)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE user_balances (
|
|
user_id BINARY(16) NOT NULL,
|
|
currency_code CHAR(3) NOT NULL,
|
|
total_proposed DECIMAL(15, 2) DEFAULT 0.00,
|
|
total_ratified DECIMAL(15, 2) DEFAULT 0.00,
|
|
total_disputed DECIMAL(15, 2) DEFAULT 0.00,
|
|
total_settled DECIMAL(15, 2) DEFAULT 0.00,
|
|
total_spent DECIMAL(15, 2) DEFAULT 0.00,
|
|
PRIMARY KEY (user_id, currency_code)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE group_user_balances (
|
|
group_id BINARY(16) NOT NULL,
|
|
user_id BINARY(16) NOT NULL,
|
|
currency_code CHAR(3) NOT NULL,
|
|
net_balance DECIMAL(15, 2) DEFAULT 0.00,
|
|
total_contributed DECIMAL(15, 2) DEFAULT 0.00,
|
|
PRIMARY KEY (group_id, user_id, currency_code),
|
|
INDEX (user_id)
|
|
) ENGINE=InnoDB;
|
|
|
|
INSERT INTO ledger_statuses (status_id, name) VALUES (1, 'proposed'), (2, 'ratified'), (3, 'disputed'), (4, 'settled');
|
|
INSERT INTO ledger_entry_types (type_id, name) VALUES (1, 'expense'), (2, 'settlement');
|
|
|
|
DELIMITER //
|
|
CREATE PROCEDURE sync_all_balances(
|
|
IN p_uid BINARY(16),
|
|
IN p_gid BINARY(16),
|
|
IN p_curr CHAR(3),
|
|
IN p_amt DECIMAL(15,2),
|
|
IN p_status TINYINT,
|
|
IN p_type TINYINT,
|
|
IN p_mod INT
|
|
)
|
|
BEGIN
|
|
INSERT INTO user_balances (user_id, currency_code) VALUES (p_uid, p_curr)
|
|
ON DUPLICATE KEY UPDATE user_id = user_id;
|
|
UPDATE user_balances SET
|
|
total_proposed = total_proposed + IF(p_status = 1, p_amt * p_mod, 0),
|
|
total_ratified = total_ratified + IF(p_status = 2, p_amt * p_mod, 0),
|
|
total_disputed = total_disputed + IF(p_status = 3, p_amt * p_mod, 0),
|
|
total_settled = total_settled + IF(p_status = 4, p_amt * p_mod, 0),
|
|
total_spent = total_spent + IF(p_type = 1 AND p_amt > 0, p_amt * p_mod, 0)
|
|
WHERE user_id = p_uid AND currency_code = p_curr;
|
|
IF p_gid IS NOT NULL THEN
|
|
INSERT INTO group_user_balances (group_id, user_id, currency_code, net_balance, total_contributed)
|
|
VALUES (p_gid, p_uid, p_curr, p_amt * p_mod, IF(p_type = 1 AND p_amt > 0, p_amt * p_mod, 0))
|
|
ON DUPLICATE KEY UPDATE
|
|
net_balance = net_balance + (p_amt * p_mod),
|
|
total_contributed = total_contributed + IF(p_type = 1 AND p_amt > 0, p_amt * p_mod, 0);
|
|
END IF;
|
|
END //
|
|
CREATE TRIGGER after_ledger_insert AFTER INSERT ON ledger FOR EACH ROW
|
|
BEGIN
|
|
CALL sync_all_balances(NEW.user_id, NEW.group_id, NEW.currency_code, NEW.amount, NEW.status_id, NEW.type_id, 1);
|
|
END //
|
|
CREATE TRIGGER after_ledger_update AFTER UPDATE ON ledger FOR EACH ROW
|
|
BEGIN
|
|
CALL sync_all_balances(OLD.user_id, OLD.group_id, OLD.currency_code, OLD.amount, OLD.status_id, OLD.type_id, -1);
|
|
CALL sync_all_balances(NEW.user_id, NEW.group_id, NEW.currency_code, NEW.amount, NEW.status_id, NEW.type_id, 1);
|
|
END //
|
|
CREATE TRIGGER after_ledger_delete AFTER DELETE ON ledger FOR EACH ROW
|
|
BEGIN
|
|
CALL sync_all_balances(OLD.user_id, OLD.group_id, OLD.currency_code, OLD.amount, OLD.status_id, OLD.type_id, -1);
|
|
END //
|
|
DELIMITER ;
|