--
-- Host: localhost    Database: kafeklou_dbv1
-- ------------------------------------------------------


--
-- Table structure for table `associate_reviews`
--

CREATE TABLE `associate_reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `associate_name` text NOT NULL,
  `logo` text,
  `rating` decimal(2,1) DEFAULT NULL,
  `review` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `associate_reviews`
--


--
-- Table structure for table `cart`
--

CREATE TABLE `cart` (
  `user_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `day_of_week` enum('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Trial') NOT NULL DEFAULT 'Monday',
  `group_id` int(11) DEFAULT NULL,
  `quantity` int(11) NOT NULL DEFAULT '1',
  `set_default` tinyint(1) DEFAULT '0',
  `set_current` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`,`item_id`,`day_of_week`),
  KEY `cart_item_id_fkey_idx` (`item_id`),
  KEY `cart_group_id_fkey` (`group_id`),
  CONSTRAINT `cart_group_id_fkey` FOREIGN KEY (`group_id`) REFERENCES `user_groups` (`group_id`) ON UPDATE CASCADE,
  CONSTRAINT `cart_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `cart`
--


--
-- Table structure for table `delivery_locations`
--

CREATE TABLE `delivery_locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(255) NOT NULL,
  `location` text NOT NULL,
  `latitude` decimal(12,8) NOT NULL,
  `longitude` decimal(12,8) NOT NULL,
  `pincodes` text,
  `radius` int(11) DEFAULT NULL,
  `status` enum ('DS' ,'DA', 'DWO') NOT NULL DEFAULT 'DA',
  `location_pic` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `delivery_locations`
--

INSERT INTO `delivery_locations` VALUES (1,'Bengaluru','Marathahalli',12.95917220,77.69741900,'560037',5,'DA','images/app/locations/marathalli.jpg','2018-07-06 07:12:24',NULL),(2,'Bengaluru','Bellandur',12.92603080,77.67624630,'560103',5,'DA','images/app/locations/2.jpg','2018-07-09 04:50:57',NULL),(3,'Bengaluru','BTM Layout',12.91657570,77.61011630,'560029,560068,560076',5,'DA','images/app/locations/3.jpg','2018-07-09 04:51:43',NULL),(4,'Bengaluru','Jayanagar',12.92500740,77.59380280,'560041',5,'DA','images/app/locations/4.jpg','2018-07-09 04:52:36',NULL),(5,'Bengaluru','Koramangala',12.92792320,77.62710780,'560034',5,'DA','images/app/locations/5.jpg','2018-07-09 04:53:19',NULL),(6,'Bengaluru','Madiwala',12.92263730,77.61744420,'560068',5,'DA','images/app/locations/6.jpg','2018-07-09 04:54:22',NULL),(7,'Bengaluru','Bommanahalli',12.89836010,77.61794650,'560076',5,'DA','images/app/locations/7.jpg','2018-07-09 04:55:19',NULL),(8,'Bengaluru','Electronic City',12.83993890,77.67700310,'560100',5,'DA','images/app/locations/8.jpg','2018-07-09 04:56:25',NULL),(9,'Bengaluru','HSR Layout',12.90813570,77.64760800,'560102',5,'DA','images/app/locations/9.jpg','2018-07-09 04:56:56',NULL);

--
-- Table structure for table `group_memberships`
--

CREATE TABLE `group_memberships` (
  `group_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `active` tinyint(4) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`group_id`,`member_id`),
  KEY `group_memberships_member_fkey_idx` (`member_id`),
  CONSTRAINT `group_memberships_group_fkey` FOREIGN KEY (`group_id`) REFERENCES `user_groups` (`group_id`) ON UPDATE CASCADE,
  CONSTRAINT `group_memberships_member_fkey` FOREIGN KEY (`member_id`) REFERENCES `user_profiles` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `group_memberships`
--


--
-- Table structure for table `item_reviews`
--

