ex2W7UL6

1CREATE TYPE enum_type AS ENUM ('vip', 'normal');
2CREATE TYPE enum_role AS ENUM ('admin', 'user');
3
4CREATE TABLE IF NOT EXISTS customers (
5    id          SERIAL PRIMARY KEY,
6    type_user   enum_type DEFAULT 'normal',
7    role_user   enum_role DEFAULT 'user',
8    fullname    TEXT NOT NULL,
9    username    TEXT NOT NULL,
10    password    TEXT NOT NULL,
11    email       TEXT NOT NULL,
12    phone       VARCHAR(20) NOT NULL
13);
14
15CREATE TABLE IF NOT EXISTS payment_gateway (
16    id SERIAL PRIMARY KEY,
17    name VARCHAR(50) NOT NULL
18);
19
20CREATE TABLE IF NOT EXISTS gateway (
21    id SERIAL PRIMARY KEY,
22    name VARCHAR(50) NOT NULL
23);
24
25CREATE TABLE IF NOT EXISTS rules (
26   id SERIAL PRIMARY KEY,
27   gateway_id INT references gateway(id),
28   regex TEXT NOT NULL,
29   disable BOOLEAN DEFAULT true NOT NULL
30);
31
32CREATE TYPE enum_state AS ENUM ('new', 'pending', 'timeout', 'confirmed');
33CREATE TYPE enum_type_tx AS ENUM ('normal', 'coupon', 'credit');
34
35CREATE TABLE IF NOT EXISTS tx (
36    id SERIAL PRIMARY KEY,
37    user_id     INT references customers(id),
38    method_id   INT references payment_gateway(id),
39    gateway_id  INT references gateway(id),
40    tx_state    enum_state DEFAULT 'new',
41    real_money  money NOT NULL,
42    expect_money money NOT NULL,
43    account     VARCHAR(26),
44    create_tx   TIMESTAMP NOT NULL,
45    sms_receipt TEXT,
46    transaction_code TEXT,
47    end_time    TIMESTAMP,
48    cashout_id  INT references customers(id),
49    type_tx     enum_type_tx DEFAULT 'normal',
50    cashout_id  INT
51);
52
53CREATE TYPE e_state_coupon AS ENUM ('spend', 'unspend');
54CREATE TABLE IF NOT EXISTS coupon (
55    id              SERIAL PRIMARY KEY,
56    tx_id           INT references tx(id),
57    user_onwer_id   INT references customers(id),
58    state_coupon    e_state_coupon DEFAULT 'unspend',
59    create_at       TIMESTAMP,
60    value_coupon    MONEY NOT NULL
61);