import { DrizzleService } from '@lib/drizzle/src/drizzle.service';
import { Injectable } from '@nestjs/common';
import {
  and,
  asc,
  count,
  desc,
  eq,
  inArray,
  like,
  notLike,
  or,
  sql,
  SQL,
} from 'drizzle-orm';
import {
  tcMedicationCategory,
  tcMedicationStandard,
  tnB2cOrderDetail,
  tnClientOrderDetail,
  tnMember,
  tnOrderDetail,
  ttB2cTreatBasic,
  ttB2cTreatPrescribe,
  ttClientTreatBasic,
  ttClientTreatPrescribe,
  ttMedicationCategory,
  ttMedicationStandard,
  ttTreatBasic,
  ttTreatPrescribe,
  tnUser,
} from 'drizzle/schema';
import { GetMedicationStandardFlatDto } from './medication-standard.dto';

type Dosing30DayDiagnosis = {
  diseaseCode: string | null;
  diseaseName: string | null;
};

type Dosing30DayDiagnosisRow = Dosing30DayDiagnosis & {
  treatStartDate: string | null;
  hospitalName: string | null;
};

const dosingDiagnosisVisitKey = (
  treatStartDate: string | null,
  hospitalName: string | null,
) =>
  treatStartDate && hospitalName ? `${treatStartDate}\0${hospitalName}` : null;

const buildDosingDiagnosisMap = (rows: Dosing30DayDiagnosisRow[]) => {
  const diagnosesByVisit = new Map<string, Dosing30DayDiagnosis[]>();
  const seenByVisit = new Map<string, Set<string>>();

  for (const row of rows) {
    const visitKey = dosingDiagnosisVisitKey(
      row.treatStartDate,
      row.hospitalName,
    );
    if (!visitKey) continue;

    const diseaseCode = row.diseaseCode?.trim() || null;
    const diseaseName = row.diseaseName?.trim() || null;
    if (!diseaseCode && !diseaseName) continue;

    const diagnosisKey = `${diseaseCode ?? ''}\0${diseaseName ?? ''}`;
    const seen = seenByVisit.get(visitKey) ?? new Set<string>();
    if (seen.has(diagnosisKey)) continue;

    seen.add(diagnosisKey);
    seenByVisit.set(visitKey, seen);
    const diagnoses = diagnosesByVisit.get(visitKey) ?? [];
    diagnoses.push({ diseaseCode, diseaseName });
    diagnosesByVisit.set(visitKey, diagnoses);
  }

  return diagnosesByVisit;
};

@Injectable()
export class MedicationStandardRepository {
  constructor(private drizzle: DrizzleService) {}

  //* 정회원 주문(oddId)의 소유자 memId·소속 기업 agcId. 소유자/관리자 열람 검증 전용 — 없으면 null.
  async findOrderOwner(
    oddId: number,
  ): Promise<{ memId: number | null; agcId: number | null } | null> {
    const [row] = await this.drizzle
      .select({ memId: tnUser.memId, agcId: tnMember.agcId })
      .from(tnOrderDetail)
      .innerJoin(tnUser, eq(tnUser.usrId, tnOrderDetail.usrId))
      .leftJoin(tnMember, eq(tnMember.memId, tnUser.memId))
      .where(eq(tnOrderDetail.oddId, oddId));

    return row ?? null;
  }

  // ==================== 대분류 (Category) ====================
  async getCategoryList() {
    return await this.drizzle
      .select()
      .from(tcMedicationCategory)
      .orderBy(asc(tcMedicationCategory.mcName));
  }

  async findCategoryByName(mcName: string) {
    const [result] = await this.drizzle
      .select()
      .from(tcMedicationCategory)
      .where(eq(tcMedicationCategory.mcName, mcName))
      .limit(1);
    return result;
  }

  async createCategory(mcName: string) {
    const [result] = await this.drizzle
      .insert(tcMedicationCategory)
      .values({ mcName });
    return result.insertId;
  }

  // 공백 제거 후 분류명(대분류) 중복 검사
  async checkDuplicateCategoryName(name: string, excludeId?: number) {
    const normalizedName = name.replace(/\s/g, '');
    const conditions: SQL<unknown>[] = [
      sql`REPLACE(${tcMedicationCategory.mcName}, ' ', '') = ${normalizedName}`,
    ];
    if (excludeId) {
      conditions.push(sql`${tcMedicationCategory.mcId} != ${excludeId}`);
    }
    const [result] = await this.drizzle
      .select({ count: count() })
      .from(tcMedicationCategory)
      .where(and(...conditions));
    return (result?.count ?? 0) > 0;
  }

