import {
    pgTable,
    varchar,
    text,
    timestamp,
    integer,
    boolean,pgEnum, 
    index, serial, pgView
} from "drizzle-orm/pg-core";
import { relations, sql, eq } from "drizzle-orm";
import { users } from "./users";
import { vendors } from "./vendors";
import { techVendors } from "./techVendors";


export const qnaCategoryEnum = pgEnum('qna_category', [
    'engineering',           // 설계
    'procurement',         // 구매  
    'technical_sales'   // 기술영업
]);


export const QNA_CATEGORY_LABELS =[
    { label: '설계', value: 'engineering' },
    { label: '구매', value: 'procurement' },
    { label: '기술영업', value: 'technical_sales' },
  ];


export const qna = pgTable(
    "qna",
    {
        id: serial("id").primaryKey(),

        title: varchar("title", { length: 255 }).notNull(),
        content: text("content").notNull(),
        category: qnaCategoryEnum("category").notNull(),

        /* 작성자 – 원본 유저 삭제 시 질문도 같이 제거(CASCADE) */
        author: integer("author")
            .references(() => users.id, {
                onDelete: "cascade",
            })
            .notNull(),

        createdAt: timestamp("created_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at").defaultNow().notNull(),

        /* 소프트 삭제 */
        isDeleted: boolean("is_deleted").default(false).notNull(),
        deletedAt: timestamp("deleted_at"),
    },
    (t) => ({
        idxAuthor: index("idx_qna_author").on(t.author),
    })
);

export const qnaAnswer = pgTable(
    "qna_answer",
    {
        id: serial("id").primaryKey(),

        /* FK → 질문 (수정: varchar → integer) */
        qnaId: integer("qna_id")
            .references(() => qna.id, {
                onDelete: "cascade",
            })
            .notNull(),

        content: text("content").notNull(),

        /* 작성자 – 삭제 시 답변도 같이 제거 */
        author: integer("author")
            .references(() => users.id, {
                onDelete: "cascade",
            })
            .notNull(),

        createdAt: timestamp("created_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at").defaultNow().notNull(),

        /* 소프트 삭제 */
        isDeleted: boolean("is_deleted").default(false).notNull(),
        deletedAt: timestamp("deleted_at"),
    },
    (t) => ({
        idxQna: index("idx_answer_qna").on(t.qnaId),
        idxAuthor: index("idx_answer_author").on(t.author),
    })
);

export const qnaComments = pgTable(
    "qna_comments",
    {
        id: serial("id").primaryKey(),

        content: text("content").notNull(),

        /* 작성자 */
        author: integer("author")
            .references(() => users.id, {
                onDelete: "cascade",
            })
            .notNull(),

        /* FK → 답변 (수정: varchar → integer) */
        answerId: integer("answer_id")
            .references(() => qnaAnswer.id, {
                onDelete: "cascade",
            })
            .notNull(),

        /* 대댓글용 self-join (nullable) - 순환참조 해결을 위해 reference 제거 */
        parentCommentId: integer("parent_comment_id"),

        createdAt: timestamp("created_at").defaultNow().notNull(),
        updatedAt: timestamp("updated_at").defaultNow().notNull(),

        /* 소프트 삭제 */
        isDeleted: boolean("is_deleted").default(false).notNull(),
        deletedAt: timestamp("deleted_at"),
    },
    (t) => ({
        idxAnswer: index("idx_comment_answer").on(t.answerId),
        idxParent: index("idx_comment_parent").on(t.parentCommentId),
    })
);


export const qnaRelations = relations(qna, ({ many }) => ({
    answers: many(qnaAnswer),
}));

export const qnaAnswerRelations = relations(qnaAnswer, ({ one, many }) => ({
    question: one(qna, {
        fields: [qnaAnswer.qnaId],
        references: [qna.id],
    }),
    comments: many(qnaComments),
}));

