telegram-ai-support/schema.sql

-- Telegram AI Support Schema
-- D1 Database for Cloudflare Workers
-- Created: 2026-02-11

----------------------------------------------------------------------
-- Core Tables
----------------------------------------------------------------------

-- 사용자 테이블
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  telegram_id TEXT UNIQUE NOT NULL,
  username TEXT,
  first_name TEXT,
  role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('admin', 'user')),
  language_code TEXT DEFAULT 'ko' CHECK(language_code IN ('ko', 'en', 'cn', 'jp')),
  context_limit INTEGER DEFAULT 10,
  last_active_at DATETIME,
  is_blocked INTEGER DEFAULT 0,
  blocked_reason TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

----------------------------------------------------------------------
-- Financial Tables (예치금/결제)
----------------------------------------------------------------------

-- 예치금 계정
CREATE TABLE IF NOT EXISTS wallets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL UNIQUE,
  balance INTEGER NOT NULL DEFAULT 0,
  currency TEXT DEFAULT 'KRW',
  version INTEGER NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 거래 내역
CREATE TABLE IF NOT EXISTS transactions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  type TEXT NOT NULL CHECK(type IN ('deposit', 'withdrawal', 'refund', 'charge')),
  amount INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'confirmed', 'rejected', 'cancelled')),
  depositor_name TEXT,
  depositor_name_prefix TEXT,
  description TEXT,
  reference_type TEXT,
  reference_id INTEGER,
  confirmed_by INTEGER,
  confirmed_at DATETIME,
  status_notified_at DATETIME,
  reminder_sent_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (confirmed_by) REFERENCES users(id)
);

-- 은행 입금 알림 (SMS 파싱)
CREATE TABLE IF NOT EXISTS bank_notifications (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  bank_name TEXT,
  depositor_name TEXT NOT NULL,
  depositor_name_prefix TEXT,
  amount INTEGER NOT NULL,
  balance_after INTEGER,
  transaction_time DATETIME,
  raw_message TEXT,
  matched_transaction_id INTEGER,
  match_notified_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (matched_transaction_id) REFERENCES transactions(id)
);

----------------------------------------------------------------------
-- Asset Tables (자산 관리)
----------------------------------------------------------------------

-- 도메인
CREATE TABLE IF NOT EXISTS domains (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  domain TEXT UNIQUE NOT NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'expired', 'pending', 'suspended')),
  registrar TEXT,
  nameservers TEXT,
  auto_renew INTEGER DEFAULT 1,
  expiry_date DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 서버
CREATE TABLE IF NOT EXISTS servers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  provider TEXT NOT NULL DEFAULT 'anvil',
  instance_id TEXT,
  label TEXT,
  ip_address TEXT,
  region TEXT,
  spec_label TEXT,
  monthly_price INTEGER,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'provisioning', 'running', 'stopped', 'terminated', 'failed')),
  image TEXT,
  provisioned_at DATETIME,
  terminated_at DATETIME,
  expires_at DATETIME,
  last_notified_status TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- DDoS 방어 서비스
CREATE TABLE IF NOT EXISTS services_ddos (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  target TEXT NOT NULL,
  protection_level TEXT DEFAULT 'basic' CHECK(protection_level IN ('basic', 'standard', 'premium')),
  status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'inactive', 'suspended')),
  provider TEXT,
  monthly_price INTEGER,
  expiry_date DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- VPN 서비스
CREATE TABLE IF NOT EXISTS services_vpn (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  protocol TEXT DEFAULT 'wireguard' CHECK(protocol IN ('wireguard', 'openvpn', 'ipsec')),
  status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'inactive', 'suspended')),
  endpoint TEXT,
  monthly_price INTEGER,
  expiry_date DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

----------------------------------------------------------------------
-- Support Tables
----------------------------------------------------------------------

-- 피드백
CREATE TABLE IF NOT EXISTS feedback (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  session_type TEXT NOT NULL,
  rating INTEGER CHECK(rating BETWEEN 1 AND 5),
  comment TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 보류 중인 작업 (인프라 변경 승인)
CREATE TABLE IF NOT EXISTS pending_actions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  action_type TEXT NOT NULL,
  target TEXT NOT NULL,
  params TEXT NOT NULL,
  status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'rejected', 'executed', 'failed')),
  approved_by INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  executed_at DATETIME,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (approved_by) REFERENCES users(id)
);

-- 감사 로그 (immutable)
CREATE TABLE IF NOT EXISTS audit_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  actor_id INTEGER,
  action TEXT NOT NULL,
  resource_type TEXT NOT NULL,
  resource_id TEXT,
  details TEXT,
  result TEXT NOT NULL CHECK(result IN ('success', 'failure')),
  request_id TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (actor_id) REFERENCES users(id)
);

-- 지식 베이스
CREATE TABLE IF NOT EXISTS knowledge_articles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  category TEXT NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  tags TEXT,
  language TEXT DEFAULT 'ko',
  is_active INTEGER DEFAULT 1,
  embedding_indexed INTEGER DEFAULT 0,
  embedding_indexed_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

----------------------------------------------------------------------
-- Cache Tables
----------------------------------------------------------------------

