<?php

declare(strict_types=1);

namespace App\Modules\Developer;

/**
 * MirrorVerifierService — Supabase Mirror Integrity Checker
 *
 * Verifies that the Supabase mirror matches the MySQL schema.
 * Checks: table count, column count, missing tables, missing columns,
 * missing indexes, missing foreign keys, datatype compatibility.
 *
 * Returns a structured health report with status: healthy / warning / error.
 *
 * All operations are read-only — nothing is modified.
 *
 * @package Inveetaire
 * @ref     DX002B
 */
class MirrorVerifierService
{
    private SchemaReaderService $schemaReader;
    private DdlGeneratorService $ddlGenerator;

    public function __construct()
    {
        $this->schemaReader = new SchemaReaderService();
        $this->ddlGenerator = new DdlGeneratorService();
    }

    /**
     * Run full mirror verification.
     *
     * @return array{
     *   verified_at: string,
     *   status: 'healthy'|'warning'|'error'|'unconfigured',
     *   status_label: string,
     *   mysql_tables: int,
     *   supabase_tables: int,
     *   missing_tables: list<string>,
     *   extra_tables: list<string>,
     *   tables_checked: int,
     *   column_issues: array,
     *   type_issues: array,
     *   index_issues: array,
     *   issues: list<string>,
     *   warnings: list<string>,
     *   management_api_available: bool
     * }
     */
    public function verify(): array
    {
        if (!$this->schemaReader->hasManagementApiAccess()) {
            return [
                'verified_at'              => date('Y-m-d H:i:s'),
                'status'                   => 'unconfigured',
                'status_label'             => 'Management API Not Configured',
                'mysql_tables'             => 0,
                'supabase_tables'          => 0,
                'missing_tables'           => [],
                'extra_tables'             => [],
                'tables_checked'           => 0,
                'column_issues'            => [],
                'type_issues'              => [],
                'index_issues'             => [],
                'issues'                   => ['SUPABASE_ACCESS_TOKEN is not set. Configure it to enable mirror verification.'],
                'warnings'                 => [],
                'management_api_available' => false,
            ];
        }

        $mysqlSchema    = $this->schemaReader->getMysqlFullSchema();
        $supabaseSchema = $this->schemaReader->getSupabaseFullSchema();

        $mysqlTables    = array_keys($mysqlSchema);
        $supabaseTables = array_keys($supabaseSchema);

        $missingTables = array_values(array_diff($mysqlTables, $supabaseTables));
        $extraTables   = array_values(array_diff($supabaseTables, $mysqlTables));

        $columnIssues = [];
        $typeIssues   = [];
        $indexIssues  = [];
        $warnings     = [];
        $errors       = [];

        foreach (array_intersect($mysqlTables, $supabaseTables) as $table) {
            $mysqlCols    = $mysqlSchema[$table]['columns']  ?? [];
            $supabaseCols = $supabaseSchema[$table]['columns'] ?? [];

            // Missing columns
            foreach (array_keys($mysqlCols) as $col) {
                if (!isset($supabaseCols[$col])) {
                    $columnIssues[] = "{$table}.{$col} is missing in Supabase";
                    $errors[] = "Column {$table}.{$col} missing in Supabase";
                }
            }

            // Type compatibility
            foreach ($mysqlCols as $col => $mysqlCol) {
                if (!isset($supabaseCols[$col])) continue;

                $expectedPg = $this->ddlGenerator->mapType(
                    $mysqlCol['column_type'] ?? '',
                    $mysqlCol['data_type']   ?? '',
                    $mysqlCol['extra']       ?? ''
                );
                $actualPg = $supabaseCols[$col]['data_type'] ?? '';

                if (!$this->typesCompatible($expectedPg, $actualPg)) {
                    $typeIssues[] = "{$table}.{$col}: expected ~{$expectedPg}, found {$actualPg}";
                    $warnings[]   = "Type mismatch {$table}.{$col} (MySQL→PG: {$expectedPg}, Supabase: {$actualPg})";
                }
            }

            // Index check (warning only)
            $mysqlIndexes    = $mysqlSchema[$table]['indexes']    ?? [];
            $supabaseIndexes = $supabaseSchema[$table]['indexes'] ?? [];
            foreach (array_keys($mysqlIndexes) as $idxName) {
                $pgIdxName = 'idx_' . $table . '_' . implode('_', $mysqlIndexes[$idxName]['columns']);
                if (!isset($supabaseIndexes[$pgIdxName])) {
                    $indexIssues[] = "Index {$idxName} on {$table} missing in Supabase";
                    $warnings[]    = "Index missing: {$pgIdxName} on {$table}";
                }
            }
        }

        // Determine status
        $hasErrors   = !empty($errors) || !empty($missingTables);
        $hasWarnings = !empty($warnings) || !empty($extraTables);

        if ($hasErrors) {
            $status      = 'error';
            $statusLabel = 'Mirror Error — Synchronization Required';
        } elseif ($hasWarnings) {
            $status      = 'warning';
            $statusLabel = 'Schema Drift Detected';
        } else {
            $status      = 'healthy';
            $statusLabel = 'Mirror Healthy';
        }

        return [
            'verified_at'              => date('Y-m-d H:i:s'),
            'status'                   => $status,
            'status_label'             => $statusLabel,
            'mysql_tables'             => count($mysqlTables),
            'supabase_tables'          => count($supabaseTables),
            'missing_tables'           => $missingTables,
            'extra_tables'             => $extraTables,
            'tables_checked'           => count(array_intersect($mysqlTables, $supabaseTables)),
            'column_issues'            => $columnIssues,
            'type_issues'              => $typeIssues,
            'index_issues'             => $indexIssues,
            'issues'                   => $errors,
            'warnings'                 => $warnings,
            'management_api_available' => true,
        ];
    }

    /**
     * Loose type compatibility check between expected PG type and actual PG type.
     */
    private function typesCompatible(string $expected, string $actual): bool
    {
        $e = strtolower(preg_replace('/\s+without time zone/', '', $expected));
        $e = strtolower(preg_replace('/\s+with time zone/', 'tz', $e));
        $e = strtok($e, ' (');
        $a = strtolower($actual);

        $aliases = [
            'bigint'    => ['bigint','int8'],
            'integer'   => ['integer','int4','int'],
            'smallint'  => ['smallint','int2'],
            'boolean'   => ['boolean','bool'],
            'text'      => ['text','character varying','varchar'],
            'jsonb'     => ['jsonb','json'],
            'timestamp' => ['timestamp','timestamp without time zone'],
            'timestamptz'=> ['timestamptz','timestamp with time zone'],
            'numeric'   => ['numeric','decimal'],
            'real'      => ['real','float4'],
            'bytea'     => ['bytea'],
        ];

        foreach ($aliases as $base => $variants) {
            if ((in_array($e, $variants, true) || $e === $base) &&
                (in_array($a, $variants, true) || $a === $base)) {
                return true;
            }
        }
        return $e === $a;
    }
}
