--
-- Host: localhost    Database: jumuiyaenergy_ecommerce
-- ------------------------------------------------------


--
-- Current Database: `jumuiyaenergy_ecommerce`
--


--
-- Table structure for table `address`
--

CREATE TABLE `address` (
  `address_id` varchar(30) NOT NULL,
  `user_id` varchar(30) NOT NULL,
  `label` varchar(200) NOT NULL,
  `place` varchar(500) DEFAULT NULL,
  `latitude` varchar(255) DEFAULT NULL,
  `longitude` varchar(255) DEFAULT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`address_id`),
  KEY `address_ibfk_1` (`user_id`),
  CONSTRAINT `address_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `address_backup`
--

CREATE TABLE `address_backup` (
  `address_id` varchar(30) CHARACTER SET latin1 NOT NULL,
  `user_id` varchar(30) CHARACTER SET latin1 NOT NULL,
  `label` varchar(200) CHARACTER SET latin1 NOT NULL,
  `place` varchar(500) CHARACTER SET latin1 DEFAULT NULL,
  `latitude` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `longitude` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `admin`
--

CREATE TABLE `admin` (
  `admin_id` varchar(255) NOT NULL,
  `admin_name` varchar(255) NOT NULL,
  `admin_email` varchar(255) NOT NULL,
  `admin_phone` varchar(20) NOT NULL,
  `admin_password` varchar(255) NOT NULL,
  `admin_privileges` enum('super','standard') CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT 'standard',
  `admin_status` enum('active','inactive') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'active',
  `reset_token` varchar(100) DEFAULT NULL,
  `reset_created_at` datetime DEFAULT NULL,
  `admin_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `agent_roles`
--

