-- ════════════════════════════════════════════════════════════
-- سودانا v23.4 — TaskEstimator Migration
-- Run AFTER migration_v23_pricing.sql
-- Adds: task_reservations, task_estimation_log
-- ════════════════════════════════════════════════════════════

-- ── 1. task_reservations ─────────────────────────────────────
-- Holds the entire lifecycle of a pre-approved multi-task project.
-- CRITICAL: reservation_id is referenced by ai_usage and wallet_transactions
-- as a soft FK — ensures no financial write happens without a reservation.
CREATE TABLE IF NOT EXISTS task_reservations (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reservation_id       VARCHAR(40) NOT NULL UNIQUE COMMENT 'RES_{uniqid}',
  user_id              INT UNSIGNED NOT NULL,
  trace_id             VARCHAR(32) NULL COMMENT 'Kernel request trace',

  -- Estimated plan (frozen at estimation time)
  estimated_plan       JSON NOT NULL COMMENT 'Full task list with price_snapshot',
  price_snapshot       JSON NOT NULL COMMENT 'Prices locked at estimation time',

  -- Financials
  reserved_sdg         INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Pessimistic hold amount',
  actual_sdg           INT UNSIGNED NULL DEFAULT NULL COMMENT 'Real cost after execution',
  refunded_sdg         INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Returned to user',

  -- Lifecycle
  status               ENUM('pending','active','executing','completed','cancelled','expired')
                       NOT NULL DEFAULT 'pending',
  confirmation_token   VARCHAR(64) UNIQUE NULL COMMENT 'Single-use, 30min TTL',

  -- Execution tracking
  tasks_total          TINYINT UNSIGNED DEFAULT 0,
  tasks_completed      TINYINT UNSIGNED DEFAULT 0,
  tasks_failed         TINYINT UNSIGNED DEFAULT 0,
  execution_log        JSON NULL COMMENT 'Per-task result log',

  -- Timestamps
  reserved_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at           DATETIME NOT NULL COMMENT 'Auto-cancel if not activated',
  activated_at         DATETIME NULL,
  completed_at         DATETIME NULL,
  cancelled_at         DATETIME NULL,
  cancel_reason        VARCHAR(200) NULL,

  INDEX idx_user_status (user_id, status),
  INDEX idx_expires (expires_at, status),
  INDEX idx_token (confirmation_token),
  INDEX idx_trace (trace_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 2. task_estimation_log ───────────────────────────────────
-- Every estimation is logged for accuracy analysis + Adaptive Feedback Loop.
-- Connects estimated vs actual to improve future accuracy via weekly cron.
CREATE TABLE IF NOT EXISTS task_estimation_log (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id              INT UNSIGNED NOT NULL,
  reservation_id       VARCHAR(40) NULL COMMENT 'NULL if never confirmed',
  trace_id             VARCHAR(32) NULL,

  -- Input
  prompt_hash          CHAR(32) NOT NULL COMMENT 'MD5 of original prompt',
  prompt_length        SMALLINT UNSIGNED DEFAULT 0,
  user_plan            VARCHAR(20) DEFAULT 'free',

  -- Estimation metadata
  estimation_method    ENUM('rules','ai','hybrid') NOT NULL DEFAULT 'rules',
  confidence_score     DECIMAL(4,3) NOT NULL DEFAULT 0.000,
  estimation_ms        SMALLINT UNSIGNED DEFAULT 0 COMMENT 'How long estimation took',
  estimation_cost_usd  DECIMAL(10,8) DEFAULT 0 COMMENT 'Cost of AI call for estimation (paid by platform)',

  -- Plans (JSON)
  estimated_plan       JSON NOT NULL,
  actual_plan          JSON NULL COMMENT 'Filled after execution completes',

  -- Accuracy (filled post-execution by ReservationEngine::complete)
  estimated_sdg        INT UNSIGNED NOT NULL,
  actual_sdg           INT UNSIGNED NULL,
  accuracy_pct         DECIMAL(6,2) NULL COMMENT '100 = perfect, <100 = under, >100 = over',

  -- Outcome
  was_approved         TINYINT(1) DEFAULT 0,
  was_executed         TINYINT(1) DEFAULT 0,

  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at         DATETIME NULL,

  INDEX idx_user (user_id),
  INDEX idx_reservation (reservation_id),
  INDEX idx_method_conf (estimation_method, confidence_score),
  INDEX idx_accuracy (accuracy_pct),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 3. estimation_multipliers ────────────────────────────────
-- Adaptive feedback: cron updates these based on accuracy data.
-- TaskEstimator reads these to correct its estimates over time.
CREATE TABLE IF NOT EXISTS estimation_multipliers (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  task_type    VARCHAR(50) NOT NULL COMMENT 'e.g. document:feasibility, image:1024x1024',
  multiplier   DECIMAL(5,3) NOT NULL DEFAULT 1.000 COMMENT '1.0 = no correction',
  sample_count INT UNSIGNED DEFAULT 0 COMMENT 'How many samples used to compute this',
  avg_accuracy DECIMAL(6,2) NULL,
  updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_type (task_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 4. platform_settings for estimation ─────────────────────
INSERT INTO platform_settings (setting_key, value, value_type, label_ar, category, unit)
VALUES
  ('estimation_rate_limit_min',        '10',    'number', 'حد estimation في الدقيقة', 'estimation', 'requests'),
  ('estimation_daily_cap_user',        '50',    'number', 'حد estimation اليومي للمستخدم', 'estimation', 'requests'),
  ('estimation_global_budget_usd_day', '1.00',  'number', 'ميزانية estimation اليومية للمنصة', 'estimation', 'USD'),
  ('estimation_threshold_sdg',         '3000',  'number', 'حد التقدير الإجباري (SDG)', 'estimation', 'SDG'),
  ('estimation_ttl_minutes',           '30',    'number', 'مدة صلاحية الـ reservation', 'estimation', 'minutes'),
  ('max_active_reservations',          '3',     'number', 'أقصى reservations نشطة للمستخدم', 'estimation', 'count'),
  ('estimation_ai_confidence_min',     '0.70',  'number', 'حد الـ confidence لتفعيل AI analyzer', 'estimation', 'ratio'),
  ('overage_tolerance_pct_free',       '3',     'number', 'هامش overage للمجاني', 'estimation', '%'),
  ('overage_tolerance_pct_plus',       '7',     'number', 'هامش overage لـ Plus', 'estimation', '%'),
  ('overage_tolerance_pct_pro',        '10',    'number', 'هامش overage لـ Pro', 'estimation', '%'),
  ('overage_tolerance_pct_corp',       '15',    'number', 'هامش overage للشركات', 'estimation', '%'),
  ('estimation_hard_stop_overage',     '1',     'boolean','إيقاف عند تجاوز الهامش', 'estimation', 'bool'),
  ('adaptive_feedback_enabled',        '1',     'boolean','تفعيل الـ feedback loop الأسبوعي', 'estimation', 'bool')
ON DUPLICATE KEY UPDATE value=VALUES(value);

-- ── 5. reservation_id column on financial tables ──────────────
-- Soft FK: any ai_usage / wallet_transactions can be linked to a reservation.
-- NOT enforced as hard FK (to allow non-reservation AI calls to coexist).
ALTER TABLE ai_usage
  ADD COLUMN IF NOT EXISTS reservation_id VARCHAR(40) NULL,
  ADD INDEX IF NOT EXISTS idx_reservation (reservation_id);

ALTER TABLE wallet_transactions
  ADD COLUMN IF NOT EXISTS reservation_id VARCHAR(40) NULL,
  ADD INDEX IF NOT EXISTS idx_reservation (reservation_id);

-- Feature flags: AI provider availability
INSERT INTO platform_settings (setting_key, value, value_type, label_ar, category, unit)
VALUES
  ('enable_image_generation', '0', 'boolean', 'تفعيل توليد الصور (Gemini Vision)', 'features', 'bool'),
  ('enable_video_generation', '0', 'boolean', 'تفعيل توليد الفيديو (Veo)', 'features', 'bool'),
  ('enable_diagram_generation', '0', 'boolean', 'تفعيل توليد الرسومات', 'features', 'bool')
ON DUPLICATE KEY UPDATE value=VALUES(value);

INSERT INTO platform_settings (setting_key, value, value_type, label_ar, category, unit) VALUES ('enable_consultation_booking', '0', 'boolean', 'تفعيل حجز الاستشارات (Beta)', 'features', 'bool') ON DUPLICATE KEY UPDATE value=VALUES(value);

INSERT INTO platform_settings (setting_key, value, value_type, label_ar, category, unit) VALUES ('subscription_price_sdg', '10000', 'number', 'سعر الاشتراك الشهري (SDG)', 'pricing', 'SDG') ON DUPLICATE KEY UPDATE value=VALUES(value);
