DROP VIEW "public"."qna_answer_view";--> statement-breakpoint
DROP VIEW "public"."qna_comment_view";--> statement-breakpoint
DROP VIEW "public"."qna_view";--> statement-breakpoint
CREATE VIEW "public"."qna_answer_view" AS (select "qna_answer"."id", "qna_answer"."qna_id", "qna_answer"."content", "qna_answer"."author", "qna_answer"."created_at" as "created_at", "qna_answer"."updated_at" as "updated_at", "qna_answer"."is_deleted" as "is_deleted", "qna_answer"."deleted_at" as "deleted_at", "qna"."title" as "question_title", "qna"."category" as "question_category", "qna"."author" as "question_author", "qna"."created_at" as "question_created_at", "users"."name" as "author_name", "users"."email" as "author_email", "users"."domain" as "author_domain", "users"."phone" as "author_phone", "users"."image_url" as "author_image_url", "users"."language" as "author_language", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "tech_vendors"."vendor_name" as "tech_vendor_name", "tech_vendors"."vendor_code" as "tech_vendor_code", COALESCE("vendors"."vendor_name", "tech_vendors"."vendor_name") as "company_name", COALESCE("vendors"."vendor_code", "tech_vendors"."vendor_code") as "company_code", 
        CASE 
          WHEN "vendors"."vendor_name" IS NOT NULL THEN 'vendor'
          WHEN "tech_vendors"."vendor_name" IS NOT NULL THEN 'techVendor'
          ELSE NULL
        END
       as "vendor_type", (
        SELECT COUNT(*)::int
        FROM "qna_comments" qc
        WHERE qc.answer_id = "qna_answer"."id"
        AND qc.is_deleted = false
      ) as "total_comments", (
        SELECT COUNT(*)::int
        FROM "qna_comments" qc
        WHERE qc.answer_id = "qna_answer"."id"
        AND qc.is_deleted = false
      ) as "comment_count", (
        SELECT COUNT(*)::int
        FROM "qna_comments" qc
        WHERE qc.answer_id = "qna_answer"."id"
        AND qc.parent_comment_id IS NULL
        AND qc.is_deleted = false
      ) as "parent_comments_count", (
        SELECT COUNT(*)::int
        FROM "qna_comments" qc
        WHERE qc.answer_id = "qna_answer"."id"
        AND qc.parent_comment_id IS NOT NULL
        AND qc.is_deleted = false
      ) as "child_comments_count", (
        SELECT MAX(qc.created_at)
        FROM "qna_comments" qc
        WHERE qc.answer_id = "qna_answer"."id"
        AND qc.is_deleted = false
      ) as "last_commented_at", (
        SELECT ROW_NUMBER() OVER (
          PARTITION BY qa2.qna_id 
          ORDER BY qa2.created_at ASC
        )
        FROM "qna_answer" qa2
        WHERE qa2.id = "qna_answer"."id"
        AND qa2.is_deleted = false
      ) as "answer_order", (
        "qna_answer"."id" = (
          SELECT qa2.id
          FROM "qna_answer" qa2
          WHERE qa2.qna_id = "qna_answer"."qna_id"
          AND qa2.is_deleted = false
          ORDER BY qa2.created_at ASC
          LIMIT 1
        )
      ) as "is_first_answer", (
        "qna_answer"."id" = (
          SELECT qa2.id
          FROM "qna_answer" qa2
          WHERE qa2.qna_id = "qna_answer"."qna_id"
          AND qa2.is_deleted = false
          ORDER BY qa2.created_at DESC
          LIMIT 1
        )
      ) as "is_latest_answer" from "qna_answer" left join "qna" on "qna_answer"."qna_id" = "qna"."id" left join "users" on "qna_answer"."author" = "users"."id" left join "vendors" on "users"."company_id" = "vendors"."id" left join "tech_vendors" on "users"."tech_company_id" = "tech_vendors"."id" where "qna_answer"."is_deleted" = false order by "qna_answer"."created_at");--> statement-breakpoint

CREATE VIEW "public"."qna_comment_view" AS (select "qna_comments"."id", "qna_comments"."content", "qna_comments"."author", "qna_comments"."answer_id", "qna_comments"."parent_comment_id", "qna_comments"."created_at" as "created_at", "qna_comments"."updated_at" as "updated_at", "qna_comments"."is_deleted" as "is_deleted", "qna_comments"."deleted_at" as "deleted_at", "qna_answer"."content" as "answer_content", "qna_answer"."author" as "answer_author", "qna_answer"."created_at" as "answer_created_at", "qna_answer"."qna_id" as "qna_id", "qna"."title" as "question_title", "qna"."category" as "question_category", "qna"."author" as "question_author", "users"."name" as "author_name", "users"."email" as "author_email", "users"."domain" as "author_domain", "users"."image_url" as "author_image_url", "vendors"."vendor_name" as "vendor_name", "tech_vendors"."vendor_name" as "tech_vendor_name", COALESCE("vendors"."vendor_name", "tech_vendors"."vendor_name") as "company_name", 
        CASE 
          WHEN "vendors"."vendor_name" IS NOT NULL THEN 'vendor'
          WHEN "tech_vendors"."vendor_name" IS NOT NULL THEN 'techVendor'
          ELSE NULL
        END
       as "vendor_type", "qna_comments"."parent_comment_id" IS NULL as "is_parent_comment", "qna_comments"."parent_comment_id" IS NOT NULL as "is_child_comment", (
        SELECT COUNT(*)::int
        FROM "qna_comments" qc2
        WHERE qc2.parent_comment_id = "qna_comments"."id"
        AND qc2.is_deleted = false
      ) as "child_comments_count", (
        SELECT COUNT(*) > 0
        FROM "qna_comments" qc2
        WHERE qc2.parent_comment_id = "qna_comments"."id"
        AND qc2.is_deleted = false
      ) as "has_child_comments", 
        CASE 
          WHEN "qna_comments"."parent_comment_id" IS NULL THEN 0
          ELSE 1
        END
       as "comment_depth", (
        SELECT ROW_NUMBER() OVER (
          PARTITION BY qc2.answer_id, qc2.parent_comment_id
          ORDER BY qc2.created_at ASC
        )
        FROM "qna_comments" qc2
        WHERE qc2.id = "qna_comments"."id"
        AND qc2.is_deleted = false
      ) as "comment_order" from "qna_comments" left join "qna_answer" on "qna_comments"."answer_id" = "qna_answer"."id" left join "qna" on "qna_answer"."qna_id" = "qna"."id" left join "users" on "qna_comments"."author" = "users"."id" left join "vendors" on "users"."company_id" = "vendors"."id" left join "tech_vendors" on "users"."tech_company_id" = "tech_vendors"."id" where "qna_comments"."is_deleted" = false order by "qna_comments"."created_at");--> statement-breakpoint

