DROP VIEW KUNDEN;
DROP VIEW HOTELS;
DROP VIEW BUCHUNGEN;
DROP VIEW REISEZEITEN;
DROP VIEW STAEDTE;

DELETE FROM REISEZEIT;
DELETE FROM KUNDE CASCADE;
DELETE FROM HOTEL;
DELETE FROM STADT;

CREATE VIEW KUNDEN AS SELECT * FROM KUNDE;
CREATE VIEW HOTELS AS SELECT * FROM HOTEL;
CREATE VIEW BUCHUNGEN AS SELECT * FROM BUCHUNG;
CREATE VIEW REISEZEITEN AS SELECT * FROM REISEZEIT;
CREATE VIEW STAEDTE AS SELECT * FROM STADT;

INSERT INTO KUNDE (KUNDEN_ID, VORNAME, NACHNAME, ADRESSE) VALUES (KUNDEN_SEQ.nextval, 'Bastian', 'Hahne', 'Gummersbach');
/*INSERT INTO KUNDE (KUNDEN_ID, VORNAME, NACHNAME, ADRESSE) VALUES (1, 'Maik', 'Milbaer', 'Gummersbach');*/

INSERT INTO STADT (STADT_ID, STADTNAME, LAND, FLUGHAFEN) VALUES (STADT_SEQ.nextval, 'Los Angles', 'Suedamerika', 'LAIA');
INSERT INTO STADT (STADT_ID, STADTNAME, LAND, FLUGHAFEN) VALUES (STADT_SEQ.nextval, 'Bossdon', 'Nordamerikanisches Japan', 'NAJIA');
/*INSERT INTO STADT (STADT_ID, STADTNAME, LAND, FLUGHAFEN) VALUES (STADT_SEQ.currval, 'Bossdon', 'Nordamerikanisches Japan', 'NAJIA');*/

INSERT INTO HOTEL (HOTEL_ID, HOTELNAME, KLASSE, ADRESSE, ANZAHLEZ, ANZAHLDZ, PREISEZ, PREISDZ, STADT_HOTEL_ID) VALUES (HOTEL_SEQ.nextval, 'Cortez', 4, 'Los Angles', 20, 50, 250.99, 400.99, (SELECT STADT_ID FROM STADT WHERE 'Los Angles' = STADT.STADTNAME));
/*INSERT INTO HOTEL (HOTEL_ID, HOTELNAME, KLASSE, ADRESSE, ANZAHLEZ, ANZAHLDZ, PREISEZ, PREISDZ, STADT_HOTEL_ID) VALUES (HOTEL_SEQ.nextval, 'Cortez', 0, 'Los Angles', 20, 50, 250.99, 400.99, (SELECT STADT_ID FROM STADT WHERE 'Los Angles' = STADT.STADTNAME));*/

INSERT INTO BUCHUNG (BUCHUNGS_ID, ANREISEDATUM, ABREISEDATUM, GEBUCHTEEZ, GEBUCHTEDZ, KUNDEN_ID, HOTEL_ID) VALUES (BUCHUNGS_SEQ.nextval, to_date('28-FEB-22', 'dd-mon-yy'), to_date('02-MAR-22', 'dd-mon-yy'), 3, 0, (SELECT KUNDEN_ID FROM KUNDE WHERE KUNDE.VORNAME = 'Bastian'), (SELECT HOTEL_ID FROM HOTEL WHERE HOTEL.HOTELNAME = 'Cortez'));
/*INSERT INTO BUCHUNG (BUCHUNGS_ID, ANREISEDATUM, ABREISEDATUM, GEBUCHTEEZ, GEBUCHTEDZ, KUNDEN_ID, HOTEL_ID) VALUES (BUCHUNGS_SEQ.nextval, to_date('02-MAR-22', 'dd-mon-yy'), to_date('28-FEB-22', 'dd-mon-yy'), 3, 0, (SELECT KUNDEN_ID FROM KUNDE WHERE KUNDE.VORNAME = 'Bastian'), (SELECT HOTEL_ID FROM HOTEL WHERE HOTEL.HOTELNAME = 'Cortez'));*/

INSERT INTO REISEZEIT (ZEIT_ID, REISEZEIT, BUCHUNGS_ID, VON_STADT_ID, NACH_STADT_ID) VALUES (REISEZEIT_SEQ.nextval, 1200, BUCHUNGS_SEQ.currval, STADT_SEQ.currval, (SELECT STADT_HOTEL_ID FROM HOTEL));
/*INSERT INTO REISEZEIT (ZEIT_ID, REISEZEIT, BUCHUNGS_ID, VON_STADT_ID, NACH_STADT_ID) VALUES (REISEZEIT_SEQ.currval, 1200, BUCHUNGS_SEQ.currval, STADT_SEQ.currval, (SELECT STADT_HOTEL_ID FROM HOTEL));*/

UPDATE BUCHUNG SET GEBUCHTEEZ = 2 WHERE GEBUCHTEEZ > 2;

COMMIT;