-- ============================================================
-- سودانا v22 — AI Engine Migration
-- Apply AFTER migration_v22_complete.sql
-- ============================================================

-- API Keys
CREATE TABLE IF NOT EXISTS ai_api_keys (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  key_hash     VARCHAR(64)  NOT NULL UNIQUE,
  key_prefix   VARCHAR(12)  NOT NULL,
  label        VARCHAR(100) DEFAULT 'Default Key',
  status       ENUM('active','revoked') DEFAULT 'active',
  plan         VARCHAR(20)  DEFAULT 'free',
  calls_today  INT UNSIGNED DEFAULT 0,
  calls_total  INT UNSIGNED DEFAULT 0,
  last_used_at DATETIME NULL,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Agents
CREATE TABLE IF NOT EXISTS ai_agents (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  name         VARCHAR(150) NOT NULL,
  description  TEXT,
  prompt       TEXT NOT NULL,
  service      VARCHAR(50)  DEFAULT 'general',
  status       ENUM('active','paused','deleted') DEFAULT 'active',
  runs_total   INT UNSIGNED DEFAULT 0,
  last_run_at  DATETIME NULL,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Agent Runs log
CREATE TABLE IF NOT EXISTS ai_agent_runs (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  agent_id     INT UNSIGNED NOT NULL,
  user_id      INT UNSIGNED NOT NULL,
  input        TEXT,
  output       TEXT,
  status       ENUM('pending','running','done','failed') DEFAULT 'pending',
  latency_ms   INT UNSIGNED DEFAULT 0,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_agent  (agent_id),
  INDEX idx_user   (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Usage tracking (billing-ready)
CREATE TABLE IF NOT EXISTS ai_usage (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  api_key_id   INT UNSIGNED NULL,
  agent_id     INT UNSIGNED NULL,
  event_type   VARCHAR(50)  NOT NULL,
  tokens_used  INT UNSIGNED DEFAULT 0,
  cost_usd     DECIMAL(10,8) DEFAULT 0,
  service      VARCHAR(50)  DEFAULT 'general',
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user    (user_id),
  INDEX idx_created (created_at),
  INDEX idx_type    (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Plan limits
CREATE TABLE IF NOT EXISTS ai_plan_limits (
  plan         VARCHAR(20) PRIMARY KEY,
  api_calls_day  INT UNSIGNED DEFAULT 50,
  agents_max     INT UNSIGNED DEFAULT 3,
  runs_day       INT UNSIGNED DEFAULT 20
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO ai_plan_limits VALUES
  ('free',  50,   2,  10),
  ('plus',  500,  10, 100),
  ('pro',   5000, 50, 1000);
