-- SMS templates per business (verified by super-admin → Meshkee service shortcode).

CREATE TABLE IF NOT EXISTS sms_templates (
  id            BIGSERIAL PRIMARY KEY,
  business_id   BIGINT NOT NULL REFERENCES businesses (id) ON DELETE CASCADE,
  name          VARCHAR(255) NOT NULL,
  body          TEXT NOT NULL,
  is_verified   BOOLEAN NOT NULL DEFAULT FALSE,
  verified_at   TIMESTAMPTZ NULL,
  verified_by   BIGINT NULL REFERENCES users (id) ON DELETE SET NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_sms_templates_business_id
  ON sms_templates (business_id);

CREATE INDEX IF NOT EXISTS idx_sms_templates_business_verified
  ON sms_templates (business_id, is_verified);

CREATE INDEX IF NOT EXISTS idx_sms_templates_created_at
  ON sms_templates (created_at DESC);

CREATE OR REPLACE FUNCTION sms_templates_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS sms_templates_set_updated_at ON sms_templates;
CREATE TRIGGER sms_templates_set_updated_at
  BEFORE UPDATE ON sms_templates
  FOR EACH ROW
  EXECUTE FUNCTION sms_templates_set_updated_at();