6P4vD7zf

1Drop TABLE if EXISTS reviews CASCADE;
2Drop TABLE if EXISTS payment_methods CASCADE;
3Drop TABLE if EXISTS shipping_methods CASCADE;
4Drop TABLE if EXISTS orders CASCADE;
5Drop TABLE if EXISTS receipts CASCADE;
6Drop Table if EXISTS customers CASCADE;
7Drop TABLE if EXISTS products CASCADE;
8Drop TABLE if EXISTS categories CASCADE;
9Drop TABLE if EXISTS admins CASCADE;
10
11
12-- New tables
13
14create TABLE customers
15(
16    id         serial PRIMARY key,
17    first_name varchar(128),
18    last_name  varchar(128),
19    email      varchar(128) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[A-Za-z]+$'
20) ,
21    password_hash   varchar(256) ,
22    salt varchar(256) CHECK((password_hash is not null and salt is null) = FALSE),
23    gender     varchar(32) CHECK (gender in ('male', 'female', 'other', 'not specified')) DEFAULT 'not specified',
24    status     varchar(32) CHECK (status in ('active', 'inactive', 'pending')) DEFAULT 'active',
25    created_date TIMESTAMP WITH time zone default current_timestamp
26);
27CREATE
28UNIQUE INDEX on customers(email);
29
30
31create table admins
32(
33    id    serial PRIMARY key,
34    email varchar(128) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[A-Za-z]+$'
35) ,
36    name          varchar(128),
37    password_hash varchar(256),
38    salt          varchar(256) CHECK ((password_hash is not null and salt is null) = FALSE)
39);
40
41
42create table shipping_methods
43(
44    id          serial PRIMARY key,
45    name        varchar(128) not null,
46    description text,
47    price_type  text CHECK ( price_type in ('flat', 'percent')),
48    price       int CHECK (price > 0)
49);
50
51create table payment_methods
52(
53    id                   serial PRIMARY key,
54    name                 varchar(128) not NULL,
55    description          TEXT,
56    price_increase_type  text CHECK ( price_increase_type in ('flat', 'percent')),
57    price_decrease_type  text CHECK ( price_decrease_type in ('flat', 'percent')),
58    price_increase_value int CHECK (price_increase_value > 0),
59    price_decrease_value int check (price_decrease_value > 0)
60);
61
62
63CREATE TABLE categories
64(
65    id              serial PRIMARY KEY,
66    name            varchar(128) NOT NULL,
67    description     VARCHAR,
68    image_link      VARCHAR,
69    parent_category integer,
70    foreign key (parent_category) REFERENCES categories (id)
71);
72
73
74CREATE TABLE products
75(
76    id               serial       NOT NULL PRIMARY KEY,
77    name             varchar(128) NOT NULL,
78    description      TEXT,
79    full_description TEXT,
80    price            float        NOT NULL CHECK (price >= 0),
81    current_stock    int          NOT NULL CHECK (current_stock >= 0),
82    bought           int          NOT NULL CHECK (bought >= 0) DEFAULT 0,
83    big_image_link   TEXT,
84    image_links      TEXT[],
85    category_id      integer      NOT NULL,
86    created_date     TIMESTAMP WITH TIME ZONE                  DEFAULT CURRENT_TIMESTAMP,
87    available        boolean      NOT NULL                     DEFAULT TRUE,
88    rating           NUMERIC                                   DEFAULT 0 CHECK (rating >= 0 and rating <= 5),
89    total_rated      integer      NOT NULL                     default 0,
90    address          varchar(256),
91    color_options    varchar(256),
92    size_options     varchar(256),
93    other_options    varchar(256),
94    CONSTRAINT valid_category_id foreign key (category_id) REFERENCES categories (id) ON DELETE CASCADE
95);
96create
97index on products(category_id);
98
99
100create table receipts
101(
102    id                 serial PRIMARY key,
103    order_date         TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP,
104    customer_id        integer NOT NULL,
105    status             varchar(60)              DEFAULT 'created' CHECK (status in ('created', 'pending', 'cancelled', 'confirmed', 'delivered')),
106    billing_first_name varchar(60),
107    billing_last_name  varchar(60),
108    company_name       varchar(60),
109    email              varchar(128),
110    phone_number       varchar(60),
111    country            varchar(60),
112    address            varchar(180),
113    postcode           varchar(60),
114    city               varchar(60),
115    note               varchar(60),
116    payment_method     varchar(60),
117    constraint valid_customer_id foreign key (customer_id) REFERENCES customers (id) on delete set NULL
118);
119
120create
121index on receipts(customer_id);
122
123create table orders
124(
125    receipt_id integer NOT NULL,
126    product_id integer NOT NULL,
127    quantity   integer NOT NULL CHECK (quantity > 0),
128    color      varchar(256),
129    size       varchar(256),
130    constraint valid_receipt_id foreign key (receipt_id) REFERENCES receipts (id) on delete set null,
131    constraint valid_product_id foreign key (product_id) REFERENCES products (id) on delete set null,
132    PRIMARY key (receipt_id, product_id, color, size)
133);
134
135
136create table reviews
137(
138    customer_id  integer not null,
139    product_id   integer not null,
140    show         boolean                  DEFAULT true,
141    value        TEXT    NOT NULL,
142    created_date TIMESTAMP with time zone DEFAULT current_timestamp,
143    constraint valid_customer_id foreign key (customer_id) REFERENCES customers (id) on delete set null,
144    constraint valid_product_id foreign key (product_id) REFERENCES products (id) on delete set null,
145    PRIMARY key (customer_id, product_id)
146);
147