kE6CbmRj

1CREATE TABLE IF NOT EXISTS settings
2(
3    timezone            TEXT   NOT NULL,
4    service_percent     BIGINT NOT NULL,
5    admin_tg_login      BIGINT NOT NULL,
6    track_domain        TEXT   NOT NULL,
7    pay_domain          TEXT   NOT NULL,
8    service_balance     BIGINT NOT NULL,
9    sum_service_balance BIGINT NOT NULL,
10    service_status      BIGINT NOT NULL
11--     created_at   TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
12--     updated_at   TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
13);
14CREATE TABLE IF NOT EXISTS users
15(
16    id              SERIAL PRIMARY KEY,
17    tg_login        BIGINT NOT NULL,
18    service_percent BIGINT NOT NULL,
19    notifications   BIGINT NOT NULL,
20    timezone        TEXT   NOT NULL,
21    track_domain    TEXT   NOT NULL,
22    pay_domain      TEXT   NOT NULL,
23    balance         BIGINT NOT NULL,
24    wallet          BIGINT NOT NULL,
25    status          BIGINT NOT NULL,
26    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
27    updated_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
28);
29
30CREATE TYPE visit_type AS ENUM ('track', 'pay', 'unique');
31CREATE TABLE IF NOT EXISTS statistics
32(
33--     visitor_id BIGINT NOT NULL REFERENCES visitors (id),
34    visitor_id BIGINT NOT NULL,
35    type_visit visit_type,
36    created_at  TIMESTAMP WITH TIME ZONE                       DEFAULT CURRENT_TIMESTAMP,
37    updated_at  TIMESTAMP WITH TIME ZONE                       DEFAULT CURRENT_TIMESTAMP
38);
39
40CREATE TABLE IF NOT EXISTS offers
41(
42    id          SERIAL PRIMARY KEY,
43    category    BIGINT NOT NULL,
44    domain      TEXT   NOT NULL,
45    name        TEXT   NOT NULL,
46    price       BIGINT NOT NULL,
47    percent     BIGINT NOT NULL,
48    success_url TEXT   NOT NULL,
49    failed_url  TEXT   NOT NULL
50);
51
52CREATE TABLE IF NOT EXISTS visitors
53(
54    id          SERIAL PRIMARY KEY,
55    user_id     BIGINT NOT NULL REFERENCES users (id),
56    offer_id    BIGINT NOT NULL REFERENCES offers (id),
57    upsell_id  BIGINT NOT NULL DEFAULT 0,
58    sub_id      BIGINT NOT NULL DEFAULT 0,
59--     track_count BIGINT NOT NULL,
60--     pay_count   BIGINT NOT NULL,
61--     "unique"    BIGINT NOT NULL,
62    country     TEXT   NOT NULL,
63    fingerprint TEXT   NOT NULL,
64    created_at  TIMESTAMP WITH TIME ZONE                       DEFAULT CURRENT_TIMESTAMP,
65    updated_at  TIMESTAMP WITH TIME ZONE                       DEFAULT CURRENT_TIMESTAMP
66);
67
68CREATE TABLE IF NOT EXISTS payouts
69(
70    id         SERIAL PRIMARY KEY,
71    user_id    BIGINT NOT NULL REFERENCES users (id),
72    sum_payout BIGINT NOT NULL,
73    status     BIGINT NOT NULL,
74    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
75    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
76);
77
78
79CREATE TABLE IF NOT EXISTS upsells
80(
81    id            SERIAL PRIMARY KEY,
82    offer_id      BIGINT NOT NULL REFERENCES offers (id),
83    upsell_number SERIAL,
84    name          TEXT   NOT NULL,
85    price         BIGINT NOT NULL,
86    percent       BIGINT NOT NULL,
87    success_url   TEXT   NOT NULL,
88    failed_url    TEXT   NOT NULL
89);
90-- добавил пока как тест
91CREATE TABLE IF NOT EXISTS offers_partners
92(
93    id           SERIAL PRIMARY KEY,
94    user_id      BIGINT NOT NULL REFERENCES users (id),
95    offer_id     BIGINT NOT NULL REFERENCES offers (id),
96    upsell_id    BIGINT NOT NULL DEFAULT 0,
97    sub_id       BIGINT NOT NULL DEFAULT 0,
98    partner_link TEXT   NOT NULL
99);
100
101-- добавил пока как тест
102CREATE TABLE IF NOT EXISTS offers_sub_ids
103(
104    id                 SERIAL PRIMARY KEY,
105    user_id            BIGINT NOT NULL REFERENCES users (id),
106    offer_id           BIGINT NOT NULL REFERENCES offers (id),
107    name               TEXT   NOT NULL,
108    subid_partner_link TEXT   NOT NULL
109
110);
111
112
113CREATE TABLE IF NOT EXISTS orders
114(
115    id         BIGSERIAL PRIMARY KEY,
116    user_id    BIGINT NOT NULL REFERENCES users (id),
117    offer_id   BIGINT NOT NULL REFERENCES offers (id),
118    upsell_id  BIGINT NOT NULL REFERENCES upsells (id),
119    visitor_id BIGINT NOT NULL REFERENCES visitors (id),
120    price      BIGINT NOT NULL,
121    status     BIGINT NOT NULL,
122    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
123    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
124);