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, ne } 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';
// Para guardar la imagen
import { writeFile, mkdir, unlink, rmdir, rm } from 'fs/promises';
import { join } from 'path';

@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}%`),
        ), 
        and(
          eq(products.userId, id), 
          ne(products.status, 'ELIMINADO')
        )
      )
    } else {
      searchCondition = and(
        eq(products.userId, id), 
        ne(products.status, 'ELIMINADO')
      )
    }

    // 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,
        gallery: products.gallery,
        userId: products.userId
      })
      .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}%`)
        ),
        and(ne(viewProductsStore.status, 'BORRADOR'), ne(viewProductsStore.status, 'ELIMINADO'))
      )
    } else if (search) {
      or(
        like(viewProductsStore.title, `%${search}%`),
        like(viewProductsStore.description, `%${search}%`)
      )
    } else if (isStore) {
      searchCondition = and(ne(viewProductsStore.status, 'BORRADOR'), ne(viewProductsStore.status, 'ELIMINADO'))
    }

    // 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: number): 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,
        gallery: viewProductsStore.gallery,
        stock: viewProductsStore.stock,
        status: viewProductsStore.status,
        userId: viewProductsStore.userId,
        fullname: viewProductsStore.fullname,
        email: viewProductsStore.email,
        phone: viewProductsStore.phone
      })
      .from(viewProductsStore)
      .where(and(eq(viewProductsStore.id, id), ne(viewProductsStore.status, 'ELIMINADO')));

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

    return find[0];
  }

  // Rest of the service remains the same
  async create(
    file: Express.Multer.File[],
    createProductDto: CreateProductDto,
    userId: number,
  ): Promise<any> {

    let gallery: string[] = [];

    
    await Promise.all(file.map(async (f, index) => {
      const fileName = `${index + 1}-${f.originalname}`;
      gallery.push(fileName);
    }));
    
    // console.log(gallery);


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

      const productValue = {
        title: createProductDto.title,
        description: createProductDto.description,
        price: createProductDto.price,
        address: createProductDto.address,
        status: createProductDto.status,
        urlImg: gallery[0],
        stock: createProductDto.stock,
        userId: userId,
        gallery: gallery
      }
      // console.log(productValue);
      

      const [newProduct] = await tx
        .insert(products)
        .values(productValue)
        .returning();

        const productId = newProduct.id;
        const picturesPath = join(__dirname, '..', '..', '..', '..', 'web', 'public', 'uploads', 'inventory',userId.toString() , productId.toString());
        // Crea el directorio si no existe
        await mkdir(picturesPath, { recursive: true });

        await Promise.all(file.map(async (f, index) => {
          const fileName = `${index + 1}-${f.originalname}`;
          const filePath = join(picturesPath, fileName);
          await writeFile(filePath, f.buffer);
        }));

      return newProduct
    })
  }

  async update(file: Express.Multer.File[], updateProductDto: UpdateProductDto, userId: number): Promise<Product> {
    const productId = parseInt(updateProductDto.id);

    // Check if exists
    await this.findOne(productId);

    let gallery: string[] = [];

    // check if product exist
    if (file && file.length > 0) {
      // Construye la ruta al directorio de imágenes.
      const picturesPath = join(__dirname, '..', '..', '..', '..', 'web', 'public', 'uploads', 'inventory', userId.toString() , productId.toString());
      
      try {
        // Borra el directorio y todos sus contenidos de forma recursiva y forzada.
        await rm(picturesPath, { recursive: true, force: true });
      } catch (error) {
        // Es buena práctica manejar el error, aunque `force: true` lo hace menos probable.
        // Podrías registrar el error, pero no detener la ejecución.
        console.error(`No se pudo eliminar el directorio ${picturesPath}:`, error);
      }
      // Crea el directorio si no existe (ya que lo acabamos de borrar o no existía).
      await mkdir(picturesPath, { recursive: true });

      // Usamos `Promise.all` para manejar las operaciones asíncronas de forma correcta.
      await Promise.all(file.map(async (f, index) => {
        const fileName = `${index + 1}-${f.originalname}`;
        gallery.push(fileName);
        const filePath = join(picturesPath, fileName);
        await writeFile(filePath, f.buffer);
      }));
    }

    // 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 (file && file.length > 0) updateData.gallery = gallery;
    if (file && file.length > 0) updateData.urlImg = gallery[0];

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

  async remove(productId: number, userId: number): Promise<{ message: string }> {
    // const picturesPath = join(__dirname, '..', '..', '..', '..', 'web', 'public', 'uploads', 'inventory', userId.toString() , productId.toString());
      
    // try {
    //   // Borra el directorio y todos sus contenidos de forma recursiva y forzada.
    //   await rm(picturesPath, { recursive: true, force: true });
    // } catch (error) {
    //   console.error(`No se pudo eliminar el directorio ${picturesPath}:`, error);
    // }

    // Check if exists
    await this.findOne(productId);

    // Delete user (this will cascade delete related records due to foreign key constraints)
    // await this.drizzle.delete(products).where(eq(products.id, productId));
    await this.drizzle.update(products).set({ status: 'ELIMINADO' }).where(eq(products.id, productId));

    return { message: 'Product deleted successfully' };
  }
}

