CREATE EXTENSION IF NOT EXISTS "unaccent";

CREATE OR REPLACE FUNCTION "public"."slugify" ("value" TEXT) RETURNS TEXT AS $$ 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"
	)
SELECT "value"
FROM "trimmed";

$$ LANGUAGE SQL STRICT IMMUTABLE;

CREATE TABLE public.artist (
	id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name TEXT NOT NULL,
	slug TEXT GENERATED ALWAYS AS ("public"."slugify" ("name")) STORED NOT NULL
);

CREATE TABLE public.release (
	id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name TEXT NOT NULL,
	slug TEXT GENERATED ALWAYS AS ("public"."slugify" ("name")) STORED NOT NULL,
	synonyms TEXT []
);

CREATE TABLE public.artist_release (
	artist_id INTEGER NOT NULL,
	release_id INTEGER NOT NULL
);

CREATE TABLE public.artist_type (
	name TEXT NOT NULL PRIMARY KEY
);

CREATE TABLE public.generated_only (
	id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);

CREATE TABLE public.defaultable (
	id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name TEXT NOT NULL DEFAULT 'This is the default'
);

CREATE TYPE public.mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE public.person (
    name text NOT NULL,
    current_mood public.mood NOT NULL
);

CREATE TABLE public.some_json_table (
	id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    json_value JSON NOT NULL,
    jsonb_value JSONB NOT NULL
);

CREATE TABLE public.soft_delete (
	id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	value INTEGER NOT NULL,
	deleted_at TIMESTAMP DEFAULT NULL
);
