import { DrizzleService } from '@lib/drizzle/src/drizzle.service';
import { Injectable } from '@nestjs/common';
import dayjs from 'dayjs';
import {
  and,
  asc,
  count,
  countDistinct,
  eq,
  gte,
  lte,
  or,
  sql,
  sum,
} from 'drizzle-orm';
import {
  tnAgency,
  tnMember,
  tnOrder,
  tnOrderDetail,
  tnPayment,
  tnSalesAgency,
  tnSalesAgencyChannel,
  tnSubscribe,
} from 'drizzle/schema';
import { MemberType } from '../fc/fc.entity';
import { OrderDetailState } from '../order/order-detail/order-detail.entity';
import { PaymentMethod, PaymentState } from '../payment/payment.entity';

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

  async getAllMemberQty() {
    const [data] = await this.drizzle.select({ count: count() }).from(tnMember);
    return data;
  }

  async getNewMemberQty(startDate: string, endDate: string) {
    const [data] = await this.drizzle
      .select({ count: count() })
      .from(tnMember)
      .where(
        and(
          gte(tnMember.memCreatedAt, startDate),
          lte(tnMember.memCreatedAt, endDate),
        ),
      );
    return data;
  }

  async getAllUsedOrderDetailQty() {
    const [data] = await this.drizzle
      .select({ count: count() })
      .from(tnOrderDetail)
      .where(eq(tnOrderDetail.oddState, OrderDetailState.분석완료));

    return data;
  }

  async getUsedOrderDetailQty(startDate: string, endDate: string) {
    const [data] = await this.drizzle
      .select({ count: count() })
      .from(tnOrderDetail)
      .where(
        and(
          eq(tnOrderDetail.oddState, OrderDetailState.분석완료),
          gte(tnOrderDetail.oddCreatedAt, startDate),
          lte(tnOrderDetail.oddCreatedAt, endDate),
        ),
      );
    return data;
  }

  async getAllPay(state: 'pay' | 'cancel') {
    const where =
      state === 'pay'
        ? eq(tnPayment.pmtState, PaymentState.결제완료)
        : eq(tnPayment.pmtState, PaymentState.결제취소);

    const [data] = await this.drizzle
      .select({
        count: count(),
        totalAmount: sum(tnPayment.pmtAmount).mapWith(Number),
      })
      .from(tnPayment)
      .where(where);

    return data;
  }

  async getPayMonth(
    state: 'pay' | 'cancel',
    startDate: string,
    endDate: string,
  ) {
    const where =
      state === 'pay'
        ? and(
            eq(tnPayment.pmtState, PaymentState.결제완료),
            gte(tnPayment.pmtCreatedAt, startDate),
            lte(tnPayment.pmtCreatedAt, endDate),
          )
        : and(
            eq(tnPayment.pmtState, PaymentState.결제취소),
            gte(tnPayment.pmtCanceledAt, startDate),
            lte(tnPayment.pmtCanceledAt, endDate),
          );
    const [data] = await this.drizzle
      .select({
        count: count(),
        totalAmount: sum(tnPayment.pmtAmount).mapWith(Number),
      })
      .from(tnPayment)
      .where(where);
    return data;
  }

  async getPayDaily(
    state: 'pay' | 'cancel',
    startDate: string,
    endDate: string,
  ) {
    const formatDate =
      state === 'pay'
        ? sql`DATE_FORMAT(${tnPayment.pmtCreatedAt}, '%Y-%m-%d')`
        : sql`DATE_FORMAT(${tnPayment.pmtCanceledAt}, '%Y-%m-%d')`;

    const where =
      state === 'pay'
        ? and(
            eq(tnPayment.pmtState, PaymentState.결제완료),
            gte(tnPayment.pmtCreatedAt, startDate),
            lte(tnPayment.pmtCreatedAt, endDate),
          )
        : and(
            eq(tnPayment.pmtState, PaymentState.결제취소),
            gte(tnPayment.pmtCanceledAt, startDate),
            lte(tnPayment.pmtCanceledAt, endDate),
          );
    return await this.drizzle
      .select({
        date: formatDate.mapWith(String),
        count: count(),
        totalAmount: sum(tnPayment.pmtAmount).mapWith(Number),
      })
      .from(tnPayment)
      .where(where)
      .groupBy(formatDate)
      .orderBy(formatDate);
  }

  //* 영업대리점 관리자를 위한 통계 작업 start(추후에 가능하다면 Refactoring 필요)
  async getSalesAllMemberQty(sagId: number) {
    const [data] = await this.drizzle
      .select({ count: countDistinct(tnMember.memId) })
      .from(tnMember)
      .leftJoin(tnAgency, eq(tnAgency.agcId, tnMember.agcId))
      .leftJoin(
        tnSalesAgencyChannel,
        eq(tnSalesAgencyChannel.sacCode, tnMember.sacCode),
      )
      .innerJoin(
        tnSalesAgency,
        or(
          eq(tnSalesAgency.sagId, tnSalesAgencyChannel.sagId),
          eq(tnSalesAgency.sagId, tnAgency.sagId),
        ),
      )
      .where(eq(tnSalesAgency.sagId, sagId))
      .groupBy(tnSalesAgency.sagId)
      .orderBy(tnSalesAgency.sagId);

    return data;
  }

  async getSalesNewMemberQty(
    startDate: string,
    endDate: string,
    sagId: number,
  ) {
    const [data] = await this.drizzle
      .select({ count: count() })
      .from(tnMember)
      .leftJoin(tnAgency, eq(tnAgency.agcId, tnMember.agcId))
      .leftJoin(
        tnSalesAgencyChannel,
        eq(tnSalesAgencyChannel.sacCode, tnMember.sacCode),
      )
      .innerJoin(
        tnSalesAgency,
        or(
          eq(tnSalesAgency.sagId, tnSalesAgencyChannel.sagId),
          eq(tnSalesAgency.sagId, tnAgency.sagId),
        ),
      )
      .where(
        and(
          gte(tnMember.memCreatedAt, startDate),
          lte(tnMember.memCreatedAt, endDate),
          eq(tnSalesAgency.sagId, sagId),
        ),
      );
    return data;
  }

  async getSalesAllUsedOrderDetailQty(sagId: number) {
    const [data] = await this.drizzle
      .select({ count: count() })
      .from(tnOrderDetail)
      .leftJoin(tnOrder, eq(tnOrder.ordId, tnOrderDetail.ordId))
      .leftJoin(tnPayment, eq(tnPayment.pmtId, tnOrder.pmtId))
      .leftJoin(tnSubscribe, eq(tnSubscribe.subId, tnPayment.subId))
      .leftJoin(tnMember, eq(tnMember.memId, tnSubscribe.memId))
      .leftJoin(tnAgency, eq(tnAgency.agcId, tnMember.agcId))
      .leftJoin(
        tnSalesAgencyChannel,
        eq(tnSalesAgencyChannel.sacCode, tnMember.sacCode),
      )
      .innerJoin(
        tnSalesAgency,
        or(
          eq(tnSalesAgency.sagId, tnSalesAgencyChannel.sagId),
          eq(tnSalesAgency.sagId, tnAgency.sagId),
        ),
      )
      .where(
        and(
          eq(tnOrderDetail.oddState, OrderDetailState.분석완료),
          eq(tnSalesAgency.sagId, sagId),
        ),
      );

    return data;
  }

  async getSalesUsedOrderDetailQty(
    startDate: string,
    endDate: string,
    sagId: number,
  ) {
    const [data] = await this.drizzle
      .select({ count: count() })
      .from(tnOrderDetail)
      .leftJoin(tnOrder, eq(tnOrder.ordId, tnOrderDetail.ordId))
      .leftJoin(tnPayment, eq(tnPayment.pmtId, tnOrder.pmtId))
      .leftJoin(tnSubscribe, eq(tnSubscribe.subId, tnPayment.subId))
      .leftJoin(tnMember, eq(tnMember.memId, tnSubscribe.memId))
      .leftJoin(tnAgency, eq(tnAgency.agcId, tnMember.agcId))
      .leftJoin(
        tnSalesAgencyChannel,
        eq(tnSalesAgencyChannel.sacCode, tnMember.sacCode),
      )
      .innerJoin(
        tnSalesAgency,
        or(
          eq(tnSalesAgency.sagId, tnSalesAgencyChannel.sagId),
          eq(tnSalesAgency.sagId, tnAgency.sagId),
        ),
      )
      .where(
        and(
          eq(tnOrderDetail.oddState, OrderDetailState.분석완료),
          gte(tnOrderDetail.oddCreatedAt, startDate),
          lte(tnOrderDetail.oddCreatedAt, endDate),
          eq(tnSalesAgency.sagId, sagId),
        ),
      );
    return data;
  }

  async getSalesAllPay(state: 'pay' | 'cancel', sagId: number) {
    const where =
      state === 'pay'
        ? and(
            eq(tnPayment.pmtState, PaymentState.결제완료),
            eq(tnSalesAgency.sagId, sagId),
          )
        : and(
            eq(tnPayment.pmtState, PaymentState.결제취소),
            eq(tnSalesAgency.sagId, sagId),
          );

    const [data] = await this.drizzle
      .select({
        count: count(),
        totalAmount: sum(tnPayment.pmtAmount).mapWith(Number),
      })
      .from(tnPayment)
      .leftJoin(tnSubscribe, eq(tnSubscribe.subId, tnPayment.subId))
      .leftJoin(tnMember, eq(tnMember.memId, tnSubscribe.memId))
      .leftJoin(tnAgency, eq(tnAgency.agcId, tnMember.agcId))
      .leftJoin(
        tnSalesAgencyChannel,
        eq(tnSalesAgencyChannel.sacCode, tnMember.sacCode),
      )
      .innerJoin(
        tnSalesAgency,
        or(
          eq(tnSalesAgency.sagId, tnSalesAgencyChannel.sagId),
          eq(tnSalesAgency.sagId, tnAgency.sagId),
        ),
      )
      .where(where);

    return data;
  }

  async getSalesPayMonth(
    state: 'pay' | 'cancel',
    startDate: string,
    endDate: string,
    sagId: number,
  ) {
    const where =
      state === 'pay'
        ? and(
            eq(tnPayment.pmtState, PaymentState.결제완료),
            gte(tnPayment.pmtCreatedAt, startDate),
            lte(tnPayment.pmtCreatedAt, endDate),
            eq(tnSalesAgency.sagId, sagId),
          )
        : and(
            eq(tnPayment.pmtState, PaymentState.결제취소),
            gte(tnPayment.pmtCanceledAt, startDate),
            lte(tnPayment.pmtCanceledAt, endDate),
            eq(tnSalesAgency.sagId, sagId),
          );

    const [data] = await this.drizzle
      .select({
        count: count(),
        totalAmount: sum(tnPayment.pmtAmount).mapWith(Number),
      })
      .from(tnPayment)
      .leftJoin(tnSubscribe, eq(tnSubscribe.subId, tnPayment.subId))
      .leftJoin(tnMember, eq(tnMember.memId, tnSubscribe.memId))
      .leftJoin(tnAgency, eq(tnAgency.agcId, tnMember.agcId))
      .leftJoin(
        tnSalesAgencyChannel,
        eq(tnSalesAgencyChannel.sacCode, tnMember.sacCode),
      )
      .innerJoin(
        tnSalesAgency,
        or(
          eq(tnSalesAgency.sagId, tnSalesAgencyChannel.sagId),
          eq(tnSalesAgency.sagId, tnAgency.sagId),
        ),
      )
      .where(where);
    return data;
  }

  async getSalesPayDaily(
    state: 'pay' | 'cancel',
    startDate: string,
    endDate: string,
    sagId: number,
  ) {
    const formatDate =
      state === 'pay'
        ? sql`DATE_FORMAT(${tnPayment.pmtCreatedAt}, '%Y-%m-%d')`
        : sql`DATE_FORMAT(${tnPayment.pmtCanceledAt}, '%Y-%m-%d')`;

    const where =
      state === 'pay'
        ? and(
            eq(tnPayment.pmtState, PaymentState.결제완료),
            gte(tnPayment.pmtCreatedAt, startDate),
            lte(tnPayment.pmtCreatedAt, endDate),
            eq(tnSalesAgency.sagId, sagId),
          )
        : and(
            eq(tnPayment.pmtState, PaymentState.결제취소),
            gte(tnPayment.pmtCanceledAt, startDate),
            lte(tnPayment.pmtCanceledAt, endDate),
            eq(tnSalesAgency.sagId, sagId),
          );

    return await this.drizzle
      .select({
        date: formatDate.mapWith(String),
        count: count(),
        totalAmount: sum(tnPayment.pmtAmount).mapWith(Number),
      })
      .from(tnPayment)
      .leftJoin(tnSubscribe, eq(tnSubscribe.subId, tnPayment.subId))
      .leftJoin(tnMember, eq(tnMember.memId, tnSubscribe.memId))
      .leftJoin(tnAgency, eq(tnAgency.agcId, tnMember.agcId))
      .leftJoin(
        tnSalesAgencyChannel,
        eq(tnSalesAgencyChannel.sacCode, tnMember.sacCode),
      )
      .innerJoin(
        tnSalesAgency,
        or(
          eq(tnSalesAgency.sagId, tnSalesAgencyChannel.sagId),
          eq(tnSalesAgency.sagId, tnAgency.sagId),
        ),
      )
      .where(where)
      .groupBy(formatDate)
      .orderBy(formatDate);
  }
  //* 영업대리점 관리자를 위한 통계 작업 end

  async getSignUpStatistics(
    dateType: 'month' | 'day',
    startDate: string,
    endDate: string,
    memType: MemberType,
  ) {
    const formatDate = dateType === 'month' ? '%Y-%m' : '%Y-%m-%d';

    // 전월/전일 대비 계산을 위하여 전월/전일 날짜 계산
    const beforeDate = dayjs(startDate)
      .subtract(1, dateType)
      .format('YYYY-MM-DD HH:mm:ss');

    const signupStatistics = await this.drizzle
      .select({
        date: sql<string>`DATE`.as(dateType),
        signupCount: sql<number>`SUM(signupCount)`.as('signupCount'),
        resignCount: sql<number>`SUM(resignCount)`.as('resignCount'),
      })
      .from(
        sql`
        (
          SELECT
            DATE_FORMAT(${tnMember.memCreatedAt}, ${formatDate}) AS DATE,
            COUNT(*) AS signupCount,
            0 AS resignCount
          FROM ${tnMember}
          WHERE
            ${tnMember.memCreatedAt} BETWEEN ${beforeDate} AND ${endDate}
            AND ${tnMember.memType} = ${memType}
          GROUP BY DATE_FORMAT(${tnMember.memCreatedAt}, ${formatDate})

          UNION ALL

          SELECT
            DATE_FORMAT(${tnMember.memResignedAt}, ${formatDate}) AS DATE,
            0 AS signupCount,
            COUNT(*) AS resignCount
          FROM ${tnMember}
          WHERE
            ${tnMember.memResignedAt} BETWEEN ${beforeDate} AND ${endDate}
            AND ${tnMember.memType} = ${memType}
          GROUP BY DATE_FORMAT(${tnMember.memResignedAt}, ${formatDate})
        ) AS signUp
        `,
      )
      .groupBy(sql`DATE`)
      .orderBy(sql`DATE ASC`);

    return signupStatistics.map((statistic) => ({
      date: statistic.date,
      signupCount: Number(statistic.signupCount ?? 0),
      resignCount: Number(statistic.resignCount ?? 0),
    }));
  }

  async getSignAllTotalCount() {
    const [totalAllSignUpCount] = await this.drizzle
      .select({
        count: count(),
      })
      .from(tnMember);

    const [totalAllResignCount] = await this.drizzle
      .select({
        count: count(),
      })
      .from(tnMember)
      .where(eq(tnMember.memIsResigned, 1));

    return {
      totalAllSignUp: totalAllSignUpCount.count ?? 0,
      totalAllResign: totalAllResignCount.count ?? 0,
    };
  }

  async getRevenueStatistics(
    dateType: 'month' | 'day',
    startDate: string,
    endDate: string,
    memType: MemberType,
  ) {
    const formatDate = dateType === 'month' ? '%Y-%m' : '%Y-%m-%d';

    const paymentMethodWhere =
      memType === MemberType.일반FC
        ? sql`${tnPayment.pmtMethod} != ${PaymentMethod.무료}`
        : sql`1=1`;

    return await this.drizzle
      .select({
        date: sql<string>`DATE`.as(dateType),
        payAmount: sql<number>`SUM(payAmount)`.as('payAmount'),
        cancelAmount: sql<number>`SUM(cancelAmount)`.as('cancelAmount'),
      })
      .from(
        sql`
        (
          SELECT 
            DATE_FORMAT(${tnPayment.pmtApprovedAt}, ${formatDate}) AS DATE,
            CASE 
                WHEN ${tnPayment.pmtState} = ${PaymentState.결제완료} 
                THEN ${tnPayment.pmtAmount} ELSE 0 
              END AS payAmount,
            0 AS cancelAmount
          FROM ${tnPayment}
          JOIN ${tnSubscribe} ON ${tnPayment.subId} = ${tnSubscribe.subId}
          JOIN ${tnMember} ON ${tnSubscribe.memId} = ${tnMember.memId} AND ${tnMember.memType} = ${memType}
          WHERE 
            ${tnPayment.pmtApprovedAt} BETWEEN ${startDate} AND ${endDate}
            AND ${paymentMethodWhere}
    
          UNION ALL
    
          SELECT 
            DATE_FORMAT(${tnPayment.pmtCanceledAt}, ${formatDate}) AS DATE,
            0 AS payAmount,
            CASE 
                WHEN ${tnPayment.pmtState} = ${PaymentState.결제취소} 
                THEN ${tnPayment.pmtAmount} ELSE 0 
              END AS cancelAmount
          FROM ${tnPayment}
          JOIN ${tnSubscribe} ON ${tnPayment.subId} = ${tnSubscribe.subId}
          JOIN ${tnMember} ON ${tnSubscribe.memId} = ${tnMember.memId} AND ${tnMember.memType} = ${memType}
          WHERE 
            ${tnPayment.pmtCanceledAt} BETWEEN ${startDate} AND ${endDate}
            AND ${paymentMethodWhere}
        ) AS stats
      `,
      )
      .groupBy(sql`DATE`)
      .orderBy(sql`DATE ASC`);
  }

  async getAnalysisStatistics(
    dateType: 'month' | 'day',
    startDate: string,
    endDate: string,
  ) {
    const formatDate = dateType === 'month' ? '%Y-%m' : '%Y-%m-%d';

    const analysisStatistics = await this.drizzle
      .select({
        date: sql<string>`DATE_FORMAT(${tnOrderDetail.oddCreatedAt}, ${formatDate})`.as(
          dateType,
        ),
        total: count(),
        successTotal:
          sql<number>`SUM(CASE WHEN ${tnOrderDetail.oddState} = ${OrderDetailState.분석완료} THEN 1 ELSE 0 END)`.as(
            'successTotal',
          ),
        failedTotal:
          sql<number>`SUM(CASE WHEN ${tnOrderDetail.oddState} != ${OrderDetailState.분석완료} THEN 1 ELSE 0 END)`.as(
            'failedTotal',
          ),
        authFailed:
          sql<number>`SUM(CASE WHEN ${tnOrderDetail.oddState} = ${OrderDetailState.인증실패} THEN 1 ELSE 0 END)`.as(
            'authFailed',
          ),
        analysisFailed:
          sql<number>`SUM(CASE WHEN ${tnOrderDetail.oddState} = ${OrderDetailState.분석실패} THEN 1 ELSE 0 END)`.as(
            'analysisFailed',
          ),
        etc: sql<number>`SUM(CASE WHEN ${tnOrderDetail.oddState} IN (
            ${OrderDetailState.인증요청중},
            ${OrderDetailState.자료수집완료},
            ${OrderDetailState.추가인증요청중},
            ${OrderDetailState.자료분석중}
          ) THEN 1 ELSE 0 END)`.as('etc'),
      })
      .from(tnOrderDetail)
      .where(
        and(
          gte(tnOrderDetail.oddCreatedAt, startDate),
          lte(tnOrderDetail.oddCreatedAt, endDate),
        ),
      )
      .groupBy(sql`DATE_FORMAT(${tnOrderDetail.oddCreatedAt}, ${formatDate})`)
      .orderBy(asc(tnOrderDetail.oddCreatedAt));

    return analysisStatistics.map((statistic) => ({
      date: statistic.date,
      total: Number(statistic.total ?? 0),
      successTotal: Number(statistic.successTotal ?? 0),
      failedTotal: Number(statistic.failedTotal ?? 0),
      authFailed: Number(statistic.authFailed ?? 0),
      analysisFailed: Number(statistic.analysisFailed ?? 0),
      etc: Number(statistic.etc ?? 0),
    }));
  }

  async getStatisticsForChatbot(
    dateType: 'month' | 'day',
    startDate: string,
    endDate: string,
  ) {
    const signupStatistics = await this.getSignUpStatistics(
      dateType,
      startDate,
      endDate,
      MemberType.일반FC, //TODO 추구 수정 필수!!
    );
    const analysisStatistics = await this.getAnalysisStatistics(
      dateType,
      startDate,
      endDate,
    );
    const revenueStatistics = await this.getRevenueStatistics(
      dateType,
      startDate,
      endDate,
      MemberType.일반FC, //TODO 추후 수정 필수!!
    );

    return { signupStatistics, analysisStatistics, revenueStatistics };
  }
}