<?php
namespace App\Core;

use App\Controllers\MaintenanceFtthController;
use App\Controllers\PlanningController;
use App\Controllers\PublicMobileApiController;
use App\Core\ProspectClientService;
use PDO;
use RecursiveDirectoryIterator;
use RecursiveIteratorIterator;
use SplFileInfo;

class DatabaseAlignmentManager
{
    public static function inspect(PDO $pdo): array
    {
        $expectedSchema = self::expectedSchemaWithSources();
        $expectedMap = [];
        foreach ($expectedSchema as $tableName => $definition) {
            $expectedMap[$tableName] = $definition['sources'];
        }
        $expectedTables = array_keys($expectedMap);
        sort($expectedTables);

        $existingTables = self::existingTables($pdo);
        $existingLookup = array_fill_keys($existingTables, true);
        $existingColumns = self::existingColumns($pdo);

        $missingTables = [];
        $presentTables = [];
        $missingColumnsByTable = [];
        $missingColumnsCount = 0;

        foreach ($expectedTables as $tableName) {
            $row = [
                'name' => $tableName,
                'sources' => $expectedMap[$tableName],
            ];

            if (isset($existingLookup[$tableName])) {
                $presentTables[] = $row;

                $expectedColumns = $expectedSchema[$tableName]['columns'] ?? [];
                $actualColumns = $existingColumns[$tableName] ?? [];
                $actualLookup = array_fill_keys($actualColumns, true);
                $missingColumns = [];

                foreach ($expectedColumns as $columnName => $columnDefinition) {
                    if (isset($actualLookup[$columnName])) {
                        continue;
                    }

                    $missingColumns[] = [
                        'name' => $columnName,
                        'sources' => $columnDefinition['sources'] ?? [],
                    ];
                }

                if (!empty($missingColumns)) {
                    $missingColumnsByTable[$tableName] = $missingColumns;
                    $missingColumnsCount += count($missingColumns);
                }
                continue;
            }

            $missingTables[] = $row;
        }

        return [
            'expected_tables' => $expectedTables,
            'existing_tables' => $existingTables,
            'missing_tables' => $missingTables,
            'present_tables' => $presentTables,
            'expected_count' => count($expectedTables),
            'existing_count' => count($existingTables),
            'missing_count' => count($missingTables),
            'present_count' => count($presentTables),
            'missing_columns_by_table' => $missingColumnsByTable,
            'missing_columns_count' => $missingColumnsCount,
            'missing_columns_tables_count' => count($missingColumnsByTable),
            'reference_sources' => self::referenceSources(),
            'generated_at' => date('Y-m-d H:i:s'),
        ];
    }

    public static function applyUpdate(PDO $pdo): array
    {
        $before = self::inspect($pdo);
        $executedSteps = [];

        self::executeSqlDumpCreateStatements($pdo, $executedSteps);
        self::executeNamedAlignmentScripts($pdo, $executedSteps);
        self::ensureSettingsTable($pdo);
        $executedSteps[] = 'settings';

        Notifier::ensureDevicesTable($pdo);
        Notifier::ensureTables($pdo);
        Notifier::ensurePublicClientNotificationsTable($pdo);
        $executedSteps[] = 'notifications';

        self::ensurePasswordResetsTable($pdo);
        self::ensureUsersSecurityColumns($pdo);
        $executedSteps[] = 'security';

        MobileLicenseManager::ensureSchema($pdo);
        ModuleManager::ensureSchema($pdo);
        RaccordementOtbService::ensureSchema($pdo);
        ProspectClientService::ensureSchema($pdo);
        self::ensureMobileApiTables($pdo);
        self::ensureReferentialTables($pdo);
        $executedSteps[] = 'services';

        new PlanningController();
        new PublicMobileApiController();
        new MaintenanceFtthController();
        $executedSteps[] = 'controllers';

        $after = self::inspect($pdo);

        $beforeMissingTables = array_map(static fn(array $row): string => (string)($row['name'] ?? ''), $before['missing_tables'] ?? []);
        $afterMissingTables = array_map(static fn(array $row): string => (string)($row['name'] ?? ''), $after['missing_tables'] ?? []);
        $resolvedTables = array_values(array_diff($beforeMissingTables, $afterMissingTables));

        $beforeMissingColumns = self::flattenMissingColumns($before['missing_columns_by_table'] ?? []);
        $afterMissingColumns = self::flattenMissingColumns($after['missing_columns_by_table'] ?? []);
        $resolvedColumns = array_values(array_diff($beforeMissingColumns, $afterMissingColumns));

        return [
            'before' => $before,
            'after' => $after,
            'resolved_count' => max(0, (int)($before['missing_count'] ?? 0) - (int)($after['missing_count'] ?? 0)),
            'remaining_count' => (int)($after['missing_count'] ?? 0),
            'resolved_columns_count' => max(0, (int)($before['missing_columns_count'] ?? 0) - (int)($after['missing_columns_count'] ?? 0)),
            'remaining_columns_count' => (int)($after['missing_columns_count'] ?? 0),
            'resolved_tables' => $resolvedTables,
            'resolved_columns' => $resolvedColumns,
            'executed_steps' => array_values(array_unique($executedSteps)),
        ];
    }

