<?php
namespace boru\ai\Persistence\Db;

use boru\ai\Conversation\Conversation;
use boru\ai\Conversation\ConversationMemory;
use boru\ai\Conversation\ConversationNote;
use boru\ai\Conversation\ConversationReference;
use boru\ai\Conversation\Turn;
use boru\ai\Persistence\PersistenceProviderInterface;
use boru\ai\Schema\Request;
use boru\ai\Schema\Response;
use boru\ai\Schema\ToolCall;
use boru\ai\Schema\ToolResult;

class DbPersistenceProvider implements PersistenceProviderInterface
{
    const CONVERSATION_ROW_STORAGE_KEY = '__db_row_id';

    /** @var DbConnection */
    protected $connection;

    public function __construct(DbConnection $connection = null)
    {
        $this->connection = $connection ? $connection : new DbConnection();
    }

    public function connection(DbConnection $connection = null)
    {
        if ($connection !== null) {
            $this->connection = $connection;
            return $this;
        }
        return $this->connection;
    }

    public function loadConversation(ConversationReference $reference)
    {
        if (!$reference->id() && !$reference->externalReference()) {
            return false;
        }

        $db = $this->connection()->db();
        $sql = 'select * from `' . $this->table('conversations') . '`';
        $params = array();

        if ($reference->id()) {
            $sql .= ' where `conversation_id`=?';
            $params[] = $reference->id();
        } else {
            $sql .= ' where `external_reference`=?';
            $params[] = $reference->externalReference();
            if ($reference->userId()) {
                $sql .= ' and `user_id`=?';
                $params[] = $reference->userId();
            }
        }

        $sql .= ' limit 1';
        $result = $this->connection()->run($sql, $params);
        $row = $db->next($result);
        if (!$row) {
            return false;
        }

        $conversation = new Conversation();
        $conversation->reference()->id($row->get('conversation_id'));
        $conversation->reference()->externalReference($row->get('external_reference'));
        $conversation->reference()->userId($row->get('user_id'));
        $conversation->provider($row->get('provider'));
        $conversation->model($row->get('model'));
        $conversation->metadata($this->decodeJsonArray($row->get('metadata')));
        $this->storeConversationRowId($conversation, $row->get('id'));
        $conversation->memory($this->hydrateConversationMemory($row, $row->get('id')));
        $conversation->turns($this->loadTurnsForConversationPk($row->get('id')));

        return $conversation;
    }

    public function saveConversation(Conversation $conversation)
    {
        $db = $this->connection()->db();
        $table = $this->table('conversations');
        $now = $this->dbNow();

        if (!$conversation->reference()->id()) {
            $conversation->reference()->id('conv_' . uniqid());
        }

        $existingId = $this->findConversationRowId($conversation->reference()->id());

        if ($existingId !== null) {
            $sql = 'update `' . $table . '` set `external_reference`=?, `user_id`=?, `provider`=?, `model`=?, `metadata`=?, `title`=?, `summary`=?, `memory_metadata`=?, `updated_at`=? where `id`=?';
            $this->connection()->run($sql, array(
                $conversation->reference()->externalReference(),
                $conversation->reference()->userId(),
                $conversation->provider(),
                $conversation->model(),
                json_encode($conversation->metadata()),
                $conversation->memory()->title(),
                $conversation->memory()->summary(),
                json_encode($conversation->memory()->metadata()),
                $now,
                $existingId,
            ));
            $conversationRowId = $existingId;
        } else {
            $sql = 'insert into `' . $table . '` (`conversation_id`, `external_reference`, `user_id`, `provider`, `model`, `metadata`, `title`, `summary`, `memory_metadata`, `created_at`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
            $this->connection()->run($sql, array(
                $conversation->reference()->id(),
                $conversation->reference()->externalReference(),
                $conversation->reference()->userId(),
                $conversation->provider(),
                $conversation->model(),
                json_encode($conversation->metadata()),
                $conversation->memory()->title(),
                $conversation->memory()->summary(),
                json_encode($conversation->memory()->metadata()),
                $now,
                $now,
            ));
            $conversationRowId = $this->findConversationRowId($conversation->reference()->id());
            if ($conversationRowId === null && method_exists($db, 'lastInsertId')) {
                $lastInsertId = $db->lastInsertId();
                if ($lastInsertId !== false && $lastInsertId !== null && $lastInsertId !== '') {
                    $conversationRowId = (int)$lastInsertId;
                }
            }
        }

        if ($conversationRowId !== null) {
            $this->storeConversationRowId($conversation, $conversationRowId);
            $this->saveConversationNotes($conversationRowId, $conversation->memory(), $now);
        }

        return $conversation;
    }

