<?php
declare(strict_types=1);

namespace App\Modules\Inventory\Controllers;

use App\Core\Controller;
use App\Core\Request;
use App\Core\Response;
use App\Core\App;
use App\Modules\Inventory\Models\Warehouse;
use App\Modules\Inventory\Models\ItemCategory;
use App\Modules\Inventory\Models\ItemWarehouseStock;
use App\Modules\Inventory\Models\ItemBatch;

class InventoryReportController extends Controller
{
    public function stockBalance(Request $request): Response
    {
        $warehouseId = (int) $request->get('warehouse_id', 0);
        $categoryId  = (int) $request->get('category_id', 0);
        $db = App::getInstance()->db();

        $where = 'i.`deleted_at` IS NULL';
        $params = [];

        if ($warehouseId > 0) {
            $where .= ' AND iws.`warehouse_id` = ?';
            $params[] = $warehouseId;
        }
        if ($categoryId > 0) {
            $where .= ' AND i.`category_id` = ?';
            $params[] = $categoryId;
        }

        $rows = $db->select(
            "SELECT i.`id`, i.`sku`, i.`name_ar`, i.`unit_of_measure`, i.`cost_price`,
                    w.`name_ar` as warehouse_name, iws.`quantity`, iws.`min_level`, iws.`max_level`,
                    (i.`cost_price` * iws.`quantity`) as stock_value
             FROM `item_warehouse_stock` iws
             JOIN `inventory_items` i ON i.`id` = iws.`item_id`
             JOIN `warehouses` w ON w.`id` = iws.`warehouse_id`
             WHERE {$where}
             ORDER BY i.`name_ar` ASC",
            $params
        );

        $totalValue = array_reduce($rows, fn($carry, $r) => bcadd($carry, (string) ($r['stock_value'] ?? 0), 2), '0.00');

        return $this->view('Inventory.Views.reports.stock_balance', [
            'rows'        => $rows,
            'totalValue'  => $totalValue,
            'warehouses'  => Warehouse::allActive(),
            'categories'  => ItemCategory::allActive(),
            'warehouseId' => $warehouseId,
            'categoryId'  => $categoryId,
        ]);
    }

    public function movements(Request $request): Response
    {
        $db = App::getInstance()->db();
        $warehouseId = (int) $request->get('warehouse_id', 0);
        $dateFrom    = trim((string) $request->get('date_from', date('Y-m-01')));
        $dateTo      = trim((string) $request->get('date_to', date('Y-m-d')));

        $where = 'm.`movement_date` BETWEEN ? AND ?';
        $params = [$dateFrom, $dateTo];

        if ($warehouseId > 0) {
            $where .= ' AND m.`warehouse_id` = ?';
            $params[] = $warehouseId;
        }

        $summary = $db->select(
            "SELECT m.`movement_type`, m.`direction`,
                    COUNT(*) as move_count,
                    SUM(m.`quantity`) as total_qty,
                    SUM(m.`total_cost`) as total_cost
             FROM `stock_movements` m
             WHERE {$where}
             GROUP BY m.`movement_type`, m.`direction`
             ORDER BY m.`direction`, m.`movement_type`",
            $params
        );

        return $this->view('Inventory.Views.reports.movements', [
            'summary'     => $summary,
            'warehouses'  => Warehouse::allActive(),
            'warehouseId' => $warehouseId,
            'dateFrom'    => $dateFrom,
            'dateTo'      => $dateTo,
        ]);
    }

    public function expiryAlert(Request $request): Response
    {
        $days = max(1, (int) $request->get('days', 30));
        $nearExpiry = ItemBatch::getNearExpiry($days);
        $expired = ItemBatch::getExpired();

        return $this->view('Inventory.Views.reports.expiry_alert', [
            'nearExpiry' => $nearExpiry,
            'expired'    => $expired,
            'days'       => $days,
        ]);
    }

    public function lowStock(Request $request): Response
    {
        $rows = ItemWarehouseStock::getLowStockItems();

        return $this->view('Inventory.Views.reports.low_stock', [
            'rows' => $rows,
        ]);
    }

    public function depreciationSchedule(Request $request): Response
    {
        $db = App::getInstance()->db();
        $month = trim((string) $request->get('month', date('Y-m')));

        $entries = $db->select(
            "SELECT de.*, ar.`asset_tag`, ar.`purchase_cost`, ar.`salvage_value`, ar.`useful_life_months`,
                    i.`name_ar` as item_name, w.`name_ar` as warehouse_name
             FROM `depreciation_entries` de
             JOIN `asset_register` ar ON ar.`id` = de.`asset_id`
             JOIN `inventory_items` i ON i.`id` = ar.`item_id`
             JOIN `warehouses` w ON w.`id` = ar.`warehouse_id`
             WHERE de.`period_month` = ?
             ORDER BY ar.`asset_tag` ASC",
            [$month]
        );

        $totalDep = array_reduce($entries, fn($c, $r) => bcadd($c, (string) ($r['depreciation_amount'] ?? 0), 2), '0.00');

        return $this->view('Inventory.Views.reports.depreciation', [
            'entries'  => $entries,
            'totalDep' => $totalDep,
            'month'    => $month,
        ]);
    }

    public function auditVariance(Request $request): Response
    {
        $db = App::getInstance()->db();
        $auditId = (int) $request->get('audit_id', 0);

        $audits = $db->select(
            "SELECT a.`id`, a.`audit_number`, a.`audit_date`, w.`name_ar` as warehouse_name
             FROM `stock_audits` a
             JOIN `warehouses` w ON w.`id` = a.`warehouse_id`
             WHERE a.`status` = 'approved'
             ORDER BY a.`audit_date` DESC LIMIT 50"
        );

        $variances = [];
        if ($auditId > 0) {
            $variances = $db->select(
                "SELECT sai.*, i.`name_ar` as item_name, i.`sku`, i.`cost_price`
                 FROM `stock_audit_items` sai
                 JOIN `inventory_items` i ON i.`id` = sai.`item_id`
                 WHERE sai.`audit_id` = ? AND sai.`variance` != 0
                 ORDER BY ABS(sai.`variance`) DESC",
                [$auditId]
            );
        }

        return $this->view('Inventory.Views.reports.audit_variance', [
            'audits'    => $audits,
            'variances' => $variances,
            'auditId'   => $auditId,
        ]);
    }

    public function supplierHistory(Request $request): Response
    {
        $db = App::getInstance()->db();
        $supplierId = (int) $request->get('supplier_id', 0);

        $suppliers = $db->select(
            "SELECT `id`, `name_ar`, `code` FROM `suppliers` WHERE `deleted_at` IS NULL ORDER BY `name_ar` ASC"
        );

        $orders = [];
        if ($supplierId > 0) {
            $orders = $db->select(
                "SELECT po.*, w.`name_ar` as warehouse_name
                 FROM `purchase_orders` po
                 JOIN `warehouses` w ON w.`id` = po.`warehouse_id`
                 WHERE po.`supplier_id` = ?
                 ORDER BY po.`created_at` DESC LIMIT 50",
                [$supplierId]
            );
        }

        return $this->view('Inventory.Views.reports.supplier_history', [
            'suppliers'  => $suppliers,
            'orders'     => $orders,
            'supplierId' => $supplierId,
        ]);
    }
}
