import { DrizzleService } from '@lib/drizzle/src';
import { Injectable } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import {
  and,
  asc,
  count,
  desc,
  eq,
  gte,
  isNull,
  lte,
  or,
  sql,
  SQL,
} from 'drizzle-orm';
import {
  tnAgency,
  tnMember,
  tnSalesAgency,
  tnSalesAgencyChannel,
  tnUser,
} from 'drizzle/schema';
import { EnrollDto, GetCommonUserListDto } from './user.dto';

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

  async create(dto: EnrollDto) {
    const [user] = await this.drizzle.insert(tnUser).values({
      memId: dto.memId,
      usrName: sql`FN_ENCRYPT(${dto.usrName}, ${this.key})`,
      usrPhone: sql`FN_ENCRYPT(${dto.usrPhone}, ${this.key})`,
      usrBirth: sql`FN_ENCRYPT(${dto.usrBirth}, ${this.key})`,
    });
    return user.insertId;
  }

  async doexExist(dto: EnrollDto) {
    const [doesExist] = await this.drizzle
      .select()
      .from(tnUser)
      .where(
        sql`${tnUser.memId} = ${dto.memId}
        and CONVERT(AES_DECRYPT(FROM_BASE64(${tnUser.usrName}), ${this.key}) USING UTF8) = ${dto.usrName}
        and CONVERT(AES_DECRYPT(FROM_BASE64(${tnUser.usrPhone}), ${this.key}) USING UTF8) = ${dto.usrPhone}
        and CONVERT(AES_DECRYPT(FROM_BASE64(${tnUser.usrBirth}), ${this.key}) USING UTF8) = ${dto.usrBirth}
        `,
      );

    return doesExist;
  }

  async getList(
    where: SQL<unknown> | undefined,
    orderBy: SQL<unknown>,
    page: number,
    isDownload: boolean,
    pageLimit?: number,
  ) {
    const limit = pageLimit ? pageLimit : 10;
    const offset = (page - 1) * limit;

    if (isDownload) {
      return await this.buildDynamicQuery(where, orderBy);
    } else {
      return await this.buildDynamicQuery(where, orderBy)
        .limit(limit)
        .offset(offset);
    }
  }

  buildDynamicQuery(where: SQL<unknown> | undefined, orderBy: SQL<unknown>) {
    return this.drizzle
      .select({
        user: {
          usrId: tnUser.usrId,
          usrName: sql<string>`FN_DECRYPT(${tnUser.usrName}, ${this.key})`,
          usrPhone: sql<string>`FN_DECRYPT(${tnUser.usrPhone}, ${this.key})`,
          usrBirth: sql<string>`FN_DECRYPT(${tnUser.usrBirth}, ${this.key})`,
          usrCreatedAt: tnUser.usrCreatedAt,
        },
        member: {
          memId: tnMember.memId,
          memLoginId: tnMember.memLoginId,
          memName: sql<string>`FN_DECRYPT(${tnMember.memName}, ${this.key})`,
          memAgency: tnMember.memAgency,
          memType: sql<string>`GET_STATUS(${tnMember.memType})`,
        },
        agency: {
          agcCorporateName: tnAgency.agcCorporateName,
        },
        salesAgency: {
          sagName: tnSalesAgency.sagName,
        },
        salesAgencyChannel: {
          sacCode: tnSalesAgencyChannel.sacCode,
          sacName: tnSalesAgencyChannel.sacName,
        },
      })
      .from(tnUser)
      .leftJoin(tnMember, eq(tnMember.memId, tnUser.memId))
      .leftJoin(tnAgency, eq(tnAgency.agcId, tnMember.agcId))
      .leftJoin(
        tnSalesAgencyChannel,
        eq(tnSalesAgencyChannel.sacCode, tnMember.sacCode),
      )
      .leftJoin(
        tnSalesAgency,
        or(
          eq(tnSalesAgency.sagId, tnSalesAgencyChannel.sagId),
          eq(tnSalesAgency.sagId, tnAgency.sagId),
        ),
      )
      .where(where)
      .orderBy(orderBy)
      .$dynamic();
  }

  async count(where: SQL<unknown> | undefined) {
    const [totalCount] = await this.drizzle
      .select({ count: count() })
      .from(tnUser)
      .leftJoin(tnMember, eq(tnMember.memId, tnUser.memId))
      .leftJoin(tnAgency, eq(tnAgency.agcId, tnMember.agcId))
      .leftJoin(
        tnSalesAgencyChannel,
        eq(tnSalesAgencyChannel.sacCode, tnMember.sacCode),
      )
      .leftJoin(
        tnSalesAgency,
        or(
          eq(tnSalesAgency.sagId, tnSalesAgencyChannel.sagId),
          eq(tnSalesAgency.sagId, tnAgency.sagId),
        ),
      )
      .where(where);

    return totalCount.count ?? 0;
  }

  async whereClause(dto: GetCommonUserListDto) {
    const {
      searchKey,
      searchText,
      memId,
      dateType,
      startDate,
      endDate,
      memType,
      agcId,
      sagId,
      sacCode,
      orgPath,
    } = dto;

    const where: SQL[] = [];
    if (!!startDate && !!endDate) {
      const dateWhere = this.makeDateWhere(dateType, startDate, endDate);
      where.push(...dateWhere);
    }

    if (!!searchText) {
      const searchKeyWhere = this.makeSearchKeyWhere(searchKey, searchText);
      where.push(searchKeyWhere);
    }

    if (!!memId) {
      const isMember = sql`${tnUser.memId} = ${memId}`;
      where.push(isMember);
    }

    if (!!memType) {
      const isMemberType = eq(tnMember.memType, memType);
      where.push(isMemberType);
    }

    if (!!agcId) {
      const isAgency = eq(tnAgency.agcId, agcId);
      where.push(isAgency);
    }

    //* 조직관리자 부분트리 스코프 — 회원 소속 org가 관리 org_path 하위인 것만.
    if (!!orgPath) {
      where.push(
        sql`EXISTS (SELECT 1 FROM tn_agency_organization o WHERE o.org_id = ${tnMember.orgId} AND o.org_path LIKE ${orgPath + '%'})`,
      );
    }

    if (!!sagId) {
      if (sagId !== -1) {
        const isSales = eq(tnSalesAgency.sagId, sagId);
        where.push(isSales);
      } else {
        const isSales = isNull(tnSalesAgency.sagId);
        where.push(isSales);
      }
    }

    //* 영업대리점 채널 코드
    if (!!sacCode) {
      if (sacCode !== 'none') {
        const isSalesAgency = eq(tnSalesAgencyChannel.sacCode, sacCode);

        if (isSalesAgency) {
          where.push(isSalesAgency);
        }
      } else {
        const isSalesAgency = or(
          isNull(tnSalesAgencyChannel.sacCode),
          eq(tnSalesAgencyChannel.sacCode, ''),
        );

        if (isSalesAgency) {
          where.push(isSalesAgency);
        }
      }
    }

    return and(...where);
  }

  makeSearchKeyWhere(searchKey: string, searchText: string): SQL {
    switch (searchKey) {
      case 'memLoginId': {
        return sql`${tnMember.memLoginId} LIKE ${`%${searchText}%`}`; //! 추후 memLoginId 조회 조건 추가되는 경우 해당부분 수정 및 전용 API 생성 필요
      }
      case 'memAgency': {
        return sql`${tnMember.memAgency} LIKE ${`%${searchText}%`}`;
      }
      case 'memName': {
        return sql`
        CONVERT(AES_DECRYPT(FROM_BASE64(${tnMember.memName}), ${this.key}) USING UTF8) LIKE ${`%${searchText}%`}
        `;
      }
      case 'memAgency': {
        return sql`${tnMember.memAgency} LIKE ${`%${searchText}%`}`;
      }
      case 'memName': {
        return sql`
        CONVERT(AES_DECRYPT(FROM_BASE64(${tnMember.memName}), ${this.key}) USING UTF8) LIKE ${`%${searchText}%`}
        `;
      }
      case 'usrName': {
        return sql`
        CONVERT(AES_DECRYPT(FROM_BASE64(${tnUser.usrName}), ${this.key}) USING UTF8) LIKE ${`%${searchText}%`}
        `;
      }
      case 'usrPhone': {
        return sql`
        CONVERT(AES_DECRYPT(FROM_BASE64(${tnUser.usrPhone}), ${this.key}) USING UTF8) LIKE ${`%${searchText}%`}
        `;
      }
      case 'agcCorporateName': {
        return sql`${tnAgency.agcCorporateName} LIKE ${`%${searchText}%`}`;
      }
      default: {
        return sql`
        CONVERT(AES_DECRYPT(FROM_BASE64(${tnUser.usrName}), ${this.key}) USING UTF8) LIKE ${`%${searchText}%`}
        `;
      }
    }
  }

  makeDateWhere(dateType: string, startDate: string, endDate: string): SQL[] {
    const where: SQL[] = [];

    switch (dateType) {
      case 'usrCreatedAt': {
        where.push(gte(tnUser.usrCreatedAt, startDate));
        where.push(lte(tnUser.usrCreatedAt, endDate));
        return where;
      }
      default: {
        where.push(gte(tnUser.usrCreatedAt, startDate));
        where.push(lte(tnUser.usrCreatedAt, endDate));
        return where;
      }
    }
  }

  async orderByClause(dto: GetCommonUserListDto) {
    const orderField = this.makeOrderField(dto.sortKey);

    switch (dto.sortValue) {
      case 'desc': {
        return desc(orderField);
      }
      default: {
        return asc(orderField);
      }
    }
  }

  makeOrderField(sortKey: string) {
    switch (sortKey) {
      case 'usrName': {
        return tnUser.usrName;
      }
      case 'usrCreatedAt': {
        return tnUser.usrCreatedAt;
      }
      default: {
        return tnUser.usrCreatedAt;
      }
    }
  }

  /** 기업 ID로 회원 목록 조회 */
  async getMembersByAgencyId(agcId: number) {
    const members = await this.drizzle
      .select({
        memId: tnMember.memId,
        memLoginId: tnMember.memLoginId,
        memName: sql<string>`FN_DECRYPT(${tnMember.memName}, ${this.key})`,
        memEmail: sql<string>`FN_DECRYPT(${tnMember.memEmail}, ${this.key})`,
        memPhone: sql<string>`FN_DECRYPT(${tnMember.memPhone}, ${this.key})`,
      })
      .from(tnMember)
      .where(eq(tnMember.agcId, agcId));

    return members;
  }

  /** 회원 ID로 회원 조회 */
  async getMemberById(memId: string) {
    const member = await this.drizzle
      .select()
      .from(tnMember)
      .where(eq(tnMember.memLoginId, memId))
      .limit(1);

    return member[0] || null;
  }
}