    public function saveTurn(Conversation $conversation, Turn $turn)
    {
        $db = $this->connection()->db();
        $table = $this->table('turns');
        $now = $this->dbNow();
        $conversationRowId = $this->resolveConversationRowId($conversation);

        if ($conversationRowId === null) {
            $this->saveConversation($conversation);
            $conversationRowId = $this->resolveConversationRowId($conversation);
        }

        if ($conversationRowId === null) {
            throw new \RuntimeException('Conversation must be saved before saving a turn');
        }

        $existingId = $this->findTurnRowId($conversationRowId, $turn->number());
        $params = array(
            $conversationRowId,
            $turn->number(),
            $turn->status(),
            $turn->request() ? json_encode($turn->request()->toArray()) : null,
            $turn->response() ? json_encode($turn->response()->toArray()) : null,
            json_encode($this->normalizeToolResults($turn)),
            json_encode($turn->metadata()),
            $this->normalizeDateTime($turn->startedAt()),
            $this->normalizeDateTime($turn->completedAt()),
            $now,
        );

        if ($existingId !== null) {
            $sql = 'update `' . $table . '` set `conversation_pk`=?, `turn_number`=?, `status`=?, `request_json`=?, `response_json`=?, `tool_results_json`=?, `metadata`=?, `started_at`=?, `completed_at`=?, `updated_at`=? where `id`=?';
            $params[] = $existingId;
            $this->connection()->run($sql, $params);
            $turnRowId = $existingId;
        } else {
            $sql = 'insert into `' . $table . '` (`conversation_pk`, `turn_number`, `status`, `request_json`, `response_json`, `tool_results_json`, `metadata`, `started_at`, `completed_at`, `created_at`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
            $params[] = $now;
            $this->connection()->run($sql, $params);
            $turnRowId = $this->findTurnRowId($conversationRowId, $turn->number());
        }

        if ($turnRowId !== null && $this->connection()->config()->persistToolCalls()) {
            $this->saveToolCalls($turnRowId, $turn, $now);
        }

        return $turn;
    }

    public function saveToolCallStarted($conversationId, $turnNumber, ToolCall $toolCall)
    {
        if (!$this->connection()->config()->persistToolCalls()) {
            return false;
        }

        $conversationRowId = $this->findConversationRowId($conversationId);
        if ($conversationRowId === null) {
            return false;
        }

        $turnRowId = $this->findTurnRowId($conversationRowId, $turnNumber);
        if ($turnRowId === null) {
            return false;
        }

        $now = $this->dbNow();
        $existing = $this->findToolCallRowId($turnRowId, $toolCall->id());
        $metadata = json_encode(array(
            'tool_call_metadata' => $toolCall->metadata(),
        ));
        $argumentsJson = $toolCall->argumentsJson() ? $toolCall->argumentsJson() : json_encode($toolCall->arguments());

        $db = $this->connection()->db();
        $table = $this->table('tool_calls');

        if ($existing !== null) {
            $sql = 'update `' . $table . '` set `name`=?, `arguments_json`=?, `status`=?, `metadata`=?, `updated_at`=? where `id`=?';
            $this->connection()->run($sql, array(
                $toolCall->name(),
                $argumentsJson,
                'started',
                $metadata,
                $now,
                $existing,
            ));
            return $existing;
        }

        $sequence = $this->nextToolCallSequence($turnRowId);
        $sql = 'insert into `' . $table . '` (`turn_pk`, `tool_call_id`, `name`, `arguments_json`, `result_json`, `success`, `status`, `sequence_number`, `metadata`, `created_at`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
        $this->connection()->run($sql, array(
            $turnRowId,
            $toolCall->id(),
            $toolCall->name(),
            $argumentsJson,
            null,
            null,
            'started',
            $sequence,
            $metadata,
            $now,
            $now,
        ));

        return $this->findToolCallRowId($turnRowId, $toolCall->id());
    }

