DatabaseCreation: base.sql

CREATE TABLE Location (
  location_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  city VARCHAR(100) NOT NULL,
  region VARCHAR(100),
  country VARCHAR(100) NOT NULL
);

CREATE TABLE Users (
  user_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email VARCHAR(150) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  phone VARCHAR(30),
  registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  account_status VARCHAR(30) DEFAULT 'PENDING',
  CHECK (email LIKE '%@%.%'),
  CHECK (account_status IN ('ACTIVE','INACTIVE','SUSPENDED','PENDING'))
);

CREATE TABLE Role (
  role_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  role_name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE UserRole (
  user_id INTEGER NOT NULL,
  role_id INTEGER NOT NULL,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES Role(role_id) ON DELETE RESTRICT
);

CREATE TABLE Genre (
  genre_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  genre_name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE ArtistType (
  artist_type_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  type_name VARCHAR(100) UNIQUE NOT NULL,
  description TEXT
);

CREATE TABLE VenueType (
  venue_type_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL,
  description TEXT
);

CREATE TABLE Venue (
  venue_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  venue_type_id INTEGER NOT NULL,
  location_id INTEGER,
  venue_name VARCHAR(150) NOT NULL,
  address VARCHAR(255) NOT NULL,
  capacity INTEGER,
  indoor_outdoor VARCHAR(20),
  FOREIGN KEY (venue_type_id) REFERENCES VenueType(venue_type_id) ON DELETE RESTRICT,
  FOREIGN KEY (location_id) REFERENCES Location(location_id) ON DELETE SET NULL,
  CHECK (capacity IS NULL OR capacity > 0),
  CHECK (indoor_outdoor IN ('INDOOR','OUTDOOR','MIXED'))
);

CREATE TABLE PerformanceDurationOption (
  duration_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  label VARCHAR(50) NOT NULL,
  duration_minutes INTEGER NOT NULL,
  CHECK (duration_minutes > 0)
);

CREATE TABLE ClientProfile (
  client_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id INTEGER UNIQUE NOT NULL,
  display_name VARCHAR(150),
  company_name VARCHAR(150),
  preferred_contact_method VARCHAR(50) DEFAULT 'EMAIL',
  location_id INTEGER,
  FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
  FOREIGN KEY (location_id) REFERENCES Location(location_id) ON DELETE SET NULL
);

CREATE TABLE Bookable (
  bookable_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  bookable_type VARCHAR(20) NOT NULL,
  display_name VARCHAR(150) NOT NULL,
  description TEXT,
  base_price_from DECIMAL(10,2),
  average_rating DECIMAL(3,2),
  location_id INTEGER,
  is_active BOOLEAN DEFAULT TRUE,
  FOREIGN KEY (location_id) REFERENCES Location(location_id) ON DELETE SET NULL,
  CHECK (bookable_type IN ('ARTIST','BAND')),
  CHECK (average_rating IS NULL OR (average_rating >= 0 AND average_rating <= 5))
);

CREATE TABLE ArtistProfile (
  artist_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  bookable_id INTEGER UNIQUE NOT NULL,
  user_id INTEGER UNIQUE NOT NULL,
  artist_type_id INTEGER NOT NULL,
  stage_name VARCHAR(150),
  biography TEXT,
  is_verified BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (bookable_id) REFERENCES Bookable(bookable_id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
  FOREIGN KEY (artist_type_id) REFERENCES ArtistType(artist_type_id) ON DELETE RESTRICT
);

CREATE TABLE BandProfile (
  band_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  bookable_id INTEGER UNIQUE NOT NULL,
  owner_user_id INTEGER,
  band_name VARCHAR(150) NOT NULL,
  formation_year INTEGER,
  biography TEXT,
  FOREIGN KEY (bookable_id) REFERENCES Bookable(bookable_id) ON DELETE CASCADE,
  FOREIGN KEY (owner_user_id) REFERENCES Users(user_id) ON DELETE SET NULL
);

CREATE TABLE BandMember (
  band_id INTEGER NOT NULL,
  artist_id INTEGER NOT NULL,
  PRIMARY KEY (band_id, artist_id),
  FOREIGN KEY (band_id) REFERENCES BandProfile(band_id) ON DELETE CASCADE,
  FOREIGN KEY (artist_id) REFERENCES ArtistProfile(artist_id) ON DELETE CASCADE
);

CREATE TABLE BookableGenre (
  bookable_id INTEGER NOT NULL,
  genre_id INTEGER NOT NULL,
  PRIMARY KEY (bookable_id, genre_id),
  FOREIGN KEY (bookable_id) REFERENCES Bookable(bookable_id) ON DELETE CASCADE,
  FOREIGN KEY (genre_id) REFERENCES Genre(genre_id) ON DELETE CASCADE
);

CREATE TABLE PricingRule (
  pricing_rule_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  bookable_id INTEGER NOT NULL,
  duration_id INTEGER NOT NULL,
  venue_type_id INTEGER,
  base_price DECIMAL(10,2) NOT NULL,
  valid_from TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  valid_to TIMESTAMP,
  FOREIGN KEY (bookable_id) REFERENCES Bookable(bookable_id) ON DELETE CASCADE,
  FOREIGN KEY (duration_id) REFERENCES PerformanceDurationOption(duration_id) ON DELETE RESTRICT,
  FOREIGN KEY (venue_type_id) REFERENCES VenueType(venue_type_id) ON DELETE SET NULL
);

CREATE TABLE AvailabilitySlot (
  slot_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  bookable_id INTEGER NOT NULL,
  start_datetime TIMESTAMP NOT NULL,
  end_datetime TIMESTAMP NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'AVAILABLE',
  FOREIGN KEY (bookable_id) REFERENCES Bookable(bookable_id) ON DELETE CASCADE,
  CHECK (end_datetime > start_datetime),
  CHECK (status IN ('AVAILABLE','BOOKED','BLOCKED'))
);

CREATE TABLE BookingRequest (
  request_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  client_id INTEGER NOT NULL,
  duration_id INTEGER NOT NULL,
  event_type VARCHAR(100) NOT NULL,
  event_date DATE NOT NULL,
  location_id INTEGER,
  venue_id INTEGER,
  venue_type_id INTEGER,
  FOREIGN KEY (client_id) REFERENCES ClientProfile(client_id) ON DELETE CASCADE,
  FOREIGN KEY (duration_id) REFERENCES PerformanceDurationOption(duration_id) ON DELETE RESTRICT,
  FOREIGN KEY (location_id) REFERENCES Location(location_id) ON DELETE SET NULL,
  FOREIGN KEY (venue_id) REFERENCES Venue(venue_id) ON DELETE SET NULL,
  FOREIGN KEY (venue_type_id) REFERENCES VenueType(venue_type_id) ON DELETE SET NULL
);

CREATE TABLE Offer (
  offer_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  request_id INTEGER NOT NULL,
  bookable_id INTEGER NOT NULL,
  duration_id INTEGER NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (request_id) REFERENCES BookingRequest(request_id) ON DELETE CASCADE,
  FOREIGN KEY (bookable_id) REFERENCES Bookable(bookable_id) ON DELETE CASCADE,
  FOREIGN KEY (duration_id) REFERENCES PerformanceDurationOption(duration_id) ON DELETE RESTRICT
);

CREATE TABLE Booking (
  booking_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  offer_id INTEGER UNIQUE NOT NULL,
  final_price DECIMAL(10,2) NOT NULL,
  booking_status VARCHAR(30) NOT NULL DEFAULT 'CREATED',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (offer_id) REFERENCES Offer(offer_id) ON DELETE RESTRICT,
  CHECK (booking_status IN ('CREATED','CONFIRMED','COMPLETED','CANCELLED'))
);

CREATE TABLE BookingStatusHistory (
  booking_status_history_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  booking_id INTEGER NOT NULL,
  changed_by_user_id INTEGER,
  new_status VARCHAR(30) NOT NULL,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (booking_id) REFERENCES Booking(booking_id) ON DELETE CASCADE,
  FOREIGN KEY (changed_by_user_id) REFERENCES Users(user_id) ON DELETE SET NULL,
  CHECK (new_status IN ('CREATED','CONFIRMED','COMPLETED','CANCELLED'))
);

CREATE TABLE Payment (
  payment_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  booking_id INTEGER NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_status VARCHAR(30) NOT NULL DEFAULT 'PENDING',
  FOREIGN KEY (booking_id) REFERENCES Booking(booking_id) ON DELETE CASCADE,
  CHECK (payment_status IN ('PAID','PENDING','FAILED'))
);

CREATE TABLE Review (
  review_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  booking_id INTEGER NOT NULL,
  rating INTEGER NOT NULL,
  comment TEXT,
  FOREIGN KEY (booking_id) REFERENCES Booking(booking_id) ON DELETE CASCADE,
  CHECK (rating BETWEEN 1 AND 5)
);

CREATE TABLE Message (
  message_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  sender_user_id INTEGER,
  receiver_user_id INTEGER,
  request_id INTEGER,
  booking_id INTEGER,
  content TEXT NOT NULL,
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sender_user_id) REFERENCES Users(user_id) ON DELETE SET NULL,
  FOREIGN KEY (receiver_user_id) REFERENCES Users(user_id) ON DELETE SET NULL,
  FOREIGN KEY (request_id) REFERENCES BookingRequest(request_id) ON DELETE CASCADE,
  FOREIGN KEY (booking_id) REFERENCES Booking(booking_id) ON DELETE CASCADE
);