import { DrizzleService } from '@lib/drizzle/src/drizzle.service';
import { Injectable } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import { hash } from 'bcrypt';
import {
  and,
  asc,
  count,
  desc,
  eq,
  gte,
  inArray,
  like,
  lte,
  sql,
  SQL,
} from 'drizzle-orm';
import {
  tnAdmin,
  tnAgency,
  tnAgencyOrganization,
  tnAuth,
  tnMember,
  tnSalesAgency,
} from 'drizzle/schema';
import { CreateAdminDto, GetAdminListDto, UpdateAdminDto } from './admin.dto';

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

  async getAdminByLoginId(admLoginId: string) {
    const [admin] = await this.drizzle
      .select()
      .from(tnAdmin)
      .where(eq(tnAdmin.admLoginId, admLoginId));

    return admin;
  }

  async getDetail(id: number) {
    const [detail] = await this.drizzle
      .select({
        admin: {
          admId: tnAdmin.admId,
          admLoginId: tnAdmin.admLoginId,
          admName: sql<string>`FN_DECRYPT(${tnAdmin.admName}, ${this.key})`,
          admEmail: sql<string>`FN_DECRYPT(${tnAdmin.admEmail}, ${this.key})`,
          admPhone: sql<string>`FN_DECRYPT(${tnAdmin.admPhone}, ${this.key})`,
          admLastLoginAt: tnAdmin.admLastLoginAt,
          admUpdatedAt: tnAdmin.admUpdatedAt,
          admCreatedAt: tnAdmin.admCreatedAt,
        },
        auth: {
          athId: tnAuth.athId,
          athName: tnAuth.athName,
        },
      })
      .from(tnAdmin)
      .where(eq(tnAdmin.admId, id))
      .leftJoin(tnAuth, eq(tnAuth.athId, tnAdmin.athId));

    return detail;
  }

  async create(dto: CreateAdminDto) {
    const hashedPassword = await hash(dto.admPassword, 10);

    await this.drizzle.insert(tnAdmin).values({
      athId: dto.athId,
      admLoginId: dto.admLoginId,
      admPassword: hashedPassword,
      admName: sql`FN_ENCRYPT(${dto.admName}, ${this.key})`,
      admPhone: sql`FN_ENCRYPT(${dto.admPhone}, ${this.key})`,
      // admName: sql`TO_BASE64(AES_ENCRYPT(${dto.admName},${this.key}))`,
      // admPhone: sql`TO_BASE64(AES_ENCRYPT(${dto.admPhone},${this.key}))`,
      admEmail:
        dto.admEmail !== ''
          ? sql`FN_ENCRYPT(${dto.admEmail}, ${this.key})`
          : null,
    });
  }

  async update(id: number, dto: UpdateAdminDto) {
    await this.drizzle
      .update(tnAdmin)
      .set({
        athId: dto.athId,
        admPassword:
          dto.admPassword !== '' ? await hash(dto.admPassword!, 10) : undefined,
        admName: sql`FN_ENCRYPT(${dto.admName}, ${this.key})`,
        admPhone: sql`FN_ENCRYPT(${dto.admPhone}, ${this.key})`,
        admEmail:
          dto.admEmail !== ''
            ? sql`FN_ENCRYPT(${dto.admEmail}, ${this.key})`
            : null,
      })
      .where(eq(tnAdmin.admId, id));
  }

  async delete(ids: number[]) {
    await this.drizzle.delete(tnAdmin).where(inArray(tnAdmin.admId, ids));
  }

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

    return await this.drizzle
      .select({
        admin: {
          admId: tnAdmin.admId,
          admLoginId: tnAdmin.admLoginId,
          admName: sql<string>`FN_DECRYPT(${tnAdmin.admName}, ${this.key})`,
          admEmail: sql<string>`FN_DECRYPT(${tnAdmin.admEmail}, ${this.key})`,
          admPhone: sql<string>`FN_DECRYPT(${tnAdmin.admPhone}, ${this.key})`,
          // admName: sql<string>`CONVERT(AES_DECRYPT(FROM_BASE64(${tnAdmin.admName}), ${this.key}) USING UTF8)`,
          // admPhone: sql<string>`CONVERT(AES_DECRYPT(FROM_BASE64(${tnAdmin.admPhone}), ${this.key}) USING UTF8)`,
          // admEmail: sql<string>`CONVERT(AES_DECRYPT(FROM_BASE64(${tnAdmin.admEmail}), ${this.key}) USING UTF8)`,
          admLastLoginAt: tnAdmin.admLastLoginAt,
          admUpdatedAt: tnAdmin.admUpdatedAt,
          admCreatedAt: tnAdmin.admCreatedAt,
        },
        auth: {
          athId: tnAuth.athId,
          athName: tnAuth.athName,
        },
      })
      .from(tnAdmin)
      .leftJoin(tnAuth, eq(tnAuth.athId, tnAdmin.athId))
      .where(where)
      .orderBy(orderBy)
      .limit(limit)
      .offset(offset);
  }

  async count(where: SQL<unknown> | undefined) {
    const [totalCount] = await this.drizzle
      .select({ count: count() })
      .from(tnAdmin)
      .leftJoin(tnAuth, eq(tnAuth.athId, tnAdmin.athId))
      .where(where);

    return totalCount.count ?? 0;
  }

  async getSalesAdminByLoginId(sagLoginId: string) {
    const [sales] = await this.drizzle
      .select({
        sagId: tnSalesAgency.sagId,
        sagLoginId: tnSalesAgency.sagLoginId,
        sagPassword: tnSalesAgency.sagPassword,
      })
      .from(tnSalesAgency)
      .where(eq(tnSalesAgency.sagLoginId, sagLoginId));

    return sales;
  }

  async getCorpAdminByLoginId(agcLoginId: string) {
    const [corp] = await this.drizzle
      .select({
        agcId: tnAgency.agcId,
        agcLoginId: tnAgency.agcLoginId,
        agcPassword: tnAgency.agcPassword,
        agcCorporateName: tnAgency.agcCorporateName,
        agcAnalysisView: tnAgency.agcAnalysisView,
      })
      .from(tnAgency)
      .where(eq(tnAgency.agcLoginId, agcLoginId));

    return corp;
  }

  //* 조직관리자 로그인 조회 — 기업(agcLoginId) 내 mem_is_org_manager=1 기업회원(MEM002).
  //*   관리 org의 org_path를 함께 반환(부분트리 스코프 payload용).
  //* 기업 식별은 공개용 기업코드(agcCorporateCode) 기준 (2026-07 기획 — 로그인 입력값 변경)
  async getOrgManagerByCorporateCode(
    agcCorporateCode: string,
    memLoginId: string,
  ) {
    const [mgr] = await this.drizzle
      .select({
        agcId: tnMember.agcId,
        agcLoginId: tnAgency.agcLoginId,
        memId: tnMember.memId,
        memName: tnMember.memName,
        memPassword: tnMember.memPassword,
        orgId: tnMember.orgId,
        orgPath: tnAgencyOrganization.orgPath,
        agcAnalysisView: tnAgency.agcAnalysisView,
      })
      .from(tnMember)
      .innerJoin(tnAgency, eq(tnMember.agcId, tnAgency.agcId))
      .leftJoin(
        tnAgencyOrganization,
        eq(tnMember.orgId, tnAgencyOrganization.orgId),
      )
      .where(
        and(
          eq(tnAgency.agcCorporateCode, agcCorporateCode),
          eq(tnMember.memLoginId, memLoginId),
          eq(tnMember.memIsOrgManager, 1),
          eq(tnMember.memType, 'MEM002'),
          eq(tnMember.memIsResigned, 0),
        ),
      );

    return mgr;
  }

  async whereClause(dto: GetAdminListDto) {
    const { searchText, searchKey, dateType, startDate, endDate, athId } = 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 (!!athId) {
      where.push(eq(tnAdmin.athId, athId));
    }

    return and(...where);
  }

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

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

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

    switch (dateType) {
      case 'admCreatedAt': {
        where.push(gte(tnAdmin.admCreatedAt, startDate));
        where.push(lte(tnAdmin.admCreatedAt, endDate));
        return where;
      }

      case 'admLastLoginAt': {
        where.push(gte(tnAdmin.admLastLoginAt, startDate));
        where.push(lte(tnAdmin.admLastLoginAt, endDate));
        return where;
      }
      default: {
        where.push(gte(tnAdmin.admCreatedAt, startDate));
        where.push(lte(tnAdmin.admCreatedAt, endDate));
        return where;
      }
    }
  }

  makeSearchKeyWhere(searchKey: string, searchText: string): SQL {
    switch (searchKey) {
      case 'admName': {
        return sql`
        CONVERT(AES_DECRYPT(FROM_BASE64(${tnAdmin.admName}), ${this.key}) USING UTF8) LIKE ${`%${searchText}%`}
        `;
      }
      case 'admLoginId': {
        return like(tnAdmin.admLoginId, `%${searchText}%`);
      }
      case 'admPhone': {
        return sql`
        CONVERT(AES_DECRYPT(FROM_BASE64(${tnAdmin.admPhone}), ${this.key}) USING UTF8) LIKE ${`%${searchText}%`}
        `;
      }
      case 'admEmail': {
        return sql`
        CONVERT(AES_DECRYPT(FROM_BASE64(${tnAdmin.admEmail}), ${this.key}) USING UTF8) LIKE ${`%${searchText}%`}
        `;
      }
      default: {
        return sql`
        CONVERT(AES_DECRYPT(FROM_BASE64(${tnAdmin.admName}), ${this.key}) USING UTF8) LIKE ${`%${searchText}%`}
        `;
      }
    }
  }

  makeOrderField(sortKey: string) {
    switch (sortKey) {
      case 'admName': {
        return tnAdmin.admName;
      }
      case 'admLoginId': {
        return tnAdmin.admLoginId;
      }
      case 'admPhone': {
        return tnAdmin.admPhone;
      }
      case 'admLastLoginAt': {
        return tnAdmin.admLastLoginAt;
      }
      case 'admCreatedAt': {
        return tnAdmin.admCreatedAt;
      }
      default: {
        return tnAdmin.admCreatedAt;
      }
    }
  }
}