    public function saveToolCallCompleted($conversationId, $turnNumber, ToolCall $toolCall, ToolResult $toolResult)
    {
        return $this->saveToolCallResultState($conversationId, $turnNumber, $toolCall, $toolResult, 'completed');
    }

    public function saveToolCallFailed($conversationId, $turnNumber, ToolCall $toolCall, ToolResult $toolResult)
    {
        return $this->saveToolCallResultState($conversationId, $turnNumber, $toolCall, $toolResult, 'failed');
    }

    public function prune(array $options = array())
    {
        $scope = isset($options['scope']) ? strtolower((string)$options['scope']) : 'conversations';
        $before = isset($options['before']) ? $options['before'] : null;
        $dryRun = !empty($options['dry_run']);

        if (!in_array($scope, array('conversations', 'turns', 'tool_calls', 'all'), true)) {
            throw new \InvalidArgumentException('Unsupported prune scope: ' . $scope);
        }

        $normalizedBefore = $this->normalizePruneBefore($before);

        $summary = array(
            'scope' => $scope,
            'before' => $normalizedBefore,
            'dry_run' => $dryRun,
            'conversations' => 0,
            'turns' => 0,
            'tool_calls' => 0,
            'conversation_notes' => 0,
        );

        $conversationTurnIds = array();
        $prunedConversationIds = array();
        $prunedTurnIds = array();

        if ($scope === 'conversations' || $scope === 'all') {
            $conversationIds = $this->listConversationRowIdsBefore($normalizedBefore);
            foreach ($conversationIds as $conversationRowId) {
                $prunedConversationIds[] = $conversationRowId;
                $conversationTurnIds = array_merge($conversationTurnIds, $this->listTurnRowIdsForConversation($conversationRowId));
                $counts = $this->summarizeConversationDeletion($conversationRowId);
                $summary['conversations'] += $counts['conversations'];
                $summary['turns'] += $counts['turns'];
                $summary['tool_calls'] += $counts['tool_calls'];
                $summary['conversation_notes'] += $counts['conversation_notes'];

                if (!$dryRun) {
                    $this->deleteConversationRowById($conversationRowId);
                }
            }
        }

        if ($scope === 'turns' || $scope === 'all') {
            $turnIds = $this->listTurnRowIdsBefore($normalizedBefore, $prunedConversationIds);
            foreach ($turnIds as $turnRowId) {
                $prunedTurnIds[] = $turnRowId;
                $counts = $this->summarizeTurnDeletion($turnRowId);
                $summary['turns'] += $counts['turns'];
                $summary['tool_calls'] += $counts['tool_calls'];

                if (!$dryRun) {
                    $this->deleteTurnRowById($turnRowId);
                }
            }
        }

        if ($scope === 'tool_calls' || $scope === 'all') {
            $excludedTurnIds = array_values(array_unique(array_merge($conversationTurnIds, $prunedTurnIds)));
            $toolCallIds = $this->listToolCallRowIdsBefore($normalizedBefore, $excludedTurnIds);
            foreach ($toolCallIds as $toolCallRowId) {
                $summary['tool_calls']++;

                if (!$dryRun) {
                    $this->deleteToolCallRowById($toolCallRowId);
                }
            }
        }

        return $summary;
    }

