-- Type: include_result
 
-- DROP TYPE IF EXISTS include_result;
 
CREATE TYPE include_result AS
(
	select_clause text,
	join_clause text,
	alias_mapping jsonb
);
 
-- ALTER TYPE include_result
--     OWNER TO postgres;

CREATE OR REPLACE PROCEDURE public.get_grid_items(
    IN json_input jsonb,
    INOUT result jsonb)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    v_record_offset INT;
    v_record_limit INT;
    v_sort_columns JSONB;
    v_search_all JSONB;
    v_search_any JSONB;
    v_select_columns JSONB;
    v_entity_table TEXT;
    v_print_query BOOLEAN;
    v_entity_name TEXT;
    v_select_clause TEXT := '';
    v_join_clause TEXT := '';
    v_where_clause TEXT := ' WHERE ';
    v_group_by_clause TEXT := '';
    v_query_str TEXT;
    v_total_records_count INT;
    v_records JSONB;
    v_headers JSONB := '[]';
    v_executed_query TEXT := '';
    v_company_id INT;
    
    -- Variables for iteration
    v_col JSONB;
    v_col_search JSONB;
    v_sort_col JSONB;
    v_inner_col_search JSONB;
    
    -- To handle column aliases with "." notation
    v_alias_mapping JSONB := '{}';
    v_include_result_row include_result;

    -- For dynamic JSON construction
    v_json_fields TEXT := '';
    v_json_object TEXT;
    v_header_object TEXT;
    
    -- For OR conditions
    v_or_clause TEXT := '';
    v_or_group TEXT := '';

    -- Security: Define valid operators
    v_valid_operators JSONB := jsonb_build_array('=', '<', '>', '<=', '>=', 'IS', 'IS NOT', 'IN', 'NOT IN', 'LIKE', 'NOT LIKE', 'ILIKE', 'NOT ILIKE', 'BETWEEN');
