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 {
  AUDIT_SECURITY_ACTION,
  SECURITY_TARGET_TYPE,
} from '../audit/audit-security';
import { AuditContext } from '../audit/audit.entity';
import { AuditService } from '../audit/audit.service';
import { CreateAdminDto, GetAdminListDto, UpdateAdminDto } from './admin.dto';

@Injectable()
export class AdminRepository {
  private key: string;
  constructor(
    private drizzle: DrizzleService,
    private configService: ConfigService,
    private audit: AuditService,
  ) {
    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;
  }

  /**
   * 관리자 계정 생성 — 업무 쓰기와 감사 행이 **한 트랜잭션**으로 커밋된다(계획 M7 D11·D12).
   *
   * 로그인과 달리 여기서는 fail-closed 가 옳다: 적재가 던지면 계정 생성이 롤백된다. 변경이
   * 사라질 뿐 접속은 살아 있으므로 조사자가 갇히지 않는다.
   *
   * **콜백 안에 `try` 도 `.catch(` 도 두지 않는다.** 둘 중 어느 쪽이든 적재 실패를 삼키면
   * 감사 행 없는 계정 변경이 커밋된다 — 이 트랜잭션의 존재 이유가 사라진다.
   */
  async create(dto: CreateAdminDto, ctx: AuditContext) {
    const hashedPassword = await hash(dto.admPassword, 10);

    await this.drizzle.transaction(async (tx) => {
      const [created] = await tx
        .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})`,
          admEmail:
            dto.admEmail !== ''
              ? sql`FN_ENCRYPT(${dto.admEmail}, ${this.key})`
              : null,
        })
        .$returningId();

      await this.audit.appendSecurity(tx, {
        action: AUDIT_SECURITY_ACTION.adminAccountCreate,
        outcome: 'COMMITTED',
        actor: ctx.actor,
        targetType: SECURITY_TARGET_TYPE.admin,
        targetId: created?.admId ?? null,
        targetCount: 1,
        //* 허용목록이다 — 이름·연락처는 `FN_ENCRYPT` 칸이고 비밀번호는 어느 형태로도 싣지 않는다.
        changes: { admLoginId: dto.admLoginId, athId: dto.athId },
        requestId: ctx.requestId,
        ip: ctx.ip,
        userAgent: ctx.userAgent,
      });
    });
  }

  async update(id: number, dto: UpdateAdminDto, ctx: AuditContext) {
    const hashedPassword =
      dto.admPassword !== '' ? await hash(dto.admPassword!, 10) : undefined;

    await this.drizzle.transaction(async (tx) => {
      //* 권한 그룹의 **이전 값**. 같은 트랜잭션에서 UPDATE 전에 읽는다 — 상승인지 하강인지는
      //*  전·후 쌍이 있어야 한 행으로 판정된다(금전 축의 `memberMoneyChanges` 와 같은 규약).
      //*  앞 행과 대조하는 방법은 배포 후 **첫** 변경에서 성립하지 않는다.
      //*  읽지 못하면(경쟁 삭제 등) `null` 이며 그것이 결측이라는 관측이다.
      const [before] = await tx
        .select({ athId: tnAdmin.athId })
        .from(tnAdmin)
        .where(eq(tnAdmin.admId, id))
        .limit(1);

      await tx
        .update(tnAdmin)
        .set({
          athId: dto.athId,
          admPassword: hashedPassword,
          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));

      await this.audit.appendSecurity(tx, {
        action: AUDIT_SECURITY_ACTION.adminAccountUpdate,
        outcome: 'COMMITTED',
        actor: ctx.actor,
        targetType: SECURITY_TARGET_TYPE.admin,
        targetId: id,
        targetCount: 1,
        //* 비밀번호는 **바뀌었는가**만 남긴다. 값도 해시도 싣지 않는다.
        //*  권한 그룹은 전·후를 함께 싣는다 — 이름·연락처는 `FN_ENCRYPT` 칸이라 전·후 어느
        //*  쪽도 싣지 않는다(PRD OQ7 의 대상이며 이 칸들은 그 판단 전까지 감사 밖이다).
        changes: {
          athIdBefore: before?.athId ?? null,
          athId: dto.athId,
          passwordChanged: hashedPassword !== undefined,
        },
        requestId: ctx.requestId,
        ip: ctx.ip,
        userAgent: ctx.userAgent,
      });
    });
  }

  /**
   * 관리자 계정 삭제 — **하드 삭제**다(계획 M7 D8). 지운 뒤에는 계정을 되찾을 원천이 없으므로
   * 감사 행이 `admLoginId` 를 싣는다.
   *
   * **계정당 한 행이다.** 한 행에 N 개를 담으면 로그인 ID 가 `audChanges` 의 1KB 상한에 걸려
   * 조용히 잘린다 — D8 이 겨냥한 바로 그 데이터가 D8 을 집행하는 형태 안에서 사라진다
   * (구현 창 리뷰 F5). 호출처는 한 곳이고 루프가 행 수를 정한다.
   */
  async delete(ids: number[], ctx: AuditContext) {
    await this.drizzle.transaction(async (tx) => {
      //* 지우기 **전에** 읽는다. 같은 tx 라 읽은 값과 지운 행이 어긋날 수 없다.
      const targets = await tx
        .select({ admId: tnAdmin.admId, admLoginId: tnAdmin.admLoginId })
        .from(tnAdmin)
        .where(inArray(tnAdmin.admId, ids));

      await tx.delete(tnAdmin).where(inArray(tnAdmin.admId, ids));

      for (const target of targets) {
        await this.audit.appendSecurity(tx, {
          action: AUDIT_SECURITY_ACTION.adminAccountDelete,
          outcome: 'COMMITTED',
          actor: ctx.actor,
          targetType: SECURITY_TARGET_TYPE.admin,
          targetId: target.admId,
          targetCount: 1,
          changes: { admLoginId: target.admLoginId },
          requestId: ctx.requestId,
          ip: ctx.ip,
          userAgent: ctx.userAgent,
        });
      }
    });
  }

  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,
        agcCorporateName: tnAgency.agcCorporateName,
        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;
      }
    }
  }
}