CREATE TABLE `tblshopping_list` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `tenant_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(191) COLLATE utf8_unicode_ci NOT NULL,
  `shop_for` enum('company','client'),
  `client_id` bigint(20) UNSIGNED DEFAULT NULL,
  `street_1` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `street_2` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `city` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `state` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zip` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `country` bigint(20) UNSIGNED DEFAULT NULL,
  `total_quantity` bigint(20) UNSIGNED DEFAULT "0",
  `total_price` decimal(15,2) DEFAULT "0.00",
  `status` enum('pending','in-progress', 'completed') DEFAULT "pending",
  `addedfrom` bigint(20) UNSIGNED DEFAULT NULL,
  `datecreated` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `tblshopping_list`
  ADD PRIMARY KEY (`id`),
  ADD KEY `tenant_id` (`tenant_id`),
  ADD KEY `client_id` (`client_id`),
  ADD KEY `addedfrom` (`addedfrom`);

ALTER TABLE `tblshopping_list`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;


CREATE TABLE `tblshopping_list_items` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `shopping_list_id` bigint(20) UNSIGNED NOT NULL,
  `cp_supply_id` bigint(20) UNSIGNED DEFAULT NULL,
  `my_supply_id` bigint(20) UNSIGNED DEFAULT NULL,
  `type` enum('my_supply','cp_supply'),
  `name` varchar(191) COLLATE utf8_unicode_ci NOT NULL,
  `image` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category_name` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `supplier_name` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tag` varchar(191) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` decimal(15,2) DEFAULT "0.00",
  `quantity` bigint(20) UNSIGNED NOT NULL,
  `supply_total_price` decimal(15,2) NOT NULL,
  `purchase_status` enum('purchased','unpurchased') DEFAULT "unpurchased"
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `tblshopping_list_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `shopping_list_id` (`shopping_list_id`),
  ADD KEY `cp_supply_id` (`cp_supply_id`),
  ADD KEY `my_supply_id` (`my_supply_id`);

ALTER TABLE `tblshopping_list_items`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `tblshopping_list_items` ADD FOREIGN KEY (`cp_supply_id`) REFERENCES `tblsystemwide_supplies`(`id`) ON DELETE SET NULL ON UPDATE SET NULL; ALTER TABLE `tblshopping_list_items` ADD FOREIGN KEY (`my_supply_id`) REFERENCES `tbltenantspecific_supplies`(`id`) ON DELETE SET NULL ON UPDATE SET NULL;

ALTER TABLE `tblshopping_list_items` ADD `dateadded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `purchase_status`, ADD `datefinished` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `dateadded`;
ALTER TABLE `tblshopping_list_items` ADD `item_order` BIGINT(20) UNSIGNED NOT NULL DEFAULT '1' AFTER `datefinished`;