<?php
namespace App\Controllers;

use App\Services\AuthService;
use App\Database\Connection;
use App\Database\Migration;
use App\Database\Schema;
use PDO;

/**
 * Simple migration controller to support the legacy "run_migration" button
 * in the admin users view. In the new MySQL-based architecture, the schema
 * is already correct, so this endpoint mostly reports success and can be
 * extended in the future if needed.
 */
class MigrationController extends ApiController
{
    private $authService;
    private $db;

    public function __construct(AuthService $authService, Connection $db)
    {
        $this->authService = $authService;
        $this->db = $db;
    }

    public function preview()
    {
        if (!$this->authService->isAdmin()) {
            $this->errorResponse('فقط مدیر می‌تواند وضعیت migration را بررسی کند');
        }

        $snapshot = $this->getSchemaSnapshot();
        $preview = $this->buildPreviewReport($snapshot);
        $this->successResponse($preview, 'پیش‌نمایش migration آماده است');
    }

    /**
     * Run idempotent migrations and return a before/after diff report.
     */
    public function run()
    {
        if (!$this->authService->isAdmin()) {
            $this->errorResponse('فقط مدیر می‌تواند migration را اجرا کند');
        }
        $before = $this->getSchemaSnapshot();
        $started = microtime(true);

        $migration = new Migration($this->db);
        $migration->run();

        $after = $this->getSchemaSnapshot();
        $report = $this->buildMigrationResultReport($before, $after, (int)round((microtime(true) - $started) * 1000));

        $this->successResponse([
            'report' => $report,
        ], 'Migration اجرا شد');
    }

