import { 
  users, 
  pharmacies, 
  couriers, 
  medicines, 
  orders, 
  orderEvents, 
  pharmacyStock, 
  wallets, 
  ledger, 
  settings,
  type User, 
  type InsertUser,
  type Pharmacy,
  type InsertPharmacy,
  type Courier,
  type InsertCourier,
  type Medicine,
  type InsertMedicine,
  type InsertPharmacyStock,
  type Order,
  type InsertOrder,
  type OrderWithDetails,
  type PharmacyWithUser,
  type CourierWithUser,
  type OrderEvent,
  type PharmacyStock,
  type Wallet,
  type LedgerEntry,
  transactions,
  paymentMethods,
  type Transaction,
  type PaymentMethod,
  type InsertTransaction,
  type InsertPaymentMethod
} from "@shared/schema";
import { db } from "./db";
import { eq, and, sql, desc, asc, inArray, or, like, isNull } from "drizzle-orm";

// Interface for storage operations
export interface IStorage {
  // User operations
  getUser(id: string): Promise<User | undefined>;
  getUserByEmail(email: string): Promise<User | undefined>;
  createUser(user: InsertUser): Promise<User>;
  updateUserPassword(userId: string, passwordHash: string): Promise<void>;
  getUserWithProfile(id: string): Promise<any>;
  
  // Pharmacy operations
  createPharmacy(pharmacy: InsertPharmacy): Promise<Pharmacy>;
  findNearbyPharmacies(lat: number, lng: number, medicineName: string, exclude?: string[]): Promise<any[]>;
  updatePharmacyStock(pharmacyId: string, medicineId: string, quantityChange: number): Promise<void>;
  
  // Courier operations
  createCourier(courier: InsertCourier): Promise<Courier>;
  findNearbyCouriers(lat: number, lng: number): Promise<CourierWithUser[]>;
  getAvailableOrders(lat: number, lng: number): Promise<OrderWithDetails[]>;
  
  // Medicine operations
  searchMedicines(query: string): Promise<Medicine[]>;
  getMedicine(id: string): Promise<Medicine | undefined>;
  createMedicine(medicine: InsertMedicine): Promise<Medicine>;
  updateMedicine(id: string, updates: Partial<Medicine>): Promise<void>;
  getMedicinesByCategory(category: string): Promise<Medicine[]>;
  getPopularMedicines(limit?: number): Promise<Medicine[]>;
  
  // Pharmacy stock operations
  getPharmacyStock(pharmacyId: string): Promise<any[]>;
  addToPharmacyStock(stock: InsertPharmacyStock): Promise<PharmacyStock>;
  updateStockQuantity(pharmacyId: string, medicineId: string, quantity: number): Promise<void>;
  updateStockPrice(pharmacyId: string, medicineId: string, price: number): Promise<void>;
  removeFromPharmacyStock(pharmacyId: string, medicineId: string): Promise<void>;
  getStockByMedicine(medicineId: string): Promise<any[]>;
  getLowStockItems(pharmacyId: string): Promise<any[]>;
  
  // Order operations
  createOrder(order: InsertOrder): Promise<Order>;
  updateOrder(id: string, updates: Partial<Order>): Promise<void>;
  getOrderWithDetails(id: string): Promise<OrderWithDetails | undefined>;
  getAllOrders(): Promise<OrderWithDetails[]>;
  getOrdersByClient(clientId: string): Promise<OrderWithDetails[]>;
  getOrdersByPharmacy(pharmacyId: string, status?: string): Promise<OrderWithDetails[]>;
  getOrdersByCourier(courierId: string, status?: string): Promise<OrderWithDetails[]>;
  
  // Order events
  createOrderEvent(orderId: string, event: string, meta?: any): Promise<OrderEvent>;
  
  // Wallet operations
  getOrCreateWallet(userId: string): Promise<Wallet>;
  getWalletBalance(userId: string): Promise<number>;
  updateWalletBalance(userId: string, newBalance: number): Promise<void>;
  createTransaction(transactionData: InsertTransaction): Promise<Transaction>;
  getTransactionHistory(userId: string, limit?: number): Promise<any[]>;
  processPayment(userId: string, amount: number, description: string, orderId?: string): Promise<{ success: boolean; transaction?: Transaction; error?: string }>;
  addFunds(userId: string, amount: number, description: string): Promise<Transaction>;
  addEarnings(userId: string, amount: number, description: string, orderId?: string): Promise<Transaction>;
  
