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

use boru\ai\Asset\Asset;
use boru\ai\Asset\AssetStoreInterface;
use boru\ai\Asset\ProviderAssetBinding;

class DbAssetStore implements AssetStoreInterface
{
    /** @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 saveAsset(Asset $asset)
    {
        $db = $this->connection()->db();
        $table = $this->table('assets');
        $now = $this->dbNow();

        if (!$asset->id()) {
            $asset->id('asset_' . uniqid());
        }

        $existingId = $this->findAssetRowId($asset->id());

        if ($existingId !== null) {
            $sql = 'update `' . $table . '` set `kind`=?, `path`=?, `filename`=?, `mime_type`=?, `size_bytes`=?, `sha256`=?, `source_url`=?, `metadata`=?, `updated_at`=? where `id`=?';
            $this->connection()->run($sql, array(
                $asset->kind(),
                $asset->path(),
                $asset->filename(),
                $asset->mimeType(),
                $asset->size(),
                $asset->sha256(),
                $asset->sourceUrl(),
                json_encode($asset->metadata()),
                $now,
                $existingId,
            ));
        } else {
            $sql = 'insert into `' . $table . '` (`asset_id`, `kind`, `path`, `filename`, `mime_type`, `size_bytes`, `sha256`, `source_url`, `metadata`, `created_at`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
            $this->connection()->run($sql, array(
                $asset->id(),
                $asset->kind(),
                $asset->path(),
                $asset->filename(),
                $asset->mimeType(),
                $asset->size(),
                $asset->sha256(),
                $asset->sourceUrl(),
                json_encode($asset->metadata()),
                $now,
                $now,
            ));
        }

        return $asset;
    }

    public function loadAsset($assetId)
    {
        $db = $this->connection()->db();
        $sql = 'select * from `' . $this->table('assets') . '` where `asset_id`=? limit 1';
        $result = $this->connection()->run($sql, array($assetId));
        $row = $db->next($result);

        if (!$row) {
            return false;
        }

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

    public function findAssetBySha256($sha256)
    {
        $db = $this->connection()->db();
        $sql = 'select * from `' . $this->table('assets') . '` where `sha256`=? limit 1';
        $result = $this->connection()->run($sql, array($sha256));
        $row = $db->next($result);

        if (!$row) {
            return false;
        }

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

    public function saveBinding(ProviderAssetBinding $binding)
    {
        $db = $this->connection()->db();
        $table = $this->table('provider_asset_bindings');
        $assetRowId = $this->findAssetRowId($binding->assetId());
        $now = $this->dbNow();

        if (!$binding->id()) {
            $binding->id('binding_' . uniqid());
        }

        if ($assetRowId === null) {
            throw new \RuntimeException('Asset must be saved before saving a provider binding');
        }

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

        $existingId = $this->findBindingRowId($binding->id());

        if ($existingId !== null) {
            $sql = 'update `' . $table . '` set `asset_pk`=?, `provider`=?, `provider_asset_id`=?, `provider_uri`=?, `purpose`=?, `status`=?, `mime_type`=?, `metadata`=?, `raw_json`=?, `created_at`=?, `updated_at`=? where `id`=?';
            $this->connection()->run($sql, array(
                $assetRowId,
                $binding->provider(),
                $binding->providerAssetId(),
                $binding->providerUri(),
                $binding->purpose(),
                $binding->status(),
                $binding->mimeType(),
                json_encode($binding->metadata()),
                is_scalar($binding->raw()) || $binding->raw() === null ? $binding->raw() : json_encode($binding->raw()),
                $createdAt,
                $updatedAt,
                $existingId,
            ));
        } else {
            $sql = 'insert into `' . $table . '` (`binding_id`, `asset_pk`, `provider`, `provider_asset_id`, `provider_uri`, `purpose`, `status`, `mime_type`, `metadata`, `raw_json`, `created_at`, `updated_at`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
            $this->connection()->run($sql, array(
                $binding->id(),
                $assetRowId,
                $binding->provider(),
                $binding->providerAssetId(),
                $binding->providerUri(),
                $binding->purpose(),
                $binding->status(),
                $binding->mimeType(),
                json_encode($binding->metadata()),
                is_scalar($binding->raw()) || $binding->raw() === null ? $binding->raw() : json_encode($binding->raw()),
                $createdAt,
                $updatedAt,
            ));
        }

        return $binding;
    }

    public function findBinding($assetId, $provider, $purpose = null)
    {
        $db = $this->connection()->db();
        $sql = 'select b.* from `' . $this->table('provider_asset_bindings') . '` b inner join `' . $this->table('assets') . '` a on a.id = b.asset_pk where a.asset_id=? and b.provider=?';
        $params = array($assetId, $provider);

        if ($purpose !== null) {
            $sql .= ' and b.purpose=?';
            $params[] = $purpose;
        }

        $sql .= ' limit 1';
        $result = $this->connection()->run($sql, $params);
        $row = $db->next($result);

        if (!$row) {
            return false;
        }

        return $this->hydrateBinding($row, $assetId);
    }

    public function listBindings($assetId)
    {
        $db = $this->connection()->db();
        $sql = 'select b.* from `' . $this->table('provider_asset_bindings') . '` b inner join `' . $this->table('assets') . '` a on a.id = b.asset_pk where a.asset_id=?';
        $result = $this->connection()->run($sql, array($assetId));

        $bindings = array();
        while ($row = $db->next($result)) {
            $bindings[] = $this->hydrateBinding($row, $assetId);
        }

        return $bindings;
    }

    protected function findAssetRowId($assetId)
    {
        if (!$assetId) {
            return null;
        }

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

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

    protected function findBindingRowId($bindingId)
    {
        if (!$bindingId) {
            return null;
        }

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

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

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

    protected function hydrateAsset($row)
    {
        $asset = new Asset();
        $asset->id($row->get('asset_id'));
        $asset->kind($row->get('kind'));
        $asset->path($row->get('path'));
        $asset->filename($row->get('filename'));
        $asset->mimeType($row->get('mime_type'));
        $asset->size($row->get('size_bytes'));
        $asset->sha256($row->get('sha256'));
        $asset->sourceUrl($row->get('source_url'));

        $metadata = $row->get('metadata');
        if (is_string($metadata) && $metadata !== '') {
            $decoded = json_decode($metadata, true);
            if (is_array($decoded)) {
                $asset->metadata($decoded);
            }
        }

        return $asset;
    }

    protected function hydrateBinding($row, $assetId)
    {
        $binding = new ProviderAssetBinding();
        $binding->id($row->get('binding_id'));
        $binding->assetId($assetId);
        $binding->provider($row->get('provider'));
        $binding->providerAssetId($row->get('provider_asset_id'));
        $binding->providerUri($row->get('provider_uri'));
        $binding->purpose($row->get('purpose'));
        $binding->status($row->get('status'));
        $binding->mimeType($row->get('mime_type'));
        $binding->raw($row->get('raw_json'));
        $binding->createdAt($row->get('created_at'));
        $binding->updatedAt($row->get('updated_at'));

        $metadata = $row->get('metadata');
        if (is_string($metadata) && $metadata !== '') {
            $decoded = json_decode($metadata, true);
            if (is_array($decoded)) {
                $binding->metadata($decoded);
            }
        }

        return $binding;
    }

    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);
    }
}