CREATE VIEW "public"."qna_view" AS (select "qna"."id", "qna"."title", "qna"."content", "qna"."author", "qna"."category", "qna"."created_at", "qna"."updated_at", "qna"."is_deleted", "qna"."deleted_at", "users"."name", "users"."email", "users"."domain", "users"."phone", "users"."image_url", "users"."language", "users"."is_active", "users"."last_login_at", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."status" as "vendor_status", "vendors"."country" as "vendor_country", "vendors"."business_size" as "vendor_business_size", "tech_vendors"."vendor_name" as "tech_vendor_name", "tech_vendors"."vendor_code" as "tech_vendor_code", "tech_vendors"."status" as "tech_vendor_status", "tech_vendors"."country" as "tech_vendor_country", "tech_vendors"."tech_vendor_type" as "tech_vendor_type", COALESCE("vendors"."vendor_name", "tech_vendors"."vendor_name") as "company_name", COALESCE("vendors"."vendor_code", "tech_vendors"."vendor_code") as "company_code", COALESCE("vendors"."country", "tech_vendors"."country") as "company_country", 
          CASE 
            WHEN "vendors"."vendor_name" IS NOT NULL THEN 'vendor'
            WHEN "tech_vendors"."vendor_name" IS NOT NULL THEN 'techVendor'
            ELSE NULL
          END
         as "vendor_type", (
          SELECT COUNT(*)::int
          FROM "qna_answer" qa
          WHERE qa.qna_id = "qna"."id"
          AND qa.is_deleted = false
        ) as "total_answers", (
          SELECT COUNT(*)::int
          FROM "qna_answer" qa
          WHERE qa.qna_id = "qna"."id"
          AND qa.is_deleted = false
        ) as "answer_count", (
          SELECT MAX(qa.created_at)
          FROM "qna_answer" qa
          WHERE qa.qna_id = "qna"."id"
          AND qa.is_deleted = false
        ) as "last_answered_at", (
          SELECT MIN(qa.created_at)
          FROM "qna_answer" qa
          WHERE qa.qna_id = "qna"."id"
          AND qa.is_deleted = false
        ) as "first_answered_at", (
          SELECT COUNT(*)::int
          FROM "qna_comments" qc
          INNER JOIN "qna_answer" qa ON qc.answer_id = qa.id
          WHERE qa.qna_id = "qna"."id"
          AND qc.is_deleted = false
          AND qa.is_deleted = false
        ) as "total_comments", (
          SELECT GREATEST(
            "qna"."updated_at",
            COALESCE((
              SELECT MAX(qa.updated_at)
              FROM "qna_answer" qa
              WHERE qa.qna_id = "qna"."id"
              AND qa.is_deleted = false
            ), "qna"."updated_at"),
            COALESCE((
              SELECT MAX(qc.updated_at)
              FROM "qna_comments" qc
              INNER JOIN "qna_answer" qa ON qc.answer_id = qa.id
              WHERE qa.qna_id = "qna"."id"
              AND qc.is_deleted = false
              AND qa.is_deleted = false
            ), "qna"."updated_at")
          )
        ) as "last_activity_at", (
          SELECT COUNT(*) > 0
          FROM "qna_answer" qa
          WHERE qa.qna_id = "qna"."id"
          AND qa.is_deleted = false
        ) as "has_answers", (
          SELECT COUNT(*) > 0
          FROM "qna_answer" qa
          WHERE qa.qna_id = "qna"."id"
          AND qa.is_deleted = false
        ) as "is_answered", (
          (SELECT COUNT(*) FROM "qna_answer" qa WHERE qa.qna_id = "qna"."id" AND qa.is_deleted = false) >= 3
          OR
          (SELECT COUNT(*) FROM "qna_comments" qc 
           INNER JOIN "qna_answer" qa ON qc.answer_id = qa.id 
           WHERE qa.qna_id = "qna"."id" AND qc.is_deleted = false AND qa.is_deleted = false) >= 5
        ) as "is_popular" from "qna" left join "users" on "qna"."author" = "users"."id" left join "vendors" on "users"."company_id" = "vendors"."id" left join "tech_vendors" on "users"."tech_company_id" = "tech_vendors"."id" where "qna"."is_deleted" = false order by "qna"."created_at");