import {
  BadRequestException,
  forwardRef,
  Inject,
  Injectable,
  Logger,
} from '@nestjs/common';
import { CreateSlotDto, VoteCommentDto } from './dto/create-slot.dto';
import { UpdateSlotDto } from './dto/update-slot.dto';
import { PrismaService } from 'src/prisma/prisma.service';
import { SearchNearSlotDto, SearchSlot, SearchSlotByCursor } from './dto/search-slot.dto';
import { AuthPayload } from 'src/auth/decorators/auth.payload';
import { SLOT_BOOKING_EXISTS, SLOT_NOTFOUND } from 'src/share/eCode';
import { BookingSlotService } from '../booking-slot/booking-slot.service';
import { BookingType, Slot, SlotType } from '@prisma/client';
import { eUserType } from 'src/common/auth/type';
import { Prisma } from '@prisma/client';
import { removeVietnameseTones } from 'src/share/tool';
@Injectable()
export class SlotService {
  private readonly LOG = new Logger(SlotService.name);

  constructor(
    @Inject(forwardRef(() => BookingSlotService))
    private readonly bookingSlotService: BookingSlotService,
    private prisma: PrismaService,
  ) { }

  public async getSlot(slotId: number) {
    const slot = await this.prisma.slot.findUnique({
      where: { id: slotId },
    });
    if (!slot) {
      throw new BadRequestException(SLOT_NOTFOUND);
    }

    return slot;
  }

  async create(createSlotDto: CreateSlotDto, user: AuthPayload) {
    const tenantId = +user.id;
    const pricingPerHour = createSlotDto.pricingPerHour;
    delete createSlotDto.pricingPerHour;
    this.LOG.debug(
      `Create slot by ${user.type}: ${user.account} data: ${JSON.stringify(
        createSlotDto,
      )}`,
    );

    const dataSlot: Slot = {
      ...(<Slot>(<unknown>createSlotDto)),
      pricing_per_hour: pricingPerHour,
      alow_booking_type:
        BookingType[
        createSlotDto.alow_booking_type as keyof typeof BookingType
        ] || BookingType.All,
      slot_type:
        SlotType[createSlotDto.slot_type as keyof typeof SlotType] ||
        SlotType.Orther,
      empty: 0,
    };
    if (user.type == eUserType.TENANT) {
      dataSlot.tenantId = tenantId;
    } else {
      dataSlot.userId = tenantId;
    }

    // delete dataSlot['slotType'];
    // delete dataSlot['bookingType'];
    const slot = await this.prisma.slot.create({
      data: dataSlot,
    });

    this.LOG.debug(
      `Create done slot by tenent: ${user.account} data: ${JSON.stringify(
        slot,
      )}`,
    );
    return slot;
  }

  async findAll(query: SearchSlot, user: AuthPayload = null) {
    const { pageSize, pageNumber, sort, search, name } = query;
    const take = pageSize;
    const skip = (pageNumber - 1) * pageSize;

    let whereClause = ``; // Điều kiện mặc định
    const ORS = [];
    const ANDS = [];
    // ✅ Thêm điều kiện tìm kiếm theo name (không dấu)
    if (name.length) {
      const keySearch = removeVietnameseTones(name).split(' ').join('').toLowerCase();
      // const keySearch = name.split(' ').join('').toLowerCase()
      ORS.push(`replace(unaccent(name), ' ', '') ILIKE '%${keySearch}%'`);
    }

    // ✅ Thêm điều kiện tìm kiếm theo search (các cột address, city, ward, district)
    if (search.length) {
      const listCheck = ['address', 'city', 'ward', 'district'];
      const keySearch = removeVietnameseTones(search).split(' ').join('').toLowerCase();;
      // const keySearch = search.split(' ').join('').toLowerCase();
      for (const element of listCheck) {
        ORS.push(` replace(unaccent(${element}), ' ', '') ILIKE '%${keySearch}%'`);
      }
    }
    if (ORS.length) {
      whereClause += `(${ORS.join(' OR ')})`;
    }
    // ✅ Điều kiện theo user type
    if (user && user.type === eUserType.TENANT) {
      ANDS.push(`"tenantId" = ${+user.id}`);
    }

    if (user && user.type === eUserType.USER) {
      ANDS.push(`"userId" = ${+user.id}`);
    }

    if (ANDS.length) {
      whereClause += `(${ANDS.join(' AND ')})`;
    }
    // ✅ Sắp xếp (mặc định theo ID nếu không có `sort`)

    let orderByClause = '';
    const orders = [];
    if (sort) {
      for (const [key, value] of Object.entries(sort)) {
        orders.push(` ${key} ${value}`);
      }
      orderByClause += 'ORDER BY';
      orderByClause += orders.join(',');
    }
    // ✅ Truy vấn dữ liệu chính
    whereClause = whereClause.length ? `WHERE ${whereClause}` : '';
    const findSlotQuery = Prisma.sql`
        SELECT * FROM "Slot"
        ${Prisma.raw(whereClause)}
        ${Prisma.raw(orderByClause)}
        LIMIT ${Prisma.raw(`${take}`)}
        OFFSET ${Prisma.raw(`${skip}`)}
      `;
    this.LOG.debug(findSlotQuery.text);
    const slots = <any>await this.prisma.$queryRaw`${findSlotQuery}`;
    // ✅ Truy vấn tổng số bản ghi (không phân trang)
    const totalQuery = Prisma.sql`
        SELECT COUNT(*) FROM "Slot"
        ${Prisma.raw(whereClause)}
      `;
    this.LOG.debug(totalQuery.text);
    const totalSlot = <Array<any>>await this.prisma.$queryRaw`${totalQuery}`;

    // ✅ Lấy danh sách ID để đếm số lượng đặt chỗ
    const slotIds = slots.map((e) => e.id);
    const countBookingActiveByGroup =
      await this.bookingSlotService.countBookingActiveByGroup(slotIds);
    const slotData = await this.prisma.slot.findMany({
      where: {
        id: { in: slotIds },
      },
      orderBy: sort,
      include: {
        tenant: true,
        user: true,
      },
    });
    // ✅ Ghi log kết quả
    this.LOG.debug(
      `find by ${sort} ${search} result: ${JSON.stringify(
        slots,
      )} total: ${totalSlot}`,
    );
    return {
      slots: slotData.map((e) => {
        delete e?.user?.password;
        delete e?.user?.username;
        delete e?.tenant?.password_confirmation;
        delete e?.tenant?.password;
        return {
          ...e,
          free: e.total - (countBookingActiveByGroup[e.id] || 0),
          image: '',
        };
      }),
      total: Number.parseInt(totalSlot.length ? totalSlot[0].count : 0),
    };
  }

