-- View: public.user_project_details_loginhour

-- DROP VIEW public.user_project_details_loginhour;

CREATE OR REPLACE VIEW public.user_project_details_loginhour
 AS
 WITH user_projects AS (
         SELECT ud_1.user_id,
            pd.name AS project_name,
            -- sum((((split_part(tl.hours, ':'::text, 1) || ' hours '::text) || split_part(tl.hours, ':'::text, 2)) || ' minutes'::text)::interval) AS total_interval
        SUM(
            (
              ((split_part(COALESCE(tl.hours, '00:00'), ':', 1) || ' hours ')
               || split_part(COALESCE(tl.hours, '00:00'), ':', 2)) || ' minutes'
            )::interval
            +
            (
              ((split_part(COALESCE(tl.non_billing_hours, '00:00'), ':', 1) || ' hours ')
               || split_part(COALESCE(tl.non_billing_hours, '00:00'), ':', 2)) || ' minutes'
            )::interval
        ) AS total_interval
           FROM user_details ud_1
             LEFT JOIN time_logs tl ON ud_1.user_id = tl.user_id
             LEFT JOIN project_details pd ON tl.project_id = pd.id
          GROUP BY ud_1.user_id, pd.name
        ), user_log_bounds AS (
         SELECT tl.user_id,
            min(tl.logged_date) AS first_logged_date,
            max(tl.logged_date) AS last_logged_date
           FROM time_logs tl
          GROUP BY tl.user_id
        )
 SELECT ud.user_id,
    concat(ud.first_name, ' ', ud.last_name) AS full_name,
    ud.company_id,
    min(ulb.first_logged_date)::date AS logged_date_start,
    max(ulb.last_logged_date)::date AS logged_date_latest,
    to_char(COALESCE(sum(
        CASE
            WHEN up.project_name = 'BenchHours'::text THEN up.total_interval
            ELSE NULL::interval
        END), '00:00:00'::interval), 'HH24:MI'::text) AS bench_hours,
    to_char(COALESCE(sum(
        CASE
            WHEN up.project_name <> 'BenchHours'::text THEN up.total_interval
            ELSE NULL::interval
        END), '00:00:00'::interval), 'HH24:MI'::text) AS total_hours,
    count(DISTINCT
        CASE
            WHEN up.project_name IS NOT NULL AND up.project_name <> 'BenchHours'::text THEN up.project_name
            ELSE NULL::text
        END) AS worked_project,
    string_agg(
        CASE
            WHEN up.project_name IS NOT NULL AND up.project_name <> 'BenchHours'::text THEN ((up.project_name || ' ('::text) || to_char(up.total_interval, 'HH24:MI'::text)) || ')'::text
            ELSE NULL::text
        END, ', '::text ORDER BY up.project_name) AS other_projects,
    '1'::text AS status_id
   FROM user_details ud
     LEFT JOIN user_projects up ON ud.user_id = up.user_id
     LEFT JOIN user_log_bounds ulb ON ud.user_id = ulb.user_id
  GROUP BY ud.user_id, ud.first_name, ud.last_name, ud.company_id;

ALTER TABLE public.user_project_details_loginhour
    OWNER TO postgres;

