CREATE TABLE categories (
  category varchar(255) NOT NULL PRIMARY KEY
) DEFAULT CHARACTER SET=utf8;

CREATE TABLE customers (
  customerId varchar(255) NOT NULL PRIMARY KEY,
  employeeId int(11) NOT NULL DEFAULT '0',
  priceList tinyint(4) NOT NULL DEFAULT '0',
  taxCode varchar(255),
  `name` varchar(255),
  address varchar(255),
  city varchar(255),
  region varchar(255),
  postalCode varchar(255),
  country varchar(255),
  latitude int(11) NOT NULL DEFAULT '0',
  longitude int(11) NOT NULL DEFAULT '0',
  contact varchar(255),
  contactTitle varchar(255),
  phone varchar(255),
  email varchar(255),
  notes text,
  discount double NOT NULL DEFAULT '0',
  colour tinyint(4) NOT NULL DEFAULT '0',
  dirty tinyint(1) NOT NULL DEFAULT '0'
) DEFAULT CHARACTER SET=utf8;

CREATE TABLE employees (
  employeeId int(11) NOT NULL PRIMARY KEY,
  `name` varchar(255),
  `password` varchar(255),
  restricted tinyint(1) NOT NULL DEFAULT '0'
) DEFAULT CHARACTER SET=utf8;

CREATE TABLE orderDetails (
  employeeId int(11) NOT NULL,
  orderId int(11) NOT NULL,
  productId varchar(255) NOT NULL,
  unitPrice double NOT NULL DEFAULT '0',
  quantity double NOT NULL DEFAULT '0',
  discount double NOT NULL DEFAULT '0',
  taxCode varchar(255) DEFAULT NULL,
  taxRate double NOT NULL DEFAULT '0',
  custom varchar(255) DEFAULT NULL,
  seq int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (seq),
  UNIQUE KEY main (employeeId,orderId,productId)
) DEFAULT CHARSET=utf8;

CREATE TABLE orders (
  employeeId int(11) NOT NULL,
  orderId int(11) NOT NULL,
  customerId varchar(255),
  orderDate varchar(255),
  requiredDate varchar(255),
  shipper varchar(255),
  freight double NOT NULL DEFAULT '0',
  shipName varchar(255),
  shipAddress varchar(255),
  shipCity varchar(255),
  shipRegion varchar(255),
  shipPostalCode varchar(255),
  shipCountry varchar(255),
  notes text,
  stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dirty tinyint(1) NOT NULL DEFAULT '0',
  custom varchar(255),
  `type` tinyint(4) NOT NULL DEFAULT '0',
  latitude int(11) NOT NULL DEFAULT '0',
  longitude int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (employeeId,orderId)
) DEFAULT CHARACTER SET=utf8;

CREATE TABLE products (
  productId varchar(255) NOT NULL PRIMARY KEY,
  `name` varchar(255),
  category varchar(255),
  barcode varchar(255),
  price double NOT NULL DEFAULT '0',
  price1 double NOT NULL DEFAULT '0',
  price2 double NOT NULL DEFAULT '0',
  price3 double NOT NULL DEFAULT '0',
  price4 double NOT NULL DEFAULT '0',
  taxCode varchar(255),
  discontinued tinyint(1) NOT NULL DEFAULT '0',
  notes text,
  banding varchar(255),
  stock double NOT NULL DEFAULT '0'
) DEFAULT CHARACTER SET=utf8;

CREATE TABLE shippers (
  shipper varchar(255) NOT NULL PRIMARY KEY
) DEFAULT CHARACTER SET=utf8;

CREATE TABLE specialPrices (
  customerId varchar(255) NOT NULL,
  productId varchar(255) NOT NULL,
  price double NOT NULL DEFAULT '0',
  description varchar(255),
  PRIMARY KEY (customerId(100),productId(100))
) DEFAULT CHARACTER SET=utf8;

CREATE TABLE taxRates (
  `code` varchar(255) NOT NULL PRIMARY KEY,
  description varchar(255),
  rate double NOT NULL DEFAULT '0',
  seq int(11) NOT NULL DEFAULT '0'
) DEFAULT CHARACTER SET=utf8;

CREATE TABLE custom (
  section tinyint(4) NOT NULL DEFAULT '0',
  seq tinyint(4) NOT NULL DEFAULT '0',
  `type` tinyint(4) NOT NULL DEFAULT '0',
  `name` varchar(255),
  vals varchar(255),
  PRIMARY KEY (section,seq)
) DEFAULT CHARACTER SET=utf8;