-- Make core.users.discord_id optional.
--
-- Discord is the first client, not the only possible one. A web frontend, a
-- Matrix bridge or a CLI would each create users who have no Discord account,
-- and NOT NULL would have forced a fake value or a schema change under
-- pressure.
--
-- The surrogate id was already the primary key for this reason; this finishes
-- the job by making discord_id what it always was conceptually - one LINK to an
-- identity rather than the identity itself.

ALTER TABLE core.users ALTER COLUMN discord_id DROP NOT NULL;

COMMENT ON COLUMN core.users.discord_id IS 'Discord snowflake, or NULL for a user who reached the backend some other way. The UNIQUE constraint still holds for non-NULL values: Postgres treats NULLs as distinct in a unique index, so any number of rows may have none.';

-- ---------------------------------------------------------------------------
-- Deletion by surrogate id.
--
-- core.forget_user(discord_id) can only reach users who have one. This is the
-- same cascade addressed by primary key, so /forget-me works regardless of how
-- the user arrived.
-- ---------------------------------------------------------------------------

CREATE FUNCTION core.forget_user_by_id(p_user_id bigint)
RETURNS TABLE (deleted_user boolean, deleted_qsos bigint)
LANGUAGE plpgsql
SET search_path = pg_catalog, core, logbook
AS $$
DECLARE
    v_qsos bigint := 0;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM core.users WHERE id = p_user_id) THEN
        RETURN QUERY SELECT false, 0::bigint;
        RETURN;
    END IF;

    SELECT count(*) INTO v_qsos FROM logbook.qsos WHERE user_id = p_user_id;

    DELETE FROM core.users WHERE id = p_user_id;

    RETURN QUERY SELECT true, v_qsos;
END;
$$;

COMMENT ON FUNCTION core.forget_user_by_id(bigint) IS 'Hard-deletes a user and all dependent rows by surrogate id. Caller must also purge Redis keys for this user.';

GRANT EXECUTE ON FUNCTION core.forget_user_by_id(bigint) TO hammy_app;

-- ---------------------------------------------------------------------------
-- Guard against rows nobody can reach.
--
-- With discord_id nullable and no other identifier column, a row with a NULL
-- discord_id and no callsign cannot be found by any lookup the application
-- performs: it is a leak, not a user. Require at least one way in.
--
-- Widen this CHECK as identifiers are added - or replace it with a core.identities
-- table, which is the right shape once there is genuinely a second client:
--
--   CREATE TABLE core.identities (
--       user_id     bigint NOT NULL REFERENCES core.users(id) ON DELETE CASCADE,
--       provider    text NOT NULL,
--       external_id text NOT NULL,
--       linked_at   timestamptz NOT NULL DEFAULT now(),
--       PRIMARY KEY (provider, external_id)
--   );
--
-- Not worth doing yet: every lookup becomes a join, and Discord is the only
-- client. The migration when the time comes is mechanical - create the table,
-- INSERT INTO identities SELECT id, 'discord', discord_id FROM users WHERE
-- discord_id IS NOT NULL, drop the column.
-- ---------------------------------------------------------------------------

ALTER TABLE core.users
    ADD CONSTRAINT users_reachable
    CHECK (discord_id IS NOT NULL OR callsign IS NOT NULL);