-- drop cascade type section_list_type;

/**
 * Section structure
 */
CREATE TYPE section_list_type AS (
    id INTEGER,
    parent_id INTEGER,
    name character varying(1024),
    owner_id INTEGER,
    GROUP_ID INTEGER,
    created TIMESTAMP without TIME ZONE,
    changed TIMESTAMP without TIME ZONE,
    group_permisssion SMALLINT,
    other_permissions SMALLINT,
    is_section BOOLEAN
);

/**
 * Select all sections in selected section
 */
CREATE OR REPLACE FUNCTION select_sections_in_section(
    IN INTEGER
) returns setof section_list_type AS $$
    SELECT id, parent_id, GROUP_ID, name, owner_id, created, changed, group_permissions, other_permissions, TRUE FROM sections WHERE parent_id = $1;
$$ language SQL;

/**
 * Select all positions int selected section
 */
CREATE OR REPLACE FUNCTION select_positions_in_section(
    IN INTEGER
) returns setof section_list_type AS $$
    SELECT id, parent_id, GROUP_ID, name, owner_id, created, changed, group_permissions, other_permissions, TRUE FROM positions WHERE parent_id = $1;
$$ language SQL;

/**
 * Select all allowed positions & sections with permissions checking
 */
CREATE OR REPLACE FUNCTION select_section_content(
    IN INTEGER, -- section id
    IN INTEGER, -- current user id
    IN INTEGER  -- current group id
) returns setof section_list_type AS $$
DECLARE
    rec section_list_type%ROWTYPE;
    current_section RECORD;
BEGIN
    -- check current section permissions
    SELECT INTO current_section id, parent_id, GROUP_ID, name, owner_id, created, changed, group_permissions, other_permissions FROM sections WHERE id = $1;
    IF ((current_section.owner_id == $2) OR (current_section.GROUP_ID == $3 AND current_section.group_permissions > 0) OR (current_section.other_permissions > 0)) THEN
        -- sections
        FOR rec IN SELECT * FROM select_sections_in_section($1) LOOP
            RETURN next rec;
        END LOOP;

        -- positions
        FOR rec IN SELECT * FROM select_positions_in_section($1) LOOP
            IF ((rec.owner_id == $2) OR (rec.GROUP_ID == $3 AND rec.group_permissions > 0) OR (rec.other_permissions > 0)) THEN -- check permissions
                RETURN next rec;
            END IF;
        END LOOP;
    END IF;
    RETURN;
END;
$$ language plpgsql;


SELECT * FROM select_section_content(0);
-- select id, parent_id, name, owner_id, created, changed, group_permissions, other_permissions, true from sections;