  async userFindNearByCoordinate(query: SearchNearSlotDto) {
    this.LOG.debug(`Find near: ${JSON.stringify(query)}`);
    const latitude = query.lat;
    const longitude = query.lng;
    const radius = query.radius;

    const whereQuery = `
      SELECT *
      FROM
        (SELECT *,
                slot.id as sId,
                (3959 * acos(cos(radians(${latitude})) * cos(radians(lat)) * cos(radians(lng) - radians(${longitude})) + sin(radians(${latitude})) * sin(radians(lat)))) AS range
        FROM "Slot" slot
      ) AS derivedTable  
      WHERE range < ${radius}
      ORDER BY range
  `;
    const countQuery = `
      SELECT COUNT(*) AS count
          FROM
            (SELECT *,
                    (3959 * acos(cos(radians(${latitude})) * cos(radians(lat)) * cos(radians(lng) - radians(${longitude})) + sin(radians(${latitude})) * sin(radians(lat)))) AS range
            FROM "Slot" slot
          ) AS derivedTable  
          WHERE range < ${radius}
    `;
    this.LOG.verbose(whereQuery);
    const slot: Array<any> = await this.prisma.$queryRawUnsafe(whereQuery);

    const count = await this.prisma.$queryRawUnsafe(countQuery);
    const slotIds = slot?.map((e) => e.id);
    const countBookingActiveByGroup =
      await this.bookingSlotService.countBookingActiveByGroup(slotIds);
    return {
      count: Number(count[0].count),
      slots: slot?.map((e) => ({
        ...e,
        id: e.sId || e.sid,
        free: e.total - (countBookingActiveByGroup[e.id] || 0),
        password: undefined,
        password_confirmation: undefined,
      })),
    };
  }

  async findOne(id: number) {
    const slot = await this.prisma.slot.findFirstOrThrow({
      include: {
        tenant: true,
        user: true,
      },
      where: {
        id,
      },
    });
    const vote = await this.prisma.vote.aggregate({
      _avg: {
        source: true,
      },
      where: { slot_id: id },
    });
    this.LOG.debug(`Find slot by id: ${id} ${JSON.stringify(slot)} `);
    return { ...slot, vote: vote._avg.source };
  }

  async update(id: number, updateSlotDto: UpdateSlotDto, tenant: AuthPayload) {
    const isTenant = tenant.type == eUserType.TENANT;
    const where = isTenant
      ? {
        tenantId: +tenant.id,
        id,
      }
      : {
        userId: +tenant.id,
        id,
      };
    const slot = await this.prisma.slot.findFirst({
      where: where,
    });
    if (!slot) {
      throw new BadRequestException(SLOT_NOTFOUND);
    }

    const result = await this.prisma.$transaction([
      this.prisma.slot.update({
        where: {
          id,
        },
        data: {
          address: updateSlotDto.address || slot.address,
          // total: updateSlotDto.total || slot.total,
          district: updateSlotDto.district || slot.district,
          ward: updateSlotDto.ward || slot.ward,
          city: updateSlotDto.city || slot.city,
          lat: updateSlotDto.lat || slot.lat,
          lng: updateSlotDto.lng || slot.lng,
          name: updateSlotDto.name || slot.name,
          images: updateSlotDto.images || slot.images,
          close: updateSlotDto.close || slot.close,
          open: updateSlotDto.open || slot.open,
          alow_booking_type:
            <BookingType>BookingType[updateSlotDto.alow_booking_type] ||
            slot.alow_booking_type,
          slot_type:
            <SlotType>SlotType[updateSlotDto.slot_type] || slot.slot_type,
          pricing_per_hour:
            updateSlotDto.pricingPerHour || slot.pricing_per_hour,
        },
      }),
    ]);

    this.LOG.debug(result);
    return `This action updates a #${id} slot`;
  }

