<?php
class StockController extends Controller
{
    public function index(): void
    {
        Auth::requirePermission('stock.view');

        $search  = trim($this->get('search', ''));
        $catId   = (int) $this->get('category', 0);
        $stockF  = $this->get('stock', 'all');    // all | low | out | ok
        $expiryF = $this->get('expiry', 'all');   // all | expired | soon | ok

        $where  = ["p.status = 'active'"];
        $params = [];

        if ($search) {
            $where[]  = '(p.product_name LIKE ? OR p.product_code LIKE ? OR p.barcode LIKE ?)';
            $params[] = "%$search%";
            $params[] = "%$search%";
            $params[] = "%$search%";
        }
        if ($catId) {
            $where[]  = 'p.category_id = ?';
            $params[] = $catId;
        }
        if ($stockF === 'low') {
            $where[] = 'p.current_stock > 0 AND p.current_stock <= p.reorder_level';
        } elseif ($stockF === 'out') {
            $where[] = 'p.current_stock <= 0';
        } elseif ($stockF === 'ok') {
            $where[] = 'p.current_stock > p.reorder_level';
        }

        // Expiry filters
        $today = date('Y-m-d');
        $in30  = date('Y-m-d', strtotime('+30 days'));
        if ($expiryF === 'expired') {
            $where[] = "p.has_expiry = 1 AND p.expiry_date < '{$today}'";
        } elseif ($expiryF === 'soon') {
            $where[] = "p.has_expiry = 1 AND p.expiry_date BETWEEN '{$today}' AND '{$in30}'";
        } elseif ($expiryF === 'ok') {
            $where[] = "p.has_expiry = 1 AND p.expiry_date > '{$in30}'";
        }

        $sql = "SELECT p.*, c.category_name, b.brand_name, u.short_name AS unit
                FROM products p
                LEFT JOIN categories c ON c.id = p.category_id
                LEFT JOIN brands     b ON b.id = p.brand_id
                LEFT JOIN units      u ON u.id = p.unit_id
                WHERE " . implode(' AND ', $where) . "
                ORDER BY p.has_expiry DESC, p.expiry_date ASC, p.product_name";

        $pagination = Database::paginate($sql, $params, max(1, (int)$this->get('page', 1)));
        $categories = Database::fetchAll("SELECT id, category_name FROM categories WHERE status='active' ORDER BY category_name");

        // Summary stats
        $totalProducts  = (int)   Database::fetchColumn("SELECT COUNT(*) FROM products WHERE status='active'");
        $totalValue     = (float) Database::fetchColumn("SELECT COALESCE(SUM(current_stock * cost_price),0) FROM products WHERE status='active'");
        $totalSellValue = (float) Database::fetchColumn("SELECT COALESCE(SUM(current_stock * selling_price),0) FROM products WHERE status='active'");
        $lowStockCount  = (int)   Database::fetchColumn("SELECT COUNT(*) FROM products WHERE status='active' AND current_stock > 0 AND current_stock <= reorder_level");
        $outStockCount  = (int)   Database::fetchColumn("SELECT COUNT(*) FROM products WHERE status='active' AND current_stock <= 0");
        $expiredCount   = (int)   Database::fetchColumn("SELECT COUNT(*) FROM products WHERE status='active' AND has_expiry=1 AND expiry_date < '{$today}'");
        $expiringSoon   = (int)   Database::fetchColumn("SELECT COUNT(*) FROM products WHERE status='active' AND has_expiry=1 AND expiry_date BETWEEN '{$today}' AND '{$in30}'");

        $pageTitle  = 'Stock';
        $breadcrumb = [['label' => 'Stock']];
        ob_start();
        require APP_PATH . '/views/stock/index.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }
    public function movements(string $id = ''): void
    {
        Auth::requirePermission('stock.view');

        // Accept product ID from URL segment or query string
        $productId = $id ? (int)$id : (int)$this->get('product_id', 0);
        $from      = $this->get('from', date('Y-m-01'));
        $to        = $this->get('to',   date('Y-m-d'));
        $typeF     = $this->get('type', 'all');

        $where  = ['sm.created_at BETWEEN ? AND ?'];
        $params = [$from . ' 00:00:00', $to . ' 23:59:59'];

        if ($productId) {
            $where[]  = 'sm.product_id = ?';
            $params[] = $productId;
        }
        if ($typeF !== 'all') {
            $where[]  = 'sm.movement_type = ?';
            $params[] = $typeF;
        }

        $sql = 'SELECT sm.*, p.product_name, p.product_code, u.name AS user_name
                FROM stock_movements sm
                JOIN products p  ON p.id = sm.product_id
                LEFT JOIN users u ON u.id = sm.created_by
                WHERE ' . implode(' AND ', $where) . '
                ORDER BY sm.created_at DESC, sm.id DESC';

        $pagination = Database::paginate($sql, $params, max(1, (int)$this->get('page', 1)));

        // Summary totals for the current filter
        $summary = Database::fetchOne(
            'SELECT COALESCE(SUM(quantity_in),0)  AS total_in,
                    COALESCE(SUM(quantity_out),0) AS total_out,
                    COUNT(*)                      AS total_rows
             FROM stock_movements sm
             WHERE ' . implode(' AND ', $where),
            $params
        );

        $products = Database::fetchAll(
            "SELECT id, product_name, product_code FROM products WHERE status='active' ORDER BY product_name"
        );

        // Distinct movement types for filter dropdown
        $movTypes = Database::fetchAll(
            'SELECT DISTINCT movement_type FROM stock_movements ORDER BY movement_type'
        );

        $pageTitle  = 'Stock Movements';
        $breadcrumb = [
            ['label' => 'Stock', 'url' => APP_URL . '/stock'],
            ['label' => 'Movements'],
        ];
        ob_start();
        require APP_PATH . '/views/stock/movements.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }
    public function adjustForm(string $id = ''): void
    {
        Auth::requirePermission('stock.adjust');

        // Accept product ID from URL segment or query string
        $preProduct = $id ? (int)$id : (int)$this->get('product', 0);

        $products = Database::fetchAll(
            "SELECT id, product_name, product_code, current_stock, reorder_level,
                    unit_id, (SELECT short_name FROM units WHERE id = p.unit_id) AS unit
             FROM products p
             WHERE status = 'active'
             ORDER BY product_name"
        );

        // Recent adjustments (last 15)
        $recentAdjustments = Database::fetchAll(
            "SELECT sm.*, p.product_name, p.product_code, u.name AS done_by
             FROM stock_movements sm
             JOIN products p ON p.id = sm.product_id
             LEFT JOIN users u ON u.id = sm.created_by
             WHERE sm.movement_type IN ('adjustment_add','adjustment_sub','adjustment_set')
             ORDER BY sm.created_at DESC
             LIMIT 15"
        );

        $pageTitle  = 'Stock Adjustment';
        $breadcrumb = [
            ['label' => 'Stock', 'url' => APP_URL . '/stock'],
            ['label' => 'Adjust'],
        ];
        ob_start();
        require APP_PATH . '/views/stock/adjust.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function adjust(string $id = ''): void
    {
        Auth::requirePermission('stock.adjust');
        Auth::requireCsrf();

        $productId = (int)   $this->post('product_id');
        $type      =         $this->post('type', 'adjustment_add');
        $qty       = (float) $this->post('quantity', 0);
        $note      = trim(   $this->post('note', ''));

        if (!$productId || $qty < 0) {
            $this->flash('error', 'Product and quantity required.');
            $this->back();
            return;
        }

        $current = (float) Database::fetchColumn(
            'SELECT current_stock FROM products WHERE id = ?', [$productId]
        );

        if ($type === 'adjustment_set') {
            $newStock = max(0, $qty);
            $qtyIn    = $qty > $current ? $qty - $current : 0;
            $qtyOut   = $qty < $current ? $current - $qty : 0;
        } elseif ($type === 'adjustment_add') {
            if ($qty <= 0) { $this->flash('error', 'Quantity must be greater than zero.'); $this->back(); return; }
            $newStock = $current + $qty;
            $qtyIn    = $qty;
            $qtyOut   = 0;
        } else { // adjustment_sub
            if ($qty <= 0) { $this->flash('error', 'Quantity must be greater than zero.'); $this->back(); return; }
            $newStock = max(0, $current - $qty);
            $qtyIn    = 0;
            $qtyOut   = $qty;
        }

        $newStock = round($newStock, 4);

        Database::beginTransaction();
        try {
            Database::execute(
                'UPDATE products SET current_stock = ? WHERE id = ?',
                [$newStock, $productId]
            );
            Database::execute(
                'INSERT INTO stock_movements
                 (product_id, movement_type, quantity_in, quantity_out, stock_after, note, created_by)
                 VALUES (?, ?, ?, ?, ?, ?, ?)',
                [$productId, $type, $qtyIn, $qtyOut, $newStock, $note ?: null, Auth::id()]
            );
            Database::commit();
        } catch (Exception $e) {
            Database::rollback();
            $this->flash('error', 'Adjustment failed: ' . $e->getMessage());
            $this->back();
            return;
        }

        $product = Database::fetchOne('SELECT product_name FROM products WHERE id = ?', [$productId]);
        Auth::logAudit(Auth::id(), 'stock_adjust', 'stock',
            "Adjusted {$product['product_name']}: {$type} {$qty} → new stock: {$newStock}");

        redirectWith(
            APP_URL . '/stock/adjust',
            'success',
            "Stock updated: {$product['product_name']} → {$newStock}"
        );
    }
    public function lowStock(): void
    {
        Auth::requirePermission('stock.view');
        $products = Database::fetchAll(
            "SELECT p.*, c.category_name, u.short_name AS unit
             FROM products p
             LEFT JOIN categories c ON c.id = p.category_id
             LEFT JOIN units      u ON u.id = p.unit_id
             WHERE p.status = 'active'
               AND p.current_stock > 0
               AND p.reorder_level > 0
               AND p.current_stock <= p.reorder_level
             ORDER BY p.current_stock ASC"
        );
        $pageTitle = 'Low Stock Report';
        $breadcrumb = [['label' => 'Stock', 'url' => APP_URL . '/stock'], ['label' => 'Low Stock']];
        ob_start();
        require APP_PATH . '/views/stock/low.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function expiry(): void
    {
        Auth::requirePermission('stock.view');
        $days     = (int) $this->get('days', (int) setting('expiry_alert_days', 30));
        $products = Database::fetchAll(
            "SELECT pb.*, p.product_name, p.product_code,
                    DATEDIFF(pb.expiry_date, CURDATE()) AS days_to_expiry
             FROM product_batches pb
             JOIN products p ON p.id = pb.product_id
             WHERE pb.expiry_date IS NOT NULL
               AND pb.quantity > 0
               AND DATEDIFF(pb.expiry_date, CURDATE()) <= ?
             ORDER BY pb.expiry_date ASC",
            [$days]
        );
        $pageTitle  = 'Expiry Alert';
        $breadcrumb = [['label' => 'Stock', 'url' => APP_URL . '/stock'], ['label' => 'Expiry']];
        ob_start();
        require APP_PATH . '/views/stock/expiry.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }
}