-- Copyright (C) 2026 Dcntrlized
--
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU Affero General Public License as published
-- by the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU Affero General Public License for more details.
--
-- You should have received a copy of the GNU Affero General Public License
-- along with this program.  If not, see <https://www.gnu.org/licenses/>.

-- 010_device_tokens.sql — subscriber authentication without an account.
--
-- Contract rev 10. A subscriber is not a user: the subscription row IS the
-- identity, and a device token minted by THIS service at join time
-- authenticates it. No auth.users row, no synthetic email, no hub round-trip —
-- an unauthenticated path, not a fake account.
--
-- What the token proves is narrow and sufficient: "this device is subscribed to
-- this alert". It authorises reading the alert's public shape, the alarm
-- check-in, acking its own debt and removing its own subscription. It can never
-- create, trigger, disarm or delete an alert — those need the admin token, which
-- a subscriber is never given.
--
-- Additive and idempotent: accounts keep working exactly as before (a logged-in
-- user's join still records subscriber_user_id and needs no device token).
\c dcntrlized

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'alarm') THEN
        RETURN;
    END IF;

    -- A device identity has no account behind it.
    ALTER TABLE alarm.subscriptions
        ALTER COLUMN subscriber_user_id DROP NOT NULL;

    -- SHA-256 (hex) of the device token. Never the token itself: a database
    -- read must not hand out a credential. NULL for account-backed rows.
    ALTER TABLE alarm.subscriptions
        ADD COLUMN IF NOT EXISTS device_token_hash CHAR(64);

    -- The event log records who acted. An anonymous subscriber may act (joining
    -- is logged), and there is no user id to write, so the column must admit
    -- NULL. Trigger/disarm stay account actions and always carry an actor.
    ALTER TABLE alarm.alarm_events
        ALTER COLUMN actor_user_id DROP NOT NULL;

    -- One subscription per device token: the hash is the lookup key on every
    -- subscriber request, so a collision would be ambiguous by construction.
    CREATE UNIQUE INDEX IF NOT EXISTS subscriptions_device_token_idx
        ON alarm.subscriptions (device_token_hash)
        WHERE device_token_hash IS NOT NULL;

    -- One device is one subscription per alert, whichever path created it. The
    -- pre-rev-10 unique key included the user id, so existing data cannot carry
    -- duplicates in practice; the guard keeps a surprise from failing a
    -- migration on a live instance (the join path rotates rather than inserts).
    IF NOT EXISTS (
        SELECT 1
          FROM alarm.subscriptions
         GROUP BY alert_id, device_id
        HAVING count(*) > 1
    ) THEN
        CREATE UNIQUE INDEX IF NOT EXISTS subscriptions_alert_device_idx
            ON alarm.subscriptions (alert_id, device_id);
    ELSE
        RAISE NOTICE 'alarm.subscriptions has duplicate (alert_id, device_id) rows; skipping the unique index';
    END IF;
END $$;