/**
 * shop tables
 *
 * Additional tables for:
 *  - article manufactorer
 *  - article multiplier
 *  - article supplier
 *
 */

/*
 * shop articles class
 */
CREATE TABLE wbshoparticleclass (
  sacid int(11) unsigned NOT NULL AUTO_INCREMENT,
  path varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  orderpos int(11) NOT NULL DEFAULT '0',
  title varchar(200) NOT NULL DEFAULT '',
  brief TEXT NOT NULL DEFAULT '',
  vfsfid int(11) unsigned NOT NULL DEFAULT '0',
  uid int(11) unsigned NOT NULL DEFAULT '0',
  changed` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  created` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY (`sacid`),
  KEY (`path`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Wombat Shop Article Classes';


/*
 * shop articles
 */
CREATE TABLE `wbshoparticle` (
    said int(11) UNSIGNED NOT NULL auto_increment
     , sacid int(11) UNSIGNED NOT NULL DEFAULT 0
     , rate DECIMAL(12,2) NOT NULL DEFAULT 0
     , title varchar(200) NOT NULL DEFAULT ''
     , unit ENUM('', 'pcs', 'volume', 'mass') NOT NULL DEFAULT 'pcs'
     , samid int(11) UNSIGNED NOT NULL DEFAULT 1
     , minquantity SMALLINT(11) UNSIGNED NOT NULL DEFAULT 1
     , tradename VARCHAR(200) NOT NULL DEFAULT ''
     , vfsfid INT(11) UNSIGNED NOT NULL DEFAULT 0 -- Thumbnail VFS media fila
     , brief VARCHAR(255) NOT NULL DEFAULT ''
     , blurb TEXT NOT NULL DEFAULT ''
     , state ENUM('', 'new') NOT NULL DEFAULT ''
     , availability ENUM('instock', 'soldout', 'datefrom', 'datebetween', 'dateto') NOT NULL DEFAULT 'instock'
     , availabilitystart DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
     , availabilityend DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
     , changed DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
     , created DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
     , enabled tinyint(1) NOT NULL DEFAULT 1
     , deleted tinyint(1) NOT NULL DEFAULT 0
     , PRIMARY KEY said (said)
     , KEY tradename (tradename)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Wombat Shop Article';

CREATE TABLE `wbshoparticlemultiplier` (
    `samid` int(11) UNSIGNED NOT NULL
    , multiplier VARCHAR(200) NOT NULL DEFAULT ''
    , factor SMALLINT(11) UNSIGNED NOT NULL DEFAULT 1
    , brief VARCHAR(255) NOT NULL DEFAULT '' -- brief description
    , blurb TEXT NOT NULL DEFAULT '' -- exhaustive description
    , vfsfid INT(11) UNSIGNED NOT NULL DEFAULT 0 -- VFS media fila
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='article quantity multiplier for ordering';

INSERT INTO `wbshoparticlemultiplier` (samid,multiplier,factor)
    VALUES
        (1, "single", 1)
        ;

/*
 * shop article media references
 */
CREATE TABLE `wbshoparticlemedia` (
    `said` INT(11) UNSIGNED NOT NULL
     , vfsfid INT(11) UNSIGNED NOT NULL DEFAULT 0 -- VFS media fila
     , KEY `said` (`said`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='shop article media references';


/*
 * shopping cart
 */
CREATE TABLE `wbshopcart` (
  `scid` int(11) UNSIGNED NOT NULL auto_increment
  , `uid` int(11) UNSIGNED NOT NULL DEFAULT 0,
  , `title` varchar(255) NOT NULL DEFAULT ''
  , `note` text NOT NULL DEFAULT ''
  , `created` datetime NOT NULL default '1970-01-01 00:00:00'
  , `changed` datetime NOT NULL default '1970-01-01 00:00:00'
  , `status` ENUM('saved', 'active', 'ordered', 'postponed', 'verified', 'prepared', 'shipped', 'canceled', 'done') NOT NULL DEFAULT 'saved'
  , `public` tinyint(1) NOT NULL DEFAULT 0
  , `deleted` tinyint(1) NOT NULL DEFAULT 0
  , PRIMARY KEY  (`scid`)
  , KEY ( `uid` )
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='shopping cart';


/*
 * items in shoppging cart
 */
CREATE TABLE `wbshopcartitem` (
  `scid` int(11) UNSIGNED NOT NULL
  , `said` int(11) UNSIGNED NOT NULL
  , `namespace` char(50) NOT NULL DEFAULT ''
  , `comment` text NOT NULL DEFAULT ''
  , `created` datetime NOT NULL default '1970-01-01 00:00:00'
  , `quantity` int(11) NOT NULL default 1
  , PRIMARY KEY (scid, said, namespace)
  , KEY (`scid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='items shopping cart';
