tg-live-game-hono/schema.sql

-- ============================================
-- TG Live Game D1 Database Schema
-- Database: tg_live_game
-- Engine: Cloudflare D1 (SQLite)
-- ============================================

-- 摄像头管理表
CREATE TABLE IF NOT EXISTS cameras (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    type TEXT CHECK(type IN ('mac', 'ip', 'rtsp', 'screen')),
    protocol TEXT CHECK(protocol IN ('rtmps', 'srt', 'whip')),
    rtsp_url TEXT,
    location TEXT,
    status TEXT DEFAULT 'offline' CHECK(status IN ('online', 'offline', 'error')),
    live_input_id TEXT,  -- Cloudflare Stream Live Input ID
    meta TEXT,  -- JSON 扩展字段
    created_at INTEGER DEFAULT (strftime('%s', 'now')),
    updated_at INTEGER DEFAULT (strftime('%s', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_cameras_status ON cameras(status);
CREATE INDEX IF NOT EXISTS idx_cameras_type ON cameras(type);
CREATE INDEX IF NOT EXISTS idx_cameras_live_input ON cameras(live_input_id);

-- 直播会话表
CREATE TABLE IF NOT EXISTS live_sessions (
    id TEXT PRIMARY KEY,
    camera_id TEXT NOT NULL REFERENCES cameras(id) ON DELETE CASCADE,
    live_input_id TEXT NOT NULL,  -- Cloudflare Stream Live Input ID
    started_at INTEGER NOT NULL,
    ended_at INTEGER,
    duration INTEGER,  -- 秒
    status TEXT DEFAULT 'live' CHECK(status IN ('live', 'ended', 'error')),
    viewer_count INTEGER DEFAULT 0,
    peak_viewers INTEGER DEFAULT 0,
    recording_id TEXT,  -- 录像视频 ID
    meta TEXT,  -- JSON 扩展字段
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_sessions_camera ON live_sessions(camera_id);
CREATE INDEX IF NOT EXISTS idx_sessions_status ON live_sessions(status);
CREATE INDEX IF NOT EXISTS idx_sessions_started ON live_sessions(started_at);

-- 视频元数据表 (扩展 Cloudflare Stream 视频)
CREATE TABLE IF NOT EXISTS videos (
    id TEXT PRIMARY KEY,
    cf_uid TEXT UNIQUE NOT NULL,  -- Cloudflare Stream Video UID
    camera_id TEXT REFERENCES cameras(id) ON DELETE SET NULL,
    session_id TEXT REFERENCES live_sessions(id) ON DELETE SET NULL,
    title TEXT,
    description TEXT,
    tags TEXT,  -- JSON array
    thumbnail TEXT,
    duration INTEGER,  -- 秒
    size INTEGER,  -- 字节
    status TEXT DEFAULT 'processing' CHECK(status IN ('ready', 'processing', 'error')),
    is_public INTEGER DEFAULT 0,
    view_count INTEGER DEFAULT 0,
    created_at INTEGER DEFAULT (strftime('%s', 'now')),
    updated_at INTEGER DEFAULT (strftime('%s', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_videos_cf_uid ON videos(cf_uid);
CREATE INDEX IF NOT EXISTS idx_videos_camera ON videos(camera_id);
CREATE INDEX IF NOT EXISTS idx_videos_session ON videos(session_id);
CREATE INDEX IF NOT EXISTS idx_videos_status ON videos(status);

-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE,
    password_hash TEXT,
    role TEXT DEFAULT 'viewer' CHECK(role IN ('admin', 'operator', 'viewer')),
    status TEXT DEFAULT 'active' CHECK(status IN ('active', 'disabled')),
    last_login INTEGER,
    created_at INTEGER DEFAULT (strftime('%s', 'now')),
    updated_at INTEGER DEFAULT (strftime('%s', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);

-- 用户权限表
CREATE TABLE IF NOT EXISTS user_permissions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    camera_id TEXT NOT NULL REFERENCES cameras(id) ON DELETE CASCADE,
    permission TEXT NOT NULL CHECK(permission IN ('view', 'control', 'manage')),
    granted_at INTEGER DEFAULT (strftime('%s', 'now')),
    granted_by TEXT REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE(user_id, camera_id)
);

CREATE INDEX IF NOT EXISTS idx_perms_user ON user_permissions(user_id);
CREATE INDEX IF NOT EXISTS idx_perms_camera ON user_permissions(camera_id);

-- 观看统计表
CREATE TABLE IF NOT EXISTS view_stats (
    id TEXT PRIMARY KEY,
    video_id TEXT REFERENCES videos(id) ON DELETE CASCADE,
    session_id TEXT REFERENCES live_sessions(id) ON DELETE CASCADE,
    user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
    ip_address TEXT,
    user_agent TEXT,
    watch_duration INTEGER,  -- 秒
    started_at INTEGER NOT NULL,
    ended_at INTEGER,
    country TEXT,
    city TEXT,
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_stats_video ON view_stats(video_id);
CREATE INDEX IF NOT EXISTS idx_stats_session ON view_stats(session_id);
CREATE INDEX IF NOT EXISTS idx_stats_started ON view_stats(started_at);

-- 操作日志表
CREATE TABLE IF NOT EXISTS audit_logs (
    id TEXT PRIMARY KEY,
    user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
    action TEXT NOT NULL CHECK(action IN ('create', 'update', 'delete', 'login', 'logout')),
    resource TEXT NOT NULL,  -- 'camera', 'video', 'user', 'session'
    resource_id TEXT,
    details TEXT,  -- JSON
    ip_address TEXT,
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_logs(action);
CREATE INDEX IF NOT EXISTS idx_audit_resource ON audit_logs(resource);
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_logs(created_at);

-- ============================================
-- 初始化数据
-- ============================================

-- 创建默认管理员用户 (密码需要后续更新)
INSERT OR IGNORE INTO users (id, username, email, role, status)
VALUES ('admin-001', 'admin', 'admin@tg-live.local', 'admin', 'active');