<?php
class Order {
	private $db;

	public function __construct(DB $db) {
	    $this->db = $db;
	}

	public function getOrdersByUserId($userId) {
	    $userId = $this->db->escape($userId);

	    return $this->db->query("
	        SELECT id, item_id, item_name, unit_price, quantity, sold, stock, returned, order_time 
	        FROM orders 
	        WHERE user_id = '{$userId}'
	        ORDER BY order_time DESC
	    ")->rows;
	}

	public function deleteOrdersWithZeroQuantity($userId) {
	    $userId = $this->db->escape($userId);
	    $this->db->query("DELETE FROM orders WHERE user_id = '{$userId}' AND quantity = 0");
	}

	public function sellItem($userId, $itemId, $quantityToSell) {
	    $this->db->query("START TRANSACTION");
	    
	    try {
	        $orders = $this->db->query("SELECT * FROM orders WHERE user_id = '{$userId}' AND item_id = '{$itemId}' ORDER BY order_time ASC")->rows;
	        
	        foreach ($orders as $order) {
	            if ($quantityToSell <= 0) break;
	            
	            $sellQuantity = min($order['stock'], $quantityToSell);
	            $newSold = $order['sold'] + $sellQuantity;
	            $newStock = $order['stock'] - $sellQuantity;
	            $quantityToSell -= $sellQuantity;
	            
	            $this->db->query("UPDATE orders SET sold = '$newSold', stock = '$newStock' WHERE id = '{$order['id']}'");
	            $updatedOrder = $this->db->query("SELECT sold, stock FROM orders WHERE item_id = '{$itemId}' AND user_id = '{$userId}'")->row;
	            $updatedSold = $updatedOrder['sold'];
	            $updatedStock = $updatedOrder['stock'];
	        }
	        $this->db->query("COMMIT");
	        return [
	            'success' => true,
	            'message' => 'Operation successful.',
	            'updatedSold' => $updatedSold,
	            'updatedStock' => $updatedStock
	        ];
	    } catch (Exception $e) {
	        $this->db->query("ROLLBACK");
	        return ['success' => false, 'message' => $e->getMessage()];
	    }
	}

	public function returnItem($userId, $itemId, $quantityToReturn) {
	    $this->db->query("START TRANSACTION");
	    
	    try {
	        $orders = $this->db->query("SELECT * FROM orders WHERE user_id = '{$userId}' AND item_id = '{$itemId}' ORDER BY order_time ASC")->rows;
	        
	        foreach ($orders as $order) {
	            if ($quantityToReturn <= 0) break;
	            
	            $returnQuantity = min(($order['quantity'] - $order['returned'] - $order['sold']), $quantityToReturn);
	            $newReturned = $order['returned'] + $returnQuantity;
	            $newStock = $order['stock'] - $returnQuantity;
	            $quantityToReturn -= $returnQuantity;
	            
	            $this->db->query("UPDATE orders SET returned = '$newReturned', stock = '$newStock' WHERE id = '{$order['id']}'");
	            $updatedOrder = $this->db->query("SELECT returned, stock FROM orders WHERE item_id = '{$itemId}' AND user_id = '{$userId}'")->row;
	            $updatedReturn = $updatedOrder['returned'];
	            $updatedStock = $updatedOrder['stock'];
	        }
	        $this->db->query("COMMIT");
	        return [
	            'success' => true,
	            'message' => 'Operation successful.',
	            'updatedReturn' => $updatedReturn,
	            'updatedStock' => $updatedStock
	        ];
	    } catch (Exception $e) {
	        $this->db->query("ROLLBACK");
	        return ['success' => false, 'message' => $e->getMessage()];
	    }
	}

	public function resetUserOrders($userId) {
	    $userId = $this->db->escape($userId);

	    $sql = "UPDATE orders SET sold = 0, returned = 0, stock = quantity WHERE user_id = '$userId'";
	    $this->db->query($sql);

	    if ($this->db->countAffected() >= 0) {
	        return ['success' => true];
	    } else {
	        return ['success' => false, 'message' => 'Failed to reset orders.'];
	    }
	}

	public function hasUnconfirmedClearance($userId) {
	    $userId = $this->db->escape($userId);
	    $result = $this->db->query("SELECT * FROM clearance WHERE user_id = '$userId'");
	    return $result && $result->num_rows > 0;
	}

	public function finalizeDeal($userId, $itemIds) {
	    $this->db->query("START TRANSACTION");

	    try {
	        // Check if a clearance record already exists for the user
	        $existingClearance = $this->db->query("SELECT * FROM clearance WHERE user_id = '{$userId}'");

	        if ($existingClearance && $existingClearance->num_rows > 0) {
	            // Abort the operation
	            $this->db->query("ROLLBACK");
	            return ['success' => false, 'message' => '你有结算单待确认.'];
	        }

	        // Proceed with calculating totalSold and clearanceAmount
	        $totalSold = 0;
	        $clearanceAmount = 0;

	        foreach ($itemIds as $itemId) {
	            $query = "SELECT SUM(sold) as totalSold, unit_price FROM orders WHERE item_id = '{$itemId}' AND user_id = '{$userId}' AND sold > 0 GROUP BY unit_price";
	            $result = $this->db->query($query);
	            if ($result && $result->num_rows > 0) {
	                $item = $result->row;
	                $totalSold += $item['totalSold'];
	                $clearanceAmount += $item['totalSold'] * $item['unit_price'];
	            }
	        }

	        // Insert into the clearance table or update if the user_id already exists
	        $insertOrUpdateQuery = "INSERT INTO clearance (user_id, total_sold, clearance_amount) VALUES ('{$userId}', '{$totalSold}', '{$clearanceAmount}')
	                                ON DUPLICATE KEY UPDATE 
	                                total_sold = VALUES(total_sold),
	                                clearance_amount = VALUES(clearance_amount),
	                                created_at = CURRENT_TIMESTAMP";
	        $this->db->query($insertOrUpdateQuery);

	        $this->db->query("COMMIT");
	        return ['success' => true, 'message' => 'Deal has been finalized successfully.', 'clearanceId' => $this->db->getLastId()];
	    } catch (Exception $e) {
	        $this->db->query("ROLLBACK");
	        return ['success' => false, 'message' => $e->getMessage()];
	    }
	}
}