import { DRIZZLE_PROVIDER } from 'src/database/drizzle-provider';
import { Inject, Injectable, HttpException, HttpStatus } from '@nestjs/common';
import { NodePgDatabase } from 'drizzle-orm/node-postgres';
import * as schema from 'src/database/index';
import { products, viewProductsStore } from 'src/database/index';
import { eq, like, or, SQL, sql, and, not } from 'drizzle-orm';
import { CreateProductDto } from './dto/create-product.dto';
import { UpdateProductDto } from './dto/update-product.dto';
import { Product, Store, Inventory } from './entities/inventory.entity';
import { PaginationDto } from '../../common/dto/pagination.dto';

@Injectable()
export class InventoryService {
  constructor(
    @Inject(DRIZZLE_PROVIDER) private drizzle: NodePgDatabase<typeof schema>,
  ) { }

  async findAllByUserId(id: number, paginationDto?: PaginationDto): Promise<{ data: Inventory[], meta: any }> {
    const { page = 1, limit = 10, search = '', sortBy = 'id', sortOrder = 'asc' } = paginationDto || {};

    // Calculate offset
    const offset = (page - 1) * limit;

    // Build search condition
    let searchCondition: SQL<unknown> | undefined;

    if (search) {
      searchCondition = and(or(
        like(products.title, `%${search}%`),
        like(products.description, `%${search}%`),
      ), eq(products.userId, id));
    }else{
      searchCondition = eq(products.userId, id);
    }

    // Build sort condition
    const orderBy = sortOrder === 'asc'
      ? sql`${products[sortBy as keyof typeof products]} asc`
      : sql`${products[sortBy as keyof typeof products]} desc`;

    // Get total count for pagination
    const totalCountResult = await this.drizzle
      .select({ count: sql<number>`count(*)` })
      .from(products)
      .where(searchCondition);

    const totalCount = Number(totalCountResult[0].count);
    const totalPages = Math.ceil(totalCount / limit);

    // Get paginated data
    const data = await this.drizzle
      .select({
        id: products.id,
        title: products.title,
        description: products.description,
        address: products.address,
        price: products.price,
        stock: products.stock,
        status: products.status,
        urlImg: products.urlImg
      })
      .from(products)
      .where(searchCondition)
      .orderBy(orderBy)
      .limit(limit)
      .offset(offset);

    // Build pagination metadata
    const meta = {
      page,
      limit,
      totalCount,
      totalPages,
      hasNextPage: page < totalPages,
      hasPreviousPage: page > 1,
      nextPage: page < totalPages ? page + 1 : null,
      previousPage: page > 1 ? page - 1 : null,
    };
    return { data, meta };
  }

  async findAll(paginationDto?: PaginationDto, isStore: boolean = false): Promise<{ data: Store[], meta: any }> {
    const { page = 1, limit = 10, search = '', sortBy = 'id', sortOrder = 'asc' } = paginationDto || {};

    // Calculate offset
    const offset = (page - 1) * limit;

    // Build search condition
    let searchCondition: SQL<unknown> | undefined;
    if (search && isStore) {
      searchCondition = and(
        or(
          like(viewProductsStore.title, `%${search}%`),
          like(viewProductsStore.description, `%${search}%`)
        ), 
        or(eq(viewProductsStore.status, 'PUBLICADO'), eq(viewProductsStore.status, 'AGOTADO'))
      )
    } else if(search){
      or(
        like(viewProductsStore.title, `%${search}%`),
        like(viewProductsStore.description, `%${search}%`)
      )
    } else if(isStore){
      searchCondition = or(eq(viewProductsStore.status, 'PUBLICADO'), eq(viewProductsStore.status, 'AGOTADO'))
    }

    // Build sort condition
    const orderBy = sortOrder === 'asc'
      ? sql`${viewProductsStore[sortBy as keyof typeof viewProductsStore]} asc`
      : sql`${viewProductsStore[sortBy as keyof typeof viewProductsStore]} desc`;

    // Get total count for pagination
    const totalCountResult = await this.drizzle
      .select({ count: sql<number>`count(*)` })
      .from(viewProductsStore)
      .where(searchCondition);

    const totalCount = Number(totalCountResult[0].count);
    const totalPages = Math.ceil(totalCount / limit);

    // Get paginated data
    const data = await this.drizzle
      .select({
        id: viewProductsStore.id,
        title: viewProductsStore.title,
        description: viewProductsStore.description,
        price: viewProductsStore.price,
        address: viewProductsStore.address,
        urlImg: viewProductsStore.urlImg,
        stock: viewProductsStore.stock,
        status: viewProductsStore.status,
        userId: viewProductsStore.userId,
        fullname: viewProductsStore.fullname,
        email: viewProductsStore.email,
        phone: viewProductsStore.phone
      })
      .from(viewProductsStore)
      .where(searchCondition)
      .orderBy(orderBy)
      .limit(limit)
      .offset(offset);

    // Build pagination metadata
    const meta = {
      page,
      limit,
      totalCount,
      totalPages,
      hasNextPage: page < totalPages,
      hasPreviousPage: page > 1,
      nextPage: page < totalPages ? page + 1 : null,
      previousPage: page > 1 ? page - 1 : null,
    };
    return { data, meta };
  }

