supabase-migration.sql

-- Run this in Supabase SQL Editor

CREATE TABLE IF NOT EXISTS importance (
	id SERIAL PRIMARY KEY,
	name TEXT NOT NULL UNIQUE,
	level INTEGER NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS tags (
	id SERIAL PRIMARY KEY,
	name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS words (
	id TEXT PRIMARY KEY,
	importance_id INTEGER REFERENCES importance(id),
	comment TEXT,
	likes INTEGER DEFAULT 0,
	created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS word_tags (
	word_id TEXT NOT NULL REFERENCES words(id) ON DELETE CASCADE,
	tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
	PRIMARY KEY (word_id, tag_id)
);

CREATE TABLE IF NOT EXISTS translations (
	id SERIAL PRIMARY KEY,
	word_id TEXT REFERENCES words(id) ON DELETE CASCADE,
	translation TEXT NOT NULL,
	comment TEXT,
	stress_position INTEGER,
	sort_order INTEGER DEFAULT 0,
	likes INTEGER DEFAULT 0,
	created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS messages (
	id SERIAL PRIMARY KEY,
	name TEXT NOT NULL,
	telegram TEXT NOT NULL,
	message TEXT NOT NULL,
	user_token TEXT,
	reply TEXT,
	created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION increment_word_likes(word_id TEXT)
RETURNS INTEGER
LANGUAGE plpgsql AS $$
DECLARE new_likes INTEGER;
BEGIN
  UPDATE words SET likes = likes + 1 WHERE id = word_id RETURNING likes INTO new_likes;
  RETURN new_likes;
END;
$$;

CREATE OR REPLACE FUNCTION decrement_word_likes(word_id TEXT)
RETURNS INTEGER
LANGUAGE plpgsql AS $$
DECLARE new_likes INTEGER;
BEGIN
  UPDATE words SET likes = GREATEST(likes - 1, 0) WHERE id = word_id RETURNING likes INTO new_likes;
  RETURN new_likes;
END;
$$;

CREATE OR REPLACE FUNCTION increment_translation_likes(trans_id INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql AS $$
DECLARE new_likes INTEGER;
BEGIN
  UPDATE translations SET likes = likes + 1 WHERE id = trans_id RETURNING likes INTO new_likes;
  RETURN new_likes;
END;
$$;

CREATE OR REPLACE FUNCTION decrement_translation_likes(trans_id INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql AS $$
DECLARE new_likes INTEGER;
BEGIN
  UPDATE translations SET likes = GREATEST(likes - 1, 0) WHERE id = trans_id RETURNING likes INTO new_likes;
  RETURN new_likes;
END;
$$;

-- Prevent removing the last tag from a word
CREATE OR REPLACE FUNCTION prevent_last_tag_removal()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM words WHERE id = OLD.word_id) THEN
    RETURN OLD;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM word_tags WHERE word_id = OLD.word_id AND tag_id != OLD.tag_id) THEN
    RAISE EXCEPTION 'Word must have at least one tag';
  END IF;
  RETURN OLD;
END;
$$;

DROP TRIGGER IF EXISTS word_tags_last_tag_check ON word_tags;
CREATE TRIGGER word_tags_last_tag_check
  BEFORE DELETE ON word_tags
  FOR EACH ROW
  EXECUTE FUNCTION prevent_last_tag_removal();

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE OR REPLACE FUNCTION normalize_text(s TEXT)
RETURNS TEXT
LANGUAGE sql IMMUTABLE AS $$
  SELECT TRANSLATE(
    REPLACE(REPLACE(LOWER(s), CHR(39), '’'), '`', '’'),
    'иiуг',
    'ііўґ'
  );
$$;

DROP FUNCTION IF EXISTS get_words(text, text);

CREATE OR REPLACE FUNCTION get_words(
	search TEXT DEFAULT '',
	tag_filter TEXT DEFAULT '',
	sort_field TEXT DEFAULT 'word',
	sort_dir TEXT DEFAULT 'desc',
	result_offset INTEGER DEFAULT 0,
	result_limit INTEGER DEFAULT 100000
)
RETURNS JSON
LANGUAGE plpgsql AS $$
DECLARE result JSON;
BEGIN
	WITH filtered AS (
		SELECT
			w.id,
			w.importance_id,
			w.comment,
			w.likes,
			w.created_at,
			i.id AS imp_id,
			i.name AS imp_name,
			i.level AS imp_level,
			CASE
				WHEN sort_field = 'importance' THEN COALESCE(i.level::text, '0')
				WHEN sort_field = 'likes' THEN LPAD(w.likes::text, 10, '0')
				WHEN sort_field = 'created_at' THEN COALESCE(to_char(w.created_at, 'YYYYMMDDHH24MISS'), '0')
				ELSE LOWER(w.id)
			END AS sort_expr
		FROM words w
		LEFT JOIN importance i ON w.importance_id = i.id
		WHERE (
			search = ''
			OR normalize_text(w.id) LIKE '%' || normalize_text(search) || '%'
			OR EXISTS (
				SELECT 1 FROM translations t
				WHERE t.word_id = w.id AND normalize_text(t.translation) LIKE '%' || normalize_text(search) || '%'
			)
		)
		AND (
			tag_filter = ''
			OR EXISTS (
				SELECT 1 FROM word_tags wt2
				JOIN tags tg2 ON wt2.tag_id = tg2.id
				WHERE wt2.word_id = w.id AND tg2.name = ANY(string_to_array(tag_filter, ','))
			)
		)
	),
	sorted AS (
		SELECT * FROM filtered
		ORDER BY
			CASE WHEN sort_dir = 'asc' THEN sort_expr END ASC NULLS LAST,
			CASE WHEN sort_dir = 'desc' THEN sort_expr END DESC NULLS LAST,
			LOWER(id)
		OFFSET result_offset
		LIMIT result_limit
	)
	SELECT json_build_object(
		'words', COALESCE((SELECT json_agg(row_to_json(subq)) FROM (
			SELECT
				s.id,
				s.comment,
				s.likes,
				json_build_object('id', s.imp_id, 'name', s.imp_name, 'level', s.imp_level) AS importance,
				COALESCE(
					(SELECT json_agg(
						json_build_object(
							'id', t.id,
							'translation', t.translation,
							'comment', t.comment,
							'stress_position', t.stress_position,
							'likes', t.likes
						) ORDER BY t.sort_order, t.id
					) FROM translations t WHERE t.word_id = s.id),
					'[]'::json
				) AS translations,
				COALESCE(
					(SELECT json_agg(tg.name ORDER BY tg.name)
					FROM word_tags wt
					JOIN tags tg ON wt.tag_id = tg.id
					WHERE wt.word_id = s.id),
					'[]'::json
				) AS tags
			FROM sorted s
		) subq), '[]'::json),
		'total', (SELECT COUNT(*) FROM filtered)
	) INTO result;

	RETURN result;
END;
$$;

CREATE INDEX IF NOT EXISTS idx_translations_word_id ON translations(word_id);
CREATE INDEX IF NOT EXISTS idx_translations_normalized_trgm ON translations USING gin (normalize_text(translation) gin_trgm_ops);