<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use App\Models\AuditLogModel;
use PDO;

class InventoryModel extends Model
{
    protected string $table = 'stock_movements';

    public function getStockSummary(int $tenantId, array $filters = [], int $page = 1, int $perPage = 10): array
    {
        $offset = ($page - 1) * $perPage;
        $sql = "SELECT p.id as product_id, p.name, p.sku, p.reorder_level, c.name as category, u.abbreviation as unit,
                COALESCE(SUM(se.quantity), 0) as total_quantity,
                COALESCE(AVG(se.cost_price), 0) as avg_cost,
                COALESCE((SUM(se.quantity) * AVG(se.cost_price)), 0) as stock_value
                FROM products p
                LEFT JOIN stock_entries se ON p.id = se.product_id
                LEFT JOIN categories c ON p.category_id = c.id
                LEFT JOIN units u ON p.unit_id = u.id
                WHERE p.tenant_id = :tenant_id";

        $params = [':tenant_id' => $tenantId];

        if (!empty($filters['search'])) {
            $sql .= " AND (p.name LIKE :search OR p.sku LIKE :search OR c.name LIKE :search)";
            $params[':search'] = '%' . $filters['search'] . '%';
        }

        if (!empty($filters['category_id'])) {
            $sql .= " AND p.category_id = :category_id";
            $params[':category_id'] = $filters['category_id'];
        }

        if (!empty($filters['warehouse_id'])) {
            $sql .= " AND se.warehouse_id = :warehouse_id";
            $params[':warehouse_id'] = $filters['warehouse_id'];
        } elseif (!empty($filters['assigned_warehouses'])) {
            $placeholders = [];
            foreach ($filters['assigned_warehouses'] as $i => $id) {
                $params[":wh_$i"] = $id;
                $placeholders[] = ":wh_$i";
            }
            if (!empty($placeholders)) {
                $sql .= " AND se.warehouse_id IN (" . implode(',', $placeholders) . ")";
            }
        }

        $sql .= " GROUP BY p.id";

        // Filtering by status (after group by since it depends on SUM(quantity))
        // Actually it's better to use HAVING for that
        if (isset($filters['low_stock']) && $filters['low_stock'] == 1) {
            $sql .= " HAVING total_quantity <= p.reorder_level";
        }

        $sql .= " ORDER BY p.name ASC LIMIT :limit OFFSET :offset";
        $stmt = $this->db->prepare($sql);
        
        $stmt->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
        foreach ($params as $key => $val) {
            if ($key === ':tenant_id') continue;
            $type = is_int($val) ? PDO::PARAM_INT : PDO::PARAM_STR;
            $stmt->bindValue($key, $val, $type);
        }
        
        $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function countStockSummary(int $tenantId, array $filters = []): int
    {
        $sql = "SELECT COUNT(*) FROM (
                SELECT p.id, p.reorder_level, COALESCE(SUM(se.quantity), 0) as total_quantity
                FROM products p
                LEFT JOIN stock_entries se ON p.id = se.product_id
                LEFT JOIN categories c ON p.category_id = c.id
                WHERE p.tenant_id = :tenant_id";

        $params = [':tenant_id' => $tenantId];

        if (!empty($filters['search'])) {
            $sql .= " AND (p.name LIKE :search OR p.sku LIKE :search OR c.name LIKE :search)";
            $params[':search'] = '%' . $filters['search'] . '%';
        }

        if (!empty($filters['category_id'])) {
            $sql .= " AND p.category_id = :category_id";
            $params[':category_id'] = $filters['category_id'];
        }

        if (!empty($filters['warehouse_id'])) {
            $sql .= " AND se.warehouse_id = :warehouse_id";
            $params[':warehouse_id'] = $filters['warehouse_id'];
        } elseif (!empty($filters['assigned_warehouses'])) {
            $placeholders = [];
            foreach ($filters['assigned_warehouses'] as $i => $id) {
                $params[":wh_$i"] = $id;
                $placeholders[] = ":wh_$i";
            }
            if (!empty($placeholders)) {
                $sql .= " AND se.warehouse_id IN (" . implode(',', $placeholders) . ")";
            }
        }

        $sql .= " GROUP BY p.id";

        if (isset($filters['low_stock']) && $filters['low_stock'] == 1) {
            $sql .= " HAVING total_quantity <= p.reorder_level";
        }

        $sql .= ") as summary";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }

    public function getValuation(int $tenantId, array $filters = []): array
    {
        $db = $this->db;
        $params = [$tenantId];
        $whConstraint = "";
        
        if (!empty($filters['assigned_warehouses'])) {
            $placeholders = implode(',', array_fill(0, count($filters['assigned_warehouses']), '?'));
            $whConstraint = " AND warehouse_id IN ($placeholders)";
            foreach ($filters['assigned_warehouses'] as $id) $params[] = $id;
        }

        // Total Valuation
        $sql = "SELECT SUM(quantity * cost_price) FROM stock_entries WHERE tenant_id = ? $whConstraint";
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $totalValuation = (float)$stmt->fetchColumn();

        // Active SKUs (SKUs that have stock in assigned warehouses or just count all if admin?)
        // Actually, let's keep it simple: only count products that have entries in these warehouses if restricted.
        if ($whConstraint) {
            $sql = "SELECT COUNT(DISTINCT product_id) FROM stock_entries WHERE tenant_id = ? $whConstraint";
        } else {
            $sql = "SELECT COUNT(*) FROM products WHERE tenant_id = ? AND is_active = 1";
        }
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $activeSkus = (int)$stmt->fetchColumn();

        // Out of Stock & Low Stock
        $sql = "SELECT 
                COUNT(CASE WHEN total_stock = 0 THEN 1 END) as out_of_stock,
                COUNT(CASE WHEN total_stock > 0 AND total_stock <= reorder_level THEN 1 END) as low_stock
                FROM (
                    SELECT p.id, p.reorder_level, COALESCE(SUM(se.quantity), 0) as total_stock
                    FROM products p
                    LEFT JOIN stock_entries se ON p.id = se.product_id" . ($whConstraint ? " AND se.warehouse_id IN (" . implode(',', array_fill(0, count($filters['assigned_warehouses']), '?')) . ")" : "") . "
                    WHERE p.tenant_id = ? AND p.is_active = 1
                    GROUP BY p.id
                ) as inventory";
        
        $paramsFinal = [];
        if ($whConstraint) {
            foreach ($filters['assigned_warehouses'] as $id) $paramsFinal[] = $id;
        }
        $paramsFinal[] = $tenantId;

        $stmt = $db->prepare($sql);
        $stmt->execute($paramsFinal);
        $counts = $stmt->fetch();

        return [
            'total_valuation' => $totalValuation,
            'active_skus' => $activeSkus,
            'low_stock' => (int)($counts['low_stock'] ?? 0),
            'out_of_stock' => (int)($counts['out_of_stock'] ?? 0)
        ];
    }

    public function recordAdjustment(int $tenantId, array $data, array $items, int $userId): int
    {
        $db = $this->db;
        $db->beginTransaction();

        try {
            // 1. Create adjustment header
            $stmt = $db->prepare("INSERT INTO stock_adjustments (tenant_id, adjustment_date, reference_number, reason, notes, created_by, status) VALUES (?, ?, ?, ?, ?, ?, 'confirmed')");
            $stmt->execute([$tenantId, $data['date'], $data['reference'] ?? null, $data['reason'], $data['notes'] ?? null, $userId]);
            $adjustmentId = (int) $db->lastInsertId();

            $productModel = new ProductModel();

            // 2. Process items
            foreach ($items as $item) {
                $stmt = $db->prepare("INSERT INTO stock_adjustment_items (adjustment_id, product_id, warehouse_id, quantity_on_hand, quantity_adjusted, cost_price) VALUES (?, ?, ?, ?, ?, ?)");

                // Get current qty on hand
                $qohStmt = $db->prepare("SELECT SUM(quantity) FROM stock_entries WHERE product_id = ? AND warehouse_id = ?");
                $qohStmt->execute([(int) $item['product_id'], (int) $item['warehouse_id']]);
                $qoh = $qohStmt->fetchColumn() ?: 0;

                $stmt->execute([$adjustmentId, $item['product_id'], $item['warehouse_id'], $qoh, $item['quantity'], $item['cost_price']]);

                // Update physical stock
                $movementType = $item['quantity'] > 0 ? 'adjustment_in' : 'adjustment_out';
                $productModel->updateStock((int) $item['product_id'], (int) $item['warehouse_id'], (float) $item['quantity'], $movementType, 'adjustment', $adjustmentId, (float) $item['cost_price'], $userId);
            }

            $db->commit();
            return $adjustmentId;
        } catch (\Exception $e) {
            $db->rollBack();
            throw $e;
        }
    }

    public function transferStock(int $tenantId, int $productId, int $fromWh, int $toWh, float $qty, int $userId, ?string $notes = null): bool
    {
        $db = $this->db;
        $db->beginTransaction();

        try {
            $productModel = new ProductModel();

            // Get cost price from source
            $costStmt = $db->prepare("SELECT cost_price FROM stock_entries WHERE product_id = ? AND warehouse_id = ?");
            $costStmt->execute([$productId, $fromWh]);
            $costPrice = (float) $costStmt->fetchColumn();

            // 1. Remove from source
            $productModel->updateStock($productId, $fromWh, -$qty, 'transfer_out', 'transfer', 0, $costPrice, $userId);

            // 2. Add to destination
            $productModel->updateStock($productId, $toWh, $qty, 'transfer_in', 'transfer', 0, $costPrice, $userId);

            $db->commit();
            return true;
        } catch (\Exception $e) {
            $db->rollBack();
            throw $e;
        }
    }
}
