<?php

namespace WMS\Services;

use WMS\Core\Database;

class RecallService
{
    public function createRecall(array $data, int $userId): array
    {
        $db = Database::getConnection();
        $recallNumber = Database::nextDocNumber('RECALL');

        $stmt = $db->prepare(
            "INSERT INTO recalls (recall_number, batch_number, item_code, item_name, reason, status, initiated_by)
             VALUES (?, ?, ?, ?, ?, 'INITIATED', ?)"
        );
        $stmt->execute([
            $recallNumber,
            $data['batch_number'],
            $data['item_code'] ?? '',
            $data['item_name'] ?? '',
            $data['reason'],
            $userId,
        ]);
        $id = (int)$db->lastInsertId();

        // Auto-resolve item info if item_code provided
        if (!empty($data['item_code'])) {
            $item = $db->prepare("SELECT item_name FROM items WHERE item_code = ? LIMIT 1");
            $item->execute([$data['item_code']]);
            $row = $item->fetch();
            if ($row) {
                $db->prepare("UPDATE recalls SET item_name = ? WHERE id = ?")->execute([$row['item_name'], $id]);
            }
        }

        return $this->getRecall($id);
    }

    public function getRecall(int $id): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare(
            "SELECT r.*, u.full_name as initiated_by_name
             FROM recalls r
             LEFT JOIN users u ON r.initiated_by = u.id
             WHERE r.id = ?"
        );
        $stmt->execute([$id]);
        $recall = $stmt->fetch(\PDO::FETCH_ASSOC);
        if (!$recall) throw new \RuntimeException('Recall no encontrado');

        // Get results
        $results = $db->prepare("SELECT * FROM recall_results WHERE recall_id = ? ORDER BY result_type, id");
        $results->execute([$id]);
        $recall['results'] = $results->fetchAll(\PDO::FETCH_ASSOC);

        // Group by type
        $recall['stock_found'] = array_filter($recall['results'], fn($r) => $r['result_type'] === 'STOCK');
        $recall['movements'] = array_filter($recall['results'], fn($r) => $r['result_type'] === 'MOVEMENT');
        $recall['dispatches'] = array_filter($recall['results'], fn($r) => $r['result_type'] === 'DISPATCH');
        $recall['customers'] = array_filter($recall['results'], fn($r) => $r['result_type'] === 'CUSTOMER');

