(대표님/최겸) 20250702 변경사항 업데이트

1 files changed, 158 insertions, 0 deletions

diff --git a/db/migrations/0178_icy_robin_chapel.sql b/db/migrations/0178_icy_robin_chapel.sql
new file mode 100644
index 00000000..0eb78a44
--- /dev/null
+++ b/db/migrations/0178_icy_robin_chapel.sql
@@ -0,0 +1,158 @@
+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", "qna_answer"."updated_at", "qna_answer"."is_deleted", "qna_answer"."deleted_at", "qna"."title", "qna"."author", "qna"."created_at", "users"."name", "users"."email", "users"."domain", "users"."phone", "users"."image_url", "users"."language", "vendors"."vendor_name", "vendors"."vendor_code", "tech_vendors"."vendor_name", "tech_vendors"."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", "qna_comments"."updated_at", "qna_comments"."is_deleted", "qna_comments"."deleted_at", "qna_answer"."content", "qna_answer"."author", "qna_answer"."created_at", "qna_answer"."qna_id", "qna"."title", "qna"."author", "users"."name", "users"."email", "users"."domain", "users"."image_url", "vendors"."vendor_name", "tech_vendors"."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"."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", "vendors"."vendor_code", "vendors"."status", "vendors"."country", "vendors"."business_size", "tech_vendors"."vendor_name", "tech_vendors"."vendor_code", "tech_vendors"."status", "tech_vendors"."country", "tech_vendors"."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");
\ No newline at end of file