CREATE TYPE enum_type AS ENUM ('vip', 'normal');
CREATE TYPE enum_role AS ENUM ('admin', 'user');

CREATE TABLE IF NOT EXISTS customers (
    id          SERIAL PRIMARY KEY,
    type_user   enum_type DEFAULT 'normal',
    role_user   enum_role DEFAULT 'user',
    fullname    TEXT NOT NULL,
    username    TEXT NOT NULL,
    password    TEXT NOT NULL,
    email       TEXT NOT NULL,
    phone       VARCHAR(20) NOT NULL
);

CREATE TABLE IF NOT EXISTS payment_gateway (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS gateway (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS rules (
   id SERIAL PRIMARY KEY,
   gateway_id INT references gateway(id),
   regex TEXT NOT NULL,
   disable BOOLEAN DEFAULT true NOT NULL
);

CREATE TYPE enum_state AS ENUM ('new', 'pending', 'timeout', 'confirmed');
CREATE TYPE enum_type_tx AS ENUM ('normal', 'coupon', 'credit');

CREATE TABLE IF NOT EXISTS tx (
    id SERIAL PRIMARY KEY,
    user_id     INT references customers(id),
    method_id   INT references payment_gateway(id),
    gateway_id  INT references gateway(id),
    tx_state    enum_state DEFAULT 'new',
    real_money  money NOT NULL,
    expect_money money NOT NULL,
    account     VARCHAR(26),
    create_tx   TIMESTAMP NOT NULL,
    sms_receipt TEXT,
    transaction_code TEXT,
    end_time    TIMESTAMP,
    cashout_id  INT references customers(id),
    type_tx     enum_type_tx DEFAULT 'normal',
    cashout_id  INT
);

CREATE TYPE e_state_coupon AS ENUM ('spend', 'unspend');
CREATE TABLE IF NOT EXISTS coupon (
    id              SERIAL PRIMARY KEY,
    tx_id           INT references tx(id),
    user_onwer_id   INT references customers(id),
    state_coupon    e_state_coupon DEFAULT 'unspend',
    create_at       TIMESTAMP,
    value_coupon    MONEY NOT NULL
);