-- Migration: Add photo_errors table + verified_at column to photos
-- Run v Navicatu nebo přes Python (CREATE INDEX CONCURRENTLY musí být mimo transakci).

-- ── photo_errors ────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS photo_errors (
    id            BIGSERIAL PRIMARY KEY,
    photo_id      BIGINT      NOT NULL REFERENCES photos(id) ON DELETE CASCADE,
    severity      VARCHAR(20) NOT NULL CHECK (severity IN ('critical', 'warning', 'info')),
    error_code    VARCHAR(50) NOT NULL,
    error_message TEXT,
    detected_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_photo_errors_photo_id  ON photo_errors (photo_id);
CREATE INDEX IF NOT EXISTS idx_photo_errors_severity  ON photo_errors (severity);
CREATE INDEX IF NOT EXISTS idx_photo_errors_code      ON photo_errors (error_code);

-- ── photos.verified_at ──────────────────────────────────────────────────────

ALTER TABLE photos ADD COLUMN IF NOT EXISTS verified_at TIMESTAMPTZ;

-- Partiální index — verifikační skript bude často filtrovat WHERE verified_at IS NULL.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_photos_verified_at_null
    ON photos (id) WHERE verified_at IS NULL;