CREATE TABLE IF NOT EXISTS local_fb_account (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  fb_user_id TEXT NOT NULL,
  fb_nickname TEXT,
  username TEXT,
  password_encrypted TEXT,
  two_fa_secret_encrypted TEXT,
  platform_url TEXT,
  window_name TEXT,
  window_group TEXT,
  open_urls TEXT,
  account_status INTEGER DEFAULT 1,
  country TEXT,
  language TEXT,
  friend_count INTEGER DEFAULT 0,
  public_enabled INTEGER DEFAULT 0,
  public_status TEXT,
  account_tags TEXT,
  cookie_encrypted TEXT,
  proxy_encrypted TEXT,
  proxy_type TEXT,
  user_agent TEXT,
  window_size TEXT,
  manual_window_id TEXT,
  manual_window_name TEXT,
  manual_window_open_time TEXT,
  last_check_time TEXT,
  sync_status INTEGER DEFAULT 0,
  last_sync_time TEXT,
  sync_retry_count INTEGER DEFAULT 0,
  sync_error TEXT,
  backup_status INTEGER DEFAULT 0,
  last_backup_time TEXT,
  backup_retry_count INTEGER DEFAULT 0,
  backup_error TEXT,
  backup_payload_hash TEXT,
  backup_remote_id TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (owner_key, fb_user_id)
);

