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

use boru\dhdb\Query;
use boru\dhdb\Schema;

class DbSchemaManager
{
    /** @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 ensureSchema()
    {
        $this->ensureConversationsTable();
        $this->ensureTurnsTable();
        $this->ensureAssetsTable();
        $this->ensureProviderAssetBindingsTable();
        $this->ensureToolCallsTable();
        $this->ensureEmbeddingsTable();
        $this->ensureConversationNotesTable();
        return $this;
    }

    public function ensureConversationsTable()
    {
        $this->ensureTable($this->buildConversationsSchema());

        $tableName = $this->table('conversations');
        $this->ensureColumnExists($tableName, 'title', 'varchar(255) null');
        $this->ensureColumnExists($tableName, 'summary', 'longtext null');
        $this->ensureColumnExists($tableName, 'memory_metadata', 'longtext null');

        return $this;
    }

    public function ensureTurnsTable()
    {
        return $this->ensureTable($this->buildTurnsSchema());
    }

    public function ensureAssetsTable()
    {
        return $this->ensureTable($this->buildAssetsSchema());
    }

    public function ensureProviderAssetBindingsTable()
    {
        return $this->ensureTable($this->buildProviderAssetBindingsSchema());
    }

    public function ensureToolCallsTable()
    {
        return $this->ensureTable($this->buildToolCallsSchema());
    }

    public function ensureEmbeddingsTable()
    {
        return $this->ensureTable($this->buildEmbeddingsSchema());
    }

    public function ensureConversationNotesTable()
    {
        return $this->ensureTable($this->buildConversationNotesSchema());
    }

    protected function ensureTable(Schema $expected)
    {
        $db = $this->connection()->db();
        $tableName = $expected->getTableName();

        if (!$this->tableExists($tableName)) {
            $this->connection()->run($db->fromQuery($expected->toQuery(false), false));
            return $this;
        }

        $current = $db->table($tableName);
        $query = $this->buildSchemaPatchQuery($current, $expected);
        $sql = $query ? $db->fromQuery($query, false) : '';
        if (trim($sql) !== '') {
            $this->connection()->run($sql);
        }

        return $this;
    }

    protected function ensureColumnExists($tableName, $columnName, $definitionSql)
    {
        if ($this->columnExists($tableName, $columnName)) {
            return $this;
        }

        $sql = 'alter table `' . $tableName . '` add `' . $columnName . '` ' . $definitionSql;
        $this->connection()->run($sql);

        return $this;
    }

    protected function columnExists($tableName, $columnName)
    {
        $db = $this->connection()->db();
        $result = $this->connection()->run('SHOW COLUMNS FROM `' . $tableName . '` LIKE ?', array($columnName));
        $row = $db->next($result);
        return $row ? true : false;
    }

    protected function copySchemaMeta(Schema $current, Schema $expected)
    {
        $metaKeys = array('engine', 'collate', 'comment');
        foreach ($metaKeys as $metaKey) {
            $normalizedValue = $this->normalizeSchemaMetaValue($this->getSchemaMetaValue($current, $metaKey));
            if ($normalizedValue !== null) {
                $expected->meta($metaKey, $normalizedValue);
            }
        }
        return $expected;
    }

    protected function getSchemaMetaValue(Schema $schema, $metaKey)
    {
        $meta = $schema->getMeta();
        if (!is_array($meta) || !isset($meta[$metaKey]) || !is_array($meta[$metaKey])) {
            return null;
        }

        if (!array_key_exists('value', $meta[$metaKey])) {
            return null;
        }

        return $meta[$metaKey]['value'];
    }

    protected function normalizeSchemaMetaValue($value)
    {
        if ($value === null) {
            return null;
        }

        if (!is_string($value)) {
            return $value;
        }

        $trimmedValue = trim($value);
        if ($trimmedValue === '') {
            return null;
        }

        $normalizedValue = strtolower($trimmedValue);
        if ($normalizedValue === 'null' || $normalizedValue === 'none') {
            return null;
        }

        return $trimmedValue;
    }

    protected function buildSchemaPatchQuery(Schema $current, Schema $expected)
    {
        $query = new Query();
        $query->alter($expected->getTableName());
        $hasChanges = false;

        $currentColumns = $this->normalizeSchemaEntries($current->getColumns());
        $expectedColumns = $this->normalizeSchemaEntries($expected->getColumns());
        $expectedAfter = $this->buildColumnAfterMap($expectedColumns);
        $currentAfter = $this->buildColumnAfterMap($currentColumns);

        foreach ($expectedColumns as $columnName => $column) {
            $extra = $this->normalizeColumnExtra($column['extra'], $expectedAfter[$columnName]);
            if (!isset($currentColumns[$columnName])) {
                $query->add($columnName, $column['type'], $extra);
                $hasChanges = true;
                continue;
            }

            $currentColumn = $currentColumns[$columnName];
            $currentExtra = $this->normalizeColumnExtra($currentColumn['extra'], $currentAfter[$columnName]);
            if ($currentColumn['type'] !== $column['type'] || $currentExtra !== $extra) {
                $query->change($columnName, $columnName, $column['type'], $extra);
                $hasChanges = true;
            }
        }

        $currentKeys = $this->normalizeSchemaEntries($current->getKeys());
        $expectedKeys = $this->normalizeSchemaEntries($expected->getKeys());
        foreach ($expectedKeys as $keyName => $key) {
            $expectedKey = $this->normalizeKeyDefinition($key);
            if (!isset($currentKeys[$keyName])) {
                $query->addIndex($keyName, $expectedKey['columns'], $expectedKey['type']);
                $hasChanges = true;
                continue;
            }

            $currentKey = $this->normalizeKeyDefinition($currentKeys[$keyName]);
            if ($currentKey !== $expectedKey) {
                if ($keyName !== 'PRIMARY') {
                    $query->dropIndex($keyName);
                }
                $query->addIndex($keyName, $expectedKey['columns'], $expectedKey['type']);
                $hasChanges = true;
            }
        }

        foreach (array('engine', 'collate', 'comment') as $metaKey) {
            $currentValue = $this->normalizeSchemaMetaValue($this->getSchemaMetaValue($current, $metaKey));
            $expectedValue = $this->normalizeSchemaMetaValue($this->getSchemaMetaValue($expected, $metaKey));
            if ($currentValue !== null && $expectedValue !== null && $currentValue !== $expectedValue) {
                $query->meta($metaKey, $expectedValue);
                $hasChanges = true;
            }
        }

        return $hasChanges ? $query : false;
    }

    protected function normalizeSchemaEntries($entries)
    {
        return is_array($entries) ? $entries : array();
    }

    protected function buildColumnAfterMap(array $columns)
    {
        $afterMap = array();
        $previousColumn = false;
        foreach (array_keys($columns) as $columnName) {
            $afterMap[$columnName] = $previousColumn;
            $previousColumn = $columnName;
        }

        return $afterMap;
    }

    protected function normalizeColumnExtra($extra, $after = false)
    {
        $normalized = is_array($extra) ? $extra : array();
        if ($after !== false) {
            $normalized['after'] = $after;
        } else {
            unset($normalized['after']);
        }

        if (isset($normalized['collate'])) {
            $normalized['collate'] = $this->normalizeSchemaMetaValue($normalized['collate']);
            if ($normalized['collate'] === null) {
                unset($normalized['collate']);
            }
        }

        ksort($normalized);

        return $normalized;
    }

    protected function normalizeKeyDefinition(array $key)
    {
        $columns = isset($key['columns']) && is_array($key['columns']) ? array_values($key['columns']) : array();
        $type = isset($key['type']) ? strtolower($key['type']) : 'key';

        return array(
            'type' => $type,
            'columns' => $columns,
        );
    }

    protected function tableExists($tableName)
    {
        $row = $this->connection()->run('SHOW TABLE STATUS LIKE ?', array($tableName))->next();
        return $row ? true : false;
    }

    protected function buildConversationsSchema()
    {
        $schema = new Schema($this->table('conversations'));
        $schema->add('id', 'bigint(20)', array('null' => false, 'auto_increment' => true));
        $schema->add('conversation_id', 'varchar(100)', array('null' => false));
        $schema->add('external_reference', 'varchar(191)', array('null' => true));
        $schema->add('user_id', 'varchar(191)', array('null' => true));
        $schema->add('provider', 'varchar(100)', array('null' => true));
        $schema->add('model', 'varchar(191)', array('null' => true));
        $schema->add('metadata', 'longtext', array('null' => true));
        $schema->add('title', 'varchar(255)', array('null' => true));
        $schema->add('summary', 'longtext', array('null' => true));
        $schema->add('memory_metadata', 'longtext', array('null' => true));
        $schema->add('created_at', 'datetime', array('null' => false));
        $schema->add('updated_at', 'datetime', array('null' => true));
        $schema->addIndex('PRIMARY', array('id'), 'primary');
        $schema->addIndex('idx_ai_conversations_conversation_id_unique', array('conversation_id'), 'unique');
        $schema->addIndex('idx_ai_conversations_external_reference', array('external_reference'), 'key');
        $schema->addIndex('idx_ai_conversations_user_id', array('user_id'), 'key');
        $schema->meta('engine', 'InnoDB');
        return $schema;
    }

    protected function buildConversationNotesSchema()
    {
        $schema = new Schema($this->table('conversation_notes'));
        $schema->add('id', 'bigint(20)', array('null' => false, 'auto_increment' => true));
        $schema->add('note_id', 'varchar(100)', array('null' => false));
        $schema->add('conversation_pk', 'bigint(20)', array('null' => false));
        $schema->add('kind', 'varchar(100)', array('null' => false, 'default' => 'note'));
        $schema->add('content', 'longtext', array('null' => false));
        $schema->add('confidence', 'decimal(8,4)', array('null' => true));
        $schema->add('source_turn_number', 'int(11)', array('null' => true));
        $schema->add('metadata', 'longtext', array('null' => true));
        $schema->add('created_at', 'datetime', array('null' => true));
        $schema->add('updated_at', 'datetime', array('null' => true));
        $schema->addIndex('PRIMARY', array('id'), 'primary');
        $schema->addIndex('idx_ai_conversation_notes_note_id_unique', array('note_id'), 'unique');
        $schema->addIndex('idx_ai_conversation_notes_conversation_pk', array('conversation_pk'), 'key');
        $schema->addIndex('idx_ai_conversation_notes_kind', array('kind'), 'key');
        $schema->meta('engine', 'InnoDB');
        return $schema;
    }

    protected function buildTurnsSchema()
    {
        $schema = new Schema($this->table('turns'));
        $schema->add('id', 'bigint(20)', array('null' => false, 'auto_increment' => true));
        $schema->add('conversation_pk', 'bigint(20)', array('null' => false));
        $schema->add('turn_number', 'int(11)', array('null' => false));
        $schema->add('status', 'varchar(100)', array('null' => false, 'default' => 'pending'));
        $schema->add('request_json', 'longtext', array('null' => true));
        $schema->add('response_json', 'longtext', array('null' => true));
        $schema->add('tool_results_json', 'longtext', array('null' => true));
        $schema->add('metadata', 'longtext', array('null' => true));
        $schema->add('started_at', 'datetime', array('null' => true));
        $schema->add('completed_at', 'datetime', array('null' => true));
        $schema->add('created_at', 'datetime', array('null' => false));
        $schema->add('updated_at', 'datetime', array('null' => true));
        $schema->addIndex('PRIMARY', array('id'), 'primary');
        $schema->addIndex('idx_ai_turns_conversation_turn_unique', array('conversation_pk', 'turn_number'), 'unique');
        $schema->addIndex('idx_ai_turns_status', array('status'), 'key');
        $schema->addIndex('idx_ai_turns_conversation_pk', array('conversation_pk'), 'key');
        $schema->meta('engine', 'InnoDB');
        return $schema;
    }

    protected function buildAssetsSchema()
    {
        $schema = new Schema($this->table('assets'));
        $schema->add('id', 'bigint(20)', array('null' => false, 'auto_increment' => true));
        $schema->add('asset_id', 'varchar(100)', array('null' => false));
        $schema->add('kind', 'varchar(50)', array('null' => true));
        $schema->add('path', 'longtext', array('null' => true));
        $schema->add('filename', 'varchar(255)', array('null' => true));
        $schema->add('mime_type', 'varchar(191)', array('null' => true));
        $schema->add('size_bytes', 'bigint(20)', array('null' => true));
        $schema->add('sha256', 'varchar(128)', array('null' => true));
        $schema->add('source_url', 'longtext', array('null' => true));
        $schema->add('metadata', 'longtext', array('null' => true));
        $schema->add('created_at', 'datetime', array('null' => false));
        $schema->add('updated_at', 'datetime', array('null' => true));
        $schema->addIndex('PRIMARY', array('id'), 'primary');
        $schema->addIndex('idx_ai_assets_asset_id_unique', array('asset_id'), 'unique');
        $schema->addIndex('idx_ai_assets_sha256', array('sha256'), 'key');
        $schema->addIndex('idx_ai_assets_kind', array('kind'), 'key');
        $schema->meta('engine', 'InnoDB');
        return $schema;
    }

    protected function buildProviderAssetBindingsSchema()
    {
        $schema = new Schema($this->table('provider_asset_bindings'));
        $schema->add('id', 'bigint(20)', array('null' => false, 'auto_increment' => true));
        $schema->add('binding_id', 'varchar(100)', array('null' => true));
        $schema->add('asset_pk', 'bigint(20)', array('null' => false));
        $schema->add('provider', 'varchar(100)', array('null' => false));
        $schema->add('provider_asset_id', 'varchar(191)', array('null' => true));
        $schema->add('provider_uri', 'longtext', array('null' => true));
        $schema->add('purpose', 'varchar(100)', array('null' => true));
        $schema->add('status', 'varchar(100)', array('null' => true));
        $schema->add('mime_type', 'varchar(191)', array('null' => true));
        $schema->add('metadata', 'longtext', array('null' => true));
        $schema->add('raw_json', 'longtext', array('null' => true));
        $schema->add('created_at', 'datetime', array('null' => true));
        $schema->add('updated_at', 'datetime', array('null' => true));
        $schema->addIndex('PRIMARY', array('id'), 'primary');
        $schema->addIndex('idx_ai_provider_asset_bindings_binding_id_unique', array('binding_id'), 'unique');
        $schema->addIndex('idx_ai_provider_asset_bindings_asset_pk', array('asset_pk'), 'key');
        $schema->addIndex('idx_ai_provider_asset_bindings_provider', array('provider'), 'key');
        $schema->addIndex('idx_ai_provider_asset_bindings_asset_provider_purpose', array('asset_pk', 'provider', 'purpose'), 'key');
        $schema->meta('engine', 'InnoDB');
        return $schema;
    }

    protected function buildToolCallsSchema()
    {
        $schema = new Schema($this->table('tool_calls'));
        $schema->add('id', 'bigint(20)', array('null' => false, 'auto_increment' => true));
        $schema->add('turn_pk', 'bigint(20)', array('null' => false));
        $schema->add('tool_call_id', 'varchar(191)', array('null' => true));
        $schema->add('name', 'varchar(191)', array('null' => false));
        $schema->add('arguments_json', 'longtext', array('null' => true));
        $schema->add('result_json', 'longtext', array('null' => true));
        $schema->add('success', 'tinyint(1)', array('null' => true));
        $schema->add('status', 'varchar(100)', array('null' => true));
        $schema->add('sequence_number', 'int(11)', array('null' => false));
        $schema->add('metadata', 'longtext', array('null' => true));
        $schema->add('created_at', 'datetime', array('null' => false));
        $schema->add('updated_at', 'datetime', array('null' => true));
        $schema->addIndex('PRIMARY', array('id'), 'primary');
        $schema->addIndex('idx_ai_tool_calls_turn_pk', array('turn_pk'), 'key');
        $schema->addIndex('idx_ai_tool_calls_tool_call_id', array('tool_call_id'), 'key');
        $schema->addIndex('idx_ai_tool_calls_name', array('name'), 'key');
        $schema->addIndex('idx_ai_tool_calls_turn_sequence', array('turn_pk', 'sequence_number'), 'unique');
        $schema->meta('engine', 'InnoDB');
        return $schema;
    }

    protected function buildEmbeddingsSchema()
    {
        $schema = new Schema($this->table('embeddings'));
        $schema->add('id', 'bigint(20)', array('null' => false, 'auto_increment' => true));
        $schema->add('cache_key', 'varchar(191)', array('null' => false));
        $schema->add('provider', 'varchar(100)', array('null' => false));
        $schema->add('model', 'varchar(191)', array('null' => false));
        $schema->add('input_hash', 'varchar(191)', array('null' => false));
        $schema->add('inputs_json', 'longtext', array('null' => true));
        $schema->add('result_json', 'longtext', array('null' => false));
        $schema->add('metadata', 'longtext', array('null' => true));
        $schema->add('created_at', 'datetime', array('null' => false));
        $schema->add('updated_at', 'datetime', array('null' => true));
        $schema->add('expires_at', 'datetime', array('null' => true));
        $schema->addIndex('PRIMARY', array('id'), 'primary');
        $schema->addIndex('idx_ai_embeddings_cache_key_unique', array('cache_key'), 'unique');
        $schema->addIndex('idx_ai_embeddings_provider_model', array('provider', 'model'), 'key');
        $schema->addIndex('idx_ai_embeddings_input_hash', array('input_hash'), 'key');
        $schema->addIndex('idx_ai_embeddings_expires_at', array('expires_at'), 'key');
        $schema->meta('engine', 'InnoDB');
        return $schema;
    }

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