<?php
/**
 * ============================================================
 * ORDER SERVICE
 * ============================================================
 * Handles order creation, payment, and status management
 */

class OrderService {
    
    /**
     * Create new order from cart
     * @param int $userId
     * @param array $shippingData
     * @param string $paymentMethod 'bKash' or 'Nagad'
     * @param array|null $promoData Applied promo code data
     * @return array
     */
    public static function create(
        int $userId, 
        array $shippingData, 
        string $paymentMethod,
        ?array $promoData = null
    ): array {
        $cartService = new CartService();
        $items = $cartService->getItems();
        
        if (empty($items)) {
            return ['success' => false, 'message' => 'কার্ট খালি'];
        }
        
        // Validate stock for all items
        foreach ($items as $item) {
            if ($item['qty'] > $item['stock_qty']) {
                return [
                    'success' => false,
                    'message' => "{$item['name']} এর স্টক পর্যাপ্ত নেই"
                ];
            }
        }
        
        // Calculate totals
        $discountAmount = $promoData['discount_amount'] ?? 0;
        $totals = $cartService->getTotals($discountAmount);
        
        try {
            Database::beginTransaction();
            
            // Generate order number
            $orderNo = generateOrderNumber();
            
            // Create order
            $orderId = Database::insert(
                "INSERT INTO orders (
                    order_no, user_id, status, subtotal, vat_percent, vat_amount, 
                    tax_amount, promo_code_id, promo_code, discount_amount, grand_total,
                    payment_method, payment_status, shipping_name, shipping_phone, 
                    shipping_email, shipping_address, shipping_city, shipping_area, notes
                ) VALUES (?, ?, 'Pending', ?, ?, ?, ?, ?, ?, ?, ?, ?, 'Unpaid', ?, ?, ?, ?, ?, ?, ?)",
                [
                    $orderNo,
                    $userId,
                    $totals['subtotal'],
                    $totals['vat_percent'],
                    $totals['vat_amount'],
                    $totals['tax_amount'],
                    $promoData['promo']['id'] ?? null,
                    $promoData['promo']['code'] ?? null,
                    $discountAmount,
                    $totals['grand_total'],
                    $paymentMethod,
                    $shippingData['name'],
                    $shippingData['phone'],
                    $shippingData['email'] ?? null,
                    $shippingData['address'],
                    $shippingData['city'] ?? null,
                    $shippingData['area'] ?? null,
                    $shippingData['notes'] ?? null
                ]
            );
            
            // Create order items and update stock
            foreach ($items as $item) {
                $lineTotal = $item['qty'] * $item['unit_price'];
                
                Database::insert(
                    "INSERT INTO order_items (order_id, product_id, product_name, product_sku, unit_price, qty, line_total)
                     VALUES (?, ?, ?, ?, ?, ?, ?)",
                    [
                        $orderId,
                        $item['product_id'],
                        $item['name'],
                        $item['sku'] ?? null,
                        $item['unit_price'],
                        $item['qty'],
                        $lineTotal
                    ]
                );
                
                // Update stock
                Database::execute(
                    "UPDATE products SET stock_qty = stock_qty - ? WHERE id = ?",
                    [$item['qty'], $item['product_id']]
                );
            }
            
            // Add initial status history
            Database::insert(
                "INSERT INTO order_status_history (order_id, status, note) VALUES (?, 'Pending', 'অর্ডার গৃহীত হয়েছে')",
                [$orderId]
            );
            
            // Increment promo usage if used
            if (!empty($promoData['promo']['id'])) {
                PromoService::incrementUsage($promoData['promo']['id']);
            }
            
            // Clear cart
            $cartService->clear();
            
            Database::commit();
            
            return [
                'success' => true,
                'message' => 'অর্ডার সফলভাবে সম্পন্ন হয়েছে',
                'order_id' => $orderId,
                'order_no' => $orderNo,
                'grand_total' => $totals['grand_total']
            ];
            
        } catch (Exception $e) {
            Database::rollback();
            logError('Order creation failed', ['error' => $e->getMessage()]);
            return ['success' => false, 'message' => 'অর্ডার তৈরিতে সমস্যা হয়েছে'];
        }
    }
    
    /**
     * Submit payment details (bKash/Nagad)
     * @param int $orderId
     * @param string $method
     * @param string $senderMobile
     * @param string $trxId
     * @param float $amount
     * @return array
     */
    public static function submitPayment(
        int $orderId, 
        string $method, 
        string $senderMobile, 
        string $trxId,
        float $amount
    ): array {
        // Validate order exists and belongs to user
        $order = Database::fetchOne(
            "SELECT id, order_no, grand_total, payment_status FROM orders WHERE id = ?",
            [$orderId]
        );
        
        if (!$order) {
            return ['success' => false, 'message' => 'অর্ডার পাওয়া যায়নি'];
        }
        
        if ($order['payment_status'] === 'Verified') {
            return ['success' => false, 'message' => 'পেমেন্ট ইতিমধ্যে ভেরিফাই হয়েছে'];
        }
        
        // Check if TRX ID already used
        $existingTrx = Database::fetchColumn(
            "SELECT COUNT(*) FROM order_payments WHERE trx_id = ?",
            [$trxId]
        );
        
        if ($existingTrx) {
            return ['success' => false, 'message' => 'এই Transaction ID ইতিমধ্যে ব্যবহৃত হয়েছে'];
        }
        
        try {
            Database::beginTransaction();
            
            // Insert payment record
            Database::insert(
                "INSERT INTO order_payments (order_id, method, sender_mobile, trx_id, amount, status)
                 VALUES (?, ?, ?, ?, ?, 'Submitted')",
                [$orderId, $method, $senderMobile, $trxId, $amount]
            );
            
            // Update order payment status
            Database::execute(
                "UPDATE orders SET payment_status = 'Submitted' WHERE id = ?",
                [$orderId]
            );
            
            Database::commit();
            
            return [
                'success' => true,
                'message' => 'পেমেন্ট তথ্য জমা হয়েছে। ভেরিফিকেশনের জন্য অপেক্ষা করুন।'
            ];
            
        } catch (Exception $e) {
            Database::rollback();
            logError('Payment submission failed', ['error' => $e->getMessage()]);
            return ['success' => false, 'message' => 'পেমেন্ট তথ্য জমা দিতে সমস্যা হয়েছে'];
        }
    }
    
    /**
     * Verify payment (admin)
     * @param int $paymentId
     * @param int $adminId
     * @param string $status 'Verified' or 'Rejected'
     * @param string|null $note
     * @return array
     */
    public static function verifyPayment(int $paymentId, int $adminId, string $status, ?string $note = null): array {
        $payment = Database::fetchOne(
            "SELECT * FROM order_payments WHERE id = ?",
            [$paymentId]
        );
        
        if (!$payment) {
            return ['success' => false, 'message' => 'পেমেন্ট রেকর্ড পাওয়া যায়নি'];
        }
        
        try {
            Database::beginTransaction();
            
            // Update payment record
            Database::execute(
                "UPDATE order_payments SET status = ?, verified_by_admin_id = ?, verified_at = NOW(), admin_note = ? WHERE id = ?",
                [$status, $adminId, $note, $paymentId]
            );
            
            // Update order payment status
            Database::execute(
                "UPDATE orders SET payment_status = ? WHERE id = ?",
                [$status, $payment['order_id']]
            );
            
            // If verified, move to Processing
            if ($status === 'Verified') {
                self::updateStatus($payment['order_id'], 'Processing', $adminId, 'পেমেন্ট ভেরিফাই হয়েছে');
            }
            
            Database::commit();
            
            $msg = $status === 'Verified' ? 'পেমেন্ট ভেরিফাই করা হয়েছে' : 'পেমেন্ট প্রত্যাখ্যাত হয়েছে';
            return ['success' => true, 'message' => $msg];
            
        } catch (Exception $e) {
            Database::rollback();
            logError('Payment verification failed', ['error' => $e->getMessage()]);
            return ['success' => false, 'message' => 'পেমেন্ট ভেরিফিকেশনে সমস্যা হয়েছে'];
        }
    }
    
    /**
     * Update order status (admin)
     * @param int $orderId
     * @param string $status
     * @param int $adminId
     * @param string|null $note
     * @return array
     */
    public static function updateStatus(int $orderId, string $status, int $adminId, ?string $note = null): array {
        $validStatuses = ['Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled'];
        
        if (!in_array($status, $validStatuses)) {
            return ['success' => false, 'message' => 'অবৈধ স্ট্যাটাস'];
        }
        
        $order = Database::fetchOne("SELECT status FROM orders WHERE id = ?", [$orderId]);
        
        if (!$order) {
            return ['success' => false, 'message' => 'অর্ডার পাওয়া যায়নি'];
        }
        
        if ($order['status'] === $status) {
            return ['success' => false, 'message' => 'স্ট্যাটাস ইতিমধ্যে আপডেট'];
        }
        
        try {
            Database::beginTransaction();
            
            // Update order status
            Database::execute(
                "UPDATE orders SET status = ? WHERE id = ?",
                [$status, $orderId]
            );
            
            // Add status history
            Database::insert(
                "INSERT INTO order_status_history (order_id, status, note, changed_by_admin_id) VALUES (?, ?, ?, ?)",
                [$orderId, $status, $note, $adminId]
            );
            
            // If delivered, update total_sold count
            if ($status === 'Delivered') {
                $items = Database::fetchAll(
                    "SELECT product_id, qty FROM order_items WHERE order_id = ?",
                    [$orderId]
                );
                
                foreach ($items as $item) {
                    Database::execute(
                        "UPDATE products SET total_sold = total_sold + ? WHERE id = ?",
                        [$item['qty'], $item['product_id']]
                    );
                }
            }
            
            // If cancelled, restore stock
            if ($status === 'Cancelled' && $order['status'] !== 'Cancelled') {
                $items = Database::fetchAll(
                    "SELECT product_id, qty FROM order_items WHERE order_id = ?",
                    [$orderId]
                );
                
                foreach ($items as $item) {
                    Database::execute(
                        "UPDATE products SET stock_qty = stock_qty + ? WHERE id = ?",
                        [$item['qty'], $item['product_id']]
                    );
                }
            }
            
            Database::commit();
            
            return ['success' => true, 'message' => 'অর্ডার স্ট্যাটাস আপডেট হয়েছে'];
            
        } catch (Exception $e) {
            Database::rollback();
            logError('Status update failed', ['error' => $e->getMessage()]);
            return ['success' => false, 'message' => 'স্ট্যাটাস আপডেটে সমস্যা হয়েছে'];
        }
    }
    
    /**
     * Get order by ID with items
     * @param int $orderId
     * @return array|null
     */
    public static function getById(int $orderId): ?array {
        $order = Database::fetchOne(
            "SELECT o.*, u.full_name as user_name, u.email as user_email, u.phone as user_phone
             FROM orders o
             JOIN users u ON o.user_id = u.id
             WHERE o.id = ?",
            [$orderId]
        );
        
        if (!$order) return null;
        
        $order['items'] = Database::fetchAll(
            "SELECT oi.*, 
                    (SELECT file_path FROM product_images WHERE product_id = oi.product_id AND is_primary = 1 LIMIT 1) as image
             FROM order_items oi
             WHERE oi.order_id = ?",
            [$orderId]
        );
        
        $order['payments'] = Database::fetchAll(
            "SELECT op.*, a.full_name as verified_by_name
             FROM order_payments op
             LEFT JOIN admins a ON op.verified_by_admin_id = a.id
             WHERE op.order_id = ?
             ORDER BY op.submitted_at DESC",
            [$orderId]
        );
        
        $order['status_history'] = Database::fetchAll(
            "SELECT osh.*, a.full_name as changed_by_name
             FROM order_status_history osh
             LEFT JOIN admins a ON osh.changed_by_admin_id = a.id
             WHERE osh.order_id = ?
             ORDER BY osh.created_at ASC",
            [$orderId]
        );
        
        return $order;
    }
    
    /**
     * Get orders for user
     * @param int $userId
     * @param int $limit
     * @param int $offset
     * @return array
     */
    public static function getByUser(int $userId, int $limit = 20, int $offset = 0): array {
        return Database::fetchAll(
            "SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?",
            [$userId, $limit, $offset]
        );
    }
    
    /**
     * Get all orders (admin)
     * @param array $filters
     * @param int $limit
     * @param int $offset
     * @return array
     */
    public static function getAll(array $filters = [], int $limit = 20, int $offset = 0): array {
        $where = "1=1";
        $params = [];
        
        if (!empty($filters['status'])) {
            $where .= " AND o.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['payment_status'])) {
            $where .= " AND o.payment_status = ?";
            $params[] = $filters['payment_status'];
        }
        
        if (!empty($filters['search'])) {
            $where .= " AND (o.order_no LIKE ? OR o.shipping_name LIKE ? OR o.shipping_phone LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }
        
        $params[] = $limit;
        $params[] = $offset;
        
        return Database::fetchAll(
            "SELECT o.*, u.full_name as user_name
             FROM orders o
             JOIN users u ON o.user_id = u.id
             WHERE {$where}
             ORDER BY o.created_at DESC
             LIMIT ? OFFSET ?",
            $params
        );
    }
    
    /**
     * Get order stats (admin dashboard)
     * @return array
     */
    public static function getStats(): array {
        return [
            'total_orders' => (int)Database::fetchColumn("SELECT COUNT(*) FROM orders"),
            'pending_orders' => (int)Database::fetchColumn("SELECT COUNT(*) FROM orders WHERE status = 'Pending'"),
            'processing_orders' => (int)Database::fetchColumn("SELECT COUNT(*) FROM orders WHERE status = 'Processing'"),
            'total_sales' => (float)Database::fetchColumn("SELECT COALESCE(SUM(grand_total), 0) FROM orders WHERE status = 'Delivered'"),
            'pending_payments' => (int)Database::fetchColumn("SELECT COUNT(*) FROM orders WHERE payment_status = 'Submitted'"),
            'today_orders' => (int)Database::fetchColumn("SELECT COUNT(*) FROM orders WHERE DATE(created_at) = CURDATE()"),
            'today_sales' => (float)Database::fetchColumn("SELECT COALESCE(SUM(grand_total), 0) FROM orders WHERE DATE(created_at) = CURDATE() AND status = 'Delivered'")
        ];
    }
}
