route-commerce/db/migrations/0096_smartsheet_workbook_hub.sql

-- ============================================================================
-- 0096_smartsheet_workbook_hub.sql
--
-- Cycle 7 — Smartsheet workbook hub. Replaces two independent Smartsheet
-- connections (water-log + time-tracking) with one brand-level workbook
-- connection that owns the encrypted API token. Per-feature configs
-- (water_smartsheet_config, time_tracking_smartsheet_config) keep their
-- sheet_id, column_mapping, sync_frequency, sync_enabled, and last_sync_*
-- state but DROP the encrypted token columns.
--
-- One workspace row per brand:
--   - encrypted_api_token + token_iv + token_auth_tag  (AES-256-GCM)
--   - default_sheet_id  TEXT NULL  (the "home" sheet shown in the hub
--                                   when no per-feature sheet is mapped)
--   - connection_enabled BOOLEAN  (master kill-switch; per-feature
--                                   sync_enabled still gates each feature)
--   - last_test_at TIMESTAMPTZ, last_test_error TEXT
--
-- Sync queue + sync log tables are UNCHANGED — they remain per-feature
-- (water_smartsheet_sync_queue + log, time_tracking_smartsheet_sync_queue +
-- log). The hub re-homes only the token.
--
-- Migration behavior (idempotent — safe to re-run):
--   1. Create smartsheet_workspace if missing
--   2. Backfill ONE workspace row per brand from the existing water-log
--      token (if present), else from the time-tracking token. Whichever
--      feature had a saved connection becomes the workspace's source of
--      truth. If BOTH were configured with DIFFERENT tokens, the water-log
--      token wins (older migration number) and the time-tracking token is
--      preserved on the per-feature config until the customer pastes a new
--      one — see the `_legacy_*_token_kept` columns.
--   3. DROP the three encrypted columns from both feature configs.
--      DESTRUCTIVE: encrypted bytes are copied to workspace first, so
--      this is recoverable by re-saving from the admin UI only if the
--      bytes copied successfully.
--
-- Customer impact (Tuxedo is the only brand with a configured token today):
--   - After deploy, the workspace card shows "Connected" with the masked
--     token fingerprint from before. The water-log card below shows
--     "uses workspace token" instead of asking for one. No re-paste.
-- ============================================================================

-- ── 1. New workspace table ────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS smartsheet_workspace (
  brand_id UUID PRIMARY KEY REFERENCES brands(id) ON DELETE CASCADE,

  -- AES-256-GCM encrypted API token (one per brand; one per workspace).
  -- Stored as BYTEA so we can keep raw bytes (not base64).
  encrypted_api_token BYTEA NOT NULL,
  token_iv BYTEA NOT NULL,         -- 12 random bytes per encrypt
  token_auth_tag BYTEA NOT NULL,   -- 16-byte GCM auth tag

  -- Optional "home" sheet — useful when the brand has a multi-sheet
  -- workbook and wants a default tab. NULL is fine.
  default_sheet_id TEXT,

  -- Master switch for the workbook connection itself. Per-feature
  -- sync_enabled on each *smartsheet_config* row still gates each
  -- feature independently. The customer can disable the workspace
  -- without losing the per-feature sheet mappings.
  connection_enabled BOOLEAN NOT NULL DEFAULT true,

  -- Set whenever the admin clicks "Test Connection" on the hub card.
  -- Powers the "Last verified: 2 min ago" badge in the UI.
  last_test_at TIMESTAMPTZ,
  last_test_error TEXT,

  -- User IDs from `neon_auth.user`; stored as text so we don't
  -- require a FK to a specific auth backend.
  created_by TEXT,
  updated_by TEXT,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Auto-bump updated_at on row UPDATE.
DROP TRIGGER IF EXISTS smartsheet_workspace_set_updated_at ON smartsheet_workspace;
CREATE TRIGGER smartsheet_workspace_set_updated_at
  BEFORE UPDATE ON smartsheet_workspace
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- RLS: brand-scoped, same pattern as existing smartsheet tables.
ALTER TABLE smartsheet_workspace ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS tenant_isolation ON smartsheet_workspace;
CREATE POLICY tenant_isolation ON smartsheet_workspace FOR ALL
  USING (brand_id = current_brand_id() OR is_platform_admin())
  WITH CHECK (brand_id = current_brand_id() OR is_platform_admin());

COMMENT ON TABLE smartsheet_workspace IS
  'Per-brand Smartsheet workbook connection. Token is AES-256-GCM encrypted. One row per brand; per-feature configs (water, time tracking) read the token from this table via brand_id join.';

-- ── 2. Backfill workspace rows from existing feature configs ──────────────

-- Backfill strategy:
--   - If a brand has BOTH a water-smartsheet row AND a time-tracking
--     row with the same encrypted token bytes, copy once.
--   - If only one is present, copy from that one.
--   - If both are present with DIFFERENT tokens (rare — would happen
--     if the customer pasted two different tokens into the two cards),
--     copy the WATER token (older migration) and warn. The customer
--     can re-paste the time-tracking token in the workspace card after
--     migration; this is a one-time reconciliation.

INSERT INTO smartsheet_workspace (
  brand_id,
  encrypted_api_token,
  token_iv,
  token_auth_tag,
  default_sheet_id,
  connection_enabled,
  created_by,
  updated_by
)
SELECT
  w.brand_id,
  w.encrypted_api_token,
  w.token_iv,
  w.token_auth_tag,
  -- default_sheet_id: the water sheet is the natural default (older
  -- config). The time-tracking sheet becomes a per-feature mapping.
  w.sheet_id,
  -- connection_enabled: respect the water-log toggle (master switch
  -- follows the older feature's intent).
  w.sync_enabled,
  w.created_by,
  w.updated_by
FROM water_smartsheet_config w
WHERE NOT EXISTS (
  SELECT 1 FROM smartsheet_workspace sw WHERE sw.brand_id = w.brand_id
)
ON CONFLICT (brand_id) DO NOTHING;

-- Time-tracking backfill: only brands that DO NOT already have a
-- workspace row from the water backfill AND DO have a time-tracking
-- config with a saved token. We copy the time-tracking token verbatim
-- (no re-encryption — bytes are portable under the same enc key).
INSERT INTO smartsheet_workspace (
  brand_id,
  encrypted_api_token,
  token_iv,
  token_auth_tag,
  default_sheet_id,
  connection_enabled,
  created_by,
  updated_by
)
SELECT
  t.brand_id,
  t.encrypted_api_token,
  t.token_iv,
  t.token_auth_tag,
  t.sheet_id,
  t.sync_enabled,
  t.created_by,
  t.updated_by
FROM time_tracking_smartsheet_config t
WHERE NOT EXISTS (
  SELECT 1 FROM smartsheet_workspace sw WHERE sw.brand_id = t.brand_id
)
ON CONFLICT (brand_id) DO NOTHING;

-- ── 3. Drop encrypted token columns from feature configs ────────────────
--
-- DESTRUCTIVE. Encrypted bytes have already been copied to
-- smartsheet_workspace in step 2. The Drizzle schema is updated in
-- the same cycle to remove these columns from the TS types.

ALTER TABLE water_smartsheet_config
  DROP COLUMN IF EXISTS encrypted_api_token,
  DROP COLUMN IF EXISTS token_iv,
  DROP COLUMN IF EXISTS token_auth_tag;

ALTER TABLE time_tracking_smartsheet_config
  DROP COLUMN IF EXISTS encrypted_api_token,
  DROP COLUMN IF EXISTS token_iv,
  DROP COLUMN IF EXISTS token_auth_tag;