trip_schema.sql - poll - Andromeda141/Adventure-service

trip_schema.sql
234 строки11 KB
andrewvnukov
Polls 1.0 + Profile
07 июн 2026, 15:15
07 июн 2026, 15:15e6f3e2e
-- ==========================================
-- Database Schema v2.0
-- Changes from v1.5:
--   - activities: добавлена колонка is_pending (bool)
--   - activity_polls: удалена колонка required_percentage
--   - v_poll_results: убран required_percentage из SELECT и GROUP BY
-- ==========================================

-- 0. Enums
CREATE TYPE trip_status_enum AS ENUM ('drafting', 'planning', 'ready', 'active', 'completed', 'cancelled', 'archived');
CREATE TYPE activity_type_enum AS ENUM ('sightseeing', 'restaurant', 'hotel', 'transport', 'shopping', 'other');
CREATE TYPE poll_status_enum AS ENUM ('active', 'passed', 'rejected', 'expired');
CREATE TYPE poll_choice_enum AS ENUM ('for', 'against');
CREATE TYPE user_gender_enum AS ENUM ('male', 'female', 'other');
CREATE TYPE member_role_enum AS ENUM ('owner', 'admin', 'member');

-- 1. Users
CREATE TABLE users (
    id            UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    login         VARCHAR(50)  UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name          VARCHAR(100) NOT NULL,
    surname       VARCHAR(100) NOT NULL,
    gender        user_gender_enum NOT NULL,
    age           INTEGER      NOT NULL CHECK (age >= 13),
    photo_url     TEXT,
    created_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_users_login ON users(login);

-- 2. Trips
CREATE TABLE trips (
    id            UUID              PRIMARY KEY DEFAULT gen_random_uuid(),
    title         VARCHAR(255)      NOT NULL,
    description   TEXT,
    start_date    DATE              NOT NULL,
    end_date      DATE,
    base_currency CHAR(3)           NOT NULL DEFAULT 'RUB' CHECK (base_currency ~ '^[A-Z]{3}$'),
    status        trip_status_enum  NOT NULL DEFAULT 'drafting',
    created_at    TIMESTAMPTZ       NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ       NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_trip_dates CHECK (end_date >= start_date)
);
CREATE INDEX idx_trips_status ON trips(status);
CREATE INDEX idx_trips_dates  ON trips(start_date, end_date);

-- 2.1. Trip Members
CREATE TABLE trip_members (
    trip_id   UUID             NOT NULL,
    user_id   UUID             NOT NULL,
    role      member_role_enum NOT NULL DEFAULT 'member',
    joined_at TIMESTAMPTZ      NOT NULL DEFAULT NOW(),
    PRIMARY KEY (trip_id, user_id),
    CONSTRAINT fk_trip_members_trip FOREIGN KEY (trip_id) REFERENCES trips(id) ON DELETE CASCADE,
    CONSTRAINT fk_trip_members_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX idx_trip_members_user_id ON trip_members(user_id);
CREATE INDEX idx_trip_members_owner   ON trip_members(user_id) WHERE role = 'owner';

-- 3. Activities
-- day_number хранит номер дня поездки
-- cost / currency / cost_in_base_currency — nullable, заполняются бэкендом
-- is_pending = TRUE означает, что активность создана в рамках голосования и ещё не подтверждена:
--   - после прохождения голосования (passed) → is_pending = FALSE (активность видна в поездке)
--   - после провала/истечения (rejected/expired) → строка удаляется
CREATE TABLE activities (
    id                    UUID                PRIMARY KEY DEFAULT gen_random_uuid(),
    trip_id               UUID                NOT NULL,
    title                 VARCHAR(255)        NOT NULL,
    type                  activity_type_enum  NOT NULL,

    location_name         VARCHAR(255),
    latitude              DECIMAL(9, 6),
    longitude             DECIMAL(9, 6),
    address               TEXT,

    start_at              TIMESTAMPTZ         NOT NULL,
    end_at                TIMESTAMPTZ         NOT NULL,

    -- nullable: бэкенд может не передавать стоимость
    cost                  DECIMAL(10, 2),
    currency              CHAR(3)             CHECK (currency ~ '^[A-Z]{3}$'),
    cost_in_base_currency DECIMAL(10, 2),

    notes                 TEXT,
    day_number            INTEGER             NOT NULL DEFAULT 1,

    -- флаг ожидания голосования; FALSE = подтверждённая активность
    is_pending            BOOLEAN             NOT NULL DEFAULT FALSE,
    created_by            UUID                REFERENCES users(id) ON DELETE SET NULL,

    created_at            TIMESTAMPTZ         NOT NULL DEFAULT NOW(),
    updated_at            TIMESTAMPTZ         NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_activities_trip FOREIGN KEY (trip_id) REFERENCES trips(id) ON DELETE CASCADE,
    CONSTRAINT chk_activity_time  CHECK (end_at >= start_at)
);
CREATE INDEX idx_activities_trip_id   ON activities(trip_id);
CREATE INDEX idx_activities_time      ON activities(trip_id, start_at);
CREATE INDEX idx_activities_is_pending ON activities(trip_id, is_pending);

-- 3.1. Activity Assignees
CREATE TABLE activity_assignees (
    activity_id UUID NOT NULL,
    user_id     UUID NOT NULL,
    PRIMARY KEY (activity_id, user_id),
    CONSTRAINT fk_assignees_activity FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_assignees_user     FOREIGN KEY (user_id)     REFERENCES users(id)       ON DELETE CASCADE
);
CREATE INDEX idx_assignees_user ON activity_assignees(user_id);

-- 4. Invite Links
CREATE TABLE invite_links (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    trip_id    UUID        NOT NULL,
    code       VARCHAR(12) UNIQUE NOT NULL,
    created_by UUID        NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    is_active  BOOLEAN     NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_invite_trip FOREIGN KEY (trip_id)    REFERENCES trips(id) ON DELETE CASCADE,
    CONSTRAINT fk_invite_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX idx_invite_code ON invite_links(code);
CREATE INDEX idx_invite_trip ON invite_links(trip_id);

-- 5. Activity Polls
-- proposed_activity_id ссылается на activities.id; при удалении поездки каскадно удаляется.
-- Порог голосования фиксирован на уровне бэкенда (51%).
CREATE TABLE activity_polls (
    id                   UUID             PRIMARY KEY DEFAULT gen_random_uuid(),
    trip_id              UUID             NOT NULL,
    proposed_activity_id UUID             NOT NULL,
    title                VARCHAR(255)     NOT NULL,
    description          TEXT,
    created_at           TIMESTAMPTZ      NOT NULL DEFAULT NOW(),
    expires_at           TIMESTAMPTZ      NOT NULL,
    status               poll_status_enum NOT NULL DEFAULT 'active',
    created_by           UUID             REFERENCES users(id) ON DELETE SET NULL,

    CONSTRAINT fk_polls_trip     FOREIGN KEY (trip_id)              REFERENCES trips(id)      ON DELETE CASCADE,
    CONSTRAINT fk_polls_activity FOREIGN KEY (proposed_activity_id) REFERENCES activities(id) ON DELETE CASCADE
);
CREATE INDEX idx_activity_polls_trip_id   ON activity_polls(trip_id);
CREATE INDEX idx_activity_polls_created_by ON activity_polls(created_by);
CREATE INDEX idx_activities_created_by     ON activities(created_by);
CREATE INDEX idx_activity_polls_status  ON activity_polls(status);

-- 5.1. Poll Votes
CREATE TABLE poll_votes (
    poll_id  UUID             NOT NULL,
    user_id  UUID             NOT NULL,
    choice   poll_choice_enum NOT NULL,
    voted_at TIMESTAMPTZ      NOT NULL DEFAULT NOW(),
    PRIMARY KEY (poll_id, user_id),
    CONSTRAINT fk_votes_poll FOREIGN KEY (poll_id) REFERENCES activity_polls(id) ON DELETE CASCADE,
    CONSTRAINT fk_votes_user FOREIGN KEY (user_id) REFERENCES users(id)          ON DELETE CASCADE
);
CREATE INDEX idx_poll_votes_user ON poll_votes(user_id);

-- ==========================================
-- VIEWS
-- ==========================================

CREATE OR REPLACE VIEW v_poll_results AS
SELECT
    p.id      AS poll_id,
    p.trip_id,
    p.status,
    COUNT(v.user_id)                                                    AS voter_count,
    COALESCE(SUM(CASE WHEN v.choice = 'for'     THEN 1 ELSE 0 END), 0) AS votes_for,
    COALESCE(SUM(CASE WHEN v.choice = 'against' THEN 1 ELSE 0 END), 0) AS votes_against
FROM activity_polls p
LEFT JOIN poll_votes v ON p.id = v.poll_id
GROUP BY p.id, p.trip_id, p.status;

-- ==========================================
-- TRIGGERS
-- ==========================================

CREATE OR REPLACE FUNCTION trigger_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at_users      BEFORE UPDATE ON users      FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
CREATE TRIGGER set_updated_at_trips      BEFORE UPDATE ON trips      FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
CREATE TRIGGER set_updated_at_activities BEFORE UPDATE ON activities FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

-- ==========================================
-- MIGRATION (если БД уже существует)
-- ==========================================
-- ALTER TABLE activities ADD COLUMN is_pending BOOLEAN NOT NULL DEFAULT FALSE;
-- CREATE INDEX idx_activities_is_pending ON activities(trip_id, is_pending);
-- ALTER TABLE activity_polls DROP COLUMN IF EXISTS required_percentage;
-- CREATE OR REPLACE VIEW v_poll_results AS ... (обновить view выше)

-- ==========================================
-- TRIGGER: лимит истории голосований
-- ==========================================
-- После завершения голосования (статус меняется с active на passed/rejected/expired)
-- удаляем самые старые записи истории если их больше 10 на поездку.
-- "История" = голосования со статусом != active.

CREATE OR REPLACE FUNCTION fn_limit_poll_history()
RETURNS TRIGGER AS $$
BEGIN
    -- Срабатывает только когда статус меняется НА завершённый
    IF NEW.status = OLD.status OR NEW.status = 'active' THEN
        RETURN NEW;
    END IF;

    -- Удаляем лишние записи истории для этой поездки (оставляем 10 свежих)
    DELETE FROM activity_polls
    WHERE id IN (
        SELECT id FROM activity_polls
        WHERE trip_id = NEW.trip_id
          AND status != 'active'
        ORDER BY expires_at DESC
        OFFSET 10
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_limit_poll_history
AFTER UPDATE OF status ON activity_polls
FOR EACH ROW
EXECUTE FUNCTION fn_limit_poll_history();