/*
 Navicat Premium Dump SQL

 Source Server         : ProductionServer
 Source Server Type    : MariaDB
 Source Server Version : 100622 (10.6.22-MariaDB-log)
 Source Host           : localhost:3306
 Source Schema         : kiosk_pc00

 Target Server Type    : MariaDB
 Target Server Version : 100622 (10.6.22-MariaDB-log)
 File Encoding         : 65001

 Date: 18/07/2025 08:45:36
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for buyQueue
-- ----------------------------
DROP TABLE IF EXISTS `buyQueue`;
CREATE TABLE `buyQueue` (
  `buyID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `drsBuyID` varchar(16) DEFAULT NULL,
  `tabletID` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `dailyNum` int(3) unsigned NOT NULL,
  `timeEntered` timestamp NOT NULL DEFAULT current_timestamp(),
  `sortStarted` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `sortCompleted` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `sorterID` int(10) unsigned DEFAULT NULL,
  `timeStarted` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `timeCompleted` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `buyerID` int(10) unsigned DEFAULT NULL,
  `numContainers` tinyint(2) unsigned NOT NULL DEFAULT 0,
  `textMe` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `inStore` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `returnTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `isProcessed` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `isReturning` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `processedContainers` tinyint(2) unsigned NOT NULL DEFAULT 0,
  `remainingContainers` tinyint(2) unsigned NOT NULL DEFAULT 0,
  `customerID` int(10) unsigned NOT NULL,
  `doCount` tinyint(1) unsigned NOT NULL DEFAULT 1,
  `twilioSent` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `isNewCustomer` tinyint(4) NOT NULL DEFAULT 0,
  `clearFlags` tinyint(3) NOT NULL DEFAULT 0,
  `remote` tinyint(1) NOT NULL DEFAULT 0,
  `dropOffTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `hasDroppedOff` tinyint(1) NOT NULL DEFAULT 0,
  `checkOutTime` timestamp NULL DEFAULT NULL,
  `checkedOut` tinyint(1) NOT NULL DEFAULT 0,
  `upperBound` smallint(5) unsigned NOT NULL DEFAULT 0,
  `lowerBound` smallint(5) unsigned NOT NULL DEFAULT 0,
  `waitTime` smallint(5) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`buyID`),
  UNIQUE KEY `drsBuyID` (`drsBuyID`) USING BTREE,
  KEY `customerID` (`customerID`),
  KEY `buyerID` (`buyerID`),
  KEY `customerID_2` (`customerID`)
) ENGINE=InnoDB AUTO_INCREMENT=84201 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- ----------------------------
-- Table structure for customerAlerts
-- ----------------------------
DROP TABLE IF EXISTS `customerAlerts`;
CREATE TABLE `customerAlerts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customerID` int(11) NOT NULL,
  `type` tinyint(1) unsigned NOT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `class` varchar(50) DEFAULT NULL,
  `repeat` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 = Indefinitely, 1 = Once, ',
  `cleared` tinyint(1) DEFAULT NULL,
  `dateAdded` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `Customer ID` (`customerID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table structure for customerRatings
-- ----------------------------
DROP TABLE IF EXISTS `customerRatings`;
CREATE TABLE `customerRatings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customerID` int(11) NOT NULL,
  `rating` tinyint(2) unsigned NOT NULL,
  `employeeID` tinyint(2) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customerID` (`customerID`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=87 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table structure for customers
-- ----------------------------
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `customerID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `dateAdded` timestamp NOT NULL DEFAULT current_timestamp(),
  `firstName` varchar(50) NOT NULL,
  `lastName` varchar(50) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `address` varchar(255) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(2) NOT NULL,
  `zipcode` varchar(5) NOT NULL,
  `phone` varchar(10) NOT NULL,
  `onSMS` tinyint(1) unsigned DEFAULT 0,
  `onEmail` tinyint(1) unsigned DEFAULT 0,
  `driversLicense` varchar(50) DEFAULT NULL,
  `referralCode` varchar(10) NOT NULL DEFAULT '000000',
  `addedToSMS` timestamp NULL DEFAULT NULL,
  `addedToEmail` timestamp NULL DEFAULT NULL,
  `driversLicenseDOB` date DEFAULT NULL,
  `driversLicenseExpiration` date DEFAULT NULL,
  PRIMARY KEY (`customerID`),
  KEY `dl` (`driversLicense`)
) ENGINE=InnoDB AUTO_INCREMENT=30112 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- ----------------------------
-- Table structure for customerSurvey
-- ----------------------------
DROP TABLE IF EXISTS `customerSurvey`;
CREATE TABLE `customerSurvey` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customerID` int(11) NOT NULL,
  `buyID` int(11) NOT NULL,
  `step1` tinyint(4) NOT NULL,
  `step2` tinyint(4) NOT NULL,
  `step3` tinyint(4) NOT NULL,
  `step4` tinyint(4) NOT NULL,
  `step5` tinyint(4) NOT NULL,
  `dateSubmitted` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `userAgent` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `type` tinyint(4) NOT NULL DEFAULT 1,
  `comments` text DEFAULT NULL,
  `comment` text DEFAULT NULL,
  `step6` tinyint(4) unsigned NOT NULL DEFAULT 0,
  `step6Comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1178 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `employeeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `login` varchar(20) NOT NULL,
  `password` varchar(60) NOT NULL,
  `employeeFirstName` varchar(20) NOT NULL,
  `employeeLastName` varchar(20) NOT NULL,
  `role` tinyint(2) unsigned NOT NULL DEFAULT 4,
  `averagePerContainer` decimal(10,0) NOT NULL DEFAULT 0,
  `averagePerBuy` decimal(10,0) NOT NULL DEFAULT 0,
  `active` tinyint(3) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`employeeID`)
) ENGINE=InnoDB AUTO_INCREMENT=51868033 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- ----------------------------
-- Table structure for shiftNotes
-- ----------------------------
DROP TABLE IF EXISTS `shiftNotes`;
CREATE TABLE `shiftNotes` (
  `internalID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id` varchar(10) DEFAULT NULL,
  `timeStamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `itemType` tinyint(1) unsigned DEFAULT NULL,
  `itemPriority` tinyint(1) unsigned DEFAULT NULL,
  `itemSubmitter` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `itemTitle` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `itemComment` text DEFAULT NULL,
  `itemCloser` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `itemStatus` tinyint(2) unsigned DEFAULT NULL,
  `timeClosed` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`internalID`) USING BTREE,
  KEY `timeStamp` (`timeStamp`) USING BTREE,
  KEY `id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=681 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for statsEmployeeDaily
-- ----------------------------
DROP TABLE IF EXISTS `statsEmployeeDaily`;
CREATE TABLE `statsEmployeeDaily` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `avgTotal` int(10) unsigned NOT NULL,
  `sortTime` int(10) unsigned NOT NULL,
  `sortDelay` int(11) DEFAULT 0,
  `avgPerContainer` int(10) unsigned NOT NULL,
  `numBuys` tinyint(3) unsigned NOT NULL,
  `numContainers` smallint(5) unsigned NOT NULL,
  `firstBuy` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lastBuy` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `firstSort` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lastSort` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `numSorts` smallint(5) unsigned NOT NULL DEFAULT 0,
  `sortContainers` smallint(5) unsigned NOT NULL DEFAULT 0,
  `sortTimePerContainer` int(10) unsigned NOT NULL DEFAULT 0,
  `employeeID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10987 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- ----------------------------
-- Table structure for statsStoreDaily
-- ----------------------------
DROP TABLE IF EXISTS `statsStoreDaily`;
CREATE TABLE `statsStoreDaily` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `avgDelay` int(10) unsigned NOT NULL,
  `sortDelay` int(10) unsigned NOT NULL,
  `sortTime` int(10) unsigned NOT NULL,
  `avgTotal` int(10) unsigned NOT NULL,
  `avgPerContainer` int(10) unsigned NOT NULL,
  `numBuys` tinyint(10) unsigned NOT NULL,
  `numContainers` smallint(10) unsigned NOT NULL,
  `numBuyers` tinyint(10) unsigned NOT NULL,
  `firstBuy` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lastBuy` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `buysStranded` tinyint(10) unsigned NOT NULL,
  `uniqueCustomers` tinyint(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `date_2` (`date`),
  KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=2362 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