  // 공백 제거 후 분류항목명(mscCode) 중복 검사
  async checkDuplicateStandardCode(code: string, excludeId?: number) {
    const normalizedCode = code.replace(/\s/g, '');
    const conditions: SQL<unknown>[] = [
      sql`REPLACE(${tcMedicationStandard.mscCode}, ' ', '') = ${normalizedCode}`,
    ];
    if (excludeId) {
      conditions.push(sql`${tcMedicationStandard.mscId} != ${excludeId}`);
    }
    const [result] = await this.drizzle
      .select({ count: count() })
      .from(tcMedicationStandard)
      .where(and(...conditions));
    return (result?.count ?? 0) > 0;
  }

  // ==================== 중분류 (Standard) ====================
  async getStandardFlatList(
    where: SQL<unknown> | undefined,
    orderBy: SQL<unknown>[],
    limit: number,
    offset: number,
  ) {
    return await this.drizzle
      .select({
        mscId: tcMedicationStandard.mscId,
        mscCategoryId: tcMedicationStandard.mscCategoryId,
        mscCategory: tcMedicationCategory.mcName,
        mscCode: tcMedicationStandard.mscCode,
        mscName: tcMedicationStandard.mscName,
        mscCreatedAt: tcMedicationStandard.mscCreatedAt,
        mscUpdatedAt: tcMedicationStandard.mscUpdatedAt,
      })
      .from(tcMedicationStandard)
      .leftJoin(
        tcMedicationCategory,
        eq(tcMedicationStandard.mscCategoryId, tcMedicationCategory.mcId),
      )
      .where(where)
      .orderBy(...orderBy)
      .limit(limit)
      .offset(offset);
  }

  async countStandard(where: SQL<unknown> | undefined) {
    const [result] = await this.drizzle
      .select({ count: count() })
      .from(tcMedicationStandard)
      .leftJoin(
        tcMedicationCategory,
        eq(tcMedicationStandard.mscCategoryId, tcMedicationCategory.mcId),
      )
      .where(where);
    return result.count ?? 0;
  }

  async getStandardDetail(mscId: number) {
    const [detail] = await this.drizzle
      .select({
        mscId: tcMedicationStandard.mscId,
        mscCategoryId: tcMedicationStandard.mscCategoryId,
        mscCategory: tcMedicationCategory.mcName,
        mscCode: tcMedicationStandard.mscCode,
        mscName: tcMedicationStandard.mscName,
        mscCreatedAt: tcMedicationStandard.mscCreatedAt,
        mscUpdatedAt: tcMedicationStandard.mscUpdatedAt,
      })
      .from(tcMedicationStandard)
      .leftJoin(
        tcMedicationCategory,
        eq(tcMedicationStandard.mscCategoryId, tcMedicationCategory.mcId),
      )
      .where(eq(tcMedicationStandard.mscId, mscId))
      .limit(1);
    return detail;
  }

  async createStandard(data: {
    mscCategoryId: number;
    mscCode: string;
    mscName: string;
  }) {
    const [result] = await this.drizzle
      .insert(tcMedicationStandard)
      .values(data);
    return result.insertId;
  }

  async updateStandard(
    mscId: number,
    data: {
      mscCategoryId: number;
      mscCode: string;
      mscName: string;
    },
  ) {
    await this.drizzle
      .update(tcMedicationStandard)
      .set(data)
      .where(eq(tcMedicationStandard.mscId, mscId));
  }

  async deleteStandards(ids: number[]) {
    await this.drizzle.transaction(async (tx) => {
      // 1. 삭제할 투약기준들이 속한 카테고리 ID 조회
      const standards = await tx
        .select({ mscCategoryId: tcMedicationStandard.mscCategoryId })
        .from(tcMedicationStandard)
        .where(inArray(tcMedicationStandard.mscId, ids));

      const categoryIds = [...new Set(standards.map((s) => s.mscCategoryId))];

      // 2. 투약기준 삭제
      await tx
        .delete(tcMedicationStandard)
        .where(inArray(tcMedicationStandard.mscId, ids));

      // 3. 각 카테고리에 남은 투약기준이 있는지 확인하고, 없으면 카테고리 삭제
      for (const categoryId of categoryIds) {
        const [remaining] = await tx
          .select({ count: count() })
          .from(tcMedicationStandard)
          .where(eq(tcMedicationStandard.mscCategoryId, categoryId));

        if (remaining.count === 0) {
          await tx
            .delete(tcMedicationCategory)
            .where(eq(tcMedicationCategory.mcId, categoryId));
        }
      }
    });
  }

