--Dimenzii


-- DimDate - tip 1 - ne se menuva

CREATE TABLE DimDate (
                         date_key        INTEGER PRIMARY KEY,  -- format YYYYMMDD
                         full_date       DATE        NOT NULL,
                         day_num         SMALLINT    NOT NULL,  -- 1-31
                         day_name        VARCHAR(10) NOT NULL,  -- Monday, Tuesday...
                         is_weekend      BOOLEAN     NOT NULL,
                         week_num        SMALLINT    NOT NULL,  -- 1-52
                         month_num       SMALLINT    NOT NULL,  -- 1-12
                         month_name      VARCHAR(10) NOT NULL,
                         quarter         SMALLINT    NOT NULL,  -- 1-4
                         year            SMALLINT    NOT NULL,
                         season          VARCHAR(10) NOT NULL   -- Winter/Spring/Summer/Fall
);

INSERT INTO DimDate
SELECT
    TO_CHAR(d, 'YYYYMMDD')::INTEGER,
    d,
    EXTRACT(DAY     FROM d)::SMALLINT,
    TO_CHAR(d, 'Day'),
    EXTRACT(DOW FROM d) IN (0, 6),
    EXTRACT(WEEK    FROM d)::SMALLINT,
    EXTRACT(MONTH   FROM d)::SMALLINT,
    TO_CHAR(d, 'Month'),
    EXTRACT(QUARTER FROM d)::SMALLINT,
    EXTRACT(YEAR    FROM d)::SMALLINT,
    CASE
        WHEN EXTRACT(MONTH FROM d) IN (12,1,2) THEN 'Winter'
        WHEN EXTRACT(MONTH FROM d) IN (3,4,5)  THEN 'Spring'
        WHEN EXTRACT(MONTH FROM d) IN (6,7,8)  THEN 'Summer'
        ELSE                                         'Fall'
        END
FROM generate_series('2015-01-01'::DATE, '2027-12-31'::DATE, '1 day') d;


-- DimHotel - tip 1 - ne se menuva

CREATE TABLE DimHotel (
                          hotel_key       SERIAL      PRIMARY KEY,
                          hotel_id        INTEGER     NOT NULL,
                          hotel_name      VARCHAR(100) NOT NULL,
                          city            VARCHAR(100) NOT NULL,
                          country         VARCHAR(100) NOT NULL DEFAULT 'USA'
);

INSERT INTO DimHotel (hotel_id, hotel_name, city)
SELECT hotel_id, name, location
FROM Hotel;



-- DimRoomType - tip 1 - ne se menuva

CREATE TABLE DimRoomType (
                             room_type_key   SERIAL      PRIMARY KEY,
                             room_type_id    INTEGER     NOT NULL,
                             type_name       VARCHAR(50) NOT NULL,
                             description     TEXT,
                             price_per_night NUMERIC(10,2) NOT NULL
);

INSERT INTO DimRoomType (room_type_id, type_name, description, price_per_night)
SELECT room_type_id, name, description, price_per_night
FROM Room_Type;


-- DimRoom - tip 2 - cenata moze da se promeni

CREATE TABLE DimRoom (
                         room_key        SERIAL        PRIMARY KEY,
                         room_id         INTEGER       NOT NULL,
                         room_number     VARCHAR(20)   NOT NULL,
                         capacity        SMALLINT      NOT NULL,
                         room_type_key   INTEGER       REFERENCES DimRoomType(room_type_key),
                         hotel_key       INTEGER       REFERENCES DimHotel(hotel_key),
    -- tip 2 koloni
                         valid_from      DATE          NOT NULL DEFAULT '2015-01-01',
                         valid_to        DATE          NOT NULL DEFAULT '9999-12-31',
                         is_current      BOOLEAN       NOT NULL DEFAULT TRUE
);

INSERT INTO DimRoom (room_id, room_number, capacity, room_type_key, hotel_key)
SELECT
    r.room_id,
    r.room_number,
    r.capacity,
    rt.room_type_key,
    h.hotel_key
FROM Room r
         JOIN DimRoomType rt ON rt.room_type_id = r.room_type_id
         JOIN DimHotel    h  ON h.hotel_id      = r.hotel_id;



-- DimSpecies + DimBreed  - tip 1 - ne se menuvaat

CREATE TABLE DimSpecies (
                            species_key     SERIAL      PRIMARY KEY,
                            species_id      INTEGER     NOT NULL,
                            species_name    VARCHAR(50) NOT NULL,
                            description     TEXT
);

INSERT INTO DimSpecies (species_id, species_name, description)
SELECT species_id, name, description
FROM Species;

CREATE TABLE DimBreed (
                          breed_key       SERIAL      PRIMARY KEY,
                          breed_id        INTEGER     NOT NULL,
                          breed_name      VARCHAR(100) NOT NULL,
                          description     TEXT,
                          species_key     INTEGER     REFERENCES DimSpecies(species_key)
);

INSERT INTO DimBreed (breed_id, breed_name, description, species_key)
SELECT
    b.breed_id,
    b.name,
    b.description,
    s.species_key
FROM Breed b
         JOIN DimSpecies s ON s.species_id = b.species_id;



-- DimPet  tip 2 - moze da se promeni

CREATE TABLE DimPet (
                        pet_key         SERIAL        PRIMARY KEY,
                        pet_id          INTEGER       NOT NULL,
                        pet_name        VARCHAR(100)  NOT NULL,
                        gender          VARCHAR(10),
                        date_of_birth   DATE,
                        age_group       VARCHAR(20),  -- 'Little', 'Adult', 'Senior'
                        species_key     INTEGER       REFERENCES DimSpecies(species_key),
                        breed_key       INTEGER       REFERENCES DimBreed(breed_key),
    -- tip 2 koloni
                        valid_from      DATE          NOT NULL DEFAULT '2015-01-01',
                        valid_to        DATE          NOT NULL DEFAULT '9999-12-31',
                        is_current      BOOLEAN       NOT NULL DEFAULT TRUE
);

