-- Table to store users
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table to store properties
CREATE TABLE properties (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  location VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  property_type VARCHAR(50) NOT NULL,
  agent_id INT REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table to store property images
CREATE TABLE property_images (
  id SERIAL PRIMARY KEY,
  property_id INT REFERENCES properties(id),
  image_url VARCHAR(255) NOT NULL
);

-- Table to store property reviews
CREATE TABLE property_reviews (
  id SERIAL PRIMARY KEY,
  property_id INT REFERENCES properties(id),
  user_id INT REFERENCES users(id),
  rating INT NOT NULL,
  comment TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table to store user favorites
CREATE TABLE user_favorites (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  property_id INT REFERENCES properties(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table to store property bookings
CREATE TABLE property_bookings (
  id SERIAL PRIMARY KEY,
  property_id INT REFERENCES properties(id),
  user_id INT REFERENCES users(id),
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table to store user notifications
CREATE TABLE user_notifications (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  message TEXT NOT NULL,
  is_read BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table to store user messages
CREATE TABLE user_messages (
  id SERIAL PRIMARY KEY,
  sender_id INT REFERENCES users(id),
  recipient_id INT REFERENCES users(id),
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);