  // ==================== 검색 조건 빌더 ====================
  buildWhereCondition(
    query: GetMedicationStandardFlatDto,
  ): SQL<unknown> | undefined {
    const conditions: SQL<unknown>[] = [];

    // 대분류 필터
    if (query.categoryId) {
      conditions.push(eq(tcMedicationStandard.mscCategoryId, query.categoryId));
    }

    // 개별 필드 필터 (프론트엔드 직접 호출)
    if (query.mcName) {
      conditions.push(like(tcMedicationCategory.mcName, `%${query.mcName}%`));
    }
    if (query.mscCode) {
      conditions.push(like(tcMedicationStandard.mscCode, `%${query.mscCode}%`));
    }
    if (query.mscName) {
      conditions.push(like(tcMedicationStandard.mscName, `%${query.mscName}%`));
    }

    // 검색 필터 (searchKey + searchText 조합)
    if (query.searchText && query.searchKey) {
      switch (query.searchKey) {
        case 'mcName':
          conditions.push(
            like(tcMedicationCategory.mcName, `%${query.searchText}%`),
          );
          break;
        case 'mscCode':
          conditions.push(
            like(tcMedicationStandard.mscCode, `%${query.searchText}%`),
          );
          break;
        case 'mscName':
          conditions.push(
            like(tcMedicationStandard.mscName, `%${query.searchText}%`),
          );
          break;
      }
    }

    // 날짜 필터
    if (query.startDate) {
      conditions.push(
        sql`${tcMedicationStandard.mscCreatedAt} >= ${query.startDate}`,
      );
    }
    if (query.endDate) {
      conditions.push(
        sql`${tcMedicationStandard.mscCreatedAt} <= CONCAT(${query.endDate}, ' 23:59:59')`,
      );
    }

    return conditions.length > 0 ? and(...conditions) : undefined;
  }

  buildOrderBy(query: GetMedicationStandardFlatDto): SQL<unknown>[] {
    if (query.sortBy && query.sortOrder) {
      let column;
      if (query.sortBy === 'mscCategory') {
        column = tcMedicationCategory.mcName;
      } else if (query.sortBy === 'mscId') {
        column = tcMedicationStandard.mscId;
      } else if (query.sortBy === 'mscCategoryId') {
        column = tcMedicationStandard.mscCategoryId;
      } else if (query.sortBy === 'mscCode') {
        column = tcMedicationStandard.mscCode;
      } else if (query.sortBy === 'mscName') {
        column = tcMedicationStandard.mscName;
      } else if (query.sortBy === 'mscCreatedAt') {
        column = tcMedicationStandard.mscCreatedAt;
      } else if (query.sortBy === 'mscUpdatedAt') {
        column = tcMedicationStandard.mscUpdatedAt;
      } else {
        column = tcMedicationStandard.mscCreatedAt;
      }
      return [query.sortOrder === 'ASC' ? asc(column) : desc(column)];
    }
    // 기본 정렬: 대분류 ID → 중분류 ID 오름차순
    return [asc(tcMedicationCategory.mcId), asc(tcMedicationStandard.mscId)];
  }

  // ==================== Excel Upload & Recovery ====================

  /** 엑셀 다운로드용 전체 목록 조회 */
  async getAllList(where: SQL<unknown> | undefined) {
    return await this.drizzle
      .select({
        mscId: tcMedicationStandard.mscId,
        mscCategoryId: tcMedicationStandard.mscCategoryId,
        mcName: tcMedicationCategory.mcName,
        mscCode: tcMedicationStandard.mscCode,
        mscName: tcMedicationStandard.mscName,
        mscCreatedAt: tcMedicationStandard.mscCreatedAt,
        mscUpdatedAt: tcMedicationStandard.mscUpdatedAt,
      })
      .from(tcMedicationStandard)
      .leftJoin(
        tcMedicationCategory,
        eq(tcMedicationStandard.mscCategoryId, tcMedicationCategory.mcId),
      )
      .where(where);
  }

  /** 임시 테이블에 백업 데이터가 존재하는지 확인 */
  async doesExistBackup() {
    const [categoryCount] = await this.drizzle
      .select({ count: count() })
      .from(ttMedicationCategory);
    const [standardCount] = await this.drizzle
      .select({ count: count() })
      .from(ttMedicationStandard);

    return (categoryCount.count ?? 0) > 0 || (standardCount.count ?? 0) > 0;
  }

