schema.sql

DROP TABLE IF EXISTS versions;
DROP TABLE IF EXISTS chains;
DROP TABLE IF EXISTS artists;
DROP TABLE IF EXISTS inspirations;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS sessions;

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  role TEXT DEFAULT 'user',
  created_at INTEGER,
  last_login INTEGER,
  storage_usage INTEGER DEFAULT 0,
  max_storage INTEGER DEFAULT 314572800
);

CREATE TABLE sessions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  expires_at INTEGER NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE chains (
  id TEXT PRIMARY KEY,
  user_id TEXT, 
  username TEXT, 
  name TEXT NOT NULL,
  description TEXT,
  tags TEXT,
  preview_image TEXT,
  base_prompt TEXT DEFAULT '',
  negative_prompt TEXT DEFAULT '',
  modules TEXT DEFAULT '[]',
  params TEXT DEFAULT '{}',
  variable_values TEXT DEFAULT '{}', -- 新增字段
  guest_hidden INTEGER NOT NULL DEFAULT 0, -- 游客不可见标记
  is_private INTEGER NOT NULL DEFAULT 0, -- 私人串：VIP仅本人可见，admin可见全部
  created_at INTEGER,
  updated_at INTEGER
);

CREATE TABLE artists (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  image_url TEXT,
  preview_url TEXT,
  benchmarks TEXT
);

CREATE TABLE shared_vibes (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  username TEXT,
  local_id TEXT NOT NULL,
  name TEXT NOT NULL,
  thumbnail_url TEXT,
  payload_url TEXT NOT NULL,
  size INTEGER DEFAULT 0,
  created_at INTEGER,
  updated_at INTEGER
);

CREATE TABLE inspirations (
  id TEXT PRIMARY KEY,
  user_id TEXT,
  username TEXT,
  title TEXT NOT NULL,
  image_url TEXT,
  prompt TEXT,
  params TEXT,
  created_at INTEGER
);

-- 访问日志表（用于统计）
CREATE TABLE access_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT,
  username TEXT,
  role TEXT,
  ip TEXT,
  user_agent TEXT,
  action TEXT,
  created_at INTEGER
);

CREATE INDEX idx_access_logs_created_at ON access_logs(created_at);
CREATE INDEX idx_access_logs_role ON access_logs(role);

-- 每日统计表（按日聚合）
CREATE TABLE daily_stats (
  date TEXT PRIMARY KEY,
  total_requests INTEGER DEFAULT 0,
  api_requests INTEGER DEFAULT 0,
  guest_logins INTEGER DEFAULT 0,
  user_logins INTEGER DEFAULT 0,
  generate_requests INTEGER DEFAULT 0
);