-- Signup chat sessions, portal user phone, customer-facing company account codes

ALTER TABLE companies
  ADD COLUMN IF NOT EXISTS account_code VARCHAR(16);

-- Backfill unique codes from company id (deterministic, collision-free per row)
UPDATE companies
SET account_code = 'C' || upper(left(replace(id::text, '-', ''), 10))
WHERE account_code IS NULL;

ALTER TABLE companies
  ALTER COLUMN account_code SET NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS companies_account_code_key ON companies (account_code);

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS phone VARCHAR(64);

CREATE TABLE IF NOT EXISTS signup_sessions (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id              UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  step                    VARCHAR(32) NOT NULL,
  state                   JSONB NOT NULL DEFAULT '{}',
  verification_token_hash TEXT,
  expires_at              TIMESTAMPTZ NOT NULL,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  consumed_at             TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_signup_sessions_expires ON signup_sessions (expires_at);
CREATE INDEX IF NOT EXISTS idx_signup_sessions_verification_hash
  ON signup_sessions (verification_token_hash)
  WHERE verification_token_hash IS NOT NULL AND consumed_at IS NULL;
