package com.example.data.local

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Update
import kotlinx.coroutines.flow.Flow

@Dao
interface FoodItemDao {
    @Query("SELECT * FROM food_items ORDER BY id ASC")
    fun getAllFoodItems(): Flow<List<FoodItemEntity>>

    @Query("SELECT * FROM food_items ORDER BY id ASC")
    suspend fun getAllFoodItemsList(): List<FoodItemEntity>

    @Query("SELECT * FROM food_items WHERE id = :id LIMIT 1")
    suspend fun getFoodItemById(id: Long): FoodItemEntity?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertItem(item: FoodItemEntity): Long

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAll(items: List<FoodItemEntity>)

    @Update
    suspend fun updateItem(item: FoodItemEntity)

    @Delete
    suspend fun deleteItem(item: FoodItemEntity)

    @Query("UPDATE food_items SET rating = :rating, reviewCount = :reviewCount WHERE id = :id")
    suspend fun updateItemRating(id: Long, rating: Float, reviewCount: Int)

    @Query("UPDATE food_items SET imageUri = :imageUri WHERE id = :id")
    suspend fun updateItemImage(id: Long, imageUri: String)

    @Query("UPDATE food_items SET isAvailable = :isAvailable WHERE id = :id")
    suspend fun updateItemAvailability(id: Long, isAvailable: Boolean)

    @Query("SELECT COUNT(*) FROM food_items")
    suspend fun getCount(): Int
}

@Dao
interface OrderDao {
    @Query("SELECT * FROM orders ORDER BY timestamp DESC")
    fun getAllOrders(): Flow<List<OrderEntity>>

    @Query("SELECT * FROM orders WHERE customerPhone LIKE '%' || :phoneDigits || '%' ORDER BY timestamp DESC")
    fun getOrdersForCustomer(phoneDigits: String): Flow<List<OrderEntity>>

    @Query("SELECT * FROM orders WHERE orderId = :orderId LIMIT 1")
    suspend fun getOrderById(orderId: String): OrderEntity?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertOrder(order: OrderEntity)

    @Query("UPDATE orders SET orderStatus = :status WHERE orderId = :orderId")
    suspend fun updateOrderStatus(orderId: String, status: String)

    @Query("UPDATE orders SET paymentStatus = :paymentStatus, upiTransactionId = :upiTxnId WHERE orderId = :orderId")
    suspend fun updateOrderPayment(orderId: String, paymentStatus: String, upiTxnId: String)

    @Query("UPDATE orders SET assignedRiderName = :name, assignedRiderPhone = :phone, assignedRiderVehicle = :vehicle WHERE orderId = :orderId")
    suspend fun assignRider(orderId: String, name: String, phone: String, vehicle: String)

    @Query("DELETE FROM orders WHERE orderId = :orderId")
    suspend fun deleteOrder(orderId: String)

    @Query("SELECT COUNT(*) FROM orders WHERE upiTransactionId = :upiTxnId")
    suspend fun countOrdersWithUpiTxnId(upiTxnId: String): Int
}

@Dao
interface FoodReviewDao {
    @Query("SELECT * FROM food_reviews ORDER BY timestamp DESC")
    fun getAllReviews(): Flow<List<FoodReviewEntity>>

    @Query("SELECT * FROM food_reviews WHERE foodItemId = :foodItemId ORDER BY timestamp DESC")
    fun getReviewsForFoodItem(foodItemId: Long): Flow<List<FoodReviewEntity>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertReview(review: FoodReviewEntity): Long

    @Query("SELECT COUNT(*) FROM food_reviews WHERE foodItemId = :foodItemId")
    suspend fun getReviewCountForItem(foodItemId: Long): Int

    @Query("SELECT AVG(rating) FROM food_reviews WHERE foodItemId = :foodItemId")
    suspend fun getAverageRatingForItem(foodItemId: Long): Double?

    @Query("SELECT COUNT(*) FROM food_reviews")
    suspend fun getCount(): Int
}

@Dao
interface NotificationDao {
    @Query("SELECT * FROM notifications ORDER BY timestamp DESC")
    fun getAllNotifications(): Flow<List<NotificationEntity>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertNotification(notification: NotificationEntity): Long

    @Query("UPDATE notifications SET isRead = 1")
    suspend fun markAllAsRead()

    @Query("DELETE FROM notifications")
    suspend fun clearAll()
}

@Dao
interface DeliveryRiderDao {
    @Query("SELECT * FROM delivery_riders ORDER BY id ASC")
    fun getAllRiders(): Flow<List<DeliveryRiderEntity>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertRider(rider: DeliveryRiderEntity): Long

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAll(riders: List<DeliveryRiderEntity>)

    @Update
    suspend fun updateRider(rider: DeliveryRiderEntity)

    @Delete
    suspend fun deleteRider(rider: DeliveryRiderEntity)

    @Query("UPDATE delivery_riders SET status = :status, currentOrderId = :currentOrderId WHERE id = :id")
    suspend fun updateRiderStatus(id: Long, status: String, currentOrderId: String)

    @Query("SELECT COUNT(*) FROM delivery_riders")
    suspend fun getCount(): Int
}

@Dao
interface UserDao {
    @Query("SELECT * FROM users WHERE phone = :phone OR phone = '+91 ' || :phone OR phone = 'cust_' || :phone LIMIT 1")
    suspend fun getUserByPhone(phone: String): UserEntity?

    @Query("SELECT COUNT(*) FROM users WHERE phone = :phone OR phone = '+91 ' || :phone OR phone = 'cust_' || :phone")
    suspend fun countUserWithPhone(phone: String): Int

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUser(user: UserEntity): Long

    @Update
    suspend fun updateUser(user: UserEntity)

    @Query("UPDATE users SET profileImageUri = :uri WHERE phone = :phone OR phone = '+91 ' || :phone OR phone = 'cust_' || :phone")
    suspend fun updateProfileImage(phone: String, uri: String)

    @Query("SELECT * FROM users ORDER BY createdAt DESC")
    fun getAllUsers(): Flow<List<UserEntity>>

    @Query("SELECT * FROM users ORDER BY createdAt DESC")
    suspend fun getAllUsersList(): List<UserEntity>

    @Query("SELECT COUNT(*) FROM users")
    suspend fun getCount(): Int
}

@Dao
interface CartItemDao {
    @Query("SELECT * FROM cart_items ORDER BY id ASC")
    fun getAllCartItems(): Flow<List<CartItemEntity>>

    @Query("SELECT * FROM cart_items ORDER BY id ASC")
    suspend fun getAllCartItemsList(): List<CartItemEntity>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertCartItem(item: CartItemEntity): Long

    @Query("UPDATE cart_items SET quantity = :quantity WHERE foodId = :foodId")
    suspend fun updateQuantity(foodId: Long, quantity: Int)

    @Query("DELETE FROM cart_items WHERE foodId = :foodId")
    suspend fun deleteByFoodId(foodId: Long)

    @Query("DELETE FROM cart_items")
    suspend fun clearCart()
}