-- $DEFINE DELIMITER ;

DROP TABLE "Order Details";

CREATE TABLE "Order Details" (
       OrderID              INTEGER NOT NULL,
       ProductID            INTEGER NOT NULL,
       UnitPrice            DECIMAL(19,4) NOT NULL DEFAULT 0,
       Quantity             SMALLINT NOT NULL DEFAULT 1,
       Discount             REAL NOT NULL DEFAULT 0
);

DROP TABLE "Products";

CREATE TABLE "Products" (
       ProductID            INTEGER NOT NULL GENERATED BY DEFAULT
                            AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ),
       ProductName          VARGRAPHIC(40) NOT NULL,
       SupplierID           INTEGER,
       CategoryID           INTEGER,
       QuantityPerUnit      VARGRAPHIC(20),
       UnitPrice            DECIMAL(15,5) DEFAULT 0,
       UnitsInStock         SMALLINT DEFAULT 0,
       UnitsOnOrder         SMALLINT DEFAULT 0,
       ReorderLevel         SMALLINT DEFAULT 0,
       Discontinued         DECIMAL(1,0) NOT NULL DEFAULT 0
);

DROP TABLE "Categories";

CREATE TABLE "Categories" (
       CategoryID           INTEGER NOT NULL GENERATED BY DEFAULT
                            AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ),
       CategoryName         VARGRAPHIC(15) NOT NULL,
       Description          LONG VARGRAPHIC,
       Picture              BLOB(1048576)
);

DROP TABLE "Suppliers";

CREATE TABLE "Suppliers" (
       SupplierID           INTEGER NOT NULL GENERATED BY DEFAULT
                            AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ),
       CompanyName          VARGRAPHIC(40) NOT NULL,
       ContactName          VARGRAPHIC(30),
       ContactTitle         VARGRAPHIC(30),
       Address              VARGRAPHIC(60),
       City                 VARGRAPHIC(15),
       Region               VARGRAPHIC(15),
       PostalCode           VARGRAPHIC(10),
       Country              VARGRAPHIC(15),
       Phone                VARGRAPHIC(24),
       Fax                  VARGRAPHIC(24),
       HomePage             LONG VARGRAPHIC
);

DROP TABLE "EmployeeTerritories";

CREATE TABLE "EmployeeTerritories" (
       EmployeeID           INTEGER NOT NULL,
       TerritoryID          VARGRAPHIC(20) NOT NULL
);

DROP TABLE "Territories";

CREATE TABLE "Territories" (
       TerritoryID          VARGRAPHIC(20) NOT NULL,
       TerritoryDescription GRAPHIC(50) NOT NULL,
       RegionID             INTEGER NOT NULL
);

DROP TABLE "Region";

CREATE TABLE "Region" (
       RegionID             INTEGER NOT NULL GENERATED BY DEFAULT
                            AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ),
       RegionDescription    GRAPHIC(50) NOT NULL
);

DROP TABLE "Orders";

CREATE TABLE "Orders" (
       OrderID              INTEGER NOT NULL GENERATED BY DEFAULT
                            AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ),
       CustomerID           GRAPHIC(5),
       EmployeeID           INTEGER,
       OrderDate            DATE,
       RequiredDate         DATE,
       ShippedDate          DATE,
       ShipVia              INTEGER,
       Freight              DECIMAL(15,5) DEFAULT 0,
       ShipName             VARGRAPHIC(40),
       ShipAddress          VARGRAPHIC(60),
       ShipCity             VARGRAPHIC(15),
       ShipRegion           VARGRAPHIC(15),
       ShipPostalCode       VARGRAPHIC(10),
       ShipCountry          VARGRAPHIC(15)
);

DROP TABLE "Employees";

CREATE TABLE "Employees" (
       EmployeeID           INTEGER NOT NULL GENERATED BY DEFAULT
                            AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ),
       LastName             VARGRAPHIC(20) NOT NULL,
       FirstName            VARGRAPHIC(10) NOT NULL,
       Title                VARGRAPHIC(30),
       TitleOfCourtesy      VARGRAPHIC(25),
       BirthDate            DATE,
       HireDate             DATE,
       Address              VARGRAPHIC(60),
       City                 VARGRAPHIC(15),
       Region               VARGRAPHIC(15),
       PostalCode           VARGRAPHIC(10),
       Country              VARGRAPHIC(15),
       HomePhone            VARGRAPHIC(24),
       Extension            VARGRAPHIC(4),
       Photo                BLOB,
       Notes                VARGRAPHIC(18),
       ReportsTo            INTEGER,
       PhotoPath            VARGRAPHIC(255)
);

DROP TABLE "Shippers";

CREATE TABLE "Shippers" (
       ShipperID            INTEGER NOT NULL GENERATED BY DEFAULT
                            AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ),
       CompanyName          VARGRAPHIC(40) NOT NULL,
       Phone                VARGRAPHIC(24)
);

DROP TABLE "CustomerCustomerDemo";

CREATE TABLE "CustomerCustomerDemo" (
       CustomerID           GRAPHIC(5) NOT NULL,
       CustomerTypeID       GRAPHIC(10) NOT NULL
);

DROP TABLE "CustomerDemographics";

CREATE TABLE "CustomerDemographics" (
       CustomerTypeID       GRAPHIC(10) NOT NULL,
       CustomerDesc         LONG VARGRAPHIC
);

DROP TABLE "Customers";

CREATE TABLE "Customers" (
       CustomerID           GRAPHIC(5) NOT NULL,
       CompanyName          VARGRAPHIC(40) NOT NULL,
       ContactName          VARGRAPHIC(30),
       ContactTitle         VARGRAPHIC(30),
       Address              VARGRAPHIC(60),
       City                 VARGRAPHIC(15),
       Region               VARGRAPHIC(15),
       PostalCode           VARGRAPHIC(10),
       Country              VARGRAPHIC(15),
       Phone                VARGRAPHIC(24),
       Fax                  VARGRAPHIC(24)
);    
