DO $$
BEGIN
  CREATE TYPE resource_kind_enum AS ENUM ('FILE', 'ARTICLE');
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

ALTER TABLE resources
  ADD COLUMN IF NOT EXISTS slug text,
  ADD COLUMN IF NOT EXISTS resource_kind resource_kind_enum NOT NULL DEFAULT 'FILE',
  ADD COLUMN IF NOT EXISTS cover_image_url text,
  ADD COLUMN IF NOT EXISTS author_name text,
  ADD COLUMN IF NOT EXISTS content_html text,
  ADD COLUMN IF NOT EXISTS show_to_individuals boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS show_to_investors boolean NOT NULL DEFAULT true,
  ADD COLUMN IF NOT EXISTS show_to_organizations boolean NOT NULL DEFAULT true;

ALTER TABLE resources
  ALTER COLUMN file_url DROP NOT NULL;

UPDATE resources
SET slug = CASE
  WHEN trim(regexp_replace(lower(title), '[^a-z0-9]+', '-', 'g'), '-') <> ''
    THEN trim(regexp_replace(lower(title), '[^a-z0-9]+', '-', 'g'), '-') || '-' || id
  ELSE 'resource-' || id
END
WHERE slug IS NULL OR btrim(slug) = '';

ALTER TABLE resources
  ALTER COLUMN slug SET NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS resources_slug_idx
  ON resources (slug);