export const qnaCommentRelations = relations(qnaComments, ({ one, many }) => ({
    answer: one(qnaAnswer, {
        fields: [qnaComments.answerId],
        references: [qnaAnswer.id],
    }),
    // self-reference relations - 순환참조 해결을 위해 별도 처리
    parent: one(qnaComments, {
        fields: [qnaComments.parentCommentId],
        references: [qnaComments.id],
        relationName: "parentComment"
    }),
    children: many(qnaComments, {
        relationName: "parentComment"
    }),
}));



export type Qna = typeof qna.$inferSelect;
export type NewQna = typeof qna.$inferInsert;

export type QnaAnswer = typeof qnaAnswer.$inferSelect;
export type NewQnaAnswer = typeof qnaAnswer.$inferInsert;

export type QnaComment = typeof qnaComments.$inferSelect;
export type NewQnaComment = typeof qnaComments.$inferInsert;


export const qnaView = pgView('qna_view').as((qb) =>
    qb
      .select({
        // ═══════════════════════════════════════════════════════════════
        // Q&A 기본 정보
        // ═══════════════════════════════════════════════════════════════
        id: qna.id,
        title: qna.title,
        content: qna.content,
        author: qna.author,
        category: qna.category,

        // 생성/수정/삭제 정보
        createdAt: qna.createdAt,
        updatedAt: qna.updatedAt,
        isDeleted: qna.isDeleted,
        deletedAt: qna.deletedAt,
        
        // ═══════════════════════════════════════════════════════════════
        // 작성자 정보 (users 조인) - 모든 컬럼에 author 접두사
        // ═══════════════════════════════════════════════════════════════
        authorName: users.name,
        authorEmail: users.email,
        authorDomain: users.domain,
        authorPhone: users.phone,
        authorImageUrl: users.imageUrl,
        authorLanguage: users.language,
        authorIsActive: users.isActive,
        authorLastLoginAt: users.lastLoginAt,
        
        // ═══════════════════════════════════════════════════════════════
        // 회사 정보 (vendors/techVendors 조인) - 명시적 별칭 사용
        // ═══════════════════════════════════════════════════════════════
        // 일반 벤더 정보
        vendorName: sql<string>`${vendors.vendorName}`.as('vendor_name'),
        vendorCode: sql<string>`${vendors.vendorCode}`.as('vendor_code'),
        vendorStatus: sql<string>`${vendors.status}`.as('vendor_status'),
        vendorCountry: sql<string>`${vendors.country}`.as('vendor_country'),
        vendorBusinessSize: sql<string>`${vendors.businessSize}`.as('vendor_business_size'),
        
        // 기술 벤더 정보
        techVendorName: sql<string>`${techVendors.vendorName}`.as('tech_vendor_name'),
        techVendorCode: sql<string>`${techVendors.vendorCode}`.as('tech_vendor_code'),
        techVendorStatus: sql<string>`${techVendors.status}`.as('tech_vendor_status'),
        techVendorCountry: sql<string>`${techVendors.country}`.as('tech_vendor_country'),
        techVendorType: sql<string>`${techVendors.techVendorType}`.as('tech_vendor_type'),
        
        // 통합 회사 정보 (COALESCE 사용)
        companyName: sql<string>`COALESCE(${vendors.vendorName}, ${techVendors.vendorName})`.as('company_name'),
        companyCode: sql<string>`COALESCE(${vendors.vendorCode}, ${techVendors.vendorCode})`.as('company_code'),
        companyCountry: sql<string>`COALESCE(${vendors.country}, ${techVendors.country})`.as('company_country'),
        vendorType: sql<string>`
          CASE 
            WHEN ${vendors.vendorName} IS NOT NULL THEN 'vendor'
            WHEN ${techVendors.vendorName} IS NOT NULL THEN 'techVendor'
            ELSE NULL
          END
        `.as('vendor_type'),
        
        // ═══════════════════════════════════════════════════════════════
        // 답변 관련 통계 (서브쿼리로 계산)
        // ═══════════════════════════════════════════════════════════════
        totalAnswers: sql<number>`(
          SELECT COUNT(*)::int
          FROM ${qnaAnswer} qa
          WHERE qa.qna_id = ${qna.id}
          AND qa.is_deleted = false
        )`.as('total_answers'),
        
        answerCount: sql<number>`(
          SELECT COUNT(*)::int
          FROM ${qnaAnswer} qa
          WHERE qa.qna_id = ${qna.id}
          AND qa.is_deleted = false
        )`.as('answer_count'),
        
        lastAnsweredAt: sql<Date>`(
          SELECT MAX(qa.created_at)
          FROM ${qnaAnswer} qa
          WHERE qa.qna_id = ${qna.id}
          AND qa.is_deleted = false
        )`.as('last_answered_at'),
        
        firstAnsweredAt: sql<Date>`(
          SELECT MIN(qa.created_at)
          FROM ${qnaAnswer} qa
          WHERE qa.qna_id = ${qna.id}
          AND qa.is_deleted = false
        )`.as('first_answered_at'),
        
        // ═══════════════════════════════════════════════════════════════
        // 댓글 관련 통계 (전체 댓글 수)
        // ═══════════════════════════════════════════════════════════════
        totalComments: sql<number>`(
          SELECT COUNT(*)::int
          FROM ${qnaComments} qc
          INNER JOIN ${qnaAnswer} 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'),
        
        // ═══════════════════════════════════════════════════════════════
        // 최근 활동 정보
        // ═══════════════════════════════════════════════════════════════
        lastActivityAt: sql<Date>`(
          SELECT GREATEST(
            ${qna.updatedAt},
            COALESCE((
              SELECT MAX(qa.updated_at)
              FROM ${qnaAnswer} qa
              WHERE qa.qna_id = ${qna.id}
              AND qa.is_deleted = false
            ), ${qna.updatedAt}),
            COALESCE((
              SELECT MAX(qc.updated_at)
              FROM ${qnaComments} qc
              INNER JOIN ${qnaAnswer} qa ON qc.answer_id = qa.id
              WHERE qa.qna_id = ${qna.id}
              AND qc.is_deleted = false
              AND qa.is_deleted = false
            ), ${qna.updatedAt})
          )
        )`.as('last_activity_at'),
        
        // ═══════════════════════════════════════════════════════════════
        // 질문 상태 분류
        // ═══════════════════════════════════════════════════════════════
        hasAnswers: sql<boolean>`(
          SELECT COUNT(*) > 0
          FROM ${qnaAnswer} qa
          WHERE qa.qna_id = ${qna.id}
          AND qa.is_deleted = false
        )`.as('has_answers'),
        
        isAnswered: sql<boolean>`(
          SELECT COUNT(*) > 0
          FROM ${qnaAnswer} qa
          WHERE qa.qna_id = ${qna.id}
          AND qa.is_deleted = false
        )`.as('is_answered'),
        
        isPopular: sql<boolean>`(
          (SELECT COUNT(*) FROM ${qnaAnswer} qa WHERE qa.qna_id = ${qna.id} AND qa.is_deleted = false) >= 3
          OR
          (SELECT COUNT(*) FROM ${qnaComments} qc 
           INNER JOIN ${qnaAnswer} 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)
      .leftJoin(users, eq(qna.author, users.id))
      .leftJoin(vendors, eq(users.companyId, vendors.id))
      .leftJoin(techVendors, eq(users.techCompanyId, techVendors.id))
      .where(eq(qna.isDeleted, false))
      .orderBy(qna.createdAt)
  );

/* ================================================================
   Q&A 답변 뷰 (답변 목록용) - 컬럼명 충돌 해결
================================================================ */
export const qnaAnswerView = pgView('qna_answer_view').as((qb) =>
  qb
    .select({
      // ═══════════════════════════════════════════════════════════════
      // 답변 기본 정보
      // ═══════════════════════════════════════════════════════════════
      id: qnaAnswer.id,
      qnaId: qnaAnswer.qnaId,
      content: qnaAnswer.content,
      author: qnaAnswer.author,
      
      // 생성/수정/삭제 정보 - 명시적 별칭
      createdAt: sql<Date>`${qnaAnswer.createdAt}`.as('created_at'),
      updatedAt: sql<Date>`${qnaAnswer.updatedAt}`.as('updated_at'),
      isDeleted: sql<boolean>`${qnaAnswer.isDeleted}`.as('is_deleted'),
      deletedAt: sql<Date>`${qnaAnswer.deletedAt}`.as('deleted_at'),
      
      // ═══════════════════════════════════════════════════════════════
      // 질문 기본 정보 (조인으로 가져와서 컨텍스트 제공) - question 접두사
      // ═══════════════════════════════════════════════════════════════
      questionTitle: sql<string>`${qna.title}`.as('question_title'),
      questionCategory: sql<string>`${qna.category}`.as('question_category'),
      questionAuthor: sql<number>`${qna.author}`.as('question_author'),
      questionCreatedAt: sql<Date>`${qna.createdAt}`.as('question_created_at'),
      
      // ═══════════════════════════════════════════════════════════════
      // 답변 작성자 정보 (users 조인) - author 접두사
      // ═══════════════════════════════════════════════════════════════
      authorName: sql<string>`${users.name}`.as('author_name'),
      authorEmail: sql<string>`${users.email}`.as('author_email'),
      authorDomain: sql<string>`${users.domain}`.as('author_domain'),
      authorPhone: sql<string>`${users.phone}`.as('author_phone'),
      authorImageUrl: sql<string>`${users.imageUrl}`.as('author_image_url'),
      authorLanguage: sql<string>`${users.language}`.as('author_language'),
      
      // ═══════════════════════════════════════════════════════════════
      // 회사 정보 (vendors/techVendors 조인) - 명시적 별칭
      // ═══════════════════════════════════════════════════════════════
      vendorName: sql<string>`${vendors.vendorName}`.as('vendor_name'),
      vendorCode: sql<string>`${vendors.vendorCode}`.as('vendor_code'),
      techVendorName: sql<string>`${techVendors.vendorName}`.as('tech_vendor_name'),
      techVendorCode: sql<string>`${techVendors.vendorCode}`.as('tech_vendor_code'),
      
      // 통합 회사 정보
      companyName: sql<string>`COALESCE(${vendors.vendorName}, ${techVendors.vendorName})`.as('company_name'),
      companyCode: sql<string>`COALESCE(${vendors.vendorCode}, ${techVendors.vendorCode})`.as('company_code'),
      vendorType: sql<string>`
        CASE 
          WHEN ${vendors.vendorName} IS NOT NULL THEN 'vendor'
          WHEN ${techVendors.vendorName} IS NOT NULL THEN 'techVendor'
          ELSE NULL
        END
      `.as('vendor_type'),
      
      // ═══════════════════════════════════════════════════════════════
      // 댓글 관련 통계 (서브쿼리로 계산)
      // ═══════════════════════════════════════════════════════════════
      totalComments: sql<number>`(
        SELECT COUNT(*)::int
        FROM ${qnaComments} qc
        WHERE qc.answer_id = ${qnaAnswer.id}
        AND qc.is_deleted = false
      )`.as('total_comments'),
      
      commentCount: sql<number>`(
        SELECT COUNT(*)::int
        FROM ${qnaComments} qc
        WHERE qc.answer_id = ${qnaAnswer.id}
        AND qc.is_deleted = false
      )`.as('comment_count'),
      
      parentCommentsCount: sql<number>`(
        SELECT COUNT(*)::int
        FROM ${qnaComments} qc
        WHERE qc.answer_id = ${qnaAnswer.id}
        AND qc.parent_comment_id IS NULL
        AND qc.is_deleted = false
      )`.as('parent_comments_count'),
      
      childCommentsCount: sql<number>`(
        SELECT COUNT(*)::int
        FROM ${qnaComments} qc
        WHERE qc.answer_id = ${qnaAnswer.id}
        AND qc.parent_comment_id IS NOT NULL
        AND qc.is_deleted = false
      )`.as('child_comments_count'),
      
      lastCommentedAt: sql<Date>`(
        SELECT MAX(qc.created_at)
        FROM ${qnaComments} qc
        WHERE qc.answer_id = ${qnaAnswer.id}
        AND qc.is_deleted = false
      )`.as('last_commented_at'),
      
      // ═══════════════════════════════════════════════════════════════
      // 답변 순서 정보
      // ═══════════════════════════════════════════════════════════════
      answerOrder: sql<number>`(
        SELECT ROW_NUMBER() OVER (
          PARTITION BY qa2.qna_id 
          ORDER BY qa2.created_at ASC
        )
        FROM ${qnaAnswer} qa2
        WHERE qa2.id = ${qnaAnswer.id}
        AND qa2.is_deleted = false
      )`.as('answer_order'),
      
      isFirstAnswer: sql<boolean>`(
        ${qnaAnswer.id} = (
          SELECT qa2.id
          FROM ${qnaAnswer} qa2
          WHERE qa2.qna_id = ${qnaAnswer.qnaId}
          AND qa2.is_deleted = false
          ORDER BY qa2.created_at ASC
          LIMIT 1
        )
      )`.as('is_first_answer'),
      
      isLatestAnswer: sql<boolean>`(
        ${qnaAnswer.id} = (
          SELECT qa2.id
          FROM ${qnaAnswer} qa2
          WHERE qa2.qna_id = ${qnaAnswer.qnaId}
          AND qa2.is_deleted = false
          ORDER BY qa2.created_at DESC
          LIMIT 1
        )
      )`.as('is_latest_answer'),
    })
    .from(qnaAnswer)
    .leftJoin(qna, eq(qnaAnswer.qnaId, qna.id))
    .leftJoin(users, eq(qnaAnswer.author, users.id))
    .leftJoin(vendors, eq(users.companyId, vendors.id))
    .leftJoin(techVendors, eq(users.techCompanyId, techVendors.id))
    .where(eq(qnaAnswer.isDeleted, false))
    .orderBy(qnaAnswer.createdAt)
);

/* ================================================================
   Q&A 댓글 뷰 (댓글 목록용) - 컬럼명 충돌 해결
================================================================ */
export const qnaCommentView = pgView('qna_comment_view').as((qb) =>
  qb
    .select({
      // ═══════════════════════════════════════════════════════════════
      // 댓글 기본 정보
      // ═══════════════════════════════════════════════════════════════
      id: qnaComments.id,
      content: qnaComments.content,
      author: qnaComments.author,
      answerId: qnaComments.answerId,
      parentCommentId: qnaComments.parentCommentId,
      
      // 생성/수정/삭제 정보 - 명시적 별칭
      createdAt: sql<Date>`${qnaComments.createdAt}`.as('created_at'),
      updatedAt: sql<Date>`${qnaComments.updatedAt}`.as('updated_at'),
      isDeleted: sql<boolean>`${qnaComments.isDeleted}`.as('is_deleted'),
      deletedAt: sql<Date>`${qnaComments.deletedAt}`.as('deleted_at'),
      
      // ═══════════════════════════════════════════════════════════════
      // 답변 기본 정보 (조인으로 가져와서 컨텍스트 제공) - answer 접두사
      // ═══════════════════════════════════════════════════════════════
      answerContent: sql<string>`${qnaAnswer.content}`.as('answer_content'),
      answerAuthor: sql<number>`${qnaAnswer.author}`.as('answer_author'),
      answerCreatedAt: sql<Date>`${qnaAnswer.createdAt}`.as('answer_created_at'),
      qnaId: sql<number>`${qnaAnswer.qnaId}`.as('qna_id'),
      
      // ═══════════════════════════════════════════════════════════════
      // 질문 기본 정보 (답변을 통한 조인) - question 접두사
      // ═══════════════════════════════════════════════════════════════
      questionTitle: sql<string>`${qna.title}`.as('question_title'),
      questionCategory: sql<string>`${qna.category}`.as('question_category'),
      questionAuthor: sql<number>`${qna.author}`.as('question_author'),
      
      // ═══════════════════════════════════════════════════════════════
      // 댓글 작성자 정보 (users 조인) - author 접두사
      // ═══════════════════════════════════════════════════════════════
      authorName: sql<string>`${users.name}`.as('author_name'),
      authorEmail: sql<string>`${users.email}`.as('author_email'),
      authorDomain: sql<string>`${users.domain}`.as('author_domain'),
      authorImageUrl: sql<string>`${users.imageUrl}`.as('author_image_url'),
      
      // ═══════════════════════════════════════════════════════════════
      // 회사 정보 (vendors/techVendors 조인) - 명시적 별칭
      // ═══════════════════════════════════════════════════════════════
      vendorName: sql<string>`${vendors.vendorName}`.as('vendor_name'),
      techVendorName: sql<string>`${techVendors.vendorName}`.as('tech_vendor_name'),
      companyName: sql<string>`COALESCE(${vendors.vendorName}, ${techVendors.vendorName})`.as('company_name'),
      vendorType: sql<string>`
        CASE 
          WHEN ${vendors.vendorName} IS NOT NULL THEN 'vendor'
          WHEN ${techVendors.vendorName} IS NOT NULL THEN 'techVendor'
          ELSE NULL
        END
      `.as('vendor_type'),
      
      // ═══════════════════════════════════════════════════════════════
      // 댓글 계층 구조 정보
      // ═══════════════════════════════════════════════════════════════
      isParentComment: sql<boolean>`${qnaComments.parentCommentId} IS NULL`.as('is_parent_comment'),
      isChildComment: sql<boolean>`${qnaComments.parentCommentId} IS NOT NULL`.as('is_child_comment'),
      
      // 자식 댓글 수 (대댓글 수)
      childCommentsCount: sql<number>`(
        SELECT COUNT(*)::int
        FROM ${qnaComments} qc2
        WHERE qc2.parent_comment_id = ${qnaComments.id}
        AND qc2.is_deleted = false
      )`.as('child_comments_count'),
      
      hasChildComments: sql<boolean>`(
        SELECT COUNT(*) > 0
        FROM ${qnaComments} qc2
        WHERE qc2.parent_comment_id = ${qnaComments.id}
        AND qc2.is_deleted = false
      )`.as('has_child_comments'),
      
      // 댓글 깊이 (0: 부모, 1: 자식)
      commentDepth: sql<number>`
        CASE 
          WHEN ${qnaComments.parentCommentId} IS NULL THEN 0
          ELSE 1
        END
      `.as('comment_depth'),
      
      // ═══════════════════════════════════════════════════════════════
      // 댓글 순서 정보
      // ═══════════════════════════════════════════════════════════════
      commentOrder: sql<number>`(
        SELECT ROW_NUMBER() OVER (
          PARTITION BY qc2.answer_id, qc2.parent_comment_id
          ORDER BY qc2.created_at ASC
        )
        FROM ${qnaComments} qc2
        WHERE qc2.id = ${qnaComments.id}
        AND qc2.is_deleted = false
      )`.as('comment_order'),
    })
    .from(qnaComments)
    .leftJoin(qnaAnswer, eq(qnaComments.answerId, qnaAnswer.id))
    .leftJoin(qna, eq(qnaAnswer.qnaId, qna.id))
    .leftJoin(users, eq(qnaComments.author, users.id))
    .leftJoin(vendors, eq(users.companyId, vendors.id))
    .leftJoin(techVendors, eq(users.techCompanyId, techVendors.id))
    .where(eq(qnaComments.isDeleted, false))
    .orderBy(qnaComments.createdAt)
);
  /* ================================================================
     타입 추론
  ================================================================ */
  export type QnaViewSelect = typeof qnaView.$inferSelect;
  export type QnaAnswerViewSelect = typeof qnaAnswerView.$inferSelect;
  export type QnaCommentViewSelect = typeof qnaCommentView.$inferSelect;