/*
 * This file is part of vospace-file-catalog
 * Copyright (C) 2021 Istituto Nazionale di Astrofisica
 * SPDX-License-Identifier: GPL-3.0-or-later
 */

-- This function returns an array adding some elements and removing others
-- It is used to update recursively groupread and groupwrite properties

CREATE OR REPLACE FUNCTION update_array(input_array VARCHAR[], elements_to_add VARCHAR[], elements_to_remove VARCHAR[]) RETURNS VARCHAR[] AS
$func$
SELECT array_agg(elements) FROM (
    SELECT DISTINCT UNNEST(input_array)
    UNION SELECT DISTINCT UNNEST(elements_to_add)
    EXCEPT SELECT DISTINCT UNNEST(elements_to_remove)
) AS output_values(elements)
$func$ LANGUAGE sql;

SELECT update_array('{"group1","group2"}'::VARCHAR[],'{"group2","group3"}'::VARCHAR[],'{"group1"}'::VARCHAR[]) = '{"group2","group3"}' AS test_passed;

-- Returns node_id of a given vos_path. Setting cost to 1 tells the planner to always execute this function before other clauses (100 is the default function cost).

CREATE OR REPLACE FUNCTION id_from_vos_path(vos_path_input VARCHAR) RETURNS BIGINT IMMUTABLE COST 1 AS
$func$
WITH RECURSIVE r_vos_path AS (
    SELECT node_id, path, 1 AS level, STRING_TO_ARRAY(vos_path_input, '/') AS parts, (CASE WHEN vos_path_input = '/' THEN '/' ELSE '' END) AS vos_path
    FROM node
    WHERE name = ''
  UNION ALL
    SELECT n.node_id, n.path, p.level + 1, p.parts, p.vos_path || '/' || p.parts[p.level + 1]
    FROM node n
    JOIN r_vos_path p ON p.path = n.parent_path
    WHERE n.name = p.parts[p.level + 1]
)
SELECT node_id FROM r_vos_path WHERE vos_path = vos_path_input
$func$ LANGUAGE sql;

-- Returns vos_path from node_id

CREATE OR REPLACE FUNCTION get_vos_path(input_id BIGINT) RETURNS VARCHAR AS
$func$
SELECT string_agg(name, '/') FROM (
    WITH RECURSIVE paths AS (
            SELECT node_id, name, path, parent_path, 1 AS level
            FROM node
            WHERE node_id = input_id
        UNION ALL
            SELECT f.node_id, f.name, f.path, f.parent_path, p.level + 1
            FROM node f
            INNER JOIN paths p ON p.parent_path = f.path
    ) SELECT name FROM paths ORDER BY LEVEL DESC
) AS names
$func$ LANGUAGE sql;

-- Returns os_path from node_id

CREATE OR REPLACE FUNCTION get_os_path(input_id BIGINT) RETURNS VARCHAR AS
$func$
SELECT '/' || string_agg(name, '/') FROM (
    WITH RECURSIVE paths AS (
            SELECT node_id, COALESCE(os_name, name) AS name, relative_path, parent_relative_path, 1 AS level
            FROM node
            WHERE node_id = input_id
        UNION ALL
            SELECT f.node_id, COALESCE(f.os_name, f.name) AS name, f.relative_path, f.parent_relative_path, p.level + 1
            FROM node f
            INNER JOIN paths p ON p.parent_relative_path = f.relative_path
            WHERE f.parent_relative_path IS NOT NULL
    ) SELECT name FROM paths ORDER BY LEVEL DESC
) AS names
$func$ LANGUAGE sql;

-- Trigger function that automatically updates start time and end time of jobs at phase update.
-- This could be performed by business logic of each application that handles jobs but relying directly on the database ensures a better uniformity.

CREATE OR REPLACE FUNCTION job_phase_inserted() RETURNS TRIGGER 
AS 
$func$
BEGIN
    IF new.phase = 'EXECUTING' THEN
        new.start_time := NOW();
    ELSIF new.phase = 'COMPLETED' OR new.phase = 'ERROR' THEN
        new.start_time := NOW();
        new.end_time := NOW();
    END IF;
    RETURN new;
END;
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION job_phase_updated() RETURNS TRIGGER 
AS 
$func$
BEGIN
    IF new.phase != old.phase THEN
        IF new.phase = 'EXECUTING' THEN
            new.start_time := NOW();
        ELSIF new.phase = 'COMPLETED' OR new.phase = 'ERROR' OR new.phase = 'ABORTED' THEN
            new.end_time := NOW();
        END IF;
    END IF;
    RETURN new;
END;
$func$ LANGUAGE plpgsql;

CREATE TRIGGER job_insert
    BEFORE INSERT ON job
    FOR EACH ROW
    EXECUTE PROCEDURE job_phase_inserted();

CREATE TRIGGER job_update
    BEFORE UPDATE OF phase ON job
    FOR EACH ROW
    EXECUTE PROCEDURE job_phase_updated();