    protected function saveToolCallResultState($conversationId, $turnNumber, ToolCall $toolCall, ToolResult $toolResult, $status)
    {
        if (!$this->connection()->config()->persistToolCalls()) {
            return false;
        }

        $conversationRowId = $this->findConversationRowId($conversationId);
        if ($conversationRowId === null) {
            return false;
        }

        $turnRowId = $this->findTurnRowId($conversationRowId, $turnNumber);
        if ($turnRowId === null) {
            return false;
        }

        $now = $this->dbNow();
        $db = $this->connection()->db();
        $table = $this->table('tool_calls');
        $existing = $this->findToolCallRowId($turnRowId, $toolCall->id());
        if ($existing === null) {
            $this->saveToolCallStarted($conversationId, $turnNumber, $toolCall);
            $existing = $this->findToolCallRowId($turnRowId, $toolCall->id());
            if ($existing === null) {
                return false;
            }
        }

        $metadata = json_encode(array(
            'tool_call_metadata' => $toolCall->metadata(),
            'tool_result_metadata' => $toolResult->metadata(),
        ));

        $sql = 'update `' . $table . '` set `result_json`=?, `success`=?, `status`=?, `metadata`=?, `updated_at`=? where `id`=?';
        $this->connection()->run($sql, array(
            json_encode($toolResult->toArray()),
            $toolResult->success() ? 1 : 0,
            $status,
            $metadata,
            $now,
            $existing,
        ));

        return $existing;
    }

    protected function normalizePruneBefore($before)
    {
        if (!is_string($before) || trim($before) === '') {
            throw new \InvalidArgumentException('Prune before timestamp is required');
        }

        $timestamp = strtotime($before);
        if ($timestamp === false) {
            throw new \InvalidArgumentException('Unable to parse prune before timestamp: ' . $before);
        }

        return date('Y-m-d H:i:s', $timestamp);
    }

    protected function listConversationRowIdsBefore($before)
    {
        $db = $this->connection()->db();
        $sql = 'select `id` from `' . $this->table('conversations') . '` where `updated_at` < ? order by `id` asc';
        $result = $this->connection()->run($sql, array($before));

        $ids = array();
        while ($row = $db->next($result)) {
            $ids[] = (int)$row->get('id');
        }

        return $ids;
    }

    protected function listTurnRowIdsBefore($before, array $excludeConversationRowIds = array())
    {
        $db = $this->connection()->db();
        $sql = 'select `id` from `' . $this->table('turns') . '` where `updated_at` < ?';
        $params = array($before);

        if (!empty($excludeConversationRowIds)) {
            $sql .= ' and `conversation_pk` not in (' . implode(', ', array_fill(0, count($excludeConversationRowIds), '?')) . ')';
            foreach ($excludeConversationRowIds as $conversationRowId) {
                $params[] = $conversationRowId;
            }
        }

        $sql .= ' order by `id` asc';
        $result = $this->connection()->run($sql, $params);

        $ids = array();
        while ($row = $db->next($result)) {
            $ids[] = (int)$row->get('id');
        }

        return $ids;
    }

    protected function listToolCallRowIdsBefore($before, array $excludeTurnRowIds = array())
    {
        $db = $this->connection()->db();
        $sql = 'select `id` from `' . $this->table('tool_calls') . '` where `updated_at` < ?';
        $params = array($before);

        if (!empty($excludeTurnRowIds)) {
            $sql .= ' and `turn_pk` not in (' . implode(', ', array_fill(0, count($excludeTurnRowIds), '?')) . ')';
            foreach ($excludeTurnRowIds as $turnRowId) {
                $params[] = $turnRowId;
            }
        }

        $sql .= ' order by `id` asc';
        $result = $this->connection()->run($sql, $params);

        $ids = array();
        while ($row = $db->next($result)) {
            $ids[] = (int)$row->get('id');
        }

        return $ids;
    }

