# /*
# * meinshop_create.sql
# *
# * ZHW Zuercher Hochschule Winterthur
# * PhPepperShop Diplomarbeit DA Fei01/1
# * Autoren: Jose Fontanil & Reto Glanzmann
# *
# * Version 1.06, basierend auf ER-Diagramm v.1.05
# *
# * CVS-Version / Datum: $Id: template_create.sql,v 1.35 2001/11/23 20:02:20 fontajos Exp $
# *
# * Initialisiert die Shop-Datenbank 'meinshop' (getestet fuer MySQL)
# *
# * einlesen z.B. mit mysql -u {grantor} -p < ./meinshop_create.sql
# * (wobei mysql im Pfad sein muss. Datenbank Hostrechner: {hostname})
# *
# * Sicherheitsstatus:       *** ADMIN ***
# *
# * ------------------------------------------------------------------------------
# * Lizenz
# * ======
# * PhPepperShop Shopsystem
# * Copyright (C) 2001  Reto Glanzmann, José Fontanil
#
# * This program is free software; you can redistribute it and/or modify
# * it under the terms of the GNU General Public License as published by
# * the Free Software Foundation; either version 2 of the License, or
# * (at your option) any later version.
#
# * This program is distributed in the hope that it will be useful,
# * but WITHOUT ANY WARRANTY; without even the implied warranty of
# * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# * GNU General Public License for more details.
#
# * You should have received a copy of the GNU General Public License
# * along with this program; if not, write to the Free Software
# * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
# * ------------------------------------------------------------------------------
# */

# /*
# * ------------------------------------------------------------------------------
# *
# * Nun wird die Shop-Datenbank erzeugt --> meinshop
# * Es werden folgende Tabellen erzeugt:
# *    artikel, artikel_optionen, artikel_variationen, kategorien,
# *    bestellung, artikel_bestellung, artikel_kategorien,
# *    hilfe, shop_settings, versandkostenpreise, css_file
# */

# /*
# * ------------------------------------------------------------------------------
# *
# * Anm. Artikel_Nr: Die Artikel_Nr ist hier NICHT per UNIQUE INDEX vor Duplikaten
# * geschuetzt. Dies aus der Entscheidung heraus, dass wir hier die Artikel_Nr. gleich-
# * zeitig auch als Hersteller-Bestell-Nr benutzen kann (Dies ist oft der Fall bei
# * kleineren Shops, die auf diese Weise ihre Bestellnummern verwalten.
# *
# */

CREATE DATABASE IF NOT EXISTS meinshop;

use meinshop;

CREATE TABLE artikel
(
       Artikel_ID INT NOT NULL DEFAULT 0 AUTO_INCREMENT,
       Artikel_Nr VARCHAR(128) NOT NULL,
       Name VARCHAR(128) NOT NULL,
       Beschreibung TEXT NOT NULL,
       Preis DOUBLE NOT NULL DEFAULT 0.0,
       Aktionspreis DOUBLE NOT NULL DEFAULT 0.0,
       Aktionspreis_verwenden enum('N','Y') NOT NULL DEFAULT 'N',
       Gewicht DOUBLE NOT NULL DEFAULT 0.0,
       Link VARCHAR(255),
       Bild_gross MEDIUMBLOB,  #/* Achtung: Bei anderer DB, Typ ev. = BLOB */
       Bild_klein BLOB,
       Bildtyp VARCHAR(15),
       Bild_last_modified TIMESTAMP(14),
       Lagerbestand INT NOT NULL DEFAULT 0,
       letzteAenderung DATE,
       PRIMARY KEY (Artikel_ID),
       UNIQUE UC_Artikel_ID (Artikel_ID)
);

