<?php
declare(strict_types=1);

namespace App\Models;

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

class ProductModel extends Model
{
    protected string $table = 'products';
    private AuditLogModel $auditLogger;

    public function __construct()
    {
        parent::__construct();
        $this->auditLogger = new AuditLogModel();
    }

    public function getStockMovements(int $productId, int $tenantId, array $filters = []): array
    {
        $sql = "SELECT sm.*, w.name as warehouse_name
                FROM stock_movements sm
                LEFT JOIN warehouses w ON sm.warehouse_id = w.id
                WHERE sm.product_id = :product_id AND sm.tenant_id = :tenant_id
                ORDER BY sm.created_at DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':product_id' => $productId, ':tenant_id' => $tenantId]);
        return $stmt->fetchAll();
    }

    public function exportToCSV(int $tenantId, array $filters): void
    {
        $products = $this->getAll($tenantId, $filters, 1, 100000);
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="products.csv"');
        $output = fopen('php://output', 'w');
        fputcsv($output, ['Name', 'SKU', 'Category', 'Unit', 'Sale Price', 'Stock', 'Status']);
        foreach ($products as $p) {
            fputcsv($output, [$p['name'], $p['sku'], $p['category_name'], $p['unit_abbr'], $p['sale_price'], $p['current_stock'], $p['is_active'] ? 'active' : 'inactive']);
        }
        fclose($output);
        exit;
    }

    public function importFromCSV(string $filePath, int $tenantId, int $userId): array
    {
        $handle = fopen($filePath, "r");
        $headers = fgetcsv($handle);
        $imported = 0;
        $failed = 0;
        $errors = [];

        while (($data = fgetcsv($handle)) !== FALSE) {
            try {
                $row = array_combine(['name', 'sku', 'sale_price', 'tax_rate', 'opening_stock'], array_slice(array_pad($data, 5, ''), 0, 5));
                $row['tenant_id'] = $tenantId;
                $row['created_by'] = $userId;
                $this->createProduct($row, $userId);
                $imported++;
            } catch (\Exception $e) {
                $failed++;
                $errors[] = $e->getMessage();
            }
        }
        fclose($handle);
        return ['imported' => $imported, 'failed' => $failed, 'errors' => $errors];
    }

    public function softDelete(int $id, int $tenantId, int $userId): bool
    {
        $old = $this->getById($id, $tenantId);
        if (!$old)
            return false;

        $stmt = $this->db->prepare("UPDATE products SET is_active = 0 WHERE id = :id AND tenant_id = :tenant_id");
        $result = $stmt->execute([':id' => $id, ':tenant_id' => $tenantId]);

        if ($result) {
            $this->auditLogger->log($tenantId, $userId, 'delete', 'products', $id, $old, ['is_active' => 0]);
        }
        return $result;
    }

    public function getAll(int $tenantId, array $filters = [], int $page = 1, int $perPage = 20): array
    {
        $offset = ($page - 1) * $perPage;
        $sql = "SELECT p.*, c.name as category_name, u.abbreviation as unit_abbr,
                (SELECT SUM(quantity) FROM stock_entries se WHERE se.product_id = p.id) as current_stock,
                (SELECT GROUP_CONCAT(CONCAT(w.name, ':', se.quantity) SEPARATOR '|') 
                 FROM stock_entries se 
                 JOIN warehouses w ON se.warehouse_id = w.id 
                 WHERE se.product_id = p.id AND se.quantity > 0) as warehouse_breakdown
                FROM products p
                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 :search1 OR p.sku LIKE :search2 OR p.barcode LIKE :search3)";
            $params[':search1'] = '%' . $filters['search'] . '%';
            $params[':search2'] = '%' . $filters['search'] . '%';
            $params[':search3'] = '%' . $filters['search'] . '%';
        }

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

        if (isset($filters['low_stock']) && $filters['low_stock'] == 1) {
            $sql .= " AND (SELECT SUM(quantity) FROM stock_entries se WHERE se.product_id = p.id) <= p.reorder_level";
        }

        $sort = $filters['sort'] ?? 'p.name';
        $order = isset($filters['order']) && strtoupper($filters['order']) === 'DESC' ? 'DESC' : 'ASC';

        $sql .= " ORDER BY $sort $order LIMIT $perPage OFFSET $offset";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function getById(int $id, int $tenantId): ?array
    {
        $sql = "SELECT p.*, c.name as category_name, u.abbreviation as unit_abbr
                FROM products p
                LEFT JOIN categories c ON p.category_id = c.id
                LEFT JOIN units u ON p.unit_id = u.id
                WHERE p.id = :id AND p.tenant_id = :tenant_id";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':id' => $id, ':tenant_id' => $tenantId]);
        $product = $stmt->fetch();

        if ($product) {
            $product['stock_by_warehouse'] = $this->getStockPerWarehouse($id, $tenantId);
            $product['total_stock'] = array_sum(array_column($product['stock_by_warehouse'], 'quantity'));
        }

        return $product ?: null;
    }

    public function getStockPerWarehouse(int $productId, int $tenantId): array
    {
        $sql = "SELECT w.id as warehouse_id, w.name as warehouse_name, COALESCE(se.quantity, 0) as quantity
                FROM warehouses w
                LEFT JOIN stock_entries se ON se.warehouse_id = w.id AND se.product_id = :product_id
                WHERE w.tenant_id = :tenant_id AND w.status = 'active'";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':product_id' => $productId, ':tenant_id' => $tenantId]);
        return $stmt->fetchAll();
    }

    public function getDefaultWarehouse(int $tenantId): int
    {
        $stmt = $this->db->prepare("SELECT id FROM warehouses WHERE tenant_id = ? AND is_default = 1 LIMIT 1");
        $stmt->execute([$tenantId]);
        $warehouseId = $stmt->fetchColumn();
        if (!$warehouseId) {
            $stmt = $this->db->prepare("SELECT id FROM warehouses WHERE tenant_id = ? LIMIT 1");
            $stmt->execute([$tenantId]);
            $warehouseId = $stmt->fetchColumn();
        }
        if (!$warehouseId) {
            $this->db->prepare("INSERT INTO warehouses (tenant_id, name, is_default) VALUES (?, 'Default Warehouse', 1)")->execute([$tenantId]);
            $warehouseId = $this->db->lastInsertId();
        }
        return (int) $warehouseId;
    }

    public function createProduct(array $data, int $userId): int
    {
        $tenantId = $data['tenant_id'];

        // SKU Generation
        if (empty($data['sku'])) {
            $data['sku'] = $this->generateSKU($tenantId);
        }

        // Check if SKU exists
        $stmt = $this->db->prepare("SELECT id FROM products WHERE sku = ? AND tenant_id = ?");
        $stmt->execute([$data['sku'], $tenantId]);
        if ($stmt->fetch())
            throw new Exception("SKU already exists for this tenant");

        $productId = $this->create($data);

        // Handle Opening Stock
        if (!empty($data['opening_stock']) && $data['opening_stock'] > 0) {
            $warehouseId = $this->getDefaultWarehouse($tenantId);
            $this->updateStock((int) $productId, $warehouseId, (float) $data['opening_stock'], 'opening', 'initial', (int) $productId, (float) ($data['purchase_price'] ?? 0), $userId);
        }

        $this->auditLogger->log($tenantId, $userId, 'create', 'products', (int) $productId, null, $data);
        return (int) $productId;
    }

    public function generateSKU(int $tenantId): string
    {
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM products WHERE tenant_id = ?");
        $stmt->execute([$tenantId]);
        $count = (int) $stmt->fetchColumn() + 1;
        return "PRD-" . str_pad((string) $count, 5, "0", STR_PAD_LEFT);
    }

    public function updateStock(int $productId, int $warehouseId, float $quantity, string $movementType, string $refType, int $refId, float $costPrice, int $userId): void
    {
        $tenantStmt = $this->db->prepare("SELECT tenant_id FROM products WHERE id = ?");
        $tenantStmt->execute([$productId]);
        $tenantId = (int) $tenantStmt->fetchColumn();

        // 1. Update Stock Entries
        $stmt = $this->db->prepare("SELECT id, quantity, cost_price FROM stock_entries WHERE product_id = ? AND warehouse_id = ?");
        $stmt->execute([$productId, $warehouseId]);
        $entry = $stmt->fetch();

        $oldQty = $entry ? (float) $entry['quantity'] : 0;
        $newQty = $oldQty + $quantity;

        if ($entry) {
            // Weighted Average Costing for incoming stock
            if ($quantity > 0 && $movementType === 'purchase') {
                $oldValue = $oldQty * (float) $entry['cost_price'];
                $newValue = $quantity * $costPrice;
                $updatedCost = ($oldValue + $newValue) / $newQty;

                $stmt = $this->db->prepare("UPDATE stock_entries SET quantity = ?, cost_price = ? WHERE id = ?");
                $stmt->execute([$newQty, $updatedCost, $entry['id']]);
            } else {
                $stmt = $this->db->prepare("UPDATE stock_entries SET quantity = ? WHERE id = ?");
                $stmt->execute([$newQty, $entry['id']]);
            }
        } else {
            $stmt = $this->db->prepare("INSERT INTO stock_entries (tenant_id, product_id, warehouse_id, quantity, cost_price) VALUES (?, ?, ?, ?, ?)");
            $stmt->execute([$tenantId, $productId, $warehouseId, $quantity, $costPrice]);
        }

        // 2. Log Movement
        $stmt = $this->db->prepare("INSERT INTO stock_movements (tenant_id, product_id, warehouse_id, movement_type, reference_type, reference_id, quantity, cost_price, stock_after, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->execute([$tenantId, $productId, $warehouseId, $movementType, $refType, $refId, $quantity, $costPrice, $newQty, $userId]);
    }

    public function searchProducts(int $tenantId, string $query): array
    {
        $sql = "SELECT p.id, p.name, p.sku, p.sale_price, p.tax_rate, p.hsn_code, u.abbreviation as unit,
                (SELECT SUM(quantity) FROM stock_entries se WHERE se.product_id = p.id) as current_stock,
                p.description
                FROM products p
                LEFT JOIN units u ON p.unit_id = u.id
                WHERE p.tenant_id = :tenant_id AND p.is_active = 1
                AND (p.name LIKE :q OR p.sku LIKE :q OR p.barcode LIKE :q)
                LIMIT 10";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':tenant_id' => $tenantId, ':q' => "%$query%"]);
        $products = $stmt->fetchAll();

        foreach ($products as &$p) {
            $p['tax_amount'] = round(($p['sale_price'] * $p['tax_rate']) / 100, 2);
            $p['price_with_tax'] = $p['sale_price'] + $p['tax_amount'];
        }

        return $products;
    }
}
