CREATE TABLE Species (
    species_id   INTEGER      NOT NULL GENERATED ALWAYS AS IDENTITY,
    name         VARCHAR(100) NOT NULL,
    description  VARCHAR(255),
    PRIMARY KEY (species_id),
    CONSTRAINT uq_species_name UNIQUE (name)
);

CREATE TABLE Breed (
    breed_id     INTEGER      NOT NULL GENERATED ALWAYS AS IDENTITY,
    name         VARCHAR(100) NOT NULL,
    description  VARCHAR(255),
    species_id   INTEGER      NOT NULL,
    PRIMARY KEY (breed_id),
    CONSTRAINT fk_breed_species
        FOREIGN KEY (species_id) REFERENCES Species(species_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Category (
    category_id  INTEGER      NOT NULL GENERATED ALWAYS AS IDENTITY,
    name         VARCHAR(100) NOT NULL,
    description  VARCHAR(255),
    PRIMARY KEY (category_id)
);

CREATE TABLE Room_Type (
    room_type_id    INTEGER        NOT NULL GENERATED ALWAYS AS IDENTITY,
    name            VARCHAR(100)   NOT NULL,
    description     VARCHAR(255),
    price_per_night NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (room_type_id),
    CONSTRAINT uq_room_type_name UNIQUE (name)
);

CREATE TABLE Service (
    service_id       INTEGER        NOT NULL GENERATED ALWAYS AS IDENTITY,
    name             VARCHAR(100)   NOT NULL,
    description      VARCHAR(255),
    price            NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
    duration_minutes INTEGER,
    PRIMARY KEY (service_id),
    CONSTRAINT uq_service_name UNIQUE (name)
);

CREATE TABLE Hotel (
    hotel_id   INTEGER      NOT NULL GENERATED ALWAYS AS IDENTITY,
    name       VARCHAR(100),
    location   VARCHAR(100),
    PRIMARY KEY (hotel_id)
);

CREATE TABLE Employee (
    employee_id INTEGER      NOT NULL GENERATED ALWAYS AS IDENTITY,
    first_name  VARCHAR(50)  NOT NULL,
    last_name   VARCHAR(50)  NOT NULL,
    role        VARCHAR(50),
    phone       VARCHAR(20),
    email       VARCHAR(100),
    hire_date   DATE,
    hotel_id    INTEGER      NOT NULL DEFAULT -1,
    PRIMARY KEY (employee_id),
    CONSTRAINT fk_employee_hotel
        FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT chk_employee_email
        CHECK (email IS NULL OR email ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT chk_employee_phone
        CHECK (phone IS NULL OR phone ~ '^\+?[0-9][0-9\s\-().]{6,18}[0-9]$')
);

CREATE TABLE Room (
    room_id      INTEGER     NOT NULL GENERATED ALWAYS AS IDENTITY,
    room_number  VARCHAR(20) NOT NULL,
    capacity     INTEGER,
    room_type_id INTEGER     NOT NULL,
    hotel_id     INTEGER     NOT NULL DEFAULT -1,
    PRIMARY KEY (room_id),
    CONSTRAINT fk_room_room_type
        FOREIGN KEY (room_type_id) REFERENCES Room_Type(room_type_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_room_hotel
        FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
        ON DELETE SET DEFAULT ON UPDATE CASCADE,
    CONSTRAINT uq_room_number UNIQUE (room_number)
);

CREATE TABLE Date (
    date    DATE        NOT NULL,
    room_id INTEGER     NOT NULL,
    status  VARCHAR(20),
    PRIMARY KEY (date, room_id),
    CONSTRAINT fk_dates_room
        FOREIGN KEY (room_id) REFERENCES Room(room_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Supplier (
    supplier_id  INTEGER      NOT NULL GENERATED ALWAYS AS IDENTITY,
    name         VARCHAR(100) NOT NULL,
    contact_name VARCHAR(100),
    phone        VARCHAR(20),
    email        VARCHAR(100),
    address      VARCHAR(255),
    PRIMARY KEY (supplier_id),
    CONSTRAINT chk_supplier_email
        CHECK (email IS NULL OR email ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT chk_supplier_phone
        CHECK (phone IS NULL OR phone ~ '^\+?[0-9][0-9\s\-().]{6,18}[0-9]$')
);

CREATE TABLE Customer (
    customer_id       INTEGER      NOT NULL GENERATED ALWAYS AS IDENTITY,
    first_name        VARCHAR(50)  NOT NULL,
    last_name         VARCHAR(50)  NOT NULL,
    email             VARCHAR(100) NOT NULL,
    phone             VARCHAR(20),
    address           VARCHAR(255),
    registration_date DATE,
    PRIMARY KEY (customer_id),
    CONSTRAINT uq_customer_email UNIQUE (email),
    CONSTRAINT chk_customer_email
        CHECK (email ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT chk_customer_phone
        CHECK (phone IS NULL OR phone ~ '^\+?[0-9][0-9\s\-().]{6,18}[0-9]$')
);

CREATE TABLE Pet (
    pet_id        INTEGER      NOT NULL GENERATED ALWAYS AS IDENTITY,
    name          VARCHAR(100) NOT NULL,
    date_of_birth DATE,
    gender        VARCHAR(10),
    customer_id   INTEGER      NOT NULL,
    species_id    INTEGER      NOT NULL,
    breed_id      INTEGER,
    PRIMARY KEY (pet_id),
    CONSTRAINT fk_pet_customer
        FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_pet_species
        FOREIGN KEY (species_id) REFERENCES Species(species_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_pet_breed
        FOREIGN KEY (breed_id) REFERENCES Breed(breed_id)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Product (
    product_id  INTEGER        NOT NULL GENERATED ALWAYS AS IDENTITY,
    name        VARCHAR(100)   NOT NULL,
    description VARCHAR(255),
    price       NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
    category_id INTEGER        NOT NULL,
    supplier_id INTEGER,
    PRIMARY KEY (product_id),
    CONSTRAINT fk_product_category
        FOREIGN KEY (category_id) REFERENCES Category(category_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_product_supplier
        FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE "Order" (
    order_id     INTEGER        NOT NULL GENERATED ALWAYS AS IDENTITY,
    order_date   DATE           NOT NULL DEFAULT CURRENT_DATE,
    status       VARCHAR(50),
    total_amount NUMERIC(10, 2)         DEFAULT 0.00,
    customer_id  INTEGER        NOT NULL,
    hotel_id     INTEGER        NOT NULL,
    PRIMARY KEY (order_id),
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_order_hotel
        FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE OrderProduct (
    orderproduct_id INTEGER        NOT NULL GENERATED ALWAYS AS IDENTITY,
    quantity        INTEGER        NOT NULL DEFAULT 1,
    unit_price      NUMERIC(10, 2) NOT NULL,
    order_id        INTEGER        NOT NULL,
    product_id      INTEGER        NOT NULL,
    PRIMARY KEY (orderproduct_id),
    CONSTRAINT uq_orderproduct_order_product UNIQUE (order_id, product_id),
    CONSTRAINT fk_orderproduct_order
        FOREIGN KEY (order_id) REFERENCES "Order"(order_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_orderproduct_product
        FOREIGN KEY (product_id) REFERENCES Product(product_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Delivery (
    delivery_id  INTEGER     NOT NULL GENERATED ALWAYS AS IDENTITY,
    delivered_at VARCHAR(50),
    status       VARCHAR(50),
    hotel_id     INTEGER     NOT NULL,
    purchase_id  INTEGER     NOT NULL,
    PRIMARY KEY (delivery_id),
    CONSTRAINT fk_delivery_hotel
        FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_delivery_orderproduct
        FOREIGN KEY (purchase_id) REFERENCES OrderProduct(orderproduct_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Reservation (
    reservation_id      INTEGER   NOT NULL GENERATED ALWAYS AS IDENTITY,
    reservation_date    DATE      NOT NULL DEFAULT CURRENT_DATE,
    status              VARCHAR(50),
    notes               VARCHAR(255),
    total_cost          INTEGER,
    pet_id              INTEGER   NOT NULL,
    employee_id         INTEGER,
    PRIMARY KEY (reservation_id),
    CONSTRAINT fk_reservation_pet
        FOREIGN KEY (pet_id) REFERENCES Pet(pet_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_reservation_employee
        FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE RoomReservation (
    room_reservation_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    check_in_date       DATE    NOT NULL,
    check_out_date      DATE    NOT NULL,
    reservation_id      INTEGER NOT NULL,
    room_id             INTEGER NOT NULL,
    PRIMARY KEY (room_reservation_id),
    CONSTRAINT chk_roomres_dates
        CHECK (check_out_date > check_in_date),
    CONSTRAINT fk_roomres_room
        FOREIGN KEY (room_id) REFERENCES Room(room_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_roomres_reservation
        FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE ServiceReservation (
    service_reservation_id INTEGER     NOT NULL GENERATED ALWAYS AS IDENTITY,
    scheduled_date         DATE        NOT NULL,
    scheduled_time         TIME,
    status                 VARCHAR(50),
    reservation_id         INTEGER     NOT NULL,
    service_id             INTEGER     NOT NULL,
    PRIMARY KEY (service_reservation_id),
    CONSTRAINT fk_svcres_reservation
        FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_svcres_service
        FOREIGN KEY (service_id) REFERENCES Service(service_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Employee_Service (
    employee_id INTEGER NOT NULL,
    service_id   INTEGER NOT NULL,
    PRIMARY KEY (employee_id, service_id),
    CONSTRAINT fk_empservice_employee
        FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
        ON DELETE SET DEFAULT ON UPDATE CASCADE,
    CONSTRAINT fk_empservice_service
        FOREIGN KEY (service_id) REFERENCES Service(service_id)
        ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Product_Service (
    product_id INTEGER NOT NULL,
    service_id INTEGER NOT NULL,
    PRIMARY KEY (product_id, service_id),
    CONSTRAINT fk_prodservice_product
        FOREIGN KEY (product_id) REFERENCES Product(product_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_prodservice_service
        FOREIGN KEY (service_id) REFERENCES Service(service_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Payment (
    payment_id     INTEGER        NOT NULL GENERATED ALWAYS AS IDENTITY,
    payment_date   DATE           NOT NULL DEFAULT CURRENT_DATE,
    amount         NUMERIC(10, 2) NOT NULL,
    payment_method VARCHAR(50),
    status         VARCHAR(50),
    reservation_id INTEGER,
    PRIMARY KEY (payment_id),
    CONSTRAINT chk_payment_source
        CHECK (reservation_id IS NOT NULL),
    CONSTRAINT fk_payment_reservation
        FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Review (
    review_id   INTEGER  NOT NULL GENERATED ALWAYS AS IDENTITY,
    review_date DATE,
    rating      INTEGER CHECK (rating BETWEEN 1 AND 9),
    comment     VARCHAR(255),
    customer_id INTEGER  NOT NULL,
    hotel_id    INTEGER  NOT NULL,
    PRIMARY KEY (review_id),
    CONSTRAINT fk_review_customer
        FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
        ON DELETE SET DEFAULT ON UPDATE CASCADE,
    CONSTRAINT fk_review_hotel
        FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE MedicalRecord (
    record_id   INTEGER      NOT NULL GENERATED ALWAYS AS IDENTITY,
    record_date DATE,
    status      VARCHAR(11),
    vet_name    VARCHAR(50),
    alergies    VARCHAR(255),
    medications VARCHAR(255),
    note        VARCHAR(255),
    diagnosis   VARCHAR(255),
    pet_id      INTEGER      NOT NULL,
    PRIMARY KEY (record_id),
    CONSTRAINT fk_medrecord_pet
        FOREIGN KEY (pet_id) REFERENCES Pet(pet_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE PetDelivery (
    pet_delivery_id INTEGER   NOT NULL GENERATED ALWAYS AS IDENTITY,
    delivery_time   TIMESTAMP,
    status          VARCHAR(11),
    destination     VARCHAR(11),
    reservation_id  INTEGER   NOT NULL,
    employee_id     INTEGER,
    PRIMARY KEY (pet_delivery_id),
    CONSTRAINT fk_petdelivery_reservation
        FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_petdelivery_employee
        FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
        ON DELETE SET NULL ON UPDATE CASCADE
);