-- C0PY 001 — registries + scans (canonical capture target registry)
-- Idempotent; owner_sub is the Zitadel introspection subject (never client-controlled).

CREATE TABLE IF NOT EXISTS registries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_sub TEXT NOT NULL,
  name TEXT NOT NULL,
  url TEXT NOT NULL,
  config JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS registries_owner_idx ON registries(owner_sub);

CREATE TABLE IF NOT EXISTS scans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  registry_id UUID NOT NULL REFERENCES registries(id) ON DELETE CASCADE,
  owner_sub TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  result JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS scans_owner_idx ON scans(owner_sub);
CREATE INDEX IF NOT EXISTS scans_registry_idx ON scans(registry_id);