`src/migrations/001_create_tables.sql`

63 lines
-- Create inquiries table
CREATE TABLE IF NOT EXISTS inquiries (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(50) NOT NULL,
  check_in_date DATE NOT NULL,
  check_out_date DATE NOT NULL,
  guest_count INTEGER NOT NULL CHECK (guest_count > 0),
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT valid_dates CHECK (check_out_date > check_in_date)
);

-- Create index on email for faster lookups
CREATE INDEX IF NOT EXISTS idx_inquiries_email ON inquiries(email);

-- Create index on check-in date for availability queries
CREATE INDEX IF NOT EXISTS idx_inquiries_check_in ON inquiries(check_in_date);

-- Create index on created_at for sorting recent inquiries
CREATE INDEX IF NOT EXISTS idx_inquiries_created_at ON inquiries(created_at DESC);

-- Create properties table
CREATE TABLE IF NOT EXISTS properties (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  description TEXT NOT NULL,
  amenities JSONB NOT NULL,
  images JSONB NOT NULL,
  airbnb_url VARCHAR(500) NOT NULL,
  capacity INTEGER CHECK (capacity > 0),
  bedrooms INTEGER CHECK (bedrooms >= 0),
  bathrooms NUMERIC(3,1) CHECK (bathrooms >= 0),
  size_sqm NUMERIC(10,2) CHECK (size_sqm >= 0),
  price_per_night NUMERIC(10,2) CHECK (price_per_night >= 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create index on property name for faster lookups
CREATE INDEX IF NOT EXISTS idx_properties_name ON properties(name);

-- Create trigger to update updated_at timestamp for inquiries
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_inquiries_updated_at
  BEFORE UPDATE ON inquiries
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_properties_updated_at
  BEFORE UPDATE ON properties
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();