-- +goose Up
-- Client account ownership.
--
-- Two recruiters should not be editing the same client. Detecting that collision after the fact and
-- refusing the second write is the wrong shape for this domain: account ownership is how staffing firms
-- actually operate, it is usually tied to commission, and a team needs to see who holds what in order to
-- work together rather than discovering an overlap at save time.
--
-- So ownership IS the lock. A client with an owner is editable by that owner. A client with no owner is
-- unclaimed and anyone with write capability may claim it. Reassignment is a deliberate, audited action
-- rather than a side effect of editing.
--
-- The column is nullable on purpose. An unassigned client is a real and useful state: a lead comes in
-- before anyone has picked it up, and a recruiter leaving must not freeze their book of business. NULL
-- means unclaimed and claimable, never broken.
--
-- ON DELETE SET NULL rather than CASCADE, because removing a user must release their accounts back to
-- the pool, never delete the client records themselves.

ALTER TABLE clients
    ADD COLUMN owner_user_id uuid,
    ADD COLUMN owner_since   timestamptz,
    ADD CONSTRAINT clients_owner_fk
        FOREIGN KEY (organization_id, owner_user_id)
        REFERENCES users (organization_id, id) ON DELETE SET NULL,
    -- An owner and the moment they took it travel together. An owner with no timestamp would make a
    -- tenure report lie, and a timestamp with no owner would imply a claim that is not held.
    ADD CONSTRAINT clients_owner_since_chk CHECK (
        (owner_user_id IS NULL AND owner_since IS NULL)
        OR (owner_user_id IS NOT NULL AND owner_since IS NOT NULL));

-- Leads with the org because every query is tenant scoped. Partial on assigned rows because the common
-- read is "what does this recruiter own", and the unassigned pool is found by the IS NULL index below.
CREATE INDEX idx_clients_owner ON clients (organization_id, owner_user_id)
    WHERE owner_user_id IS NOT NULL;
CREATE INDEX idx_clients_unclaimed ON clients (organization_id, name)
    WHERE owner_user_id IS NULL;

-- +goose Down
DROP INDEX IF EXISTS idx_clients_unclaimed;
DROP INDEX IF EXISTS idx_clients_owner;
ALTER TABLE clients
    DROP CONSTRAINT IF EXISTS clients_owner_since_chk,
    DROP CONSTRAINT IF EXISTS clients_owner_fk,
    DROP COLUMN IF EXISTS owner_since,
    DROP COLUMN IF EXISTS owner_user_id;