  /** Excel 업로드 처리 */
  async uploadExcel(data: { categories: any[]; standards: any[] }) {
    await this.drizzle.transaction(async (tx) => {
      // 1. 임시 테이블 삭제
      await tx.delete(ttMedicationCategory);
      await tx.delete(ttMedicationStandard);

      // 2. 현재 데이터를 임시 테이블로 백업
      const oldCategories = await tx
        .select({
          mcId: tcMedicationCategory.mcId,
          mcName: tcMedicationCategory.mcName,
          mcCreatedAt: tcMedicationCategory.mcCreatedAt,
        })
        .from(tcMedicationCategory);

      const oldStandards = await tx
        .select({
          mscId: tcMedicationStandard.mscId,
          mscCategoryId: tcMedicationStandard.mscCategoryId,
          mscCode: tcMedicationStandard.mscCode,
          mscName: tcMedicationStandard.mscName,
          mscCreatedAt: tcMedicationStandard.mscCreatedAt,
          mscUpdatedAt: tcMedicationStandard.mscUpdatedAt,
        })
        .from(tcMedicationStandard);

      // 백업 데이터 저장 (원시 SQL 사용)
      if (oldCategories.length > 0) {
        await tx.execute(sql`
          INSERT INTO tt_medication_category (mc_id, mc_name, mc_created_at)
          SELECT mc_id, mc_name, mc_created_at FROM tc_medication_category
        `);
      }
      if (oldStandards.length > 0) {
        await tx.execute(sql`
          INSERT INTO tt_medication_standard (msc_id, msc_category_id, msc_code, msc_name, msc_created_at, msc_updated_at)
          SELECT msc_id, msc_category_id, msc_code, msc_name, msc_created_at, msc_updated_at FROM tc_medication_standard
        `);
      }

      // 3. 메인 테이블 전체 삭제
      await tx.delete(tcMedicationStandard);
      await tx.delete(tcMedicationCategory);

      // 4. 신규 데이터 삽입 (대분류 먼저, 그 다음 중분류)
      if (data.categories.length > 0) {
        await tx.insert(tcMedicationCategory).values(data.categories);
      }

      // 중분류 삽입 (대분류 이름을 ID로 변환)
      if (data.standards.length > 0) {
        for (const standardItem of data.standards) {
          const [category] = await tx
            .select({ mcId: tcMedicationCategory.mcId })
            .from(tcMedicationCategory)
            .where(eq(tcMedicationCategory.mcName, standardItem.categoryName));

          if (category) {
            await tx.insert(tcMedicationStandard).values({
              mscCategoryId: category.mcId,
              mscCode: standardItem.mscCode,
              mscName: standardItem.mscName,
            });
          }
        }
      }
    });
  }

  /** 이전 데이터 복구 */
  async recovery() {
    await this.drizzle.transaction(async (tx) => {
      const backupCategories = await tx
        .select({
          mcId: ttMedicationCategory.mcId,
          mcName: ttMedicationCategory.mcName,
          mcCreatedAt: ttMedicationCategory.mcCreatedAt,
        })
        .from(ttMedicationCategory);

      const backupStandards = await tx
        .select({
          mscId: ttMedicationStandard.mscId,
          mscCategoryId: ttMedicationStandard.mscCategoryId,
          mscCode: ttMedicationStandard.mscCode,
          mscName: ttMedicationStandard.mscName,
          mscCreatedAt: ttMedicationStandard.mscCreatedAt,
          mscUpdatedAt: ttMedicationStandard.mscUpdatedAt,
        })
        .from(ttMedicationStandard);

      // 메인 테이블 전체 삭제
      await tx.delete(tcMedicationStandard);
      await tx.delete(tcMedicationCategory);

      // 백업 데이터로 복원 (원시 SQL 사용)
      if (backupCategories.length > 0) {
        await tx.execute(sql`
          INSERT INTO tc_medication_category (mc_id, mc_name, mc_created_at)
          SELECT mc_id, mc_name, mc_created_at FROM tt_medication_category
        `);
      }
      if (backupStandards.length > 0) {
        await tx.execute(sql`
          INSERT INTO tc_medication_standard (msc_id, msc_category_id, msc_code, msc_name, msc_created_at, msc_updated_at)
          SELECT msc_id, msc_category_id, msc_code, msc_name, msc_created_at, msc_updated_at FROM tt_medication_standard
        `);
      }
    });
  }

  // ==================== 투약기준 추천 ====================

