CREATE OR REPLACE FUNCTION normalize_arabic_persian_text(input_text TEXT)
RETURNS TEXT AS $$
DECLARE
    result TEXT := '';
    i INT;
    current_char TEXT;
    prev_char TEXT := '';
    next_char TEXT := '';
    in_word BOOLEAN := FALSE;
BEGIN
    -- First, convert to isolated forms and map to Persian equivalents
    FOR i IN 1..length(input_text) LOOP
        current_char := substring(input_text FROM i FOR 1);
        IF i < length(input_text) THEN
            next_char := substring(input_text FROM i+1 FOR 1);
        ELSE
            next_char := '';
        END IF;

        -- Alef and hamza
        IF current_char = ANY(ARRAY[E'\uFE81'::TEXT, E'\uFE83'::TEXT, E'\uFE85'::TEXT, E'\uFE87'::TEXT, E'\uFE89'::TEXT, E'\uFE8D'::TEXT, E'\u0622'::TEXT, E'\u0623'::TEXT, E'\u0625'::TEXT, E'\u0627'::TEXT, E'\u0671'::TEXT]) THEN
            current_char := E'\u0627';  -- All forms of alef to ا
        -- Beh
        ELSIF current_char = ANY(ARRAY[E'\uFE8F'::TEXT, E'\uFE91'::TEXT, E'\uFE93'::TEXT, E'\u0628'::TEXT]) THEN
            current_char := E'\u0628';  -- ب
        -- Teh
        ELSIF current_char = ANY(ARRAY[E'\uFE95'::TEXT, E'\uFE97'::TEXT, E'\uFE99'::TEXT, E'\u062A'::TEXT]) THEN
            current_char := E'\u062A';  -- ت
        -- Theh
        ELSIF current_char = ANY(ARRAY[E'\uFE99'::TEXT, E'\uFE9B'::TEXT, E'\uFE9D'::TEXT, E'\u062B'::TEXT]) THEN
            current_char := E'\u062B';  -- ث
        -- Jeem
        ELSIF current_char = ANY(ARRAY[E'\uFE9D'::TEXT, E'\uFE9F'::TEXT, E'\uFEA1'::TEXT, E'\u062C'::TEXT]) THEN
            current_char := E'\u062C';  -- ج
        -- Hah
        ELSIF current_char = ANY(ARRAY[E'\uFEA1'::TEXT, E'\uFEA3'::TEXT, E'\uFEA5'::TEXT, E'\u062D'::TEXT]) THEN
            current_char := E'\u062D';  -- ح
        -- Khah
        ELSIF current_char = ANY(ARRAY[E'\uFEA5'::TEXT, E'\uFEA7'::TEXT, E'\uFEA9'::TEXT, E'\u062E'::TEXT]) THEN
            current_char := E'\u062E';  -- خ
        -- Dal
        ELSIF current_char = ANY(ARRAY[E'\uFEA9'::TEXT, E'\uFEAB'::TEXT, E'\u062F'::TEXT]) THEN
            current_char := E'\u062F';  -- د
        -- Thal
        ELSIF current_char = ANY(ARRAY[E'\uFEAB'::TEXT, E'\uFEAD'::TEXT, E'\u0630'::TEXT]) THEN
            current_char := E'\u0630';  -- ذ
        -- Reh
        ELSIF current_char = ANY(ARRAY[E'\uFEAD'::TEXT, E'\uFEAF'::TEXT, E'\u0631'::TEXT]) THEN
            current_char := E'\u0631';  -- ر
        -- Zain
        ELSIF current_char = ANY(ARRAY[E'\uFEAF'::TEXT, E'\uFEB1'::TEXT, E'\u0632'::TEXT]) THEN
            current_char := E'\u0632';  -- ز
        -- Seen
        ELSIF current_char = ANY(ARRAY[E'\uFEB1'::TEXT, E'\uFEB3'::TEXT, E'\uFEB5'::TEXT, E'\u0633'::TEXT]) THEN
            current_char := E'\u0633';  -- س
        -- Sheen
        ELSIF current_char = ANY(ARRAY[E'\uFEB5'::TEXT, E'\uFEB7'::TEXT, E'\uFEB9'::TEXT, E'\u0634'::TEXT]) THEN
            current_char := E'\u0634';  -- ش
        -- Sad
        ELSIF current_char = ANY(ARRAY[E'\uFEB9'::TEXT, E'\uFEBB'::TEXT, E'\uFEBD'::TEXT, E'\u0635'::TEXT]) THEN
            current_char := E'\u0635';  -- ص
        -- Dad
        ELSIF current_char = ANY(ARRAY[E'\uFEBD'::TEXT, E'\uFEBF'::TEXT, E'\uFEC1'::TEXT, E'\u0636'::TEXT]) THEN
            current_char := E'\u0636';  -- ض
        -- Tah
        ELSIF current_char = ANY(ARRAY[E'\uFEC1'::TEXT, E'\uFEC3'::TEXT, E'\uFEC5'::TEXT, E'\u0637'::TEXT]) THEN
            current_char := E'\u0637';  -- ط
        -- Zah
        ELSIF current_char = ANY(ARRAY[E'\uFEC5'::TEXT, E'\uFEC7'::TEXT, E'\uFEC9'::TEXT, E'\u0638'::TEXT]) THEN
            current_char := E'\u0638';  -- ظ
        -- Ain
        ELSIF current_char = ANY(ARRAY[E'\uFEC9'::TEXT, E'\uFECB'::TEXT, E'\uFECD'::TEXT, E'\u0639'::TEXT]) THEN
            current_char := E'\u0639';  -- ع
        -- Ghain
        ELSIF current_char = ANY(ARRAY[E'\uFECD'::TEXT, E'\uFECF'::TEXT, E'\uFED1'::TEXT, E'\u063A'::TEXT]) THEN
            current_char := E'\u063A';  -- غ
        -- Feh
        ELSIF current_char = ANY(ARRAY[E'\uFED1'::TEXT, E'\uFED3'::TEXT, E'\uFED5'::TEXT, E'\u0641'::TEXT]) THEN

