<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use App\Core\Validator;
use App\Models\Category;
use App\Models\Order;
use App\Models\Product;
use App\Models\User;
use Exception;

/**
 * Admin Controller
 * Handles admin dashboard functionality, analytics, and reporting
 */
class AdminController extends BaseController
{
    /**
     * @var mixed
     */
    private $orderModel;
    /**
     * @var mixed
     */
    private $productModel;
    /**
     * @var mixed
     */
    private $userModel;
    /**
     * @var mixed
     */
    private $categoryModel;

    public function __construct()
    {
        parent::__construct();
        $this->orderModel = new Order();
        $this->productModel = new Product();
        $this->userModel = new User();
        $this->categoryModel = new Category();
    }

    /**
     * Get dashboard overview statistics
     * GET /api/admin/dashboard
     */
    public function dashboard()
    {
        try {
            // Get date range from query parameters
            $startDate = $_GET['start_date'] ?? date('Y-m-01'); // First day of current month
            $endDate = $_GET['end_date'] ?? date('Y-m-d'); // Today

            // Get real statistics from database
            $stats = [
                'sales' => $this->getSalesStats($startDate, $endDate),
                'orders' => $this->getOrderStats($startDate, $endDate),
                'products' => $this->getProductStats(),
                'customers' => $this->getCustomerStats($startDate, $endDate)
            ];

            $this->jsonResponse([
                'success' => true,
                'data' => [
                    'period' => [
                        'start_date' => $startDate,
                        'end_date' => $endDate
                    ],
                    'sales' => $stats['sales'],
                    'orders' => $stats['orders'],
                    'products' => $stats['products'],
                    'customers' => $stats['customers']
                ]
            ]);

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to fetch dashboard data',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Get sales analytics
     * GET /api/admin/analytics/sales
     */
    public function salesAnalytics()
    {
        try {
            $period = $_GET['period'] ?? 'month'; // day, week, month, year
            $startDate = $_GET['start_date'] ?? date('Y-m-01');
            $endDate = $_GET['end_date'] ?? date('Y-m-d');

            $analytics = [
                'revenue_trend' => $this->getRevenueTrend($period, $startDate, $endDate),
                'top_products' => $this->getTopSellingProducts($startDate, $endDate),
                'sales_by_category' => $this->getSalesByCategory($startDate, $endDate),
                'payment_methods' => $this->getPaymentMethodStats($startDate, $endDate),
                'order_status_distribution' => $this->getOrderStatusDistribution($startDate, $endDate)
            ];

            $this->jsonResponse([
                'success' => true,
                'data' => $analytics
            ]);

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to fetch sales analytics',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Get customer analytics
     * GET /api/admin/analytics/customers
     */
    public function customerAnalytics()
    {
        try {
            $startDate = $_GET['start_date'] ?? date('Y-m-01');
            $endDate = $_GET['end_date'] ?? date('Y-m-d');

            $analytics = [
                'new_customers' => $this->getNewCustomersData($startDate, $endDate),
                'customer_lifetime_value' => $this->getCustomerLifetimeValue(),
                'repeat_customers' => $this->getRepeatCustomerStats($startDate, $endDate),
                'customer_segments' => $this->getCustomerSegments(),
                'geographic_distribution' => $this->getCustomerGeographicData()
            ];

            $this->jsonResponse([
                'success' => true,
                'data' => $analytics
            ]);

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to fetch customer analytics',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Get inventory analytics
     * GET /api/admin/analytics/inventory
     */
    public function inventoryAnalytics()
    {
        try {
            $analytics = [
                'low_stock_products' => $this->getLowStockProducts(),
                'out_of_stock_products' => $this->getOutOfStockProducts(),
                'inventory_value' => $this->getInventoryValue(),
                'stock_movement' => $this->getStockMovement(),
                'product_performance' => $this->getProductPerformance()
            ];

            $this->jsonResponse([
                'success' => true,
                'data' => $analytics
            ]);

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to fetch inventory analytics',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Get inventory overview
     * GET /api/admin/inventory
     */
    public function inventory()
    {
        try {
            $page = (int) ($_GET['page'] ?? 1);
            $limit = (int) ($_GET['limit'] ?? 20);
            $status = $_GET['status'] ?? null; // low_stock, out_of_stock, in_stock
            $search = $_GET['search'] ?? null;

            $inventory = $this->getInventoryData($page, $limit, $status, $search);

            $this->jsonResponse([
                'success' => true,
                'data' => $inventory
            ]);

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to fetch inventory data',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Update product stock
     * POST /api/admin/inventory/{productId}/stock
     */
    public function updateStock($productId)
    {
        try {
            if (!is_numeric($productId)) {
                $this->jsonResponse([
                    'success' => false,
                    'error' => 'Invalid product ID'
                ], 400);
                return;
            }

            $data = $this->getJsonInput();

            // Validate input
            $validator = new Validator();
            $isValid = $validator->validate($data, [
                'variant_id' => ['numeric'],
                'quantity' => ['required', 'numeric', 'min:0'],
                'operation' => ['required', 'in:set,add,subtract'],
                'reason' => ['max:500']
            ]);

            if (!$isValid) {
                $this->jsonResponse([
                    'success' => false,
                    'error' => 'Validation failed',
                    'errors' => $validator->getErrors()
                ], 400);
                return;
            }

            $variantId = $data['variant_id'] ?? null;
            $quantity = (int) $data['quantity'];
            $operation = $data['operation'];
            $reason = $data['reason'] ?? 'Manual adjustment';

            // Check if product exists
            $product = $this->productModel->find($productId);
            if (!$product) {
                $this->jsonResponse([
                    'success' => false,
                    'error' => 'Product not found'
                ], 404);
                return;
            }

            // Update stock based on operation
            $result = $this->updateProductStock($productId, $variantId, $quantity, $operation, $reason);

            if ($result['success']) {
                $this->jsonResponse([
                    'success' => true,
                    'message' => 'Stock updated successfully',
                    'data' => [
                        'product_id' => $productId,
                        'variant_id' => $variantId,
                        'new_quantity' => $result['new_quantity'],
                        'operation' => $operation,
                        'adjustment' => $quantity
                    ]
                ]);
            } else {
                $this->jsonResponse([
                    'success' => false,
                    'error' => $result['error']
                ], 400);
            }

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to update stock',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Get inventory alerts
     * GET /api/admin/inventory/alerts
     */
    public function inventoryAlerts()
    {
        try {
            $alerts = [
                'low_stock' => $this->getLowStockProducts(),
                'out_of_stock' => $this->getOutOfStockProducts(),
                'overstocked' => $this->getOverstockedProducts(),
                'negative_stock' => $this->getNegativeStockProducts()
            ];

            $this->jsonResponse([
                'success' => true,
                'data' => $alerts
            ]);

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to fetch inventory alerts',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Get sales statistics
     */
    private function getSalesStats(string $startDate, string $endDate): array
    {
        $db = \App\Core\Database::getInstance();

        try {
            // Total revenue for current period
            $revenueResult = $db->queryOne(
                "SELECT
                    SUM(total_amount) as total_revenue,
                    COUNT(*) as total_orders,
                    AVG(total_amount) as average_order_value
                 FROM orders
                 WHERE payment_status = 'paid'
                 AND DATE(created_at) BETWEEN ? AND ?",
                [$startDate, $endDate]
            );

            // Previous period comparison
            $daysDiff = (strtotime($endDate) - strtotime($startDate)) / (60 * 60 * 24);
            $prevStartDate = date('Y-m-d', strtotime($startDate.' -'.$daysDiff.' days'));
            $prevEndDate = date('Y-m-d', strtotime($startDate.' -1 day'));

            $prevRevenueResult = $db->queryOne(
                "SELECT SUM(total_amount) as total_revenue FROM orders
                 WHERE payment_status = 'paid'
                 AND DATE(created_at) BETWEEN ? AND ?",
                [$prevStartDate, $prevEndDate]
            );

            $currentRevenue = (float) ($revenueResult['total_revenue'] ?? 0);
            $prevRevenue = (float) ($prevRevenueResult['total_revenue'] ?? 0);
            $revenueChange = $prevRevenue > 0 ? (($currentRevenue - $prevRevenue) / $prevRevenue) * 100 : 0;

            // Get daily sales trend for the period
            $trendData = $this->getDailySalesTrend($startDate, $endDate);

            return [
                'total_revenue' => $currentRevenue,
                'total_orders' => (int) ($revenueResult['total_orders'] ?? 0),
                'average_order_value' => (float) ($revenueResult['average_order_value'] ?? 0),
                'revenue_change' => round($revenueChange, 1),
                'currency' => 'THB',
                'trend' => $trendData
            ];
        } catch (Exception $e) {
            return [
                'total_revenue' => 0.0,
                'total_orders' => 0,
                'average_order_value' => 0.0,
                'revenue_change' => 0.0,
                'currency' => 'THB'
            ];
        }
    }

    /**
     * Get order statistics
     */
    private function getOrderStats(string $startDate, string $endDate): array
    {
        $db = \App\Core\Database::getInstance();

        try {
            $result = $db->query(
                "SELECT
                    status,
                    COUNT(*) as count,
                    SUM(total_amount) as total_amount
                 FROM orders
                 WHERE DATE(created_at) BETWEEN ? AND ?
                 GROUP BY status",
                [$startDate, $endDate]
            );

            $stats = [
                'total_orders' => 0,
                'orders_change' => 0,
                'by_status' => []
            ];

            foreach ($result as $row) {
                $stats['total_orders'] += $row['count'];
                $stats['by_status'][$row['status']] = [
                    'count' => (int) $row['count'],
                    'total_amount' => (float) $row['total_amount']
                ];
            }

            return $stats;
        } catch (Exception $e) {
            // Return default stats if query fails
            return [
                'total_orders' => 0,
                'orders_change' => 0,
                'by_status' => []
            ];
        }
    }

    /**
     * Get product statistics
     */
    private function getProductStats(): array
    {
        $db = \App\Core\Database::getInstance();

        try {
            $result = $db->queryOne(
                "SELECT
                    COUNT(*) as total_products,
                    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_products,
                    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) as inactive_products,
                    SUM(CASE WHEN featured = 1 THEN 1 ELSE 0 END) as featured_products
                 FROM products"
            );

            // Get products created this month vs last month for change calculation
            $thisMonth = date('Y-m-01');
            $lastMonth = date('Y-m-01', strtotime('-1 month'));
            $lastMonthEnd = date('Y-m-t', strtotime('-1 month'));

            $thisMonthResult = $db->queryOne(
                "SELECT COUNT(*) as this_month_products FROM products WHERE DATE(created_at) >= ?",
                [$thisMonth]
            );

            $lastMonthResult = $db->queryOne(
                "SELECT COUNT(*) as last_month_products FROM products WHERE DATE(created_at) BETWEEN ? AND ?",
                [$lastMonth, $lastMonthEnd]
            );

            $thisMonthCount = (int) ($thisMonthResult['this_month_products'] ?? 0);
            $lastMonthCount = (int) ($lastMonthResult['last_month_products'] ?? 0);
            $productsChange = $lastMonthCount > 0 ? (($thisMonthCount - $lastMonthCount) / $lastMonthCount) * 100 : 0;

            return [
                'total_products' => (int) ($result['total_products'] ?? 0),
                'active_products' => (int) ($result['active_products'] ?? 0),
                'inactive_products' => (int) ($result['inactive_products'] ?? 0),
                'featured_products' => (int) ($result['featured_products'] ?? 0),
                'products_change' => round($productsChange, 1)
            ];
        } catch (Exception $e) {
            return [
                'total_products' => 0,
                'active_products' => 0,
                'inactive_products' => 0,
                'featured_products' => 0,
                'products_change' => 0
            ];
        }
    }

    /**
     * Get customer statistics
     */
    private function getCustomerStats(string $startDate, string $endDate): array
    {
        $db = \App\Core\Database::getInstance();

        try {
            $result = $db->queryOne(
                "SELECT
                    COUNT(*) as total_customers,
                    SUM(CASE WHEN DATE(created_at) BETWEEN ? AND ? THEN 1 ELSE 0 END) as new_customers,
                    SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) as active_customers
                 FROM users
                 WHERE role = 'customer'",
                [$startDate, $endDate]
            );

            // Get previous period for comparison
            $daysDiff = (strtotime($endDate) - strtotime($startDate)) / (60 * 60 * 24);
            $prevStartDate = date('Y-m-d', strtotime($startDate.' -'.$daysDiff.' days'));
            $prevEndDate = date('Y-m-d', strtotime($startDate.' -1 day'));

            $prevResult = $db->queryOne(
                "SELECT COUNT(*) as prev_new_customers
                 FROM users
                 WHERE role = 'customer' AND DATE(created_at) BETWEEN ? AND ?",
                [$prevStartDate, $prevEndDate]
            );

            $currentNew = (int) ($result['new_customers'] ?? 0);
            $prevNew = (int) ($prevResult['prev_new_customers'] ?? 0);
            $customersChange = $prevNew > 0 ? (($currentNew - $prevNew) / $prevNew) * 100 : 0;

            return [
                'total_customers' => (int) ($result['total_customers'] ?? 0),
                'new_customers' => $currentNew,
                'active_customers' => (int) ($result['active_customers'] ?? 0),
                'customers_change' => round($customersChange, 1)
            ];
        } catch (Exception $e) {
            return [
                'total_customers' => 0,
                'new_customers' => 0,
                'active_customers' => 0,
                'customers_change' => 0
            ];
        }
    }

    /**
     * Get inventory statistics
     */
    private function getInventoryStats(): array
    {
        $db = \App\Core\Database::getInstance();

        // Get low stock and out of stock counts
        $stockResult = $db->queryOne(
            "SELECT
                COUNT(CASE WHEN pv.inventory_quantity <= p.min_stock_level AND pv.inventory_quantity > 0 THEN 1 END) as low_stock_count,
                COUNT(CASE WHEN pv.inventory_quantity = 0 THEN 1 END) as out_of_stock_count,
                SUM(pv.inventory_quantity * COALESCE(pv.price, p.base_price)) as inventory_value
             FROM products p
             LEFT JOIN product_variants pv ON p.id = pv.product_id
             WHERE p.status = 'active' AND p.track_inventory = 1"
        );

        return [
            'low_stock_count' => (int) ($stockResult['low_stock_count'] ?? 0),
            'out_of_stock_count' => (int) ($stockResult['out_of_stock_count'] ?? 0),
            'inventory_value' => (float) ($stockResult['inventory_value'] ?? 0)
        ];
    }

    /**
     * Get inventory data with pagination and filtering
     */
    private function getInventoryData(int $page, int $limit, ?string $status, ?string $search): array
    {
        $db = \App\Core\Database::getInstance();
        $offset = ($page - 1) * $limit;

        // Build WHERE clause
        $whereConditions = ['p.status = ?'];
        $params = ['active'];

        if ($search) {
            $whereConditions[] = '(p.name LIKE ? OR p.sku LIKE ?)';
            $params[] = "%{$search}%";
            $params[] = "%{$search}%";
        }

        $whereClause = implode(' AND ', $whereConditions);

        // Get total count
        $countSql = "SELECT COUNT(DISTINCT p.id) as total
                     FROM products p
                     WHERE {$whereClause}";
        $totalResult = $db->queryOne($countSql, $params);
        $total = (int) ($totalResult['total'] ?? 0);

        // Get inventory data
        $sql = "SELECT
                    p.id,
                    p.name,
                    p.sku,
                    p.base_price,
                    p.min_stock_level,
                    p.max_stock_level,
                    p.track_inventory,
                    p.inventory_quantity,
                    c.name as category_name
                FROM products p
                LEFT JOIN categories c ON p.category_id = c.id
                WHERE {$whereClause}
                ORDER BY p.name ASC
                LIMIT {$limit} OFFSET {$offset}";

        $results = $db->query($sql, $params);

        // Format products
        $products = [];
        foreach ($results as $row) {
            $products[] = [
                'id' => (int) $row['id'],
                'name' => $row['name'],
                'sku' => $row['sku'],
                'category' => $row['category_name'],
                'base_price' => (float) $row['base_price'],
                'min_stock_level' => (int) $row['min_stock_level'],
                'max_stock_level' => (int) $row['max_stock_level'],
                'track_inventory' => (bool) $row['track_inventory'],
                'inventory_quantity' => (int) $row['inventory_quantity'],
                'status' => $this->getStockStatus((int) $row['inventory_quantity'], (int) $row['min_stock_level'])
            ];
        }

        return [
            'inventory' => $products,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $limit,
                'total' => $total,
                'total_pages' => ceil($total / $limit)
            ],
            'stats' => $this->getInventoryStats()
        ];
    }

    /**
     * Update product stock
     */
    private function updateProductStock(int $productId, ?int $variantId, int $quantity, string $operation, string $reason): array
    {
        $db = \App\Core\Database::getInstance();

        try {
            $db->getConnection()->beginTransaction();

            // Get current stock from products table
            $currentStock = $db->queryOne(
                "SELECT inventory_quantity FROM products WHERE id = ?",
                [$productId]
            );

            if (!$currentStock) {
                $db->getConnection()->rollBack();
                return ['success' => false, 'error' => 'Product not found'];
            }

            $currentQuantity = (int) $currentStock['inventory_quantity'];

            // Calculate new quantity based on operation
            switch ($operation) {
                case 'set':
                    $newQuantity = $quantity;
                    break;
                case 'add':
                    $newQuantity = $currentQuantity + $quantity;
                    break;
                case 'subtract':
                    $newQuantity = $currentQuantity - $quantity;
                    if ($newQuantity < 0) {
                        $db->getConnection()->rollBack();
                        return ['success' => false, 'error' => 'Insufficient stock for subtraction'];
                    }
                    break;
                default:
                    $db->getConnection()->rollBack();
                    return ['success' => false, 'error' => 'Invalid operation'];
            }

            // Update stock
            $db->execute(
                "UPDATE products SET inventory_quantity = ?, updated_at = NOW() WHERE id = ?",
                [$newQuantity, $productId]
            );

            // Log the inventory adjustment
            $this->logInventoryAdjustment($productId, $variantId, $currentQuantity, $newQuantity, $operation, $reason);

            $db->getConnection()->commit();

            return [
                'success' => true,
                'new_quantity' => $newQuantity
            ];

        } catch (Exception $e) {
            $db->getConnection()->rollBack();
            return ['success' => false, 'error' => 'Database error: '.$e->getMessage()];
        }
    }

    /**
     * Log inventory adjustment
     */
    private function logInventoryAdjustment(int $productId, ?int $variantId, int $oldQuantity, int $newQuantity, string $operation, string $reason): void
    {
        $db = \App\Core\Database::getInstance();

        // Create inventory_adjustments table if it doesn't exist
        $db->execute("
            CREATE TABLE IF NOT EXISTS inventory_adjustments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                product_id INT NOT NULL,
                variant_id INT NULL,
                old_quantity INT NOT NULL,
                new_quantity INT NOT NULL,
                adjustment_quantity INT NOT NULL,
                operation ENUM('set', 'add', 'subtract') NOT NULL,
                reason TEXT,
                admin_id INT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
            )
        ");

        $adjustmentQuantity = $newQuantity - $oldQuantity;
        $adminId = $_SESSION['user_id'] ?? null;

        $db->execute(
            "INSERT INTO inventory_adjustments (product_id, variant_id, old_quantity, new_quantity, adjustment_quantity, operation, reason, admin_id)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            [$productId, $variantId, $oldQuantity, $newQuantity, $adjustmentQuantity, $operation, $reason, $adminId]
        );
    }

    /**
     * Get stock status based on quantity and min level
     */
    private function getStockStatus(int $quantity, int $minLevel): string
    {
        if ($quantity <= 0) {
            return 'out_of_stock';
        } elseif ($quantity <= $minLevel) {
            return 'low_stock';
        } else {
            return 'in_stock';
        }
    }

    /**
     * Get low stock products
     */
    private function getLowStockProducts(): array
    {
        $db = \App\Core\Database::getInstance();

        $result = $db->query(
            "SELECT
                p.id,
                p.name,
                p.sku,
                p.inventory_quantity,
                p.min_stock_level
             FROM products p
             WHERE p.track_inventory = 1
             AND p.inventory_quantity <= p.min_stock_level
             AND p.inventory_quantity > 0
             ORDER BY p.inventory_quantity ASC
             LIMIT 20"
        );

        return array_map(function ($row) {
            return [
                'product_id' => (int) $row['id'],
                'name' => $row['name'],
                'sku' => $row['sku'],
                'current_stock' => (int) $row['inventory_quantity'],
                'min_stock_level' => (int) $row['min_stock_level']
            ];
        }, $result);
    }

    /**
     * Get out of stock products
     */
    private function getOutOfStockProducts(): array
    {
        $db = \App\Core\Database::getInstance();

        $result = $db->query(
            "SELECT
                p.id,
                p.name,
                p.sku
             FROM products p
             WHERE p.track_inventory = 1
             AND p.inventory_quantity = 0
             ORDER BY p.name ASC
             LIMIT 20"
        );

        return array_map(function ($row) {
            return [
                'product_id' => (int) $row['id'],
                'name' => $row['name'],
                'sku' => $row['sku']
            ];
        }, $result);
    }

    /**
     * Get overstocked products
     */
    private function getOverstockedProducts(): array
    {
        $db = \App\Core\Database::getInstance();

        $result = $db->query(
            "SELECT
                p.id,
                p.name,
                p.sku,
                p.inventory_quantity,
                p.max_stock_level
             FROM products p
             WHERE p.track_inventory = 1
             AND p.max_stock_level > 0
             AND p.inventory_quantity > p.max_stock_level
             ORDER BY (p.inventory_quantity - p.max_stock_level) DESC
             LIMIT 20"
        );

        return array_map(function ($row) {
            return [
                'product_id' => (int) $row['id'],
                'name' => $row['name'],
                'sku' => $row['sku'],
                'current_stock' => (int) $row['inventory_quantity'],
                'max_stock_level' => (int) $row['max_stock_level'],
                'excess_quantity' => (int) $row['inventory_quantity'] - (int) $row['max_stock_level']
            ];
        }, $result);
    }

    /**
     * Get products with negative stock
     */
    private function getNegativeStockProducts(): array
    {
        $db = \App\Core\Database::getInstance();

        $result = $db->query(
            "SELECT
                p.id,
                p.name,
                p.sku,
                p.inventory_quantity
             FROM products p
             WHERE p.track_inventory = 1
             AND p.inventory_quantity < 0
             ORDER BY p.inventory_quantity ASC
             LIMIT 20"
        );

        return array_map(function ($row) {
            return [
                'product_id' => (int) $row['id'],
                'name' => $row['name'],
                'sku' => $row['sku'],
                'current_stock' => (int) $row['inventory_quantity']
            ];
        }, $result);
    }

    // Placeholder methods for additional analytics
    /**
     * @param string $period
     * @param string $startDate
     * @param string $endDate
     */
    private function getRevenueTrend(string $period, string $startDate, string $endDate): array
    {return [];}
    /**
     * @param string $startDate
     * @param string $endDate
     */
    private function getTopSellingProducts(string $startDate, string $endDate): array
    {return [];}
    /**
     * @param string $startDate
     * @param string $endDate
     */
    private function getSalesByCategory(string $startDate, string $endDate): array
    {return [];}
    /**
     * @param string $startDate
     * @param string $endDate
     */
    private function getPaymentMethodStats(string $startDate, string $endDate): array
    {return [];}
    /**
     * @param string $startDate
     * @param string $endDate
     */
    private function getOrderStatusDistribution(string $startDate, string $endDate): array
    {return [];}
    /**
     * @param string $startDate
     * @param string $endDate
     */
    private function getNewCustomersData(string $startDate, string $endDate): array
    {return [];}
    private function getCustomerLifetimeValue(): array
    {return [];}
    /**
     * @param string $startDate
     * @param string $endDate
     */
    private function getRepeatCustomerStats(string $startDate, string $endDate): array
    {return [];}
    private function getCustomerSegments(): array
    {return [];}
    private function getCustomerGeographicData(): array
    {return [];}
    private function getInventoryValue(): array
    {return [];}
    private function getStockMovement(): array
    {return [];}
    private function getProductPerformance(): array
    {return [];}

    /**
     * Get customers for admin management
     * GET /api/admin/customers
     */
    public function customers()
    {
        try {
            $page = (int) ($_GET['page'] ?? 1);
            $limit = min((int) ($_GET['limit'] ?? 20), 100);
            $search = $_GET['search'] ?? '';
            $role = $_GET['role'] ?? '';
            $status = $_GET['status'] ?? '';

            $filters = [];
            if ($search) {
                $filters['search'] = $search;
            }

            if ($role) {
                $filters['role'] = $role;
            }

            if ($status !== '') {
                $filters['is_active'] = (bool) $status;
            }

            $offset = ($page - 1) * $limit;
            $result = $this->userModel->getUsers($filters, $limit, $offset);

            $this->jsonResponse([
                'success' => true,
                'data' => [
                    'users' => $result['users'],
                    'pagination' => [
                        'current_page' => $page,
                        'per_page' => $limit,
                        'total' => $result['total'],
                        'total_pages' => ceil($result['total'] / $limit)
                    ]
                ]
            ]);

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to fetch customers',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Get products for admin management
     * GET /api/admin/products
     */
    public function products()
    {
        try {
            $page = (int) ($_GET['page'] ?? 1);
            $limit = min((int) ($_GET['limit'] ?? 20), 100);
            $category = $_GET['category'] ?? '';
            $status = $_GET['status'] ?? '';
            $search = $_GET['search'] ?? '';

            $filters = [
                'status' => $status ?: null,
                'category_id' => $category ?: null,
                'search' => $search ?: null
            ];

            $offset = ($page - 1) * $limit;
            $result = $this->productModel->getProducts($filters, $limit, $offset);

            $this->jsonResponse([
                'success' => true,
                'data' => [
                    'products' => $result['products'],
                    'pagination' => [
                        'current_page' => $page,
                        'per_page' => $limit,
                        'total' => $result['total'],
                        'total_pages' => ceil($result['total'] / $limit)
                    ]
                ]
            ]);

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to fetch products',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Get categories for admin management
     * GET /api/admin/categories
     */
    public function categories()
    {
        try {
            $search = $_GET['search'] ?? '';
            $status = $_GET['status'] ?? '';
            $parent_id = $_GET['parent_id'] ?? null;

            $filters = [
                'search' => $search ?: null,
                'status' => $status ?: null,
                'parent_id' => $parent_id
            ];

            $categories = $this->categoryModel->getCategories($filters);

            $this->jsonResponse([
                'success' => true,
                'data' => [
                    'categories' => $categories
                ]
            ]);

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to fetch categories',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Create a new category
     * POST /api/admin/categories
     */
    public function createCategory()
    {
        try {
            $data = $this->getJsonInput();

            // Validate input
            $validator = new Validator();
            $rules = [
                'name' => ['required', 'string', 'min:2', 'max:100'],
                'description' => ['optional', 'string'],
                'parent_id' => ['optional', 'integer'],
                'status' => ['optional', 'string'],
                'sort_order' => ['optional', 'integer']
            ];

            if (!$validator->validate($data, $rules)) {
                $this->jsonResponse([
                    'success' => false,
                    'error' => 'Validation failed',
                    'errors' => $validator->getErrors()
                ], 400);
                return;
            }

            $categoryData = [
                'name' => trim($data['name']),
                'description' => isset($data['description']) ? trim($data['description']) : null,
                'parent_id' => isset($data['parent_id']) ? (int)$data['parent_id'] : null,
                'status' => $data['status'] ?? 'active',
                'sort_order' => $data['sort_order'] ?? 0
            ];

            $categoryId = $this->categoryModel->create($categoryData);

            if ($categoryId) {
                $category = $this->categoryModel->findById($categoryId);
                $this->jsonResponse([
                    'success' => true,
                    'message' => 'Category created successfully',
                    'data' => [
                        'category' => $category
                    ]
                ], 201);
            } else {
                throw new Exception('Failed to create category');
            }

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to create category',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Update category
     * POST /api/admin/categories/{id} with _method=PUT
     */
    public function updateCategory($categoryId)
    {
        try {
            $data = $this->getJsonInput();

            // Validate input
            $validator = new Validator();
            $rules = [
                'name' => ['required', 'string', 'min:2', 'max:100'],
                'description' => ['optional', 'string'],
                'parent_id' => ['optional', 'integer'],
                'status' => ['optional', 'string'],
                'sort_order' => ['optional', 'integer']
            ];

            if (!$validator->validate($data, $rules)) {
                $this->jsonResponse([
                    'success' => false,
                    'error' => 'Validation failed',
                    'errors' => $validator->getErrors()
                ], 400);
                return;
            }

            // Check if category exists
            $category = $this->categoryModel->findById($categoryId);
            if (!$category) {
                $this->jsonResponse([
                    'success' => false,
                    'error' => 'Category not found'
                ], 404);
                return;
            }

            $categoryData = [
                'name' => trim($data['name']),
                'description' => isset($data['description']) ? trim($data['description']) : null,
                'parent_id' => isset($data['parent_id']) ? (int)$data['parent_id'] : null,
                'status' => $data['status'] ?? $category['status'],
                'sort_order' => $data['sort_order'] ?? $category['sort_order']
            ];

            $updated = $this->categoryModel->update($categoryId, $categoryData);

            if ($updated) {
                $updatedCategory = $this->categoryModel->findById($categoryId);
                $this->jsonResponse([
                    'success' => true,
                    'message' => 'Category updated successfully',
                    'data' => [
                        'category' => $updatedCategory
                    ]
                ]);
            } else {
                throw new Exception('Failed to update category');
            }

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to update category',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Delete category
     * POST /api/admin/categories/{id}/delete with _method=DELETE
     */
    public function deleteCategory($categoryId)
    {
        try {
            // Check if category exists
            $category = $this->categoryModel->findById($categoryId);
            if (!$category) {
                $this->jsonResponse([
                    'success' => false,
                    'error' => 'Category not found'
                ], 404);
                return;
            }

            // Check if category has products
            $productCount = $this->productModel->countByCategory($categoryId);
            if ($productCount > 0) {
                $this->jsonResponse([
                    'success' => false,
                    'error' => 'Cannot delete category with existing products',
                    'message' => "This category has {$productCount} products. Please move or delete them first."
                ], 400);
                return;
            }

            // Check if category has subcategories
            $subcategoryCount = $this->categoryModel->countSubcategories($categoryId);
            if ($subcategoryCount > 0) {
                $this->jsonResponse([
                    'success' => false,
                    'error' => 'Cannot delete category with subcategories',
                    'message' => "This category has {$subcategoryCount} subcategories. Please move or delete them first."
                ], 400);
                return;
            }

            $deleted = $this->categoryModel->delete($categoryId);

            if ($deleted) {
                $this->jsonResponse([
                    'success' => true,
                    'message' => 'Category deleted successfully'
                ]);
            } else {
                throw new Exception('Failed to delete category');
            }

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to delete category',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Get orders for admin management
     * GET /api/admin/orders
     */
    public function orders()
    {
        try {
            $page = (int) ($_GET['page'] ?? 1);
            $limit = min((int) ($_GET['limit'] ?? 20), 100);
            $status = $_GET['status'] ?? '';
            $paymentStatus = $_GET['payment_status'] ?? '';
            $dateFrom = $_GET['date_from'] ?? '';
            $dateTo = $_GET['date_to'] ?? '';

            $filters = [];
            if ($status) {
                $filters['status'] = $status;
            }

            if ($paymentStatus) {
                $filters['payment_status'] = $paymentStatus;
            }

            if ($dateFrom) {
                $filters['date_from'] = $dateFrom;
            }

            if ($dateTo) {
                $filters['date_to'] = $dateTo;
            }

            $offset = ($page - 1) * $limit;
            $result = $this->orderModel->getOrders($filters, $limit, $offset);

            $this->jsonResponse([
                'success' => true,
                'data' => [
                    'orders' => $result['orders'],
                    'pagination' => [
                        'current_page' => $page,
                        'per_page' => $limit,
                        'total' => $result['total'],
                        'total_pages' => ceil($result['total'] / $limit)
                    ]
                ]
            ]);

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to fetch orders',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Update order status
     * POST /api/admin/orders/{id}/status
     */
    public function updateOrderStatus($orderId)
    {
        try {
            $data = $this->getJsonInput();

            $validator = new Validator();
            $isValid = $validator->validate($data, [
                'status' => ['required', 'in:pending,confirmed,processing,shipped,delivered,cancelled'],
                'notes' => ['max:1000']
            ]);

            if (!$isValid) {
                $this->jsonResponse([
                    'success' => false,
                    'error' => 'Validation failed',
                    'errors' => $validator->getErrors()
                ], 400);
                return;
            }

            $success = $this->orderModel->updateStatus(
                $orderId,
                $data['status'],
                $data['notes'] ?? null
            );

            if ($success) {
                // TODO: Send notification email to customer
                $this->jsonResponse([
                    'success' => true,
                    'message' => 'Order status updated successfully'
                ]);
            } else {
                $this->jsonResponse([
                    'success' => false,
                    'error' => 'Failed to update order status'
                ], 500);
            }

        } catch (Exception $e) {
            $this->jsonResponse([
                'success' => false,
                'error' => 'Failed to update order status',
                'message' => $this->isDebugMode() ? $e->getMessage() : 'An error occurred'
            ], 500);
        }
    }

    /**
     * Get daily sales trend data for chart
     */
    private function getDailySalesTrend(string $startDate, string $endDate): array
    {
        $db = \App\Core\Database::getInstance();

        try {
            // Get daily sales data
            $result = $db->query(
                "SELECT
                    DATE(created_at) as date,
                    SUM(total_amount) as daily_revenue,
                    COUNT(*) as daily_orders
                 FROM orders
                 WHERE payment_status = 'paid'
                 AND DATE(created_at) BETWEEN ? AND ?
                 GROUP BY DATE(created_at)
                 ORDER BY DATE(created_at)",
                [$startDate, $endDate]
            );

            // Create labels and data arrays
            $labels = [];
            $data = [];

            // Fill in missing dates with 0 values
            $start = strtotime($startDate);
            $end = strtotime($endDate);
            $dailyData = [];

            // Index existing data by date
            foreach ($result as $row) {
                $dailyData[$row['date']] = (float) $row['daily_revenue'];
            }

            // Generate complete date range
            for ($current = $start; $current <= $end; $current += 86400) {
                $dateStr = date('Y-m-d', $current);
                $labels[] = date('M j', $current); // Format: "Aug 1"
                $data[] = $dailyData[$dateStr] ?? 0;
            }

            return [
                'labels' => $labels,
                'data' => $data
            ];

        } catch (Exception $e) {
            // Return empty trend data on error
            return [
                'labels' => [],
                'data' => []
            ];
        }
    }
}