CREATE TABLE IF NOT EXISTS campus_announcements (
    id BIGSERIAL PRIMARY KEY,
    author_id BIGINT NOT NULL,
    title VARCHAR(180) NOT NULL,
    body TEXT NOT NULL,
    category VARCHAR(80) NOT NULL DEFAULT 'General',
    priority VARCHAR(20) NOT NULL DEFAULT 'Normal',
    audience VARCHAR(50) NOT NULL DEFAULT 'All Staff',
    campus VARCHAR(180),
    status VARCHAR(20) NOT NULL DEFAULT 'published',
    is_pinned BOOLEAN NOT NULL DEFAULT FALSE,
    scheduled_at TIMESTAMPTZ,
    published_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS campus_announcement_media (
    id BIGSERIAL PRIMARY KEY,
    announcement_id BIGINT NOT NULL REFERENCES campus_announcements(id) ON DELETE CASCADE,
    media_type VARCHAR(20) NOT NULL DEFAULT 'image',
    media_path TEXT NOT NULL,
    thumbnail_path TEXT,
    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_announcement_comments (
    id BIGSERIAL PRIMARY KEY,
    announcement_id BIGINT NOT NULL REFERENCES campus_announcements(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL,
    parent_comment_id BIGINT REFERENCES campus_announcement_comments(id) ON DELETE CASCADE,
    comment TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS campus_announcement_likes (
    id BIGSERIAL PRIMARY KEY,
    announcement_id BIGINT NOT NULL REFERENCES campus_announcements(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (announcement_id, user_id)
);

CREATE TABLE IF NOT EXISTS campus_announcement_mentions (
    id BIGSERIAL PRIMARY KEY,
    comment_id BIGINT NOT NULL REFERENCES campus_announcement_comments(id) ON DELETE CASCADE,
    mentioned_user_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (comment_id, mentioned_user_id)
);

CREATE TABLE IF NOT EXISTS campus_announcement_notifications (
    id BIGSERIAL PRIMARY KEY,
    recipient_user_id BIGINT NOT NULL,
    actor_user_id BIGINT,
    announcement_id BIGINT REFERENCES campus_announcements(id) ON DELETE CASCADE,
    comment_id BIGINT REFERENCES campus_announcement_comments(id) ON DELETE CASCADE,
    type VARCHAR(40) NOT NULL DEFAULT 'general',
    title VARCHAR(180) NOT NULL,
    message VARCHAR(255) NOT NULL,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
