-- Rockview Connect Posts, Media, Comments, Notifications and Communities

CREATE TABLE IF NOT EXISTS connect_posts (
    id BIGSERIAL PRIMARY KEY,
    author_id BIGINT NULL,
    author_name VARCHAR(255) NOT NULL,
    author_email VARCHAR(255) NOT NULL,
    author_profile_picture TEXT NULL,
    campus VARCHAR(255) NULL,
    content TEXT NOT NULL DEFAULT '',
    tagged_name VARCHAR(255) NULL,
    location VARCHAR(255) NULL,
    visibility VARCHAR(40) NOT NULL DEFAULT 'public',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS connect_post_media (
    id BIGSERIAL PRIMARY KEY,
    post_id BIGINT NOT NULL REFERENCES connect_posts(id) ON DELETE CASCADE,
    media_type VARCHAR(20) NOT NULL,
    file_path TEXT NOT NULL,
    original_name VARCHAR(255) NULL,
    mime_type VARCHAR(120) NULL,
    size_bytes BIGINT NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS connect_post_likes (
    post_id BIGINT NOT NULL REFERENCES connect_posts(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL,
    user_email VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, user_id)
);

CREATE TABLE IF NOT EXISTS connect_post_comments (
    id BIGSERIAL PRIMARY KEY,
    post_id BIGINT NOT NULL REFERENCES connect_posts(id) ON DELETE CASCADE,
    parent_id BIGINT NULL REFERENCES connect_post_comments(id) ON DELETE CASCADE,
    author_id BIGINT NULL,
    author_name VARCHAR(255) NOT NULL,
    author_email VARCHAR(255) NOT NULL,
    author_profile_picture TEXT NULL,
    content TEXT NOT NULL DEFAULT '',
    image_path TEXT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS connect_communities (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    creator_id BIGINT NOT NULL,
    creator_name VARCHAR(255) NOT NULL,
    creator_email VARCHAR(255) NOT NULL,
    campus VARCHAR(255) NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS connect_community_members (
    community_id BIGINT NOT NULL REFERENCES connect_communities(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    user_email VARCHAR(255) NOT NULL,
    role VARCHAR(30) NOT NULL DEFAULT 'member',
    joined_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (community_id, user_id)
);

CREATE TABLE IF NOT EXISTS connect_notifications (
    id BIGSERIAL PRIMARY KEY,
    recipient_id BIGINT NOT NULL,
    recipient_email VARCHAR(255) NOT NULL,
    actor_id BIGINT NULL,
    actor_name VARCHAR(255) NOT NULL,
    actor_email VARCHAR(255) NULL,
    type VARCHAR(40) NOT NULL,
    title VARCHAR(180) NOT NULL,
    body TEXT NOT NULL DEFAULT '',
    post_id BIGINT NULL,
    comment_id BIGINT NULL,
    community_id BIGINT NULL,
    dedupe_key VARCHAR(255) NULL,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE connect_notifications ADD COLUMN IF NOT EXISTS dedupe_key VARCHAR(255) NULL;
ALTER TABLE connect_notifications ADD COLUMN IF NOT EXISTS community_id BIGINT NULL;

CREATE INDEX IF NOT EXISTS idx_connect_posts_created_at ON connect_posts (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_connect_post_comments_post ON connect_post_comments (post_id, created_at);
CREATE INDEX IF NOT EXISTS idx_connect_communities_created_at ON connect_communities (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_connect_notifications_recipient ON connect_notifications (recipient_id, is_read, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_connect_notifications_post ON connect_notifications (post_id);
CREATE INDEX IF NOT EXISTS idx_connect_notifications_dedupe ON connect_notifications (recipient_id, dedupe_key);
