CREATE VIEW vwknowledgedocument AS (
 SELECT doc.id,
    doc.title,
    doc.visible,
    doc.state,
    doc.datecreated AS date_created,
    doc.datemodified AS date_modified,
    doc.dateimported AS date_imported,
    doc.datepublished AS date_published,
    doc.lastpublishedversionnumber AS last_published_version,
    doc.documentversion AS document_version,
    doc.externalid AS external_id,
    doc.readonly AS read_only,
    doc.updated AS last_updated,
    doc.knowledgebaseid AS knowledge_base_id,
    kb.name AS knowledge_base_name,
    kb.corelanguage AS knowledge_base_language,
    kb.published AS knowledge_base_published,
    doc.categoryid AS category_id,
    cat.name AS category_name,
    cat.description AS category_description,
    cat.parentcategoryid AS parent_category_id,
    cat.parentcategoryname AS parent_category_name,
    COALESCE(feedback_stats.total_feedback, (0)::bigint) AS total_feedback_count,
    COALESCE(feedback_stats.positive_feedback, (0)::bigint) AS positive_feedback_count,
    COALESCE(feedback_stats.negative_feedback, (0)::bigint) AS negative_feedback_count,
        CASE
            WHEN (COALESCE(feedback_stats.total_feedback, (0)::bigint) > 0) THEN round((((feedback_stats.positive_feedback)::numeric * 100.0) / (feedback_stats.total_feedback)::numeric), 2)
            ELSE NULL::numeric
        END AS positive_feedback_percentage,
        CASE doc.state
            WHEN 'Draft'::text THEN 'In Development'::character varying
            WHEN 'Published'::text THEN 'Live'::character varying
            WHEN 'Archived'::text THEN 'Retired'::character varying
            ELSE doc.state
        END AS state_description,
        CASE
            WHEN ((doc.visible = true) AND ((doc.state)::text = 'Published'::text)) THEN 'Visible & Published'::text
            WHEN (doc.visible = true) THEN 'Visible (Not Published)'::text
            WHEN ((doc.state)::text = 'Published'::text) THEN 'Hidden (Published)'::text
            ELSE 'Hidden'::text
        END AS visibility_status
   FROM (((knowledgebasedocument doc
     LEFT JOIN knowledgebase kb ON (((doc.knowledgebaseid)::text = (kb.id)::text)))
     LEFT JOIN knowledgebasecategorydata cat ON (((doc.categoryid)::text = (cat.id)::text)))
     LEFT JOIN ( SELECT knowledgedocumentfeedbackdata.documentid,
            count(*) AS total_feedback,
            sum(
                CASE
                    WHEN ((knowledgedocumentfeedbackdata.rating)::text = 'Positive'::text) THEN 1
                    ELSE 0
                END) AS positive_feedback,
            sum(
                CASE
                    WHEN ((knowledgedocumentfeedbackdata.rating)::text = 'Negative'::text) THEN 1
                    ELSE 0
                END) AS negative_feedback
           FROM knowledgedocumentfeedbackdata
          GROUP BY knowledgedocumentfeedbackdata.documentid) feedback_stats ON (((doc.id)::text = (feedback_stats.documentid)::text)))
)