text: SET NAMES utf8mb4;
  SET FOREIGN_KEY_CHECKS = 0;
  
  -- ----------------------------
  -- Table structure for customers
  -- ----------------------------
  DROP TABLE IF EXISTS `customers`;
  CREATE TABLE `customers` (
    `customerNumber` int(11) NOT NULL,
    `customerName` varchar(50) NOT NULL,
    `contactLastName` varchar(50) NOT NULL,
    `contactFirstName` varchar(50) NOT NULL,
    `phone` varchar(50) NOT NULL,
    `addressLine1` varchar(50) NOT NULL,
    `addressLine2` varchar(50) DEFAULT NULL,
    `city` varchar(50) NOT NULL,
    `state` varchar(50) DEFAULT NULL,
    `postalCode` varchar(15) DEFAULT NULL,
    `country` varchar(50) NOT NULL,
    `salesRepEmployeeNumber` int(11) DEFAULT NULL,
    `creditLimit` decimal(10,2) DEFAULT NULL,
    `status` bit(1) DEFAULT b'0',
    PRIMARY KEY (`customerNumber`),
    KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`),
    CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  -- ----------------------------
  -- Table structure for employees
  -- ----------------------------
  DROP TABLE IF EXISTS `employees`;
  CREATE TABLE `employees` (
    `employeeNumber` int(11) NOT NULL,
    `lastName` varchar(50) NOT NULL,
    `firstName` varchar(50) NOT NULL,
    `extension` varchar(10) NOT NULL,
    `email` varchar(100) NOT NULL,
    `officeCode` varchar(10) NOT NULL,
    `reportsTo` int(11) DEFAULT NULL,
    `jobTitle` varchar(50) NOT NULL,
    PRIMARY KEY (`employeeNumber`),
    KEY `reportsTo` (`reportsTo`),
    KEY `officeCode` (`officeCode`),
    CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`reportsTo`) REFERENCES `employees` (`employeeNumber`),
    CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  -- ----------------------------
  -- Table structure for offices
  -- ----------------------------
  DROP TABLE IF EXISTS `offices`;
  CREATE TABLE `offices` (
    `officeCode` varchar(10) NOT NULL,
    `city` varchar(50) NOT NULL,
    `phone` varchar(50) NOT NULL,
    `addressLine1` varchar(50) NOT NULL,
    `addressLine2` varchar(50) DEFAULT NULL,
    `state` varchar(50) DEFAULT NULL,
    `country` varchar(50) NOT NULL,
    `postalCode` varchar(15) NOT NULL,
    `territory` varchar(10) NOT NULL,
    PRIMARY KEY (`officeCode`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  -- ----------------------------
  -- Table structure for orderdetails
  -- ----------------------------
  DROP TABLE IF EXISTS `orderdetails`;
  CREATE TABLE `orderdetails` (
    `orderNumber` int(11) NOT NULL,
    `productCode` varchar(15) NOT NULL,
    `quantityOrdered` int(11) NOT NULL,
    `priceEach` decimal(10,2) NOT NULL,
    `orderLineNumber` smallint(6) NOT NULL,
    PRIMARY KEY (`orderNumber`,`productCode`),
    KEY `productCode` (`productCode`),
    CONSTRAINT `orderdetails_ibfk_1` FOREIGN KEY (`orderNumber`) REFERENCES `orders` (`orderNumber`),
    CONSTRAINT `orderdetails_ibfk_2` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  -- ----------------------------
  -- Table structure for orders
  -- ----------------------------
  DROP TABLE IF EXISTS `orders`;
  CREATE TABLE `orders` (
    `orderNumber` int(11) NOT NULL,
    `orderDate` date NOT NULL,
    `requiredDate` date NOT NULL,
    `shippedDate` date DEFAULT NULL,
    `status` varchar(15) NOT NULL,
    `comments` text,
    `customerNumber` int(11) NOT NULL,
    PRIMARY KEY (`orderNumber`),
    KEY `customerNumber` (`customerNumber`),
    CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  -- ----------------------------
  -- Table structure for payments
  -- ----------------------------
  DROP TABLE IF EXISTS `payments`;
  CREATE TABLE `payments` (
    `customerNumber` int(11) NOT NULL,
    `checkNumber` varchar(50) NOT NULL,
    `paymentDate` date NOT NULL,
    `amount` decimal(10,2) NOT NULL,
    PRIMARY KEY (`customerNumber`,`checkNumber`),
    CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  -- ----------------------------
  -- Table structure for productlines
  -- ----------------------------
  DROP TABLE IF EXISTS `productlines`;
  CREATE TABLE `productlines` (
    `productLine` varchar(50) NOT NULL,
    `textDescription` varchar(4000) DEFAULT NULL,
    `htmlDescription` mediumtext,
    `image` mediumblob,
    PRIMARY KEY (`productLine`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  -- ----------------------------
  -- Table structure for products
  -- ----------------------------
  DROP TABLE IF EXISTS `products`;
  CREATE TABLE `products` (
    `productCode` varchar(15) NOT NULL,
    `productName` varchar(70) NOT NULL,
    `productLine` varchar(50) NOT NULL,
    `productScale` varchar(10) NOT NULL,
    `productVendor` varchar(50) NOT NULL,
    `productDescription` text NOT NULL,
    `quantityInStock` smallint(6) NOT NULL,
    `buyPrice` decimal(10,2) NOT NULL,
    `MSRP` decimal(10,2) NOT NULL,
    PRIMARY KEY (`productCode`),
    KEY `productLine` (`productLine`),
    CONSTRAINT `products_ibfk_1` FOREIGN KEY (`productLine`) REFERENCES `productlines` (`productLine`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  CREATE TABLE `family` (
    `family_id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    `phase` varchar(50) DEFAULT NULL,
    `customer_id` varchar(64) DEFAULT NULL COMMENT 'comment',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_by` varchar(50) DEFAULT NULL,
    `updated_by` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`family_id`),
    KEY `family_index_16` (`created_at`),
    KEY `family_index_17` (`updated_at`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  SET FOREIGN_KEY_CHECKS = 1;
  