Deploymentcenter/sql/migrations/004_unified_tokens_and_bugtracker.sql

-- Migration 004: Unified Tokens (Master & Sub-Tokens) and Bugtracker / Feature-Tracker Module (Non-destructive)

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Central Master & Sub-Token Hierarchy Table
CREATE TABLE IF NOT EXISTS dc_tokens (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  token_id VARCHAR(64) NOT NULL UNIQUE,
  parent_token_id VARCHAR(64) NULL,
  token_hash VARCHAR(128) NOT NULL,
  raw_token VARCHAR(128) NULL,
  name VARCHAR(100) NOT NULL,
  project_slug VARCHAR(64) NULL,
  license_key CHAR(29) NULL,
  owner_type ENUM('license', 'project', 'host', 'dev_agent', 'custom') NOT NULL DEFAULT 'custom',
  owner_identity VARCHAR(190) NULL,
  type ENUM('master', 'sub') NOT NULL DEFAULT 'sub',
  scopes JSON NOT NULL,
  environment ENUM('production', 'development', 'all') NOT NULL DEFAULT 'all',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at DATETIME NULL,
  expires_at DATETIME NULL,
  revoked TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (parent_token_id) REFERENCES dc_tokens(token_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Unified Bugtracker & Feature Request Items
CREATE TABLE IF NOT EXISTS bugtracker_items (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NULL,
  project_slug VARCHAR(64) NOT NULL,
  type ENUM('bug', 'feature_request') NOT NULL DEFAULT 'bug',
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  error_message TEXT NULL,
  stack_trace TEXT NULL,
  error_hash VARCHAR(64) NULL,
  build_version VARCHAR(64) NULL,
  environment ENUM('production', 'development', 'staging', 'testing') NOT NULL DEFAULT 'production',
  severity ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
  status ENUM('open', 'planned', 'in_progress', 'resolved', 'closed', 'rejected') NOT NULL DEFAULT 'open',
  occurrence_count INT NOT NULL DEFAULT 1,
  first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  resolved_in_build VARCHAR(64) NULL,
  resolution_notes TEXT NULL,
  created_by VARCHAR(100) NOT NULL DEFAULT 'agent',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY ix_bt_proj_env (project_slug, environment, type, status),
  KEY ix_bt_hash (error_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Diagnostic Comments & Timeline Table
CREATE TABLE IF NOT EXISTS bugtracker_comments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  item_id BIGINT NOT NULL,
  author VARCHAR(100) NOT NULL,
  comment TEXT NOT NULL,
  action_taken VARCHAR(64) NULL,
  meta_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (item_id) REFERENCES bugtracker_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- Seed Sample Master Token (Non-destructive)
INSERT INTO dc_tokens (
  token_id, parent_token_id, token_hash, raw_token, name, project_slug, owner_type, owner_identity, type, scopes, environment
) VALUES (
  'tok_m_myapp_dev', NULL,
  '5a38a7c29e64e526c71c4c16a695123d6874e0d9bf0d768132049e8fa65e10aa',
  'dc_master_myapp_dev_agent_001',
  'MyApp Dev Workstation Master Key',
  'myapp', 'dev_agent', 'DEV-WORKSTATION-01', 'master',
  '["bugtracker:report", "bugtracker:manage", "watchdog:ping", "updateservice:read"]',
  'all'
) ON DUPLICATE KEY UPDATE token_id = token_id;

-- Seed Sample Sub Token provisioned from Master Token (Non-destructive)
INSERT INTO dc_tokens (
  token_id, parent_token_id, token_hash, raw_token, name, project_slug, owner_type, owner_identity, type, scopes, environment
) VALUES (
  'tok_s_myapp_agent_sub1', 'tok_m_myapp_dev',
  '7c7bc0a5d4d3856b3e9447477c133a8a3a0e67617ab6f6d0a793a388b14a27bc',
  'dc_sub_myapp_agent_live_001',
  'Dev Agent Auto-Provisioned Token',
  'myapp', 'dev_agent', 'DEV-WORKSTATION-01', 'sub',
  '["bugtracker:report", "bugtracker:manage"]',
  'development'
) ON DUPLICATE KEY UPDATE token_id = token_id;

-- Seed Sample Bugs and Feature Requests (Non-destructive)
INSERT INTO bugtracker_items (
  id, project_slug, type, title, description, error_message, stack_trace, error_hash, build_version, environment, severity, status, occurrence_count, created_by
) VALUES
(1, 'myapp', 'bug', 'NullReferenceException in UserAuthService.cs line 42', 'Beim Login ohne gesetzte Session ist ein Unerwarteter Nullpointer-Fehler aufgetreten.', 'NullReferenceException: Object reference not set to an instance of an object.', 'at MyApp.Core.UserAuthService.ValidateToken(String token) in UserAuthService.cs:line 42\nat MyApp.Controllers.AuthController.Login() in AuthController.cs:line 18', 'e2c918a514d89a42f', 'v1.4.2-dev', 'development', 'high', 'open', 3, 'agent:dev-monitor-01'),
(2, 'polytrader', 'bug', 'Database Connection Timeout on Order Execution', 'Unter hoher Last bricht die Verbindung zur MariaDB nach 30s ab.', 'SQLSTATE[HY000] [2002] Connection timed out', 'PDOException: SQLSTATE[HY000] [2002] Connection timed out at Db.php:24', 'f9a2b881c1092837d', 'v2.0.1', 'production', 'critical', 'in_progress', 14, 'agent:prod-watchdog'),
(3, 'myapp', 'feature_request', 'Unterstützung für TOTP 2FA Login erzwingen', 'KI-Agent empfiehlt die Hinzufügung einer erzwungenen Zwei-Faktor-Authentifizierung für Administrator-Konten.', NULL, NULL, NULL, 'v1.5.0-roadmap', 'development', 'medium', 'open', 1, 'agent:security-agent')
ON DUPLICATE KEY UPDATE id = id;

-- Seed Sample Comments (Non-destructive)
INSERT INTO bugtracker_comments (
  id, item_id, author, comment, action_taken, created_at
) VALUES
(1, 1, 'agent:code-fixer-01', 'Log-Analyse gestartet. Der Fehler tritt auf, wenn $_SESSION["dc_user_id"] nicht gesetzt ist.', 'investigated', NOW()),
(2, 2, 'agent:db-optimizer', 'Max Connection Limit in config.php auf 100 erhöht. Connection Pool wird beobachtet.', 'added_hint', NOW())
ON DUPLICATE KEY UPDATE id = id;