create table ETL_WATERMARK
(
    ENTITY_NAME VARCHAR2(50)           not null
        constraint PK_ETL_WATERMARK
            primary key,
    LAST_KEY    NUMBER(20)   default 0 not null,
    LAST_RUN_TS TIMESTAMP(6) default SYSTIMESTAMP
)
/

create table STG_HOTEL
(
    HOTEL_ID         NUMBER(10)    not null,
    HOTEL_CODE       VARCHAR2(20)  not null,
    HOTEL_NAME       VARCHAR2(150) not null,
    CITY             VARCHAR2(100) not null,
    COUNTRY_CODE     CHAR(2)       not null,
    COUNTRY_NAME     VARCHAR2(100) not null,
    CURRENCY         VARCHAR2(10)  not null,
    CHAIN_CODE       VARCHAR2(10),
    CHAIN_NAME       VARCHAR2(100),
    STAR_RATING      NUMBER(1)     not null,
    STAR_DESCRIPTION VARCHAR2(20)
)
/

create table DIM_DATE
(
    DATE_KEY        NUMBER(8)    not null
        constraint PK_DIM_DATE
            primary key,
    FULL_DATE       DATE         not null,
    YEAR            NUMBER(4)    not null,
    QUARTER         NUMBER(1)    not null,
    MONTH           NUMBER(2)    not null,
    MONTH_NAME      VARCHAR2(10) not null,
    WEEK_NUMBER     NUMBER(2)    not null,
    DAY_OF_MONTH    NUMBER(2)    not null,
    DAY_NAME        VARCHAR2(10) not null,
    IS_WEEKEND      NUMBER(1)    not null
        constraint CK_DIM_DATE_WEEKEND
            check (is_weekend IN (0, 1)),
    IS_BUSINESS_DAY NUMBER(1)    not null
        constraint CK_DIM_DATE_BUSINESS
            check (is_business_day IN (0, 1)),
    SEASON          VARCHAR2(10) not null
)
/

create table DIM_HOTEL
(
    HOTEL_KEY        NUMBER(10) default "IPZ19438"."ISEQ$$_303891".nextval generated as identity
		constraint PK_DIM_HOTEL
			primary key,
    SOURCE_HOTEL_ID  NUMBER(10)           not null,
    HOTEL_CODE       VARCHAR2(20)         not null,
    HOTEL_NAME       VARCHAR2(150)        not null,
    CITY             VARCHAR2(100)        not null,
    COUNTRY_CODE     CHAR(2)              not null,
    COUNTRY_NAME     VARCHAR2(100)        not null,
    CURRENCY         VARCHAR2(10)         not null,
    CHAIN_CODE       VARCHAR2(10),
    CHAIN_NAME       VARCHAR2(100),
    STAR_RATING      NUMBER(1)            not null,
    STAR_DESCRIPTION VARCHAR2(20),
    EFFECTIVE_DATE   DATE                 not null,
    EXPIRY_DATE      DATE,
    IS_CURRENT       NUMBER(1)  default 1 not null
        constraint CK_DIM_HOTEL_CURRENT
            check (is_current IN (0, 1))
)
/

create table DIM_ROOM
(
    ROOM_KEY              NUMBER(10) generated as identity
        constraint PK_DIM_ROOM
            primary key,
    SOURCE_ROOM_ID        NUMBER(10)    not null
        constraint UQ_DIM_ROOM
            unique,
    HOTEL_KEY             NUMBER(10)    not null
        constraint FK_DIM_ROOM_HOTEL
            references DIM_HOTEL,
    ROOM_NUMBER           VARCHAR2(10)  not null,
    FLOOR                 NUMBER(3)     not null,
    ROOM_TYPE_CODE        VARCHAR2(20)  not null,
    ROOM_TYPE_DESCRIPTION VARCHAR2(100) not null,
    SMOKING_YN            NUMBER(1)     not null
        constraint CK_DIM_ROOM_SMOKING
            check (smoking_yn IN (0, 1)),
    STANDARD_RATE         NUMBER(10, 2) not null
)
/

