<?php

declare(strict_types=1);

namespace App\Modules\Developer;

use App\Core\Database;

/**
 * SchemaReaderService — Schema Introspection Foundation (Extended DX002B)
 *
 * DX002A: Table list, basic column reading, presence-only comparison.
 * DX002B: Full schema comparison (columns, types, nullable, defaults, PKs, FKs, indexes).
 *         Supabase schema reading via Management API (if PAT configured).
 *
 * @package Inveetaire
 * @ref     DX002A, DX002B
 */
class SchemaReaderService
{
    private SupabaseService $supabase;

    /** Management API base URL */
    private string $mgmtApi;
    /** Personal Access Token */
    private string $accessToken;
    /** Project ref */
    private string $projectRef;
    /** HTTP timeout */
    private int $timeout;

    public function __construct()
    {
        $this->supabase     = new SupabaseService();
        $this->mgmtApi      = (string) config('supabase.management_api_url', 'https://api.supabase.com/v1');
        $this->accessToken  = (string) config('supabase.access_token', '');
        $this->projectRef   = (string) config('supabase.project_ref', '');
        $this->timeout      = (int)   config('supabase.timeout', 8);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // MySQL — Table List (DX002A compatible)
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Retrieve all tables with stats from the MySQL database.
     *
     * @return list<array{table_name:string,engine:string,table_rows:int,data_length_kb:float,index_length_kb:float,create_time:string|null}>
     */
    public function getMysqlTables(): array
    {
        try {
            $pdo = Database::getConnection();
            $stmt = $pdo->prepare(
                "SELECT TABLE_NAME AS table_name, ENGINE AS engine,
                        COALESCE(TABLE_ROWS,0) AS table_rows,
                        ROUND(COALESCE(DATA_LENGTH,0)/1024,2) AS data_length_kb,
                        ROUND(COALESCE(INDEX_LENGTH,0)/1024,2) AS index_length_kb,
                        CREATE_TIME
                 FROM information_schema.tables
                 WHERE table_schema=DATABASE() ORDER BY TABLE_NAME ASC"
            );
            $stmt->execute();
            $rows = $stmt->fetchAll();
            return array_map(static fn(array $r) => [
                'table_name'      => $r['table_name'],
                'engine'          => $r['engine'] ?? 'Unknown',
                'table_rows'      => (int)$r['table_rows'],
                'data_length_kb'  => (float)$r['data_length_kb'],
                'index_length_kb' => (float)$r['index_length_kb'],
                'create_time'     => $r['CREATE_TIME'] ?? null,
            ], $rows);
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * Retrieve full column definitions for a single MySQL table (DX002A compatible).
     *
     * @return list<array{column_name:string,data_type:string,column_type:string,is_nullable:string,column_default:string|null,column_key:string,extra:string}>
     */
    public function getMysqlTableDetail(string $tableName): array
    {
        try {
            $pdo  = Database::getConnection();
            $stmt = $pdo->prepare(
                "SELECT COLUMN_NAME AS column_name, DATA_TYPE AS data_type,
                        COLUMN_TYPE AS column_type, IS_NULLABLE AS is_nullable,
                        COLUMN_DEFAULT AS column_default, COLUMN_KEY AS column_key,
                        EXTRA AS extra
                 FROM information_schema.columns
                 WHERE table_schema=DATABASE() AND TABLE_NAME=?
                 ORDER BY ORDINAL_POSITION ASC"
            );
            $stmt->execute([$tableName]);
            return $stmt->fetchAll() ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    // ─────────────────────────────────────────────────────────────────────────
    // MySQL — Full Schema (DX002B)
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Read the full MySQL schema: tables + columns + PKs + FKs + indexes.
     * Used by the comparison engine and DDL generator.
     *
     * @return array<string, array{columns:array, primary_keys:list<string>, foreign_keys:array, indexes:array}>
     */
    public function getMysqlFullSchema(): array
    {
        try {
            $pdo    = Database::getConnection();
            $schema = [];

            // 1. Tables + columns
            $stmt = $pdo->prepare(
                "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE,
                        IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY, EXTRA, ORDINAL_POSITION
                 FROM information_schema.columns
                 WHERE table_schema=DATABASE()
                 ORDER BY TABLE_NAME, ORDINAL_POSITION"
            );
            $stmt->execute();
            foreach ($stmt->fetchAll() as $row) {
                $t = $row['TABLE_NAME'];
                if (!isset($schema[$t])) {
                    $schema[$t] = ['columns' => [], 'primary_keys' => [], 'foreign_keys' => [], 'indexes' => []];
                }
                $schema[$t]['columns'][$row['COLUMN_NAME']] = [
                    'data_type'      => $row['DATA_TYPE'],
                    'column_type'    => $row['COLUMN_TYPE'],
                    'is_nullable'    => $row['IS_NULLABLE'],
                    'column_default' => $row['COLUMN_DEFAULT'],
                    'column_key'     => $row['COLUMN_KEY'],
                    'extra'          => $row['EXTRA'],
                    'position'       => (int)$row['ORDINAL_POSITION'],
                ];
            }

            // 2. Primary keys
            $pkStmt = $pdo->prepare(
                "SELECT TABLE_NAME, COLUMN_NAME
                 FROM information_schema.key_column_usage
                 WHERE table_schema=DATABASE() AND CONSTRAINT_NAME='PRIMARY'
                 ORDER BY TABLE_NAME, ORDINAL_POSITION"
            );
            $pkStmt->execute();
            foreach ($pkStmt->fetchAll() as $row) {
                if (isset($schema[$row['TABLE_NAME']])) {
                    $schema[$row['TABLE_NAME']]['primary_keys'][] = $row['COLUMN_NAME'];
                }
            }

            // 3. Foreign keys
            $fkStmt = $pdo->prepare(
                "SELECT kcu.TABLE_NAME, kcu.COLUMN_NAME, kcu.CONSTRAINT_NAME,
                        kcu.REFERENCED_TABLE_NAME, kcu.REFERENCED_COLUMN_NAME,
                        rc.DELETE_RULE, rc.UPDATE_RULE
                 FROM information_schema.key_column_usage kcu
                 JOIN information_schema.referential_constraints rc
                   ON kcu.CONSTRAINT_NAME=rc.CONSTRAINT_NAME AND kcu.table_schema=rc.CONSTRAINT_SCHEMA
                 WHERE kcu.table_schema=DATABASE() AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
                 ORDER BY kcu.TABLE_NAME, kcu.CONSTRAINT_NAME"
            );
            $fkStmt->execute();
            foreach ($fkStmt->fetchAll() as $row) {
                if (isset($schema[$row['TABLE_NAME']])) {
                    $schema[$row['TABLE_NAME']]['foreign_keys'][$row['CONSTRAINT_NAME']] = [
                        'column'            => $row['COLUMN_NAME'],
                        'ref_table'         => $row['REFERENCED_TABLE_NAME'],
                        'ref_column'        => $row['REFERENCED_COLUMN_NAME'],
                        'on_delete'         => $row['DELETE_RULE'],
                        'on_update'         => $row['UPDATE_RULE'],
                    ];
                }
            }

            // 4. Indexes (non-PK)
            $idxStmt = $pdo->prepare(
                "SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, COLUMN_NAME, SEQ_IN_INDEX
                 FROM information_schema.statistics
                 WHERE table_schema=DATABASE() AND INDEX_NAME != 'PRIMARY'
                 ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX"
            );
            $idxStmt->execute();
            foreach ($idxStmt->fetchAll() as $row) {
                if (isset($schema[$row['TABLE_NAME']])) {
                    $iname = $row['INDEX_NAME'];
                    if (!isset($schema[$row['TABLE_NAME']]['indexes'][$iname])) {
                        $schema[$row['TABLE_NAME']]['indexes'][$iname] = [
                            'unique'   => !(bool)$row['NON_UNIQUE'],
                            'columns'  => [],
                        ];
                    }
                    $schema[$row['TABLE_NAME']]['indexes'][$iname]['columns'][] = $row['COLUMN_NAME'];
                }
            }

            return $schema;
        } catch (\Throwable $e) {
            return [];
        }
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Supabase — Schema Reading (DX002B)
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Whether the Management API PAT is configured.
     */
    public function hasManagementApiAccess(): bool
    {
        return $this->accessToken !== '' && $this->projectRef !== '';
    }

    /**
     * Get table list from Supabase via Management API.
     * Falls back to empty array if not configured.
     *
     * @return list<string>
     */
    public function getSupabaseTables(): array
    {
        if (!$this->hasManagementApiAccess()) {
            return [];
        }

        $sql = "SELECT table_name FROM information_schema.tables
                WHERE table_schema='public' AND table_type='BASE TABLE'
                ORDER BY table_name";

        $result = $this->executeMgmtQuery($sql);
        if ($result === null || isset($result['error'])) {
            return [];
        }

        return array_column($result, 'table_name');
    }

    /**
     * Read Supabase public schema in the same structure as getMysqlFullSchema().
     *
     * @return array<string, array{columns:array, primary_keys:list<string>, foreign_keys:array, indexes:array}>
     */
    public function getSupabaseFullSchema(): array
    {
        if (!$this->hasManagementApiAccess()) {
            return [];
        }

        $schema = [];

        // Columns
        $colSql = "SELECT c.table_name, c.column_name, c.data_type, c.udt_name,
                          c.is_nullable, c.column_default, c.ordinal_position
                   FROM information_schema.columns c
                   JOIN information_schema.tables t
                     ON c.table_name=t.table_name AND c.table_schema=t.table_schema
                   WHERE c.table_schema='public' AND t.table_type='BASE TABLE'
                   ORDER BY c.table_name, c.ordinal_position";

        $cols = $this->executeMgmtQuery($colSql);
        if (!is_array($cols)) {
            return [];
        }

        foreach ($cols as $row) {
            $t = $row['table_name'];
            if (!isset($schema[$t])) {
                $schema[$t] = ['columns' => [], 'primary_keys' => [], 'foreign_keys' => [], 'indexes' => []];
            }
            $schema[$t]['columns'][$row['column_name']] = [
                'data_type'      => $row['data_type'],
                'udt_name'       => $row['udt_name'] ?? $row['data_type'],
                'is_nullable'    => $row['is_nullable'],
                'column_default' => $row['column_default'],
                'position'       => (int)$row['ordinal_position'],
            ];
        }

        // Primary keys
        $pkSql = "SELECT tc.table_name, kcu.column_name
                  FROM information_schema.table_constraints tc
                  JOIN information_schema.key_column_usage kcu
                    ON tc.constraint_name=kcu.constraint_name AND tc.table_schema=kcu.table_schema
                  WHERE tc.table_schema='public' AND tc.constraint_type='PRIMARY KEY'
                  ORDER BY tc.table_name, kcu.ordinal_position";

        $pks = $this->executeMgmtQuery($pkSql);
        if (is_array($pks)) {
            foreach ($pks as $row) {
                if (isset($schema[$row['table_name']])) {
                    $schema[$row['table_name']]['primary_keys'][] = $row['column_name'];
                }
            }
        }

        // Indexes
        $idxSql = "SELECT ix.relname AS index_name, t.relname AS table_name,
                          a.attname AS column_name, ix.indisunique AS is_unique
                   FROM pg_class t
                   JOIN pg_index i ON t.oid=i.indrelid
                   JOIN pg_class ix ON ix.oid=i.indexrelid
                   JOIN pg_attribute a ON a.attrelid=t.oid AND a.attnum=ANY(i.indkey)
                   JOIN pg_namespace n ON n.oid=t.relnamespace
                   WHERE n.nspname='public' AND NOT i.indisprimary
                   ORDER BY t.relname, ix.relname";

        $idxs = $this->executeMgmtQuery($idxSql);
        if (is_array($idxs)) {
            foreach ($idxs as $row) {
                $tbl  = $row['table_name'];
                $iname = $row['index_name'];
                if (isset($schema[$tbl])) {
                    if (!isset($schema[$tbl]['indexes'][$iname])) {
                        $schema[$tbl]['indexes'][$iname] = ['unique' => (bool)$row['is_unique'], 'columns' => []];
                    }
                    $schema[$tbl]['indexes'][$iname]['columns'][] = $row['column_name'];
                }
            }
        }

        return $schema;
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Comparison Engine (DX002B)
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Full schema comparison: MySQL ↔ Supabase.
     * Returns structured diff — does not modify anything.
     *
     * @return array{
     *   compared_at: string,
     *   mysql_tables: int,
     *   supabase_tables: int,
     *   missing_in_supabase: list<string>,
     *   missing_in_mysql: list<string>,
     *   matched: list<string>,
     *   modified: array<string, array{column_diffs: array}>,
     *   has_drift: bool,
     *   drift_summary: string,
     *   management_api_available: bool
     * }
     */
    public function compareFullSchema(): array
    {
        $mysqlSchema    = $this->getMysqlFullSchema();
        $supabaseSchema = $this->getSupabaseFullSchema();

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

        $missingInSupabase = array_values(array_diff($mysqlTables, $supabaseTables));
        $missingInMysql    = array_values(array_diff($supabaseTables, $mysqlTables));
        $matched           = [];
        $modified          = [];

        foreach (array_intersect($mysqlTables, $supabaseTables) as $table) {
            $diffs = $this->compareColumns(
                $mysqlSchema[$table]['columns'] ?? [],
                $supabaseSchema[$table]['columns'] ?? []
            );

            if (empty($diffs)) {
                $matched[] = $table;
            } else {
                $modified[$table] = ['column_diffs' => $diffs];
            }
        }

        $hasDrift  = !empty($missingInSupabase) || !empty($missingInMysql) || !empty($modified);
        $driftParts = [];
        if (!empty($missingInSupabase)) $driftParts[] = count($missingInSupabase) . ' table(s) missing in Supabase';
        if (!empty($missingInMysql))    $driftParts[] = count($missingInMysql)    . ' table(s) extra in Supabase';
        if (!empty($modified))          $driftParts[] = count($modified)           . ' table(s) have column drift';

        return [
            'compared_at'              => date('Y-m-d H:i:s'),
            'mysql_tables'             => count($mysqlTables),
            'supabase_tables'          => count($supabaseTables),
            'missing_in_supabase'      => $missingInSupabase,
            'missing_in_mysql'         => $missingInMysql,
            'matched'                  => $matched,
            'modified'                 => $modified,
            'has_drift'                => $hasDrift,
            'drift_summary'            => $hasDrift ? implode('; ', $driftParts) : 'No drift detected',
            'management_api_available' => $this->hasManagementApiAccess(),
        ];
    }

    /**
     * Compare column sets between MySQL and Supabase (presence + type check).
     *
     * @return list<array{column:string, status:string, mysql_type:string|null, pg_type:string|null}>
     */
    private function compareColumns(array $mysqlCols, array $pgCols): array
    {
        $diffs  = [];
        $mapper = new DdlGeneratorService();

        foreach ($mysqlCols as $colName => $mysqlCol) {
            if (!isset($pgCols[$colName])) {
                $diffs[] = [
                    'column'     => $colName,
                    'status'     => 'missing_in_supabase',
                    'mysql_type' => $mysqlCol['column_type'] ?? $mysqlCol['data_type'],
                    'pg_type'    => null,
                ];
                continue;
            }

            // Type compatibility check
            $expectedPg = $mapper->mapType(
                $mysqlCol['column_type'] ?? '',
                $mysqlCol['data_type'] ?? '',
                $mysqlCol['extra'] ?? ''
            );
            $actualPg   = $pgCols[$colName]['data_type'] ?? '';

            if (!$this->typesCompatible($expectedPg, $actualPg)) {
                $diffs[] = [
                    'column'     => $colName,
                    'status'     => 'type_mismatch',
                    'mysql_type' => $mysqlCol['column_type'],
                    'pg_type'    => $actualPg,
                    'expected_pg'=> $expectedPg,
                ];
            }
        }

        foreach (array_keys($pgCols) as $colName) {
            if (!isset($mysqlCols[$colName])) {
                $diffs[] = [
                    'column'     => $colName,
                    'status'     => 'extra_in_supabase',
                    'mysql_type' => null,
                    'pg_type'    => $pgCols[$colName]['data_type'] ?? '—',
                ];
            }
        }

        return $diffs;
    }

    /**
     * Loose compatibility check between expected and actual PostgreSQL types.
     */
    private function typesCompatible(string $expected, string $actual): bool
    {
        // Normalize to lowercase base types for comparison
        $e = strtolower(preg_replace('/\s+without time zone/', '', $expected));
        $e = preg_replace('/\s+with time zone/', 'tz', $e);
        $e = explode(' ', $e)[0]; // first word

        $a = strtolower($actual);

        $compatible = [
            '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 ($compatible as $base => $variants) {
            if (($e === $base || in_array($e, $variants, true)) &&
                ($a === $base || in_array($a, $variants, true))) {
                return true;
            }
        }

        return $e === $a;
    }

    /**
     * Backward-compatible: presence-only table comparison (DX002A).
     */
    public function compareTableLists(): array
    {
        $mysqlTables    = array_column($this->getMysqlTables(), 'table_name');
        $supabaseTables = $this->getSupabaseTables();

        return [
            'mysql_only'     => array_values(array_diff($mysqlTables, $supabaseTables)),
            'supabase_only'  => array_values(array_diff($supabaseTables, $mysqlTables)),
            'in_both'        => array_values(array_intersect($mysqlTables, $supabaseTables)),
            'mysql_total'    => count($mysqlTables),
            'supabase_total' => count($supabaseTables),
        ];
    }

    /**
     * Backward-compatible: schema metadata (DX002A).
     */
    public function generateSchemaMetadata(): array
    {
        $tables = $this->getMysqlTables();
        return [
            'generated_at'    => date('Y-m-d H:i:s'),
            'mysql_tables'    => count($tables),
            'supabase_tables' => count($this->getSupabaseTables()),
            'sync_ready'      => $this->hasManagementApiAccess(),
            'note'            => $this->hasManagementApiAccess()
                ? 'Management API available. Use Push to Supabase to sync.'
                : 'Configure SUPABASE_ACCESS_TOKEN to enable schema push.',
        ];
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Management API Helper
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Execute a SQL query via Supabase Management API.
     * Returns parsed result rows or null on failure.
     * NEVER called if not configured. Service role key not used here.
     *
     * @return array|null
     */
    public function executeMgmtQuery(string $sql): ?array
    {
        if (!$this->hasManagementApiAccess()) {
            return null;
        }

        $url  = rtrim($this->mgmtApi, '/') . '/projects/' . $this->projectRef . '/database/query';
        $body = json_encode(['query' => $sql]);

        $context = stream_context_create([
            'http' => [
                'method'        => 'POST',
                'header'        => implode("\r\n", [
                    'Content-Type: application/json',
                    'Authorization: Bearer ' . $this->accessToken,
                ]),
                'content'       => $body,
                'timeout'       => $this->timeout,
                'ignore_errors' => true,
            ],
        ]);

        try {
            $response = @file_get_contents($url, false, $context);
            if ($response === false) {
                return null;
            }
            $data = json_decode($response, true);
            if (!is_array($data)) {
                return null;
            }
            // Management API wraps results
            return $data;
        } catch (\Throwable $e) {
            return null;
        }
    }
}
