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 '',
  points_balance INT NOT NULL DEFAULT 10000 CHECK (points_balance >= 0),
  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';
ALTER TABLE users ADD COLUMN IF NOT EXISTS points_balance INT NOT NULL DEFAULT 10000;
ALTER TABLE users ALTER COLUMN points_balance SET DEFAULT 10000;

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 '',
  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 '',
  estimated_minutes INT NOT NULL DEFAULT 1,
  estimated_points INT NOT NULL DEFAULT 1000,
  actual_points INT,
  points_charged BOOLEAN NOT NULL DEFAULT FALSE,
  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;
ALTER TABLE courses ADD COLUMN IF NOT EXISTS estimated_minutes INT NOT NULL DEFAULT 1;
ALTER TABLE courses ADD COLUMN IF NOT EXISTS estimated_points INT NOT NULL DEFAULT 1000;
ALTER TABLE courses ALTER COLUMN estimated_points SET DEFAULT 1000;
ALTER TABLE courses ADD COLUMN IF NOT EXISTS actual_points INT;
ALTER TABLE courses ADD COLUMN IF NOT EXISTS points_charged BOOLEAN NOT NULL DEFAULT FALSE;

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 instructor_images (
  id UUID PRIMARY KEY,
  instructor_id UUID NOT NULL REFERENCES instructors(id) ON DELETE CASCADE,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_instructor_images_instructor ON instructor_images(instructor_id, sort_order, 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);

CREATE TABLE IF NOT EXISTS course_progress_events (
  id UUID PRIMARY KEY,
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  actor_type VARCHAR(20) NOT NULL DEFAULT 'SYSTEM',
  event_type VARCHAR(40) NOT NULL,
  title VARCHAR(120) NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_course_progress_events ON course_progress_events(course_id, created_at DESC);

-- 默认按每集 1000 积分计费，并同步刷新已有课程的默认值。
UPDATE courses c
SET estimated_points = GREATEST(1, (SELECT COUNT(*) FROM episodes e WHERE e.course_id = c.id)) * 1000;