-- Per-school Hubtel SMS configuration — same architecture as
-- school_paystack_config: each school connects its OWN Hubtel account,
-- never a shared credential. The clientSecret is encrypted at rest
-- (same crypto.js utility Paystack already uses).
--
-- Unlike Paystack, Hubtel has no documented free "check credentials"
-- endpoint (confirmed by actual research, not assumed) — so
-- verification here means sending one real test SMS during setup,
-- not a side-effect-free API call. That's reflected in the routes,
-- not just this schema.
CREATE TABLE IF NOT EXISTS school_hubtel_config (
  id                SERIAL PRIMARY KEY,
  school_id         INTEGER NOT NULL UNIQUE REFERENCES schools(id) ON DELETE CASCADE,
  client_id         TEXT NOT NULL,
  client_secret_enc TEXT NOT NULL,
  sender_id         TEXT NOT NULL DEFAULT 'SchoollyApp',
  status            TEXT NOT NULL DEFAULT 'not_connected' CHECK (status IN ('connected', 'not_connected')),
  last_verified_at  TIMESTAMPTZ,
  configured_by     TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE school_hubtel_config FORCE ROW LEVEL SECURITY;
ALTER TABLE school_hubtel_config ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON school_hubtel_config;
CREATE POLICY tenant_isolation ON school_hubtel_config
  USING (school_id = NULLIF(current_setting('app.current_school_id', true), '')::int);