BEGIN;


CREATE TABLE sonex.dictionary_values
(
    table_name character varying(120) COLLATE pg_catalog."default" NOT NULL,
    column_name character varying(120) COLLATE pg_catalog."default" NOT NULL,
    value text COLLATE pg_catalog."default" NOT NULL
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS sonex.dictionary_values
    OWNER TO postgres;

GRANT ALL ON TABLE sonex.dictionary_values TO postgres;
GRANT INSERT, DELETE, SELECT, UPDATE ON TABLE sonex.dictionary_values TO sonex;

CREATE UNIQUE INDEX uq_dictionary_values_table_column_value_ci
    ON sonex.dictionary_values USING btree
    (lower(table_name), lower(column_name), lower(value))
    TABLESPACE pg_default;

CREATE INDEX ix_dictionary_values_table_name_ci
    ON sonex.dictionary_values USING btree
    (lower(table_name))
    TABLESPACE pg_default;

CREATE INDEX ix_dictionary_values_table_column_ci
    ON sonex.dictionary_values USING btree
    (lower(table_name), lower(column_name))
    TABLESPACE pg_default;

-- DROP FUNCTION IF EXISTS sonex.refresh_dictionary_values(text);
CREATE OR REPLACE FUNCTION sonex.refresh_dictionary_values(
    p_table_name text)
RETURNS TABLE(processed_columns integer, stored_columns integer, stored_values integer)
LANGUAGE plpgsql
AS $BODY$
DECLARE
    v_input_table_name text;
    v_schema_name text := 'sonex';
    v_table_name text;
    v_relation regclass;
    v_column record;
    v_distinct_count bigint;
    v_inserted_count integer;
    v_processed_columns integer := 0;
    v_stored_columns integer := 0;
    v_stored_values integer := 0;
BEGIN
    v_input_table_name := btrim(p_table_name);
    IF v_input_table_name IS NULL OR v_input_table_name = '' THEN
        RAISE EXCEPTION 'Table name cannot be empty.';
    END IF;

    IF strpos(v_input_table_name, '.') > 0 THEN
        v_schema_name := btrim(split_part(v_input_table_name, '.', 1));
        v_table_name := btrim(split_part(v_input_table_name, '.', 2));
    ELSE
        v_table_name := v_input_table_name;
    END IF;

    IF v_schema_name = '' OR v_table_name = '' THEN
        RAISE EXCEPTION 'Invalid table name: %.', p_table_name;
    END IF;

    SELECT to_regclass(format('%I.%I', v_schema_name, v_table_name))
    INTO v_relation;

    IF v_relation IS NULL THEN
        RAISE EXCEPTION 'Table %.% does not exist.', v_schema_name, v_table_name;
    END IF;

    DELETE FROM sonex.dictionary_values
    WHERE lower(table_name) = lower(v_table_name);

    FOR v_column IN
        SELECT c.column_name
        FROM information_schema.columns c
        WHERE c.table_schema = v_schema_name
          AND c.table_name = v_table_name
        ORDER BY c.ordinal_position
    LOOP
        v_processed_columns := v_processed_columns + 1;

        EXECUTE format(
            'SELECT count(*) FROM (
                SELECT DISTINCT lower(btrim((%1$I)::text)) AS value_key
                FROM %2$I.%3$I
                WHERE %1$I IS NOT NULL
                  AND btrim((%1$I)::text) <> ''''
                LIMIT 1001
            ) AS distinct_values',
            v_column.column_name,
            v_schema_name,
            v_table_name)
        INTO v_distinct_count;

        IF COALESCE(v_distinct_count, 0) = 0 OR v_distinct_count > 1000 THEN
            CONTINUE;
        END IF;

        EXECUTE format(
            'INSERT INTO sonex.dictionary_values (table_name, column_name, value)
             SELECT %1$L, %2$L, value_text
             FROM (
                 SELECT min(value_text) AS value_text
                 FROM (
                     SELECT btrim((%3$I)::text) AS value_text
                     FROM %4$I.%5$I
                     WHERE %3$I IS NOT NULL
                       AND btrim((%3$I)::text) <> ''''
                 ) AS source_values
                 GROUP BY lower(value_text)
             ) AS values_source
             ORDER BY value_text',
            v_table_name,
            v_column.column_name,
            v_column.column_name,
            v_schema_name,
            v_table_name);

        GET DIAGNOSTICS v_inserted_count = ROW_COUNT;
        v_stored_columns := v_stored_columns + 1;
        v_stored_values := v_stored_values + COALESCE(v_inserted_count, 0);
    END LOOP;

    RETURN QUERY
    SELECT
        v_processed_columns,
        v_stored_columns,
        v_stored_values;
END;
$BODY$;

ALTER FUNCTION sonex.refresh_dictionary_values(text)
    OWNER TO postgres;

REVOKE ALL ON FUNCTION sonex.refresh_dictionary_values(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION sonex.refresh_dictionary_values(text) TO postgres;
GRANT EXECUTE ON FUNCTION sonex.refresh_dictionary_values(text) TO sonex;

COMMIT;