    private static function existingTables(PDO $pdo): array
    {
        $stmt = $pdo->query('SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_NAME ASC');
        $tables = $stmt ? ($stmt->fetchAll(PDO::FETCH_COLUMN) ?: []) : [];

        return array_values(array_map('strval', $tables));
    }

    private static function expectedSchemaWithSources(): array
    {
        $root = dirname(__DIR__, 2);
        $expected = [];

        foreach (self::referenceSources() as $sourcePath) {
            if (!is_file($sourcePath)) {
                continue;
            }

            $content = @file_get_contents($sourcePath);
            if ($content === false || $content === '') {
                continue;
            }

            preg_match_all('/CREATE\s+TABLE(?:\s+IF\s+NOT\s+EXISTS)?\s+`?([a-zA-Z0-9_]+)`?\s*\((.*?)\)\s*(?:ENGINE|DEFAULT|COMMENT|;)/is', $content, $matches, PREG_SET_ORDER);
            foreach ($matches as $match) {
                $normalized = strtolower(trim((string)($match[1] ?? '')));
                if ($normalized === '' || self::shouldIgnoreTable($normalized)) {
                    continue;
                }

                if (!isset($expected[$normalized])) {
                    $expected[$normalized] = [
                        'sources' => [],
                        'columns' => [],
                    ];
                }

                $relative = str_replace($root . DIRECTORY_SEPARATOR, '', $sourcePath);
                $relative = str_replace('\\', '/', $relative);
                if (!in_array($relative, $expected[$normalized]['sources'], true)) {
                    $expected[$normalized]['sources'][] = $relative;
                }

                foreach (self::extractColumnNames((string)($match[2] ?? '')) as $columnName) {
                    if (!isset($expected[$normalized]['columns'][$columnName])) {
                        $expected[$normalized]['columns'][$columnName] = ['sources' => []];
                    }
                    if (!in_array($relative, $expected[$normalized]['columns'][$columnName]['sources'], true)) {
                        $expected[$normalized]['columns'][$columnName]['sources'][] = $relative;
                    }
                }
            }
        }

        $expected = self::canonicalizeExpectedSchema($expected);
        ksort($expected);
        return $expected;
    }

    private static function executeSqlDumpCreateStatements(PDO $pdo, array &$executedSteps): void
    {
        $root = dirname(__DIR__, 2);
        $dumpPath = $root . DIRECTORY_SEPARATOR . 'bd_insuite_backbone.sql';
        if (!is_file($dumpPath)) {
            return;
        }

        $content = @file_get_contents($dumpPath);
        if ($content === false || $content === '') {
            return;
        }

        preg_match_all('/CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\s+.*?;/is', $content, $matches);
        $statements = $matches[0] ?? [];
        if (empty($statements)) {
            return;
        }

        try {
            $pdo->exec('SET FOREIGN_KEY_CHECKS=0');
        } catch (\Throwable $e) {
        }

        foreach ($statements as $statement) {
            try {
                $pdo->exec($statement);
                $executedSteps[] = 'dump';
            } catch (\Throwable $e) {
            }
        }

        try {
            $pdo->exec('SET FOREIGN_KEY_CHECKS=1');
        } catch (\Throwable $e) {
        }
    }

    private static function executeNamedAlignmentScripts(PDO $pdo, array &$executedSteps): void
    {
        $root = dirname(__DIR__, 2);
        $scriptPaths = [
            $root . DIRECTORY_SEPARATOR . 'database' . DIRECTORY_SEPARATOR . 'align_c2685816c_dump_to_local_schema_production_safe.sql',
        ];

        foreach ($scriptPaths as $scriptPath) {
            if (!is_file($scriptPath)) {
                continue;
            }

            $content = @file_get_contents($scriptPath);
            if ($content === false || trim($content) === '') {
                continue;
            }

            foreach (self::splitSqlStatements($content) as $statement) {
                try {
                    $pdo->exec($statement);
                } catch (\Throwable $e) {
                }
            }

            $executedSteps[] = 'database-alignment-rules';
        }
    }