  /**
   * 투약기준 추천 조회
   * 1. tt_treat_prescribe에서 oddId로 조회
   * 2. 병원명에 '약국' 포함된 것 제외
   * 3. 약명별로 총 투약일수 합산
   * 4. tc_medication_standard, tc_medication_category 조인
   * 5. 투약일수 내림차순 정렬
   */
  /*
   * [분석상세 데이터 소스 - 20260508]
   * 분석상세에서 원래는 tn_analysis_* 테이블을 노출해야 했으나,
   * 분석상세 구조 변경(1. 병력분석 탭 추가, 2. 건강체 탭 내 '5년내 7일/30일이상 투약' →
   * '7일이상 진료', '30일이상 투약' 탭 분리)으로 인해 tt_treat_* 테이블을 직접 조회하게 됨.
   *
   * tn_analysis_* 로 보여주기 위해 PROC_COMPARE_HEALTH_RECORD, PROC_COMPARE_DOSING_RECORD,
   * PROC_COMPARE_LOG 프로시저를 추가했었으나, 커넥션 풀 부족 + 분석 속도 증가 이슈로
   * 해당 프로시저를 사용하지 않고 tt_treat_* 테이블을 직접 조회하기로 결정됨.
   */
  async getRecommendedMedication(
    oddId?: number,
    bodId?: number,
    codId?: number,
  ) {
    if (codId) {
      const rows = await this.drizzle
        .select({
          categoryName: tcMedicationCategory.mcName,
          itemName: tcMedicationStandard.mscName,
          efficacy: tcMedicationStandard.mscCode,
          days: sql<number>`SUM(${ttClientTreatPrescribe.ctrpTotalDosingDays})`.mapWith(
            Number,
          ),
        })
        .from(ttClientTreatPrescribe)
        .innerJoin(
          tcMedicationStandard,
          sql`REPLACE(${ttClientTreatPrescribe.ctrpDrugName}, ' ', '') COLLATE utf8mb4_general_ci = REPLACE(${tcMedicationStandard.mscCode}, ' ', '') COLLATE utf8mb4_general_ci`,
        )
        .innerJoin(
          tcMedicationCategory,
          eq(tcMedicationStandard.mscCategoryId, tcMedicationCategory.mcId),
        )
        .where(
          and(
            eq(ttClientTreatPrescribe.codId, codId),
            or(
              sql`${ttClientTreatPrescribe.ctrpHospitalName} IS NULL`,
              notLike(ttClientTreatPrescribe.ctrpHospitalName, '%약국%'),
            ),
          ),
        )
        .groupBy(
          tcMedicationCategory.mcName,
          tcMedicationStandard.mscName,
          tcMedicationStandard.mscCode,
        )
        .orderBy(desc(sql`SUM(${ttClientTreatPrescribe.ctrpTotalDosingDays})`));
      return rows;
    } else if (bodId) {
      const rows = await this.drizzle
        .select({
          categoryName: tcMedicationCategory.mcName,
          itemName: tcMedicationStandard.mscName,
          efficacy: tcMedicationStandard.mscCode,
          days: sql<number>`SUM(${ttB2cTreatPrescribe.btrpTotalDosingDays})`.mapWith(
            Number,
          ),
        })
        .from(ttB2cTreatPrescribe)
        .innerJoin(
          tcMedicationStandard,
          sql`REPLACE(${ttB2cTreatPrescribe.btrpDrugName}, ' ', '') COLLATE utf8mb4_general_ci = REPLACE(${tcMedicationStandard.mscCode}, ' ', '') COLLATE utf8mb4_general_ci`,
        )
        .innerJoin(
          tcMedicationCategory,
          eq(tcMedicationStandard.mscCategoryId, tcMedicationCategory.mcId),
        )
        .where(
          and(
            eq(ttB2cTreatPrescribe.bodId, bodId),
            or(
              sql`${ttB2cTreatPrescribe.btrpHospitalName} IS NULL`,
              notLike(ttB2cTreatPrescribe.btrpHospitalName, '%약국%'),
            ),
          ),
        )
        .groupBy(
          tcMedicationCategory.mcName,
          tcMedicationStandard.mscName,
          tcMedicationStandard.mscCode,
        )
        .orderBy(desc(sql`SUM(${ttB2cTreatPrescribe.btrpTotalDosingDays})`));
      return rows;
    } else if (oddId) {
      const rows = await this.drizzle
        .select({
          categoryName: tcMedicationCategory.mcName,
          itemName: tcMedicationStandard.mscName,
          efficacy: tcMedicationStandard.mscCode,
          days: sql<number>`SUM(${ttTreatPrescribe.trpTotalDosingDays})`.mapWith(
            Number,
          ),
        })
        .from(ttTreatPrescribe)
        .innerJoin(
          tcMedicationStandard,
          sql`REPLACE(${ttTreatPrescribe.trpDrugName}, ' ', '') COLLATE utf8mb4_general_ci = REPLACE(${tcMedicationStandard.mscCode}, ' ', '') COLLATE utf8mb4_general_ci`,
        )
        .innerJoin(
          tcMedicationCategory,
          eq(tcMedicationStandard.mscCategoryId, tcMedicationCategory.mcId),
        )
        .where(
          and(
            eq(ttTreatPrescribe.oddId, oddId),
            or(
              sql`${ttTreatPrescribe.trpHospitalName} IS NULL`,
              notLike(ttTreatPrescribe.trpHospitalName, '%약국%'),
            ),
          ),
        )
        .groupBy(
          tcMedicationCategory.mcName,
          tcMedicationStandard.mscName,
          tcMedicationStandard.mscCode,
        )
        .orderBy(desc(sql`SUM(${ttTreatPrescribe.trpTotalDosingDays})`));
      return rows;
    }

    return [];
  }

  // ==================== 5년 내 30일이상 투약 그룹 ====================

