Queries.txt

CREATE DATABASE IF NOT EXISTS `tsec_hackathon`;
USE `tsec_hackathon`;

CREATE TABLE `users`
(
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar
(100) DEFAULT NULL,
  `full_name` varchar
(100) DEFAULT NULL,
  `email` varchar
(100) DEFAULT NULL,
  `mobile_number` varchar
(100) DEFAULT NULL,
  `address` varchar
(255) DEFAULT NULL,
  `area` varchar
(100) DEFAULT NULL,
  `city_with_pincode` varchar
(100) DEFAULT NULL,
  `state_name` varchar
(100) DEFAULT NULL,
  `password` varchar
(100) DEFAULT NULL,
  `role` varchar
(20) DEFAULT 'customer',
  `coins` varchar
(20) DEFAULT 50,
  PRIMARY KEY
(`id`)
)

CREATE TABLE `items`
(
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `title` varchar
(100) DEFAULT NULL,
  `description` varchar
(255) DEFAULT NULL,
  `price` varchar
(50) DEFAULT NULL,
  `disc_price` varchar
(50) DEFAULT NULL,
  `size` varchar
(50) DEFAULT NULL,
  `colour` varchar
(50) DEFAULT NULL,
  `category` varchar
(50) DEFAULT NULL,
  `type` varchar
(50) DEFAULT NULL,
  `delivery_in_days` varchar
(20) DEFAULT NULL,
  `img_link` varchar(255) DEFAULT NULL,
  `seller` varchar(50) DEFAULT NULL,
  PRIMARY KEY
(`id`),
  KEY `user_id`
(`user_id`),
  FOREIGN KEY(`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `cart`
(
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `item_id` int DEFAULT NULL,
  `ordered` varchar
(15) DEFAULT False,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `orders`
(
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `item_id` int DEFAULT NULL,
  `cart_id` int DEFAULT NULL,
  `status` varchar
(15) DEFAULT 'ordered',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`cart_id`) REFERENCES `cart`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
)