-- =============================================================================
-- MIGRATION: admin.user_sessions — revocable login sessions
--
-- Until now the only credential was a self-contained 1-hour JWT with no
-- server-side record. That meant:
--   * logout only deleted the client copy; the token stayed valid
--   * a password reset could not end an attacker's existing session
--   * a stolen token was usable until expiry with no way to kill it
--   * sessions died after exactly one hour of wall-clock time, because
--     nothing could renew them (the old /auth/refresh needs a STILL-VALID
--     token, so it cannot help once expired — the case that logs people out)
--
-- This table backs rotating refresh tokens, which is what makes a long-lived
-- session safe:
--   * every refresh issues a new token and revokes the presented one
--   * all tokens rotated from one login share familyId
--   * a token presented twice can only mean it was captured (the real client
--     already swapped its copy), so the whole family is revoked
--
-- Purely additive: no existing table or column is touched, and nothing reads
-- this table until a client starts sending refreshToken. Safe to apply ahead
-- of the application deploy.
--
-- Rollback:
--   DROP TABLE IF EXISTS admin.user_sessions;
-- =============================================================================

BEGIN;

CREATE TABLE IF NOT EXISTS admin.user_sessions (
  id            TEXT PRIMARY KEY,
  "userId"      TEXT NOT NULL,

  -- Groups every token rotated from a single login so reuse detection can
  -- revoke the whole chain rather than one link.
  "familyId"    TEXT NOT NULL,

  -- SHA-256 of the refresh token, never the token itself: a database dump
  -- must not hand over usable sessions. UNIQUE because lookup is by hash.
  "tokenHash"   TEXT NOT NULL,

  -- Shown in the "active sessions" UI so a merchant can recognise a device
  -- that is not theirs.
  "userAgent"   TEXT,
  "ipAddress"   TEXT,

  "expiresAt"   TIMESTAMP(3) NOT NULL,
  "lastUsedAt"  TIMESTAMP(3) NOT NULL DEFAULT NOW(),

  -- Set on rotation, explicit logout, or when reuse detection kills the
  -- family. Rows are kept rather than deleted so the audit trail survives.
  "revokedAt"     TIMESTAMP(3),
  "revokedReason" TEXT,

  "createdOn"   TIMESTAMP(3) NOT NULL DEFAULT NOW(),
  "updatedOn"   TIMESTAMP(3) NOT NULL DEFAULT NOW(),

  CONSTRAINT user_sessions_user_fk
    FOREIGN KEY ("userId") REFERENCES admin.users(id) ON DELETE CASCADE
);

-- Lookup on every refresh is by tokenHash, so this index is on the hot path
-- as well as enforcing that a hash cannot be registered twice.
CREATE UNIQUE INDEX IF NOT EXISTS user_sessions_token_hash_key
  ON admin.user_sessions ("tokenHash");

-- "every session for this user" — the password-reset revocation path and the
-- active-sessions list.
CREATE INDEX IF NOT EXISTS user_sessions_user_id_idx
  ON admin.user_sessions ("userId");

-- Reuse detection revokes by family.
CREATE INDEX IF NOT EXISTS user_sessions_family_id_idx
  ON admin.user_sessions ("familyId");

-- Supports pruning expired rows.
CREATE INDEX IF NOT EXISTS user_sessions_expires_at_idx
  ON admin.user_sessions ("expiresAt");

COMMIT;

-- Verify: table shape
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'admin' AND table_name = 'user_sessions'
ORDER BY ordinal_position;

-- Verify: indexes
SELECT indexname FROM pg_indexes
WHERE schemaname = 'admin' AND tablename = 'user_sessions'
ORDER BY indexname;

-- =============================================================================
-- MAINTENANCE — this table needs pruning, and it is not optional
--
-- Rotation writes a NEW row on every refresh and revokes the old one rather
-- than deleting it, so rows accumulate per active user rather than per login.
-- With a 15-minute access token, one user active eight hours a day produces
-- roughly 30 rows a day; at 30-day retention that is ~900 rows per active
-- user, before counting logins from multiple devices.
--
-- Nothing in the application deletes them. Run this periodically (a nightly
-- job, or @nestjs/schedule which is already a dependency).
--
-- The 7-day grace on revoked rows is deliberate: a revoked session is
-- evidence. If reuse detection fires, those rows are what show when the token
-- was stolen and from which IP, so they should outlive the incident rather
-- than vanish the moment they stop being usable.
-- =============================================================================

-- DELETE FROM admin.user_sessions
-- WHERE ("expiresAt" < NOW() - INTERVAL '7 days')
--    OR ("revokedAt" IS NOT NULL AND "revokedAt" < NOW() - INTERVAL '7 days');

-- Check growth before scheduling, to size the interval sensibly:
-- SELECT
--   COUNT(*)                                             AS total,
--   COUNT(*) FILTER (WHERE "revokedAt" IS NULL)          AS active,
--   COUNT(*) FILTER (WHERE "expiresAt" < NOW())          AS expired,
--   pg_size_pretty(pg_total_relation_size('admin.user_sessions')) AS size
-- FROM admin.user_sessions;