WG3pyZBQ

1DROP TABLE IF EXISTS pool_period;
2DROP TABLE IF EXISTS pool_balance_history;
3DROP TABLE IF EXISTS pool_transactions;
4DROP TABLE IF EXISTS pools;
5DROP TABLE IF EXISTS client_transactions;
6DROP TABLE IF EXISTS clients;
7DROP TABLE IF EXISTS users;
8
9CREATE TABLE IF NOT EXISTS users (
10  id SERIAL PRIMARY KEY,
11  name TEXT NOT NULL DEFAULT '',
12  email TEXT NOT NULL UNIQUE,
13  cellphone TEXT NOT NULL UNIQUE,
14  password TEXT NOT NULL,
15  created_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
16  updated_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP)
17);
18
19CREATE TABLE IF NOT EXISTS clients (
20  id SERIAL PRIMARY KEY,
21  name TEXT NOT NULL DEFAULT '',
22  cellphone TEXT NOT NULL UNIQUE,
23  email TEXT NOT NULL UNIQUE,
24  notes TEXT,
25  created_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
26  updated_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP)
27);
28
29-- transaction_type / deposit/withdrawal/stake/unstake
30CREATE TABLE IF NOT EXISTS client_transactions (
31  id SERIAL PRIMARY KEY,
32  client_id INTEGER NOT NULL,
33  client_transaction_type TEXT NOT NULL,
34  amount  DECIMAL(17,8) NOT NULL DEFAULT '0.00000000',
35  created_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
36  updated_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
37  FOREIGN KEY (client_id) REFERENCES clients (id)
38);
39
40CREATE TABLE IF NOT EXISTS pools (
41  id SERIAL PRIMARY KEY,
42  name TEXT NOT NULL DEFAULT '',
43  estimated_return DECIMAL(10,2) NOT NULL DEFAULT '0.00',
44  estimated_period_days INTEGER NOT NULL DEFAULT '7',
45  created_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
46  updated_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
47  deleted BOOLEAN NOT NULL DEFAULT FALSE,
48);
49
50-- pool_transaction_type / stake/unstake/interest
51CREATE TABLE IF NOT EXISTS pool_transactions (
52  id SERIAL PRIMARY KEY,
53  client_id INTEGER NOT NULL,
54  pool_id INTEGER NOT NULL,
55  pool_transaction_type TEXT NOT NULL,
56  amount  DECIMAL(17,8) NOT NULL DEFAULT '0.00000000',
57  created_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
58  updated_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
59  FOREIGN KEY (client_id) REFERENCES clients (id)
60  FOREIGN KEY (pool_id) REFERENCES pools (id)
61);
62
63-- balance_source / binance_api / manual
64CREATE TABLE IF NOT EXISTS pool_balance_history (
65  id SERIAL PRIMARY KEY,
66  pool_id INTEGER NOT NULL
67  balance DECIMAL(17,8) NOT NULL DEFAULT '0.00000000',
68  balance_timestamp TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
69  balance_source TEXT NOT NULL DEFAULT 'manual',
70  FOREIGN KEY (pool_id) REFERENCES pools (id)
71);
72
73CREATE TABLE IF NOT EXISTS pool_period (
74  id SERIAL PRIMARY KEY,
75  pool_id INTEGER NOT NULL
76  opened_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
77  closed_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
78  closing_amount DECIMAL(17,8) NOT NULL DEFAULT '0.00000000',
79  FOREIGN KEY (pool_id) REFERENCES pools (id)
80);