-- D2 렌더링 캐시 메타데이터
CREATE TABLE IF NOT EXISTS d2_cache (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  source_hash TEXT UNIQUE NOT NULL,
  r2_key TEXT NOT NULL,
  format TEXT DEFAULT 'svg' CHECK(format IN ('svg', 'png')),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

----------------------------------------------------------------------
-- Conversation Tables
----------------------------------------------------------------------

-- 대화 이력 (최근 대화 저장)
CREATE TABLE IF NOT EXISTS conversations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  role TEXT NOT NULL CHECK(role IN ('user', 'assistant', 'system')),
  content TEXT NOT NULL,
  tool_calls TEXT,
  tool_results TEXT,
  request_id TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 대화 아카이브 요약 (90일 이후)
CREATE TABLE IF NOT EXISTS conversation_archives (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  summary TEXT NOT NULL,
  message_count INTEGER NOT NULL,
  period_start DATETIME NOT NULL,
  period_end DATETIME NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

----------------------------------------------------------------------
-- Agent Session Tables
----------------------------------------------------------------------

-- 온보딩 상담 세션
CREATE TABLE IF NOT EXISTS onboarding_sessions (
  user_id TEXT PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'greeting' CHECK(status IN ('greeting', 'gathering', 'suggesting', 'completed')),
  collected_info TEXT NOT NULL DEFAULT '{}',
  messages TEXT NOT NULL DEFAULT '[]',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL
);

-- 트러블슈팅 상담 세션
CREATE TABLE IF NOT EXISTS troubleshoot_sessions (
  user_id TEXT PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'gathering' CHECK(status IN ('gathering', 'diagnosing', 'suggesting', 'escalated', 'completed')),
  collected_info TEXT NOT NULL DEFAULT '{}',
  messages TEXT NOT NULL DEFAULT '[]',
  escalation_count INTEGER DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL
);

-- 자산 조회 세션
CREATE TABLE IF NOT EXISTS asset_sessions (
  user_id TEXT PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'idle' CHECK(status IN ('idle', 'viewing', 'managing', 'completed')),
  collected_info TEXT NOT NULL DEFAULT '{}',
  messages TEXT NOT NULL DEFAULT '[]',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL
);

-- 결제 세션
CREATE TABLE IF NOT EXISTS billing_sessions (
  user_id TEXT PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'collecting_amount' CHECK(status IN ('collecting_amount', 'collecting_name', 'confirming', 'completed')),
  collected_info TEXT NOT NULL DEFAULT '{}',
  messages TEXT NOT NULL DEFAULT '[]',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL
);

----------------------------------------------------------------------
-- Indexes
----------------------------------------------------------------------

-- Core
CREATE INDEX IF NOT EXISTS idx_users_telegram ON users(telegram_id);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_users_active ON users(last_active_at DESC);

-- Financial
CREATE INDEX IF NOT EXISTS idx_wallets_user ON wallets(user_id);
CREATE INDEX IF NOT EXISTS idx_transactions_user ON transactions(user_id);
CREATE INDEX IF NOT EXISTS idx_transactions_status ON transactions(status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_transactions_prefix_pending ON transactions(status, type, depositor_name_prefix, amount, created_at)
  WHERE status = 'pending' AND type = 'deposit';
CREATE INDEX IF NOT EXISTS idx_bank_notif_prefix ON bank_notifications(depositor_name_prefix, amount, created_at DESC)
  WHERE matched_transaction_id IS NULL;

-- Assets
CREATE INDEX IF NOT EXISTS idx_domains_user ON domains(user_id);
CREATE INDEX IF NOT EXISTS idx_domains_expiry ON domains(expiry_date);
CREATE INDEX IF NOT EXISTS idx_servers_user ON servers(user_id);
CREATE INDEX IF NOT EXISTS idx_servers_status ON servers(status);
CREATE INDEX IF NOT EXISTS idx_ddos_user ON services_ddos(user_id);
CREATE INDEX IF NOT EXISTS idx_vpn_user ON services_vpn(user_id);

-- Support
CREATE INDEX IF NOT EXISTS idx_feedback_user ON feedback(user_id);
CREATE INDEX IF NOT EXISTS idx_pending_actions_status ON pending_actions(status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_logs_actor ON audit_logs(actor_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_logs_resource ON audit_logs(resource_type, resource_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_request ON audit_logs(request_id);
CREATE INDEX IF NOT EXISTS idx_knowledge_category ON knowledge_articles(category, is_active);

-- Cache
CREATE INDEX IF NOT EXISTS idx_d2_cache_hash ON d2_cache(source_hash);

-- Conversations
CREATE INDEX IF NOT EXISTS idx_conversations_user ON conversations(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_archives_user ON conversation_archives(user_id, period_end DESC);

-- Agent Sessions
CREATE INDEX IF NOT EXISTS idx_onboarding_expires ON onboarding_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_troubleshoot_expires ON troubleshoot_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_asset_expires ON asset_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_billing_expires ON billing_sessions(expires_at);

----------------------------------------------------------------------
-- Notification Tracking Columns (Migration)
----------------------------------------------------------------------

-- servers: 상태 변경 알림 추적
-- ALTER TABLE servers ADD COLUMN last_notified_status TEXT;

-- transactions: 입금 승인/거부 알림 + 리마인더 추적
-- ALTER TABLE transactions ADD COLUMN status_notified_at DATETIME;
-- ALTER TABLE transactions ADD COLUMN reminder_sent_at DATETIME;

-- bank_notifications: 매칭 알림 추적
-- ALTER TABLE bank_notifications ADD COLUMN match_notified_at DATETIME;

-- Knowledge articles: 임베딩 인덱싱 추적
-- ALTER TABLE knowledge_articles ADD COLUMN embedding_indexed INTEGER DEFAULT 0;
-- ALTER TABLE knowledge_articles ADD COLUMN embedding_indexed_at DATETIME;