<?php

namespace onespace\core\behaviours;

use InvalidArgumentException;
use Yii;
use yii\base\Behavior;
use yii\db\ActiveQuery;
use yii\db\ActiveRecord;
use yii\db\AfterSaveEvent;
use yii\db\Connection;
use yii\db\Expression;
use yii\db\Query;
use yii\db\Transaction;

/**
 * @psalm-api
 * 
 * @property ActiveRecord $owner
 * @property ActiveRecord[] $ancestors
 * @property ActiveRecord[] $descendants
 * 
 * @template-extends Behavior<ActiveRecord>
 */
class TreeClosureBehaviour extends Behavior {
    public const string EVENT_INSERT = 'insert';
    public const string EVENT_UPDATE = 'update';
    public const string EVENT_DELETE = 'delete';

    /**
     * Whether or not the behaviour should run
     */
    public bool $enabled = true;

    /**
     * Whether or not this tree allows sharing items across branches
     */
    public bool $shareEnabled = false;

    /**
     * Name of the tree closure entity. Will be used in exchange name and within payload
     * @psalm-var class-string
     */
    public ?string $closureTableClass = null;

    /**
     * Instance of the tree closure entity. Will be used in exchange name and within payload
     */
    private ?ActiveRecord $closureTable = null;

    /**
     * Name of the primary key attribute in the entity table
     */
    public string $entityIdAttributeName = 'id';

    /**
     * Name of the parent ID attribute in the entity table
     */
    public string $entityParentIdAttributeName = 'parent_id';

    /**
     * Name of the ancestor column
     */
    public string $ancestorColumnName = 'ancestor_id';

    /**
     * Name of the descendant column
     */
    public string $descendantColumnName = 'descendant_id';

    /**
     * Name of the depth column
     */
    public string $depthColumnName = 'depth';

    /**
     * Name of the share column (if applicable)
     */
    public string $shareColumnName = 'share_id';

    /**
     * Name of the share table (if applicable)
     */
    public string $shareTableName = 'share';

    /**
     * Name of the column in the share table that stores the ID of shares themselves (if applicable)
     */
    public string $shareTableIdColumnName = 'id';

    /**
     * Name of the column in the share table that stores the entity ID of shares (if applicable)
     */
    public string $shareTableSharedColumnName = 'shared_entity_id';

    /**
     * Name of the column in the share table that stores the entity ID of containers (if applicable)
     */
    public string $shareTableContainerColumnName = 'container_entity_id';

    /**
     * @var Transaction|null $transaction
     */
    private $transaction = null;

    /**
     * Prep a transaction to cover the event, so that we can undo it if for some reason the event isn't emitted
     */
    public function prepareTransaction(): true {
        // if this is a standard yii db connection (not an azure one)
        if ($this->owner->getDb() instanceof Connection) {
            $this->transaction = $this->owner->getDb()->beginTransaction();
        }
        // have to return true to allow things to complete
        return true;
    }

    /**
     * List of events to run this for. Currently supports `BaseActiveRecord::EVENT_AFTER_INSERT`, `BaseActiveRecord::EVENT_AFTER_UPDATE`, and `BaseActiveRecord::EVENT_AFTER_DELETE`
     * @var array<self::EVENT_INSERT | self::EVENT_UPDATE | self::EVENT_DELETE>
     */
    public $events = [
        self::EVENT_INSERT,
        self::EVENT_UPDATE,
        self::EVENT_DELETE,
    ];
    /**
     * {@inheritdoc}
     */
    #[\Override]
    public function events() {
        $supportedEvents = [
            self::EVENT_INSERT => [ActiveRecord::EVENT_AFTER_INSERT => 'afterInsert', ActiveRecord::EVENT_BEFORE_INSERT => 'prepareTransaction'],
            self::EVENT_UPDATE => [ActiveRecord::EVENT_AFTER_UPDATE => 'afterUpdate', ActiveRecord::EVENT_BEFORE_UPDATE => 'prepareTransaction'],
            self::EVENT_DELETE => [ActiveRecord::EVENT_AFTER_DELETE => 'afterDelete', ActiveRecord::EVENT_BEFORE_DELETE => 'prepareTransaction'],
        ];

        // filter the supported events by the list provided
        $activeEvents = [];
        foreach ($this->events as $enabledEvent) {
            if (key_exists($enabledEvent, $supportedEvents)) {
                foreach ($supportedEvents[$enabledEvent] as $event => $action) {
                    $activeEvents[$event] = $action;
                }
            }
        }

        return $activeEvents;
    }

