xinghen.work/server/schema.sql

CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY,
  phone VARCHAR(20) UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'USER',
  organization VARCHAR(120) NOT NULL DEFAULT '',
  wechat VARCHAR(80) NOT NULL DEFAULT '',
  contact_name VARCHAR(80) NOT NULL DEFAULT '',
  bio VARCHAR(500) NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE users ADD COLUMN IF NOT EXISTS role VARCHAR(20) NOT NULL DEFAULT 'USER';

CREATE TABLE IF NOT EXISTS sessions (
  token_hash CHAR(64) PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);

CREATE TABLE IF NOT EXISTS courses (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(50) NOT NULL DEFAULT '',
  audience VARCHAR(100) NOT NULL DEFAULT '',
  description VARCHAR(500) NOT NULL DEFAULT '',
  status VARCHAR(30) NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT', 'WAITING_PRODUCTION', 'IN_PRODUCTION', 'COMPLETED', 'REJECTED')),
  production_notes TEXT NOT NULL DEFAULT '',
  ppt_original_name VARCHAR(255),
  ppt_stored_name VARCHAR(255),
  ppt_size BIGINT,
  ppt_mime_type VARCHAR(150),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  submitted_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ
);

ALTER TABLE courses ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE courses ADD COLUMN IF NOT EXISTS production_notes TEXT NOT NULL DEFAULT '';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS completed_at TIMESTAMPTZ;

DO $$
BEGIN
  ALTER TABLE courses DROP CONSTRAINT IF EXISTS courses_status_check;
  ALTER TABLE courses ADD CONSTRAINT courses_status_check CHECK (status IN ('DRAFT', 'WAITING_PRODUCTION', 'IN_PRODUCTION', 'COMPLETED', 'REJECTED'));
EXCEPTION
  WHEN OTHERS THEN NULL;
END $$;

CREATE INDEX IF NOT EXISTS idx_courses_user_created ON courses(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_courses_status_created ON courses(status, created_at DESC);

-- 课程分集表（Episodes）
CREATE TABLE IF NOT EXISTS episodes (
  id UUID PRIMARY KEY,
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  episode_number INT NOT NULL DEFAULT 1,
  title VARCHAR(200) NOT NULL,
  summary VARCHAR(500) NOT NULL DEFAULT '',
  lecture_notes TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_episodes_course ON episodes(course_id, episode_number ASC);

CREATE TABLE IF NOT EXISTS course_assets (
  id UUID PRIMARY KEY,
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  episode_id UUID REFERENCES episodes(id) ON DELETE CASCADE,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  size BIGINT NOT NULL,
  mime_type VARCHAR(150) NOT NULL DEFAULT 'application/octet-stream',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
ALTER TABLE course_assets ADD COLUMN IF NOT EXISTS episode_id UUID REFERENCES episodes(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS idx_assets_course ON course_assets(course_id, created_at);
CREATE INDEX IF NOT EXISTS idx_assets_episode ON course_assets(episode_id, created_at);

CREATE TABLE IF NOT EXISTS instructors (
  id UUID PRIMARY KEY,
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  name VARCHAR(80) NOT NULL,
  organization VARCHAR(120) NOT NULL DEFAULT '',
  introduction VARCHAR(1000) NOT NULL DEFAULT '',
  image_original_name VARCHAR(255),
  image_stored_name VARCHAR(255),
  image_mime_type VARCHAR(100),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_instructors_course ON instructors(course_id, created_at);

CREATE TABLE IF NOT EXISTS course_deliverables (
  id UUID PRIMARY KEY,
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  episode_id UUID REFERENCES episodes(id) ON DELETE SET NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  size BIGINT NOT NULL,
  mime_type VARCHAR(150) NOT NULL DEFAULT 'application/octet-stream',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
ALTER TABLE course_deliverables ADD COLUMN IF NOT EXISTS episode_id UUID REFERENCES episodes(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_deliverables_course ON course_deliverables(course_id, created_at);
CREATE INDEX IF NOT EXISTS idx_deliverables_episode ON course_deliverables(episode_id, created_at);