CREATE TABLE `agent_roles` (
  `role_id` varchar(30) NOT NULL,
  `role_name` varchar(30) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `admin_id` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `authtokens`
--

CREATE TABLE `authtokens` (
  `tokenID` int NOT NULL AUTO_INCREMENT,
  `token` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`tokenID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `brand`
--

CREATE TABLE `brand` (
  `brand_id` varchar(30) NOT NULL,
  `category_id` varchar(30) NOT NULL,
  `brand_name` varchar(255) NOT NULL,
  `brand_description` text,
  `brand_image` varchar(255) NOT NULL,
  `brand_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `brand_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `admin_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

CREATE TABLE `cart` (
  `cart_id` varchar(30) NOT NULL,
  `user_id` varchar(30) DEFAULT NULL,
  `product_id` varchar(30) NOT NULL,
  `product_price` int NOT NULL,
  `product_total_price` int NOT NULL,
  `variations_price` int DEFAULT NULL,
  `device` text,
  `cart_quantity` int NOT NULL,
  `cart_variations` text,
  `cart_price` int NOT NULL,
  `cart_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cart_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `category`
--

CREATE TABLE `category` (
  `category_id` varchar(30) NOT NULL,
  `category_name` varchar(255) NOT NULL,
  `category_description` text NOT NULL,
  `category_image` varchar(255) NOT NULL,
  `category_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `category_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `category_order` int NOT NULL,
  `admin_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `hub`
--

CREATE TABLE `hub` (
  `hub_id` varchar(30) NOT NULL,
  `hub_name` varchar(255) NOT NULL,
  `hub_image` varchar(255) DEFAULT NULL,
  `hub_location` varchar(255) NOT NULL,
  `hub_latitude` varchar(255) NOT NULL,
  `hub_longitude` varchar(255) NOT NULL,
  `hub_place_id` varchar(255) DEFAULT NULL,
  `hub_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `hub_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`hub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `jp_table`
--

CREATE TABLE `jp_table` (
  `status` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `ref` varchar(255) DEFAULT NULL,
  `orderId` varchar(255) NOT NULL,
  `checksum_status` enum('failed','success') NOT NULL DEFAULT 'success',
  `description` varchar(255) DEFAULT NULL,
  `runningBalance` varchar(255) DEFAULT NULL,
  `checksum` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `location`
--

CREATE TABLE `location` (
  `location_id` varchar(255) NOT NULL,
  `location_name` varchar(255) NOT NULL,
  `location_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `location_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `oil_marketers`
--

CREATE TABLE `oil_marketers` (
  `oil_marketers_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `delivery_fee` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0.00',
  `retailer_code` int unsigned NOT NULL AUTO_INCREMENT,
  `profile_image` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `reset_token` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `reset_created_at` datetime DEFAULT NULL,
  `banner_image` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `full_names` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `user_type` enum('oil_marketer','retailer') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'retailer',
  `license_registration_number` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `business_permit_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `oil_marketers` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `locations` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `brands` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `oil_marketers_status` enum('active','inactive') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'inactive',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `admin_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `fcmToken` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `login_time` datetime DEFAULT NULL,
  PRIMARY KEY (`retailer_code`),
  KEY `idx_oil_marketers_id` (`oil_marketers_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `oil_marketers_clone`
--

CREATE TABLE `oil_marketers_clone` (
  `oil_marketers_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `delivery_fee` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0.00',
  `retailer_code` int unsigned NOT NULL AUTO_INCREMENT,
  `profile_image` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `banner_image` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `full_names` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `user_type` enum('oil_marketer','retailer') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'retailer',
  `license_registration_number` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `business_permit_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `oil_marketers` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `locations` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `brands` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `oil_marketers_status` enum('active','inactive') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'inactive',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `admin_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `fcmToken` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `login_time` datetime DEFAULT NULL,
  PRIMARY KEY (`retailer_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `oil_marketers_documents`
--

CREATE TABLE `oil_marketers_documents` (
  `document_id` int NOT NULL AUTO_INCREMENT,
  `oil_marketers_id` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `document_path` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`document_id`),
  KEY `product_id` (`oil_marketers_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

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

CREATE TABLE `orders` (
  `order_id` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `user_id` varchar(255) CHARACTER SET latin1 NOT NULL,
  `channel` enum('ussd','mobile','mini_app') CHARACTER SET latin1 NOT NULL DEFAULT 'mobile',
  `retailer_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `confirmation_code` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `assigned_to` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `total_amount` varchar(100) CHARACTER SET latin1 NOT NULL,
  `delivery_fee` varchar(100) CHARACTER SET latin1 NOT NULL,
  `status` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT 'pending',
  `source_latitude` varchar(100) CHARACTER SET latin1 NOT NULL,
  `source_longitude` varchar(100) CHARACTER SET latin1 NOT NULL,
  `payment_method` varchar(255) CHARACTER SET latin1 NOT NULL,
  `delivery_address` varchar(100) CHARACTER SET latin1 NOT NULL,
  `payment_status` enum('pending','paid','rejected') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'pending',
  `payment_ref_id` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `payment_date` datetime DEFAULT NULL,
  `phone_number` varchar(15) CHARACTER SET latin1 DEFAULT NULL,
  `payment_amount` varchar(100) CHARACTER SET latin1 NOT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `delivered_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`order_id`),
  KEY `user_id` (`user_id`),
  KEY `retailer_id` (`retailer_id`),
  KEY `delivery_address` (`delivery_address`),
  KEY `assigned_to` (`assigned_to`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`retailer_id`) REFERENCES `oil_marketers` (`oil_marketers_id`) ON DELETE CASCADE,
  CONSTRAINT `orders_ibfk_3` FOREIGN KEY (`delivery_address`) REFERENCES `address` (`address_id`) ON DELETE CASCADE,
  CONSTRAINT `orders_ibfk_4` FOREIGN KEY (`assigned_to`) REFERENCES `sysusers` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `orders_backup`
--

CREATE TABLE `orders_backup` (
  `order_id` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `user_id` varchar(255) CHARACTER SET latin1 NOT NULL,
  `channel` enum('ussd','mobile','mini_app') CHARACTER SET latin1 NOT NULL DEFAULT 'mobile',
  `retailer_id` varchar(100) CHARACTER SET latin1 NOT NULL,
  `confirmation_code` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `assigned_to` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `total_amount` varchar(100) CHARACTER SET latin1 NOT NULL,
  `delivery_fee` varchar(100) CHARACTER SET latin1 NOT NULL,
  `status` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT 'pending',
  `source_latitude` varchar(100) CHARACTER SET latin1 NOT NULL,
  `source_longitude` varchar(100) CHARACTER SET latin1 NOT NULL,
  `payment_method` varchar(255) CHARACTER SET latin1 NOT NULL,
  `delivery_address` varchar(100) CHARACTER SET latin1 NOT NULL,
  `payment_status` enum('pending','paid','rejected') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'pending',
  `payment_ref_id` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `payment_date` datetime DEFAULT NULL,
  `phone_number` varchar(15) CHARACTER SET latin1 DEFAULT NULL,
  `payment_amount` varchar(100) CHARACTER SET latin1 NOT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `delivered_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `product_id` varchar(30) NOT NULL,
  `product_barcode` varchar(100) NOT NULL,
  `product_name` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `retailer_id` varchar(30) NOT NULL,
  `product_image` varchar(255) DEFAULT NULL,
  `product_description` text,
  `brand_id` varchar(30) DEFAULT NULL,
  `product_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_id` varchar(30) DEFAULT NULL,
  `product_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `product_data`
--

CREATE TABLE `product_data` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` varchar(100) NOT NULL,
  `batch` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expiry_date` date NOT NULL,
  `expiry_alert` varchar(100) NOT NULL,
  `quantity` int NOT NULL,
  `remain_quantity` int DEFAULT '10',
  `expiry_date1` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

--
-- Table structure for table `product_image`
--

CREATE TABLE `product_image` (
  `product_image_id` varchar(255) NOT NULL,
  `product_id` varchar(255) NOT NULL,
  `product_image_one` varchar(255) DEFAULT NULL,
  `product_image_two` varchar(255) DEFAULT NULL,
  `product_image_three` varchar(255) DEFAULT NULL,
  `product_image_four` varchar(255) DEFAULT NULL,
  `product_image_five` varchar(255) DEFAULT NULL,
  `product_image_six` varchar(255) DEFAULT NULL,
  `product_image_seven` varchar(255) DEFAULT NULL,
  `product_image_eight` varchar(255) DEFAULT NULL,
  `product_image_nine` varchar(255) DEFAULT NULL,
  `product_image_ten` varchar(255) DEFAULT NULL,
  `product_image_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`product_image_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `product_variants`
--

CREATE TABLE `product_variants` (
  `variant_id` varchar(30) NOT NULL,
  `product_id` varchar(30) NOT NULL,
  `subvariation_id` varchar(30) NOT NULL,
  `variant_name` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `variant_purchasing_price` varchar(30) NOT NULL,
  `variant_refilling_price` varchar(30) NOT NULL,
  `discount_refilling_price` varchar(30) DEFAULT NULL,
  `discount_purchasing_price` varchar(30) DEFAULT NULL,
  `variant_image` varchar(255) DEFAULT NULL,
  `variant_quantity` varchar(30) NOT NULL DEFAULT '0',
  `variant_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_id` varchar(30) DEFAULT NULL,
  `min_shelf_products` varchar(30) NOT NULL DEFAULT '10',
  `variant_updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`variant_id`),
  KEY `fk_product_id` (`product_id`),
  CONSTRAINT `fk_product_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `storage`
--

CREATE TABLE `storage` (
  `user_id` int NOT NULL,
  `key` varchar(255) NOT NULL,
  `value` text,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `sub_orders`
--

CREATE TABLE `sub_orders` (
  `id` varchar(255) NOT NULL,
  `order_id` varchar(255) NOT NULL,
  `product_id` varchar(255) NOT NULL,
  `variation_id` varchar(255) NOT NULL,
  `quantity` varchar(100) NOT NULL,
  `size` varchar(255) NOT NULL,
  `sub_total` varchar(100) NOT NULL,
  `type` varchar(255) NOT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_variation_id_FK` (`variation_id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `product_variation_id_FK` FOREIGN KEY (`variation_id`) REFERENCES `product_variants` (`variant_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `sub_orders_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `subcategory`
--

CREATE TABLE `subcategory` (
  `subcategory_id` varchar(30) NOT NULL,
  `subcategory_name` varchar(255) NOT NULL,
  `category_id` varchar(30) NOT NULL,
  `subcategory_description` text,
  `subcategory_image` varchar(255) NOT NULL,
  `subcategory_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `subcategory_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `admin_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`subcategory_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `subcategory_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `subvariation`
--

CREATE TABLE `subvariation` (
  `subvariation_id` varchar(30) NOT NULL,
  `subvariation_name` varchar(255) NOT NULL,
  `subvariation_image` varchar(255) DEFAULT NULL,
  `variation_id` varchar(30) NOT NULL,
  `brand_id` varchar(30) DEFAULT NULL,
  `subvariation_price` varchar(100) NOT NULL DEFAULT '0',
  `subvariation_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`subvariation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `supplier`
--

CREATE TABLE `supplier` (
  `supplier_id` varchar(30) NOT NULL,
  `supplier_name` varchar(255) NOT NULL,
  `supplier_email` varchar(255) DEFAULT NULL,
  `user_id` varchar(30) NOT NULL,
  `product_name` text NOT NULL,
  `supplier_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `supplier_type` enum('individual','company') NOT NULL,
  `supplier_document` varchar(255) NOT NULL,
  `supplier_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`supplier_id`),
  UNIQUE KEY `supplier_email` (`supplier_email`),
  KEY `fk_user_id` (`user_id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `system_audit`
--

CREATE TABLE `system_audit` (
  `system_audit_id` varchar(18) NOT NULL,
  `system_audit_user_id` varchar(18) NOT NULL,
  `system_audit_status` enum('SUCCESS','FAILED') NOT NULL DEFAULT 'SUCCESS',
  `system_audit_table_name` varchar(20) NOT NULL,
  `system_audit_table_id` varchar(18) NOT NULL,
  `system_audit_action` enum('create','update') NOT NULL,
  `system_audit_action_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `system_audit_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`system_audit_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `sysusers`
--

CREATE TABLE `sysusers` (
  `user_id` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `full_names` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `user_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `retailer_id` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `phone_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `user_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `status` enum('isLoggedOut','isLoggedIn') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'isLoggedIn',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `admin_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `fcmToken` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `loginTime` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `phone_number` (`phone_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `enter_login_times` AFTER UPDATE ON `sysusers` FOR EACH ROW BEGIN
   -- Set the server's timezone to 'Africa/Nairobi'
   -- SET @@session.time_zone = 'Africa/Nairobi';
   -- Check if the specified column was updated
   IF NEW.status <> OLD.status THEN
       -- Insert rows into another table using the values from the updated rows
       INSERT INTO loggedInUsers (userID, loginTime,entryDate)
       VALUES (NEW.userID, NOW(),NOW());
   END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `tag`
--

CREATE TABLE `tag` (
  `tag_id` varchar(30) NOT NULL,
  `tag_title` varchar(255) NOT NULL,
  `tag_hex` varchar(255) DEFAULT NULL,
  `tag_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `unit`
--

CREATE TABLE `unit` (
  `unit_id` varchar(30) NOT NULL,
  `unit_name` varchar(255) NOT NULL,
  `unit_symbol` varchar(255) DEFAULT NULL,
  `unit_description` text NOT NULL,
  `unit_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `user_id` varchar(30) NOT NULL,
  `retailer_code` varchar(10) NOT NULL DEFAULT '0000',
  `assigned_to` varchar(100) DEFAULT NULL,
  `user_first_name` varchar(255) NOT NULL,
  `user_last_name` varchar(255) NOT NULL,
  `user_email` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `user_phone` varchar(255) NOT NULL,
  `user_image` varchar(255) DEFAULT NULL,
  `bio` varchar(255) DEFAULT NULL,
  `type` enum('mobile','ussd') NOT NULL DEFAULT 'mobile',
  `user_password` varchar(255) NOT NULL,
  `user_points` double NOT NULL DEFAULT '0',
  `verification_code` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `verification_code_created_at` datetime DEFAULT NULL,
  `verified_at` datetime DEFAULT NULL,
  `user_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user_date_updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `referral_code` varchar(30) DEFAULT NULL,
  `referred_by` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_phone` (`user_phone`),
  UNIQUE KEY `user_email` (`user_email`),
  UNIQUE KEY `Referral_code` (`referral_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `user_clone`
--

CREATE TABLE `user_clone` (
  `user_id` varchar(30) NOT NULL,
  `retailer_code` varchar(10) NOT NULL DEFAULT '0000',
  `user_first_name` varchar(255) NOT NULL,
  `user_last_name` varchar(255) NOT NULL,
  `user_email` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `user_phone` varchar(255) NOT NULL,
  `user_image` varchar(255) DEFAULT NULL,
  `bio` varchar(255) DEFAULT NULL,
  `type` enum('mobile','ussd') NOT NULL DEFAULT 'mobile',
  `user_password` varchar(255) NOT NULL,
  `user_points` double NOT NULL DEFAULT '0',
  `verification_code` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `verification_code_created_at` datetime DEFAULT NULL,
  `verified_at` datetime DEFAULT NULL,
  `user_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `referral_code` varchar(30) DEFAULT NULL,
  `referred_by` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_phone` (`user_phone`),
  UNIQUE KEY `user_email` (`user_email`),
  UNIQUE KEY `Referral_code` (`referral_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `user_location`
--

CREATE TABLE `user_location` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` varchar(100) NOT NULL,
  `order_id` varchar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `longitude` varchar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `latitude` varchar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

--
-- Table structure for table `variation`
--

CREATE TABLE `variation` (
  `variation_id` varchar(30) NOT NULL,
  `variation_name` varchar(255) NOT NULL,
  `variation_image` varchar(255) DEFAULT NULL,
  `variation_date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`variation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `wishlist`
--

CREATE TABLE `wishlist` (
  `id` varchar(255) NOT NULL,
  `products` varchar(400) NOT NULL,
  `user_id` varchar(30) NOT NULL,
  `type` enum('wishlist','regular') NOT NULL DEFAULT 'wishlist',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping events for database 'jumuiyaenergy_ecommerce'
--

--
-- Dumping routines for database 'jumuiyaenergy_ecommerce'
--


