photoplaces/backend/migrations/000004_create_places.up.sql

CREATE TABLE places (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type VARCHAR(10) NOT NULL CHECK (type IN ('place', 'studio')),
    owner_id UUID NOT NULL REFERENCES users(id),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    address VARCHAR(500),
    coordinates GEOGRAPHY(Point, 4326) NOT NULL,
    cover_image TEXT,
    access_info TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'pending_moderation'
        CHECK (status IN ('draft', 'pending_moderation', 'published', 'rejected', 'archived')),
    moderation_comment TEXT,
    rating DECIMAL(2,1) DEFAULT 0,
    reviews_count INT DEFAULT 0,
    hourly_rate INT,
    currency VARCHAR(3) DEFAULT 'RUB',
    min_hours INT DEFAULT 1,
    booking_url TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ,
    published_at TIMESTAMPTZ
);

CREATE INDEX idx_places_coordinates ON places USING GIST (coordinates);
CREATE INDEX idx_places_type ON places(type);
CREATE INDEX idx_places_status ON places(status);
CREATE INDEX idx_places_owner_id ON places(owner_id);
CREATE INDEX idx_places_rating ON places(rating);
CREATE INDEX idx_places_published_at ON places(published_at);

CREATE TABLE place_images (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    place_id UUID NOT NULL REFERENCES places(id) ON DELETE CASCADE,
    url TEXT NOT NULL,
    alt VARCHAR(255),
    sort_order INT DEFAULT 0,
    is_cover BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_place_images_place_id ON place_images(place_id);

CREATE TABLE place_tags (
    place_id UUID NOT NULL REFERENCES places(id) ON DELETE CASCADE,
    tag_id VARCHAR(50) NOT NULL REFERENCES tags(id),
    PRIMARY KEY (place_id, tag_id)
);

CREATE TABLE place_features (
    place_id UUID NOT NULL REFERENCES places(id) ON DELETE CASCADE,
    feature_id VARCHAR(50) NOT NULL REFERENCES features(id),
    PRIMARY KEY (place_id, feature_id)
);