current_char := E'\u0641';  -- ف
        -- Qaf
        ELSIF current_char = ANY(ARRAY[E'\uFED5'::TEXT, E'\uFED7'::TEXT, E'\uFED9'::TEXT, E'\u0642'::TEXT]) THEN
            current_char := E'\u0642';  -- ق
        -- Kaf
        ELSIF current_char = ANY(ARRAY[E'\uFED9'::TEXT, E'\uFEDB'::TEXT, E'\uFEDD'::TEXT, E'\u0643'::TEXT]) THEN
            current_char := E'\u06A9';  -- ک (Persian kaf)
        -- Lam
        ELSIF current_char = ANY(ARRAY[E'\uFEDD'::TEXT, E'\uFEDF'::TEXT, E'\uFEE1'::TEXT, E'\u0644'::TEXT]) THEN
            current_char := E'\u0644';  -- ل
        -- Meem
        ELSIF current_char = ANY(ARRAY[E'\uFEE1'::TEXT, E'\uFEE3'::TEXT, E'\uFEE5'::TEXT, E'\u0645'::TEXT]) THEN
            current_char := E'\u0645';  -- م
        -- Noon
        ELSIF current_char = ANY(ARRAY[E'\uFEE5'::TEXT, E'\uFEE7'::TEXT, E'\uFEE9'::TEXT, E'\u0646'::TEXT]) THEN
            current_char := E'\u0646';  -- ن
        -- Heh
        ELSIF current_char = ANY(ARRAY[E'\uFEE9'::TEXT, E'\uFEEB'::TEXT, E'\uFEED'::TEXT, E'\u0647'::TEXT, E'\u06C0'::TEXT]) THEN
            current_char := E'\u0647';  -- ه
        -- Waw
        ELSIF current_char = ANY(ARRAY[E'\uFEED'::TEXT, E'\uFEEF'::TEXT, E'\u0648'::TEXT]) THEN
            current_char := E'\u0648';  -- و
        -- Yeh
        ELSIF current_char = ANY(ARRAY[E'\uFEF1'::TEXT, E'\uFEF3'::TEXT, E'\uFEF4'::TEXT, E'\u0649'::TEXT, E'\u064A'::TEXT, E'\uFEF5'::TEXT]) THEN
            current_char := E'\u06CC';  -- ی (Persian yeh)
        -- Persian specific letters
        ELSIF current_char = ANY(ARRAY[E'\uFB56'::TEXT, E'\uFB57'::TEXT, E'\uFB58'::TEXT, E'\uFB59'::TEXT, E'\u067E'::TEXT]) THEN
            current_char := E'\u067E';  -- پ (peh)
        ELSIF current_char = ANY(ARRAY[E'\uFB7A'::TEXT, E'\uFB7B'::TEXT, E'\uFB7C'::TEXT, E'\uFB7D'::TEXT, E'\u0686'::TEXT]) THEN
            current_char := E'\u0686';  -- چ (cheh)
        ELSIF current_char = ANY(ARRAY[E'\uFB8A'::TEXT, E'\uFB8B'::TEXT, E'\u0698'::TEXT]) THEN
            current_char := E'\u0698';  -- ژ (jeh)
        ELSIF current_char = ANY(ARRAY[E'\uFB92'::TEXT, E'\uFB93'::TEXT, E'\uFB94'::TEXT, E'\uFB95'::TEXT, E'\u06AF'::TEXT]) THEN
            current_char := E'\u06AF';  -- گ (gaf)
        -- Additional Arabic letters not commonly used in Persian
        ELSIF current_char = ANY(ARRAY[E'\u0629'::TEXT, E'\uFE93'::TEXT, E'\uFE94'::TEXT]) THEN
            current_char := E'\u0647';  -- ة (teh marbuta) to ه (heh)
        ELSIF current_char = ANY(ARRAY[E'\u0624'::TEXT, E'\uFE85'::TEXT, E'\uFE86'::TEXT]) THEN
            current_char := E'\u0648';  -- ؤ (waw with hamza) to و (waw)
        ELSIF current_char = ANY(ARRAY[E'\u0626'::TEXT, E'\uFE89'::TEXT, E'\uFE8A'::TEXT, E'\uFE8B'::TEXT, E'\uFE8C'::TEXT]) THEN
            current_char := E'\u06CC';  -- ئ (yeh with hamza) to ی (Persian yeh)
        -- Additional special cases
        ELSIF current_char = E'\u0640'::TEXT THEN
            current_char := '';  -- Remove tatweel (kashida)
        ELSIF current_char = E'\uFEFB'::TEXT OR current_char = E'\uFEFC'::TEXT THEN
            current_char := E'\u0644\u0627';  -- لا (lam-alef ligature) to separate ل and ا
        ELSIF current_char = E'\uFEF5'::TEXT OR current_char = E'\uFEF6'::TEXT THEN
            current_char := E'\u0644\u0622';  -- لآ (lam-alef madda ligature) to separate ل and آ
        ELSIF current_char = E'\uFEF7'::TEXT OR current_char = E'\uFEF8'::TEXT THEN
            current_char := E'\u0644\u0623';  -- لأ (lam-alef hamza above ligature) to separate ل and أ
        ELSIF current_char = E'\uFEF9'::TEXT OR current_char = E'\uFEFA'::TEXT THEN
            current_char := E'\u0644\u0625';  -- لإ (lam-alef hamza below ligature) to separate ل and إ
        ELSIF current_char = E'\u0621'::TEXT THEN
            current_char := '';  -- Remove standalone hamza
        -- Special case for لا
        ELSIF current_char = E'\u0644'::TEXT AND next_char = E'\u0627'::TEXT THEN
            current_char := E'\u0644\u0627';  -- لا
            i := i + 1;  -- Skip next character
        -- Special case for لآ

        ELSIF current_char = E'\u0644'::TEXT AND next_char = E'\u0622'::TEXT THEN
            current_char := E'\u0644\u0622';  -- لآ
            i := i + 1;  -- Skip next character
        END IF;

        -- Append the character to the result, preserving word boundaries
        IF current_char != '' THEN
            IF NOT in_word THEN
                -- Add a space only if it's not the first character
                IF result != '' THEN
                    result := result || ' ';
                END IF;
                in_word := TRUE;
            END IF;
            result := result || current_char;
        END IF;

        prev_char := current_char;
    END LOOP;

    -- Remove diacritical marks
    result := translate(result,
        E'\u064B\u064C\u064D\u064E\u064F\u0650\u0651\u0652\u0670\u0655\u0656\u0653\u0654\u0610\u0611\u0612\u0613\u0614\u0615\u0616\u0617\u0618\u0619\u061A\u06D6\u06D7\u06D8\u06D9\u06DA\u06DB\u06DC\u06DD\u06DE\u06DF\u06E0\u06E1\u06E2\u06E3\u06E4\u06E5\u06E6\u06E7\u06E8\u06E9\u06EA\u06EB\u06EC\u06ED',
        repeat('', 48)
    );
    -- The above line removes the following diacritical marks:
    -- \u064B (Fathatan), \u064C (Dammatan), \u064D (Kasratan), \u064E (Fatha), \u064F (Damma), \u0650 (Kasra),
    -- \u0651 (Shadda), \u0652 (Sukun), \u0670 (Supernoscript Alef), \u0655 (Hamza Below), \u0656 (Subnoscript Alef),
    -- \u0653 (Maddah Above), \u0654 (Hamza Above), \u0610-\u061A (Arabic signs),
    -- \u06D6-\u06ED (Arabic small high ligatures and other marks)

    -- Remove multiple spaces
    result := regexp_replace(result, '\s+', ' ', 'g');
    -- This removes any sequence of one or more whitespace characters (\s+)
    -- and replaces it with a single space

    -- Trim leading and trailing spaces
    result := trim(both from result);
    -- 'both' removes spaces from both the beginning and end of the string
    -- This includes spaces, tabs, newlines, and other whitespace characters

    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT normalize_arabic_persian_text(E'نَصّ حَكيمٌ لَهُ سِرٌّ قَاطِعٌ وَذُو شَأنٍ عَظِيمٍ مَكتُوبٌ عَلَى ثَوْبٍ أخْضَرَ وَمُغَلَّفٌ بِجِلْدٍ أزْرَق');