CREATE TABLE EMPLOYEE
(   
    E_ID NUMBER(30) NOT NULL,
    E_PASSWORD VARCHAR2(40) NOT NULL,
    E_ADRESS_STREET VARCHAR2(50) NOT NULL,
    E_ADRESS_HNO VARCHAR2(10) NOT NULL, -- Hausnummer
    E_ADRESS_ZIP VARCHAR2(10) NOT NULL,
    E_ADRESS_CITY VARCHAR2(50) NOT NULL,
    E_ADRESS_COUNTRY VARCHAR2(50) NOT NULL,
    E_EMAIL VARCHAR2(60) NOT NULL,
    E_PHONE NUMBER(30) NOT NULL,
    E_BRANCHID VARCHAR2(7) NOT NULL,
    E_LEDITET DATE NOT NULL, --Zuletzt Bearbeitet
    
    CONSTRAINT EMPLOYEE_pk PRIMARY KEY (E_ID)

);

CREATE TABLE BOOKINGS
(   B_ID NUMBER(15) NOT NULL,
    B_C_ID NUMBER(35 NOT NULL,
    B_B_ACCID NUMBER(35) NOT NULL,
    B_PRICE NUMBER(10) NOT NULL,
    B_CURRENCY VARCHAR2(6) NOT NULL,
    B_FROM DATE NOT NULL,
    B_TO DATE NOT NULL,
    B_COUNTRY VARCHAR2(20) NOT NULL,
    B_TCID VARCHAR2(38) NOT NULL,
    B_DEP_TIME DATE NOT NULL,
    B_DEP_PLACE VARCHAR2(38) NOT NULL,
    B_ARI_TIME DATE NOT NULL,
    B_ARI_PLACE VARCHAR2(38) NOT NULL,
    B_TYPE VARCHAR2(15) NOT NULL,
    B_TRAVELLERS NUMBER(10) NOT NULL,
    B_LASTEDITED DATE NOT NULL, --Zuletzt Bearbeitet
    
    CONSTRAINT BOOKINGS_pk PRIMARY KEY (B_ID)
);

CREATE TABLE CUSTOMER
(   C_ID NUMBER(30) NOT NULL,
    C_NAME VARCHAR2(50) NOT NULL,
    C_ADRESS_STREET VARCHAR2(50) NOT NULL,
    C_ADRESS_HNO VARCHAR2(10) NOT NULL,
    C_ADRESS_ZIP VARCHAR2(10) NOT NULL,
    C_ADRESS_CITY VARCHAR2(50) NOT NULL,
    C_ADRESS_COUNTRY VARCHAR2(50) NOT NULL,
    C_EMAIL VARCHAR2(60) NOT NULL,
    C_PHONE NUMBER(30) NOT NULL,
    C_BOOKINGS NUMBER(20) NOT NULL,
    C_LEDITED DATE NOT NULL, --Zuletzt Bearbeitet
    
    CONSTRAINT CUSTOMER_pk PRIMARY KEY (C_ID)
);

CREATE TABLE SEASON
(   S_ID NUMBER(30) NOT NULL, --1=Summer / 2=Harvest / 3=Winter / 4=Spring
    S_NAME VARCHAR2(50) NOT NULL, --Hier tragen wir den oberen Kommentar ein
    S_FAC VARCHAR2(50) NOT NULL, --Faktor des Sesonalrabattes
    S_LEDITED DATE NOT NULL, --Zuletzt Bearbeitet
    
    CONSTRAINT SEASON_pk PRIMARY KEY (S_ID)
);

CREATE TABLE OFFER
(   O_ID NUMBER(30) NOT NULL,
    O_B_ACCID NUMBER(35) NOT NULL,
    O_CHLOE_PRICE VARCHAR2(50) NOT NULL, --Der Preis des ganzen
    O_CURRENCY VARCHAR2(10) NOT NULL, --Die Währung wird seperat angegeben
    O_TIME_START DATE NOT NULL,
    O_TIME_STOP DATE NOT NULL,
    O_AVABILE BOOLEAN NOT NULL,
    O_SECURITY VARCHAR2(60) NOT NULL, --Die Infos vom Auswärtigen Amt
    O_COMMENTS VARCHAR2(60) NOT NULL, --z.B. Erfahrungsberichte von vorangegangenen Besuchern
    O_LEDITED DATE NOT NULL, --Zuletzt Bearbeitet
    
    CONSTRAINT OFFER_pk PRIMARY KEY (O_ID)
);

CREATE TABLE ACCOMMODATION --Unterkunft
(   A_ACCID NUMBER(30) NOT NULL,
    A_ADRESS_STREET VARCHAR2(50) NOT NULL,
    A_ADRESS_HNO VARCHAR2(10) NOT NULL, -- Hausnummer
    A_ADRESS_ZIP VARCHAR2(10) NOT NULL,
    A_ADRESS_CITY VARCHAR2(50) NOT NULL,
    A_ADRESS_COUNTRY VARCHAR2(50) NOT NULL,
    A_TYPE VARCHAR2(50) NOT NULL, --Hotel/Hostel/Jugendherberge usw.
    A_COMMENTS NUMBER(30) NOT NULL, --Bewertungen/Bemerkungen
    A_QUALITY NUMBER(30) NOT NULL, --Qualität z.B. Sterne bei einem Hotel
    A_UNICORN NUMBER(30) NOT NULL, -- Besonderheiten, z.B. Außenpool, ITA Sicherung usw.
    A_PHONE NUMBER(30) NOT NULL,
    A_FAX NUMBER(30) NOT NULL,
    A_E-MAIL VARCHAR(60) NOT NULL,
    A_LEDITED DATE NOT NULL, --Zuletzt Bearbeitet
    
    CONSTRAINT ACCOMMODATION_pk PRIMARY KEY (A_ACCID)
);