<?php

declare(strict_types=1);

namespace FiloBlu\Refilo\Remote;

use Generator;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\DB\Ddl\Table;
use Magento\Framework\DB\Ddl\Trigger;
use Magento\Framework\DB\Ddl\TriggerFactory;
use Zend_Db_Exception;
use Zend_Db_Expr;
use function sprintf;


/**
 * Class BasesSqlDeletedItemFinder
 *
 * Implements DeletedItemFinderInterface to track and manage deleted item IDs
 * using a change log table and database triggers. This class creates a change
 * log table for deleted items, sets up an AFTER DELETE trigger on the main table,
 * and provides methods to retrieve, clean, and discover deleted IDs.
 *
 * @package FiloBlu\Refilo\Remote
 */
class BasesSqlDeletedItemFinder implements DeletedItemFinderInterface
{
    /**
     * @var string
     */
    private $table;

    /**
     * @var string
     */
    private $idColumn;

    /**
     * @var ResourceConnection
     */
    private $resourceConnection;

    /**
     * @var int
     */
    private $chunkSize;

    /**
     * @var TriggerFactory
     */
    private $triggerFactory;

    /**
     * @param ResourceConnection $resourceConnection
     * @param TriggerFactory $triggerFactory
     * @param string $table
     * @param string $idColumn
     * @param int $chunkSize
     */
    public function __construct(
        ResourceConnection $resourceConnection,
        TriggerFactory     $triggerFactory,
        string             $table,
        string             $idColumn,
        int                $chunkSize = 1000
    )
    {
        $this->table = $table;
        $this->idColumn = $idColumn;
        $this->resourceConnection = $resourceConnection;
        $this->chunkSize = $chunkSize;
        $this->triggerFactory = $triggerFactory;
    }

    /**
     * @param $ids
     * @return array
     */
    public function getDeletedIds($ids): array
    {
        $connection = $this->resourceConnection->getConnection();

        $select = $connection->select()->from(
            $connection->getTableName($this->getChangeLogTable()), ['id' => $this->idColumn]);

        if (!empty($ids)) {
            $select->where(sprintf('%s IN (?)', $this->idColumn), $ids);
        }

        return $connection->fetchCol($select);
    }

    /**
     * @return string
     */
    public function getChangeLogTable()
    {
        return $this->table . '_' . $this->idColumn . '_deleted_items_cl';
    }

    /**
     * @return Table
     * @throws Zend_Db_Exception
     */
    public function createTemporaryTable(): Table
    {
        $connection = $this->resourceConnection->getConnection();
        $tableName = $this->table . '_' . str_replace('.', '_', uniqid('tmp', true));
        $table = $connection->newTable($tableName);
        $table->addColumn(
            $this->idColumn,
            Table::TYPE_INTEGER,
            10,
            ['unsigned' => true, 'nullable' => false, 'primary' => true, 'auto_increment' => true],
            'Entity ID'
        );

        $table->setOption('type', 'memory');
        $connection->createTemporaryTable($table);
        return $table;
    }


    /**
     * Installs the change log table and associated trigger for tracking deleted items.
     *
     * Creates a new table to log deleted item IDs and sets up an AFTER DELETE trigger
     * on the main table to insert deleted IDs into the change log table.
     *
     * @return void
     * @throws \Zend_Db_Exception
     */
    public function install()
    {
        $connection = $this->resourceConnection->getConnection();
        $tableName = $this->resourceConnection->getTableName($this->table);

        $clTable = $this->getChangeLogTable();
        $clTableName = $this->resourceConnection->getTableName($clTable);

        if ($connection->isTableExists($clTableName)) {
            return;
        }

        $tableDescription = $connection->describeTable($tableName);
        $columnInfo = $connection->getColumnCreateByDescribe($tableDescription[$this->idColumn]);
        unset($columnInfo['options']['identity'], $columnInfo['options']['primary']);

        $table = $this->resourceConnection->getConnection()->newTable($clTableName)
            ->addColumn(
                'version_id',
                Table::TYPE_INTEGER,
                10,
                ['unsigned' => true, 'nullable' => false, 'primary' => true, 'auto_increment' => true],
                'Version ID'
            )
            ->addColumn(
                $columnInfo['name'],
                $columnInfo['type'],
                $columnInfo['length'],
                $columnInfo['options'],
                $columnInfo['comment']
            );

        $connection->createTable($table);

        $triggerName = 'trg_refilo_' . $this->table . '_after_delete';
        $trigger = $this->triggerFactory->create()
            ->setName($triggerName)
            ->setTime(Trigger::TIME_AFTER)
            ->setEvent(Trigger::EVENT_DELETE)
            ->setTable($tableName);

        $trigger->addStatement(
            sprintf('INSERT IGNORE INTO `%s` (`%s`) VALUES (OLD.`%s`);', $clTableName, $this->idColumn, $this->idColumn)
        );

        $connection->dropTrigger($trigger->getName());
        $connection->createTrigger($trigger);
    }


    /**
     * Removes tracking for the specified IDs from the change log table.
     *
     * Deletes rows from the change log table where the ID column matches any of the provided IDs.
     *
     * @param array $ids List of IDs to remove from the change log table.
     * @return void
     */
    public function clean($ids)
    {
        $where = '';

        if (!empty($ids)) {
            $where = [$this->idColumn . ' IN (?)' => $ids];
        }

        $this->resourceConnection->getConnection()->delete($this->getChangeLogTable(), $where);
    }

    /**
     * Scans the main table for missing IDs in sequential chunks, identifying gaps (i.e., deleted items)
     * in the primary key sequence. Returns an array of IDs that are missing from the main table,
     * which are considered deleted. Uses chunking for efficiency on large tables.
     *
     * @return Generator List of deleted (missing) IDs.
     */
    public function discover(): Generator
    {
        $connection = $this->resourceConnection->getConnection();
        $mainTable = $connection->getTableName($this->table);
        $idColumn = $this->idColumn;

        $row = $connection->fetchRow(
            $connection->select()->from($mainTable, [
                'max' => new Zend_Db_Expr("MAX($idColumn)"),
                'total' => new Zend_Db_Expr("COUNT($idColumn)")
            ])
        );

        $max = max((int)$row['max'], (int)$row['total']);
        $remainder = $max % $this->chunkSize;
        $times = ($max - $remainder) / $this->chunkSize;

        if ($remainder > 0) {
            $times++;
        }

        $preparedStatement = $connection->select()->from($mainTable, [$idColumn])->where("$idColumn >= :start")->where("$idColumn <= :end");

        foreach (range(1, $times) as $i) {

            $start = 1 + (($i - 1) * $this->chunkSize);
            $end = $i * $this->chunkSize;

            $total = $connection->fetchCol($preparedStatement, [':start' => $start, ':end' => $end]);

            if (count($total) == $this->chunkSize) {
                continue;
            }
            foreach (array_diff(range($start, $end), $total) as $w) {
                yield $w;
            }
        }


    }
}
