<?php

declare(strict_types=1);

namespace PortalPeople\schema\services;

use Yii;
use yii\web\UploadedFile;

/**
 * SchemaService – DB schema synchronisation.
 *
 * Reads JSON schema definition files and applies ADD/REMOVE column operations
 * and CREATE TABLE operations to the target database connection.
 *
 * SECURITY NOTE: This service can create tables and add/remove columns in the
 * database. Access must be restricted to trusted admin users only.
 */
class SchemaService
{
    /**
     * Upload a schema JSON file and merge it with the existing schema, then
     * trigger a schema check and queue Gii model regeneration for each table.
     *
     * @return mixed
     */
    public function uploadschema(): mixed
    {
        $console    = Yii::getAlias('@api') . '/../';
        $connection = (string) Yii::$app->request->post('connection', '');
        $current    = $console . $connection . '.json';

        $upload = UploadedFile::getInstanceByName('schema');

        $newcontents     = json_decode(file_get_contents($upload->tempName) ?: '{}', true) ?? [];
        $currentcontents = file_exists($current)
            ? (json_decode(file_get_contents($current) ?: '{}', true) ?? [])
            : [];

        $contents = is_array($currentcontents) ? array_merge($currentcontents, $newcontents) : $newcontents;

        file_put_contents($console . $connection . '.json', json_encode($contents));

        $out   = [];
        $out[] = $this->checkschema($connection);

        foreach ($contents as $sqltable => $data) {
            $out[] = Yii::$app->queue->jobname('Check Schema')->push(
                new \console\jobs\system\GiiModels(['sqltable' => $sqltable, 'connection' => $connection])
            );
        }

        return $out;
    }

    /**
     * Check the current DB schema against the stored JSON definition and apply
     * any pending ADD/REMOVE column or CREATE TABLE operations.
     *
     * @param string|null $connection Database connection component name (may be
     *                                passed directly or read from request POST).
     * @return array<mixed>
     */
    public function checkschema(?string $connection = null): array
    {
        Yii::$app->response->format = 'json';

        if ($connection === null) {
            $connection = (string) Yii::$app->request->post('connection', '');
        }

        $console = Yii::getAlias('@api') . '/../';
        $tables  = json_decode(file_get_contents($console . $connection . '.json') ?: '{}', true) ?? [];
        $array   = [];

        foreach ($tables as $sqltable => $dataa) {
            $table = Yii::$app->$connection->schema->getTableSchema($sqltable);

            if (is_object($table)) {
                foreach ($dataa as $type => $fielddata) {
                    foreach ($fielddata as $key => $row) {
                        if ($type === 'remove') {
                            if (isset($table->columns[$row['field']])) {
                                $data = Yii::$app->$connection->createCommand()
                                    ->dropColumn($sqltable, $row['field'])
                                    ->execute();
                                if ($data === 0) {
                                    $array['removed'][$sqltable][$type][] = $row;
                                } else {
                                    $array['error'][$sqltable][$type][] = $row;
                                }
                            } else {
                                $array['exists'][$sqltable][$type][] = $row;
                            }
                        } else {
                            if (!array_key_exists($row['field'], $table->columns)) {
                                if (preg_match('/\d{' . strlen($row['field']) . '}/', $row['field'])) {
                                    break;
                                }
                                Yii::$app->$connection->createCommand()
                                    ->addColumn($sqltable, $row['field'], $type)
                                    ->execute();
                                Yii::$app->$connection->createCommand()
                                    ->addCommentOnColumn($sqltable, $row['field'], $row['comment'])
                                    ->execute();
                                $data = Yii::$app->$connection->createCommand()
                                    ->addColumn($sqltable, $row['field'], $type)
                                    ->execute();
                                if ($data === 0) {
                                    $array['added'][$sqltable][$type][] = $row['field'];
                                } else {
                                    $array['error'][$sqltable][$type][] = $row['field'];
                                }
                            } else {
                                $array['exists'][$sqltable][$type][] = $row['field'];
                            }
                        }
                    }
                }
            } elseif (!$table) {
                $out = [];
                $pk  = [];

                foreach ($dataa as $type => $field) {
                    foreach ($field as $b => $data) {
                        if (isset($data['pk'])) {
                            $pk[] = $data['field'];
                        }
                        $out[$data['field']] = $type . " COMMENT '" . $data['comment'] . "'";
                    }
                }

                $createtable = Yii::$app->$connection->createCommand()
                    ->createTable($sqltable, $out)
                    ->execute();

                if ($createtable === 0) {
                    Yii::$app->$connection->createCommand()
                        ->addPrimaryKey($sqltable . '_pk', $sqltable, $pk)
                        ->execute();
                }

                $array[] = ['Table Added'];
                $array[] = ['fields' => $out];
            }
        }

        return $array;
    }
}
