-- 0046 org branding. Per organization white labeling so the application carries each client's mark.
--
-- missionMATCH is sold to more than one client. FG Talent Partners is the first, not the only one, so
-- branding is tenant data on the organizations row rather than anything hard coded.
--
-- The logo bytes live in Postgres rather than object storage on purpose. A logo is tens of kilobytes and
-- there is exactly one per tenant, so a column inherits the existing RLS isolation for free and needs no
-- new infrastructure. Wiring object storage into the API would mean building the AWS adapter that does
-- not exist yet, for a few kilobytes per client. internal/platform/storage remains available if this ever
-- outgrows a column.
--
-- Every constraint here is defense in depth. The application validates the same rules in Go before it
-- ever reaches the database, decoding the bytes to prove the declared format is real. These CHECKs make a
-- bad row impossible even if that code is bypassed or a future caller forgets.

-- +goose Up
-- +goose StatementBegin

ALTER TABLE organizations
    ADD COLUMN display_name     text,
    ADD COLUMN logo             bytea,
    ADD COLUMN logo_mime        text,
    ADD COLUMN logo_updated_at  timestamptz;

-- SVG is deliberately absent. An uploaded SVG served from the application origin is stored cross site
-- scripting: it can carry a script element that executes for every user of that organization. Only raster
-- formats the Go standard library can decode are permitted, so the server can prove a file is really an
-- image before storing it.
ALTER TABLE organizations
    ADD CONSTRAINT organizations_logo_mime_chk
    CHECK (logo_mime IS NULL OR logo_mime IN ('image/png', 'image/jpeg'));

-- The bytes and the type travel together or not at all. A logo with no type could not be served safely,
-- and a type with no logo would make has_logo lie.
ALTER TABLE organizations
    ADD CONSTRAINT organizations_logo_pair_chk
    CHECK ((logo IS NULL AND logo_mime IS NULL) OR (logo IS NOT NULL AND logo_mime IS NOT NULL));

-- 512 KiB. Far beyond any real logo and small enough that a tenant cannot bloat the row or the backups.
ALTER TABLE organizations
    ADD CONSTRAINT organizations_logo_size_chk
    CHECK (logo IS NULL OR octet_length(logo) <= 524288);

-- A blank display name would render an empty brand slot. NULL means not set and falls back to name.
ALTER TABLE organizations
    ADD CONSTRAINT organizations_display_name_chk
    CHECK (display_name IS NULL OR length(btrim(display_name)) BETWEEN 1 AND 120);

-- Widen the column grant to exactly the branding columns and nothing else. status and slug stay
-- unwritable by the application role, so a tenant still cannot un suspend itself or seize another slug.
GRANT UPDATE (display_name, logo, logo_mime, logo_updated_at) ON organizations TO missionmatch_app;

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin

REVOKE UPDATE (display_name, logo, logo_mime, logo_updated_at) ON organizations FROM missionmatch_app;

ALTER TABLE organizations DROP CONSTRAINT IF EXISTS organizations_display_name_chk;
ALTER TABLE organizations DROP CONSTRAINT IF EXISTS organizations_logo_size_chk;
ALTER TABLE organizations DROP CONSTRAINT IF EXISTS organizations_logo_pair_chk;
ALTER TABLE organizations DROP CONSTRAINT IF EXISTS organizations_logo_mime_chk;

ALTER TABLE organizations
    DROP COLUMN IF EXISTS logo_updated_at,
    DROP COLUMN IF EXISTS logo_mime,
    DROP COLUMN IF EXISTS logo,
    DROP COLUMN IF EXISTS display_name;

-- +goose StatementEnd