import { db } from '../db/db_index';
import { productInfo, units, specialDeals, productTags, productReviews, productGroupInfo, productGroupMembership } from '../db/schema';
import { eq, and, inArray, desc, sql, asc } from 'drizzle-orm';

export async function getAllProducts(): Promise<any[]> {
  return await db.query.productInfo.findMany({
    orderBy: productInfo.name,
    with: {
      unit: true,
      store: true,
    },
  });
}

export async function getProductById(id: number): Promise<any | null> {
  return await db.query.productInfo.findFirst({
    where: eq(productInfo.id, id),
    with: {
      unit: true,
      store: true,
      specialDeals: true,
      productTags: {
        with: {
          tag: true,
        },
      },
    },
  });
}

export async function createProduct(input: any): Promise<any> {
  const [product] = await db.insert(productInfo).values(input).returning();
  return product;
}

export async function updateProduct(id: number, updates: any): Promise<any> {
  const [product] = await db.update(productInfo)
    .set(updates)
    .where(eq(productInfo.id, id))
    .returning();
  return product;
}

export async function toggleProductOutOfStock(id: number, isOutOfStock: boolean): Promise<any> {
  const [product] = await db.update(productInfo)
    .set({ isOutOfStock })
    .where(eq(productInfo.id, id))
    .returning();
  return product;
}

export async function getAllUnits(): Promise<any[]> {
  return await db.query.units.findMany({
    orderBy: units.name,
  });
}

export async function getAllProductTags(): Promise<any[]> {
  return await db.query.productTags.findMany({
    with: {
      products: {
        with: {
          product: true,
        },
      },
    },
  });
}

export async function getProductReviews(productId: number): Promise<any[]> {
  return await db.query.productReviews.findMany({
    where: eq(productReviews.productId, productId),
    with: {
      user: true,
    },
    orderBy: desc(productReviews.createdAt),
  });
}

export async function respondToReview(reviewId: number, adminResponse: string): Promise<void> {
  await db.update(productReviews)
    .set({ adminResponse })
    .where(eq(productReviews.id, reviewId));
}

export async function getAllProductGroups(): Promise<any[]> {
  return await db.query.productGroupInfo.findMany({
    with: {
      products: {
        with: {
          product: true,
        },
      },
    },
  });
}

export async function createProductGroup(name: string): Promise<any> {
  const [group] = await db.insert(productGroupInfo).values({ name }).returning();
  return group;
}

export async function updateProductGroup(id: number, name: string): Promise<any> {
  const [group] = await db.update(productGroupInfo)
    .set({ name })
    .where(eq(productGroupInfo.id, id))
    .returning();
  return group;
}

export async function deleteProductGroup(id: number): Promise<void> {
  await db.delete(productGroupInfo).where(eq(productGroupInfo.id, id));
}

export async function addProductToGroup(groupId: number, productId: number): Promise<void> {
  await db.insert(productGroupMembership).values({ groupId, productId });
}

export async function removeProductFromGroup(groupId: number, productId: number): Promise<void> {
  await db.delete(productGroupMembership)
    .where(and(
      eq(productGroupMembership.groupId, groupId),
      eq(productGroupMembership.productId, productId)
    ));
}
