-- ============================================================
-- سودانا v22 — Complete Migration
-- Apply this file AFTER existing sudanna_v21_MASTER.sql
-- ============================================================

-- tasks
CREATE TABLE IF NOT EXISTS tasks (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type        VARCHAR(50)  NOT NULL,
  payload     JSON         NOT NULL,
  status      ENUM('pending','processing','done','failed') DEFAULT 'pending',
  attempts    INT UNSIGNED DEFAULT 0,
  run_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  event_id    VARCHAR(64)  NULL,
  last_error  TEXT         NULL,
  failed_at   DATETIME     NULL,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE  KEY uq_event_id  (event_id),
  INDEX       idx_status   (status, run_at),
  INDEX       idx_retry    (status, attempts, run_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ai_decisions
CREATE TABLE IF NOT EXISTS ai_decisions (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event      VARCHAR(100) NOT NULL,
  decision   JSON         NOT NULL,
  score      FLOAT        DEFAULT 0,
  created_at DATETIME     DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_event   (event),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ai_metrics
CREATE TABLE IF NOT EXISTS ai_metrics (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  metric     VARCHAR(100) NOT NULL,
  value      FLOAT        NOT NULL DEFAULT 0,
  context    JSON         NULL,
  created_at DATETIME     DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_metric  (metric),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Stuck task recovery (requires MySQL Event Scheduler ON)
CREATE EVENT IF NOT EXISTS sudanna_reset_stuck_tasks
  ON SCHEDULE EVERY 10 MINUTE
  DO UPDATE tasks
     SET status='pending', updated_at=NOW()
     WHERE status='processing'
       AND updated_at < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
       AND attempts < 3;

-- Enable event scheduler (run once as root):
-- SET GLOBAL event_scheduler = ON;
