-- =============================================================================
-- Per-user IANA timezone (Phase 7.1 — TZ-01)
-- =============================================================================
-- Adds a `timezone` column to the Better Auth "user" table so day/week
-- boundary math (dashboards, ticket filters, finance, engagement) can be
-- computed against the viewer's zone instead of server UTC.
--
-- Storage zone for every existing TIMESTAMP / TIMESTAMPTZ column is unchanged.
-- Only display/range-bucketing logic in subsequent plans reads this column.
--
-- The SQL default here is the literal 'UTC'. The application-level default
-- (process.env.DEFAULT_TIMEZONE || 'UTC') is enforced by Better Auth's
-- additionalField `defaultValue` in lib/auth.ts so new sessions see the env-
-- driven value even if a row was created without it.
-- =============================================================================

ALTER TABLE "user"
  ADD COLUMN IF NOT EXISTS timezone TEXT NOT NULL DEFAULT 'UTC';

-- Backfill any rows that may have been created with NULL (defensive — the
-- DEFAULT clause above covers new inserts, but on managed Postgres a column
-- added with DEFAULT may briefly show NULL in flight on some replicas).
UPDATE "user" SET timezone = 'UTC' WHERE timezone IS NULL;

COMMENT ON COLUMN "user".timezone IS
  'IANA timezone string (e.g. America/New_York). Storage timezone for all date columns remains UTC; this column only affects display and range-bucketing.';