CREATE TABLE IF NOT EXISTS local_version_info (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  current_version_code INTEGER NOT NULL,
  current_version_name TEXT NOT NULL,
  last_check_time TEXT,
  package_path TEXT,
  download_progress REAL DEFAULT 0,
  last_error TEXT,
  install_status TEXT,
  backup_path TEXT,
  rollback_error TEXT,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS local_task_detail_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  module_code TEXT NOT NULL,
  task_batch_id TEXT NOT NULL,
  account_id INTEGER,
  target_id TEXT,
  status TEXT NOT NULL,
  error_message TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS local_task_batch (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  batch_no TEXT NOT NULL UNIQUE,
  module_code TEXT NOT NULL,
  module_name TEXT NOT NULL,
  task_name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  total_count INTEGER NOT NULL DEFAULT 0,
  pending_count INTEGER NOT NULL DEFAULT 0,
  running_count INTEGER NOT NULL DEFAULT 0,
  success_count INTEGER NOT NULL DEFAULT 0,
  fail_count INTEGER NOT NULL DEFAULT 0,
  config_snapshot TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  start_time TEXT,
  finish_time TEXT,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS local_task_item (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  batch_id INTEGER NOT NULL,
  batch_no TEXT NOT NULL,
  item_no TEXT,
  module_code TEXT NOT NULL,
  task_name TEXT NOT NULL,
  account_id INTEGER,
  account_fb_user_id TEXT,
  account_nickname TEXT,
  target_type TEXT,
  target_id TEXT,
  target_url TEXT,
  action_type TEXT,
  target_snapshot TEXT,
  params_snapshot TEXT,
  global_snapshot TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  result_json TEXT,
  result_message TEXT,
  error_code TEXT,
  error_message TEXT,
  attempt_count INTEGER DEFAULT 0,
  worker_id TEXT,
  window_id TEXT,
  window_name TEXT,
  duration_ms INTEGER DEFAULT 0,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  start_time TEXT,
  finish_time TEXT,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(batch_id) REFERENCES local_task_batch(id)
);

CREATE INDEX IF NOT EXISTS idx_local_task_item_status ON local_task_item(status, id);
CREATE INDEX IF NOT EXISTS idx_local_task_item_batch ON local_task_item(batch_id, id);

CREATE TABLE IF NOT EXISTS local_fb_group (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  group_url TEXT NOT NULL,
  group_id TEXT,
  group_name TEXT,
  remark TEXT,
  post_success_count INTEGER NOT NULL DEFAULT 0,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (owner_key, group_url)
);

CREATE INDEX IF NOT EXISTS idx_local_fb_group_owner
  ON local_fb_group(owner_key, id);

CREATE TABLE IF NOT EXISTS local_fb_group_account_binding (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  group_id INTEGER NOT NULL,
  account_id INTEGER,
  account_fb_user_id TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'joined_valid',
  last_joined_at TEXT,
  last_repost_at TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (owner_key, group_id, account_fb_user_id)
);

CREATE INDEX IF NOT EXISTS idx_local_fb_group_binding_group
  ON local_fb_group_account_binding(owner_key, group_id, status);

CREATE INDEX IF NOT EXISTS idx_local_fb_group_binding_account
  ON local_fb_group_account_binding(owner_key, account_fb_user_id, status);

CREATE TABLE IF NOT EXISTS local_fb_group_repost_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  batch_no TEXT,
  item_no TEXT,
  group_id INTEGER,
  group_url TEXT,
  account_id INTEGER,
  post_url TEXT,
  copy_summary TEXT,
  image_paths TEXT,
  status TEXT,
  error_message TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_local_fb_group_repost_history_batch
  ON local_fb_group_repost_history(owner_key, batch_no, id);

CREATE TABLE IF NOT EXISTS group_comment_group (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  group_id TEXT NOT NULL,
  group_url TEXT NOT NULL,
  group_name TEXT,
  group_category TEXT,
  remark TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  last_collect_at TEXT,
  last_collect_status TEXT,
  last_collect_batch_no TEXT,
  last_error TEXT,
  mention_total_count INTEGER NOT NULL DEFAULT 0,
  mention_cursor_member_id INTEGER NOT NULL DEFAULT 0,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (owner_key, group_id)
);

CREATE INDEX IF NOT EXISTS idx_group_comment_group_owner
  ON group_comment_group(owner_key, status, id);

CREATE TABLE IF NOT EXISTS group_comment_member (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  group_id TEXT NOT NULL,
  facebook_id TEXT,
  name TEXT NOT NULL,
  normalized_name TEXT,
  member_status TEXT NOT NULL DEFAULT 'pending',
  reserved_batch_no TEXT,
  reserved_item_id INTEGER,
  last_reserved_at TEXT,
  mentioned_batch_no TEXT,
  mentioned_item_id INTEGER,
  mentioned_at TEXT,
  mention_fail_count INTEGER NOT NULL DEFAULT 0,
  last_mention_error TEXT,
  source_collect_batch_no TEXT,
  source_collect_item_no TEXT,
  source_collect_url TEXT,
  raw_json TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_group_comment_member_fbid_unique
  ON group_comment_member(owner_key, group_id, facebook_id)
  WHERE facebook_id IS NOT NULL AND facebook_id <> '';

CREATE UNIQUE INDEX IF NOT EXISTS idx_group_comment_member_name_unique
  ON group_comment_member(owner_key, group_id, normalized_name)
  WHERE (facebook_id IS NULL OR facebook_id = '') AND normalized_name IS NOT NULL AND normalized_name <> '';

CREATE INDEX IF NOT EXISTS idx_group_comment_member_status
  ON group_comment_member(owner_key, group_id, member_status, id);

CREATE TABLE IF NOT EXISTS local_task_attempt (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  task_item_id INTEGER NOT NULL,
  batch_id INTEGER NOT NULL,
  batch_no TEXT NOT NULL,
  attempt_no INTEGER NOT NULL DEFAULT 1,
  worker_id TEXT,
  window_id TEXT,
  window_name TEXT,
  status TEXT NOT NULL,
  result_message TEXT,
  error_code TEXT,
  error_message TEXT,
  duration_ms INTEGER DEFAULT 0,
  result_json TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  finish_time TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_local_task_attempt_item ON local_task_attempt(task_item_id, id);
CREATE INDEX IF NOT EXISTS idx_local_task_attempt_batch ON local_task_attempt(batch_id, id);

CREATE TABLE IF NOT EXISTS local_upload_queue (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  event_type TEXT NOT NULL,
  local_batch_id TEXT,
  local_item_id TEXT,
  payload_hash TEXT NOT NULL UNIQUE,
  encrypted_payload TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  retry_count INTEGER NOT NULL DEFAULT 0,
  next_retry_at TEXT,
  last_error_category TEXT,
  remote_id TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_local_upload_queue_status ON local_upload_queue(status, next_retry_at, id);

CREATE TABLE IF NOT EXISTS local_task_executor_state (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  status TEXT NOT NULL DEFAULT 'idle',
  concurrency INTEGER DEFAULT 1,
  pause_requested INTEGER NOT NULL DEFAULT 0,
  current_message TEXT,
  started_at TEXT,
  paused_at TEXT,
  lock_owner TEXT,
  lock_pid INTEGER,
  lock_acquired_at TEXT,
  lock_heartbeat_at TEXT,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS local_session (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  base_url TEXT,
  token TEXT,
  session_id TEXT,
  role TEXT DEFAULT 'MEMBER',
  member_id INTEGER DEFAULT 1,
  enterprise_id INTEGER DEFAULT 1,
  username TEXT,
  level TEXT,
  expire_time TEXT,
  offline_mode INTEGER DEFAULT 1,
  last_login_time TEXT,
  last_heartbeat_time TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS local_app_config (
  config_key TEXT PRIMARY KEY,
  config_value TEXT,
  description TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS data_collect_friend_base (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  batch_no TEXT,
  item_no TEXT,
  source_account_id TEXT NOT NULL,
  facebook_id TEXT NOT NULL,
  name TEXT,
  collected_at TEXT NOT NULL,
  raw_json TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (owner_key, source_account_id, facebook_id)
);

CREATE INDEX IF NOT EXISTS idx_data_collect_friend_base_account
  ON data_collect_friend_base(owner_key, source_account_id, id);

CREATE TABLE IF NOT EXISTS data_collect_people (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  batch_no TEXT,
  item_no TEXT,
  facebook_id TEXT NOT NULL,
  name TEXT,
  source_type TEXT NOT NULL,
  source_url TEXT NOT NULL DEFAULT '',
  source_account_id TEXT,
  source_post_url TEXT,
  source_page_url TEXT,
  source_group_url TEXT,
  collection_method TEXT,
  reaction_type TEXT,
  comment_text TEXT,
  collected_at TEXT NOT NULL,
  raw_json TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (owner_key, facebook_id, source_type, source_url)
);

CREATE INDEX IF NOT EXISTS idx_data_collect_people_source
  ON data_collect_people(owner_key, source_type, source_url, id);

CREATE TABLE IF NOT EXISTS data_collect_pages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  batch_no TEXT,
  item_no TEXT,
  page_url TEXT NOT NULL,
  page_name TEXT,
  followers_count INTEGER DEFAULT 0,
  keyword TEXT,
  collected_at TEXT NOT NULL,
  raw_json TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (owner_key, page_url)
);

CREATE INDEX IF NOT EXISTS idx_data_collect_pages_keyword
  ON data_collect_pages(owner_key, keyword, id);

CREATE TABLE IF NOT EXISTS data_collect_groups (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  batch_no TEXT,
  item_no TEXT,
  group_url TEXT NOT NULL,
  group_name TEXT,
  is_public INTEGER DEFAULT 1,
  member_count INTEGER DEFAULT 0,
  keyword TEXT,
  collected_at TEXT NOT NULL,
  raw_json TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (owner_key, group_url)
);

CREATE INDEX IF NOT EXISTS idx_data_collect_groups_keyword
  ON data_collect_groups(owner_key, keyword, id);

CREATE TABLE IF NOT EXISTS data_collect_batch_result (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  batch_no TEXT NOT NULL,
  item_no TEXT,
  result_type TEXT NOT NULL,
  result_key TEXT NOT NULL,
  result_row_id INTEGER,
  source_account_id TEXT,
  source_value TEXT,
  display_name TEXT,
  raw_json TEXT,
  collected_at TEXT NOT NULL,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (owner_key, batch_no, result_type, result_key, source_account_id, source_value)
);

CREATE INDEX IF NOT EXISTS idx_data_collect_batch_result_batch
  ON data_collect_batch_result(owner_key, batch_no, result_type, id);
CREATE INDEX IF NOT EXISTS idx_data_collect_batch_result_key
  ON data_collect_batch_result(owner_key, result_type, result_key, id);

CREATE TABLE IF NOT EXISTS data_collect_run_record (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_key TEXT NOT NULL DEFAULT 'legacy',
  batch_no TEXT NOT NULL,
  item_no TEXT,
  collect_type TEXT NOT NULL,
  task_name TEXT NOT NULL,
  source_value TEXT,
  account_id INTEGER,
  inserted_count INTEGER DEFAULT 0,
  deduped_count INTEGER DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'success',
  error_message TEXT,
  params_snapshot TEXT,
  create_time TEXT DEFAULT CURRENT_TIMESTAMP,
  update_time TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_data_collect_run_record_batch
  ON data_collect_run_record(owner_key, batch_no, id);
