-- ***************************************************************************************************
--
-- Seagull CMS shopping cart add-on source code
-- (C)2006 by umop ap!sdn (aka E. Lee Gagnon)
-- 
-- Module: build_cart_tables.sql
--
-- The commands in this script must be run before the shopping cart will work properly.
--
-- ***************************************************************************************************

CREATE TABLE customers
             (customer_id            BIGINT PRIMARY KEY,
              name                   VARCHAR(71),
              address                VARCHAR(71),
              city                   VARCHAR(29),
              state                  VARCHAR(3),       -- State or province. Australia has 3 letter state codes.
              zip                    VARCHAR(10),      -- Allows for 9 digit zip codes, as well as 6 
                                                       -- character Canada & UK postal codes.
              email                  VARCHAR(58)
             );
CREATE TABLE orders
             (order_id               BIGINT PRIMARY KEY,
              customer_id            BIGINT,
              when_placed            DATETIME,
              when_sent              DATETIME
             );
CREATE TABLE products
             (catalog_number         VARCHAR(255) PRIMARY KEY,  -- Allows alphanumeric catalog numbers
              description            VARCHAR(255),
              style                  VARCHAR(255),     -- Style, color, flavor, scent, platform, whatever
              sku                    VARCHAR(8),       -- Entirely optional.
              picture                VARCHAR(255),     -- Also optional, this is a URL of an image file.
              price                  FLOAT             -- Per unit retail price of the item.
             );
CREATE TABLE articleproducts
             (article_id             INT,
              catalog_number         VARCHAR(255)
             );
CREATE TABLE orderitems
             (order_id               BIGINT,
              catalog_number         VARCHAR(255),
              quantity               INT
             );
CREATE TABLE payments
             (payment_id             BIGINT,
              order_id               BIGINT,
              when_received          DATETIME,
              amount                 FLOAT,
              balance_due            FLOAT
             );
INSERT INTO permissions (permission_id, permission_desc) VALUES (71, 'MANAGE_INVENTORY');
INSERT INTO permissions (permission_id, permission_desc) VALUES (72, 'FILL_ORDERS');