BEGIN;

/*
 * Use pgmemcache and memcached to increase the speed of org tree traversal
 * ------------------------------------------------------------------------
 *
 * This set of functions allows the use of memcached as a caching mechanism for
 * org tree traversal checks.  It is transparent and optional.  If memcache is
 * not set up, either by not running or the lack of the pgmemcache postgres
 * addon, then the default, existing behaviour is preserved and live database
 * queries are used to test all org tree traversals.
 *
 * This Evergreen addon extention requires the pgmemcache-perm_cache.sql to be
 * installed as well.  See that extention script for details on pgmemcache
 * setup and installation.
 *
 * TODO: Make the cache timeout configurable via a global setting for EG 2.0
 *
 */


CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
    SELECT  *
      FROM  actor.org_unit_descendants(
                CASE WHEN $2 IS NOT NULL THEN
                    (actor.org_unit_ancestor_at_depth($1,$2)).id
                ELSE 
                    $1
                END
    );
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION actor.noncached_org_unit_descendants ( org INT ) RETURNS SETOF actor.org_unit AS $$
DECLARE
    kid             actor.org_unit%ROWTYPE;
    curr_org        actor.org_unit%ROWTYPE;
BEGIN

    SELECT * INTO curr_org FROM actor.org_unit WHERE id = org;
    RETURN NEXT curr_org;

    FOR kid IN SELECT * FROM actor.org_unit WHERE parent_ou = org LOOP
        FOR curr_org IN SELECT * FROM actor.noncached_org_unit_descendants(kid.id) LOOP
            RETURN NEXT curr_org;
        END LOOP;
    END LOOP;

    RETURN;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( org INT ) RETURNS SETOF actor.org_unit AS $func$
DECLARE
    kid             actor.org_unit%ROWTYPE;
    curr_org        actor.org_unit%ROWTYPE;
    idlist          INT[] := '{}'::INT[];
    cached_value    RECORD;
BEGIN

    IF org IS NULL THEN
        RETURN;
    END IF;

    IF permission.mc_init() THEN
        -- RAISE NOTICE 'Getting perm from cache';
        EXECUTE $$SELECT memcache_get('oils_orgcache_$$ || org || $$') AS x;$$ INTO cached_value;

        IF cached_value.x IS NOT NULL AND cached_value.x <> '' THEN
            FOR curr_org IN
                SELECT  *
                  FROM  actor.org_unit
                  WHERE id IN ( SELECT * FROM explode_array( STRING_TO_ARRAY( cached_value.x, ',' ) ) )
            LOOP
                RETURN NEXT curr_org;
            END LOOP;
    
            RETURN;
        END IF;

    END IF;

    SELECT * INTO curr_org FROM actor.org_unit WHERE id = org;
    RETURN NEXT curr_org;

    idlist := ARRAY_APPEND( idlist, curr_org.id );

    FOR kid IN SELECT * FROM actor.org_unit WHERE parent_ou = org LOOP
        FOR curr_org IN SELECT * FROM actor.noncached_org_unit_descendants(kid.id) LOOP
            RETURN NEXT curr_org;
            idlist := ARRAY_APPEND( idlist, curr_org.id );
        END LOOP;
    END LOOP;

    IF permission.mc_init() THEN
        EXECUTE $$
            SELECT memcache_set(
                'oils_orgcache_$$ || org || $$',
                $$ || QUOTE_LITERAL(ARRAY_TO_STRING(idlist,',')) || $$,
                '10 minutes'::INTERVAL
            );
        $$;
    END IF;

    RETURN;
END;
$func$ LANGUAGE PLPGSQL;

COMMIT;

