7 files changed, 80 insertions, 0 deletions

diff --git a/backend/migrations/.gitkeep b/backend/migrations/.gitkeep
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/backend/migrations/.gitkeep
diff --git a/backend/migrations/2020-09-22-104623_create_users/down.sql b/backend/migrations/2020-09-22-104623_create_users/down.sql
new file mode 100644
index 0000000..2c92cb7
--- /dev/null
+++ b/backend/migrations/2020-09-22-104623_create_users/down.sql
@@ -0,0 +1,3 @@
+-- This file should undo anything in `up.sql`
+
+DROP TABLE members;
diff --git a/backend/migrations/2020-09-22-104623_create_users/up.sql b/backend/migrations/2020-09-22-104623_create_users/up.sql
new file mode 100644
index 0000000..dfd27d9
--- /dev/null
+++ b/backend/migrations/2020-09-22-104623_create_users/up.sql
@@ -0,0 +1,8 @@
+-- Your SQL goes here
+CREATE TABLE members (
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    username VARCHAR(255) NOT NULL UNIQUE,
+    password VARCHAR(255) NOT NULL,
+    phone_number VARCHAR(10) NOT NULL,
+    email_id VARCHAR(255) NOT NULL
+)
diff --git a/backend/migrations/2020-11-16-133516_create_products_table/down.sql b/backend/migrations/2020-11-16-133516_create_products_table/down.sql
new file mode 100644
index 0000000..7d99a98
--- /dev/null
+++ b/backend/migrations/2020-11-16-133516_create_products_table/down.sql
@@ -0,0 +1,3 @@
+-- This file should undo anything in `up.sql`
+
+DROP TABLE product;
diff --git a/backend/migrations/2020-11-16-133516_create_products_table/up.sql b/backend/migrations/2020-11-16-133516_create_products_table/up.sql
new file mode 100644
index 0000000..829c5be
--- /dev/null
+++ b/backend/migrations/2020-11-16-133516_create_products_table/up.sql
@@ -0,0 +1,8 @@
+-- Your SQL goes here
+CREATE TABLE product (
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    name VARCHAR(255) NOT NULL,
+    kind VARCHAR(255),
+    price FLOAT NOT NULL,
+    description VARCHAR(255)
+)
diff --git a/backend/migrations/2020-12-13-140215_all_tables/down.sql b/backend/migrations/2020-12-13-140215_all_tables/down.sql
new file mode 100644
index 0000000..9c554a2
--- /dev/null
+++ b/backend/migrations/2020-12-13-140215_all_tables/down.sql
@@ -0,0 +1,11 @@
+-- This file should undo anything in `up.sql`
+
+drop table customer;
+
+drop table product;
+
+drop table cart_items;
+
+drop table rating;
+
+drop table transaction;
diff --git a/backend/migrations/2020-12-13-140215_all_tables/up.sql b/backend/migrations/2020-12-13-140215_all_tables/up.sql
new file mode 100644
index 0000000..527b926
--- /dev/null
+++ b/backend/migrations/2020-12-13-140215_all_tables/up.sql
@@ -0,0 +1,47 @@
+-- Your SQL goes here
+
+create table customer (
+    id integer primary key auto_increment,
+    username varchar(255) not null unique,
+    password varchar(255) not null,
+    phone_number varchar(10) not null,
+    email_id varchar(255) not null,
+    address text(500)
+);
+
+create table product (
+    id integer primary key auto_increment,
+    name varchar(255) not null,
+    kind varchar(255),
+    price float not null,
+    description varchar(255)
+);
+
+create table cart_items (
+    cart_id integer,
+    product_id integer,
+    constraint cart_items_pk primary key (cart_id, product_id),
+    foreign key (cart_id) references customer(id),
+    foreign key (product_id) references product(id)
+);
+
+create table rating (
+    id integer primary key auto_increment,
+    comment_text text(500),
+    comment_date date default curdate(),
+    product_id integer,
+    customer_id integer,
+
+    stars integer check (stars >= 0 AND stars <= 5),
+    foreign key (customer_id) references customer(id),
+    foreign key (product_id) references product(id)
+);
+
+create table transaction (
+    id integer primary key auto_increment,
+    payment_type varchar(255) not null,
+    amount float not null,
+    customer_id integer,
+
+    foreign key (customer_id) references customer(id)
+);