  async findOne(id: string): Promise<Product> {
    const find = await this.drizzle
      .select({
        id: viewProductsStore.id,
        title: viewProductsStore.title,
        description: viewProductsStore.description,
        price: viewProductsStore.price,
        address: viewProductsStore.address,
        urlImg: viewProductsStore.urlImg,
        stock: viewProductsStore.stock,
        status: viewProductsStore.status,
        userId: viewProductsStore.userId,
        fullname: viewProductsStore.fullname,
        email: viewProductsStore.email,
        phone: viewProductsStore.phone
      })
      .from(viewProductsStore)
      .where(eq(viewProductsStore.id, parseInt(id)));

    if (find.length === 0) {
      throw new HttpException('Product does not exist', HttpStatus.BAD_REQUEST);
    }

    return find[0];
  }

  // Rest of the service remains the same
  async create(
    createProductDto: CreateProductDto
  ): Promise<any> {


    // Start a transaction
    return await this.drizzle.transaction(async (tx) => {

      const [newProduct] = await tx
        .insert(products)
        .values({
          title: createProductDto.title,
          description: createProductDto.description,
          price: createProductDto.price,
          address: createProductDto.address,
          urlImg: createProductDto.urlImg,
          stock: createProductDto.stock,
          status: createProductDto.status,
          userId: createProductDto.userId
        })
        .returning();
        return newProduct
    });
  }

  async update(id: string, updateProductDto: UpdateProductDto): Promise<Product> {
    const productId = parseInt(id);
    console.log(updateProductDto);
    
    // Check if exists
    await this.findOne(id);

    // Prepare update data
    const updateData: any = {};
    if (updateProductDto.title) updateData.title = updateProductDto.title;
    if (updateProductDto.description) updateData.description = updateProductDto.description;
    if (updateProductDto.price) updateData.price = updateProductDto.price;
    if (updateProductDto.address) updateData.address = updateProductDto.address;
    if (updateProductDto.status) updateData.status = updateProductDto.status;
    if (updateProductDto.stock) updateData.stock = updateProductDto.stock;
    if (updateProductDto.urlImg) updateData.urlImg = updateProductDto.urlImg;

    const [updatedProduct] = await this.drizzle.update(products).set(updateData).where(eq(products.id, productId)).returning();
    return updatedProduct
    // Return updated user
    // return this.findOne(id);
  }


  // async remove(id: string): Promise<{ message: string, data: User }> {
  //   const userId = parseInt(id);

  //   // Check if user exists
  //   const user = await this.findOne(id);

  //   // Delete user (this will cascade delete related records due to foreign key constraints)
  //   // await this.drizzle.delete(users).where(eq(users.id, userId));
  //   await this.drizzle.update(users).set({ isActive: false }).where(eq(users.id, userId));

  //   return { message: 'User deleted successfully', data: user };
  // }
}

