-- Отзывы и рейтинги после встречи
CREATE TABLE IF NOT EXISTS reviews (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    reviewer_id   UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    reviewed_id   UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    chat_id       UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
    rating        SMALLINT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    body          TEXT,
    anonymous     BOOLEAN NOT NULL DEFAULT FALSE,
    created_at    TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (reviewer_id, chat_id),  -- один отзыв на чат
    CHECK (reviewer_id <> reviewed_id)
);

CREATE INDEX IF NOT EXISTS idx_reviews_reviewed ON reviews(reviewed_id, created_at DESC);

-- Агрегаты (можно считать по reviews но удобнее держать)
CREATE TABLE IF NOT EXISTS user_stats (
    user_id          UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    rating_avg       NUMERIC(3,2) NOT NULL DEFAULT 0,
    rating_count     INTEGER NOT NULL DEFAULT 0,
    reviews_left     INTEGER NOT NULL DEFAULT 0,
    last_active_at   TIMESTAMPTZ DEFAULT NOW()
);