CREATE VIEW vwknowledgecategory AS (
 SELECT cat.id AS category_id,
    cat.name AS category_name,
    cat.description AS category_description,
    cat.externalid AS external_id,
    cat.datecreated AS date_created,
    cat.datemodified AS date_modified,
    cat.documentcount AS document_count,
    cat.updated AS last_updated,
    cat.parentcategoryid AS parent_category_id,
    cat.parentcategoryname AS parent_category_name,
    pcat.description AS parent_category_description,
    cat.knowledgebaseid AS knowledge_base_id,
    kb.name AS knowledge_base_name,
    kb.description AS knowledge_base_description,
    kb.corelanguage AS knowledge_base_language,
    kb.published AS knowledge_base_published,
    kb.faqcount AS knowledge_base_faq_count,
    kb.articlecount AS knowledge_base_article_count,
        CASE
            WHEN (cat.parentcategoryid IS NULL) THEN 'Root Category'::text
            WHEN (pcat.parentcategoryid IS NULL) THEN 'Level 1'::text
            ELSE 'Level 2+'::text
        END AS category_level,
        CASE
            WHEN (cat.parentcategoryname IS NOT NULL) THEN ((((cat.parentcategoryname)::text || ' > '::text) || (cat.name)::text))::character varying
            ELSE cat.name
        END AS category_path,
    COALESCE(doc_stats.total_documents, (0)::bigint) AS total_documents,
    COALESCE(doc_stats.published_documents, (0)::bigint) AS published_documents,
    COALESCE(doc_stats.draft_documents, (0)::bigint) AS draft_documents,
    COALESCE(doc_stats.visible_documents, (0)::bigint) AS visible_documents,
    COALESCE(feedback_stats.total_feedback, (0)::bigint) AS total_feedback,
    COALESCE(feedback_stats.positive_feedback, (0)::bigint) AS positive_feedback,
    COALESCE(feedback_stats.negative_feedback, (0)::bigint) AS negative_feedback,
        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,
    COALESCE(child_counts.child_category_count, (0)::bigint) AS child_category_count
   FROM (((((knowledgebasecategorydata cat
     LEFT JOIN knowledgebase kb ON (((cat.knowledgebaseid)::text = (kb.id)::text)))
     LEFT JOIN knowledgebasecategorydata pcat ON (((cat.parentcategoryid)::text = (pcat.id)::text)))
     LEFT JOIN ( SELECT knowledgebasedocument.categoryid,
            count(*) AS total_documents,
            sum(
                CASE
                    WHEN ((knowledgebasedocument.state)::text = 'Published'::text) THEN 1
                    ELSE 0
                END) AS published_documents,
            sum(
                CASE
                    WHEN ((knowledgebasedocument.state)::text = 'Draft'::text) THEN 1
                    ELSE 0
                END) AS draft_documents,
            sum(
                CASE
                    WHEN (knowledgebasedocument.visible = true) THEN 1
                    ELSE 0
                END) AS visible_documents
           FROM knowledgebasedocument
          GROUP BY knowledgebasedocument.categoryid) doc_stats ON (((cat.id)::text = (doc_stats.categoryid)::text)))
     LEFT JOIN ( SELECT doc.categoryid,
            count(*) AS total_feedback,
            sum(
                CASE
                    WHEN ((fb.rating)::text = 'Positive'::text) THEN 1
                    ELSE 0
                END) AS positive_feedback,
            sum(
                CASE
                    WHEN ((fb.rating)::text = 'Negative'::text) THEN 1
                    ELSE 0
                END) AS negative_feedback
           FROM (knowledgedocumentfeedbackdata fb
             JOIN knowledgebasedocument doc ON (((fb.documentid)::text = (doc.id)::text)))
          GROUP BY doc.categoryid) feedback_stats ON (((cat.id)::text = (feedback_stats.categoryid)::text)))
     LEFT JOIN ( SELECT knowledgebasecategorydata.parentcategoryid,
            count(*) AS child_category_count
           FROM knowledgebasecategorydata
          WHERE (knowledgebasecategorydata.parentcategoryid IS NOT NULL)
          GROUP BY knowledgebasecategorydata.parentcategoryid) child_counts ON (((cat.id)::text = (child_counts.parentcategoryid)::text)))
)