init.sql

CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,      -
    login VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL
);

CREATE TABLE IF NOT EXISTS countries (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS resorts (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    country_id BIGINT NOT NULL,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS hotels (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    stars INT CHECK (stars >= 1 AND stars <= 5),
    resort_id BIGINT NOT NULL,     -- Тут має бути BIGINT
    FOREIGN KEY (resort_id) REFERENCES resorts(id) ON DELETE CASCADE
);

INSERT INTO users (login, password, role)
SELECT 'admin', 'YWRtaW4=', 'ADMIN'
    WHERE NOT EXISTS (SELECT 1 FROM users WHERE login = 'admin');

INSERT INTO users (login, password, role)
SELECT 'user', 'dXNlcg==', 'USER'
    WHERE NOT EXISTS (SELECT 1 FROM users WHERE login = 'user');

-- Країни
INSERT INTO countries (name)
SELECT 'Туреччина' WHERE NOT EXISTS (SELECT 1 FROM countries WHERE name = 'Туреччина');
INSERT INTO countries (name)
SELECT 'Єгипет' WHERE NOT EXISTS (SELECT 1 FROM countries WHERE name = 'Єгипет');

-- Курорти
INSERT INTO resorts (name, description, country_id)
SELECT 'Анталія', 'Популярний середземноморський курорт', 1
    WHERE NOT EXISTS (SELECT 1 FROM resorts WHERE name = 'Анталія');

INSERT INTO resorts (name, description, country_id)
SELECT 'Шарм-ель-Шейх', 'Курорт на Червоному морі', 2
    WHERE NOT EXISTS (SELECT 1 FROM resorts WHERE name = 'Шарм-ель-Шейх');

-- Готелі
INSERT INTO hotels (name, stars, resort_id)
SELECT 'Rixos Downtown', 5, 1
    WHERE NOT EXISTS (SELECT 1 FROM hotels WHERE name = 'Rixos Downtown');

INSERT INTO hotels (name, stars, resort_id)
SELECT 'Savoy', 5, 2
    WHERE NOT EXISTS (SELECT 1 FROM hotels WHERE name = 'Savoy');