photoplaces/database/migrations/001-initial-schema.sql

-- Миграция 001: Инициализация основной схемы базы данных
-- Автор: PhotoPlaces Team
-- Дата: 2025-12-04

BEGIN;

-- Создание расширения PostGIS для работы с геоданными
CREATE EXTENSION IF NOT EXISTS postgis;

-- Таблица пользователей системы
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('superadmin', 'moderator', 'landlord', 'performer', 'customer', 'guest')),
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE users IS 'Таблица пользователей системы с различными ролями';
COMMENT ON COLUMN users.role IS 'Роль пользователя: superadmin, moderator, landlord, performer, customer, guest';

-- Таблица мест для фотосессий
CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    location GEOGRAPHY(Point, 4326) NOT NULL,
    address TEXT,
    type VARCHAR(20) NOT NULL CHECK (type IN ('place', 'studio')),
    owner_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    is_approved BOOLEAN DEFAULT FALSE,
    rating DECIMAL(3,2) DEFAULT 0.00,
    price_per_hour DECIMAL(10,2),
    max_capacity INTEGER,
    amenities JSONB,
    tags VARCHAR(255)[],
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE places IS 'Таблица мест для фотосессий с геометрическими координатами';
COMMENT ON COLUMN places.location IS 'Географические координаты места в формате WGS84';
COMMENT ON COLUMN places.type IS 'Тип места: place (место) или studio (студия)';

-- Таблица изображений мест
CREATE TABLE place_images (
    id SERIAL PRIMARY KEY,
    place_id INTEGER REFERENCES places(id) ON DELETE CASCADE,
    image_url VARCHAR(500) NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE place_images IS 'Таблица изображений для мест';

-- Таблица услуг фотографов
CREATE TABLE services (
    id SERIAL PRIMARY KEY,
    performer_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    duration_hours INTEGER,
    style_tags VARCHAR(255)[],
    rating DECIMAL(3,2) DEFAULT 0.00,
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE services IS 'Таблица услуг фотографов';

-- Таблица бронирований
CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    place_id INTEGER REFERENCES places(id) ON DELETE CASCADE,
    customer_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'cancelled', 'completed')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE bookings IS 'Таблица бронирований мест';

-- Создание индексов для оптимизации запросов
CREATE INDEX idx_places_location ON places USING GIST(location);
CREATE INDEX idx_places_type ON places(type);
CREATE INDEX idx_places_approved ON places(is_approved);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_bookings_dates ON bookings(start_time, end_time);
CREATE INDEX idx_services_performer ON services(performer_id);
CREATE INDEX idx_place_images_place ON place_images(place_id);

-- Функция для автоматического обновления updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Триггеры для автоматического обновления updated_at
CREATE TRIGGER update_users_updated_at 
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_places_updated_at 
    BEFORE UPDATE ON places
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_services_updated_at 
    BEFORE UPDATE ON services
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_bookings_updated_at 
    BEFORE UPDATE ON bookings
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

COMMIT;

-- Комментарий к миграции
COMMENT ON MIGRATION 001 IS 'Инициализация основной схемы базы данных с таблицами пользователей, мест, услуг и бронирований';