-- ///<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 {TABLE_PREFIX}affiliate (
  id varchar(32),
  "date" date NOT NULL,
  hits int(11) NOT NULL,
  PRIMARY KEY  (id,"date")
);

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

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

CREATE TABLE {TABLE_PREFIX}billids (
  bill_id integer,
  PRIMARY KEY (bill_id)
);

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

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

CREATE TABLE {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)
);

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

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

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

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

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

CREATE TABLE {TABLE_PREFIX}coupondefs (
  coupondef_id integer,
  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)
);

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

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

CREATE TABLE {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)
);

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

--
-- Tabellenstruktur für Tabelle `s2d6_deletedclients`
--

CREATE TABLE {TABLE_PREFIX}deleted (
  client_id mediumint(8),
  order_id int(11),
  change_id int(11),
  PRIMARY KEY (change_id)
);

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

--
-- Tabellenstruktur für Tabelle `s2d6_downloadlicenses`
--

CREATE TABLE {TABLE_PREFIX}esdlicenses (
  id varchar(20),
  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)
);

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

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

CREATE TABLE {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)
);

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

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

CREATE TABLE {TABLE_PREFIX}emails (
  email_id integer,
  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)
);

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

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

CREATE TABLE {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)
);

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

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

CREATE TABLE {TABLE_PREFIX}orderids (
  order_id integer,
  PRIMARY KEY (order_id)
);

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

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

CREATE TABLE {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,
  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)
);

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

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

CREATE TABLE {TABLE_PREFIX}osconfig (
  "mode" varchar(15) NOT NULL,
  mykey varchar(20) NOT NULL,
  "value" varchar(32) NOT NULL,
  PRIMARY KEY  ("mode",mykey)
);

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

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

CREATE TABLE {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)
);

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

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

CREATE TABLE {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)
);

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

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

CREATE TABLE {TABLE_PREFIX}templates (
  id INTEGER PRIMARY KEY,
  "status" char(1) default NULL,
  "name" varchar(100) NOT NULL,
  "subject" varchar(255) NOT NULL,
  body text NOT NULL
);

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

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

CREATE TABLE {TABLE_PREFIX}uniqueids (
  id integer,
  PRIMARY KEY (id)
);