  /**
   * 5년 내 30일이상 투약 카테고리 그룹 조회
   * 1. 주문 상세의 생성일 기준 5년 필터
   * 2. 처방 데이터를 약효 카테고리별로 그루핑하여 투약일수 합산
   * 3. `minTotalDays`(기본 30일) 이상인 카테고리만 필터
   * 4. 각 카테고리의 진료 기록(약국 제외) 조회
   *
   * `minTotalDays` 는 상병별 판정 입력(`ddx-verdict.service`)만 0 을 준다 — 2026-10-13 고객사 「하나의 상병 내에서
   * 30일 이상」: 약효분류 합계로 미리 거르면 여러 분류에 나뉜 한 상병의 처방이 빠진다. 30일 컷은 상병별로 판정 서버가 한다.
   */
  async getDosing30DayGroups(
    oddId?: number,
    bodId?: number,
    codId?: number,
    minTotalDays = 30,
  ) {
    let dosingRecords: {
      categoryName: string | null;
      efficacy: string | null;
      treatStartDate: string | null;
      hospitalName: string | null;
      drugName: string | null;
      ingredients: string | null;
      oneDose: number | null;
      dailyDosesNumber: number | null;
      totalDosingDays: number | null;
    }[] = [];

    if (codId) {
      dosingRecords = await this.drizzle
        .select({
          categoryName: tcMedicationCategory.mcName,
          efficacy: tcMedicationStandard.mscName,
          treatStartDate: ttClientTreatPrescribe.ctrpTreatStartDate,
          hospitalName: ttClientTreatPrescribe.ctrpHospitalName,
          drugName: ttClientTreatPrescribe.ctrpDrugName,
          ingredients: ttClientTreatPrescribe.ctrpIngredients,
          oneDose: ttClientTreatPrescribe.ctrpOneDose,
          dailyDosesNumber: ttClientTreatPrescribe.ctrpDailyDosesNumber,
          totalDosingDays: ttClientTreatPrescribe.ctrpTotalDosingDays,
        })
        .from(ttClientTreatPrescribe)
        .innerJoin(
          tcMedicationStandard,
          sql`REPLACE(${ttClientTreatPrescribe.ctrpDrugName}, ' ', '') COLLATE utf8mb4_general_ci = REPLACE(${tcMedicationStandard.mscCode}, ' ', '') COLLATE utf8mb4_general_ci`,
        )
        .innerJoin(
          tcMedicationCategory,
          eq(tcMedicationStandard.mscCategoryId, tcMedicationCategory.mcId),
        )
        .innerJoin(
          tnClientOrderDetail,
          eq(tnClientOrderDetail.codId, ttClientTreatPrescribe.codId),
        )
        .where(
          and(
            eq(ttClientTreatPrescribe.codId, codId),
            notLike(ttClientTreatPrescribe.ctrpHospitalName, '%약국%'),
            sql`${ttClientTreatPrescribe.ctrpTreatStartDate} >= DATE_FORMAT(DATE_SUB(${tnClientOrderDetail.codCreatedAt}, INTERVAL 5 YEAR), '%Y-%m-%d')`,
          ),
        );
    } else if (bodId) {
      dosingRecords = await this.drizzle
        .select({
          categoryName: tcMedicationCategory.mcName,
          efficacy: tcMedicationStandard.mscName,
          treatStartDate: ttB2cTreatPrescribe.btrpTreatStartDate,
          hospitalName: ttB2cTreatPrescribe.btrpHospitalName,
          drugName: ttB2cTreatPrescribe.btrpDrugName,
          ingredients: ttB2cTreatPrescribe.btrpIngredients,
          oneDose: ttB2cTreatPrescribe.btrpOneDose,
          dailyDosesNumber: ttB2cTreatPrescribe.btrpDailyDosesNumber,
          totalDosingDays: ttB2cTreatPrescribe.btrpTotalDosingDays,
        })
        .from(ttB2cTreatPrescribe)
        .innerJoin(
          tcMedicationStandard,
          sql`REPLACE(${ttB2cTreatPrescribe.btrpDrugName}, ' ', '') COLLATE utf8mb4_general_ci = REPLACE(${tcMedicationStandard.mscCode}, ' ', '') COLLATE utf8mb4_general_ci`,
        )
        .innerJoin(
          tcMedicationCategory,
          eq(tcMedicationStandard.mscCategoryId, tcMedicationCategory.mcId),
        )
        .innerJoin(
          tnB2cOrderDetail,
          eq(tnB2cOrderDetail.bodId, ttB2cTreatPrescribe.bodId),
        )
        .where(
          and(
            eq(ttB2cTreatPrescribe.bodId, bodId),
            notLike(ttB2cTreatPrescribe.btrpHospitalName, '%약국%'),
            sql`${ttB2cTreatPrescribe.btrpTreatStartDate} >= DATE_FORMAT(DATE_SUB(${tnB2cOrderDetail.bodCreatedAt}, INTERVAL 5 YEAR), '%Y-%m-%d')`,
          ),
        );
    } else if (oddId) {
      dosingRecords = await this.drizzle
        .select({
          categoryName: tcMedicationCategory.mcName,
          efficacy: tcMedicationStandard.mscName,
          treatStartDate: ttTreatPrescribe.trpTreatStartDate,
          hospitalName: ttTreatPrescribe.trpHospitalName,
          drugName: ttTreatPrescribe.trpDrugName,
          ingredients: ttTreatPrescribe.trpIngredients,
          oneDose: ttTreatPrescribe.trpOneDose,
          dailyDosesNumber: ttTreatPrescribe.trpDailyDosesNumber,
          totalDosingDays: ttTreatPrescribe.trpTotalDosingDays,
        })
        .from(ttTreatPrescribe)
        .innerJoin(
          tcMedicationStandard,
          sql`REPLACE(${ttTreatPrescribe.trpDrugName}, ' ', '') COLLATE utf8mb4_general_ci = REPLACE(${tcMedicationStandard.mscCode}, ' ', '') COLLATE utf8mb4_general_ci`,
        )
        .innerJoin(
          tcMedicationCategory,
          eq(tcMedicationStandard.mscCategoryId, tcMedicationCategory.mcId),
        )
        .innerJoin(
          tnOrderDetail,
          eq(tnOrderDetail.oddId, ttTreatPrescribe.oddId),
        )
        .where(
          and(
            eq(ttTreatPrescribe.oddId, oddId),
            notLike(ttTreatPrescribe.trpHospitalName, '%약국%'),
            sql`${ttTreatPrescribe.trpTreatStartDate} >= DATE_FORMAT(DATE_SUB(${tnOrderDetail.oddCreatedAt}, INTERVAL 5 YEAR), '%Y-%m-%d')`,
          ),
        );
    }

    if (dosingRecords.length === 0) {
      return [];
    }

    const categoryMap = new Map<
      string,
      {
        totalDosingDays: number;
        startDate: string;
        endDate: string;
        records: typeof dosingRecords;
      }
    >();

    for (const r of dosingRecords) {
      const name = r.categoryName!;
      const existing = categoryMap.get(name);
      if (existing) {
        existing.totalDosingDays += Number(r.totalDosingDays ?? 0);
        if (r.treatStartDate && r.treatStartDate < existing.startDate)
          existing.startDate = r.treatStartDate;
        if (r.treatStartDate && r.treatStartDate > existing.endDate)
          existing.endDate = r.treatStartDate;
        existing.records.push(r);
      } else {
        categoryMap.set(name, {
          totalDosingDays: Number(r.totalDosingDays ?? 0),
          startDate: r.treatStartDate ?? '',
          endDate: r.treatStartDate ?? '',
          records: [r],
        });
      }
    }

    const eligibleGroups = Array.from(categoryMap.entries())
      .filter(([, v]) => v.totalDosingDays >= minTotalDays)
      .sort((a, b) => b[1].totalDosingDays - a[1].totalDosingDays);

    if (eligibleGroups.length === 0) {
      return [];
    }

    let diagnosisRows: Dosing30DayDiagnosisRow[] = [];

    if (codId) {
      diagnosisRows = await this.drizzle
        .select({
          treatStartDate: ttClientTreatBasic.ctrbTreatStartDate,
          hospitalName: ttClientTreatBasic.ctrbHospitalName,
          diseaseCode: ttClientTreatBasic.ctrbDiseaseCode,
          diseaseName: ttClientTreatBasic.ctrbDiseaseName,
        })
        .from(ttClientTreatBasic)
        .where(eq(ttClientTreatBasic.codId, codId))
        .orderBy(asc(ttClientTreatBasic.ctrbId));
    } else if (bodId) {
      diagnosisRows = await this.drizzle
        .select({
          treatStartDate: ttB2cTreatBasic.btrbTreatStartDate,
          hospitalName: ttB2cTreatBasic.btrbHospitalName,
          diseaseCode: ttB2cTreatBasic.btrbDiseaseCode,
          diseaseName: ttB2cTreatBasic.btrbDiseaseName,
        })
        .from(ttB2cTreatBasic)
        .where(eq(ttB2cTreatBasic.bodId, bodId))
        .orderBy(asc(ttB2cTreatBasic.btrbId));
    } else if (oddId) {
      diagnosisRows = await this.drizzle
        .select({
          treatStartDate: ttTreatBasic.trbTreatStartDate,
          hospitalName: ttTreatBasic.trbHospitalName,
          diseaseCode: ttTreatBasic.trbDiseaseCode,
          diseaseName: ttTreatBasic.trbDiseaseName,
        })
        .from(ttTreatBasic)
        .where(eq(ttTreatBasic.oddId, oddId))
        .orderBy(asc(ttTreatBasic.trbId));
    }

    const diagnosisMap = buildDosingDiagnosisMap(diagnosisRows);

    return eligibleGroups.map(([name, v]) => ({
      categoryName: name,
      totalDosingDays: v.totalDosingDays,
      startDate: v.startDate,
      endDate: v.endDate,
      records: v.records
        // 진료시작일 최신순 정렬 (최신 → 오래된)
        .slice()
        .sort((a, b) =>
          (b.treatStartDate ?? '').localeCompare(a.treatStartDate ?? ''),
        )
        .map((r) => {
          const visitKey = dosingDiagnosisVisitKey(
            r.treatStartDate,
            r.hospitalName,
          );
          return {
            efficacy: r.efficacy,
            treatStartDate: r.treatStartDate,
            hospitalName: r.hospitalName,
            diagnoses: visitKey ? (diagnosisMap.get(visitKey) ?? []) : [],
            drugName: r.drugName,
            ingredients: r.ingredients,
            oneDose: r.oneDose,
            dailyDosesNumber: r.dailyDosesNumber,
            totalDosingDays: r.totalDosingDays,
          };
        }),
    }));
  }

