-- License MIT
-- Place in your schema & update your table name

-- First, make sure the pgcrypto & unaccent extension is available
CREATE EXTENSION IF NOT EXISTS unaccent with schema extensions;
CREATE EXTENSION IF NOT EXISTS pgcrypto with schema extensions;

CREATE OR REPLACE FUNCTION public.slugify("value" TEXT)
RETURNS TEXT AS $$
  -- removes accents (diacritic signs) from a given string --
  WITH "unaccented" AS (
    SELECT unaccent("value") AS "value"
  ),
  -- lowercases the string
  "lowercase" AS (
    SELECT lower("value") AS "value"
    FROM "unaccented"
  ),
  -- replaces anything that's not a letter, number, hyphen('-'), or underscore('_') with a hyphen('-')
  "hyphenated" AS (
    SELECT regexp_replace("value", '[^a-z0-9\\-_]+', '-', 'gi') AS "value"
    FROM "lowercase"
  ),
  -- trims hyphens('-') if they exist on the head or tail of the string
  "trimmed" AS (
    SELECT regexp_replace(regexp_replace("value", '\\-+$', ''), '^\\-', '') AS "value"
    FROM "hyphenated"
  )
  -- add a 6 character md5 to the slug e.g. -2c4377
  SELECT concat("value", '-', substring(md5(random()::text), 1, 6)) FROM "trimmed";
$$ LANGUAGE SQL STRICT IMMUTABLE;

CREATE FUNCTION public.set_slug_from_title() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.slug := public.slugify(NEW.title);
  RETURN NEW;
END
$$;

CREATE TRIGGER "trg_slug_insert_update"
BEFORE INSERT OR UPDATE ON public.decision
FOR EACH ROW
WHEN (NEW.title IS NOT NULL AND (NEW.slug IS NULL OR LEFT(NEW.slug, -7) != LEFT(public.slugify(NEW.title), -7)))
EXECUTE PROCEDURE public.set_slug_from_title();