-- Migration generated on: 2025-01-31 15:23:38

-- Operation: create_table
-- Description: Create service queue table
CREATE TABLE `serviceQueue` (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `dailyNum` int(11) NOT NULL,
                  `customerID` int(11) unsigned NOT NULL,
                  `timeEntered` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                  `timeStarted` TIMESTAMP NULL DEFAULT NULL,
                  `timeCompleted` TIMESTAMP NULL DEFAULT NULL,
                  `employeeID` int(10) unsigned DEFAULT NULL,
                  `textMe` tinyint(1) DEFAULT 0,
                  `numItems` int(11) DEFAULT 0,
                  `textSent` tinyint(1) DEFAULT 0,
                  `isFinished` tinyint(1) DEFAULT 0,
                  PRIMARY KEY (`id`),
                  INDEX `idx_customer` (`customerID`),
                  KEY `employeeID` (`employeeID`),
                  CONSTRAINT `fk_servicequeue_customer` 
                    FOREIGN KEY (`customerID`) 
                    REFERENCES `{{DATABASE}}`.`customers` (`customerID`) 
                    ON DELETE RESTRICT ON UPDATE CASCADE,
                  CONSTRAINT `fk_servicequeue_employee` 
                    FOREIGN KEY (`employeeID`) 
                    REFERENCES `{{DATABASE}}`.`employees` (`employeeID`) 
                    ON DELETE RESTRICT ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Operation: alter_table
-- Description: Modify driversLicense column in customers table
ALTER TABLE `customers` 
                 MODIFY COLUMN `driversLicense` varchar(50) NULL 
                 AFTER `onEmail`;

-- Operation: alter_table
-- Description: Modify address fields in customers table
ALTER TABLE `customers`
                    MODIFY COLUMN `address` varchar(255) NULL,
                    MODIFY COLUMN `city` varchar(50) NULL,
                    MODIFY COLUMN `state` varchar(2) NULL,
                    MODIFY COLUMN `zipcode` varchar(5) NULL;

