DROP FUNCTION IF EXISTS PUBLIC.efficiency_engineers(INTEGER, VARCHAR, VARCHAR);

CREATE OR REPLACE FUNCTION PUBLIC.efficiency_engineers(IN p_user_id INTEGER, IN p_date_start VARCHAR, IN p_date_end VARCHAR)
    RETURNS TABLE(
        department_id               INTEGER,
        department_name             VARCHAR,
        assigned_to_id              INTEGER,
        first_name                  VARCHAR,
        last_name                   VARCHAR,
        NEW                         INTEGER,
        OPEN                        INTEGER,
        closed                      INTEGER,
        pending                     INTEGER,
        reasigned                   INTEGER,
        backlog                     INTEGER
    ) AS
$BODY$
DECLARE
    query                           TEXT := '';
    departments                     INTEGER[];
BEGIN
    SELECT ARRAY(SELECT ud.department_id FROM user_departments(p_user_id) ud) INTO departments;
    IF array_length(departments, 1) = 0 THEN
        RAISE EXCEPTION 'El usuario no está asociado a ningún departamento'
             USING HINT = 'Por favor modifique el usuario';
    END IF;

    query := '
    WITH counters AS(
        SELECT
            cl.department_id,
            cl.user_id,
            get_ef_new_tickets(cl.department_id, cl.user_id, ''' || p_date_start || ''', '''|| p_date_end ||''') AS new,
            get_ef_open_tickets(cl.department_id, cl.user_id, ''' || p_date_start || ''', '''|| p_date_end ||''') AS open,
            get_ef_closed_tickets(cl.department_id, cl.user_id, ''' || p_date_start || ''', '''|| p_date_end ||''') AS closed,
            get_ef_pending_tickets(cl.department_id, cl.user_id, ''' || p_date_start || ''') AS pending,
            get_ef_reasigned_tickets(cl.department_id, cl.user_id, ''' || p_date_start || ''', '''|| p_date_end ||''') AS reasigned
        FROM coworkers_list cl
        WHERE department_id IS NOT NULL
    )
    SELECT
        d.id AS department_id,
        d.department_name,
        c.user_id,
        au.first_name,
        au.last_name,
        c.new,
        (c.open - c.closed) AS open,
        c.closed,
        c.pending,
        c.reasigned,
        (c.new - c.closed + c.pending) AS backlog
    FROM counters c
    LEFT JOIN departments d ON c.department_id = d.id
    LEFT JOIN auth_user au ON c.user_id = au.id
    WHERE c.department_id IS NOT NULL
    ORDER BY c.department_id;';

    RETURN QUERY EXECUTE query;
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE STRICT SECURITY DEFINER COST 10 ROWS 1000;