CREATE VIEW vwlearninguserassignmentsummary AS (
 SELECT u.id AS user_id,
    u.name AS user_name,
    u.email AS user_email,
    u.department AS user_department,
    lma.id AS assignment_id,
    lma.moduleid AS module_id,
    lm.name AS module_name,
    lm.description AS module_description,
    lma.state AS assignment_state,
    lma.isoverdue AS is_overdue,
    lma.dateassigned AS date_assigned,
    lma.datedue AS date_due,
    lma.daterecommendedforcompletion AS date_recommended_completion,
    lar.id AS result_id,
    lar.assessmentpercentagescore AS assessment_score,
    lar.completionpercentage AS completion_percentage,
    lar.passpercent AS pass_percentage,
    lar.lengthinminutes AS duration_minutes,
    lar.datecreated AS result_date_created,
    lar.datemodified AS result_date_modified,
        CASE
            WHEN (lar.id IS NOT NULL) THEN 'Completed'::text
            WHEN (lma.isoverdue = true) THEN 'Overdue'::text
            WHEN ((lma.state)::text = 'Assigned'::text) THEN 'Assigned'::text
            ELSE 'Unknown'::text
        END AS completion_status,
        CASE
            WHEN ((lar.assessmentpercentagescore IS NOT NULL) AND (lar.passpercent IS NOT NULL)) THEN
            CASE
                WHEN (lar.assessmentpercentagescore >= lar.passpercent) THEN 'Passed'::text
                ELSE 'Failed'::text
            END
            ELSE NULL::text
        END AS pass_status
   FROM (((userdetails u
     LEFT JOIN learningmoduleassignments lma ON (((u.id)::text = (lma.userid)::text)))
     LEFT JOIN learningmodules lm ON (((lma.moduleid)::text = (lm.id)::text)))
     LEFT JOIN learningassignmentresults lar ON ((((lma.userid)::text = (lar.userid)::text) AND ((lma.moduleid)::text = (lar.moduleid)::text))))
  WHERE ((u.state)::text <> 'deleted'::text)
  ORDER BY u.name, lm.name
)