        return $recall;
    }

    public function listRecalls(?string $status = null): array
    {
        $db = Database::getConnection();
        $sql = "SELECT r.*, u.full_name as initiated_by_name,
                (SELECT COUNT(*) FROM recall_results WHERE recall_id = r.id) as result_count
                FROM recalls r
                LEFT JOIN users u ON r.initiated_by = u.id";
        $params = [];
        if ($status) {
            $sql .= " WHERE r.status = ?";
            $params[] = $status;
        }
        $sql .= " ORDER BY r.created_at DESC";
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Execute recall trace - finds all stock, movements, dispatches, customers for the batch
     */
    public function executeRecall(int $recallId, int $userId): array
    {
        $db = Database::getConnection();
        $recall = $this->getRecall($recallId);
        $batchNumber = $recall['batch_number'];
        $itemCode = $recall['item_code'];

        // Clear previous results
        $db->prepare("DELETE FROM recall_results WHERE recall_id = ?")->execute([$recallId]);

        $totalStock = 0;
        $customers = [];

        // 1. Find ALL stock positions for this batch
        $sql = "SELECT s.quantity, s.stock_status, s.reserved_qty,
                       w.code as warehouse_code, b2.code as bin_code,
                       i.item_code, i.item_name, bt.batch_number, bt.expiry_date
                FROM stock s
                LEFT JOIN warehouses w ON s.warehouse_id = w.id
                LEFT JOIN bins b2 ON s.bin_id = b2.id
                LEFT JOIN items i ON s.item_id = i.id
                LEFT JOIN batches bt ON s.batch_id = bt.id
                WHERE bt.batch_number = ?";
        $params = [$batchNumber];
        if ($itemCode) {
            $sql .= " AND i.item_code = ?";
            $params[] = $itemCode;
        }
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $stocks = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        foreach ($stocks as $s) {
            $totalStock += (float)$s['quantity'];
            $db->prepare(
                "INSERT INTO recall_results (recall_id, result_type, warehouse_code, bin_code, quantity, stock_status)
                 VALUES (?, 'STOCK', ?, ?, ?, ?)"
            )->execute([$recallId, $s['warehouse_code'], $s['bin_code'], $s['quantity'], $s['stock_status']]);
        }

        // 2. Find ALL movements for this batch
        $sql2 = "SELECT sm.movement_type, sm.quantity, sm.created_at,
                        fb.code as from_bin, tb.code as to_bin,
                        i.item_code, bt.batch_number, u.full_name as operator
                 FROM stock_movements sm
                 LEFT JOIN bins fb ON sm.from_bin_id = fb.id
                 LEFT JOIN bins tb ON sm.to_bin_id = tb.id
                 LEFT JOIN items i ON sm.item_id = i.id
                 LEFT JOIN batches bt ON sm.batch_id = bt.id
                 LEFT JOIN users u ON sm.created_by = u.id
                 WHERE bt.batch_number = ?
                 ORDER BY sm.created_at DESC LIMIT 100";
        $stmt2 = $db->prepare($sql2);
        $stmt2->execute([$batchNumber]);
        $movements = $stmt2->fetchAll(\PDO::FETCH_ASSOC);

        foreach ($movements as $m) {
            $db->prepare(
                "INSERT INTO recall_results (recall_id, result_type, movement_type, movement_date, quantity, details)
                 VALUES (?, 'MOVEMENT', ?, ?, ?, ?)"
            )->execute([$recallId, $m['movement_type'], $m['created_at'], $m['quantity'],
                json_encode(['from' => $m['from_bin'], 'to' => $m['to_bin'], 'operator' => $m['operator']])]);
        }

        // 3. Find dispatches containing this batch
        $sql3 = "SELECT d.dispatch_number, d.truck_plate, d.departed_at, d.status,
                        pl.customer_name, pl.customer_code
                 FROM dispatches d
                 JOIN dispatch_lines dl ON d.id = dl.dispatch_id
                 JOIN packing_orders po ON dl.packing_order_id = po.id
                 JOIN packing_lines pkl ON po.id = pkl.packing_order_id
                 JOIN batches bt ON pkl.batch_id = bt.id
                 WHERE bt.batch_number = ?
                 GROUP BY d.id";
        try {
            $stmt3 = $db->prepare($sql3);
            $stmt3->execute([$batchNumber]);
            $dispatches = $stmt3->fetchAll(\PDO::FETCH_ASSOC);

            foreach ($dispatches as $disp) {
                $db->prepare(
                    "INSERT INTO recall_results (recall_id, result_type, dispatch_number, dispatch_date, customer_code, customer_name)
                     VALUES (?, 'DISPATCH', ?, ?, ?, ?)"
                )->execute([$recallId, $disp['dispatch_number'], $disp['departed_at'], $disp['customer_code'], $disp['customer_name']]);

                if ($disp['customer_code'] && !in_array($disp['customer_code'], $customers)) {
                    $customers[] = $disp['customer_code'];
                    $db->prepare(
                        "INSERT INTO recall_results (recall_id, result_type, customer_code, customer_name)
                         VALUES (?, 'CUSTOMER', ?, ?)"
                    )->execute([$recallId, $disp['customer_code'], $disp['customer_name']]);
                }
            }
        } catch (\Exception $e) {
            // Tables may not have data yet
        }

        // 4. Also check pick_lists for customers
        $sql4 = "SELECT DISTINCT pl.customer_code, pl.customer_name
                 FROM pick_lists pl
                 JOIN pick_list_lines pll ON pl.id = pll.pick_list_id
                 JOIN batches bt ON pll.batch_id = bt.id
                 WHERE bt.batch_number = ? AND pl.customer_code IS NOT NULL";
        try {
            $stmt4 = $db->prepare($sql4);
            $stmt4->execute([$batchNumber]);
            $pickCustomers = $stmt4->fetchAll(\PDO::FETCH_ASSOC);
            foreach ($pickCustomers as $pc) {
                if (!in_array($pc['customer_code'], $customers)) {
                    $customers[] = $pc['customer_code'];
                    $db->prepare(
                        "INSERT INTO recall_results (recall_id, result_type, customer_code, customer_name)
                         VALUES (?, 'CUSTOMER', ?, ?)"
                    )->execute([$recallId, $pc['customer_code'], $pc['customer_name']]);
                }
            }
        } catch (\Exception $e) {}

        // Update recall totals
        $db->prepare(
            "UPDATE recalls SET status = 'IN_PROGRESS', total_stock_found = ?, total_customers_affected = ? WHERE id = ?"
        )->execute([$totalStock, count($customers), $recallId]);

        return $this->getRecall($recallId);
    }

    /**
     * Complete recall - block all remaining stock for this batch
     */
    public function completeRecall(int $recallId, int $userId): array
    {
        $db = Database::getConnection();
        $recall = $this->getRecall($recallId);
        $batchNumber = $recall['batch_number'];

        // Block ALL stock for this batch
        $stmt = $db->prepare(
            "UPDATE stock s
             JOIN batches bt ON s.batch_id = bt.id
             SET s.stock_status = 'BLOCKED'
             WHERE bt.batch_number = ? AND s.stock_status != 'BLOCKED'"
        );
        $stmt->execute([$batchNumber]);
        $blocked = $stmt->rowCount();

        // Also update batch status
        $db->prepare("UPDATE batches SET status = 'BLOCKED' WHERE batch_number = ?")->execute([$batchNumber]);

        // Log stock block movements
        $stocks = $db->prepare(
            "SELECT s.id, s.item_id, s.batch_id, s.warehouse_id, s.bin_id, s.quantity
             FROM stock s JOIN batches bt ON s.batch_id = bt.id
             WHERE bt.batch_number = ?"
        );
        $stocks->execute([$batchNumber]);
        foreach ($stocks->fetchAll(\PDO::FETCH_ASSOC) as $s) {
            $db->prepare(
                "INSERT INTO stock_movements (item_id, batch_id, warehouse_id, from_bin_id, quantity, movement_type, from_status, to_status, reference_type, reference_id, created_by, created_at)
                 VALUES (?, ?, ?, ?, ?, 'BLOCK', 'AVAILABLE', 'BLOCKED', 'RECALL', ?, ?, NOW())"
            )->execute([$s['item_id'], $s['batch_id'], $s['warehouse_id'], $s['bin_id'], $s['quantity'], $recallId, $userId]);
        }

        // Mark recall complete
        $db->prepare("UPDATE recalls SET status = 'COMPLETED', completed_at = NOW() WHERE id = ?")->execute([$recallId]);

        return $this->getRecall($recallId);
    }
}