  async remove(id: number, tenant: AuthPayload) {
    const slot = await this.getSlot(id);
    const count = await this.bookingSlotService.countBookingActive(slot.id);
    if (count) {
      throw new BadRequestException(SLOT_BOOKING_EXISTS);
    }

    await this.prisma.slot.deleteMany({
      where: {
        id,
        tenantId: +tenant.id,
      },
    });
    return `This action delete a #${id} slot`;
  }

  async voteSlot(
    id: number,
    voteCommentDto: VoteCommentDto,
    user: AuthPayload,
  ) {
    try {
      const slot = await this.getSlot(id);
      const vote = await this.prisma.vote.findFirst({
        where: {
          slot_id: slot.id,
          userId: +user.id,
        },
      });
      if (vote) {
        await this.prisma.vote.update({
          where: { id: vote.id },
          data: { source: voteCommentDto.source },
        });
      } else {
        await this.prisma.vote.create({
          data: {
            slot_id: slot.id,
            userId: +user.id,
            source: voteCommentDto.source,
          },
        });
      }
      return true;
    } catch (error) {
      this.LOG.error(error);
      throw error;
    }
  }

  async getVoteSlot(id: number, user: AuthPayload) {
    try {
      const slot = await this.getSlot(id);
      const vote = await this.prisma.vote.findFirst({
        where: {
          slot_id: slot.id,
          userId: +user.id,
        },
      });
      return vote;
    } catch (error) {
      this.LOG.error(error);
      throw error;
    }
  }

  async listByCursor(
    query: SearchSlotByCursor,
  ): Promise<{
    slots: Slot[];
    total: number;
  }> {

    const { pageSize, sort, search, cursorId, action } = query;
    const take = pageSize;

    let whereClause = ``; // Điều kiện mặc định
    if (cursorId) {
      if (action === 'next') {
        whereClause += `id > ${cursorId}`;
      } else if (action === 'prev') {
        whereClause += `id < ${cursorId}`;
      } else {
        throw new BadRequestException('Action not found');
      }
    }
    const ORS = [];
    const ANDS = [];


    if (search.length) {
      const listCheck = ['address', 'city', 'ward', 'district', 'name'];
      const keySearch = search.split(' ').join('').toLowerCase();
      for (const element of listCheck) {
        ORS.push(` replace(unaccent(${element}), ' ', '') ILIKE '%${keySearch}%'`);
      }
    }
    if (ORS.length) {
      whereClause += `(${ORS.join(' OR ')})`;
    }


    if (ANDS.length) {
      whereClause += `(${ANDS.join(' AND ')})`;
    }

    let orderByClause = '';
    const orders = [];
    if (sort) {
      for (const [key, value] of Object.entries(sort)) {
        orders.push(` ${key} ${value}`);
      }
      orderByClause += 'ORDER BY';
      orderByClause += orders.join(',');
    }


    whereClause = whereClause.length ? `WHERE ${whereClause}` : '';
    const findSlotQuery = Prisma.sql`
        SELECT * FROM "Slot"
        ${Prisma.raw(whereClause)}
        ${Prisma.raw(orderByClause)}
        LIMIT ${Prisma.raw(`${take}`)}
      `;
    this.LOG.debug(findSlotQuery.text);
    const slots = <any>await this.prisma.$queryRaw`${findSlotQuery}`;
    // ✅ Truy vấn tổng số bản ghi (không phân trang)
    const totalQuery = Prisma.sql`
        SELECT COUNT(*) FROM "Slot"
        ${Prisma.raw(whereClause)}
      `;
    this.LOG.debug(totalQuery.text);
    const totalSlot = <Array<any>>await this.prisma.$queryRaw`${totalQuery}`;

    // ✅ Lấy danh sách ID để đếm số lượng đặt chỗ
    const slotIds = slots.map((e) => e.id);
    const countBookingActiveByGroup =
      await this.bookingSlotService.countBookingActiveByGroup(slotIds);
    const slotData = await this.prisma.slot.findMany({
      where: {
        id: { in: slotIds },
      },
      orderBy: sort,
      include: {
        tenant: true,
        user: true,
      },
    });
    // ✅ Ghi log kết quả
    this.LOG.debug(
      `find by ${sort} ${search} result: ${JSON.stringify(
        slots,
      )} total: ${totalSlot}`,
    );
    return {
      slots: slotData.map((e) => {
        delete e?.user?.password;
        delete e?.user?.username;
        delete e?.tenant?.password_confirmation;
        delete e?.tenant?.password;
        return {
          ...e,
          free: e.total - (countBookingActiveByGroup[e.id] || 0),
          image: '',
        };
      }),
      total: Number.parseInt(totalSlot.length ? totalSlot[0].count : 0),
    };
  }
}
