-- Unique human-readable player IDs and owner-approved, no-payment virtual chip requests.
CREATE SEQUENCE IF NOT EXISTS tp_game_id_seq START WITH 100001;
ALTER TABLE users ADD COLUMN IF NOT EXISTS game_id varchar(18);
UPDATE users SET game_id='TP'||lpad(nextval('tp_game_id_seq')::text,6,'0') WHERE game_id IS NULL;
ALTER TABLE users ALTER COLUMN game_id SET DEFAULT 'TP'||lpad(nextval('tp_game_id_seq')::text,6,'0');
ALTER TABLE users ALTER COLUMN game_id SET NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_game_id ON users (game_id);

CREATE TABLE IF NOT EXISTS chip_requests (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  requested_chips int NOT NULL CHECK (requested_chips BETWEEN 100 AND 100000),
  message varchar(200) NOT NULL DEFAULT '',
  status varchar(12) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected','cancelled')),
  reviewer_id uuid REFERENCES users(id),
  review_note varchar(200),
  created_at timestamptz NOT NULL DEFAULT now(),
  reviewed_at timestamptz,
  CHECK ((status='pending' AND reviewer_id IS NULL AND reviewed_at IS NULL) OR
         (status<>'pending'))
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_chip_requests_one_pending ON chip_requests(user_id) WHERE status='pending';
CREATE INDEX IF NOT EXISTS idx_chip_requests_recent ON chip_requests(status,created_at DESC);
-- Disable the earlier opt-in owner-player live view on existing upgraded tables.
-- This release deliberately uses fair-play tables with hidden hands.
UPDATE rooms SET state=jsonb_set(state,'{ownerPlayerView}','false'::jsonb,true)
  WHERE state->>'ownerPlayerView'='true';