CREATE TABLE `item_reviews` (
  `user_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `rating` decimal(2,1) NOT NULL,
  `review` text NOT NULL,
  `moderate` enum('Hide','Show','ShowOnHomePage') DEFAULT 'Show',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`user_id`,`item_id`),
  KEY `item_review_item_id_fkey` (`item_id`),
  CONSTRAINT `item_review_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `item_review_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `user_profiles` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `item_reviews`
--



--
-- Table structure for table `menu_items`
--

CREATE TABLE `menu_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(45) NOT NULL,
  `short_description` varchar(100) NOT NULL,
  `item_description` text NOT NULL,
  `category` int(11) NOT NULL,
  `food_type` enum('Veg','Non-Veg') NOT NULL DEFAULT 'Veg',
  `max_price` decimal(8,2) NOT NULL,
  `trial_price` decimal(8,2) NULL,
  `available` tinyint(1) DEFAULT '1',
  `exclusive` tinyint(1) DEFAULT '0',
  `item_pic_0` text,
  `item_pic_1` text,
  `item_pic_2` text,
  `item_thumbnail` text,
  `calories` decimal(8,3) DEFAULT NULL,
  `carbs` text,
  `proteins` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `menu_items_cat_fkey` (`category`),
  CONSTRAINT `menu_items_cat_fkey` FOREIGN KEY (`category`) REFERENCES `option_values` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `menu_items`
--


--
-- Table structure for table `menu_pricing`
--

CREATE TABLE `menu_pricing` (
  `item_id` int(11) NOT NULL,
  `subscription_type` enum('Group','Individual','Trial') NOT NULL,
  `plan_id` int(11) NOT NULL,
  `price` decimal(10,0) NOT NULL,
  `quantity` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`item_id`,`subscription_type`,`plan_id`),
  KEY `menu_pricing_plan_id_fkey_idx` (`plan_id`),
  CONSTRAINT `menu_pricing_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `menu_pricing_plan_id_fkey` FOREIGN KEY (`plan_id`) REFERENCES `option_values` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `menu_pricing`
--

INSERT INTO `menu_pricing` VALUES (1,'Group',6,210,NULL,'2018-04-08 05:59:59',NULL),(1,'Group',7,200,NULL,'2018-04-08 06:27:07',NULL),(1,'Individual',6,220,NULL,'2018-04-08 06:27:52',NULL),(2,'Group',6,180,NULL,'2018-05-02 17:30:09',NULL);

--
-- Table structure for table `option_types`
--

CREATE TABLE `option_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type_name` varchar(45) NOT NULL,
  `active` tinyint(4) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `option_types`
--

INSERT INTO `option_types` VALUES (1,'Home Page Settings',1,'2018-04-06 17:49:29',NULL),(2,'Email Settings',1,'2018-04-06 17:49:34',NULL),(3,'SMS Settings',1,'2018-04-06 17:49:40',NULL),(4,'Subscription Plans',1,'2018-04-07 09:32:07',NULL),(5,'Menu Categories',1,'2018-04-07 17:18:14',NULL),(6,'Tax Settings',1,'2018-04-10 14:30:00',NULL),(7,'Facebook Settings',1,'2018-05-06 11:56:27',NULL),(8,'Twitter Settings',1,'2018-05-06 11:56:43',NULL),(9,'Google+ Settings',1,'2018-05-06 11:56:56',NULL),(10,'Linkedin Settings',1,'2018-05-06 11:57:12',NULL),(11,'Delivery Timings',1,'2018-06-07 17:03:31',NULL), (12,'Company Names',1,'2018-04-06 17:49:29',NULL);

--
-- Table structure for table `option_values`
--

CREATE TABLE `option_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `option_type` int(11) NOT NULL,
  `type_name` varchar(45) NOT NULL,
  `active` tinyint(4) NOT NULL DEFAULT '1',
  `value` text,
  `attribute_1` text,
  `attribute_2` text,
  `attribute_3` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `option_values_type_id_fkey_idx` (`option_type`),
  CONSTRAINT `option_values_option_type_fkey` FOREIGN KEY (`option_type`) REFERENCES `option_types` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `option_values`
--



--
-- Table structure for table `order_details`
--

CREATE TABLE `order_details` (
  `order_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT '1',
  `item_price` decimal(10,0) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`,`item_id`),
  KEY `order_details_order_id_fkey_idx` (`order_id`),
  KEY `order_details_item_id_fkey_idx` (`item_id`),
  CONSTRAINT `order_details_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `order_details_order_id_fkey` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `order_details`
--


--
-- Table structure for table `order_reviews`
--

CREATE TABLE `order_reviews` (
  `user_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `rating` decimal(2,1) NOT NULL,
  `review` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`user_id`,`order_id`),
  KEY `order_review_order_id_fkey` (`order_id`),
  CONSTRAINT `order_review_order_id_fkey` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON UPDATE CASCADE,
  CONSTRAINT `order_review_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `user_profiles` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `order_reviews`
--

--
-- Table structure for table `order_taxes`
--

CREATE TABLE `order_taxes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `tax_string` varchar(45) NOT NULL,
  `tax_perc` decimal(3,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `order_taxes_order_id_fkey` (`order_id`),
  CONSTRAINT `order_taxes_order_id_fkey` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `order_taxes`
--


--
-- Table structure for table `order_transactions`
--

CREATE TABLE `order_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `paid_by` int(11) NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL,
  `txn_reference` text,
  `pay_mode` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `order_txn_paid_by_fkey` (`paid_by`),
  KEY `order_txn_order_id_fkey` (`order_id`),
  CONSTRAINT `order_txn_order_id_fkey` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `order_txn_paid_by_fkey` FOREIGN KEY (`paid_by`) REFERENCES `user_profiles` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `order_transactions`
--


--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `order_status` enum('Placed','Payment Pending','Processing','Cancelled','Delivery In Progres','Completed','Refunded') DEFAULT NULL,
  `bill_amount` decimal(10,2) DEFAULT NULL,
  `discount_perc` decimal(10,0) DEFAULT NULL,
  `promo_code` varchar(45) DEFAULT NULL,
  `tax_perc` decimal(3,2) DEFAULT NULL,
  `total_amount` decimal(10,2) DEFAULT NULL,
  `parent_order` int(11) DEFAULT NULL,
  `shipping_address` text,
  `latitude` decimal(12,8) DEFAULT NULL,
  `longitude` decimal(12,8) DEFAULT NULL,
  `city` text NOT NULL,
  `locality` text NOT NULL,
  `pincode` int(6) NOT NULL,
  `delivery_person` int(11) DEFAULT NULL,
  `delivery_time` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `orders_user_id_fkey_idx` (`user_id`),
  KEY `orders_subscriptions_fkey_idx` (`plan_id`),
  KEY `orders_parent_order_fkey_idx` (`parent_order`),
  KEY `orders_delivery_person_fkey_idx` (`delivery_person`),
  CONSTRAINT `orders_delivery_person_fkey` FOREIGN KEY (`delivery_person`) REFERENCES `user_profiles` (`user_id`) ON UPDATE CASCADE,
  CONSTRAINT `orders_parent_order_fkey` FOREIGN KEY (`parent_order`) REFERENCES `orders` (`order_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `orders_subscriptions_fkey` FOREIGN KEY (`plan_id`) REFERENCES `subscriptions` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `orders_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `user_profiles` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `orders`
--


--
-- Table structure for table `subscriptions`
--

CREATE TABLE `subscriptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('Group','Individual','Trial') NOT NULL,
  `plan_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `status` enum('Pending','Payment Pending','Active','Cancelled','Completed') DEFAULT 'Pending',
  `order_dates` text,
  `load_default_cart` tinyint(1) DEFAULT '0',
  `set_default_cart` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_subscriptions_plan_id_fkey_idx` (`plan_id`),
  KEY `user_subscriptions_user_id_fkey_idx` (`user_id`),
  KEY `subscriptions_group_id_fkey` (`group_id`),
  CONSTRAINT `subscriptions_group_id_fkey` FOREIGN KEY (`group_id`) REFERENCES `user_groups` (`group_id`) ON UPDATE CASCADE,
  CONSTRAINT `subscriptions_plan_id_fkey` FOREIGN KEY (`plan_id`) REFERENCES `option_values` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `subscriptions_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `user_profiles` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `subscriptions`
--


--
-- Table structure for table `user_auth`
--

CREATE TABLE `user_auth` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `provider` varchar(255) NOT NULL,
  `provider_id` varchar(255) NOT NULL,
  `provider_attributes` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `user_auth_user_id_fkey` (`user_id`),
  CONSTRAINT `user_auth_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_auth`
--


--
-- Table structure for table `user_groups`
--

CREATE TABLE `user_groups` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_leader` int(11) NOT NULL,
  `company_name` text NOT NULL,
  `company_address` text,
  `longitude` decimal(10,8) DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `active` tinyint(4) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`group_id`),
  KEY `subscription_groups_leader_fkey_idx` (`group_leader`),
  CONSTRAINT `subscription_groups_leader_fkey` FOREIGN KEY (`group_leader`) REFERENCES `user_profiles` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_groups`
--


--
-- Table structure for table `user_profiles`
--

CREATE TABLE `user_profiles` (
  `user_id` int(11) NOT NULL,
  `full_name` text NOT NULL,
  `email_id` varchar(60) DEFAULT NULL,
  `mobile_number` varchar(15) DEFAULT NULL,
  `company_name` text NOT NULL,
  `company_address` text,
  `city` text,
  `locality` text,
  `latitude` decimal(12,8) DEFAULT NULL,
  `longitude` decimal(12,8) DEFAULT NULL,
  `pincode` int(6) DEFAULT NULL,
  `gender` enum('MALE','FEMALE') NOT NULL DEFAULT 'MALE',
  `date_of_birth` date DEFAULT NULL,
  `profile_pic` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_profiles_unique_email_key` (`email_id`),
  UNIQUE KEY `user_profiles_unique_mobile_key` (`mobile_number`),
  CONSTRAINT `user_profiles_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_profiles`
--

INSERT INTO `user_profiles` VALUES (1,'Kafekloud, Admin','admin@kafekloud.com','7674929003','KafeKloud Inc',NULL,NULL,NULL,NULL,NULL,560102,'MALE','1984-09-04',NULL,'2018-05-02 16:01:28',NULL);


