<?php

namespace MintyPHP\Module\Helpdesk\Repository;

use MintyPHP\DB;

class HandoverRevisionRepository
{
    /**
     * @param array<string, mixed> $data
     * @return int|null Inserted ID or null on failure
     */
    public function insert(array $data): ?int
    {
        $result = DB::insert(
            'INSERT INTO helpdesk_handover_revisions '
            . '(tenant_id, handover_id, revision, field_values, status, schema_version, change_type, changed_by) '
            . 'VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            (string) ($data['tenant_id'] ?? 0),
            (string) ($data['handover_id'] ?? 0),
            (string) ($data['revision'] ?? 1),
            $data['field_values'] ?? null,
            (string) ($data['status'] ?? ''),
            (string) ($data['schema_version'] ?? 1),
            (string) ($data['change_type'] ?? 'fields'),
            (string) ($data['changed_by'] ?? 0)
        );

        if ($result === false) {
            return null;
        }

        return (int) $result;
    }

    /**
     * Get the latest revision number for a handover.
     */
    public function getLatestRevisionNumber(int $tenantId, int $handoverId): int
    {
        $value = DB::selectValue(
            'SELECT MAX(revision) FROM helpdesk_handover_revisions WHERE tenant_id = ? AND handover_id = ?',
            (string) $tenantId,
            (string) $handoverId
        );

        return $value !== null ? (int) $value : 0;
    }

    /**
     * Find a specific revision by handover ID and revision number.
     *
     * @return array<string, mixed>|null
     */
    public function findByRevision(int $tenantId, int $handoverId, int $revision): ?array
    {
        $row = DB::selectOne(
            'SELECT r.*, u.display_name AS changed_by_label, u.uuid AS changed_by_uuid'
            . ' FROM helpdesk_handover_revisions r'
            . ' LEFT JOIN users u ON u.id = r.changed_by'
            . ' WHERE r.tenant_id = ? AND r.handover_id = ? AND r.revision = ? LIMIT 1',
            (string) $tenantId,
            (string) $handoverId,
            (string) $revision
        );

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

    /**
     * List all revisions for a handover, newest first.
     *
     * @return list<array<string, mixed>>
     */
    public function listByHandover(int $tenantId, int $handoverId): array
    {
        $rows = DB::select(
            'SELECT r.*, u.display_name AS changed_by_label'
            . ' FROM helpdesk_handover_revisions r'
            . ' LEFT JOIN users u ON u.id = r.changed_by'
            . ' WHERE r.tenant_id = ? AND r.handover_id = ?'
            . ' ORDER BY r.revision DESC',
            (string) $tenantId,
            (string) $handoverId
        );

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

        return $result;
    }

    private function normalizeRow(mixed $row): ?array
    {
        if (!is_array($row)) {
            return null;
        }

        $item = [];
        foreach ($row as $key => $value) {
            if (is_array($value)) {
                $item = array_merge($item, $value);
            } else {
                $item[$key] = $value;
            }
        }

        if (!isset($item['id'])) {
            return null;
        }

        return $item;
    }
}