import { DrizzleService } from '@lib/drizzle/src';
import { Injectable } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import { and, count, eq, gte, isNotNull, like, lte, or, SQL, sql } from 'drizzle-orm';
import { tnMember, tnReferralBenefit } from 'drizzle/schema';
import { GetReferralMemberListDto, UpdateReferralBenefitDto } from './referral.dto';

@Injectable()
export class ReferralRepository {
  private key: string;

  constructor(
    private readonly drizzle: DrizzleService,
    private configService: ConfigService,
  ) {
    this.key = this.configService.get<string>('aes.secret') ?? '';
  }

  /**
   * 추천인 혜택 설정 조회
   */
  async getReferralBenefit() {
    const result = await this.drizzle
      .select()
      .from(tnReferralBenefit)
      .limit(1);

    return result[0] || null;
  }

  /**
   * 추천인 혜택 설정 수정
   */
  async updateReferralBenefit(dto: UpdateReferralBenefitDto) {
    // 기존 데이터 확인
    const existing = await this.getReferralBenefit();

    if (existing) {
      // 업데이트
      const result = await this.drizzle
        .update(tnReferralBenefit)
        .set({
          rfbEnabled: dto.rfbEnabled,
          rfbReferrerPoint: dto.rfbReferrerPoint,
          rfbRefereePoint: dto.rfbRefereePoint,
          rfbExpireEnabled: dto.rfbExpireEnabled,
          rfbExpireDays: dto.rfbExpireDays,
          rfbUpdatedAt: new Date().toISOString().slice(0, 19).replace('T', ' '),
        })
        .where(eq(tnReferralBenefit.rfbId, existing.rfbId));

      return result;
    } else {
      // 신규 생성
      const result = await this.drizzle.insert(tnReferralBenefit).values({
        rfbEnabled: dto.rfbEnabled,
        rfbReferrerPoint: dto.rfbReferrerPoint,
        rfbRefereePoint: dto.rfbRefereePoint,
        rfbExpireEnabled: dto.rfbExpireEnabled,
        rfbExpireDays: dto.rfbExpireDays,
      });

      return result;
    }
  }

  /**
   * 추천인 회원 목록 조회
   */
  async getReferralMemberList(dto: GetReferralMemberListDto) {
    const { page, searchKeyword, searchType, startDate, endDate, dateType, isDownload } = dto;

    const limit = dto.limit ? dto.limit : 10;
    const offset = (page - 1) * limit;

    // WHERE 조건 생성
    const whereConditions: SQL[] = [isNotNull(tnMember.memReferralCode)];

    // 날짜 검색
    if (startDate && endDate && dateType === 'memCreatedAt') {
      whereConditions.push(gte(tnMember.memCreatedAt, startDate));
      whereConditions.push(lte(tnMember.memCreatedAt, endDate));
    }

    // 키워드 검색
    if (searchKeyword && searchType) {
      if (searchType === 'memName') {
        whereConditions.push(
          sql`FN_DECRYPT(${tnMember.memName}, ${this.key}) LIKE ${`%${searchKeyword}%`} COLLATE utf8mb4_general_ci`,
        );
      } else if (searchType === 'memLoginId') {
        whereConditions.push(like(tnMember.memLoginId, `%${searchKeyword}%`));
      } else if (searchType === 'memPhone') {
        whereConditions.push(
          sql`FN_DECRYPT(${tnMember.memPhone}, ${this.key}) LIKE ${`%${searchKeyword}%`} COLLATE utf8mb4_general_ci`,
        );
      }
    }

    const where = and(...whereConditions);

    // 피추천인 조회 (referee: 추천코드를 사용한 사람)
    const refereeAlias = tnMember;
    const referrerAlias = sql`referrer`;

    const query = this.drizzle
      .select({
        referrer: {
          memId: sql<number>`referrer.mem_id`,
          memLoginId: sql<string>`referrer.mem_login_id`,
          memName: sql<string>`FN_DECRYPT(referrer.mem_name, ${this.key})`,
          memMyReferralCode: sql<string>`referrer.mem_my_referral_code`,
        },
        referee: {
          memId: refereeAlias.memId,
          memLoginId: refereeAlias.memLoginId,
          memName: sql<string>`FN_DECRYPT(${refereeAlias.memName}, ${this.key})`,
          memCreatedAt: refereeAlias.memCreatedAt,
        },
      })
      .from(refereeAlias)
      .innerJoin(
        sql`tn_member AS referrer`,
        sql`referrer.mem_my_referral_code = ${refereeAlias.memReferralCode}`
      )
      .where(where)
      .orderBy(sql`${refereeAlias.memCreatedAt} DESC`);

    if (isDownload) {
      return await query;
    } else {
      return await query.limit(limit).offset(offset);
    }
  }

  /**
   * 추천인 회원 총 개수
   */
  async getReferralMemberCount(dto: GetReferralMemberListDto) {
    const { searchKeyword, searchType, startDate, endDate, dateType } = dto;

    const whereConditions: SQL[] = [isNotNull(tnMember.memReferralCode)];

    if (startDate && endDate && dateType === 'memCreatedAt') {
      whereConditions.push(gte(tnMember.memCreatedAt, startDate));
      whereConditions.push(lte(tnMember.memCreatedAt, endDate));
    }

    if (searchKeyword && searchType) {
      if (searchType === 'memName') {
        whereConditions.push(
          sql`FN_DECRYPT(${tnMember.memName}, ${this.key}) LIKE ${`%${searchKeyword}%`} COLLATE utf8mb4_general_ci`,
        );
      } else if (searchType === 'memLoginId') {
        whereConditions.push(like(tnMember.memLoginId, `%${searchKeyword}%`));
      } else if (searchType === 'memPhone') {
        whereConditions.push(
          sql`FN_DECRYPT(${tnMember.memPhone}, ${this.key}) LIKE ${`%${searchKeyword}%`} COLLATE utf8mb4_general_ci`,
        );
      }
    }

    const where = and(...whereConditions);

    const result = await this.drizzle
      .select({ count: count() })
      .from(tnMember)
      .where(where);

    return result[0]?.count ?? 0;
  }
}