INSERT INTO DimPet (
    pet_id, pet_name, gender, date_of_birth,
    age_group, species_key, breed_key
)
SELECT
    p.pet_id,
    p.name,
    p.gender,
    p.date_of_birth,
    CASE
        WHEN DATE_PART('year', AGE(p.date_of_birth)) < 1  THEN 'Baby'
        WHEN DATE_PART('year', AGE(p.date_of_birth)) < 3  THEN 'Young'
        WHEN DATE_PART('year', AGE(p.date_of_birth)) < 8  THEN 'Adult'
        ELSE                                                    'Senior'
        END,
    s.species_key,
    b.breed_key
FROM Pet p
         JOIN DimSpecies s ON s.species_id = p.species_id
         JOIN DimBreed   b ON b.breed_id   = p.breed_id;


-- DimCustomer - tip - 2 moze da se promeni

CREATE TABLE DimCustomer (
                             customer_key        SERIAL        PRIMARY KEY,
                             customer_id         INTEGER       NOT NULL,
                             first_name          VARCHAR(50)   NOT NULL,
                             last_name           VARCHAR(50)   NOT NULL,
                             full_name            VARCHAR(100)  NOT NULL,
                             city                 VARCHAR(100),
                             registration_date    DATE,
                             customer_segment      VARCHAR(20),  -- 'New', 'Regular', 'VIP'
    -- tip 2 koloni
                             valid_from            DATE          NOT NULL DEFAULT '2015-01-01',
                             valid_to              DATE          NOT NULL DEFAULT '9999-12-31',
                             is_current            BOOLEAN       NOT NULL DEFAULT TRUE
);

INSERT INTO DimCustomer (
    customer_id, first_name, last_name,
    full_name, city, registration_date, customer_segment
)
SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.first_name || ' ' || c.last_name,
    TRIM(SPLIT_PART(SPLIT_PART(c.address, ',', 2), ',', 1)),
    c.registration_date,
    CASE
        WHEN c.registration_date >= CURRENT_DATE - INTERVAL '1 year'  THEN 'New'
        WHEN c.registration_date >= CURRENT_DATE - INTERVAL '3 years' THEN 'Regular'
        ELSE                                                                'VIP'
        END
FROM Customer c;


-- DimEmployee - tip 2 - role moze da se promeni

CREATE TABLE DimEmployee (
                             employee_key    SERIAL        PRIMARY KEY,
                             employee_id     INTEGER       NOT NULL,
                             first_name      VARCHAR(50)   NOT NULL,
                             last_name       VARCHAR(50)   NOT NULL,
                             full_name        VARCHAR(100)  NOT NULL,
                             role              VARCHAR(50)   NOT NULL,
                             hotel_key         INTEGER       REFERENCES DimHotel(hotel_key),
                             hire_date         DATE,
    -- tip 2 koloni
                             valid_from        DATE          NOT NULL DEFAULT '2015-01-01',
                             valid_to          DATE          NOT NULL DEFAULT '9999-12-31',
                             is_current        BOOLEAN       NOT NULL DEFAULT TRUE
);

INSERT INTO DimEmployee (
    employee_id, first_name, last_name,
    full_name, role, hotel_key, hire_date
)
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.first_name || ' ' || e.last_name,
    e.role,
    h.hotel_key,
    e.hire_date
FROM Employee e
         JOIN DimHotel h ON h.hotel_id = e.hotel_id;


-- DimService - tip 1 - poretko se menuvaat uslugi
CREATE TABLE DimService (
                            service_key          SERIAL        PRIMARY KEY,
                            service_id           INTEGER       NOT NULL,
                            service_name          VARCHAR(100)  NOT NULL,
                            description            TEXT,
                            price                  NUMERIC(10,2) NOT NULL,
                            duration_minutes        INTEGER       NOT NULL,
                            price_category          VARCHAR(20)   -- 'Budget', 'Standard', 'Premium'
);

INSERT INTO DimService (
    service_id, service_name, description,
    price, duration_minutes, price_category
)
SELECT
    service_id,
    name,
    description,
    price,
    duration_minutes,
    CASE
        WHEN price < 20 THEN 'Budget'
        WHEN price < 40 THEN 'Standard'
        ELSE                 'Premium'
        END
FROM Service;



-- DimProduct  - tip 1 - ne se menuva
CREATE TABLE DimProduct (
                            product_key      SERIAL        PRIMARY KEY,
                            product_id        INTEGER       NOT NULL,
                            product_name       VARCHAR(100)  NOT NULL,
                            category_name       VARCHAR(50)   NOT NULL,
                            supplier_name        VARCHAR(100)  NOT NULL,
                            price                 NUMERIC(10,2) NOT NULL,
                            price_range            VARCHAR(20)   -- 'Low', 'Mid', 'High'
);

INSERT INTO DimProduct (
    product_id, product_name, category_name,
    supplier_name, price, price_range
)
SELECT
    p.product_id,
    p.name,
    c.name,
    s.name,
    p.price,
    CASE
        WHEN p.price < 30  THEN 'Low'
        WHEN p.price < 70  THEN 'Mid'
        ELSE                    'High'
        END
FROM Product  p
         JOIN Category c ON c.category_id = p.category_id
         JOIN Supplier s ON s.supplier_id = p.supplier_id;