  /**
   * 약품명 → 허가 효능효과(`msc_name`) 배치 대조.
   *
   * **조인식은 `analyze.repository.ts` 의 `aspEfficacy` 상관 서브쿼리와 같은 것**이다 —
   * 공백을 지우고 `utf8mb4_general_ci` 로 맞춘다. 그 서브쿼리는 행마다 한 번 도는 단건용이라
   * 판정 화면처럼 약이 수백 건인 자리에는 못 쓴다. 여기서 한 번에 끌어와 맵으로 준다.
   *
   * **좌변에 `COLLATE` 를 명시한다.** 바인딩 파라미터는 연결 charset(`utf8mb4_0900_ai_ci`)을
   * 따르므로 컬럼의 `utf8mb4_general_ci` 와 만나면 `Illegal mix of collations` 로 죽는다
   * (실측 — 이 조인식을 임시 테이블로 재현했을 때 ERROR 1267).
   *
   * `msc_code` 는 중복 등록될 수 있다(같은 약품명 여러 행). **`msc_id` 오름차순의 첫 행을
   * 쓴다** — 같은 약이 화면마다 다른 효능효과를 갖지 않으려면 고르는 규칙이 결정적이어야 하는데,
   * `ORDER BY` 없는 스캔 순서는 MySQL 이 보장하지 않는다. 기존 서브쿼리의 `LIMIT 1` 도 같은
   * 자리에서 순서가 미정이므로, 그쪽과 다른 행을 고를 여지까지 이 정렬이 닫는다.
   *
   * **이 조회는 `idx_msc_code` 를 타지 못한다** — 컬럼을 `REPLACE`·`COLLATE` 로 감싸면 인덱스가
   * 죽어 매 호출이 `tc_medication_standard` 전체 스캔이다. 그래도 기존 상관 서브쿼리(처방 행마다
   * 스캔 1회)보다는 적게 돌므로 이 창의 선택은 개선이다. **테이블이 커지면 그 말이 뒤집힌다** —
   * 판정 화면을 열 때마다 붙는 쿼리이므로, 행 수를 재고 큰 값이면 `REPLACE(msc_code,' ','')` 를
   * 생성 컬럼으로 굳혀 인덱스를 얹는 것이 유일한 길이다. 이 창은 stg DB 자격증명이 없어 그 값을
   * 재지 못했고, 재지 않은 채로 스키마를 바꾸지 않는다(저장소 규약 INV4).
   */
  async findEfficacyByDrugNames(
    drugNames: string[],
  ): Promise<Map<string, string>> {
    const keys = [...new Set(drugNames.map((n) => n.replace(/ /g, '')))].filter(
      (k) => k.length > 0,
    );
    if (keys.length === 0) return new Map();

    const keyExpr = sql<string>`REPLACE(${tcMedicationStandard.mscCode}, ' ', '') COLLATE utf8mb4_general_ci`;
    const rows = await this.drizzle
      .select({ key: keyExpr, efficacy: tcMedicationStandard.mscName })
      .from(tcMedicationStandard)
      .where(inArray(keyExpr, keys))
      .orderBy(asc(tcMedicationStandard.mscId));

    const out = new Map<string, string>();
    for (const r of rows) {
      if (r.efficacy && r.efficacy.length > 0 && !out.has(r.key)) {
        out.set(r.key, r.efficacy);
      }
    }
    return out;
  }
}