CREATE VIEW vwknowledgebase AS (
 SELECT kb.id,
    kb.name,
    kb.description,
    kb.corelanguage AS core_language,
    kb.datecreated AS date_created,
    kb.datemodified AS date_modified,
    kb.datedocumentlastmodified AS date_document_last_modified,
    kb.faqcount AS faq_count,
    kb.articlecount AS article_count,
    kb.published,
    kb.updated AS last_updated,
    COALESCE(doc_stats.total_documents, (0)::bigint) AS total_documents,
    COALESCE(doc_stats.published_documents, (0)::bigint) AS published_documents,
    COALESCE(doc_stats.visible_documents, (0)::bigint) AS visible_documents,
    COALESCE(cat_stats.total_categories, (0)::bigint) AS total_categories,
    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,
    COALESCE(usage_stats.total_documents_surfaced, (0)::bigint) AS total_documents_surfaced,
    COALESCE(usage_stats.unique_users, (0)::bigint) AS unique_users_count
   FROM ((((knowledgebase kb
     LEFT JOIN ( SELECT knowledgebasedocument.knowledgebaseid,
            count(*) AS total_documents,
            sum(
                CASE
                    WHEN ((knowledgebasedocument.state)::text = 'Published'::text) THEN 1
                    ELSE 0
                END) AS published_documents,
            sum(
                CASE
                    WHEN (knowledgebasedocument.visible = true) THEN 1
                    ELSE 0
                END) AS visible_documents
           FROM knowledgebasedocument
          GROUP BY knowledgebasedocument.knowledgebaseid) doc_stats ON (((kb.id)::text = (doc_stats.knowledgebaseid)::text)))
     LEFT JOIN ( SELECT knowledgebasecategorydata.knowledgebaseid,
            count(*) AS total_categories
           FROM knowledgebasecategorydata
          GROUP BY knowledgebasecategorydata.knowledgebaseid) cat_stats ON (((kb.id)::text = (cat_stats.knowledgebaseid)::text)))
     LEFT JOIN ( SELECT knowledgedocumentfeedbackdata.knowledgebaseid,
            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.knowledgebaseid) feedback_stats ON (((kb.id)::text = (feedback_stats.knowledgebaseid)::text)))
     LEFT JOIN ( SELECT knowledgeaggregatesdata.knowledgebaseid,
            sum(knowledgeaggregatesdata.nknowledgedocumentsurfaced) AS total_documents_surfaced,
            count(DISTINCT knowledgeaggregatesdata.userid) AS unique_users
           FROM knowledgeaggregatesdata
          GROUP BY knowledgeaggregatesdata.knowledgebaseid) usage_stats ON (((kb.id)::text = (usage_stats.knowledgebaseid)::text)))
)