PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS wm_users (
  id            TEXT PRIMARY KEY,
  username      TEXT NOT NULL UNIQUE,
  email         TEXT NOT NULL UNIQUE,
  display_name  TEXT NOT NULL,
  avatar_key    TEXT,
  bio           TEXT DEFAULT '',
  url           TEXT DEFAULT '',
  password_hash TEXT NOT NULL,
  role          TEXT NOT NULL DEFAULT 'subscriber' CHECK (role IN ('subscriber','author','admin')),
  status        TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','banned','pending')),
  created_at    INTEGER NOT NULL,
  updated_at    INTEGER NOT NULL,
  last_login_at INTEGER
);
CREATE INDEX IF NOT EXISTS idx_users_role ON wm_users(role);

CREATE TABLE IF NOT EXISTS wm_sessions (
  token_hash  TEXT PRIMARY KEY,
  user_id     TEXT NOT NULL REFERENCES wm_users(id) ON DELETE CASCADE,
  created_at  INTEGER NOT NULL,
  expires_at  INTEGER NOT NULL,
  user_agent  TEXT,
  ip          TEXT
);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON wm_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON wm_sessions(expires_at);

CREATE TABLE IF NOT EXISTS wm_posts (
  id            TEXT PRIMARY KEY,
  author_id     TEXT NOT NULL REFERENCES wm_users(id) ON DELETE CASCADE,
  type          TEXT NOT NULL DEFAULT 'moment' CHECK (type IN ('moment','article')),
  visibility    TEXT NOT NULL DEFAULT 'public' CHECK (visibility IN ('public','private')),
  status        TEXT NOT NULL DEFAULT 'published' CHECK (status IN ('published','draft','trash')),
  is_top        INTEGER NOT NULL DEFAULT 0,
  is_ad         INTEGER NOT NULL DEFAULT 0,
  title         TEXT DEFAULT '',
  content       TEXT NOT NULL DEFAULT '',
  content_html  TEXT NOT NULL DEFAULT '',
  video_key     TEXT,
  position      TEXT,
  position_lat  REAL,
  position_lng  REAL,
  share_url     TEXT,
  share_thumb   TEXT,
  like_count    INTEGER NOT NULL DEFAULT 0,
  comment_count INTEGER NOT NULL DEFAULT 0,
  created_at    INTEGER NOT NULL,
  updated_at    INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_posts_feed ON wm_posts(status, visibility, is_top DESC, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_posts_author ON wm_posts(author_id, created_at DESC);

CREATE TABLE IF NOT EXISTS wm_post_images (
  post_id   TEXT NOT NULL REFERENCES wm_posts(id) ON DELETE CASCADE,
  position  INTEGER NOT NULL CHECK (position >= 0 AND position <= 8),
  r2_key    TEXT NOT NULL,
  width     INTEGER,
  height    INTEGER,
  PRIMARY KEY (post_id, position)
);

CREATE TABLE IF NOT EXISTS wm_comments (
  id          TEXT PRIMARY KEY,
  post_id     TEXT NOT NULL REFERENCES wm_posts(id) ON DELETE CASCADE,
  parent_id   TEXT REFERENCES wm_comments(id) ON DELETE CASCADE,
  user_id     TEXT REFERENCES wm_users(id) ON DELETE SET NULL,
  guest_name  TEXT,
  guest_email TEXT,
  guest_url   TEXT,
  content     TEXT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'approved' CHECK (status IN ('approved','pending','spam','trash')),
  ip          TEXT,
  user_agent  TEXT,
  created_at  INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_comments_post ON wm_comments(post_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_comments_parent ON wm_comments(parent_id);

CREATE TABLE IF NOT EXISTS wm_likes (
  post_id    TEXT NOT NULL REFERENCES wm_posts(id) ON DELETE CASCADE,
  user_id    TEXT NOT NULL REFERENCES wm_users(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (post_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_likes_user ON wm_likes(user_id);

CREATE TABLE IF NOT EXISTS wm_links (
  id          TEXT PRIMARY KEY,
  name        TEXT NOT NULL,
  url         TEXT NOT NULL,
  avatar      TEXT,
  description TEXT DEFAULT '',
  category    TEXT DEFAULT 'default',
  visible     INTEGER NOT NULL DEFAULT 1,
  sort_order  INTEGER NOT NULL DEFAULT 0,
  created_at  INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_links_visible_sort ON wm_links(visible, category, sort_order);

CREATE TABLE IF NOT EXISTS wm_settings (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS wm_audit_log (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  actor_id   TEXT,
  action     TEXT NOT NULL,
  target     TEXT,
  detail     TEXT,
  ip         TEXT,
  created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_audit_created ON wm_audit_log(created_at DESC);

CREATE TRIGGER IF NOT EXISTS wm_likes_ai AFTER INSERT ON wm_likes
BEGIN
  UPDATE wm_posts SET like_count = like_count + 1 WHERE id = NEW.post_id;
END;

CREATE TRIGGER IF NOT EXISTS wm_likes_ad AFTER DELETE ON wm_likes
BEGIN
  UPDATE wm_posts SET like_count = CASE WHEN like_count > 0 THEN like_count - 1 ELSE 0 END WHERE id = OLD.post_id;
END;

CREATE TRIGGER IF NOT EXISTS wm_comments_ai AFTER INSERT ON wm_comments WHEN NEW.status = 'approved'
BEGIN
  UPDATE wm_posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
END;

CREATE TRIGGER IF NOT EXISTS wm_comments_ad AFTER DELETE ON wm_comments WHEN OLD.status = 'approved'
BEGIN
  UPDATE wm_posts SET comment_count = CASE WHEN comment_count > 0 THEN comment_count - 1 ELSE 0 END WHERE id = OLD.post_id;
END;

INSERT OR IGNORE INTO wm_settings(key, value) VALUES
('site_name', '"Wemoments"'),
('site_description', '""'),
('site_avatar_key', '""'),
('top_image_key', '""'),
('top_video_key', '""'),
('avatar_link', '""'),
('avatar_mirror', '""'),
('tencent_map_key', '""'),
('beian_info', '""'),
('list_collapse', 'true'),
('custom_css', '""'),
('custom_js', '""'),
('analytics_code', '""'),
('allow_register', 'true'),
('default_role', '"subscriber"'),
('comment_mode', '"registered"'),
('comment_review', 'false'),
('upload_max_image_mb', '8'),
('upload_max_video_mb', '50'),
('cdn_base_url', '""');