CREATE TABLE kategorien
(
       Kategorie_ID INT NOT NULL DEFAULT 0 AUTO_INCREMENT,
       Positions_Nr INT NOT NULL DEFAULT 0,
       Name VARCHAR(128) NOT NULL,
       Beschreibung TEXT NOT NULL,
       Bild_gross MEDIUMBLOB,  #/* Achtung: Bei anderer DB, Typ ev. = BLOB */
       Bild_klein BLOB,
       Bildtyp VARCHAR(15),
       Bild_last_modified TIMESTAMP(14),
       Unterkategorie_von VARCHAR(128) DEFAULT NULL,
       PRIMARY KEY (Kategorie_ID),
       UNIQUE UC_Kategorie_ID (Kategorie_ID),
       INDEX IDX_kategorien_1 (Name)
);

CREATE TABLE bestellung
(
       Bestellungs_ID INT NOT NULL DEFAULT 0 AUTO_INCREMENT,
       Session_ID CHAR(32) NOT NULL DEFAULT 0,
       # /* Unsere getesteten Session_IDs waren immer CHAR(32)... aber man weiss ja nie */
       expired BIGINT NOT NULL DEFAULT 0, # /* In andernen DBs ev. anderer Typ (10 Stellen) */
       Datum Date,
       Endpreis DOUBLE NOT NULL DEFAULT 0.0,
       # /* Veraltete Kundendaten, werden naechstens entfernt, liegen jetzt in der Tabelle kunde */
       Firma CHAR(128) NOT NULL,
       Anrede CHAR(24) NOT NULL,
       Abteilung CHAR(128) NOT NULL,
       Vorname CHAR(128) NOT NULL,
       Name CHAR(128) NOT NULL,
       Adresse1 CHAR(128) NOT NULL,
       Adresse2 CHAR(128) NOT NULL,
       Telefon CHAR(32) NOT NULL,
       Email CHAR(128) NOT NULL,
       PLZ CHAR(32) NOT NULL,
       Ort CHAR(64) NOT NULL,
       Land CHAR(40) NOT NULL,
       # /* Ende der veralteten Kundendaten */
       Anmerkung TEXT,
       Bezahlungsart CHAR(32) NOT NULL,
       Kreditkarten_Hersteller CHAR(32) NOT NULL,
       Kreditkarten_Nummer CHAR(16) NOT NULL,
       Kreditkarten_Ablaufdatum VARCHAR(10) NOT NULL,
       Kreditkarten_Vorname VARCHAR(128) NOT NULL,
       Kreditkarten_Nachname VARCHAR(128) NOT NULL,
       Attribut1 VARCHAR(128) NOT NULL,
       Attribut2 VARCHAR(128) NOT NULL,
       Attribut3 VARCHAR(128) NOT NULL,
       Attribut4 VARCHAR(128) NOT NULL,
       Attributwert1 VARCHAR(128) NOT NULL,
       Attributwert2 VARCHAR(128) NOT NULL,
       Attributwert3 VARCHAR(128) NOT NULL,
       Attributwert4 VARCHAR(128) NOT NULL,
       Versandkosten DOUBLE NOT NULL DEFAULT 0.0,
       Nachnamebetrag DOUBLE NOT NULL DEFAULT 0.0,
       Mindermengenzuschlag DOUBLE NOT NULL DEFAULT 0.0,
       Rechnungsbetrag DOUBLE NOT NULL DEFAULT 0.0,
       Bestellung_abgeschlossen enum('N','Y') NOT NULL DEFAULT 'N',
       Bestellung_ausgeloest enum('N','Y') NOT NULL DEFAULT 'N',
       Bestellung_bezahlt enum('N','Y') NOT NULL DEFAULT 'N',
       temp_message_string TEXT NOT NULL,
       PRIMARY KEY (Bestellungs_ID),
       UNIQUE UC_Bestellungs_ID (Bestellungs_ID)
);

