<?php
declare(strict_types=1);

namespace App\Repositories;

use App\Core\Repository;

final class ItemRepository extends Repository {
    public function allWithFilters(array $filters=[]): array {
        $where = ['i.is_active=1'];
        $params = [];
        if (!empty($filters['category_id'])) { $where[] = 'i.category_id=?'; $params[] = (int)$filters['category_id']; }
        if (!empty($filters['status'])) { $where[] = 'i.item_status=?'; $params[] = $filters['status']; }
        if (!empty($filters['q'])) { $where[] = '(i.item_code LIKE ? OR i.item_name LIKE ?)'; $params[]='%'.$filters['q'].'%'; $params[]='%'.$filters['q'].'%'; }

        $rows = $this->all("SELECT i.*, c.name_ar category_name, l.name location_name,
            (SELECT image_path FROM item_images im WHERE im.item_id=i.id ORDER BY is_primary DESC, sort_order ASC, id ASC LIMIT 1) primary_image
            FROM items i
            JOIN categories c ON c.id=i.category_id
            LEFT JOIN item_locations l ON l.id=i.location_id
            WHERE ".implode(' AND ', $where)." ORDER BY c.sort_order ASC, i.item_code ASC", $params);

        foreach ($rows as &$row) {
            $images = $this->all("SELECT image_path FROM item_images WHERE item_id=? ORDER BY is_primary DESC, sort_order ASC, id ASC", [(int)$row['id']]);
            $row['images_json'] = json_encode(array_values(array_map(fn($x) => (string)$x['image_path'], $images)), JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
        }
        unset($row);

        return $rows;
    }


    public function filteredItems(array $filters=[]): array {
        return $this->allWithFilters($filters);
    }

    public function activeLocations(): array {
        return $this->all("SELECT id, name FROM item_locations WHERE is_active=1 ORDER BY name ASC");
    }

    public function availableForPeriod(?int $categoryId, string $deliveryDate, string $returnDate): array {
        $where = ['i.is_active=1', "i.item_status NOT IN ('maintenance','missing','sold')"];
        $params = [];
        if ($categoryId) { $where[] = 'i.category_id=?'; $params[] = $categoryId; }
        $params[] = $returnDate; 
        $params[] = $deliveryDate;

        return $this->all("SELECT i.*, c.name_ar category_name FROM items i
            JOIN categories c ON c.id=i.category_id
            WHERE ".implode(' AND ', $where)."
            AND i.id NOT IN (
                SELECT ii.item_id FROM invoice_items ii
                JOIN invoices inv ON inv.id=ii.invoice_id
                WHERE ii.line_type='rental'
                  AND inv.invoice_status NOT IN ('cancelled','closed')
                  AND (ii.return_status='pending' OR ii.return_status IS NULL)
                  AND inv.delivery_date <= ?
                  AND inv.return_date >= ?
            )
            ORDER BY c.sort_order ASC, i.item_code ASC", $params);
    }

    public function find(int $id): ?array {
        $row = $this->one("SELECT i.*, c.name_ar category_name, l.name location_name,
            (SELECT image_path FROM item_images im WHERE im.item_id=i.id ORDER BY is_primary DESC, sort_order ASC, id ASC LIMIT 1) primary_image
            FROM items i
            JOIN categories c ON c.id=i.category_id
            LEFT JOIN item_locations l ON l.id=i.location_id
            WHERE i.id=?", [$id]);

        if ($row) {
            $images = $this->all("SELECT image_path FROM item_images WHERE item_id=? ORDER BY is_primary DESC, sort_order ASC, id ASC", [$id]);
            $row['images_json'] = json_encode(array_values(array_map(fn($x) => (string)$x['image_path'], $images)), JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
        }

        return $row;
    }

    public function findWithSchedule(int $id): ?array {
        $item = $this->find($id);
        if (!$item) return null;
        $item['future_bookings'] = $this->all("SELECT inv.invoice_no, inv.delivery_date, inv.return_date, c.full_name customer_name
            FROM invoice_items ii
            JOIN invoices inv ON inv.id = ii.invoice_id
            JOIN customers c ON c.id = inv.customer_id
            WHERE ii.item_id = ?
              AND ii.line_type='rental'
              AND inv.return_date >= CURDATE()
              AND inv.invoice_status NOT IN ('cancelled')
            ORDER BY inv.delivery_date ASC, inv.id ASC", [$id]);
        return $item;
    }

    public function codeExists(string $itemCode, ?int $exceptId = null): bool {
        $sql = "SELECT id FROM items WHERE item_code = ?";
        $params = [$itemCode];
        if ($exceptId) {
            $sql .= " AND id <> ?";
            $params[] = $exceptId;
        }
        return (bool)$this->one($sql, $params);
    }

    public function create(array $data): int {
        return $this->insert("INSERT INTO items
            (category_id, location_id, item_code, item_name, default_price, item_type, item_status, barcode_value, is_active, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())", [
                (int)$data['category_id'],
                !empty($data['location_id']) ? (int)$data['location_id'] : null,
                $data['item_code'],
                $data['item_name'] !== '' ? $data['item_name'] : null,
                number_format((float)$data['default_price'], 3, '.', ''),
                $data['item_type'],
                $data['item_status'],
                $data['barcode_value'] !== '' ? $data['barcode_value'] : null,
                !empty($data['is_active']) ? 1 : 0,
            ]);
    }

    public function updateItem(int $id, array $data): int {
        return $this->exec("UPDATE items SET
            category_id=?, location_id=?, item_code=?, item_name=?, default_price=?, item_type=?, item_status=?, barcode_value=?, is_active=?, updated_at=NOW()
            WHERE id=?", [
                (int)$data['category_id'],
                !empty($data['location_id']) ? (int)$data['location_id'] : null,
                $data['item_code'],
                $data['item_name'] !== '' ? $data['item_name'] : null,
                number_format((float)$data['default_price'], 3, '.', ''),
                $data['item_type'],
                $data['item_status'],
                $data['barcode_value'] !== '' ? $data['barcode_value'] : null,
                !empty($data['is_active']) ? 1 : 0,
                $id
            ]);
    }

    public function softDelete(int $id): int {
        return $this->exec("UPDATE items SET is_active=0, updated_at=NOW() WHERE id=?", [$id]);
    }

    /**
     * Sync item images without touching the existing controller flow.
     * Works with the current DB schema where item_images has:
     * id, item_id, image_path, is_primary, sort_order, created_at
     */
    public function syncImages(int $itemId, array $existingOrPayload = [], array $uploadedPaths = [], string $primaryChoice = ''): void {
        $itemId = (int)$itemId;
        if ($itemId <= 0) {
            return;
        }

        // Supports both calling styles:
        // 1) syncImages($itemId, ['existing_images'=>[], 'uploaded_paths'=>[], 'primary_image_choice'=>'...'])
        // 2) syncImages($itemId, $keepExisting, $uploadedImages, $primaryChoice)
        if (array_key_exists('existing_images', $existingOrPayload) || array_key_exists('uploaded_paths', $existingOrPayload) || array_key_exists('primary_image_choice', $existingOrPayload) || array_key_exists('remove_existing_images', $existingOrPayload)) {
            $existingImages = array_values(array_filter(array_map('strval', (array)($existingOrPayload['existing_images'] ?? []))));
            $removeExisting = array_values(array_filter(array_map('strval', (array)($existingOrPayload['remove_existing_images'] ?? []))));
            $uploadedPaths = array_values(array_filter(array_map('strval', (array)($existingOrPayload['uploaded_paths'] ?? []))));
            $primaryChoice = (string)($existingOrPayload['primary_image_choice'] ?? ($existingOrPayload['primary_image_selector'] ?? $primaryChoice));
        } else {
            $existingImages = array_values(array_filter(array_map('strval', (array)$existingOrPayload)));
            $removeExisting = [];
            $uploadedPaths = array_values(array_filter(array_map('strval', (array)$uploadedPaths)));
            $primaryChoice = (string)$primaryChoice;
        }

        $finalPaths = array_values(array_unique(array_merge($existingImages, $uploadedPaths)));
        if (count($finalPaths) > 5) {
            $finalPaths = array_slice($finalPaths, 0, 5);
        }

        $currentRows = $this->all("SELECT id, image_path FROM item_images WHERE item_id=? ORDER BY sort_order ASC, id ASC", [$itemId]);
        $currentMap = [];
        foreach ($currentRows as $row) {
            $currentMap[(string)$row['image_path']] = (int)$row['id'];
        }

        foreach ($currentRows as $row) {
            if (!in_array((string)$row['image_path'], $finalPaths, true)) {
                $this->exec("DELETE FROM item_images WHERE id=?", [(int)$row['id']]);
            }
        }

        if ($primaryChoice === '' && !empty($finalPaths)) {
            $primaryChoice = (string)$finalPaths[0];
        }
        if (str_starts_with($primaryChoice, 'new:')) {
            $idx = (int)substr($primaryChoice, 4);
            if (isset($uploadedPaths[$idx])) {
                $primaryChoice = (string)$uploadedPaths[$idx];
            } elseif (isset($finalPaths[$idx])) {
                $primaryChoice = (string)$finalPaths[$idx];
            }
        }

        foreach ($finalPaths as $i => $path) {
            $isPrimary = ((string)$path === (string)$primaryChoice) ? 1 : 0;
            $sortOrder = $i + 1;

            if (isset($currentMap[$path])) {
                $this->exec("UPDATE item_images SET is_primary=?, sort_order=? WHERE id=?", [$isPrimary, $sortOrder, (int)$currentMap[$path]]);
            } else {
                $this->insert("INSERT INTO item_images (item_id, image_path, is_primary, sort_order, created_at) VALUES (?, ?, ?, ?, NOW())", [$itemId, $path, $isPrimary, $sortOrder]);
            }
        }

        if (!empty($finalPaths)) {
            $hasPrimary = $this->one("SELECT id FROM item_images WHERE item_id=? AND is_primary=1 LIMIT 1", [$itemId]);
            if (!$hasPrimary) {
                $first = $this->one("SELECT id FROM item_images WHERE item_id=? ORDER BY sort_order ASC, id ASC LIMIT 1", [$itemId]);
                if ($first) {
                    $this->exec("UPDATE item_images SET is_primary=1 WHERE id=?", [(int)$first['id']]);
                }
            }
        }
    }
}
