-- Migration: Add diff_id column to scanner layers table
-- Sprint: SPRINT_025_Scanner_layer_manifest_infrastructure
-- Task: TASK-025-03

-- Add diff_id column to layers table (sha256:64hex = 71 chars)
ALTER TABLE scanner.layers
ADD COLUMN IF NOT EXISTS diff_id VARCHAR(71);

-- Add timestamp for when diffID was computed
ALTER TABLE scanner.layers
ADD COLUMN IF NOT EXISTS diff_id_computed_at_utc TIMESTAMP;

-- Create index on diff_id for fast lookups
CREATE INDEX IF NOT EXISTS idx_layers_diff_id
ON scanner.layers (diff_id)
WHERE diff_id IS NOT NULL;

-- Create image_layers junction table if it doesn't exist
-- This tracks which layers belong to which images
CREATE TABLE IF NOT EXISTS scanner.image_layers (
    image_reference VARCHAR(512) NOT NULL,
    layer_digest VARCHAR(71) NOT NULL,
    layer_index INT NOT NULL,
    created_at_utc TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (image_reference, layer_digest)
);

CREATE INDEX IF NOT EXISTS idx_image_layers_digest
ON scanner.image_layers (layer_digest);

-- DiffID cache table for resolved diffIDs
CREATE TABLE IF NOT EXISTS scanner.scanner_diffid_cache (
    layer_digest VARCHAR(71) PRIMARY KEY,
    diff_id VARCHAR(71) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Base image fingerprint tables for layer reuse detection
CREATE TABLE IF NOT EXISTS scanner.scanner_base_image_fingerprints (
    image_reference VARCHAR(512) PRIMARY KEY,
    layer_count INT NOT NULL,
    registered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    detection_count BIGINT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS scanner.scanner_base_image_layers (
    image_reference VARCHAR(512) NOT NULL REFERENCES scanner.scanner_base_image_fingerprints(image_reference) ON DELETE CASCADE,
    layer_index INT NOT NULL,
    diff_id VARCHAR(71) NOT NULL,
    PRIMARY KEY (image_reference, layer_index)
);

CREATE INDEX IF NOT EXISTS idx_base_image_layers_diff_id
ON scanner.scanner_base_image_layers (diff_id);

-- Manifest snapshots table for IOciManifestSnapshotService
CREATE TABLE IF NOT EXISTS scanner.manifest_snapshots (
    id UUID PRIMARY KEY,
    image_reference VARCHAR(512) NOT NULL,
    registry VARCHAR(256) NOT NULL,
    repository VARCHAR(256) NOT NULL,
    tag VARCHAR(128),
    manifest_digest VARCHAR(71) NOT NULL,
    config_digest VARCHAR(71) NOT NULL,
    media_type VARCHAR(128) NOT NULL,
    layers JSONB NOT NULL,
    diff_ids JSONB NOT NULL,
    platform JSONB,
    total_size BIGINT NOT NULL,
    captured_at TIMESTAMPTZ NOT NULL,
    snapshot_version VARCHAR(32),
    UNIQUE (manifest_digest)
);

CREATE INDEX IF NOT EXISTS idx_manifest_snapshots_image_ref
ON scanner.manifest_snapshots (image_reference);

CREATE INDEX IF NOT EXISTS idx_manifest_snapshots_repository
ON scanner.manifest_snapshots (registry, repository);

CREATE INDEX IF NOT EXISTS idx_manifest_snapshots_captured_at
ON scanner.manifest_snapshots (captured_at DESC);

-- Layer scan history for reuse detection (TASK-025-04)
CREATE TABLE IF NOT EXISTS scanner.layer_scans (
    diff_id VARCHAR(71) PRIMARY KEY,
    scanned_at TIMESTAMPTZ NOT NULL,
    finding_count INT,
    scanned_by VARCHAR(128) NOT NULL,
    scanner_version VARCHAR(64)
);

-- Layer reuse counts for statistics
CREATE TABLE IF NOT EXISTS scanner.layer_reuse_counts (
    diff_id VARCHAR(71) PRIMARY KEY,
    reuse_count INT NOT NULL DEFAULT 1,
    first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_layer_reuse_counts_count
ON scanner.layer_reuse_counts (reuse_count DESC);

COMMENT ON COLUMN scanner.layers.diff_id IS 'Uncompressed layer content hash (sha256:hex64). Immutable once computed.';
COMMENT ON TABLE scanner.scanner_diffid_cache IS 'Cache of layer digest to diffID mappings. Layer digests are immutable so cache entries never expire.';
COMMENT ON TABLE scanner.scanner_base_image_fingerprints IS 'Known base image fingerprints for layer reuse detection.';
COMMENT ON TABLE scanner.manifest_snapshots IS 'Point-in-time captures of OCI image manifests for delta scanning.';
COMMENT ON TABLE scanner.layer_scans IS 'History of layer scans for deduplication. One entry per diffID.';
COMMENT ON TABLE scanner.layer_reuse_counts IS 'Counts of how many times each layer appears across images.';

-- Layer SBOM CAS for per-layer SBOM storage (TASK-026-02)
CREATE TABLE IF NOT EXISTS scanner.layer_sbom_cas (
    diff_id VARCHAR(71) NOT NULL,
    format VARCHAR(20) NOT NULL,
    content BYTEA NOT NULL,
    size_bytes BIGINT NOT NULL,
    compressed BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_accessed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (diff_id, format)
);

CREATE INDEX IF NOT EXISTS idx_layer_sbom_cas_last_accessed
ON scanner.layer_sbom_cas (last_accessed_at);

CREATE INDEX IF NOT EXISTS idx_layer_sbom_cas_format
ON scanner.layer_sbom_cas (format);

COMMENT ON TABLE scanner.layer_sbom_cas IS 'Content-addressable storage for per-layer SBOMs. Keyed by diffID (immutable).';
COMMENT ON COLUMN scanner.layer_sbom_cas.content IS 'Compressed (gzip) SBOM content.';
COMMENT ON COLUMN scanner.layer_sbom_cas.last_accessed_at IS 'For TTL-based eviction of cold entries.';