    protected function summarizeConversationDeletion($conversationRowId)
    {
        $noteCount = $this->countRowsByColumn($this->table('conversation_notes'), 'conversation_pk', $conversationRowId);
        $turnIds = $this->listTurnRowIdsForConversation($conversationRowId);
        $toolCallCount = 0;

        foreach ($turnIds as $turnRowId) {
            $toolCallCount += $this->countRowsByColumn($this->table('tool_calls'), 'turn_pk', $turnRowId);
        }

        return array(
            'conversations' => 1,
            'turns' => count($turnIds),
            'tool_calls' => $toolCallCount,
            'conversation_notes' => $noteCount,
        );
    }

    protected function summarizeTurnDeletion($turnRowId)
    {
        return array(
            'turns' => 1,
            'tool_calls' => $this->countRowsByColumn($this->table('tool_calls'), 'turn_pk', $turnRowId),
        );
    }

    protected function listTurnRowIdsForConversation($conversationRowId)
    {
        $db = $this->connection()->db();
        $sql = 'select `id` from `' . $this->table('turns') . '` where `conversation_pk`=? order by `id` asc';
        $result = $this->connection()->run($sql, array($conversationRowId));

        $ids = array();
        while ($row = $db->next($result)) {
            $ids[] = (int)$row->get('id');
        }

        return $ids;
    }

    protected function countRowsByColumn($table, $column, $value)
    {
        $db = $this->connection()->db();
        $sql = 'select count(*) as `row_count` from `' . $table . '` where `' . $column . '`=?';
        $result = $this->connection()->run($sql, array($value));
        $row = $db->next($result);

        return $row ? (int)$row->get('row_count') : 0;
    }

    protected function deleteConversationRowById($conversationRowId)
    {
        foreach ($this->listTurnRowIdsForConversation($conversationRowId) as $turnRowId) {
            $this->deleteTurnRowById($turnRowId);
        }

        $this->connection()->run(
            'delete from `' . $this->table('conversation_notes') . '` where `conversation_pk`=?',
            array($conversationRowId)
        );
        $this->connection()->run(
            'delete from `' . $this->table('conversations') . '` where `id`=?',
            array($conversationRowId)
        );

        return true;
    }

    protected function deleteTurnRowById($turnRowId)
    {
        $this->connection()->run(
            'delete from `' . $this->table('tool_calls') . '` where `turn_pk`=?',
            array($turnRowId)
        );
        $this->connection()->run(
            'delete from `' . $this->table('turns') . '` where `id`=?',
            array($turnRowId)
        );

        return true;
    }

    protected function deleteToolCallRowById($toolCallRowId)
    {
        $this->connection()->run(
            'delete from `' . $this->table('tool_calls') . '` where `id`=?',
            array($toolCallRowId)
        );

        return true;
    }

    protected function saveToolCalls($turnRowId, Turn $turn, $now)
    {
        $db = $this->connection()->db();
        $table = $this->table('tool_calls');

        $this->connection()->run('delete from `' . $table . '` where `turn_pk`=?', array($turnRowId));

        $toolResultsByCallId = array();
        foreach ($turn->toolResults() as $toolResult) {
            $toolResultsByCallId[$toolResult->toolCallId()] = $toolResult;
        }

        $sequence = 0;
        foreach ($turn->toolCalls() as $toolCall) {
            $sequence++;
            $toolResult = isset($toolResultsByCallId[$toolCall->id()]) ? $toolResultsByCallId[$toolCall->id()] : null;

            $resultJson = null;
            $success = null;
            $metadata = array(
                'tool_call_metadata' => $toolCall->metadata(),
            );

            if ($toolResult instanceof ToolResult) {
                $resultJson = json_encode($toolResult->toArray());
                $success = $toolResult->success() ? 1 : 0;
                $metadata['tool_result_metadata'] = $toolResult->metadata();
            }

            $sql = 'insert into `' . $table . '` (`turn_pk`, `tool_call_id`, `name`, `arguments_json`, `result_json`, `success`, `status`, `sequence_number`, `metadata`, `created_at`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
            $this->connection()->run($sql, array(
                $turnRowId,
                $toolCall->id(),
                $toolCall->name(),
                $toolCall->argumentsJson() ? $toolCall->argumentsJson() : json_encode($toolCall->arguments()),
                $resultJson,
                $success,
                $toolCall->status(),
                $sequence,
                json_encode($metadata),
                $now,
                $now,
            ));
        }
    }

