\c dcntrlized;

-- 003_hash_device_id.sql — store SHA-256 digests of device ids, not the values.
--
-- Why: alarm.subscriptions.device_id is a stable pseudonymous client value
-- (dev_ + 96 random bits). It is an idempotency key, not a credential, but a
-- database dump should not be a usable list of device identifiers. The service
-- hashes every caller-supplied value before it touches the table, so new rows
-- are already stored as 'sha256:<64 lowercase hex>'.
--
-- Why this does not break idempotency (the thing someone will worry about):
-- SHA-256 equality is exactly equality of the raw values. Two raw ids still
-- collide iff their digests do, so the
-- subscriptions_alert_user_device_key UNIQUE (alert_id, subscriber_user_id,
-- device_id) constraint keeps its meaning and the re-join lookup still finds the
-- same row. The hash is one-way, so the raw value cannot be recovered from it —
-- which is the point.
--
-- The encoding matches Alarm/src/internal/deviceid.Hash exactly:
--   'sha256:' || encode(sha256(convert_to(device_id,'UTF8')),'hex')
-- and is self-describing: a value starting with 'sha256:' is a digest, never a
-- client id. Stored length is 71 bytes, inside the column's VARCHAR(128).
--
-- sha256(bytea) is a core PostgreSQL function since 11 (no pgcrypto needed); the
-- dev database is 16.14. The migration is guarded on that anyway: if sha256 is
-- somehow unavailable it fails loudly here rather than storing raw ids mixed
-- with digests.
--
-- Idempotent: the NOT LIKE 'sha256:%' predicate skips rows already hashed, so
-- re-applying the sequence is a clean no-op (same pattern as 002).

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'alarm') THEN
        RETURN;
    END IF;
    IF to_regprocedure('sha256(bytea)') IS NOT NULL THEN
        UPDATE alarm.subscriptions
           SET device_id = 'sha256:' || encode(sha256(convert_to(device_id, 'UTF8')), 'hex')
         WHERE device_id NOT LIKE 'sha256:%';
    ELSE
        RAISE EXCEPTION
            'sha256(bytea) is unavailable; device_id hashing would store mixed raw/digest rows';
    END IF;
END
$$;