konceptualenDizajnLogickiFizicki: kreiranje.sql

drop table if exists item_reservtion_junction;
drop table if exists Reservation;
drop type if exists status_enum;
drop table if exists Restaurant_Table;
drop table if exists Item;
drop table if exists Menu;
drop table if exists Restaurant;
drop table if exists Payment;
drop table if exists Customer;
drop type if exists customer_type;

create type customer_type as enum('premium', 'gold', 'standard', 'platinum');

CREATE TABLE Customer(
  customerId SERIAL PRIMARY KEY ,
  registrationDate DATE,
  firstName TEXT,
  lastName TEXT,
  email TEXT,
  pass TEXT,
  customer_role customer_type,
  phone TEXT,
  address TEXT,
  membershipLevel INT 
);

CREATE TABLE Payment(
  paymentId SERIAL PRIMARY KEY,
  customerId INT DEFAULT -1 REFERENCES Customer(customerId) ON DELETE SET DEFAULT ON UPDATE CASCADE,
  totalPrice INT NOT NULL,
  dateCreation DATE, 
  reservationNumber INT  
);

CREATE TABLE Restaurant(
  restaurantId SERIAL PRIMARY KEY,
  restaurantName TEXT NOT NULL,
  address TEXT NOT NULL,
  cuisineType TEXT,
  phone TEXT,
  operatingHours TEXT,
  website TEXT,
  deposit FLOAT default 0.0,
  socialMediaLinks TEXT,
  rating FLOAT DEFAULT 0.0
);

CREATE TABLE Menu (
  menuId INT PRIMARY KEY,
  restaurantId INT REFERENCES Restaurant(restaurantId) ON DELETE CASCADE ON UPDATE CASCADE,
  category TEXT not NULL
);

create table Item(
        itemId SERIAL primary key,
        menuId INT REFERENCES Menu(menuId) ON DELETE CASCADE ON UPDATE CASCADE, 
        itemName TEXT, 
        price FLOAT default 0.0, 
        description TEXT, 
        dietaryInformation TEXT
);

CREATE TABLE Restaurant_Table(
  tableId SERIAL PRIMARY KEY,
  restaurantId INT REFERENCES Restaurant(restaurantId) ON DELETE CASCADE ON UPDATE CASCADE,
  capacity INT NOT NULL,
  table_location TEXT NOT NULL,
  isSmokingArea BOOL DEFAULT false,
  description TEXT
);

CREATE TYPE status_enum AS ENUM ('reserved', 'canceled', 'pending');

CREATE TABLE Reservation (
  reservationId SERIAL PRIMARY KEY,
  customerId INT DEFAULT -1 REFERENCES Customer(customerId) ON DELETE SET DEFAULT ON UPDATE CASCADE,
  restaurantId INT DEFAULT -1 REFERENCES Restaurant(restaurantId) ON DELETE SET DEFAULT ON UPDATE CASCADE,
  tableId INT default -1 references Restaurant_Table(tableId) on delete set default on update cascade,
  paymentStatus bool not null default false, 
  partySize int not null,
  reservationDateTime Date default (now() at time zone 'utc'), 
  specialRequests text,
  status status_enum,
  checkInTime timestamp not null,
  checkOutTime timestamp not null,
  totalPrice float default 0.0 not null
);

CREATE TABLE item_reservation_junction (
    reservationId INT,
    itemId INT,
    PRIMARY KEY(reservationId, itemId),
    CONSTRAINT fk_reservations FOREIGN KEY(reservationId) REFERENCES Reservation(reservationId),
    CONSTRAINT fk_item FOREIGN KEY(itemId) REFERENCES Item(itemId)
);