-- AI POD Master Control V0.0
-- Import manually: mysql -u root -p aiworker < schema.sql

CREATE DATABASE IF NOT EXISTS aiworker
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE aiworker;

CREATE TABLE IF NOT EXISTS skills (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  skill_id VARCHAR(64) NOT NULL,
  version VARCHAR(32) NOT NULL,
  package_path VARCHAR(512) NOT NULL,
  sha256 CHAR(64) NOT NULL,
  signature TEXT DEFAULT NULL,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_skill_version (skill_id, version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tool_packs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tool_id VARCHAR(64) NOT NULL,
  version VARCHAR(32) NOT NULL,
  package_path VARCHAR(512) NOT NULL,
  sha256 CHAR(64) NOT NULL,
  signature TEXT DEFAULT NULL,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_tool_version (tool_id, version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS model_packs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  model_id VARCHAR(64) NOT NULL,
  version VARCHAR(32) NOT NULL,
  package_path VARCHAR(512) NOT NULL,
  package_format ENUM('zip','gguf') NOT NULL DEFAULT 'gguf',
  file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
  sha256 CHAR(64) NOT NULL,
  signature TEXT DEFAULT NULL,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_model_version (model_id, version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tasks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  task_id VARCHAR(32) NOT NULL,
  sku VARCHAR(64) NOT NULL,
  source_image_path VARCHAR(512) NOT NULL,
  skill_id VARCHAR(64) NOT NULL,
  skill_version VARCHAR(32) NOT NULL,
  required_tools_json JSON DEFAULT NULL,
  required_models_json JSON DEFAULT NULL,
  status ENUM('pending','claimed','running','uploaded','verified','settled','failed') NOT NULL DEFAULT 'pending',
  worker_id VARCHAR(32) DEFAULT NULL,
  attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
  reward_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  claimed_at DATETIME DEFAULT NULL,
  started_at DATETIME DEFAULT NULL,
  uploaded_at DATETIME DEFAULT NULL,
  verified_at DATETIME DEFAULT NULL,
  settled_at DATETIME DEFAULT NULL,
  result_package_path VARCHAR(512) DEFAULT NULL,
  verification_result_json JSON DEFAULT NULL,
  error_message TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_task_id (task_id),
  KEY idx_status (status),
  KEY idx_worker_id (worker_id),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS task_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  task_id VARCHAR(32) NOT NULL,
  worker_id VARCHAR(32) DEFAULT NULL,
  event_type VARCHAR(64) NOT NULL,
  message VARCHAR(512) DEFAULT NULL,
  payload_json JSON DEFAULT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_task_id (task_id),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rewards (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  reward_id VARCHAR(32) NOT NULL,
  task_id VARCHAR(32) NOT NULL,
  worker_id VARCHAR(32) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status ENUM('pending','settled','cancelled') NOT NULL DEFAULT 'settled',
  reason VARCHAR(64) NOT NULL DEFAULT 'task_verified',
  settled_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_reward_id (reward_id),
  UNIQUE KEY uk_task_id (task_id),
  KEY idx_worker_id (worker_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
