import { 
  users, sellers, categories, stores, products, orders, orderItems, cartItems,
  type User, type InsertUser,
  type Seller, type InsertSeller,
  type Category, type InsertCategory,
  type Store, type InsertStore,
  type Product, type InsertProduct,
  type Order, type InsertOrder,
  type OrderItem, type InsertOrderItem,
  type CartItem, type InsertCartItem
} from "@shared/schema";
import { db } from "./db";
import { eq, desc, and, like, or, inArray } from "drizzle-orm";

export interface IStorage {
  // Users
  getUser(id: string): Promise<User | undefined>;
  getUserByUsername(username: string): Promise<User | undefined>;
  getUserByEmail(email: string): Promise<User | undefined>;
  createUser(user: InsertUser): Promise<User>;
  updateUser(id: string, user: Partial<InsertUser>): Promise<User>;
  getAllUsers(): Promise<User[]>;
  
  // Sellers
  getSeller(id: string): Promise<Seller | undefined>;
  getSellerByUsername(username: string): Promise<Seller | undefined>;
  createSeller(seller: InsertSeller): Promise<Seller>;
  getAllSellers(): Promise<Seller[]>;
  deleteSeller(id: string): Promise<void>;
  
  // Categories
  getCategory(id: string): Promise<Category | undefined>;
  getAllCategories(): Promise<Category[]>;
  createCategory(category: InsertCategory): Promise<Category>;
  updateCategory(id: string, category: Partial<InsertCategory>): Promise<Category>;
  deleteCategory(id: string): Promise<void>;
  
  // Stores
  getStore(id: string): Promise<Store | undefined>;
  getStoreBysellerId(sellerId: string): Promise<Store | undefined>;
  getStoresBySeller(sellerId: string): Promise<Store[]>;
  getAllStores(): Promise<Store[]>;
  createStore(store: InsertStore): Promise<Store>;
  updateStore(id: string, store: Partial<InsertStore>): Promise<Store>;
  deleteStore(id: string): Promise<void>;
  
  // Products
  getProduct(id: string): Promise<Product | undefined>;
  getAllProducts(): Promise<Product[]>;
  getProductsByCategory(categoryId: string): Promise<Product[]>;
  getProductsBySeller(sellerId: string): Promise<Product[]>;
  getFeaturedProducts(limit?: number): Promise<Product[]>;
  searchProducts(query: string): Promise<Product[]>;
  createProduct(product: InsertProduct): Promise<Product>;
  updateProduct(id: string, product: Partial<InsertProduct>): Promise<Product>;
  deleteProduct(id: string): Promise<void>;
  
  // Orders
  getOrder(id: string): Promise<Order | undefined>;
  getOrdersByUser(userId: string): Promise<Order[]>;
  getAllOrders(): Promise<Order[]>;
  getOrdersForSeller(productIds: string[]): Promise<Order[]>;
  createOrder(order: InsertOrder): Promise<Order>;
  updateOrderStatus(id: string, status: string): Promise<Order>;
  updateOrderPaymentStatus(id: string, paymentStatus: string): Promise<Order>;
  updateOrderStatusAndPayment(id: string, updates: { status?: string; paymentStatus?: string }): Promise<Order>;
  
  // Order Items
  getOrderItems(orderId: string): Promise<OrderItem[]>;
  createOrderItem(orderItem: InsertOrderItem): Promise<OrderItem>;
  
  // Cart
  getCartItems(userId: string): Promise<CartItem[]>;
  addToCart(cartItem: InsertCartItem): Promise<CartItem>;
  updateCartItem(id: string, quantity: number): Promise<CartItem>;
  removeFromCart(id: string): Promise<void>;
  clearCart(userId: string): Promise<void>;
}