  // Payment methods operations
  addPaymentMethod(paymentMethodData: InsertPaymentMethod): Promise<PaymentMethod>;
  getUserPaymentMethods(userId: string): Promise<PaymentMethod[]>;
  removePaymentMethod(userId: string, paymentMethodId: string): Promise<void>;

  // Legacy wallet & Ledger operations
  getWallet(ownerType: string, ownerId: string): Promise<Wallet>;
  createLedgerEntry(entry: Partial<LedgerEntry>): Promise<LedgerEntry>;
  
  // Settings operations
  getSettings(): Promise<Record<string, string>>;
  updateSettings(settings: Record<string, any>): Promise<void>;
  
  // Admin operations
  getAdminStats(): Promise<any>;
  getAllUsers(filters?: { role?: string; verified?: boolean }): Promise<any[]>;
  verifyUser(userId: string, verified: boolean): Promise<void>;
  updateUserStatus(userId: string, active: boolean): Promise<void>;
  getOrdersStats(): Promise<any>;
  getFinancialStats(): Promise<any>;
  getRecentActivity(): Promise<any[]>;
}

export class DatabaseStorage implements IStorage {
  
  // User operations
  async getUser(id: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.id, id));
    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> {
    const [user] = await db
      .insert(users)
      .values(insertUser)
      .returning();
    return user;
  }

  async updateUserPassword(userId: string, passwordHash: string): Promise<void> {
    await db
      .update(users)
      .set({ passwordHash })
      .where(eq(users.id, userId));
  }

  async getUserWithProfile(id: string): Promise<any> {
    const [user] = await db
      .select()
      .from(users)
      .where(eq(users.id, id));
    
    if (!user) return undefined;
    
    let profile: any = { ...user };
    
    if (user.role === 'pharmacy') {
      const [pharmacy] = await db
        .select()
        .from(pharmacies)
        .where(eq(pharmacies.userId, id));
      profile.pharmacy = pharmacy;
    } else if (user.role === 'courier') {
      const [courier] = await db
        .select()
        .from(couriers)
        .where(eq(couriers.userId, id));
      profile.courier = courier;
    }
    
    return profile;
  }

  // Pharmacy operations
  async createPharmacy(pharmacy: InsertPharmacy): Promise<Pharmacy> {
    const [result] = await db
      .insert(pharmacies)
      .values(pharmacy)
      .returning();
    return result;
  }

  async findNearbyPharmacies(lat: number, lng: number, medicineName: string, exclude: string[] = []): Promise<any[]> {
    // Use Haversine formula to calculate distance
    const query = db
      .select({
        id: pharmacies.id,
        name: pharmacies.name,
        address: pharmacies.address,
        lat: pharmacies.lat,
        lng: pharmacies.lng,
        verified: pharmacies.verified,
        distance: sql<number>`
          6371 * acos(
            cos(radians(${lat})) * 
            cos(radians(${pharmacies.lat})) * 
            cos(radians(${pharmacies.lng}) - radians(${lng})) + 
            sin(radians(${lat})) * 
            sin(radians(${pharmacies.lat}))
          )
        `.as('distance'),
        stock: pharmacyStock.stock,
        unitPrice: pharmacyStock.unitPrice
      })
      .from(pharmacies)
      .leftJoin(medicines, like(medicines.name, `%${medicineName}%`))
      .leftJoin(pharmacyStock, and(
        eq(pharmacyStock.pharmacyId, pharmacies.id),
        eq(pharmacyStock.medicineId, medicines.id)
      ))
      .where(
        and(
          eq(pharmacies.verified, true),
          sql`${pharmacyStock.stock} > 0`,
          exclude.length > 0 ? sql`${pharmacies.id} NOT IN (${exclude.join(',')})` : sql`1=1`
        )
      )
      .orderBy(sql`distance ASC`);

    return await query;
  }

  async updatePharmacyStock(pharmacyId: string, medicineId: string, quantityChange: number): Promise<void> {
    await db
      .update(pharmacyStock)
      .set({ 
        stock: sql`${pharmacyStock.stock} + ${quantityChange}` 
      })
      .where(and(
        eq(pharmacyStock.pharmacyId, pharmacyId),
        eq(pharmacyStock.medicineId, medicineId)
      ));
  }

  // Courier operations
  async createCourier(courier: InsertCourier): Promise<Courier> {
    const [result] = await db
      .insert(couriers)
      .values(courier)
      .returning();
    return result;
  }

  async findNearbyCouriers(lat: number, lng: number): Promise<CourierWithUser[]> {
    const result = await db
      .select({
        courier: couriers,
        user: users,
        distance: sql<number>`
          6371 * acos(
            cos(radians(${lat})) * 
            cos(radians(${couriers.lat})) * 
            cos(radians(${couriers.lng}) - radians(${lng})) + 
            sin(radians(${lat})) * 
            sin(radians(${couriers.lat}))
          )
        `.as('distance')
      })
      .from(couriers)
      .innerJoin(users, eq(users.id, couriers.userId))
      .where(and(
        eq(couriers.active, true),
        eq(couriers.available, true)
      ))
      .orderBy(sql`distance ASC`)
      .limit(5);

    return result.map(r => ({
      ...r.courier,
      user: r.user,
      distance: r.distance
    })) as CourierWithUser[];
  }

  async getAvailableOrders(lat: number, lng: number): Promise<OrderWithDetails[]> {
    const result = await db
      .select()
      .from(orders)
      .leftJoin(users, eq(users.id, orders.clientId))
      .leftJoin(pharmacies, eq(pharmacies.id, orders.pharmacyId))
      .leftJoin(medicines, eq(medicines.id, orders.medicineId))
      .where(eq(orders.status, 'PHARMACY_CONFIRMED'))
      .orderBy(desc(orders.createdAt));

    return result.map(r => ({
      ...r.orders,
      client: r.users,
      pharmacy: r.pharmacies ? { ...r.pharmacies, user: {} as User } : null,
      courier: null,
      medicine: r.medicines,
      events: []
    })) as OrderWithDetails[];
  }

  // Medicine operations
  async searchMedicines(query: string): Promise<Medicine[]> {
    return await db
      .select()
      .from(medicines)
      .where(
        or(
          like(medicines.name, `%${query}%`),
          like(medicines.genericName, `%${query}%`),
          like(medicines.activeIngredient, `%${query}%`),
          like(medicines.category, `%${query}%`)
        )
      )
      .orderBy(asc(medicines.name))
      .limit(50);
  }

  async getMedicine(id: string): Promise<Medicine | undefined> {
    const [medicine] = await db.select().from(medicines).where(eq(medicines.id, id));
    return medicine || undefined;
  }

  async createMedicine(medicine: InsertMedicine): Promise<Medicine> {
    const [result] = await db
      .insert(medicines)
      .values(medicine)
      .returning();
    return result;
  }

  async updateMedicine(id: string, updates: Partial<Medicine>): Promise<void> {
    await db
      .update(medicines)
      .set({ ...updates, updatedAt: new Date() })
      .where(eq(medicines.id, id));
  }

  async getMedicinesByCategory(category: string): Promise<Medicine[]> {
    return await db
      .select()
      .from(medicines)
      .where(eq(medicines.category, category))
      .orderBy(asc(medicines.name));
  }

  async getPopularMedicines(limit: number = 20): Promise<Medicine[]> {
    // Get most ordered medicines
    return await db
      .select()
      .from(medicines)
      .orderBy(desc(medicines.name)) // TODO: Add popularity tracking
      .limit(limit);
  }

  // Pharmacy stock operations
  async getPharmacyStock(pharmacyId: string): Promise<any[]> {
    return await db
      .select({
        id: pharmacyStock.id,
        medicineId: pharmacyStock.medicineId,
        stock: pharmacyStock.stock,
        unitPrice: pharmacyStock.unitPrice,
        minimumStock: pharmacyStock.minimumStock,
        available: pharmacyStock.available,
        lastRestockedAt: pharmacyStock.lastRestockedAt,
        medicine: {
          id: medicines.id,
          name: medicines.name,
          genericName: medicines.genericName,
          category: medicines.category,
          form: medicines.form,
          dosage: medicines.dosage,
          requiresPrescription: medicines.requiresPrescription
        }
      })
      .from(pharmacyStock)
      .leftJoin(medicines, eq(pharmacyStock.medicineId, medicines.id))
      .where(eq(pharmacyStock.pharmacyId, pharmacyId))
      .orderBy(asc(medicines.name));
  }

  async addToPharmacyStock(stock: InsertPharmacyStock): Promise<PharmacyStock> {
    // Check if stock already exists for this medicine in this pharmacy
    const [existing] = await db
      .select()
      .from(pharmacyStock)
      .where(
        and(
          eq(pharmacyStock.pharmacyId, stock.pharmacyId),
          eq(pharmacyStock.medicineId, stock.medicineId)
        )
      );

    if (existing) {
      // Update existing stock
      const [updated] = await db
        .update(pharmacyStock)
        .set({
          stock: (existing.stock || 0) + (stock.stock || 0),
          unitPrice: stock.unitPrice || existing.unitPrice,
          updatedAt: new Date()
        })
        .where(eq(pharmacyStock.id, existing.id))
        .returning();
      return updated;
    } else {
      // Create new stock entry
      const [result] = await db
        .insert(pharmacyStock)
        .values(stock)
        .returning();
      return result;
    }
  }

  async updateStockQuantity(pharmacyId: string, medicineId: string, quantity: number): Promise<void> {
    await db
      .update(pharmacyStock)
      .set({ 
        stock: quantity,
        updatedAt: new Date()
      })
      .where(
        and(
          eq(pharmacyStock.pharmacyId, pharmacyId),
          eq(pharmacyStock.medicineId, medicineId)
        )
      );
  }

  async updateStockPrice(pharmacyId: string, medicineId: string, price: number): Promise<void> {
    await db
      .update(pharmacyStock)
      .set({ 
        unitPrice: price,
        updatedAt: new Date()
      })
      .where(
        and(
          eq(pharmacyStock.pharmacyId, pharmacyId),
          eq(pharmacyStock.medicineId, medicineId)
        )
      );
  }

  async removeFromPharmacyStock(pharmacyId: string, medicineId: string): Promise<void> {
    await db
      .delete(pharmacyStock)
      .where(
        and(
          eq(pharmacyStock.pharmacyId, pharmacyId),
          eq(pharmacyStock.medicineId, medicineId)
        )
      );
  }

  async getStockByMedicine(medicineId: string): Promise<any[]> {
    return await db
      .select({
        pharmacyId: pharmacyStock.pharmacyId,
        stock: pharmacyStock.stock,
        unitPrice: pharmacyStock.unitPrice,
        available: pharmacyStock.available,
        pharmacy: {
          id: pharmacies.id,
          name: pharmacies.name,
          address: pharmacies.address,
          lat: pharmacies.lat,
          lng: pharmacies.lng,
          verified: pharmacies.verified
        }
      })
      .from(pharmacyStock)
      .leftJoin(pharmacies, eq(pharmacyStock.pharmacyId, pharmacies.id))
      .where(
        and(
          eq(pharmacyStock.medicineId, medicineId),
          eq(pharmacyStock.available, true),
          eq(pharmacies.verified, true)
        )
      )
      .orderBy(asc(pharmacyStock.unitPrice));
  }

  async getLowStockItems(pharmacyId: string): Promise<any[]> {
    return await db
      .select({
        id: pharmacyStock.id,
        stock: pharmacyStock.stock,
        minimumStock: pharmacyStock.minimumStock,
        medicine: {
          id: medicines.id,
          name: medicines.name,
          category: medicines.category
        }
      })
      .from(pharmacyStock)
      .leftJoin(medicines, eq(pharmacyStock.medicineId, medicines.id))
      .where(
        and(
          eq(pharmacyStock.pharmacyId, pharmacyId),
          sql`${pharmacyStock.stock} <= ${pharmacyStock.minimumStock}`
        )
      )
      .orderBy(asc(pharmacyStock.stock));
  }

  // Wallet operations
  async getOrCreateWallet(userId: string): Promise<Wallet> {
    // Check if wallet exists
    const [existingWallet] = await db
      .select()
      .from(wallets)
      .where(eq(wallets.ownerId, userId));

    if (existingWallet) {
      return existingWallet;
    }

    // Create new wallet
    const [newWallet] = await db
      .insert(wallets)
      .values({
        ownerType: 'MEDIFLASH',
        ownerId: userId,
        balance: 0
      })
      .returning();

    return newWallet;
  }

  async getWalletBalance(userId: string): Promise<number> {
    const wallet = await this.getOrCreateWallet(userId);
    return wallet.balance ?? 0;
  }

  async updateWalletBalance(userId: string, newBalance: number): Promise<void> {
    await db
      .update(wallets)
      .set({ 
        balance: newBalance,
        updatedAt: new Date()
      })
      .where(eq(wallets.ownerId, userId));
  }

  async createTransaction(transactionData: InsertTransaction): Promise<Transaction> {
    const [transaction] = await db
      .insert(transactions)
      .values(transactionData)
      .returning();

    return transaction;
  }

  async getTransactionHistory(userId: string, limit: number = 20): Promise<any[]> {
    const wallet = await this.getOrCreateWallet(userId);
    
    return await db
      .select({
        id: transactions.id,
        type: transactions.type,
        amount: transactions.amount,
        balanceAfter: transactions.balanceAfter,
        description: transactions.description,
        status: transactions.status,
        createdAt: transactions.createdAt,
        orderId: transactions.orderId
      })
      .from(transactions)
      .where(eq(transactions.walletId, wallet.id))
      .orderBy(desc(transactions.createdAt))
      .limit(limit);
  }

  async processPayment(userId: string, amount: number, description: string, orderId?: string): Promise<{ success: boolean; transaction?: Transaction; error?: string }> {
    const wallet = await this.getOrCreateWallet(userId);
    
    if ((wallet.balance ?? 0) < amount) {
      return {
        success: false,
        error: "Solde insuffisant"
      };
    }

    const newBalance = (wallet.balance ?? 0) - amount;
    
    // Update wallet balance
    await this.updateWalletBalance(userId, newBalance);
    
    // Create transaction record
    const transaction = await this.createTransaction({
      walletId: wallet.id,
      orderId: orderId,
      type: 'payment',
      amount: -amount,
      balanceAfter: newBalance,
      description: description,
      status: 'completed'
    });

    return {
      success: true,
      transaction
    };
  }

  async addFunds(userId: string, amount: number, description: string): Promise<Transaction> {
    const wallet = await this.getOrCreateWallet(userId);
    const newBalance = (wallet.balance ?? 0) + amount;
    
    // Update wallet balance
    await this.updateWalletBalance(userId, newBalance);
    
    // Create transaction record
    return await this.createTransaction({
      walletId: wallet.id,
      type: 'deposit',
      amount: amount,
      balanceAfter: newBalance,
      description: description,
      status: 'completed'
    });
  }

  async addEarnings(userId: string, amount: number, description: string, orderId?: string): Promise<Transaction> {
    const wallet = await this.getOrCreateWallet(userId);
    const newBalance = (wallet.balance ?? 0) + amount;
    
    // Update wallet balance
    await this.updateWalletBalance(userId, newBalance);
    
    // Create transaction record
    return await this.createTransaction({
      walletId: wallet.id,
      orderId: orderId,
      type: 'earnings',
      amount: amount,
      balanceAfter: newBalance,
      description: description,
      status: 'completed'
    });
  }

  // Payment methods operations
  async addPaymentMethod(paymentMethodData: InsertPaymentMethod): Promise<PaymentMethod> {
    // If this is set as default, unset other defaults
    if (paymentMethodData.isDefault) {
      await db
        .update(paymentMethods)
        .set({ isDefault: false })
        .where(eq(paymentMethods.userId, paymentMethodData.userId));
    }

    const [paymentMethod] = await db
      .insert(paymentMethods)
      .values(paymentMethodData)
      .returning();

    return paymentMethod;
  }

  async getUserPaymentMethods(userId: string): Promise<PaymentMethod[]> {
    return await db
      .select()
      .from(paymentMethods)
      .where(
        and(
          eq(paymentMethods.userId, userId),
          eq(paymentMethods.isActive, true)
        )
      )
      .orderBy(desc(paymentMethods.isDefault), desc(paymentMethods.createdAt));
  }

  async removePaymentMethod(userId: string, paymentMethodId: string): Promise<void> {
    await db
      .update(paymentMethods)
      .set({ isActive: false })
      .where(
        and(
          eq(paymentMethods.id, paymentMethodId),
          eq(paymentMethods.userId, userId)
        )
      );
  }

  // Order operations
  async createOrder(order: InsertOrder): Promise<Order> {
    const [result] = await db
      .insert(orders)
      .values({
        ...order,
        createdAt: new Date(),
        updatedAt: new Date()
      })
      .returning();
    return result;
  }

  async updateOrder(id: string, updates: Partial<Order>): Promise<void> {
    await db
      .update(orders)
      .set({
        ...updates,
        updatedAt: new Date()
      })
      .where(eq(orders.id, id));
  }

  async getOrderWithDetails(id: string): Promise<OrderWithDetails | undefined> {
    const result = await db
      .select()
      .from(orders)
      .leftJoin(users, eq(users.id, orders.clientId))
      .leftJoin(pharmacies, eq(pharmacies.id, orders.pharmacyId))
      .leftJoin(couriers, eq(couriers.id, orders.courierId))
      .leftJoin(medicines, eq(medicines.id, orders.medicineId))
      .where(eq(orders.id, id));

    if (result.length === 0) return undefined;

    const order = result[0];
    
    // Get events
    const events = await db
      .select()
      .from(orderEvents)
      .where(eq(orderEvents.orderId, id))
      .orderBy(asc(orderEvents.createdAt));

    // Get pharmacy user
    let pharmacyUser: User | undefined;
    if (order.pharmacies) {
      const [pharmUser] = await db
        .select()
        .from(users)
        .where(eq(users.id, order.pharmacies.userId));
      pharmacyUser = pharmUser;
    }

    // Get courier user
    let courierUser: User | undefined;
    if (order.couriers) {
      const [courUser] = await db
        .select()
        .from(users)
        .where(eq(users.id, order.couriers.userId));
      courierUser = courUser;
    }

    return {
      ...order.orders,
      client: order.users!,
      pharmacy: order.pharmacies ? { ...order.pharmacies, user: pharmacyUser! } : null,
      courier: order.couriers ? { ...order.couriers, user: courierUser! } : null,
      medicine: order.medicines,
      events
    } as OrderWithDetails;
  }

  async getAllOrders(): Promise<OrderWithDetails[]> {
    const result = await db
      .select()
      .from(orders)
      .leftJoin(users, eq(users.id, orders.clientId))
      .leftJoin(pharmacies, eq(pharmacies.id, orders.pharmacyId))
      .leftJoin(couriers, eq(couriers.id, orders.courierId))
      .leftJoin(medicines, eq(medicines.id, orders.medicineId))
      .orderBy(desc(orders.createdAt));

    return result.map(r => ({
      ...r.orders,
      client: r.users!,
      pharmacy: r.pharmacies ? { ...r.pharmacies, user: {} as User } : null,
      courier: r.couriers ? { ...r.couriers, user: {} as User } : null,
      medicine: r.medicines,
      events: []
    })) as OrderWithDetails[];
  }

  async getOrdersByClient(clientId: string): Promise<OrderWithDetails[]> {
    const result = await db
      .select()
      .from(orders)
      .leftJoin(users, eq(users.id, orders.clientId))
      .leftJoin(pharmacies, eq(pharmacies.id, orders.pharmacyId))
      .leftJoin(couriers, eq(couriers.id, orders.courierId))
      .leftJoin(medicines, eq(medicines.id, orders.medicineId))
      .where(eq(orders.clientId, clientId))
      .orderBy(desc(orders.createdAt));

    return result.map(r => ({
      ...r.orders,
      client: r.users!,
      pharmacy: r.pharmacies ? { ...r.pharmacies, user: {} as User } : null,
      courier: r.couriers ? { ...r.couriers, user: {} as User } : null,
      medicine: r.medicines,
      events: []
    })) as OrderWithDetails[];
  }

  async getOrdersByPharmacy(pharmacyId: string, status?: string): Promise<OrderWithDetails[]> {
    let whereConditions = eq(orders.pharmacyId, pharmacyId);
    
    if (status) {
      const statuses = status.split(',');
      whereConditions = and(whereConditions, inArray(orders.status, statuses as any[])) as any;
    }

    const result = await db
      .select()
      .from(orders)
      .leftJoin(users, eq(users.id, orders.clientId))
      .leftJoin(pharmacies, eq(pharmacies.id, orders.pharmacyId))
      .leftJoin(couriers, eq(couriers.id, orders.courierId))
      .leftJoin(medicines, eq(medicines.id, orders.medicineId))
      .where(whereConditions)
      .orderBy(desc(orders.createdAt));

    return result.map(r => ({
      ...r.orders,
      client: r.users!,
      pharmacy: r.pharmacies ? { ...r.pharmacies, user: {} as User } : null,
      courier: r.couriers ? { ...r.couriers, user: {} as User } : null,
      medicine: r.medicines,
      events: []
    })) as OrderWithDetails[];
  }

  async getOrdersByCourier(courierId: string, status?: string): Promise<OrderWithDetails[]> {
    let whereConditions = eq(orders.courierId, courierId);
    
    if (status) {
      const statuses = status.split(',');
      whereConditions = and(whereConditions, inArray(orders.status, statuses as any[])) as any;
    }

    const result = await db
      .select()
      .from(orders)
      .leftJoin(users, eq(users.id, orders.clientId))
      .leftJoin(pharmacies, eq(pharmacies.id, orders.pharmacyId))
      .leftJoin(couriers, eq(couriers.id, orders.courierId))
      .leftJoin(medicines, eq(medicines.id, orders.medicineId))
      .where(whereConditions)
      .orderBy(desc(orders.createdAt));

    return result.map(r => ({
      ...r.orders,
      client: r.users!,
      pharmacy: r.pharmacies ? { ...r.pharmacies, user: {} as User } : null,
      courier: r.couriers ? { ...r.couriers, user: {} as User } : null,
      medicine: r.medicines,
      events: []
    })) as OrderWithDetails[];
  }

  // Order events
  async createOrderEvent(orderId: string, event: string, meta?: any): Promise<OrderEvent> {
    const [result] = await db
      .insert(orderEvents)
      .values({
        orderId,
        event,
        meta: meta ? JSON.stringify(meta) : null,
        createdAt: new Date()
      })
      .returning();
    return result;
  }

  // Wallet & Ledger operations
  async getWallet(ownerType: string, ownerId: string): Promise<Wallet> {
    const [wallet] = await db
      .select()
      .from(wallets)
      .where(and(
        eq(wallets.ownerType, ownerType as any),
        eq(wallets.ownerId, ownerId)
      ));

    if (wallet) return wallet;

    // Create wallet if it doesn't exist
    const [newWallet] = await db
      .insert(wallets)
      .values({
        ownerType: ownerType as any,
        ownerId,
        balance: 0,
        updatedAt: new Date()
      })
      .returning();

    return newWallet;
  }

  async createLedgerEntry(entry: Partial<LedgerEntry>): Promise<LedgerEntry> {
    const [result] = await db
      .insert(ledger)
      .values({
        ...entry,
        createdAt: new Date()
      } as any)
      .returning();

    // Update corresponding wallet
    if (entry.account && entry.amount) {
      const walletUpdate = entry.entryType === 'CREDIT' ? entry.amount : -entry.amount;
      
      await db
        .update(wallets)
        .set({
          balance: sql`${wallets.balance} + ${walletUpdate}`,
          updatedAt: new Date()
        })
        .where(and(
          eq(wallets.ownerType, entry.account as any),
          entry.account === 'CLIENT' ? isNull(wallets.ownerId) : sql`1=1`
        ));
    }

    return result;
  }

  // Settings operations
  async getSettings(): Promise<Record<string, string>> {
    const result = await db.select().from(settings);
    return Object.fromEntries(result.map(s => [s.key, s.value]));
  }

  async updateSettings(settingsUpdate: Record<string, any>): Promise<void> {
    for (const [key, value] of Object.entries(settingsUpdate)) {
      await db
        .insert(settings)
        .values({ key, value: String(value) })
        .onConflictDoUpdate({
          target: settings.key,
          set: { value: String(value) }
        });
    }
  }

  // Admin operations
  async getAdminStats(): Promise<any> {
    // Get user statistics
    const totalUsers = await db.select({ count: sql`count(*)` }).from(users);
    const usersByRole = await db
      .select({ 
        role: users.role, 
        count: sql`count(*)` 
      })
      .from(users)
      .groupBy(users.role);
    
    // Get order statistics
    const totalOrders = await db.select({ count: sql`count(*)` }).from(orders);
    const ordersByStatus = await db
      .select({ 
        status: orders.status, 
        count: sql`count(*)` 
      })
      .from(orders)
      .groupBy(orders.status);
    
    // Get financial statistics - from existing wallet system
    const totalTransactions = await db
      .select({ 
        total: sql`sum(${transactions.amount})`,
        count: sql`count(*)`
      })
      .from(transactions);
    
    // Get pharmacy statistics
    const verifiedPharmacies = await db
      .select({ count: sql`count(*)` })
      .from(pharmacies)
      .where(eq(pharmacies.verified, true));
    
    const unverifiedPharmacies = await db
      .select({ count: sql`count(*)` })
      .from(pharmacies)
      .where(eq(pharmacies.verified, false));
    
    return {
      users: {
        total: Number(totalUsers[0]?.count || 0),
        byRole: usersByRole.reduce((acc, item) => {
          acc[item.role] = Number(item.count);
          return acc;
        }, {} as Record<string, number>)
      },
      orders: {
        total: Number(totalOrders[0]?.count || 0),
        byStatus: ordersByStatus.reduce((acc, item) => {
          acc[item.status] = Number(item.count);
          return acc;
        }, {} as Record<string, number>)
      },
      finance: {
        totalAmount: Number(totalTransactions[0]?.total || 0),
        totalTransactions: Number(totalTransactions[0]?.count || 0)
      },
      pharmacies: {
        verified: Number(verifiedPharmacies[0]?.count || 0),
        unverified: Number(unverifiedPharmacies[0]?.count || 0)
      }
    };
  }

  async getAllUsers(filters?: { role?: string; verified?: boolean }): Promise<any[]> {
    let query = db
      .select({
        id: users.id,
        name: users.name,
        email: users.email,
        phone: users.phone,
        role: users.role,
        active: users.active,
        createdAt: users.createdAt,
        pharmacy: {
          id: pharmacies.id,
          name: pharmacies.name,
          verified: pharmacies.verified,
          address: pharmacies.address
        },
        courier: {
          id: couriers.id,
          active: couriers.active,
          vehicleType: couriers.vehicleType
        }
      })
      .from(users)
      .leftJoin(pharmacies, eq(users.id, pharmacies.userId))
      .leftJoin(couriers, eq(users.id, couriers.userId));

    let baseQuery = query;
    if (filters?.role) {
      baseQuery = baseQuery.where(eq(users.role, filters.role));
    }

    const result = await baseQuery.orderBy(desc(users.createdAt));
    
    return result.map(user => ({
      ...user,
      verified: user.role === 'pharmacy' ? user.pharmacy?.verified : 
                user.role === 'courier' ? user.courier?.active : true
    }));
  }

  async verifyUser(userId: string, verified: boolean): Promise<void> {
    const user = await this.getUser(userId);
    if (!user) throw new Error("Utilisateur introuvable");

    if (user.role === 'pharmacy') {
      await db
        .update(pharmacies)
        .set({ verified })
        .where(eq(pharmacies.userId, userId));
    } else if (user.role === 'courier') {
      await db
        .update(couriers)
        .set({ active: verified })
        .where(eq(couriers.userId, userId));
    }
  }

  async updateUserStatus(userId: string, active: boolean): Promise<void> {
    await db
      .update(users)
      .set({ active })
      .where(eq(users.id, userId));
  }

  async getOrdersStats(): Promise<any> {
    const today = new Date();
    const weekAgo = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);
    const monthAgo = new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000);

    const ordersToday = await db
      .select({ count: sql`count(*)` })
      .from(orders)
      .where(sql`DATE(${orders.createdAt}) = DATE(NOW())`);

    const ordersThisWeek = await db
      .select({ count: sql`count(*)` })
      .from(orders)
      .where(sql`${orders.createdAt} >= ${weekAgo}`);

    const ordersThisMonth = await db
      .select({ count: sql`count(*)` })
      .from(orders)
      .where(sql`${orders.createdAt} >= ${monthAgo}`);

    return {
      today: Number(ordersToday[0]?.count || 0),
      thisWeek: Number(ordersThisWeek[0]?.count || 0),
      thisMonth: Number(ordersThisMonth[0]?.count || 0)
    };
  }

  async getFinancialStats(): Promise<any> {
    const monthAgo = new Date();
    monthAgo.setMonth(monthAgo.getMonth() - 1);

    const revenueThisMonth = await db
      .select({ total: sql`sum(${transactions.amount})` })
      .from(transactions)
      .where(
        and(
          sql`${transactions.createdAt} >= ${monthAgo}`,
          eq(transactions.type, 'payment')
        )
      );

    const earningsThisMonth = await db
      .select({ total: sql`sum(${transactions.amount})` })
      .from(transactions)
      .where(
        and(
          sql`${transactions.createdAt} >= ${monthAgo}`,
          eq(transactions.type, 'earnings')
        )
      );

    return {
      revenue: Math.abs(Number(revenueThisMonth[0]?.total || 0)),
      earnings: Number(earningsThisMonth[0]?.total || 0)
    };
  }

  async getRecentActivity(): Promise<any[]> {
    // Get recent orders
    const recentOrders = await db
      .select({
        id: orders.id,
        type: sql`'order'`,
        description: sql`CONCAT('Commande ', ${orders.medicineName})`,
        createdAt: orders.createdAt,
        status: orders.status,
        clientName: users.name
      })
      .from(orders)
      .leftJoin(users, eq(orders.clientId, users.id))
      .orderBy(desc(orders.createdAt))
      .limit(10);

    // Get recent user registrations
    const recentUsers = await db
      .select({
        id: users.id,
        type: sql`'user'`,
        description: sql`CONCAT('Nouvel utilisateur: ', ${users.name}, ' (', ${users.role}, ')')`,
        createdAt: users.createdAt,
        status: sql`'active'`,
        clientName: users.name
      })
      .from(users)
      .orderBy(desc(users.createdAt))
      .limit(10);

    // Combine and sort by date
    const allActivity = [...recentOrders, ...recentUsers]
      .filter(item => item.createdAt)
      .sort((a, b) => new Date(b.createdAt!).getTime() - new Date(a.createdAt!).getTime())
      .slice(0, 15);

    return allActivity;
  }
}

export const storage = new DatabaseStorage();