BEGIN
    -- Parse the JSON input
    v_record_offset := COALESCE((json_input->>'start_index')::INT, 0);
    v_record_limit := COALESCE((json_input->>'limit_range')::INT, 10);
    v_sort_columns := (json_input->'sort_columns')::jsonb;
    v_search_all := (json_input->'search_all')::jsonb;
    v_search_any := (json_input->'search_any')::jsonb;
    v_entity_name := json_input->>'entity_name';
    v_company_id := COALESCE((json_input->>'company_id')::INT, 0);
    v_print_query := COALESCE((json_input->>'print_query')::BOOLEAN, FALSE);

    -- Fetch the select_columns dynamically
    v_select_columns := get_grid_columns(v_entity_name, v_company_id);

    -- Fetch the primary_table dynamically based on the entity_name
    SELECT primary_table INTO v_entity_table 
    FROM master_grids 
    WHERE entity_name = v_entity_name AND company_id = v_company_id;

    -- Build the SELECT clause
    FOR v_col IN SELECT * FROM jsonb_array_elements(v_select_columns) LOOP
        IF v_select_clause <> '' THEN
            v_select_clause := v_select_clause || ', ';
        END IF;
        v_select_clause := v_select_clause || (v_col->>0);
        IF (v_col->>1) IS NOT NULL THEN
            v_select_clause := v_select_clause || ' AS ' || quote_ident((v_col->>1));
                
            v_header_object := jsonb_build_object(
                'header', v_col->>1,
                'column_width', v_col->>2,
                'column_order', v_col->>3,
                'is_searchable', v_col->>4,
                'is_grid_column', v_col->>5,
                'is_sortable', v_col->>6,
                'field_value', v_col->>7
            )::TEXT;

            v_headers := v_headers || jsonb(v_header_object);
        END IF;
        v_alias_mapping := v_alias_mapping || jsonb_build_object(quote_ident((v_col->>1)), (v_col->>0));

        -- Construct the dynamic JSON field list
        IF v_json_fields <> '' THEN
            v_json_fields := v_json_fields || ', ';
        END IF;
        v_json_fields := v_json_fields || quote_literal(v_col->>1) || ', ' || (v_col->>1);
    END LOOP;

    -- Build the JOIN clauses and GROUP BY clause
    v_include_result_row := build_includes1(v_entity_name, '', '', v_company_id);
    v_join_clause := v_join_clause || ' ' || v_include_result_row.join_clause;
    v_alias_mapping := v_alias_mapping || v_include_result_row.alias_mapping;
    v_group_by_clause := v_include_result_row.select_clause;

    -- Build the WHERE clause for column-specific search
    v_where_clause := v_where_clause || v_entity_table || '.company_id' || ' = ' || quote_literal(v_company_id);

    -- Handle the "search_all" conditions with AND
    FOR v_col_search IN SELECT * FROM jsonb_array_elements(v_search_all) LOOP
        -- Security check for column name and operator
        IF (v_col_search->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',]+$' THEN
            RAISE EXCEPTION 'Invalid characters in column name: %', (v_col_search->>'column_name');
        END IF;
        IF NOT v_valid_operators ? (v_col_search->>'operator') THEN
            RAISE EXCEPTION 'Invalid operator: %', (v_col_search->>'operator');
        END IF;
        -- Construct the condition
        IF (v_col_search->>'operator') = 'IN' OR (v_col_search->>'operator') = 'NOT IN' THEN
            v_where_clause := v_where_clause || ' AND ' || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_col_search->'value') x), ', ') || ')';
        ELSIF (v_col_search->>'operator') = 'BETWEEN' THEN
            v_where_clause := v_where_clause || ' AND ' || (v_col_search->>'column_name') || ' BETWEEN ' || quote_literal((v_col_search->'value')->>0) || ' AND ' || quote_literal((v_col_search->'value')->>1);
        ELSE
            IF (v_col_search->>'value') IS NOT NULL THEN 
                v_where_clause := v_where_clause || ' AND ' || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' ' || quote_literal((v_col_search->>'value'));
            ELSIF (v_col_search->>'operator' = 'IS' OR v_col_search->>'operator' = 'IS NOT') THEN
                v_where_clause := v_where_clause || ' AND ' || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' ' || 'NULL';
            END IF;
        END IF;
    END LOOP;

    -- Handle the "search_any" conditions with nested OR
    FOR v_col_search IN SELECT * FROM jsonb_array_elements(v_search_any) LOOP
        IF jsonb_typeof(v_col_search) = 'array' THEN
            -- Multiple sets of conditions
            v_or_group := '';
            FOR v_inner_col_search IN SELECT * FROM jsonb_array_elements(v_col_search) LOOP
                -- Security check for column name and operator
                IF (v_inner_col_search->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',]+$' THEN
                    RAISE EXCEPTION 'Invalid characters in column name: %', (v_inner_col_search->>'column_name');
                END IF;
                IF NOT v_valid_operators ? (v_inner_col_search->>'operator') THEN
                    RAISE EXCEPTION 'Invalid operator: %', (v_inner_col_search->>'operator');
                END IF;
                -- Construct the condition
                IF (v_inner_col_search->>'operator') = 'IN' OR (v_inner_col_search->>'operator') = 'NOT IN' THEN
                    v_or_group := v_or_group || (v_inner_col_search->>'column_name') || ' ' || (v_inner_col_search->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_inner_col_search->'value') x), ', ') || ') OR ';
                ELSIF (v_inner_col_search->>'operator') = 'BETWEEN' THEN
                    v_or_group := v_or_group || (v_inner_col_search->>'column_name') || ' BETWEEN ' || quote_literal((v_inner_col_search->'value')->>0) || ' AND ' || quote_literal((v_inner_col_search->'value')->>1) || ' OR ';
                ELSE
                    IF (v_inner_col_search->>'value') IS NOT NULL THEN 
                        v_or_group := v_or_group || (v_inner_col_search->>'column_name') || ' ' || (v_inner_col_search->>'operator') || ' ' || quote_literal((v_inner_col_search->>'value')) || ' OR ';
                    ELSIF (v_inner_col_search->>'operator' = 'IS' OR v_inner_col_search->>'operator' = 'IS NOT') THEN
                        v_or_group := v_or_group || (v_inner_col_search->>'column_name') || ' ' || (v_inner_col_search->>'operator') || ' ' || 'NULL' || ' OR ';
                    END IF;
                END IF;
            END LOOP;
            -- Remove the trailing ' OR ' and add to the OR clause
            IF v_or_group <> '' THEN
                v_or_group := LEFT(v_or_group, LENGTH(v_or_group) - 4);
                v_or_clause := v_or_clause || '(' || v_or_group || ')' || ' AND ';
            END IF;
        ELSE
            -- Single set of conditions
            -- Security check for column name and operator
            IF (v_col_search->>'column_name') !~ '^[a-zA-Z0-9_\. ()'',]+$' THEN
                RAISE EXCEPTION 'Invalid characters in column name: %', (v_col_search->>'column_name');
            END IF;
            IF NOT v_valid_operators ? (v_col_search->>'operator') THEN
                RAISE EXCEPTION 'Invalid operator: %', (v_col_search->>'operator');
            END IF;
            -- Construct the condition
            IF (v_col_search->>'operator') = 'IN' OR (v_col_search->>'operator') = 'NOT IN' THEN
                v_or_clause := v_or_clause || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' (' || array_to_string(ARRAY(SELECT quote_literal(trim(both '''' FROM x)) FROM jsonb_array_elements_text(v_col_search->'value') x), ', ') || ') OR ';
            ELSIF (v_col_search->>'operator') = 'BETWEEN' THEN
                v_or_clause := v_or_clause || (v_col_search->>'column_name') || ' BETWEEN ' || quote_literal((v_col_search->'value')->>0) || ' AND ' || quote_literal((v_col_search->'value')->>1) || ' OR ';
            ELSE
                IF (v_col_search->>'value') IS NOT NULL THEN 
                    v_or_clause := v_or_clause || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' ' || quote_literal((v_col_search->>'value')) || ' OR ';
                ELSIF (v_col_search->>'operator' = 'IS' OR v_col_search->>'operator' = 'IS NOT') THEN
                    v_or_clause := v_or_clause || (v_col_search->>'column_name') || ' ' || (v_col_search->>'operator') || ' ' || 'NULL' || ' OR ';
                END IF;
            END IF;
        END IF;
    END LOOP;

    -- Remove the trailing ' OR ' and ' AND ' and add to the where clause
    IF v_or_clause <> '' THEN
        v_or_clause := LEFT(v_or_clause, LENGTH(v_or_clause) - 4);
        v_where_clause := v_where_clause || ' AND (' || v_or_clause || ')';
    END IF;

    -- Initialize query string
    v_query_str := 'SELECT ' || v_select_clause || ' FROM ' || v_entity_table || v_join_clause || v_where_clause || v_group_by_clause;

    -- Total records count
    EXECUTE 'SELECT COUNT(*) FROM ' || v_entity_table || v_join_clause || v_where_clause INTO v_total_records_count;

    -- Order by and limit
    IF v_sort_columns IS NOT NULL THEN
        FOR v_sort_col IN SELECT * FROM jsonb_array_elements(v_sort_columns) LOOP
            -- Security check for column name
            IF (v_sort_col->>0) !~ '^[a-zA-Z0-9_\. ()'',]+$' THEN
                RAISE EXCEPTION 'Invalid characters in column name in sort: %', (v_sort_col->>0);
            END IF;
            IF v_query_str NOT LIKE '%ORDER BY%' THEN
                v_query_str := v_query_str || ' ORDER BY ';
            ELSE
                v_query_str := v_query_str || ', ';
            END IF;
            v_query_str := v_query_str || (v_sort_col->>0) || ' ' || (v_sort_col->>1);
        END LOOP;
    END IF;
    v_query_str := v_query_str || ' LIMIT ' || v_record_limit || ' OFFSET ' || v_record_offset;

    -- Store the executed query if print_query is true
    IF v_print_query THEN
        v_executed_query := v_query_str;
    END IF;

    -- Construct the JSON object manually to maintain the column order
    v_query_str := 'SELECT jsonb_agg(jsonb_build_object(' || v_json_fields || ')) FROM (' || v_query_str || ') t';

    -- Get the records
    EXECUTE v_query_str INTO v_records;

    -- Set the output result
    result := jsonb_build_object(
        'total_records_count', v_total_records_count,
        'records', v_records,
        'headers', v_headers,
        'query_executed', v_executed_query,
        'error_type', '',
        'error_message', '',
        'execution_status', TRUE
    );
EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions and set the result with the error message
        result := jsonb_build_object(
            'total_records_count', 0,
            'records', jsonb '[]',
            'query_executed', v_executed_query,
            'error_type', SQLSTATE,
            'error_message', SQLERRM,
            'execution_status', FALSE
        );
END;
$BODY$;


-- ALTER PROCEDURE public.get_grid_items(jsonb, jsonb)
--     OWNER TO postgres;

/*
DO $$
DECLARE
    input JSONB;
    output JSONB;
BEGIN
    input := '{
  "company_id": 1,
  "print_query": true,
  "entity_name": "user-grid",
  "start_index": 0,
  "limit_range": 15,
  "sort_columns": [
    [
      "users.id",
      "desc"
    ]
  ],
  "search_all": [
    {
      "column_name": "concat(user_details.first_name, '' '', user_details.last_name)",
      "value": "%admin",
      "operator": "ILIKE"
    },
    {
      "column_name": "user_details.id",
      "value": [2, 3],
      "operator": "BETWEEN"
    }
  ],
  "search_any": [
    {
      "column_name": "user_details.code",
      "value": "1000",
      "operator": "="
    },
    {
      "column_name": "users.id",
      "value": ["1", "2"],
      "operator": "IN"
    }
  ]
}';

    CALL public.get_grid_items(input, output);
    RAISE NOTICE 'Output for single record: %', output;
END $$;
*/