    /**
     * @inheritdoc
     * @param ActiveRecord $owner
     * @return void
     */
    #[\Override]
    public function attach($owner) {

        // make sure we're being attached to a compatible model
        /** @psalm-suppress RedundantConditionGivenDocblockType */
        if ($owner instanceof ActiveRecord) {
            parent::attach($owner);
        } else {
            throw new InvalidArgumentException("Tree Closure Behavior can only be attached to access records");
        }

        if ($this->closureTableClass != null) {
            /** @var \yii\db\ActiveRecord */
            $this->closureTable = new $this->closureTableClass();
        }
    }

    private function getDb(): Connection {
        return $this->owner->getDb();
    }

    /**
     * Append an item to the end of a branch
     *
     * @param int|string $itemId
     * @param int|string|null $branchId 
     *  ID of item to link to end of.
     * @param int|string|null $share
     * @return bool whether update succeeded or failed
     * @throws \yii\db\Exception
     */
    public function appendItemToTree(int|string $itemId, int|string|null $branchId, int|string|null $share = null): bool {
        if ($this->closureTable === null) {
            throw new \Exception("Closure table must be defined");
        }

        $columns = [
            $this->ancestorColumnName => $this->ancestorColumnName,
            $this->descendantColumnName => new Expression(strval($itemId)),
            $this->depthColumnName => new Expression($this->getDb()->quoteColumnName($this->depthColumnName) . '+1'),
        ];

        $baseRowColumns = [
            $this->ancestorColumnName => new Expression(strval($itemId)),
            $this->descendantColumnName => new Expression(strval($itemId)),
            $this->depthColumnName => new Expression(strval(0)),
        ];

        if ($this->shareEnabled) {
            $columns[$this->shareColumnName] = $this->shareColumnName;
            $baseRowColumns[$this->shareColumnName] = new Expression(strval($share));
        }
        $query = (new Query())->from($this->closureTable->tableName())
            ->select($columns)
            ->where([$this->descendantColumnName => $branchId])
            ->union(
                (new Query())->select($baseRowColumns),
                true
            );
        $command = $this->getDb()->createCommand()->insert(
            $this->closureTable->tableName(),
            $query
        );

        return $command->execute() > 0;
    }