export class DatabaseStorage implements IStorage {
  // Users
  async getUser(id: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.id, id));
    return user || undefined;
  }

  async getUserByUsername(username: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.username, username));
    return user || undefined;
  }

  async getUserByEmail(email: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.email, email));
    return user || undefined;
  }

  async createUser(insertUser: InsertUser): Promise<User> {
    // Convert latitude/longitude numbers to strings for database storage
    const userData = {
      ...insertUser,
      latitude: insertUser.latitude ? String(insertUser.latitude) : undefined,
      longitude: insertUser.longitude ? String(insertUser.longitude) : undefined,
    };
    const [user] = await db.insert(users).values([userData]).returning();
    return user;
  }

  async getAllUsers(): Promise<User[]> {
    return await db.select().from(users).orderBy(desc(users.createdAt));
  }

  async updateUser(id: string, updateUser: Partial<InsertUser>): Promise<User> {
    // Convert latitude/longitude numbers to strings for database storage
    const userData = {
      ...updateUser,
      latitude: updateUser.latitude ? String(updateUser.latitude) : undefined,
      longitude: updateUser.longitude ? String(updateUser.longitude) : undefined,
    };
    const [user] = await db.update(users)
      .set(userData)
      .where(eq(users.id, id))
      .returning();
    return user;
  }

  // Sellers
  async getSeller(id: string): Promise<Seller | undefined> {
    const [seller] = await db.select().from(sellers).where(eq(sellers.id, id));
    return seller || undefined;
  }

  async getSellerByUsername(username: string): Promise<Seller | undefined> {
    const [seller] = await db.select().from(sellers).where(eq(sellers.username, username));
    return seller || undefined;
  }

  async createSeller(insertSeller: InsertSeller): Promise<Seller> {
    const [seller] = await db.insert(sellers).values([insertSeller]).returning();
    return seller;
  }

  async getAllSellers(): Promise<Seller[]> {
    return await db.select().from(sellers).orderBy(desc(sellers.createdAt));
  }

  async deleteSeller(id: string): Promise<void> {
    await db.delete(sellers).where(eq(sellers.id, id));
  }

  // Categories
  async getCategory(id: string): Promise<Category | undefined> {
    const [category] = await db.select().from(categories).where(eq(categories.id, id));
    return category || undefined;
  }

  async getAllCategories(): Promise<Category[]> {
    return await db.select().from(categories).orderBy(categories.name);
  }

  async createCategory(insertCategory: InsertCategory): Promise<Category> {
    const [category] = await db.insert(categories).values([insertCategory]).returning();
    return category;
  }

  async updateCategory(id: string, updateCategory: Partial<InsertCategory>): Promise<Category> {
    const [category] = await db.update(categories)
      .set(updateCategory)
      .where(eq(categories.id, id))
      .returning();
    return category;
  }

  async deleteCategory(id: string): Promise<void> {
    await db.delete(categories).where(eq(categories.id, id));
  }

  // Stores
  async getStore(id: string): Promise<Store | undefined> {
    const [store] = await db.select().from(stores).where(eq(stores.id, id));
    return store || undefined;
  }

  async getStoreBysellerId(sellerId: string): Promise<Store | undefined> {
    const [store] = await db.select().from(stores).where(eq(stores.sellerId, sellerId));
    return store || undefined;
  }

  async getAllStores(): Promise<Store[]> {
    return await db.select().from(stores).orderBy(desc(stores.createdAt));
  }

  async createStore(insertStore: InsertStore): Promise<Store> {
    const [store] = await db.insert(stores).values([insertStore]).returning();
    return store;
  }

  async updateStore(id: string, updateStore: Partial<InsertStore>): Promise<Store> {
    const [store] = await db.update(stores)
      .set(updateStore)
      .where(eq(stores.id, id))
      .returning();
    return store;
  }

  async getStoresBySeller(sellerId: string): Promise<Store[]> {
    return await db.select().from(stores).where(eq(stores.sellerId, sellerId));
  }

  async deleteStore(id: string): Promise<void> {
    await db.delete(stores).where(eq(stores.id, id));
  }

  // Products
  async getProduct(id: string): Promise<Product | undefined> {
    const [product] = await db.select().from(products).where(eq(products.id, id));
    return product || undefined;
  }

  async getAllProducts(): Promise<Product[]> {
    return await db.select()
      .from(products)
      .where(eq(products.isActive, true))
      .orderBy(desc(products.createdAt));
  }

  async getProductsByCategory(categoryId: string): Promise<Product[]> {
    return await db.select()
      .from(products)
      .where(and(eq(products.categoryId, categoryId), eq(products.isActive, true)))
      .orderBy(desc(products.createdAt));
  }

  async getProductsBySeller(sellerId: string): Promise<Product[]> {
    return await db.select()
      .from(products)
      .where(and(eq(products.sellerId, sellerId), eq(products.isActive, true)))
      .orderBy(desc(products.createdAt));
  }

  async getFeaturedProducts(limit = 8): Promise<Product[]> {
    return await db.select()
      .from(products)
      .where(eq(products.isActive, true))
      .orderBy(desc(products.createdAt))
      .limit(limit);
  }

  async getFeaturedProductsByCategories(): Promise<Product[]> {
    // Get all categories
    const allCategories = await this.getAllCategories();
    const allFeaturedProducts: Product[] = [];
    
    // For each category, get up to 5 products
    for (const category of allCategories) {
      const categoryProducts = await db.select()
        .from(products)
        .where(and(eq(products.categoryId, category.id), eq(products.isActive, true)))
        .orderBy(desc(products.createdAt))
        .limit(5);
      
      // If category has less than 5 products, we'll need to create more later
      allFeaturedProducts.push(...categoryProducts);
    }
    
    return allFeaturedProducts;
  }

  async searchProducts(query: string): Promise<Product[]> {
    return await db.select()
      .from(products)
      .where(and(
        eq(products.isActive, true),
        or(
          like(products.title, `%${query}%`),
          like(products.description, `%${query}%`)
        )
      ))
      .orderBy(desc(products.createdAt));
  }

  async createProduct(insertProduct: InsertProduct): Promise<Product> {
    const [product] = await db.insert(products).values([insertProduct]).returning();
    return product;
  }

  async updateProduct(id: string, updateProduct: Partial<InsertProduct>): Promise<Product> {
    const [product] = await db.update(products)
      .set(updateProduct)
      .where(eq(products.id, id))
      .returning();
    return product;
  }

  async deleteProduct(id: string): Promise<void> {
    await db.delete(products).where(eq(products.id, id));
  }

  // Orders
  async getOrder(id: string): Promise<Order | undefined> {
    const [order] = await db.select().from(orders).where(eq(orders.id, id));
    return order || undefined;
  }

  async getOrdersByUser(userId: string): Promise<Order[]> {
    return await db.select()
      .from(orders)
      .where(eq(orders.userId, userId))
      .orderBy(desc(orders.createdAt));
  }

  async getAllOrders(): Promise<Order[]> {
    return await db.select()
      .from(orders)
      .orderBy(desc(orders.createdAt));
  }

  async getOrdersForSeller(productIds: string[]): Promise<Order[]> {
    if (productIds.length === 0) return [];
    
    // Find distinct orders that contain any of the seller's products
    const orderIdsResult = await db.selectDistinct({ orderId: orderItems.orderId })
      .from(orderItems)
      .where(inArray(orderItems.productId, productIds));
    
    const orderIds = orderIdsResult.map(row => row.orderId);
    
    if (orderIds.length === 0) return [];
    
    return await db.select()
      .from(orders)
      .where(inArray(orders.id, orderIds))
      .orderBy(desc(orders.createdAt));
  }

  async createOrder(insertOrder: InsertOrder): Promise<Order> {
    const [order] = await db.insert(orders).values([insertOrder]).returning();
    return order;
  }

  async updateOrderStatus(id: string, status: string): Promise<Order> {
    const [order] = await db.update(orders)
      .set({ status })
      .where(eq(orders.id, id))
      .returning();
    return order;
  }

  async updateOrderPaymentStatus(id: string, paymentStatus: string): Promise<Order> {
    const [order] = await db.update(orders)
      .set({ paymentStatus })
      .where(eq(orders.id, id))
      .returning();
    return order;
  }

  async updateOrderStatusAndPayment(id: string, updates: { status?: string; paymentStatus?: string }): Promise<Order> {
    const [order] = await db.update(orders)
      .set(updates)
      .where(eq(orders.id, id))
      .returning();
    return order;
  }

  // Order Items
  async getOrderItems(orderId: string): Promise<OrderItem[]> {
    return await db.select().from(orderItems).where(eq(orderItems.orderId, orderId));
  }

  async createOrderItem(insertOrderItem: InsertOrderItem): Promise<OrderItem> {
    const [orderItem] = await db.insert(orderItems).values([insertOrderItem]).returning();
    return orderItem;
  }

  // Cart
  async getCartItems(userId: string): Promise<CartItem[]> {
    return await db.select()
      .from(cartItems)
      .where(eq(cartItems.userId, userId))
      .orderBy(desc(cartItems.createdAt));
  }

  async addToCart(insertCartItem: InsertCartItem): Promise<CartItem> {
    // Check if item already exists in cart
    const [existingItem] = await db.select()
      .from(cartItems)
      .where(and(
        eq(cartItems.userId, insertCartItem.userId),
        eq(cartItems.productId, insertCartItem.productId)
      ));

    if (existingItem) {
      // Update quantity
      const [updatedItem] = await db.update(cartItems)
        .set({ quantity: existingItem.quantity + (insertCartItem.quantity || 1) })
        .where(eq(cartItems.id, existingItem.id))
        .returning();
      return updatedItem;
    }

    const [cartItem] = await db.insert(cartItems).values([insertCartItem]).returning();
    return cartItem;
  }

  async updateCartItem(id: string, quantity: number): Promise<CartItem> {
    const [cartItem] = await db.update(cartItems)
      .set({ quantity })
      .where(eq(cartItems.id, id))
      .returning();
    return cartItem;
  }

  async removeFromCart(id: string): Promise<void> {
    await db.delete(cartItems).where(eq(cartItems.id, id));
  }

  async clearCart(userId: string): Promise<void> {
    await db.delete(cartItems).where(eq(cartItems.userId, userId));
  }
}

export const storage = new DatabaseStorage();