<?php
/**
 * Resolve virtual columns for JSON fields
 *
 * @package agenda
 * @subpackage build
 *
 * @var array $options
 * @var xPDOObject $object
 */

$success = false;

if ($object->xpdo) {
    /** @var xPDO $modx */
    $modx =& $object->xpdo;

    if (!function_exists('addVirtualColumn')) {
        function addVirtualColumn($modx, $classname, $jsonname, $fieldname, $fieldtype)
        {
            $tableName = $modx->getTableName($classname);
            $tableName = str_replace('`', '', $tableName);
            $dbname = $modx->getOption('dbname');

            $c = $modx->prepare('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = :dbName AND table_name = :tableName');
            $c->bindParam(':dbName', $dbname);
            $c->bindParam(':tableName', $tableName);
            $c->execute();

            $availableColumns = $c->fetchAll(PDO::FETCH_COLUMN, 0);
            $availableColumns = array_flip($availableColumns);

            if (in_array($fieldname, $availableColumns)) {
                $modx->exec('DROP INDEX `' . $fieldname . '` ON ' . $modx->getTableName($classname) . ';');
                $modx->exec('ALTER TABLE ' . $modx->getTableName($classname) . ' DROP COLUMN `' . $fieldname . '`;');
                $modx->log(xPDO::LOG_LEVEL_INFO, ' -- altered virtual column to ' . $modx->getTableName($classname) . ': ' . $fieldname);
            } else {
                $modx->log(xPDO::LOG_LEVEL_INFO, ' -- added virtual column to ' . $modx->getTableName($classname) . ': ' . $fieldname);
            }
            $modx->exec('ALTER TABLE ' . $modx->getTableName($classname) . ' ADD COLUMN `' . $fieldname . '` ' . $fieldtype . ' GENERATED ALWAYS AS (`' . $jsonname . '` ->> \'$.' . $fieldname . '\') VIRTUAL;');
            if (!in_array($fieldtype, ['TEXT'])) {
                $modx->exec('CREATE INDEX `' . $fieldname . '` ON ' . $modx->getTableName($classname) . '(`' . $fieldname . '`);');
            }
        }
    }

    if (!function_exists('removeVirtualColumn')) {
        function removeVirtualColumn($modx, $classname, $fieldname)
        {
            $tableName = $modx->getTableName($classname);
            $tableName = str_replace('`', '', $tableName);
            $dbname = $modx->getOption('dbname');

            $c = $modx->prepare('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = :dbName AND table_name = :tableName');
            $c->bindParam(':dbName', $dbname);
            $c->bindParam(':tableName', $tableName);
            $c->execute();

            $availableColumns = $c->fetchAll(PDO::FETCH_COLUMN, 0);
            $availableColumns = array_flip($availableColumns);

            if (in_array($fieldname, $availableColumns)) {
                $modx->exec('DROP INDEX `' . $fieldname . '` ON ' . $modx->getTableName($classname) . ';');
                $modx->exec('ALTER TABLE ' . $modx->getTableName($classname) . ' DROP COLUMN `' . $fieldname . '`;');
                $modx->log(xPDO::LOG_LEVEL_INFO, ' -- removed virtual column from ' . $modx->getTableName($classname) . ': ' . $fieldname);
            }
        }
    }

    $corePath = $modx->getOption('agenda.core_path', null, $modx->getOption('core_path') . 'components/agenda/');
    /** @var Agenda $agenda */
    $agenda = $modx->getService('agenda', 'Agenda', $corePath . 'model/agenda/', array(
        'core_path' => $corePath
    ));

    switch ($options[xPDOTransport::PACKAGE_ACTION]) {
        case xPDOTransport::ACTION_INSTALL:
        case xPDOTransport::ACTION_UPGRADE:
            addVirtualColumn($modx, 'AgendaEvents', 'extended', 'teaser', $fieldtype = 'TEXT');
            addVirtualColumn($modx, 'AgendaEventDates', 'extended', 'foreignId', $fieldtype = 'VARCHAR(10)');
            addVirtualColumn($modx, 'AgendaEventDates', 'extended', 'highlight', $fieldtype = 'VARCHAR(5)');
            addVirtualColumn($modx, 'AgendaEventDates', 'extended', 'community', $fieldtype = 'VARCHAR(5)');

            $success = true;
            break;
        case xPDOTransport::ACTION_UNINSTALL:
            removeVirtualColumn($modx, 'AgendaEvents', 'teaser');
            removeVirtualColumn($modx, 'AgendaEventDates', 'foreignId');
            removeVirtualColumn($modx, 'AgendaEventDates', 'highlight');
            removeVirtualColumn($modx, 'AgendaEventDates', 'community');

            $success = true;
            break;
    }
}
return $success;