CREATE TABLE kunde
(
       k_ID INT NOT NULL DEFAULT 0 AUTO_INCREMENT,
       Kunden_ID VARCHAR(255) NOT NULL,
       Kunden_Nr VARCHAR(128) NOT NULL DEFAULT 0,
       Session_ID CHAR(32) NOT NULL DEFAULT 0,
       Anrede VARCHAR(24) NOT NULL,
       Vorname VARCHAR(128) NOT NULL,
       Nachname VARCHAR(128) NOT NULL,
       Firma VARCHAR(128) NOT NULL,
       Abteilung VARCHAR(128) NOT NULL,
       Strasse VARCHAR(128) NOT NULL,
       Postfach VARCHAR(16) NOT NULL,
       PLZ VARCHAR(32) NOT NULL,
       Ort VARCHAR(128) NOT NULL,
       Land VARCHAR(128) NOT NULL,
       Tel VARCHAR(32) NOT NULL,
       Fax VARCHAR(32) NOT NULL,
       Email VARCHAR(128) NOT NULL,
       Login VARCHAR(32) NOT NULL,
       Passwort VARCHAR(32) NOT NULL,
       gesperrt enum('N','Y') NOT NULL DEFAULT 'N',
       temp enum('N','Y') NOT NULL DEFAULT 'Y',
       Bezahlungsart VARCHAR(32) NOT NULL,
       AnmeldeDatum Date,
       LetzteBestellung Date,
       Einkaufsvolumen DOUBLE NOT NULL DEFAULT 0.0,
       #/* Attribute sind selbstkonfigurierbare Eintraege */
       Attribut1 VARCHAR(128) NOT NULL,
       Attribut2 VARCHAR(128) NOT NULL,
       Attribut3 VARCHAR(128) NOT NULL,
       Attribut4 VARCHAR(128) NOT NULL,
       Attributwert1 VARCHAR(128) NOT NULL,
       Attributwert2 VARCHAR(128) NOT NULL,
       Attributwert3 VARCHAR(128) NOT NULL,
       Attributwert4 VARCHAR(128) NOT NULL,
       PRIMARY KEY (k_ID),
       UNIQUE UC_k_ID (k_ID),
       UNIQUE UC_Kunden_ID (Kunden_ID),
       UNIQUE UC_Login (Login),
       INDEX IDX_Kunden_ID_1 (Kunden_ID)
);

CREATE TABLE attribut
(
       Attribut_ID INT NOT NULL DEFAULT 0 AUTO_INCREMENT,
       Name VARCHAR(128) NOT NULL,
       Wert VARCHAR(128) NOT NULL,
       anzeigen enum('N','Y') NOT NULL DEFAULT 'Y',
       in_DB enum('N','Y') NOT NULL DEFAULT 'Y',
       Eingabe_testen enum('N','Y') NOT NULL DEFAULT 'Y',
       Positions_Nr INT NOT NULL DEFAULT 0,
       PRIMARY KEY (Attribut_ID),
       UNIQUE UC_Attribut_ID (Attribut_ID),
       UNIQUE UC_Positions_Nr (Positions_Nr)
);

CREATE TABLE artikel_kategorie
(
       a_k_ID INT NOT NULL DEFAULT 0 AUTO_INCREMENT,
       FK_Artikel_ID INT NOT NULL,
       FK_Kategorie_ID INT NOT NULL,
       FOREIGN KEY (FK_Artikel_ID) REFERENCES artikel (Artikel_ID),
       FOREIGN KEY (FK_Kategorie_ID) REFERENCES kategorien (Kategorie_ID),
       PRIMARY KEY (a_k_ID)
);

CREATE TABLE artikel_optionen
(
       Optionen_Nr INT NOT NULL DEFAULT 1,
       Optionstext CHAR(64),
       Preisdifferenz DOUBLE NOT NULL DEFAULT 0.0,
       FK_Artikel_ID INT NOT NULL,
       FOREIGN KEY (FK_Artikel_ID) REFERENCES artikel (Artikel_ID)
);

