CREATE OR REPLACE VIEW user_project_hours AS
SELECT
	upl.user_id AS user_id,
    upl.full_name AS name,
    pd.name AS project_name,
    upl.status_id AS status_id,
	upl.company_id AS company_id,
    SUM(
        SPLIT_PART(tl.hours, ':', 1)::INT * 60
      + SPLIT_PART(tl.hours, ':', 2)::INT
    ) AS total_billing_minutes,
    SUM(
        SPLIT_PART(tl.non_billing_hours, ':', 1)::INT * 60
      + SPLIT_PART(tl.non_billing_hours, ':', 2)::INT
    ) AS total_non_billing_minutes
FROM user_project_details_loginhour upl
INNER JOIN time_logs tl
    ON upl.user_id = tl.user_id
INNER JOIN project_details pd
    ON tl.project_id = pd.id
WHERE upl.company_id = '1'
  AND upl.status_id != '3'
  AND pd.name != 'BenchHours'
GROUP BY
    upl.full_name,
    pd.name,
	upl.user_id,
	upl.company_id,
    upl.status_id;
