<?php

namespace MintyPHP\Module\Helpdesk\Repository;

use MintyPHP\DB;
use MintyPHP\Repository\Support\RepoQuery;

class SoftwareProductRepository
{
    /**
     * Insert or update a software product by code.
     *
     * Updates bc_description and synced_at on conflict. Reactivates soft-deleted entries.
     * Never touches the user-managed name field.
     */
    public function upsertByCode(string $code, string $bcDescription): bool
    {
        $result = DB::update(
            'INSERT INTO helpdesk_software_products (code, bc_description, synced_at, active) VALUES (?, ?, NOW(), 1) '
            . 'ON DUPLICATE KEY UPDATE bc_description = VALUES(bc_description), synced_at = VALUES(synced_at), active = 1',
            $code,
            $bcDescription
        );

        return $result !== false;
    }

    public function findByCode(string $code): ?array
    {
        $code = trim($code);
        if ($code === '') {
            return null;
        }

        $row = DB::selectOne(
            'SELECT * FROM helpdesk_software_products WHERE code = ? LIMIT 1',
            $code
        );

        return $this->normalizeRow($row);
    }

    /**
     * @return array{total: int, rows: list<array<string, mixed>>}
     */
    public function listPaged(array $filters): array
    {
        $search = trim((string) ($filters['search'] ?? ''));
        $active = trim((string) ($filters['active'] ?? ''));

        [$limit, $offset] = RepoQuery::sanitizeLimitOffset($filters, 20, 1, 100, 0);
        [$order, $dir] = RepoQuery::sanitizeOrder(
            $filters,
            ['code', 'bc_description', 'name', 'active', 'synced_at'],
            'code',
            'asc'
        );

        $where = [];
        $params = [];

        RepoQuery::addLikeFilter($where, $params, ['code', 'bc_description', 'name'], $search);

        if (in_array($active, ['0', '1'], true)) {
            $where[] = 'active = ?';
            $params[] = $active;
        }

        $whereSql = $where ? (' WHERE ' . implode(' AND ', $where)) : '';
        $total = (int) (DB::selectValue('SELECT COUNT(*) FROM helpdesk_software_products' . $whereSql, ...$params) ?? 0);

        $rows = DB::select(
            'SELECT * FROM helpdesk_software_products'
            . $whereSql
            . sprintf(' ORDER BY `%s` %s LIMIT ? OFFSET ?', $order, $dir),
            ...array_merge($params, [(string) $limit, (string) $offset])
        );

        $normalized = [];
        if (is_array($rows)) {
            foreach ($rows as $row) {
                $item = $this->normalizeRow($row);
                if ($item !== null) {
                    $normalized[] = $item;
                }
            }
        }

        return [
            'total' => $total,
            'rows' => $normalized,
        ];
    }

    public function updateHandoverSchema(string $code, ?string $schemaJson): bool
    {
        if (trim($code) === '') {
            return false;
        }

        $result = DB::update(
            'UPDATE helpdesk_software_products SET handover_protocol_schema = ? WHERE code = ?',
            $schemaJson,
            $code
        );

        return $result !== false;
    }

    public function updateName(string $code, string $name): bool
    {
        if (trim($code) === '') {
            return false;
        }

        $result = DB::update(
            'UPDATE helpdesk_software_products SET name = ? WHERE code = ?',
            $name,
            $code
        );

        return $result !== false;
    }

    /**
     * Soft-delete entries whose code is NOT in the given list of active BC codes.
     *
     * @param  list<string> $activeCodes Codes that still exist in BC
     * @return int Number of deactivated rows
     */
    public function softDeleteNotInCodes(array $activeCodes): int
    {
        if ($activeCodes === []) {
            // Deactivate all if BC returned no codes
            $result = DB::update('UPDATE helpdesk_software_products SET active = 0 WHERE active = 1');

            return is_int($result) ? $result : 0;
        }

        $placeholders = implode(',', array_fill(0, count($activeCodes), '?'));
        $result = DB::update(
            'UPDATE helpdesk_software_products SET active = 0 WHERE active = 1 AND code NOT IN (' . $placeholders . ')',
            ...$activeCodes
        );

        return is_int($result) ? $result : 0;
    }

    /**
     * List active products that have a handover protocol schema defined.
     *
     * @return list<array<string, mixed>>
     */
    public function listWithSchema(): array
    {
        $rows = DB::select(
            'SELECT code, bc_description, name FROM helpdesk_software_products '
            . 'WHERE active = 1 AND handover_protocol_schema IS NOT NULL '
            . 'ORDER BY COALESCE(NULLIF(name, \'\'), bc_description) ASC'
        );

        if (!is_array($rows)) {
            return [];
        }

        $result = [];
        foreach ($rows as $row) {
            $item = $row['helpdesk_software_products'] ?? $row;
            if (is_array($item) && isset($item['code'])) {
                $result[] = $item;
            }
        }

        return $result;
    }

    private function normalizeRow(mixed $row): ?array
    {
        if (!is_array($row)) {
            return null;
        }
        $item = $row['helpdesk_software_products'] ?? $row;
        if (!is_array($item) || !isset($item['id'])) {
            return null;
        }

        return $item;
    }
}
