`db/migrations/011_installer_credentials.sql`

64 lines
-- 011 · Brand-trained credentials (UX idea #2)
--
-- "Verified" is meaningless to a designer specifying $4K/roll de Gournay.
-- They want: "Has this installer been TRAINED by de Gournay?" We capture
-- structured per-brand credentials with optional cert scan + dates.
--
-- Existing `installers.accreditations TEXT[]` stays as the lightweight
-- catch-all (e.g. "WIA Certified Installer"). Brand-specific training lives
-- in the new structured table so each badge can carry a verifiable cert.
--
-- Reversible: DROP TABLE installer_credentials;

BEGIN;

CREATE TABLE IF NOT EXISTS installer_credentials (
  id                 SERIAL PRIMARY KEY,
  installer_id       INTEGER NOT NULL REFERENCES installers(id) ON DELETE CASCADE,
  brand              TEXT NOT NULL,
  credential_type    TEXT NOT NULL DEFAULT 'brand_trained',
                     -- brand_trained | brand_certified | brand_approved | manufacturer_partner | trade_member
  year_issued        INTEGER,
  year_expires       INTEGER,
  certificate_url    TEXT,
                     -- HTTPS URL of the cert scan. Validated http(s) only at write time.
  notes              TEXT,
  ops_verified       BOOLEAN NOT NULL DEFAULT false,
  ops_verified_at    TIMESTAMPTZ,
  ops_verified_by    TEXT,
  display_order      INTEGER DEFAULT 0,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT installer_credentials_type_check
    CHECK (credential_type IN ('brand_trained','brand_certified','brand_approved','manufacturer_partner','trade_member')),
  CONSTRAINT installer_credentials_year_check
    CHECK (year_issued IS NULL OR (year_issued >= 1900 AND year_issued <= 2100)),
  CONSTRAINT installer_credentials_expiry_check
    CHECK (year_expires IS NULL OR (year_expires >= 1900 AND year_expires <= 2200))
);

-- Per-installer view + ordering. Most installers will have 1-5 credentials.
CREATE INDEX IF NOT EXISTS idx_installer_credentials_installer
  ON installer_credentials (installer_id, display_order, year_issued DESC NULLS LAST);

-- Verified-only filter for the lead-side directory ("show me only de Gournay-trained installers").
CREATE INDEX IF NOT EXISTS idx_installer_credentials_brand
  ON installer_credentials (LOWER(brand))
  WHERE ops_verified = true;

-- Updated-at touch trigger — uses the same pattern as bookings/installers.
DROP TRIGGER IF EXISTS trg_installer_credentials_updated ON installer_credentials;
CREATE OR REPLACE FUNCTION installer_credentials_touch_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_installer_credentials_updated
  BEFORE UPDATE ON installer_credentials
  FOR EACH ROW EXECUTE FUNCTION installer_credentials_touch_updated_at();

COMMIT;