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 '',
  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 ADD COLUMN IF NOT EXISTS must_change_password BOOLEAN NOT NULL DEFAULT FALSE;
UPDATE users SET must_change_password = FALSE WHERE must_change_password = TRUE;
ALTER TABLE users ALTER COLUMN points_balance SET DEFAULT 10000;

CREATE TABLE IF NOT EXISTS platform_admins (
  id UUID PRIMARY KEY,
  phone VARCHAR(20) UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  contact_name VARCHAR(80) NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS platform_admin_sessions (
  token_hash CHAR(64) PRIMARY KEY,
  admin_id UUID NOT NULL REFERENCES platform_admins(id) ON DELETE CASCADE,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_platform_admin_sessions_admin ON platform_admin_sessions(admin_id);
CREATE OR REPLACE FUNCTION reject_platform_admin_user() RETURNS trigger AS $$
BEGIN
  PERFORM pg_advisory_xact_lock(hashtextextended(NEW.phone, 0));
  IF NEW.role <> 'USER' THEN RAISE EXCEPTION 'users table only accepts USER role'; END IF;
  IF EXISTS (SELECT 1 FROM platform_admins WHERE phone = NEW.phone) THEN RAISE EXCEPTION 'phone belongs to platform administrator'; END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_reject_platform_admin_user ON users;
CREATE TRIGGER trg_reject_platform_admin_user BEFORE INSERT OR UPDATE OF phone, role ON users
FOR EACH ROW EXECUTE FUNCTION reject_platform_admin_user();
CREATE OR REPLACE FUNCTION reject_existing_user_admin() RETURNS trigger AS $$
BEGIN
  PERFORM pg_advisory_xact_lock(hashtextextended(NEW.phone, 0));
  IF EXISTS (SELECT 1 FROM users WHERE phone = NEW.phone) THEN RAISE EXCEPTION 'phone belongs to ordinary user'; END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_reject_existing_user_admin ON platform_admins;
CREATE TRIGGER trg_reject_existing_user_admin BEFORE INSERT OR UPDATE OF phone ON platform_admins
FOR EACH ROW EXECUTE FUNCTION reject_existing_user_admin();

CREATE TABLE IF NOT EXISTS organizations (
  id UUID PRIMARY KEY,
  slug VARCHAR(80) UNIQUE NOT NULL,
  name VARCHAR(120) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  points_balance INT NOT NULL DEFAULT 0 CHECK (points_balance >= 0),
  invite_token_hash CHAR(64),
  invite_token TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
ALTER TABLE organizations ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT TRUE;
ALTER TABLE organizations ADD COLUMN IF NOT EXISTS invite_token TEXT;
CREATE TABLE IF NOT EXISTS organization_members (
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role VARCHAR(20) NOT NULL DEFAULT 'MEMBER' CHECK (role IN ('MEMBER', 'MANAGER')),
  joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (organization_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_organization_members_user ON organization_members(user_id);
CREATE TABLE IF NOT EXISTS organization_applications (
  id UUID PRIMARY KEY,
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED')),
  reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  reviewed_at TIMESTAMPTZ,
  UNIQUE (organization_id, user_id)
);
ALTER TABLE organization_applications ADD COLUMN IF NOT EXISTS reviewed_by_admin UUID REFERENCES platform_admins(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_organization_applications_org ON organization_applications(organization_id, status);
CREATE TABLE IF NOT EXISTS audit_events (
  id UUID PRIMARY KEY,
  organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL,
  course_id UUID,
  actor_id UUID REFERENCES users(id) ON DELETE SET NULL,
  subject_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  action VARCHAR(100) NOT NULL,
  details JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
ALTER TABLE audit_events ADD COLUMN IF NOT EXISTS admin_actor_id UUID REFERENCES platform_admins(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_audit_events_org ON audit_events(organization_id, created_at DESC);
CREATE TABLE IF NOT EXISTS points_ledger (
  id UUID PRIMARY KEY,
  owner_type VARCHAR(20) NOT NULL CHECK (owner_type IN ('PERSONAL', 'ORGANIZATION')),
  owner_id UUID NOT NULL,
  course_id UUID,
  course_name_snapshot VARCHAR(100),
  beneficiary_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  actor_id UUID REFERENCES users(id) ON DELETE SET NULL,
  amount INT NOT NULL,
  reason VARCHAR(200) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
ALTER TABLE points_ledger ADD COLUMN IF NOT EXISTS admin_actor_id UUID REFERENCES platform_admins(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_points_ledger_owner ON points_ledger(owner_type, owner_id, created_at DESC);
ALTER TABLE points_ledger ADD COLUMN IF NOT EXISTS course_name_snapshot VARCHAR(100);
CREATE OR REPLACE FUNCTION initialize_personal_points_ledger() RETURNS trigger AS $$
BEGIN
  INSERT INTO points_ledger(id,owner_type,owner_id,beneficiary_user_id,amount,reason)
  VALUES(gen_random_uuid(),'PERSONAL',NEW.id,NEW.id,NEW.points_balance,'账户初始积分');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_personal_points_opening ON users;
CREATE TRIGGER trg_personal_points_opening AFTER INSERT ON users
FOR EACH ROW EXECUTE FUNCTION initialize_personal_points_ledger();

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 '',
  asset_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 organization_id UUID REFERENCES organizations(id) ON DELETE RESTRICT;
CREATE INDEX IF NOT EXISTS idx_courses_organization_created ON courses(organization_id, created_at DESC);
ALTER TABLE courses ADD COLUMN IF NOT EXISTS production_notes TEXT NOT NULL DEFAULT '';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS asset_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()
);

-- 平台制作阶段对单门课程的讲师快照补充的照片与声音，不影响讲师资产库。
CREATE TABLE IF NOT EXISTS course_instructor_files (
  id UUID PRIMARY KEY,
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  kind TEXT NOT NULL CHECK (kind IN ('IMAGE', 'AUDIO')),
  original_name TEXT NOT NULL,
  stored_name TEXT NOT NULL UNIQUE,
  size BIGINT NOT NULL,
  mime_type TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_course_instructor_files_course ON course_instructor_files(course_id);
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 digital_instructors (
  id UUID PRIMARY KEY,
  organization_id UUID REFERENCES organizations(id) ON DELETE RESTRICT,
  personal_user_id UUID REFERENCES users(id) ON DELETE RESTRICT,
  owner_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  creator_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  source_id UUID REFERENCES digital_instructors(id) ON DELETE SET NULL,
  legacy_instructor_id UUID UNIQUE REFERENCES instructors(id) ON DELETE SET NULL,
  name VARCHAR(80) NOT NULL,
  organization VARCHAR(120) NOT NULL DEFAULT '',
  introduction VARCHAR(1000) NOT NULL DEFAULT '',
  note VARCHAR(1000),
  quality_status VARCHAR(30) NOT NULL DEFAULT 'NOT_CHECKED',
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CHECK ((organization_id IS NULL) <> (personal_user_id IS NULL))
);
ALTER TABLE digital_instructors ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE digital_instructors ADD COLUMN IF NOT EXISTS note VARCHAR(1000);
ALTER TABLE digital_instructors ADD COLUMN IF NOT EXISTS admin_creator_id UUID REFERENCES platform_admins(id) ON DELETE SET NULL;
ALTER TABLE digital_instructors ALTER COLUMN creator_id DROP NOT NULL;
CREATE INDEX IF NOT EXISTS idx_digital_instructors_workspace ON digital_instructors(organization_id, personal_user_id, created_at DESC);
CREATE TABLE IF NOT EXISTS digital_instructor_files (
  id UUID PRIMARY KEY,
  instructor_id UUID NOT NULL REFERENCES digital_instructors(id) ON DELETE CASCADE,
  kind VARCHAR(10) NOT NULL CHECK (kind IN ('IMAGE', 'AUDIO')),
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL UNIQUE,
  size BIGINT NOT NULL DEFAULT 0,
  mime_type VARCHAR(150) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
ALTER TABLE digital_instructor_files ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT TRUE;
CREATE INDEX IF NOT EXISTS idx_digital_instructor_files_parent ON digital_instructor_files(instructor_id, kind, sort_order);
CREATE TABLE IF NOT EXISTS digital_instructor_events (
  id UUID PRIMARY KEY,
  instructor_id UUID,
  actor_id UUID REFERENCES users(id) ON DELETE SET NULL,
  action VARCHAR(50) NOT NULL,
  details JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
ALTER TABLE courses ADD COLUMN IF NOT EXISTS digital_instructor_id UUID REFERENCES digital_instructors(id) ON DELETE SET NULL;
ALTER TABLE courses ADD COLUMN IF NOT EXISTS digital_instructor_snapshot JSONB;

-- 幂等迁移：保留旧讲师及文件；缺录音的历史记录不能供新课程选择。
INSERT INTO digital_instructors(id, organization_id, personal_user_id, owner_id, creator_id, legacy_instructor_id, name, organization, introduction, created_at)
SELECT i.id, c.organization_id, CASE WHEN c.organization_id IS NULL THEN c.user_id ELSE NULL END,
       c.user_id, c.user_id, i.id, i.name, i.organization, i.introduction, i.created_at
FROM instructors i JOIN courses c ON c.id=i.course_id WHERE c.user_id IS NOT NULL
ON CONFLICT DO NOTHING;
INSERT INTO digital_instructor_files(id,instructor_id,kind,original_name,stored_name,size,mime_type,sort_order,created_at)
SELECT ii.id,ii.instructor_id,'IMAGE',ii.original_name,ii.stored_name,0,ii.mime_type,ii.sort_order,ii.created_at
FROM instructor_images ii JOIN digital_instructors di ON di.legacy_instructor_id=ii.instructor_id
ON CONFLICT DO NOTHING;
INSERT INTO digital_instructor_files(id,instructor_id,kind,original_name,stored_name,size,mime_type,sort_order,created_at)
SELECT gen_random_uuid(),i.id,'IMAGE',COALESCE(i.image_original_name,'历史照片'),i.image_stored_name,0,
       COALESCE(i.image_mime_type,'image/jpeg'),0,i.created_at
FROM instructors i JOIN digital_instructors di ON di.legacy_instructor_id=i.id
WHERE i.image_stored_name IS NOT NULL
ON CONFLICT DO NOTHING;
UPDATE courses c SET digital_instructor_id=(SELECT di.id FROM digital_instructors di JOIN instructors i ON i.id=di.legacy_instructor_id WHERE i.course_id=c.id ORDER BY i.created_at LIMIT 1)
WHERE c.digital_instructor_id IS NULL AND EXISTS(SELECT 1 FROM instructors i WHERE i.course_id=c.id);
UPDATE digital_instructors SET note=introduction WHERE note IS NULL;
ALTER TABLE digital_instructors ALTER COLUMN note SET DEFAULT '';
ALTER TABLE digital_instructors ALTER COLUMN note SET NOT NULL;

-- 保留历史重名记录；新建、改名和复制必须在组织内唯一，行锁防止并发写入重名。
CREATE OR REPLACE FUNCTION check_digital_instructor_name() RETURNS trigger AS $$
BEGIN
  IF NEW.organization_id IS NOT NULL AND NEW.deleted_at IS NULL THEN
    PERFORM 1 FROM organizations WHERE id=NEW.organization_id FOR UPDATE;
    IF EXISTS (SELECT 1 FROM digital_instructors d WHERE d.organization_id=NEW.organization_id
      AND d.deleted_at IS NULL AND d.id<>NEW.id AND lower(btrim(d.name))=lower(btrim(NEW.name))) THEN
      RAISE EXCEPTION 'digital instructor name already exists' USING ERRCODE='23505', CONSTRAINT='digital_instructors_org_name_unique';
    END IF;
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_digital_instructor_name ON digital_instructors;
CREATE TRIGGER trg_digital_instructor_name BEFORE INSERT OR UPDATE OF name, organization_id, deleted_at
ON digital_instructors FOR EACH ROW EXECUTE FUNCTION check_digital_instructor_name();

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;

-- 旧账户的既有余额与已完成课程，折算为可追溯的个人流水。
INSERT INTO points_ledger(id,owner_type,owner_id,beneficiary_user_id,amount,reason)
SELECT gen_random_uuid(),'PERSONAL',u.id,u.id,
  u.points_balance + COALESCE(SUM(COALESCE(c.actual_points,c.estimated_points)) FILTER (WHERE c.points_charged AND c.organization_id IS NULL),0)::int,
  '历史账户期初余额'
FROM users u LEFT JOIN courses c ON c.user_id=u.id
WHERE NOT EXISTS (SELECT 1 FROM points_ledger l WHERE l.owner_type='PERSONAL' AND l.owner_id=u.id)
GROUP BY u.id;
INSERT INTO points_ledger(id,owner_type,owner_id,course_id,course_name_snapshot,beneficiary_user_id,amount,reason,created_at)
SELECT gen_random_uuid(),'PERSONAL',c.user_id,c.id,c.name,c.user_id,
  -COALESCE(c.actual_points,c.estimated_points),'历史课程完成扣费',COALESCE(c.completed_at,c.updated_at)
FROM courses c WHERE c.points_charged AND c.organization_id IS NULL
  AND NOT EXISTS (SELECT 1 FROM points_ledger l WHERE l.owner_type='PERSONAL' AND l.course_id=c.id);
UPDATE points_ledger l SET course_name_snapshot=c.name FROM courses c
WHERE l.course_id=c.id AND l.course_name_snapshot IS NULL;

ALTER TABLE digital_instructor_events ADD COLUMN IF NOT EXISTS admin_actor_id UUID REFERENCES platform_admins(id) ON DELETE SET NULL;

-- 广告落地页（/lp）收集的课程咨询线索。无需登录即可写入，由管理员在后台跟进。
CREATE TABLE IF NOT EXISTS lead_requests (
  id UUID PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  wechat VARCHAR(80) NOT NULL DEFAULT '',
  need_type VARCHAR(40) NOT NULL DEFAULT 'OTHER',
  course_scale VARCHAR(40) NOT NULL DEFAULT '',
  message TEXT NOT NULL DEFAULT '',
  source VARCHAR(120) NOT NULL DEFAULT '',
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  note TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
ALTER TABLE lead_requests ADD COLUMN IF NOT EXISTS status VARCHAR(20) NOT NULL DEFAULT 'PENDING';
ALTER TABLE lead_requests ADD COLUMN IF NOT EXISTS note TEXT NOT NULL DEFAULT '';
ALTER TABLE lead_requests ADD COLUMN IF NOT EXISTS source VARCHAR(120) NOT NULL DEFAULT '';
CREATE INDEX IF NOT EXISTS idx_lead_requests_created ON lead_requests(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_lead_requests_phone ON lead_requests(phone);