-- SaferPage Security Feed Storage migration
-- Version: security-feed-storage-2026-06-09
-- Scope: only Malware/Blacklist/DAST feed observations, alert links and audit log.
-- Guardrail: this DDL creates storage capability only; writes still require
-- SAFERPAGE_SECURITY_FEED_STORAGE_APPROVED=yes and productive feed credentials.

create extension if not exists pgcrypto;

create table if not exists security_feed_observations (
    id uuid primary key default gen_random_uuid(),
    domain text not null,
    source_id text not null,
    checked_at timestamptz not null default now(),
    verdict text not null default 'inconclusive'
        check (verdict in ('clean', 'hit', 'inconclusive', 'not_applicable', 'not_run')),
    severity text not null default 'niedrig'
        check (severity in ('kritisch', 'hoch', 'mittel', 'niedrig', 'info')),
    matched_url_hash text not null,
    reference_id text not null,
    dedupe_key text not null unique,
    evidence_url text,
    expires_at timestamptz,
    created_by_job_id text not null,
    review_status text not null default 'private_evidence'
        check (review_status in ('private_evidence', 'operator_review', 'published', 'suppressed', 'deleted')),
    publish_decision text not null default 'not_public'
        check (publish_decision in ('not_public', 'operator_review_required', 'publish_after_review', 'suppress')),
    result_meta jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create table if not exists security_feed_alert_links (
    id uuid primary key default gen_random_uuid(),
    dedupe_key text not null references security_feed_observations(dedupe_key) on delete cascade,
    alert_id text,
    ticket_id text,
    owner_role text not null default 'IT/Security',
    sla text not null default 'naechster Review',
    publish_decision text not null default 'not_public',
    review_status text not null default 'private_evidence',
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create table if not exists security_feed_audit_log (
    id uuid primary key default gen_random_uuid(),
    dedupe_key text,
    event_type text not null,
    actor_role text not null default 'system',
    before_hash text,
    after_hash text,
    reason text not null default '',
    meta jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now()
);

create index if not exists security_feed_observations_domain_checked_idx
    on security_feed_observations (domain, checked_at desc);
create index if not exists security_feed_observations_source_verdict_idx
    on security_feed_observations (source_id, verdict, checked_at desc);
create index if not exists security_feed_observations_expires_idx
    on security_feed_observations (expires_at);
create index if not exists security_feed_observations_result_meta_gin_idx
    on security_feed_observations using gin (result_meta);
create index if not exists security_feed_alert_links_dedupe_idx
    on security_feed_alert_links (dedupe_key);
create index if not exists security_feed_audit_log_dedupe_idx
    on security_feed_audit_log (dedupe_key, created_at desc);

create or replace function set_updated_at()
returns trigger language plpgsql as $$
begin
    new.updated_at = now();
    return new;
end;
$$;

drop trigger if exists security_feed_observations_set_updated_at on security_feed_observations;
create trigger security_feed_observations_set_updated_at
before update on security_feed_observations
for each row execute function set_updated_at();

drop trigger if exists security_feed_alert_links_set_updated_at on security_feed_alert_links;
create trigger security_feed_alert_links_set_updated_at
before update on security_feed_alert_links
for each row execute function set_updated_at();
