-- Rockview Campus Events social upgrade (PostgreSQL)
-- Safe to run before copying the PHP files. The PHP also self-creates these structures.

ALTER TABLE events ADD COLUMN IF NOT EXISTS author_id BIGINT NULL;
ALTER TABLE events ADD COLUMN IF NOT EXISTS category VARCHAR(80) NULL DEFAULT 'General';
ALTER TABLE events ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE events ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE events ADD COLUMN IF NOT EXISTS is_active BOOLEAN NULL DEFAULT TRUE;

CREATE TABLE IF NOT EXISTS campus_event_media (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT NOT NULL,
    media_type VARCHAR(20) NOT NULL,
    media_path TEXT NOT NULL,
    thumbnail_path TEXT NULL,
    size_bytes BIGINT NOT NULL DEFAULT 0,
    position INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS campus_event_likes (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT campus_event_likes_unique UNIQUE(event_id, user_id)
);

CREATE TABLE IF NOT EXISTS campus_event_comments (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    parent_comment_id BIGINT NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS campus_event_comment_mentions (
    id BIGSERIAL PRIMARY KEY,
    comment_id BIGINT NOT NULL,
    mentioned_user_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT campus_event_mentions_unique UNIQUE(comment_id, mentioned_user_id)
);

CREATE TABLE IF NOT EXISTS campus_event_notifications (
    id BIGSERIAL PRIMARY KEY,
    recipient_user_id BIGINT NOT NULL,
    actor_user_id BIGINT NULL,
    event_id BIGINT NULL,
    comment_id BIGINT NULL,
    type VARCHAR(40) NOT NULL DEFAULT 'general',
    title VARCHAR(180) NOT NULL,
    message VARCHAR(300) NOT NULL,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_event_media_event ON campus_event_media(event_id, position);
CREATE INDEX IF NOT EXISTS idx_event_comments_event ON campus_event_comments(event_id, created_at);
CREATE INDEX IF NOT EXISTS idx_event_notifications_recipient ON campus_event_notifications(recipient_user_id, is_read, created_at DESC);