    /**
     * Move an item to a different point in the tree
     */
    public function moveItemTo(int|string $itemId, int|string|null $parentId): bool {
        if($this->closureTable === null) {
            throw new \Exception("Closure table not found for model $this->closureTableClass");
        }

        $db = $this->getDb();
        if ($parentId === null) {
            $parentId = 'null';
        } else {
            $parentId = $db->quoteValue(strval($parentId));
        }

        // delete existing transactions
        try {
            $closureTableName = $db->quoteTableName($this->closureTable->tableName());
            $shareTableName = $db->quoteTableName($this->shareTableName);
            $ancestorColumn = $db->quoteColumnName($this->ancestorColumnName);
            $descendantColumn = $db->quoteColumnName($this->descendantColumnName);
            $depthColumn = $db->quoteColumnName($this->depthColumnName);
            $shareColumn = $db->quoteColumnName($this->shareColumnName);

            $entityId = $db->quoteValue(strval($itemId));



            $sql = "DELETE ct1 FROM $closureTableName ct1"
                . " INNER JOIN $closureTableName ct2 ON ct1.$descendantColumn = ct2.$descendantColumn "
                . (
                    $this->shareEnabled ?
                    "and (ct1.$shareColumn = ct2.$shareColumn or ct2.$shareColumn is null)"
                    :
                    ''
                )
                . " LEFT JOIN $closureTableName ct3 ON ct3.$ancestorColumn = ct2.$ancestorColumn"
                . " AND ct3.$descendantColumn = ct1.$ancestorColumn"
                . " WHERE ct2.$ancestorColumn = $entityId AND ct3.$ancestorColumn IS NULL;";

            $db->createCommand($sql)->execute();


            $sql = "INSERT IGNORE INTO $closureTableName ($ancestorColumn, $descendantColumn, $depthColumn"
                . (
                    $this->shareEnabled ?
                    ", $shareColumn"
                    :
                    ''
                )
                . ")"
                . " SELECT parents.$ancestorColumn, children.$descendantColumn, parents.$depthColumn + children.$depthColumn+1" . ($this->shareEnabled ? ", coalesce(parents.$shareColumn, children.$shareColumn)" : '')
                . " FROM $closureTableName parents"
                . " INNER JOIN $closureTableName children"
                . " WHERE children.$ancestorColumn = $entityId AND parents.$descendantColumn = $parentId"
                . (
                    $this->shareEnabled ?
                    " UNION"
                    . " SELECT parents.$ancestorColumn, children.$descendantColumn, parents.$depthColumn + children.$depthColumn+1, shares.id"
                    . " FROM $closureTableName parents"
                    . " INNER JOIN $closureTableName children"
                    . " INNER JOIN $shareTableName shares"
                    . " WHERE shares." . $db->quoteColumnName($this->shareTableSharedColumnName) . " = $entityId and children.$ancestorColumn = $entityId AND parents.$descendantColumn = shares." . $db->quoteColumnName($this->shareTableContainerColumnName) . " and parents.$shareColumn is null and children.$shareColumn is null;"
                    :
                    ';'
                );

            $db->createCommand($sql)->execute();

            if ($this->transaction != null) {
                $this->transaction->commit();
                $this->transaction = null;
            }
            return true;
        } catch (\Exception $e) {
            if ($this->transaction != null) {
                $this->transaction->rollBack();
                $this->transaction = null;
            }
            throw $e;
        }
    }

    /**
     * Remove an item from the tree
     */
    public function removeItemFromTree(int|string $itemId): bool {
        if ($this->closureTable === null) {
            throw new \Exception("Closure table must be defined");
        }

        $db = $this->getDb();

        // make sure there is nothing below this item in the tree
        $children = $this->closureTable::find()
            ->where([$this->ancestorColumnName => $itemId])
            ->andWhere(['>', $this->depthColumnName, 0])
            ->all();

        if ($children != null) {
            return false;
        }


        // delete existing transactions
        try {
            $closureTableName = $db->quoteTableName($this->closureTable->tableName());
            $ancestorColumn = $db->quoteColumnName($this->ancestorColumnName);
            $descendantColumn = $db->quoteColumnName($this->descendantColumnName);
            $shareColumn = $db->quoteColumnName($this->shareColumnName);

            $entityId = $db->quoteValue(strval($itemId));

            $sql = "DELETE ct1 FROM $closureTableName ct1"
                . " INNER JOIN $closureTableName ct2 ON ct1.$descendantColumn = ct2.$descendantColumn "
                . (
                    $this->shareEnabled ?
                    "and (ct1.$shareColumn = ct2.$shareColumn or ct2.$shareColumn is null)"
                    :
                    ''
                )
                . " LEFT JOIN $closureTableName ct3 ON ct3.$ancestorColumn = ct2.$ancestorColumn"
                . " AND ct3.$descendantColumn = ct1.$ancestorColumn"
                . " WHERE ct2.$ancestorColumn = $entityId AND ct3.$ancestorColumn IS NULL;";

            $db->createCommand($sql)->execute();

            if ($this->transaction != null) {
                $this->transaction->commit();
                $this->transaction = null;
            }
            return true;
        } catch (\Exception $e) {
            if ($this->transaction != null) {
                $this->transaction->rollBack();
                $this->transaction = null;
            }
            throw $e;
        }
    }

