<?php
class Order {
    private $conn;
    private $table_name = "orders";
    private $table_order_items = "order_items";
    private $table_payment_accounts = "payment_accounts";

    public $id;
    public $user_id;
    public $total_amount;
    public $status;
    public $shipping_address;
    public $payment_method;
    public $payment_account_id;

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

    public function getAvailablePaymentAccount($payment_method) {
        $current_date = date('Y-m-d');

        // Reset daily amounts at midnight
        $query = "UPDATE " . $this->table_payment_accounts . " SET current_daily_amount = 0 WHERE DATE(updated_at) < ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $current_date);
        $stmt->execute();

        // Get active accounts for the payment method, ordered by current usage
        $query = "SELECT id, account_name, account_number, daily_limit, current_daily_amount
                  FROM " . $this->table_payment_accounts . " pa
                  JOIN payment_methods pm ON pa.payment_method_id = pm.id
                  WHERE pm.code = ? AND pa.is_active = 1 AND DATE(pa.updated_at) = ?
                  AND (pa.daily_limit = 0 OR pa.current_daily_amount < pa.daily_limit)
                  ORDER BY pa.current_daily_amount ASC LIMIT 1";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $payment_method);
        $stmt->bindParam(2, $current_date);
        $stmt->execute();

        $account = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($account) {
            // Check if adding this order would exceed the limit
            if ($account['daily_limit'] > 0 && ($account['current_daily_amount'] + $this->total_amount) > $account['daily_limit']) {
                return false; // No account available that can handle this amount
            }
            return $account;
        }

        return false;
    }

    public function updatePaymentAccountUsage($account_id, $amount) {
        $query = "UPDATE " . $this->table_payment_accounts . "
                  SET current_daily_amount = current_daily_amount + ?, updated_at = NOW()
                  WHERE id = ?";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $amount);
        $stmt->bindParam(2, $account_id);
        return $stmt->execute();
    }

    public function create($cart_items) {
        try {
            $this->conn->beginTransaction();

            // Calculate total amount
            $this->total_amount = 0;
            foreach ($cart_items as $item) {
                $this->total_amount += $item['price'] * $item['quantity'];
            }

            // Get available payment account for the selected method
            if ($this->payment_method && $this->payment_method !== 'cash_on_delivery') {
                $available_account = $this->getAvailablePaymentAccount($this->payment_method);
                if (!$available_account) {
                    throw new Exception("No available payment account for the selected method");
                }
                $this->payment_account_id = $available_account['id'];
                $this->updatePaymentAccountUsage($this->payment_account_id, $this->total_amount);
            }

            // Create order
            $query = "INSERT INTO " . $this->table_name . "
                    SET user_id=:user_id, customer_name=:customer_name, customer_phone=:customer_phone,
                        customer_address=:customer_address, total_amount=:total_amount, status=:status,
                        payment_method=:payment_method, payment_account_id=:payment_account_id";

            $stmt = $this->conn->prepare($query);

            $stmt->bindParam(":user_id", $this->user_id);
            $stmt->bindParam(":customer_name", $this->customer_name);
            $stmt->bindParam(":customer_phone", $this->customer_phone);
            $stmt->bindParam(":customer_address", $this->customer_address);
            $stmt->bindParam(":total_amount", $this->total_amount);
            $stmt->bindParam(":status", $this->status);
            $stmt->bindParam(":payment_method", $this->payment_method);
            $stmt->bindParam(":payment_account_id", $this->payment_account_id);

            $stmt->execute();
            $order_id = $this->conn->lastInsertId();

            // Add order items
            $query = "INSERT INTO " . $this->table_order_items . "
                    SET order_id=:order_id, product_id=:product_id, quantity=:quantity, price=:price";

            $stmt = $this->conn->prepare($query);

            foreach ($cart_items as $item) {
                $stmt->bindParam(":order_id", $order_id);
                $stmt->bindParam(":product_id", $item['product_id']);
                $stmt->bindParam(":quantity", $item['quantity']);
                $stmt->bindParam(":price", $item['price']);
                $stmt->execute();
            }

            // Clear cart
            $cart = new Cart($this->conn);
            $cart->clearCart($this->user_id);

            $this->conn->commit();

            return $order_id;

        } catch (Exception $e) {
            $this->conn->rollBack();
            return false;
        }
    }

