DROP FUNCTION IF EXISTS public.immutable_digest CASCADE;
CREATE FUNCTION public.immutable_digest(input_text text, algorithm text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
SELECT pg_catalog.encode(public.digest(input_text, algorithm), 'hex');
$$;

DROP FUNCTION IF EXISTS public.immutable_to_char(timestamp, text);
CREATE FUNCTION public.immutable_to_char(timestamp, text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
SELECT pg_catalog.to_char($1, $2);
$$;

DROP FUNCTION IF EXISTS public.immutable_to_char(timestamptz, text);
CREATE FUNCTION public.immutable_to_char(timestamptz, text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
SELECT pg_catalog.to_char($1, $2);
$$;

DROP FUNCTION IF EXISTS public.immutable_to_char(date, text);
CREATE FUNCTION public.immutable_to_char(date, text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
SELECT pg_catalog.to_char($1, $2);
$$;

DROP FUNCTION IF EXISTS public.fn_addhashcolumn;
DROP PROCEDURE IF EXISTS public.sp_AddHashColumn;
CREATE PROCEDURE public.sp_AddHashColumn(v_table_name text)
AS
$$
DECLARE
    v_columns text := '';
    v_sql text;
    col RECORD;
    v_schema text := pg_catalog.split_part(v_table_name, '.', 1);
    v_table text := pg_catalog.split_part(v_table_name, '.', 2);
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_name = v_table
        AND table_schema = v_schema
        AND column_name = 'hash'
    ) then
        EXECUTE 'ALTER TABLE ' || v_table_name || ' DROP COLUMN Hash';
    END IF;

    FOR col IN
    SELECT a.attname AS column_name, 
           t.typname AS data_type
    FROM pg_catalog.pg_attribute a
    JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
    JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
    WHERE c.relname = v_table
      AND c.relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = v_schema)
      AND a.attnum > 0
      AND NOT a.attisdropped
      AND a.attname != 'Hash'
	LOOP
	    IF col.data_type IN ('date', 'timestamp', 'timestamptz') THEN
	        v_columns := v_columns || 'COALESCE(public.immutable_to_char(' || quote_ident(col.column_name) || ', ''yyyy.mm.dd hh24:mi:ss''), '''') || ';
	    ELSE
	        v_columns := v_columns || 'COALESCE(' || quote_ident(col.column_name) || '::text, '''') || ';
	    END IF;
	END LOOP;
    
    IF length(v_columns) > 0 THEN
        v_columns := left(v_columns, length(v_columns) - 4);
    ELSE
        v_columns := '''''';
    END IF;
    
    v_sql := pg_catalog.format('
            ALTER TABLE %I.%I
            ADD COLUMN Hash TEXT GENERATED ALWAYS AS (
                public.immutable_digest(%s, ''sha256'')
            ) STORED;
        ', v_schema, v_table, v_columns);

    EXECUTE v_sql;
END;
$$ LANGUAGE plpgsql;