    /**
     * Get ancestors of this item
     *
     * @param int $layers Number of layers to look up the tree. If 0 or unset, will return all descendants
     * @param boolean $includeSelf Include this item in the list
     * @param boolean $canonicalOnly Whether to only include canonical items (ie, not results of shares if shares exist)
     *
     * @return ActiveQuery Query to get a list of entities of owner class via the closure table
     */
    public function getAncestors($layers = 0, $includeSelf = false, $canonicalOnly = false): ActiveQuery {
        if($this->closureTable === null) {
            throw new \Exception("Closure table not found for model $this->closureTableClass");
        }

        $query = $this->owner->find()
            ->join('INNER JOIN', $this->closureTable::tableName(), [
                'and',
                "{$this->descendantColumnName} = {$this->owner->{$this->entityIdAttributeName}}",
                "{$this->owner::tableName()}.{$this->entityIdAttributeName} = {$this->ancestorColumnName}",
            ])
            ->orderBy(['depth' => SORT_ASC]);
        // force the query to return multiple results
        $query->multiple = true;

        if ($layers != 0) {
            $query->andWhere(['<=', $this->depthColumnName, $layers]);
        }
        if (!$includeSelf) {
            $query->andWhere(['>', $this->depthColumnName, 0]);
        }

        if ($this->shareEnabled) {
            if ($canonicalOnly) {
                $query->andWhere([$this->shareColumnName => null]);
            }
        }
        Yii::info($query->createCommand()->rawSql, __METHOD__);
        return $query;
    }

    /**
     * Get descendants of this item
     *
     * @param int $layers Number of layers to look up the tree. If 0 or unset, will return all descendants
     * @param boolean $includeSelf Include this item in the list
     * @param boolean $canonicalOnly Whether to only include canonical items (ie, not results of shares if shares exist)
     *
     * @return ActiveQuery Query to get a list of entities of owner class via the closure table
     */
    public function getDescendants($layers = 0, $includeSelf = false, $canonicalOnly = false): ActiveQuery {
        if($this->closureTable === null) {
            throw new \Exception("Closure table not found for model $this->closureTableClass");
        }

        $query = $this->owner->find()
            ->join('INNER JOIN', $this->closureTable::tableName(), [
                'and',
                "{$this->ancestorColumnName} = {$this->owner->{$this->entityIdAttributeName}}",
                "{$this->owner::tableName()}.{$this->entityIdAttributeName} = {$this->descendantColumnName}",
            ])
            ->orderBy(['depth' => SORT_ASC]);
        // force the query to return multiple results
        $query->multiple = true;

        if ($layers != 0) {
            $query->andWhere(['<=', $this->depthColumnName, $layers]);
        }
        if (!$includeSelf) {
            $query->andWhere(['>', $this->depthColumnName, 0]);
        }

        if ($this->shareEnabled) {
            if ($canonicalOnly) {
                $query->andWhere([$this->shareColumnName => null]);
            }
        }
        // Yii::info($query->createCommand()->rawSql, __METHOD__);
        return $query;
    }

    /**
     * Emit a creation event to the server
     */
    public function afterInsert(AfterSaveEvent $_event): void {
        if ($this->appendItemToTree($this->owner->{$this->entityIdAttributeName}, $this->owner->{$this->entityParentIdAttributeName})) {
            $this->transaction?->commit();
        }
        $this->transaction = null;
    }

    /**
     * Emit an update event to the server
     */
    public function afterUpdate(AfterSaveEvent $event): void {
        if (array_key_exists($this->entityParentIdAttributeName, $event->changedAttributes)) {
            if ($this->moveItemTo($this->owner->{$this->entityIdAttributeName}, $this->owner->{$this->entityParentIdAttributeName})) {
                $this->transaction?->commit();
            }
        } else {
            $this->transaction?->commit();
        }
        $this->transaction = null;
    }

    /**
     * Emit a deletion
     */
    public function afterDelete(): void {
        // move any children of this item to its parent
        /** @var ActiveRecord $child */
        foreach ($this->getDescendants(1, false)->all() as $child) {
            $child->{$this->entityParentIdAttributeName} = $this->owner->{$this->entityParentIdAttributeName};
            $child->save();
        }

        if ($this->removeItemFromTree($this->owner->{$this->entityIdAttributeName}) && $this->transaction != null) {
            $this->transaction->commit();
        }
        $this->transaction = null;
    }

    /**
     * To be used in `beforeSave` or `beforeDelete` in the case of a validation failure.
     * If you are returning false, clear the transaction if it exists.
     */
    public function clearTreeTransaction(): void {
        if ($this->transaction !== null && $this->transaction->isActive) {
            $this->transaction->rollBack();
        }
        $this->transaction = null;
    }
}
