34 lines
1.2 KiB
PL/PgSQL
34 lines
1.2 KiB
PL/PgSQL
-- Course Announcements System
|
|
-- Allows instructors to post announcements to students with automatic notifications
|
|
|
|
CREATE TABLE IF NOT EXISTS course_announcements (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
|
|
author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
is_pinned BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes for performance
|
|
CREATE INDEX idx_announcements_course ON course_announcements(course_id);
|
|
CREATE INDEX idx_announcements_org ON course_announcements(organization_id);
|
|
CREATE INDEX idx_announcements_pinned ON course_announcements(is_pinned, created_at DESC);
|
|
|
|
-- Trigger to update updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION update_announcement_timestamp()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER announcement_updated_at
|
|
BEFORE UPDATE ON course_announcements
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_announcement_timestamp();
|