    private function getSchemaSnapshot(): array
    {
        $pdo = $this->db->getPdo();
        $tablesStmt = $pdo->query("SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()");
        $tables = [];
        foreach (($tablesStmt ? $tablesStmt->fetchAll(PDO::FETCH_COLUMN) : []) as $tableName) {
            $tables[(string)$tableName] = ['columns' => [], 'indexes' => [], 'index_columns' => []];
        }
        ksort($tables);

        if (!empty($tables)) {
            $columnsStmt = $pdo->query("SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = DATABASE()");
            foreach (($columnsStmt ? $columnsStmt->fetchAll(PDO::FETCH_ASSOC) : []) as $row) {
                $table = (string)$row['table_name'];
                if (!isset($tables[$table])) {
                    continue;
                }
                $tables[$table]['columns'][] = (string)$row['column_name'];
            }
            $indexesStmt = $pdo->query("
                SELECT table_name, index_name, column_name, seq_in_index
                FROM information_schema.statistics
                WHERE table_schema = DATABASE()
                ORDER BY table_name, index_name, seq_in_index
            ");
            foreach (($indexesStmt ? $indexesStmt->fetchAll(PDO::FETCH_ASSOC) : []) as $row) {
                $table = (string)$row['table_name'];
                $index = (string)$row['index_name'];
                $column = (string)($row['column_name'] ?? '');
                if (!isset($tables[$table])) {
                    continue;
                }
                if ($index === 'PRIMARY') {
                    continue;
                }
                $tables[$table]['indexes'][] = $index;
                if ($column !== '') {
                    if (!isset($tables[$table]['index_columns'][$index])) {
                        $tables[$table]['index_columns'][$index] = [];
                    }
                    $tables[$table]['index_columns'][$index][] = $column;
                }
            }
            foreach ($tables as &$tableMeta) {
                $tableMeta['columns'] = array_values(array_unique($tableMeta['columns']));
                sort($tableMeta['columns']);
                $tableMeta['indexes'] = array_values(array_unique($tableMeta['indexes']));
                sort($tableMeta['indexes']);
                foreach (($tableMeta['index_columns'] ?? []) as &$indexCols) {
                    $indexCols = array_values($indexCols);
                }
                unset($indexCols);
            }
            unset($tableMeta);
        }

        return ['tables' => $tables];
    }

    private function buildPreviewReport(array $snapshot): array
    {
        $existingTables = array_keys($snapshot['tables'] ?? []);
        $expectedTables = $this->extractExpectedTables();
        $missingTables = array_values(array_diff($expectedTables, $existingTables));

        $monitoredColumns = $this->getMonitoredColumns();
        $missingColumns = [];
        foreach ($monitoredColumns as $table => $columns) {
            $present = $snapshot['tables'][$table]['columns'] ?? [];
            foreach ($columns as $column) {
                if (!in_array($column, $present, true)) {
                    $missingColumns[] = "{$table}.{$column}";
                }
            }
        }

        $monitoredIndexes = $this->getMonitoredIndexes();
        $missingIndexes = [];
        foreach ($monitoredIndexes as $table => $indexes) {
            $presentNames = $snapshot['tables'][$table]['indexes'] ?? [];
            $presentIndexColumns = $snapshot['tables'][$table]['index_columns'] ?? [];
            foreach ($indexes as $indexSpec) {
                $indexName = is_array($indexSpec) ? (string)($indexSpec['name'] ?? '') : (string)$indexSpec;
                $expectedColumns = is_array($indexSpec) ? array_values((array)($indexSpec['columns'] ?? [])) : [];
                if ($indexName === '') {
                    continue;
                }
                if (in_array($indexName, $presentNames, true)) {
                    continue;
                }
                if (!empty($expectedColumns) && $this->hasEquivalentIndexColumns($presentIndexColumns, $expectedColumns)) {
                    continue;
                }
                $missingIndexes[] = "{$table}.{$indexName}";
            }
        }

        $pendingItems = [];
        foreach ($missingTables as $table) {
            $pendingItems[] = "ایجاد جدول: {$table}";
        }
        foreach ($missingColumns as $item) {
            $pendingItems[] = "افزودن ستون: {$item}";
        }
        foreach ($missingIndexes as $item) {
            $pendingItems[] = "ایجاد ایندکس: {$item}";
        }

        return [
            'pending_summary' => [
                'missing_tables' => count($missingTables),
                'missing_columns' => count($missingColumns),
                'missing_indexes' => count($missingIndexes),
            ],
            'pending_items' => $pendingItems,
            'notes' => [
                'پیش‌نمایش فقط موارد پایش‌شده را نشان می‌دهد و اکنون ایندکس‌های معادل (با نام متفاوت ولی ستون‌های یکسان) را موجود در نظر می‌گیرد.',
                'Migration فعلی idempotent است و علاوه بر موارد بالا ممکن است seed یا همگام‌سازی پرداخت‌ها را نیز بررسی/اجرا کند.',
            ],
        ];
    }

    private function hasEquivalentIndexColumns(array $presentIndexColumns, array $expectedColumns): bool
    {
        $expected = array_values($expectedColumns);
        foreach ($presentIndexColumns as $cols) {
            if (array_values((array)$cols) === $expected) {
                return true;
            }
        }
        return false;
    }

    private function buildMigrationResultReport(array $before, array $after, int $durationMs): array
    {
        $beforeTables = array_keys($before['tables'] ?? []);
        $afterTables = array_keys($after['tables'] ?? []);
        $createdTables = array_values(array_diff($afterTables, $beforeTables));

        $addedColumns = [];
        $addedIndexes = [];
        foreach (($after['tables'] ?? []) as $table => $afterMeta) {
            $beforeMeta = $before['tables'][$table] ?? ['columns' => [], 'indexes' => []];
            foreach (array_values(array_diff($afterMeta['columns'] ?? [], $beforeMeta['columns'] ?? [])) as $column) {
                $addedColumns[] = "{$table}.{$column}";
            }
            foreach (array_values(array_diff($afterMeta['indexes'] ?? [], $beforeMeta['indexes'] ?? [])) as $index) {
                $addedIndexes[] = "{$table}.{$index}";
            }
        }

        sort($createdTables);
        sort($addedColumns);
        sort($addedIndexes);

        return [
            'duration_ms' => $durationMs,
            'created_tables' => $createdTables,
            'added_columns' => $addedColumns,
            'added_indexes' => $addedIndexes,
            'summary' => [
                'created_tables' => count($createdTables),
                'added_columns' => count($addedColumns),
                'added_indexes' => count($addedIndexes),
            ],
        ];
    }

    private function extractExpectedTables(): array
    {
        $tables = [];
        foreach (Schema::getCreateTableStatements() as $sql) {
            if (preg_match('/CREATE TABLE IF NOT EXISTS\s+`?([a-zA-Z0-9_]+)`?/i', $sql, $matches)) {
                $tables[] = $matches[1];
            }
        }
        $tables = array_values(array_unique($tables));
        sort($tables);
        return $tables;
    }

    private function getMonitoredColumns(): array
    {
        return [
            'users' => ['display_name', 'password', 'role', 'is_active'],
            'persons' => ['secondary_mobile_phone', 'sale_status', 'register_time_jalali'],
            'deals' => ['payments', 'is_legacy_didar', 'pipeline_stage', 'payable_amount'],
            'transactions' => ['payment_date', 'status', 'amount'],
            'activities' => ['owner_didar_id', 'register_date', 'duration'],
            'sync_log' => ['http_code', 'response_data'],
            'audit_logs' => ['actor_name', 'actor_ip'],
            'field_configurations' => ['help_text', 'module_name', 'delete_allow_roles'],
        ];
    }

    private function getMonitoredIndexes(): array
    {
        return [
            'deals' => [
                ['name' => 'idx_deals_owner', 'columns' => ['owner_didar_id']],
                ['name' => 'idx_deals_register_time', 'columns' => ['register_time']],
                ['name' => 'idx_deals_status', 'columns' => ['status']],
                ['name' => 'idx_deals_contact_didar', 'columns' => ['contact_didar_id']],
                ['name' => 'idx_deals_pipeline', 'columns' => ['pipeline_stage']],
                ['name' => 'idx_deals_person_id', 'columns' => ['person_id']],
                ['name' => 'idx_deals_legacy', 'columns' => ['is_legacy_didar']],
            ],
            'persons' => [
                ['name' => 'idx_persons_didar_contact_id', 'columns' => ['didar_contact_id']],
            ],
            'activities' => [
                ['name' => 'idx_activities_register_date', 'columns' => ['register_date']],
                ['name' => 'idx_due_date', 'columns' => ['due_date']],
                ['name' => 'idx_is_scheduled', 'columns' => ['is_scheduled']],
            ],
        ];
    }
}


