-- +goose Up
-- Fonts become a file kind of their own so the design page can offer them.
ALTER TABLE files DROP CONSTRAINT files_kind_check;
ALTER TABLE files ADD CONSTRAINT files_kind_check
    CHECK (kind IN ('image', 'document', 'audio', 'video', 'font', 'archive', 'other'));
-- Fonts uploaded before this were stored as "other"; the magic bytes tell them
-- apart (the same signatures the Go sniffer trusts), and substring() reads
-- only that slice.
UPDATE files SET kind = 'font', content_type = CASE substring(data from 1 for 4)
    WHEN '\x774f4632'::bytea THEN 'font/woff2'
    WHEN '\x774f4646'::bytea THEN 'font/woff'
    WHEN '\x4f54544f'::bytea THEN 'font/otf'
    ELSE 'font/ttf' END
WHERE kind = 'other'
  AND substring(data from 1 for 4) IN ('\x774f4632'::bytea, '\x774f4646'::bytea, '\x4f54544f'::bytea, '\x00010000'::bytea);

-- +goose Down
UPDATE files SET kind = 'other', content_type = 'application/octet-stream' WHERE kind = 'font';
ALTER TABLE files DROP CONSTRAINT files_kind_check;
ALTER TABLE files ADD CONSTRAINT files_kind_check
    CHECK (kind IN ('image', 'document', 'audio', 'video', 'archive', 'other'));