-- Migration 058: Repo commit tracking and OpenClaw instance registry

CREATE TABLE IF NOT EXISTS repo_commits (
  id              SERIAL PRIMARY KEY,
  repo_name       TEXT NOT NULL,
  repo_full_name  TEXT NOT NULL,
  branch          TEXT NOT NULL,
  commit_sha      TEXT NOT NULL,
  commit_message  TEXT,
  author_name     TEXT,
  author_email    TEXT,
  committed_at    TIMESTAMPTZ,
  pushed_by       TEXT,
  raw_payload     JSONB,
  received_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  notified_at     TIMESTAMPTZ,
  UNIQUE (repo_full_name, commit_sha)
);

CREATE INDEX IF NOT EXISTS idx_repo_commits_repo      ON repo_commits (repo_name);
CREATE INDEX IF NOT EXISTS idx_repo_commits_received  ON repo_commits (received_at DESC);
CREATE INDEX IF NOT EXISTS idx_repo_commits_branch    ON repo_commits (branch);

CREATE TABLE IF NOT EXISTS openclaw_instances (
  id               SERIAL PRIMARY KEY,
  name             TEXT NOT NULL UNIQUE,
  webhook_url      TEXT NOT NULL,
  webhook_secret   TEXT NOT NULL,
  enabled          BOOLEAN NOT NULL DEFAULT TRUE,
  last_notified_at TIMESTAMPTZ,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Seed overwatch instance
INSERT INTO openclaw_instances (name, webhook_url, webhook_secret, enabled)
VALUES (
  'overwatch',
  'http://100.89.248.105:8420/webhook/git-push',
  'e984b79196c91104a7ab8fa7d7f554e7c5b3ba5a4ebddb92f2ae2fbee675c14b',
  TRUE
)
ON CONFLICT (name) DO NOTHING;