-- tenant-portals (wave 9, task 1): portal_sessions + customer_portal_users.locale + tenant_settings.portal_max_session_hours

CREATE TABLE portal_sessions (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash  TEXT NOT NULL,
  expires_at  TIMESTAMPTZ NOT NULL,
  revoked_at  TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX idx_portal_sessions_token ON portal_sessions(token_hash);
CREATE INDEX idx_portal_sessions_customer ON portal_sessions(tenant_id, customer_id) WHERE revoked_at IS NULL;

ALTER TABLE customer_portal_users
  ADD COLUMN IF NOT EXISTS locale TEXT DEFAULT NULL;
ALTER TABLE customer_portal_users
  ADD CONSTRAINT customer_portal_users_locale_check
  CHECK (locale IS NULL OR locale IN ('he', 'en'));

ALTER TABLE tenant_settings
  ADD COLUMN IF NOT EXISTS portal_max_session_hours INTEGER NOT NULL DEFAULT 24;
ALTER TABLE tenant_settings
  ADD CONSTRAINT tenant_settings_portal_max_session_hours_check
  CHECK (portal_max_session_hours BETWEEN 4 AND 72);