CREATE TABLE artikel_variationen
(
       Variations_Nr INT NOT NULL DEFAULT 1,
       Variationstext CHAR(64),
       Aufpreis DOUBLE NOT NULL DEFAULT 0.0,
       FK_Artikel_ID INT NOT NULL,
       FOREIGN KEY (FK_Artikel_ID) REFERENCES artikel (Artikel_ID)
);

CREATE TABLE artikel_bestellung
(
       a_b_ID INT NOT NULL DEFAULT 0 AUTO_INCREMENT,
       FK_Artikel_ID INT NOT NULL,
       FK_Bestellungs_ID INT NOT NULL,
       Anzahl INT NOT NULL DEFAULT 1,
       Variation TEXT NOT NULL,
       #/* Aufbau einer Variation:  Variationstext þ Aufpreis */
       Optionen TEXT NOT NULL,
       #/* Aufbau einer Variation:  Optionstext þ Preisdifferenz ... */
       FOREIGN KEY (FK_Artikel_ID) REFERENCES artikel (Artikel_ID),
       FOREIGN KEY (FK_Bestellungs_ID) REFERENCES bestellung (Bestellungs_ID),
       PRIMARY KEY (a_b_ID,FK_Artikel_ID,FK_Bestellungs_ID)
       #/* Anm. Das Trennzeichen ist ALT+0254, dieser Design muss allenfalls
       # * ueberarbeitet werden! */
);

CREATE TABLE bestellung_kunde
(
       b_k_ID INT NOT NULL DEFAULT 0 AUTO_INCREMENT,
       FK_Kunden_ID VARCHAR(255) NOT NULL,
       FK_Bestellungs_ID INT NOT NULL,
       FOREIGN KEY (FK_Kunden_ID) REFERENCES kunde (Kunden_ID),
       PRIMARY KEY (b_k_ID)
);

CREATE TABLE hilfe
(
       Hilfe_ID VARCHAR(128) NOT NULL,
       Hilfetext TEXT,
       PRIMARY KEY (Hilfe_ID),
       UNIQUE UC_Hilfe_ID (Hilfe_ID)
);

CREATE TABLE shop_settings
(
       Setting_Nr INT NOT NULL DEFAULT 0 AUTO_INCREMENT,
       MwStsatz DOUBLE NOT NULL DEFAULT 7.6,
       MwStpflichtig enum('N','Y') NOT NULL DEFAULT 'N',
       MwStNummer INT NOT NULL DEFAULT 0,
       Name CHAR(48) NOT NULL DEFAULT ' meinshop ',
       Adresse1 CHAR(48) NOT NULL DEFAULT 'Adresse 1',
       Adresse2 CHAR(48) NOT NULL DEFAULT 'Adresse 2',
       PLZOrt CHAR(48) NOT NULL DEFAULT 'PLZ und Ort',
       Tel1 CHAR(24) NOT NULL DEFAULT 'Telefon Nummer 1',
       Tel2 CHAR(24) NOT NULL DEFAULT 'Telefon Nummer 2',
       Email VARCHAR(128) NOT NULL DEFAULT 'IhrShop@shopserver.com',
       Admin_pwd CHAR(16) NOT NULL DEFAULT 'machshop',
       Abrechnung_nach_Preis enum('N','Y') NOT NULL DEFAULT 'N',
       Abrechnung_nach_Gewicht enum('N','Y') NOT NULL DEFAULT 'N',
       Abrechnung_nach_Pauschale enum('N','Y') NOT NULL DEFAULT 'Y',
       Pauschale_text varchar(127) NOT NULL DEFAULT 'Versand- und Verpackungskosten',
       Mindermengenzuschlag enum('N','Y') NOT NULL DEFAULT 'N',
       Mindermengenzuschlag_bis_Preis DOUBLE NOT NULL DEFAULT 0.0,
       Mindermengenzuschlag_Aufpreis DOUBLE NOT NULL DEFAULT 0.0,
       keineVersandkostenmehr enum('N','Y') NOT NULL DEFAULT 'N',
       keineVersandkostenmehr_ab DOUBLE NOT NULL DEFAULT 0.0,
       Rechnung enum('N','Y') NOT NULL DEFAULT 'Y',
       Nachnahme enum('N','Y') NOT NULL DEFAULT 'Y',
       Vorauskasse enum('N','Y') NOT NULL DEFAULT 'Y',
       Kreditkarten_Postcard enum('N','Y') NOT NULL DEFAULT 'N',
       Nachnamebetrag DOUBLE NOT NULL DEFAULT 0.0,
       Kontoinformation VARCHAR(255) NOT NULL DEFAULT 'Einzahlungen bitte auf unser Postkonto PC 40-168786-4',
       Waehrung CHAR(4) NOT NULL DEFAULT 'CHF',
       ShopVersion CHAR(48) NOT NULL DEFAULT 'November 2001, Version v.1.06',
       Gewichts_Masseinheit CHAR(16) NOT NULL DEFAULT 'kg',
       Thumbnail_Breite INT NOT NULL DEFAULT 100,
       AGB TEXT, #/* Max. 65535 Bytes gross in MySQL */
       SSL enum('N','Y') NOT NULL DEFAULT 'N',
       Bestellungsmanagement enum('N','Y') NOT NULL DEFAULT 'Y',
       SuchInkrement INT NOT NULL DEFAULT 10,
       max_session_time BIGINT, #/* In anderen DBs ev. ein anderer Typ (10 Stellen) */
       Opt_inc INT NOT NULL DEFAULT 3,
       Var_inc INT NOT NULL DEFAULT 3,
       Opt_anz INT NOT NULL DEFAULT 5,
       Var_anz INT NOT NULL DEFAULT 5,
       PRIMARY KEY (Setting_Nr),
       UNIQUE UC_Setting_Nr (Setting_Nr)
);