    protected function findConversationRowId($conversationId)
    {
        if (!$conversationId) {
            return null;
        }

        $db = $this->connection()->db();
        $sql = 'select `id` from `' . $this->table('conversations') . '` where `conversation_id`=? limit 1';
        $result = $this->connection()->run($sql, array($conversationId));
        $row = $db->next($result);

        return $row ? $row->get('id') : null;
    }

    protected function resolveConversationRowId(Conversation $conversation)
    {
        $rowId = $conversation->storage(self::CONVERSATION_ROW_STORAGE_KEY);
        if ($rowId !== null && $rowId !== '') {
            return (int)$rowId;
        }

        $rowId = $this->findConversationRowId($conversation->reference()->id());
        if ($rowId !== null) {
            $this->storeConversationRowId($conversation, $rowId);
            return (int)$rowId;
        }

        return null;
    }

    protected function storeConversationRowId(Conversation $conversation, $rowId)
    {
        if ($rowId === null || $rowId === '') {
            return $conversation;
        }

        $conversation->storage(self::CONVERSATION_ROW_STORAGE_KEY, (int)$rowId);
        return $conversation;
    }

    protected function findTurnRowId($conversationPk, $turnNumber)
    {
        $db = $this->connection()->db();
        $sql = 'select `id` from `' . $this->table('turns') . '` where `conversation_pk`=? and `turn_number`=? limit 1';
        $result = $this->connection()->run($sql, array($conversationPk, $turnNumber));
        $row = $db->next($result);

        return $row ? $row->get('id') : null;
    }

    protected function findToolCallRowId($turnPk, $toolCallId)
    {
        $db = $this->connection()->db();
        $sql = 'select `id` from `' . $this->table('tool_calls') . '` where `turn_pk`=? and `tool_call_id`=? limit 1';
        $result = $this->connection()->run($sql, array($turnPk, $toolCallId));
        $row = $db->next($result);

        return $row ? $row->get('id') : null;
    }

    protected function nextToolCallSequence($turnPk)
    {
        $db = $this->connection()->db();
        $sql = 'select max(`sequence_number`) as `max_sequence` from `' . $this->table('tool_calls') . '` where `turn_pk`=?';
        $result = $this->connection()->run($sql, array($turnPk));
        $row = $db->next($result);
        $max = $row ? $row->get('max_sequence') : null;
        return $max ? ((int)$max + 1) : 1;
    }

    protected function loadNotesForConversationPk($conversationPk)
    {
        $db = $this->connection()->db();
        $sql = 'select * from `' . $this->table('conversation_notes') . '` where `conversation_pk`=? order by `id` asc';
        $result = $this->connection()->run($sql, array($conversationPk));

        $notes = array();
        while ($row = $db->next($result)) {
            $note = new ConversationNote();
            $note->id($row->get('note_id'));
            $note->kind($row->get('kind'));
            $note->text($row->get('content'));
            if ($row->get('confidence') !== null && $row->get('confidence') !== '') {
                $note->confidence((float)$row->get('confidence'));
            }
            if ($row->get('source_turn_number') !== null && $row->get('source_turn_number') !== '') {
                $note->sourceTurnNumber((int)$row->get('source_turn_number'));
            }
            $note->metadata($this->decodeJsonArray($row->get('metadata')));
            $note->createdAt($row->get('created_at'));
            $note->updatedAt($row->get('updated_at'));
            $notes[] = $note;
        }

        return $notes;
    }