    public function getUserOrders($user_id) {
        $query = "SELECT o.id, o.order_number, o.total, o.status, o.address, o.city, o.created_at,
                         COUNT(oi.id) as item_count
                FROM " . $this->table_name . " o
                LEFT JOIN " . $this->table_order_items . " oi ON o.id = oi.order_id
                WHERE o.user_id = ?
                GROUP BY o.id
                ORDER BY o.created_at DESC";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $user_id);
        $stmt->execute();

        return $stmt;
    }

    public function getOrderDetails($order_id, $user_id) {
        $query = "SELECT o.id, o.order_number, o.total, o.status, o.address, o.city, o.created_at,
                         oi.quantity, oi.price, oi.product_name, p.image
                FROM " . $this->table_name . " o
                LEFT JOIN " . $this->table_order_items . " oi ON o.id = oi.order_id
                LEFT JOIN products p ON oi.product_id = p.id
                WHERE o.id = ? AND o.user_id = ?
                ORDER BY o.created_at DESC";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $order_id);
        $stmt->bindParam(2, $user_id);
        $stmt->execute();

        return $stmt;
    }

    public function getAllOrders() {
        $query = "SELECT o.id, o.user_id, o.order_number, o.first_name, o.last_name, o.phone, 
                         o.email, o.address, o.city, o.total, o.status, o.payment_method, o.created_at
                FROM " . $this->table_name . " o
                ORDER BY o.created_at DESC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function updateOrderStatus($order_id, $status) {
        $query = "UPDATE " . $this->table_name . "
                SET status = :status
                WHERE id = :order_id";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":status", $status);
        $stmt->bindParam(":order_id", $order_id);

        if($stmt->execute()) {
            // Send Telegram notification
            try {
                require_once __DIR__ . '/../helpers/telegram.php';
                $telegram = new TelegramBot($this->conn);
                
                // Get order details
                $query = "SELECT * FROM " . $this->table_name . " WHERE id = ?";
                $stmt = $this->conn->prepare($query);
                $stmt->execute([$order_id]);
                $order = $stmt->fetch(PDO::FETCH_ASSOC);
                
                if ($order) {
                    $telegram->sendOrderStatusUpdate($order, $status);
                }
            } catch (Exception $e) {
                // Log error but don't stop the update process
                error_log("Telegram notification failed: " . $e->getMessage());
            }
            
            return true;
        }

        return false;
    }

    public function checkPaymentAccountLimits() {
        $current_date = date('Y-m-d');

        // Reset daily amounts at midnight
        $query = "UPDATE " . $this->table_payment_accounts . " SET current_daily_amount = 0, updated_at = NOW() WHERE DATE(updated_at) < ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $current_date);
        $stmt->execute();

        // Get accounts that are close to or exceeding their limits
        $query = "SELECT pa.id, pa.account_name, pa.account_number, pa.daily_limit, pa.current_daily_amount,
                         pm.name as payment_method_name, pm.code as payment_method_code
                  FROM " . $this->table_payment_accounts . " pa
                  JOIN payment_methods pm ON pa.payment_method_id = pm.id
                  WHERE pa.is_active = 1 AND pa.daily_limit > 0
                  AND (pa.current_daily_amount >= pa.daily_limit * 0.8 OR pa.current_daily_amount >= pa.daily_limit)";

        $stmt = $this->conn->prepare($query);
        $stmt->execute();

        $limit_alerts = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $percentage = ($row['current_daily_amount'] / $row['daily_limit']) * 100;
            $limit_alerts[] = [
                'account_id' => $row['id'],
                'account_name' => $row['account_name'],
                'account_number' => $row['account_number'],
                'payment_method' => $row['payment_method_name'],
                'current_amount' => $row['current_daily_amount'],
                'daily_limit' => $row['daily_limit'],
                'percentage' => round($percentage, 1),
                'is_exceeded' => $row['current_daily_amount'] >= $row['daily_limit']
            ];
        }

        return $limit_alerts;
    }

    public function deactivateAccountIfLimitExceeded($account_id) {
        // Check if account has exceeded limit
        $query = "SELECT daily_limit, current_daily_amount FROM " . $this->table_payment_accounts . " WHERE id = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $account_id);
        $stmt->execute();
        $account = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($account && $account['current_daily_amount'] >= $account['daily_limit']) {
            // Deactivate account
            $query = "UPDATE " . $this->table_payment_accounts . " SET is_active = 0 WHERE id = ?";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(1, $account_id);
            return $stmt->execute();
        }

        return false;
    }
}
?>