    private static function ensureSettingsTable(PDO $pdo): void
    {
        $pdo->exec("CREATE TABLE IF NOT EXISTS settings (
            `key` VARCHAR(64) NOT NULL,
            `value` TEXT NULL,
            PRIMARY KEY (`key`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    private static function ensureMobileApiTables(PDO $pdo): void
    {
        $pdo->exec("CREATE TABLE IF NOT EXISTS api_tokens (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            token_hash CHAR(64) NOT NULL,
            name VARCHAR(60) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_used_at DATETIME NULL,
            expires_at DATETIME NULL,
            revoked_at DATETIME NULL,
            UNIQUE KEY uniq_token_hash (token_hash),
            INDEX(user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS mobile_device_trials (
            id INT AUTO_INCREMENT PRIMARY KEY,
            device_id VARCHAR(191) NOT NULL,
            device_fingerprint VARCHAR(191) NULL,
            platform VARCHAR(30) NULL,
            model VARCHAR(120) NULL,
            first_seen_at DATETIME NOT NULL,
            trial_started_at DATETIME NOT NULL,
            last_seen_at DATETIME NOT NULL,
            UNIQUE KEY uniq_device_id (device_id),
            KEY idx_device_fingerprint (device_fingerprint)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    private static function ensureReferentialTables(PDO $pdo): void
    {
        $pdo->exec("CREATE TABLE IF NOT EXISTS maintenance_types (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(120) NOT NULL,
            active TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS sites (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            description TEXT NULL,
            active TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS incident_causes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            category VARCHAR(120) NULL,
            name VARCHAR(180) NOT NULL,
            description TEXT NULL,
            active TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS priority_levels (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(120) NOT NULL,
            color VARCHAR(20) NULL,
            sort_order INT NOT NULL DEFAULT 0,
            description TEXT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS equipments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(180) NOT NULL,
            category VARCHAR(100) NULL,
            description TEXT NULL,
            active TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS liaisons (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            description TEXT NULL,
            site_a_name VARCHAR(255) NULL,
            site_b_name VARCHAR(255) NULL,
            site_a_latitude DECIMAL(10,7) NULL,
            site_a_longitude DECIMAL(10,7) NULL,
            site_b_latitude DECIMAL(10,7) NULL,
            site_b_longitude DECIMAL(10,7) NULL,
            active TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY name (name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $pdo->exec("CREATE TABLE IF NOT EXISTS locations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            client_id INT NULL,
            name VARCHAR(255) NOT NULL,
            description TEXT NULL,
            address TEXT NULL,
            city VARCHAR(100) NULL,
            postal_code VARCHAR(20) NULL,
            active TINYINT(1) DEFAULT 1,
            latitude DECIMAL(10,8) NULL,
            longitude DECIMAL(11,8) NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    private static function ensurePasswordResetsTable(PDO $pdo): void
    {
        $pdo->exec("CREATE TABLE IF NOT EXISTS password_resets (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            token_hash CHAR(64) NOT NULL,
            expires_at DATETIME NOT NULL,
            used_at DATETIME NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX(user_id),
            INDEX(expires_at),
            UNIQUE KEY uniq_user_token (user_id, token_hash)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    private static function ensureUsersSecurityColumns(PDO $pdo): void
    {
        foreach ([
            'totp_secret' => 'ALTER TABLE users ADD COLUMN totp_secret VARCHAR(64) NULL AFTER role_key',
            'last_login' => 'ALTER TABLE users ADD COLUMN last_login TIMESTAMP NULL DEFAULT NULL',
        ] as $columnName => $sql) {
            try {
                $stmt = $pdo->prepare('SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1');
                $stmt->execute(['users', $columnName]);
                if (!$stmt->fetchColumn()) {
                    $pdo->exec($sql);
                }
            } catch (\Throwable $e) {
            }
        }
    }

    private static function referenceSources(): array
    {
        $root = dirname(__DIR__, 2);
        $paths = [];

        $sqlDump = $root . DIRECTORY_SEPARATOR . 'bd_insuite_backbone.sql';
        if (is_file($sqlDump)) {
            $paths[] = $sqlDump;
        }

        foreach ([
            $root . DIRECTORY_SEPARATOR . 'app',
            $root . DIRECTORY_SEPARATOR . 'database',
        ] as $directory) {
            if (!is_dir($directory)) {
                continue;
            }

            $iterator = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($directory));
            foreach ($iterator as $file) {
                if (!$file instanceof SplFileInfo || !$file->isFile()) {
                    continue;
                }

                $extension = strtolower((string)$file->getExtension());
                if (!in_array($extension, ['php', 'sql'], true)) {
                    continue;
                }

                $paths[] = $file->getPathname();
            }
        }

        $paths = array_values(array_unique($paths));
        sort($paths);
        return $paths;
    }

    private static function shouldIgnoreTable(string $tableName): bool
    {
        if ($tableName === 'settings_migrate_tmp') {
            return true;
        }

        return str_ends_with($tableName, '_backup') || str_ends_with($tableName, '_tmp');
    }

    private static function canonicalizeExpectedSchema(array $expected): array
    {
        $ignoreColumns = [
            'clients' => ['contact_email'],
            'incident_statuses' => ['is_system'],
            'locations' => ['description'],
            'maintenance_types' => ['description'],
            'priority_levels' => ['level', 'active'],
            'settings' => ['id', 'key_name', 'description', 'type', 'created_at', 'updated_at'],
            'sites' => ['code', 'location_id'],
            'users' => ['password_hash'],
        ];

        foreach ($ignoreColumns as $tableName => $columns) {
            if (!isset($expected[$tableName]['columns'])) {
                continue;
            }

            foreach ($columns as $columnName) {
                unset($expected[$tableName]['columns'][$columnName]);
            }
        }

        return $expected;
    }

    private static function existingColumns(PDO $pdo): array
    {
        $stmt = $pdo->query('SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_NAME ASC, ORDINAL_POSITION ASC');
        $rows = $stmt ? ($stmt->fetchAll(PDO::FETCH_ASSOC) ?: []) : [];
        $result = [];

        foreach ($rows as $row) {
            $tableName = strtolower(trim((string)($row['TABLE_NAME'] ?? '')));
            $columnName = strtolower(trim((string)($row['COLUMN_NAME'] ?? '')));
            if ($tableName === '' || $columnName === '') {
                continue;
            }
            if (!isset($result[$tableName])) {
                $result[$tableName] = [];
            }
            $result[$tableName][] = $columnName;
        }

        return $result;
    }

    private static function extractColumnNames(string $tableBody): array
    {
        $columns = [];
        foreach (preg_split('/\R/', $tableBody) ?: [] as $line) {
            $trimmed = trim($line);
            if ($trimmed === '') {
                continue;
            }
            $trimmed = rtrim($trimmed, ',');
            if (preg_match('/^`([a-zA-Z0-9_]+)`\s+/i', $trimmed, $match)) {
                $columns[] = strtolower($match[1]);
                continue;
            }
            if (preg_match('/^([a-zA-Z0-9_]+)\s+/i', $trimmed, $match)) {
                $keyword = strtoupper($match[1]);
                if (in_array($keyword, ['PRIMARY', 'UNIQUE', 'KEY', 'INDEX', 'CONSTRAINT', 'FOREIGN'], true)) {
                    continue;
                }
                $columns[] = strtolower($match[1]);
            }
        }

        return array_values(array_unique($columns));
    }

    private static function flattenMissingColumns(array $missingColumnsByTable): array
    {
        $flat = [];
        foreach ($missingColumnsByTable as $tableName => $columns) {
            foreach ($columns as $column) {
                $name = trim((string)($column['name'] ?? ''));
                if ($name === '') {
                    continue;
                }
                $flat[] = strtolower((string)$tableName) . '.' . strtolower($name);
            }
        }

        sort($flat);
        return $flat;
    }

    private static function splitSqlStatements(string $sql): array
    {
        $statements = [];
        $buffer = '';
        $inSingleQuote = false;
        $inDoubleQuote = false;
        $inBacktick = false;
        $length = strlen($sql);

        for ($index = 0; $index < $length; $index++) {
            $char = $sql[$index];
            $next = $index + 1 < $length ? $sql[$index + 1] : '';

            if (!$inSingleQuote && !$inDoubleQuote && !$inBacktick) {
                if ($char === '-' && $next === '-') {
                    while ($index < $length && $sql[$index] !== "\n") {
                        $index++;
                    }
                    continue;
                }

                if ($char === '#') {
                    while ($index < $length && $sql[$index] !== "\n") {
                        $index++;
                    }
                    continue;
                }

                if ($char === '/' && $next === '*') {
                    $index += 2;
                    while ($index < $length - 1 && !($sql[$index] === '*' && $sql[$index + 1] === '/')) {
                        $index++;
                    }
                    $index++;
                    continue;
                }
            }

            if ($char === "'" && !$inDoubleQuote && !$inBacktick) {
                $escaped = $index > 0 && $sql[$index - 1] === '\\';
                if (!$escaped) {
                    $inSingleQuote = !$inSingleQuote;
                }
            } elseif ($char === '"' && !$inSingleQuote && !$inBacktick) {
                $escaped = $index > 0 && $sql[$index - 1] === '\\';
                if (!$escaped) {
                    $inDoubleQuote = !$inDoubleQuote;
                }
            } elseif ($char === '`' && !$inSingleQuote && !$inDoubleQuote) {
                $inBacktick = !$inBacktick;
            }

            if ($char === ';' && !$inSingleQuote && !$inDoubleQuote && !$inBacktick) {
                $statement = trim($buffer);
                if ($statement !== '') {
                    $statements[] = $statement;
                }
                $buffer = '';
                continue;
            }

            $buffer .= $char;
        }

        $statement = trim($buffer);
        if ($statement !== '') {
            $statements[] = $statement;
        }

        return $statements;
    }
}