CREATE TABLE amazon_items (
    "company_id" int
        NOT NULL,                         -- Reference to company_master.company_id
    "amazon_sku"    varchar(32)
        NOT NULL,               -- User assigned item code/part number.  This code is indepenent of the local item code.  This allows a cross reference to an Amazon third party seller's SKU.
    "amazon_asin"      varchar(32),         -- Amazon's assigned item number.  This is the Amazon-specific item code reference.  This is a unqiue record identifier at Amazon.
    "product_name"    text,
    "price"    numeric(15,5),      -- This price field indicates the item's retail price on Amazon.  It is not tied to any other pricing scheme within Venture.  It's a static value.
    "quantity_override" integer,   -- In normal operations, the Amazon inventory updates send a calculated quantity free value derived from Venture's item_warehouse table.  In some circumstances a seller may wish to override the actual quantity free value.  Some examples include pre-selling or direct shipment items.  Typically, Amazon inventory levels are updated hourly.  When sales occur on Amazon, Amazon will reduce your on-hand by the quantity sold.  Keep that in mind -- a popular drop ship or pre-sale item may need a relatively large number in this field.
    "suspend_updates"   BOOLEAN
        DEFAULT false,          -- A value of TRUE will Suspend updates to Amazon for this item.
    "date_added"        timestamp       -- Timestamp for record creation.
        DEFAULT current_timestamp,
    "item_code"   varchar(32)
        NOT NULL,               -- Venture item code reference item_master.item_code.
    "lead_time_override" int,            -- Lead time override in days; An Amazon seller indicates a default lead time in days via Amazon seller preferences.  However, individual items may have longer or shorter lead times based on this override value.  Although Amazon typically presents lead time in a range of dates, it only accepts an integer value in the update.  A value of 1 (one) will typically result in "usually ships in 1-2 days" availability message on the item detail record at amazon.com.
FOREIGN KEY (company_id)
REFERENCES company_master,
FOREIGN KEY (company_id,item_code)
REFERENCES item_master
ON UPDATE cascade ON DELETE cascade
);