CREATE TABLE versandkostenpreise
(
       Von_Bis_ID INT DEFAULT 0 NOT NULL AUTO_INCREMENT,
       Von DOUBLE DEFAULT 0.0,
       Bis DOUBLE DEFAULT 100.0,
       Betrag DOUBLE DEFAULT 0.0,
       Vorauskasse enum('N','Y') NOT NULL DEFAULT 'Y',
       Rechnung enum('N','Y') NOT NULL DEFAULT 'Y',
       Nachname enum('N','Y') NOT NULL DEFAULT 'Y',
       Kreditkarte enum('N','Y') NOT NULL DEFAULT 'N',
       FK_Setting_Nr INT NOT NULL DEFAULT 1,
       FOREIGN KEY (FK_Setting_Nr) REFERENCES shop_settings (Setting_Nr),
       PRIMARY KEY (Von_Bis_ID),
       UNIQUE UC_Von_Bis_ID (Von_Bis_ID)
);

CREATE TABLE kreditkarte
(
       Kreditkarten_ID INT DEFAULT 0 NOT NULL AUTO_INCREMENT,
       Hersteller CHAR(32) NOT NULL,
       Handling enum('intern','extern') NOT NULL DEFAULT 'intern',
       benutzen enum('N','Y') NOT NULL DEFAULT 'Y',
       PRIMARY KEY (Kreditkarten_ID),
       UNIQUE UC_Kreditkarten_ID (Kreditkarten_ID)
);

CREATE TABLE css_file
(
       Attribut_ID CHAR(32) NOT NULL,
       CSS_String CHAR(255),
       PRIMARY KEY (Attribut_ID),
       UNIQUE UC_Attribut_ID (Attribut_ID),
       INDEX IDX_Attribut_ID_1 (Attribut_ID)
);

CREATE TABLE backup
(
       Backup_ID CHAR(32) NOT NULL,
       Wert CHAR(255),
       PRIMARY KEY (Backup_ID),
       UNIQUE UC_Backup_ID (Backup_ID)
);


# /*
# * ------------------------------------------------------------ End of File Marke
# */