    protected function table($key)
    {
        return $this->connection()->config()->table($key);
    }

    protected function loadTurnsForConversationPk($conversationPk)
    {
        $db = $this->connection()->db();
        $sql = 'select * from `' . $this->table('turns') . '` where `conversation_pk`=? order by `turn_number` asc';
        $result = $this->connection()->run($sql, array($conversationPk));

        $turns = array();
        while ($row = $db->next($result)) {
            $turns[] = $this->hydrateTurn($row);
        }

        return $turns;
    }

    protected function hydrateTurn($row)
    {
        $turn = new Turn();
        $turn->number($row->get('turn_number'));
        $turn->status($row->get('status'));
        $turn->metadata($this->decodeJsonArray($row->get('metadata')));
        $turn->startedAt($row->get('started_at'));
        $turn->completedAt($row->get('completed_at'));

        $request = $this->hydrateRequest($row->get('request_json'));
        if ($request instanceof Request) {
            $turn->request($request);
        }

        $response = $this->hydrateResponse($row->get('response_json'));
        if ($response instanceof Response) {
            $turn->response($response);
        }

        $turn->toolResults($this->hydrateToolResults($row->get('tool_results_json')));

        return $turn;
    }

    protected function hydrateConversationMemory($row, $conversationPk)
    {
        $memory = new ConversationMemory();
        $memory->title($row->get('title'));
        $memory->summary($row->get('summary'));
        $memory->metadata($this->decodeJsonArray($row->get('memory_metadata')));
        $memory->notes($this->loadNotesForConversationPk($conversationPk));
        return $memory;
    }

    protected function hydrateRequest($json)
    {
        $data = $this->decodeJsonArray($json);
        if (empty($data)) {
            return null;
        }

        $request = new Request();

        if (isset($data['provider'])) {
            $request->provider($data['provider']);
        }
        if (isset($data['model'])) {
            $request->model($data['model']);
        }
        if (isset($data['instructions'])) {
            $request->instructions($data['instructions']);
        }
        if (isset($data['messages']) && is_array($data['messages'])) {
            $request->messages($data['messages']);
        }
        if (isset($data['tools']) && is_array($data['tools'])) {
            $request->tools($data['tools']);
        }
        if (isset($data['options']) && is_array($data['options'])) {
            $request->options($data['options']);
        }
        if (isset($data['metadata']) && is_array($data['metadata'])) {
            $request->metadata($data['metadata']);
        }
        if (isset($data['tool_results']) && is_array($data['tool_results'])) {
            $request->toolResults($this->hydrateToolResultsArray($data['tool_results']));
        }

        return $request;
    }

    protected function hydrateResponse($json)
    {
        $data = $this->decodeJsonArray($json);
        if (empty($data)) {
            return null;
        }

        $response = new Response();

        if (isset($data['id'])) {
            $response->id($data['id']);
        }
        if (isset($data['provider'])) {
            $response->provider($data['provider']);
        }
        if (isset($data['model'])) {
            $response->model($data['model']);
        }
        if (isset($data['status'])) {
            $response->status($data['status']);
        }
        if (isset($data['text'])) {
            $response->text($data['text']);
        }
        if (isset($data['usage']) && is_array($data['usage'])) {
            $response->usage($data['usage']);
        }
        if (array_key_exists('error', $data)) {
            $response->error($data['error']);
        }
        if (array_key_exists('raw', $data)) {
            $response->raw($data['raw']);
        }
        if (isset($data['metadata']) && is_array($data['metadata'])) {
            $response->metadata($data['metadata']);
        }
        if (isset($data['tool_calls']) && is_array($data['tool_calls'])) {
            $response->toolCalls($this->hydrateToolCallsArray($data['tool_calls']));
        }

        return $response;
    }

