BEGIN;
ALTER TABLE materials ADD COLUMN canvas_version bigint NOT NULL DEFAULT 1 CHECK (canvas_version > 0);
ALTER TABLE materials ADD COLUMN canvas_document jsonb;
ALTER TABLE materials ADD CONSTRAINT canvas_board_only CHECK (canvas_document IS NULL OR kind='board');
ALTER TABLE workspaces ADD COLUMN slug text;
UPDATE workspaces SET slug=COALESCE(NULLIF(trim(both '-' from left(trim(both '-' from regexp_replace(lower(name),'[^a-z0-9]+','-','g')),50)),''),'workspace') || '-' || id::text;
ALTER TABLE workspaces ALTER COLUMN slug SET NOT NULL;

ALTER TABLE workspaces ADD CONSTRAINT workspace_slug_unique UNIQUE(project,slug);
ALTER TABLE workspaces ADD CONSTRAINT workspace_slug_valid CHECK (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$' AND length(slug)<=100);
ALTER TABLE materials ADD COLUMN slug text;
UPDATE materials SET slug=COALESCE(NULLIF(trim(both '-' from left(trim(both '-' from regexp_replace(lower(title),'[^a-z0-9]+','-','g')),50)),''),'canvas') || '-' || id::text;
ALTER TABLE materials ALTER COLUMN slug SET NOT NULL;

ALTER TABLE materials ADD CONSTRAINT material_slug_unique UNIQUE(project,workspace_id,slug);
ALTER TABLE materials ADD CONSTRAINT material_slug_valid CHECK (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$' AND length(slug)<=100);
CREATE FUNCTION foundry_default_slug() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE label text;
BEGIN
 IF NEW.slug IS NULL THEN
   IF TG_TABLE_NAME='workspaces' THEN label=NEW.name; ELSE label=NEW.title; END IF;
   label=left(trim(both '-' from regexp_replace(lower(label),'[^a-z0-9]+','-','g')),50);
   NEW.slug=COALESCE(NULLIF(trim(both '-' from label),''),'canvas') || '-' || NEW.id::text;
 END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER workspace_default_slug BEFORE INSERT ON workspaces FOR EACH ROW EXECUTE FUNCTION foundry_default_slug();
CREATE TRIGGER material_default_slug BEFORE INSERT ON materials FOR EACH ROW EXECUTE FUNCTION foundry_default_slug();
COMMIT;