`db/migrations/013_consumer_accounts_and_brief.sql`

39 lines
-- 013 · Consumer accounts (Google OAuth) + missing brief fields on bookings.
--
-- Buyer-side authentication didn't exist before — anyone could submit a
-- booking with just a typed name + email. We now offer Google sign-in for
-- any buyer (homeowner / designer / architect / contractor / property mgr)
-- and persist their identity on the booking.

BEGIN;

CREATE TABLE IF NOT EXISTS consumer_accounts (
  id              SERIAL PRIMARY KEY,
  google_sub      TEXT UNIQUE,            -- Google OpenID `sub` claim (stable per account)
  email           TEXT NOT NULL,
  email_verified  BOOLEAN NOT NULL DEFAULT FALSE,
  name            TEXT,
  picture_url     TEXT,
  customer_role   TEXT,                   -- homeowner | designer | architect | contractor | property_mgr | other
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_login_at   TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_consumer_accounts_email ON consumer_accounts(LOWER(email));

ALTER TABLE bookings
  ADD COLUMN IF NOT EXISTS customer_role        TEXT,
  ADD COLUMN IF NOT EXISTS product_sourced      BOOLEAN,
  ADD COLUMN IF NOT EXISTS consumer_account_id  INTEGER REFERENCES consumer_accounts(id) ON DELETE SET NULL;

ALTER TABLE bookings
  DROP CONSTRAINT IF EXISTS bookings_customer_role_check,
  ADD CONSTRAINT bookings_customer_role_check
    CHECK (customer_role IS NULL OR customer_role IN (
      'homeowner', 'designer', 'architect', 'contractor', 'property_mgr', 'other'
    ));

CREATE INDEX IF NOT EXISTS idx_bookings_consumer_account ON bookings(consumer_account_id);

COMMIT;