    protected function hydrateToolResults($json)
    {
        $data = $this->decodeJsonArray($json);
        if (empty($data)) {
            return array();
        }

        return $this->hydrateToolResultsArray($data);
    }

    protected function hydrateToolResultsArray(array $items)
    {
        $results = array();
        foreach ($items as $item) {
            if (!is_array($item)) {
                continue;
            }

            $toolResult = new ToolResult();
            if (isset($item['tool_call_id'])) {
                $toolResult->toolCallId($item['tool_call_id']);
            }
            if (isset($item['name'])) {
                $toolResult->name($item['name']);
            }
            if (array_key_exists('output', $item)) {
                $toolResult->output($item['output']);
            }
            if (isset($item['success'])) {
                $toolResult->success($item['success']);
            }
            if (isset($item['metadata']) && is_array($item['metadata'])) {
                $toolResult->metadata($item['metadata']);
            }
            $results[] = $toolResult;
        }

        return $results;
    }

    protected function hydrateToolCallsArray(array $items)
    {
        $calls = array();
        foreach ($items as $item) {
            if (!is_array($item)) {
                continue;
            }

            $toolCall = new ToolCall();
            if (isset($item['id'])) {
                $toolCall->id($item['id']);
            }
            if (isset($item['name'])) {
                $toolCall->name($item['name']);
            }
            if (isset($item['arguments']) && is_array($item['arguments'])) {
                $toolCall->arguments($item['arguments']);
            }
            if (isset($item['arguments_json'])) {
                $toolCall->argumentsJson($item['arguments_json']);
            }
            if (isset($item['status'])) {
                $toolCall->status($item['status']);
            }
            if (isset($item['metadata']) && is_array($item['metadata'])) {
                $toolCall->metadata($item['metadata']);
            }
            $calls[] = $toolCall;
        }

        return $calls;
    }

    protected function decodeJsonArray($json)
    {
        if (!is_string($json) || $json === '') {
            return array();
        }

        $decoded = json_decode($json, true);
        return is_array($decoded) ? $decoded : array();
    }

    protected function normalizeToolResults(Turn $turn)
    {
        $results = array();
        foreach ($turn->toolResults() as $toolResult) {
            $results[] = $toolResult->toArray();
        }
        return $results;
    }

    protected function saveConversationNotes($conversationRowId, ConversationMemory $memory, $now)
    {
        $db = $this->connection()->db();
        $table = $this->table('conversation_notes');

        $this->connection()->run('delete from `' . $table . '` where `conversation_pk`=?', array($conversationRowId));

        foreach ($memory->notes() as $note) {
            if (!$note instanceof ConversationNote) {
                continue;
            }

            if (!$note->id()) {
                $note->id('note_' . uniqid());
            }

            $createdAt = $this->normalizeDateTime($note->createdAt());
            $updatedAt = $this->normalizeDateTime($note->updatedAt());
            if ($createdAt === null) {
                $createdAt = $now;
            }
            if ($updatedAt === null) {
                $updatedAt = $now;
            }

            $sql = 'insert into `' . $table . '` (`note_id`, `conversation_pk`, `kind`, `content`, `confidence`, `source_turn_number`, `metadata`, `created_at`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?, ?, ?)';
            $this->connection()->run($sql, array(
                $note->id(),
                $conversationRowId,
                $note->kind(),
                $note->text(),
                $note->confidence(),
                $note->sourceTurnNumber(),
                json_encode($note->metadata()),
                $createdAt,
                $updatedAt,
            ));
        }
    }

    protected function dbNow()
    {
        return date('Y-m-d H:i:s');
    }

    protected function normalizeDateTime($value)
    {
        if ($value === null || $value === '') {
            return null;
        }

        $timestamp = strtotime($value);
        if ($timestamp === false) {
            return $value;
        }

        return date('Y-m-d H:i:s', $timestamp);
    }
}
