-- A customer number a person can say out loud.
--
-- Until now `RecordVisit` named every new customer with eight hex characters
-- off their uuid:
--
--     UPDATE visitors SET label = 'Visitor ' || left(id::text, 8)
--
-- So the name a shop assistant reads on the arrivals feed, and reads back to a
-- colleague, and types into a search box, was "Visitor 3446ec35". That is not
-- a display problem to paper over in the front end - `label` is a stored
-- column that staff can overwrite, it is what `SearchVisitors` matches on, and
-- it is what the desktop app, the web console and the mobile feed all show.
-- It had to be fixed where it is written.
--
-- The replacement is a per-CLIENT sequential number: "Visitor 42", referenced
-- as `V-42`. Three properties, each of which rules out an alternative:
--
--   * Speakable and typeable. This is the whole point. A number is read down a
--     phone, written on a card and searched for; a uuid is copy-pasted or got
--     wrong.
--   * Per client, not global. A global sequence tells any customer who signs
--     up how many people the entire platform has ever seen, from their own
--     first visitor number - the German-tank estimate, and a number no
--     customer should be able to compute. Per tenant it only reveals a
--     tenant's own count to that tenant's own staff, who know it already.
--   * Not the primary key. `visitors.id` stays a uuid. The ids in this schema
--     are generated in places that cannot ask a database for the next value,
--     and swapping a PK that eleven tables reference for a sequence buys
--     nothing internal while risking everything. This is a public REFERENCE
--     sitting beside the key, which is the part humans needed all along.
--
-- The counter lives on `clients`, and `UPDATE ... RETURNING` returns the value
-- AFTER the update - which is exactly what is wanted here, and is the same
-- semantics that silently broke the face prune in 011 by returning the value
-- it had just written. Taking the number this way row-locks the client for the
-- length of the insert, serialising new-visitor creation per tenant. That is
-- free: a new visitor row is written only for a face nobody in the estate has
-- ever seen, not once per visit. Computing MAX(number)+1 instead would race
-- two shops onto one number.

BEGIN;

ALTER TABLE clients  ADD COLUMN IF NOT EXISTS visitor_seq bigint NOT NULL DEFAULT 0;
ALTER TABLE visitors ADD COLUMN IF NOT EXISTS number      bigint;

-- Existing rows are numbered in the order they were first seen, so a customer
-- who has been coming for a year has a lower number than one who arrived
-- yesterday. Ties break on id only so the result is deterministic.
WITH numbered AS (
    SELECT id,
           row_number() OVER (PARTITION BY client_id
                              ORDER BY first_seen_at, id) AS n
      FROM visitors
)
UPDATE visitors v
   SET number = numbered.n
  FROM numbered
 WHERE v.id = numbered.id
   AND v.number IS NULL;

UPDATE clients c
   SET visitor_seq = COALESCE((SELECT max(number) FROM visitors
                                WHERE client_id = c.id), 0);

-- Rename ONLY the labels this system generated. The pattern is exactly the
-- eight lowercase hex characters the old statement produced, so a name a human
-- typed - including one that legitimately starts with the word Visitor - is
-- left alone. Overwriting a staff-assigned name would be silent data loss of
-- the kind nobody would notice until a customer was greeted wrongly.
UPDATE visitors
   SET label = 'Visitor ' || number
 WHERE number IS NOT NULL
   AND label ~ '^Visitor [0-9a-f]{8}$';

ALTER TABLE visitors ALTER COLUMN number SET NOT NULL;

-- Unique per tenant, and the index `V-42` is resolved through.
CREATE UNIQUE INDEX IF NOT EXISTS visitors_number_idx
    ON visitors (client_id, number);

COMMIT;