Deploymentcenter/sql/migrations/006_agent_workflow.sql

-- Migration 006: Agenten-Workflow, Webhooks, Login-Drosselung, Reparaturen
--
-- Additive Migration. Der Migrator toleriert 1050/1060/1061/1062 ("existiert
-- bereits"), sodass sie auf einer teilweise migrierten Datenbank durchlaeuft.

-- ---------------------------------------------------------------------------
-- 1. Anmeldeversuche (Brute-Force-Drosselung fuer login.php)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS dc_login_attempts (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  ip           VARBINARY(16) NOT NULL,
  username     VARCHAR(64)   NULL,
  success      TINYINT(1)    NOT NULL DEFAULT 0,
  attempted_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_login_ip_time (ip, attempted_at),
  KEY ix_login_time (attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 2. Bugtracker: Agenten-Workflow
-- ---------------------------------------------------------------------------

-- Exklusive Bearbeitung (Claim/Lease): verhindert, dass zwei Agenten
-- gleichzeitig am selben Item arbeiten.
ALTER TABLE bugtracker_items ADD COLUMN claimed_by  VARCHAR(100) NULL AFTER target_agent;
ALTER TABLE bugtracker_items ADD COLUMN claimed_at  DATETIME     NULL AFTER claimed_by;
ALTER TABLE bugtracker_items ADD COLUMN lease_until DATETIME     NULL AFTER claimed_at;

-- Idempotenz: ein Agent, der wegen Timeout erneut sendet, erzeugt kein Duplikat.
ALTER TABLE bugtracker_items ADD COLUMN client_ref VARCHAR(128) NULL AFTER tags;

-- Dedup-Schluessel auch fuer Feature Requests und Ideen (nicht nur Bugs).
ALTER TABLE bugtracker_items ADD COLUMN dedup_key VARCHAR(64) NULL AFTER error_hash;

-- Strukturierter Code-Kontext: ein Agent kann direkt an die Stelle springen,
-- statt den Stacktrace parsen zu muessen.
ALTER TABLE bugtracker_items ADD COLUMN repo_url     VARCHAR(255) NULL AFTER build_version;
ALTER TABLE bugtracker_items ADD COLUMN git_branch   VARCHAR(120) NULL AFTER repo_url;
ALTER TABLE bugtracker_items ADD COLUMN commit_sha   VARCHAR(64)  NULL AFTER git_branch;
ALTER TABLE bugtracker_items ADD COLUMN file_path    VARCHAR(400) NULL AFTER commit_sha;
ALTER TABLE bugtracker_items ADD COLUMN line_no      INT          NULL AFTER file_path;
ALTER TABLE bugtracker_items ADD COLUMN context_json JSON         NULL AFTER line_no;

-- Verknuepfung zum Release, das den Fehler behoben hat.
ALTER TABLE bugtracker_items ADD COLUMN resolved_release_id INT NULL AFTER resolved_in_build;

-- Wiederauftreten nach Behebung: Verweis auf das urspruengliche Item.
ALTER TABLE bugtracker_items ADD COLUMN regression_of BIGINT NULL AFTER resolved_release_id;

ALTER TABLE bugtracker_items ADD UNIQUE KEY uq_bt_client_ref (project_slug, client_ref);
ALTER TABLE bugtracker_items ADD KEY ix_bt_dedup (dedup_key);
ALTER TABLE bugtracker_items ADD KEY ix_bt_updated (updated_at);
ALTER TABLE bugtracker_items ADD KEY ix_bt_status (status, severity);
ALTER TABLE bugtracker_items ADD KEY ix_bt_agent (target_agent, status);
ALTER TABLE bugtracker_items ADD KEY ix_bt_lease (lease_until);

-- ---------------------------------------------------------------------------
-- 3. Projekte: Repository-Bezug fuer Agenten
-- ---------------------------------------------------------------------------
ALTER TABLE dc_projects ADD COLUMN repo_url      VARCHAR(255) NULL AFTER notes;
ALTER TABLE dc_projects ADD COLUMN default_agent VARCHAR(100) NULL AFTER repo_url;
ALTER TABLE dc_projects ADD COLUMN is_active     TINYINT(1) NOT NULL DEFAULT 1 AFTER default_agent;

-- ---------------------------------------------------------------------------
-- 4. Webhooks (ausgehende Benachrichtigungen)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS dc_webhooks (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  url           VARCHAR(500) NOT NULL,
  project_slug  VARCHAR(64)  NULL,
  events        VARCHAR(500) NOT NULL DEFAULT 'bug.created,bug.critical,monitor.down',
  secret        VARCHAR(128) NULL,
  enabled       TINYINT(1)   NOT NULL DEFAULT 1,
  last_status   VARCHAR(50)  NULL,
  last_error    TEXT         NULL,
  last_fired_at DATETIME     NULL,
  failure_count INT          NOT NULL DEFAULT 0,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_webhook_project (project_slug, enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 5. Watchdog: Zustandswechsel nachvollziehbar machen
-- ---------------------------------------------------------------------------
ALTER TABLE watchdog_monitors ADD COLUMN last_state_change_utc DATETIME NULL AFTER state;
ALTER TABLE watchdog_monitors ADD COLUMN down_since_utc        DATETIME NULL AFTER last_state_change_utc;

-- 'unknown' als Anfangszustand, bevor je ein Heartbeat eintraf.
ALTER TABLE watchdog_monitors
  MODIFY COLUMN state ENUM('up','warning','down','error','stopped','maintenance','unknown')
  NOT NULL DEFAULT 'unknown';

-- Der Evaluator laeuft als eigener Cron-Job.
INSERT INTO watchdog_cron_jobs (name, interval_sec, enabled) VALUES
('evaluator', 60, 1)
ON DUPLICATE KEY UPDATE interval_sec = VALUES(interval_sec);

-- ---------------------------------------------------------------------------
-- 6. Reparatur: Token-Hashes
-- ---------------------------------------------------------------------------
-- Die Validierung vergleicht ab sofort ausschliesslich den SHA-256-Hash und
-- nicht mehr zusaetzlich den Klartext. Die geseedeten Beispiel-Tokens trugen
-- Hashes, die nicht zu ihrem raw_token passten - diese werden hier korrigiert,
-- damit bestehende Tokens weiter funktionieren.
UPDATE dc_tokens
SET token_hash = SHA2(raw_token, 256)
WHERE raw_token IS NOT NULL
  AND raw_token <> ''
  AND token_hash <> SHA2(raw_token, 256);

UPDATE watchdog_agent_tokens
SET token_hash = SHA2(raw_token, 256)
WHERE raw_token IS NOT NULL
  AND raw_token <> ''
  AND token_hash <> SHA2(raw_token, 256);

-- ---------------------------------------------------------------------------
-- 7. Konsistenz: resolved_at fuer bereits geloeste Items nachtragen
-- ---------------------------------------------------------------------------
UPDATE bugtracker_items
SET resolved_at = COALESCE(updated_at, last_seen_at, created_at)
WHERE status = 'resolved' AND resolved_at IS NULL;