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

CREATE OR REPLACE FUNCTION PUBLIC.efficiency_department(IN p_user_id INTEGER, IN p_date_start VARCHAR, IN p_date_end VARCHAR)
    RETURNS TABLE(
        department_id               INTEGER,
        department_name             VARCHAR,
        NEW                         BIGINT,
        OPEN                        BIGINT,
        closed                      BIGINT,
        pending                     BIGINT,
        transfer                    BIGINT,
        backlog                     BIGINT
    ) 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 last_movement_without_timer AS(
        SELECT
            ttt.department_id,
            ttt.ticket_id,
            MAX(ttt.created_at) AS last_movement_without_timer
        FROM tickets_time_travel ttt
        WHERE ttt.department_id IS NOT NULL
          AND ttt.created_at::DATE >= '''|| p_date_start ||'''::DATE
          AND ttt.created_at::DATE <= '''|| p_date_end ||'''::DATE
        GROUP BY ttt.department_id, ttt.ticket_id
    ), last_movement_with_timer AS(
        SELECT
            ttt.department_id,
            ttt.ticket_id,
            MAX(ttt.created_at) AS last_movement_with_timer
        FROM tickets_time_travel ttt
        WHERE ttt.department_id IS NOT NULL
          AND ttt.timer_id IS NOT NULL
          AND ttt.created_at::DATE >= '''|| p_date_start ||'''::DATE
          AND ttt.created_at::DATE <= '''|| p_date_end ||'''::DATE
        GROUP BY ttt.department_id, ttt.ticket_id
    ), all_new_tickets AS(
        SELECT
            t.department_id,
            COUNT(t.id) AS new
        FROM tickets t
        WHERE t.department_id IS NOT NULL
          AND t.created_at::DATE >= '''|| p_date_start ||'''::DATE
          AND t.created_at::DATE <= '''|| p_date_end ||'''::DATE
          GROUP BY t.department_id
    ), all_open_tickets AS(
        SELECT
            lmwt.department_id,
            COUNT(lmwt.*) AS open
        FROM last_movement_with_timer lmwt
        GROUP BY lmwt.department_id
    ), all_closed_tickets AS(
        SELECT
            ttt.department_id,
            COUNT(ts.ticket_state_type_id) AS closed
        FROM last_movement_without_timer lmwot
        INNER JOIN tickets_time_travel ttt ON lmwot.department_id = ttt.department_id AND lmwot.last_movement_without_timer = ttt.created_at
        LEFT JOIN ticket_states ts ON ttt.ticket_state_id = ts.id
        WHERE ts.ticket_state_type_id = 3
        GROUP BY ttt.department_id
    ), last_movement_with_timer_before AS(
        SELECT
            ttt.department_id,
            ttt.ticket_id,
            MAX(ttt.created_at)
        FROM tickets_time_travel ttt
        LEFT JOIN last_movement_with_timer lmwt ON ttt.department_id = lmwt.department_id AND ttt.ticket_id = lmwt.ticket_id
        WHERE ttt.created_at::DATE < '''|| p_date_start ||'''::DATE
          AND ttt.timer_id IS NULL
          AND lmwt.ticket_id IS NULL
        GROUP BY ttt.department_id, ttt.ticket_id
    ), all_pending_tickets AS(
        SELECT
            lmwtb.department_id,
            COUNT(lmwtb.*) AS pending
        FROM last_movement_with_timer_before lmwtb
        GROUP BY lmwtb.department_id
    ), all_transfer_tickets AS(
        SELECT
            ttt.previous_department_id AS department_id,
            COUNT(ttt.*) AS transfer
        FROM tickets_time_travel ttt
        WHERE ttt.previous_department_id IS NOT NULL
          AND ttt.created_at::DATE >= '''|| p_date_start ||'''::DATE
          AND ttt.created_at::DATE <= '''|| p_date_end ||'''::DATE
        GROUP BY ttt.previous_department_id
    )
    SELECT
        d.id AS department_id,
        d.department_name,
        COALESCE(ant.new, 0) AS new,
        COALESCE(aot.open, 0) AS open,
        COALESCE(act.closed, 0) AS closed,
        COALESCE(apt.pending, 0) AS pending,
        COALESCE(att.transfer, 0) AS pending,
        (COALESCE(ant.new, 0) + COALESCE(aot.open, 0) + COALESCE(apt.pending, 0) - COALESCE(act.closed, 0)) AS backlog
    FROM departments d
    LEFT JOIN all_new_tickets ant ON d.id = ant.department_id
    LEFT JOIN all_open_tickets aot ON d.id = aot.department_id
    LEFT JOIN all_closed_tickets act ON d.id = act.department_id
    LEFT JOIN all_pending_tickets apt ON d.id = apt.department_id
    LEFT JOIN all_transfer_tickets att ON d.id = att.department_id
    WHERE d.id IN('|| array_to_string(departments, ',') ||')';

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