-- ///<09.03.2010/7.0.0.11/>
-- /// Letzte Änderungen:
-- /// 30.01.2008 timestamp in version
-- /// 10.02.2008 Tabelle version wird zu config
-- /// 16.02.2009 Änderungen Tabelle Gutscheindefinition
-- /// 16.04.2009 Anpassung Postleitzahlen
-- /// 21.09.2009 Wetebereich Kundennummer erhöht
-- /// 09.02.2010 Alle Migrationspfade für s2d7 eingebaut
-- /// 09.03.2010 item.caption -> text

--
-- Datenbank: `s2d7`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_affiliate`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}affiliate` (
  `id` varchar(32) NOT NULL,
  `date` date NOT NULL,
  `hits` int(11) NOT NULL,
  PRIMARY KEY  (`id`,`date`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_billids`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}billids` (
  `bill_id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = MYISAM ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_clients`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}clients` (
  `client_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL default '0',
  `email` varchar(50) NOT NULL default '',
  `password` varchar(32) default NULL,
  `shipping_uid` varchar(20) default NULL,
  `payment_uid` varchar(20) default NULL,
  `salutation` varchar(10) NOT NULL default '',
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `company` varchar(50) default NULL,
  `street` varchar(30) NOT NULL default '',
  `streetnumber` varchar(10) NOT NULL,
  `zip` varchar(10) NOT NULL,
  `city` varchar(30) NOT NULL default '',
  `country` varchar(60) NOT NULL default '',
  `phone` varchar(30) default NULL,
  `cellphone` varchar(30) default NULL,
  `fax` varchar(30) default NULL,
  `custom1` varchar(50) default NULL,
  `custom2` varchar(50) default NULL,
  `custom3` varchar(50) default NULL,
  `custom4` varchar(50) default NULL,
  `custom5` varchar(50) default NULL,
  `newsletter` tinyint(1) NOT NULL default '0',
  `reliability` tinyint(2) NOT NULL default '0',
  `novat` tinyint(1) NOT NULL default '0',
  `rebate_amount` decimal(8,2) NOT NULL default '0.00',
  `rebate_absolute` tinyint(1) NOT NULL default '0',
  `coupon_id` varchar(32) NULL default NULL,
  `custom_options` text default NULL,
  `deviating_shipping_address` tinyint(1) NOT NULL default '0',
  `shipping_salutation` varchar(10) default NULL,
  `shipping_firstname` varchar(30) default NULL,
  `shipping_lastname` varchar(30) default NULL,
  `shipping_company` varchar(50) default NULL,
  `shipping_street` varchar(30) default NULL,
  `shipping_streetnumber` varchar(10) default NULL,
  `shipping_zip` varchar(10) default NULL,
  `shipping_city` varchar(30) default NULL,
  `shipping_country` varchar(60) default NULL,
  `bank_accountnumber` varchar(60) default NULL,
  `bank_accountholder` varchar(30) default NULL,
  `bank_name` varchar(30) default NULL,
  `bank_code` varchar(30) default NULL,
  `credit_accountnumber` varchar(60) default NULL,
  `credit_validuntil` varchar(30) default NULL,
  `credit_accountholder` varchar(30) default NULL,
  `credit_cvc` varchar(6) default NULL,
  `daystopayment` smallint(6) default NULL,
  `message` text,
  `change_id` int(11) default NULL,
  PRIMARY KEY  (`client_id`,`order_id`),
  KEY (`change_id`),
  KEY (`email`),
  KEY (`firstname` , `lastname`),
  KEY (`password`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_config`
--

CREATE TABLE `{TABLE_PREFIX}config` (
   `config_key` varchar(10),
   `config_value` int(11),
   PRIMARY KEY (`config_key`)
) TYPE=MyISAM ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_coupondefs`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}coupondefs` (
  `coupondef_id` smallint(6) NOT NULL auto_increment,
  `item_price` decimal(8, 2) default NULL,
  `item_uid` varchar(32) default NULL,
  `rebate_amount` decimal(8, 2) default 0,
  `rebate_absolute` tinyint(1) default 0,
  `date_from` date default NULL,
  `date_until` date default NULL,
  `subtotal` decimal(8,2) default NULL,
  `caption` varchar(50) default NULL,
  PRIMARY KEY  (`coupondef_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_coupons`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}coupons` (
  `coupon_id`varchar(32) NOT NULL,
  `coupondef_id` smallint(6) NOT NULL,
  `client_email` varchar(50) default NULL,
  `order_id` int(11) default NULL,
  `client_id` mediumint(9) default NULL,
  `mode` TINYINT( 1 ) NOT NULL default '1',
  PRIMARY KEY  (`coupon_id`),
  KEY (`order_id` , `client_id`),
  KEY (`coupondef_id`),
  KEY (`mode`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle 's2d6_deletedclients'
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}deleted` (
  `client_id` mediumint(8),
  `order_id` int(11),
  `change_id` int(11) NOT NULL,
  PRIMARY KEY (`change_id`),
  KEY (`client_id`, `order_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_downloadlicences`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}esdlicenses` (
  `id` varchar(20) NOT NULL,
  `order_id` int(10) NOT NULL,
  `date` datetime NOT NULL,
  `hardware_id` varchar(100) NOT NULL,
  `license_code` varchar(100) default NULL,
  `client_comment` varchar(255) default NULL,
  `license_pending` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`,`order_id`,`hardware_id`),
  KEY (`license_pending`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_downloadproducts`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}downloadproducts` (
  `id` varchar(20) NOT NULL,
  `caption` varchar(100) NOT NULL,
  `filename` varchar(100) NOT NULL,
  `pid` varchar(50) default NULL,
  `order_count` smallint(6) NOT NULL default '0',
  `download_count` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY (`pid`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_emails`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}emails` (
  `email_id` mediumint(9) NOT NULL auto_increment,
  `client_id` mediumint(9) default NULL,
  `order_id` int(11) default NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `to_email` varchar(50) NOT NULL,
  `to_plain` varchar(50) default NULL,
  `from_email` varchar(50) NOT NULL,
  `from_plain` varchar(50) default NULL,
  `subject` varchar(255) NOT NULL,
  `content_plain` text NOT NULL,
  `content_html` text,
  PRIMARY KEY  (`email_id`),
  KEY (`client_id`),
  KEY (`order_id`),
  KEY (`date`, `time`)
) TYPE=MyISAM ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_items`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}items` (
  `order_id` varchar(32) NOT NULL default '0',
  `order_status` char(1) default NULL,
  `uid` varchar(36),
  `position` smallint(6),
  `position_main` smallint(6) default NULL,
  `id` varchar(32) default NULL,
  `quantity` decimal(7,3) NOT NULL,
  `qprecision` tinyint(1) NOT NULL default '0',
  `caption` text NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `vat` decimal(4,2) NOT NULL,
  `rebatable` tinyint(1) NOT NULL default '1',
  `url` text NOT NULL,
  `unit` varchar(10) NOT NULL,
  `noship` varchar(255) default NULL,
  `weight` decimal(7,2) NOT NULL,
  `rebate` varchar(100) default NULL,
  `status` tinyint(1) default '1',
  `minimum` decimal(7,3) default NULL,
  `maximum` decimal(7,3) default NULL,
  `variationa_caption` varchar(50) default NULL,
  `variationb_caption` varchar(50) default NULL,
  `variationa` tinyint(1) default '0',
  `variationb` tinyint(1) default '0',
  `stockserver` tinyint(1) default '0',
  `specialinfo` text,
  `download_uid` varchar(20) default NULL,
  `download_pid` varchar(50) default NULL,
  `download_filename` varchar(100) default NULL,
  `download_count` smallint(6) default NULL,
  `memo` tinyint(1) default '0',
  PRIMARY KEY  (`order_id`,`position`, `memo`),
  KEY (`order_id`),
  KEY (`uid`),
  KEY (`download_uid`),
  KEY (`order_status`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_orderids`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}orderids` (
  `order_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`order_id`)
) ENGINE = MYISAM ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_orders`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}orders` (
  `order_id` int(11) NOT NULL,
  `order_id_prefix` varchar(10) NULL default NULL,
  `order_date` date NOT NULL,
  `order_time` time NOT NULL,
  `bill_id` int(11) NULL default NULL,
  `bill_id_prefix` varchar(10) NULL default NULL,
  `bill_date` date NULL default NULL,
  `b2b` tinyint(1) NOT NULL default '0',
  `currency` varchar(10) NOT NULL,
  `sumrebate_amount` decimal(8,2) NOT NULL default '0.00',
  `sumrebate_absolute` tinyint(1) NOT NULL default '0',
  `couponrebate_amount` decimal(8,2) NOT NULL default '0.00',
  `couponrebate_absolute` tinyint(1) NOT NULL default '0',
  `paymentrebate_amount` DECIMAL( 8, 2 ) NOT NULL default '0.00',
  `paymentrebate_absolute` TINYINT( 1 ) NOT NULL default '0',
  `coupon_id` varchar(32) default NULL,
  `coupon_caption` varchar(50) default NULL,
  `shipping_cost` decimal(8,2) NOT NULL default '0.00',
  `shipping_vat` decimal(4,2) NOT NULL,
  `shipping_caption` varchar(50) NOT NULL,
  `shipping_trackingurl` varchar(255) default NULL,
  `payment_amount` DECIMAL( 8, 2 ) NOT NULL default '0.00',
  `payment_absolute` TINYINT( 1 ) NOT NULL default '0',
  `payment_vat` decimal(4,2) NOT NULL,
  `payment_caption` varchar(50) NOT NULL,
  `payment_param` varchar(20) default NULL,
  `status` char(1) NOT NULL default 'a',
  `expected_date` date default NULL,
  `shipping_date` date default NULL,
  `payment_date` date default NULL,
  `message` text,
  `tracking_id` varchar(100) default NULL,
  `affiliate_id` varchar(32) default NULL,
  `affiliate_subid` varchar(32) default NULL,
  `pdf_creation` tinyint(1) not null default 0,
  `change_id` int(11) NOT NULL,
  PRIMARY KEY  (`order_id`),
  KEY (`change_id`),
  KEY (`order_date`),
  KEY (`affiliate_id`),
  KEY (`order_time`),
  KEY (`status`),
  KEY (`bill_id`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_osconfig`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}osconfig` (
  `mode` varchar(15) NOT NULL,
  `mykey` varchar(20) NOT NULL,
  `value` varchar(32) NOT NULL,
  PRIMARY KEY  (`mode`,`mykey`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_products`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}stock` (
  `id` varchar(20) NOT NULL default '',
  `variationa` tinyint(4) NOT NULL default '0',
  `variationb` tinyint(4) NOT NULL default '0',
  `stock` decimal(9,3) NOT NULL,
  `live` tinyint(1) default '1',
  PRIMARY KEY  (`id`,`variationa`,`variationb`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_sessions`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}sessions` (
  `session_id` varchar(32) NOT NULL default '',
  `session_time` int(11) NOT NULL,
  `securekey` varchar(32) default NULL,
  PRIMARY KEY  (`session_id`),
  KEY (`session_time`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_templates`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}templates` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `status` char(1) default NULL,
  `name` varchar(100) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY (`status`),
  KEY (`name`)
) TYPE=MyISAM ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `s2d6_uniqueids`
--

CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}uniqueids` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM ;