konceptualenDizajnLogickiFizicki: kreiranje.sql

drop table if exists item_reservtion_junction;
drop table if exists Reservations;
drop type if exists status_enum;
drop table if exists Restaurant_Tables;
drop table if exists Items;
drop table if exists Menus;
drop table if exists Restaurants;
drop table if exists Payments;
drop table if exists Customers;
drop type if exists customer_type;

create type customer_type as enum('premium', 'gold', 'standard', 'platinum');
CREATE TYPE status_enum AS ENUM ('reserved', 'canceled', 'pending');

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL,
    role VARCHAR(20),
    phone VARCHAR(20),
    address TEXT,
    membership_level VARCHAR(20),
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE restaurants (
    restaurant_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    address VARCHAR(255),
    cuisine_type VARCHAR(50),
    phone VARCHAR(20),
    operating_hours VARCHAR(100),
    website VARCHAR(100),
    social_media_links TEXT,
    rating NUMERIC(3, 2)
);

CREATE TABLE tables (
    table_id SERIAL PRIMARY KEY,
    restaurant_id INT REFERENCES restaurants(restaurant_id),
    capacity INT,
    location VARCHAR(100),
    is_smoking_area BOOLEAN,
    description TEXT,
    reservation_duration_hours INT DEFAULT 2
);

CREATE TABLE menus (
    menu_id SERIAL PRIMARY KEY,
    restaurant_id INT REFERENCES restaurants(restaurant_id),
    item_name VARCHAR(100),
    category VARCHAR(50),
    price NUMERIC(8, 2),
    description TEXT
);

CREATE TABLE menu_tags (
    id SERIAL PRIMARY KEY,
    menu_id INT REFERENCES menus(menu_id),
    tag_name VARCHAR(50),
    tag_value VARCHAR(50)
);

CREATE TABLE reservations (
    reservation_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    table_id INT REFERENCES tables(table_id),
    restaurant_id INT REFERENCES restaurants(restaurant_id),
    reservation_date_time TIMESTAMP NOT NULL,
    party_size INT,
    special_requests TEXT,
    status VARCHAR(20),
    check_in_time TIMESTAMP,
    check_out_time TIMESTAMP,
    payment_status VARCHAR(20) DEFAULT 'Unpaid'
);

CREATE TABLE reservation_preordered_items (
    id SERIAL PRIMARY KEY,
    reservation_id INT REFERENCES reservations(reservation_id),
    name VARCHAR(100),
    quantity INT,
    price NUMERIC(8, 2)
);

CREATE TABLE reservation_history (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES users(id),
    table_id INT REFERENCES tables(table_id),
    restaurant_id INT REFERENCES restaurants(restaurant_id),
    reservation_datetime TIMESTAMP,
    party_size INT,
    special_requests TEXT,
    status VARCHAR(20),
    cancellation_reason TEXT,
    check_in_date TIMESTAMP
);