create table DIM_GUEST
(
    GUEST_KEY       NUMBER(10) generated as identity
        constraint PK_DIM_GUEST
            primary key,
    SOURCE_GUEST_ID NUMBER(10)    not null
        constraint UQ_DIM_GUEST
            unique,
    GUEST_NAME      VARCHAR2(150) not null,
    CITY            VARCHAR2(100),
    COUNTRY_CODE    CHAR(2),
    COUNTRY_NAME    VARCHAR2(100)
)
/

create table FACT_ROOM_BOOKING
(
    FACT_ID                  NUMBER(10) default "IPZ19438"."ISEQ$$_303902".nextval generated as identity
		constraint PK_FACT_ROOM_BOOKING
			primary key,
    SOURCE_RB_ID             NUMBER(10)           not null
        constraint UQ_FACT_ROOM_BOOKING_SRC
            unique,
    HOTEL_KEY                NUMBER(10)           not null
        constraint FK_FACT_HOTEL
            references DIM_HOTEL,
    ROOM_KEY                 NUMBER(10)           not null
        constraint FK_FACT_ROOM
            references DIM_ROOM,
    GUEST_KEY                NUMBER(10)           not null
        constraint FK_FACT_GUEST
            references DIM_GUEST,
    BOOKING_CREATED_DATE_KEY NUMBER(8)            not null
        constraint FK_FACT_BOOKING_DATE
            references DIM_DATE,
    CHECKIN_DATE_KEY         NUMBER(8)            not null
        constraint FK_FACT_CHECKIN_DATE
            references DIM_DATE,
    CHECKOUT_DATE_KEY        NUMBER(8)            not null
        constraint FK_FACT_CHECKOUT_DATE
            references DIM_DATE,
    BOOKING_STATUS           VARCHAR2(20)         not null,
    BOOKING_COUNT            NUMBER(1)  default 1 not null
        constraint CK_FACT_BOOKING_COUNT
            check (booking_count = 1),
    NIGHTS_STAYED            NUMBER(4)            not null,
    NIGHTLY_RATE             NUMBER(10, 2)        not null,
    TOTAL_AMOUNT             NUMBER(12, 2)        not null
)
/

create table STG_GUEST
(
    SOURCE_GUEST_ID NUMBER(10)    not null,
    GUEST_NAME      VARCHAR2(150) not null,
    CITY            VARCHAR2(100),
    COUNTRY_CODE    CHAR(2),
    COUNTRY_NAME    VARCHAR2(100)
)
/

create table STG_ROOM
(
    SOURCE_ROOM_ID        NUMBER(10)    not null,
    HOTEL_CODE            VARCHAR2(20)  not null,
    ROOM_NUMBER           VARCHAR2(10)  not null,
    FLOOR                 NUMBER(3)     not null,
    ROOM_TYPE_CODE        VARCHAR2(20)  not null,
    ROOM_TYPE_DESCRIPTION VARCHAR2(100) not null,
    SMOKING_YN            NUMBER(1)     not null,
    STANDARD_RATE         NUMBER(10, 2) not null,
    HOTEL_ID              NUMBER(10)
)
/

create table STG_ROOM_BOOKING
(
    SOURCE_RB_ID         NUMBER(10)          not null,
    GUEST_ID             NUMBER(10)          not null,
    BOOKING_CREATED_DATE DATE                not null,
    CHECKIN_DATE         DATE                not null,
    CHECKOUT_DATE        DATE                not null,
    BOOKING_STATUS       VARCHAR2(20)        not null,
    BOOKING_COUNT        NUMBER(1) default 1 not null,
    NIGHTS_STAYED        NUMBER(4)           not null,
    NIGHTLY_RATE         NUMBER(10, 2)       not null,
    TOTAL_AMOUNT         NUMBER(12, 2)       not null,
    HOTEL_ID             NUMBER(10)          not null,
    ROOM_ID              NUMBER(10)          not null
)
/