-- Settings Panel Database Migration
-- Creates tables for Token Usage & Budget Monitoring and Scraping Tool Monitoring
-- Does NOT modify any existing tables

-- Token Usage Logs
CREATE TABLE IF NOT EXISTS token_usage_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  feature_type TEXT NOT NULL,        -- 'article_generation', 'ai_draft_reply', 'faq_generation'
  tokens_used INTEGER NOT NULL,
  cost REAL NOT NULL DEFAULT 0,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
  user_id TEXT REFERENCES admins(id)
);

CREATE INDEX IF NOT EXISTS idx_token_usage_timestamp ON token_usage_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_token_usage_feature ON token_usage_logs(feature_type);
CREATE INDEX IF NOT EXISTS idx_token_usage_user ON token_usage_logs(user_id);

-- Scraping Calls Logs
CREATE TABLE IF NOT EXISTS scraping_calls_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  action_type TEXT NOT NULL,         -- 'product_scrape', 'price_check', 'other'
  status TEXT NOT NULL,              -- 'success', 'failed'
  response_time_ms INTEGER,
  error_message TEXT,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
  user_id TEXT REFERENCES admins(id)
);

CREATE INDEX IF NOT EXISTS idx_scraping_timestamp ON scraping_calls_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_scraping_action ON scraping_calls_logs(action_type);
CREATE INDEX IF NOT EXISTS idx_scraping_user ON scraping_calls_logs(user_id);

-- Admin Settings (global configuration)
CREATE TABLE IF NOT EXISTS admin_settings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  setting_key TEXT UNIQUE NOT NULL,
  setting_value TEXT NOT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Token Usage Summary (pre-aggregated for performance)
CREATE TABLE IF NOT EXISTS token_usage_summary (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  period_type TEXT NOT NULL,          -- 'daily', 'weekly', 'monthly'
  period_start DATETIME NOT NULL,
  feature_type TEXT NOT NULL,
  total_tokens INTEGER NOT NULL DEFAULT 0,
  total_cost REAL NOT NULL DEFAULT 0,
  UNIQUE(period_type, period_start, feature_type)
);

CREATE INDEX IF NOT EXISTS idx_token_summary_period ON token_usage_summary(period_type, period_start);

-- Scraping Calls Summary (pre-aggregated for performance)
CREATE TABLE IF NOT EXISTS scraping_calls_summary (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  period_type TEXT NOT NULL,          -- 'daily', 'weekly', 'monthly'
  period_start DATETIME NOT NULL,
  action_type TEXT NOT NULL,
  total_calls INTEGER NOT NULL DEFAULT 0,
  success_count INTEGER NOT NULL DEFAULT 0,
  failed_count INTEGER NOT NULL DEFAULT 0,
  avg_response_time_ms REAL NOT NULL DEFAULT 0,
  UNIQUE(period_type, period_start, action_type)
);

CREATE INDEX IF NOT EXISTS idx_scraping_summary_period ON scraping_calls_summary(period_type, period_start);

-- Insert default settings
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('ai_auto_draft', 'true');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('ai_response_tone', 'Professional');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('token_budget', '1000000');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('token_warning_threshold', '80');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('token_critical_threshold', '95');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('scraping_quota', '1000');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('default_commission', '10');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('default_cookie_life', '30');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('session_timeout', '60');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('two_factor_enabled', 'false');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('notify_new_messages', 'true');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('notify_product_submissions', 'true');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('notify_revenue_thresholds', 'true');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('notify_token_budget', 'true');
INSERT OR IGNORE INTO admin_settings (setting_key, setting_value) VALUES ('notify_scraping_quota', 'true');