CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE "notifications" (
    "id" uuid NOT NULL DEFAULT gen_random_uuid(),
    "app_key" TEXT NOT NULL DEFAULT 'all',
    "audience" TEXT NOT NULL DEFAULT 'all',
    "category" TEXT NOT NULL DEFAULT 'notice',
    "title" TEXT NOT NULL,
    "body" TEXT NOT NULL,
    "link_url" TEXT,
    "priority" INTEGER NOT NULL DEFAULT 0,
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "starts_at" TIMESTAMP(3),
    "ends_at" TIMESTAMP(3),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "notifications_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "notification_reads" (
    "id" uuid NOT NULL DEFAULT gen_random_uuid(),
    "notification_id" uuid NOT NULL,
    "auth_user_id" uuid NOT NULL,
    "read_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "notification_reads_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "notifications_app_key_audience_is_active_priority_idx" ON "notifications"("app_key", "audience", "is_active", "priority");
CREATE INDEX "notifications_is_active_created_at_idx" ON "notifications"("is_active", "created_at");
CREATE UNIQUE INDEX "notification_reads_notification_id_auth_user_id_key" ON "notification_reads"("notification_id", "auth_user_id");
CREATE INDEX "notification_reads_auth_user_id_idx" ON "notification_reads"("auth_user_id");

ALTER TABLE "notification_reads" ADD CONSTRAINT "notification_reads_notification_id_fkey" FOREIGN KEY ("notification_id") REFERENCES "notifications"("id") ON DELETE CASCADE ON UPDATE CASCADE;

COMMENT ON TABLE "notifications" IS '런처와 모든 앱이 공통으로 사용하는 알림 메시지';
COMMENT ON COLUMN "notifications"."id" IS '알림 고유 ID';
COMMENT ON COLUMN "notifications"."app_key" IS '알림 대상 앱 식별자. all이면 전체 앱 대상';
COMMENT ON COLUMN "notifications"."audience" IS '알림 대상 사용자 그룹. all, user, student, instructor, admin 등을 사용';
COMMENT ON COLUMN "notifications"."category" IS '알림 분류';
COMMENT ON COLUMN "notifications"."title" IS '알림 제목';
COMMENT ON COLUMN "notifications"."body" IS '알림 본문';
COMMENT ON COLUMN "notifications"."link_url" IS '알림 클릭 시 이동할 링크 URL';
COMMENT ON COLUMN "notifications"."priority" IS '알림 우선순위. 값이 높을수록 우선 노출';
COMMENT ON COLUMN "notifications"."is_active" IS '알림 활성 여부';
COMMENT ON COLUMN "notifications"."starts_at" IS '알림 노출 시작 일시';
COMMENT ON COLUMN "notifications"."ends_at" IS '알림 노출 종료 일시';
COMMENT ON COLUMN "notifications"."created_at" IS '알림 생성 일시';
COMMENT ON COLUMN "notifications"."updated_at" IS '알림 수정 일시';

COMMENT ON TABLE "notification_reads" IS '사용자별 공통 알림 읽음 처리';
COMMENT ON COLUMN "notification_reads"."id" IS '알림 읽음 레코드 고유 ID';
COMMENT ON COLUMN "notification_reads"."notification_id" IS '알림 ID';
COMMENT ON COLUMN "notification_reads"."auth_user_id" IS '중앙 auth DB users.id';
COMMENT ON COLUMN "notification_reads"."read_at" IS '알림 읽음 일시';

INSERT INTO "notifications" ("app_key", "audience", "category", "title", "body", "priority", "created_at", "updated_at")
VALUES
  ('LMS', 'instructor', 'assignment', '신규 과제 검토 요청', '자율주행 인지 실습 과정에 제출된 과제 3건의 검토가 필요합니다.', 100, '2026-06-02T00:00:00.000Z', CURRENT_TIMESTAMP),
  ('LMS', 'instructor', 'schedule', '라이브 세션 일정 안내', '오늘 15:00에 SLAM 기초 라이브 세션이 예정되어 있습니다.', 90, '2026-06-01T00:00:00.000Z', CURRENT_TIMESTAMP),
  ('LMS', 'instructor', 'system', '시스템 점검 안내', '6월 5일 02:00부터 03:00까지 LMS 점검이 진행됩니다.', 80, '2026-05-31T00:00:00.000Z', CURRENT_TIMESTAMP),
  ('LMS', 'student', 'assignment', '과제 제출 마감 안내', '인지 실습 리포트 제출 마감이 오늘 18:00까지입니다.', 100, '2026-06-02T00:00:00.000Z', CURRENT_TIMESTAMP),
  ('LMS', 'student', 'course', '신규 강의 공개', '자율주행 센서 융합 기초 강의가 새로 공개되었습니다.', 90, '2026-06-01T00:00:00.000Z', CURRENT_TIMESTAMP),
  ('LMS', 'student', 'schedule', '실습 세션 안내', '내일 10:00에 ROS2 실습 세션이 예정되어 있습니다.', 80, '2026-05-31T00:00:00.000Z', CURRENT_TIMESTAMP);