<?php

declare(strict_types=1);

namespace App\Modules\Developer;

/**
 * SynchronizationService — MySQL → Supabase Sync Orchestrator
 *
 * Central service for all synchronization operations.
 * Controllers remain thin — all business logic lives here.
 *
 * Push Workflow (manual only):
 *   1. Read MySQL full schema
 *   2. Read Supabase schema (Management API)
 *   3. Compare → generate diff
 *   4. Generate incremental DDL
 *   5. If PAT configured → execute via Management API
 *      Else → SQL Export Mode (return SQL for manual execution)
 *   6. Log result
 *
 * Security:
 *   - SUPABASE_ACCESS_TOKEN never returned to views
 *   - SUPABASE_SERVICE_ROLE_KEY never used here
 *   - Stack traces never returned — human-friendly errors only
 *
 * Non-goals (DX002B):
 *   - NO automatic sync
 *   - NO cron
 *   - NO pull from Supabase (disabled — disaster recovery only)
 *   - NO runtime DB switching
 *
 * @package Inveetaire
 * @ref     DX002B
 */
class SynchronizationService
{
    private SchemaReaderService  $schemaReader;
    private DdlGeneratorService  $ddlGenerator;
    private MirrorVerifierService $verifier;
    private SyncLogService       $logger;
    private string               $accessToken;
    private string               $projectRef;
    private string               $mgmtApi;
    private int                  $timeout;

    public function __construct()
    {
        $this->schemaReader = new SchemaReaderService();
        $this->ddlGenerator = new DdlGeneratorService();
        $this->verifier     = new MirrorVerifierService();
        $this->logger       = new SyncLogService();
        $this->accessToken  = (string) config('supabase.access_token',    '');
        $this->projectRef   = (string) config('supabase.project_ref',     '');
        $this->mgmtApi      = (string) config('supabase.management_api_url', 'https://api.supabase.com/v1');
        $this->timeout      = (int)   config('supabase.timeout', 8);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Public Interface
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Get overall mirror status for dashboard display.
     *
     * @return array{
     *   has_pat: bool,
     *   supabase_configured: bool,
     *   last_sync: string|null,
     *   total_syncs: int,
     *   last_status: string,
     *   execution_mode: string
     * }
     */
    public function getStatus(): array
    {
        $supabaseService = new SupabaseService();

        return [
            'has_pat'             => $this->accessToken !== '',
            'supabase_configured' => $supabaseService->isConfigured(),
            'last_sync'           => $this->logger->getLastSyncTime(),
            'total_syncs'         => $this->logger->getTotalSyncs(),
            'last_status'         => $this->logger->getLastStatus(),
            'execution_mode'      => $this->accessToken !== '' ? 'management_api' : 'sql_export',
        ];
    }

    /**
     * DRY RUN — Compare, generate SQL, estimate impact.
     * NEVER executes any SQL.
     *
     * @param string $username  Current user for logging
     * @return array{
     *   success: bool,
     *   is_dry_run: true,
     *   comparison: array,
     *   ddl_sql: string,
     *   tables_affected: int,
     *   columns_affected: int,
     *   sql_statements: int,
     *   duration_ms: int,
     *   error: string|null
     * }
     */
    public function dryRun(string $username = 'super_admin'): array
    {
        $startMs = (int)round(microtime(true) * 1000);

        try {
            // 1. Compare schemas
            $comparison = $this->schemaReader->compareFullSchema();

            // 2. Generate DDL (no execution)
            $mysqlSchema = $this->schemaReader->getMysqlFullSchema();
            $ddl         = $this->ddlGenerator->generateIncrementalDdl($comparison, $mysqlSchema);

            // 3. Count affected tables/columns
            $tablesAffected  = count($comparison['missing_in_supabase']);
            $columnsAffected = 0;
            foreach ($comparison['modified'] as $tableData) {
                $columnsAffected += count($tableData['column_diffs']);
            }
            $sqlStatements = max(1, substr_count($ddl, ';'));

            $durationMs = (int)round(microtime(true) * 1000) - $startMs;

            // Log dry run
            $this->logger->append([
                'user'             => $username,
                'direction'        => 'mysql_to_supabase',
                'is_dry_run'       => true,
                'tables_affected'  => $tablesAffected,
                'columns_affected' => $columnsAffected,
                'sql_statements'   => $sqlStatements,
                'result'           => 'dry_run_complete',
                'duration_ms'      => $durationMs,
                'execution_mode'   => 'dry_run',
                'error_message'    => null,
            ]);

            return [
                'success'          => true,
                'is_dry_run'       => true,
                'comparison'       => $comparison,
                'ddl_sql'          => $ddl,
                'tables_affected'  => $tablesAffected,
                'columns_affected' => $columnsAffected,
                'sql_statements'   => $sqlStatements,
                'duration_ms'      => $durationMs,
                'error'            => null,
            ];
        } catch (\Throwable $e) {
            $durationMs = (int)round(microtime(true) * 1000) - $startMs;
            $this->logger->append([
                'user'          => $username,
                'direction'     => 'mysql_to_supabase',
                'is_dry_run'    => true,
                'result'        => 'error',
                'duration_ms'   => $durationMs,
                'execution_mode'=> 'dry_run',
                'error_message' => 'Dry run failed: schema comparison error.',
            ]);

            return [
                'success'         => false,
                'is_dry_run'      => true,
                'comparison'      => [],
                'ddl_sql'         => '',
                'tables_affected' => 0,
                'columns_affected'=> 0,
                'sql_statements'  => 0,
                'duration_ms'     => $durationMs,
                'error'           => 'Schema comparison failed. Check Supabase configuration.',
            ];
        }
    }

    /**
     * Compare schemas only (no DDL generation, no execution).
     *
     * @return array{success:bool, comparison:array, error:string|null}
     */
    public function compare(): array
    {
        try {
            $comparison = $this->schemaReader->compareFullSchema();
            return ['success' => true, 'comparison' => $comparison, 'error' => null];
        } catch (\Throwable $e) {
            return ['success' => false, 'comparison' => [], 'error' => 'Schema comparison failed. Check Supabase connectivity.'];
        }
    }

    /**
     * Generate SQL preview without execution.
     *
     * @param string $mode  'incremental' or 'full'
     * @return array{success:bool, sql:string, mode:string, statements:int, error:string|null}
     */
    public function generatePreview(string $mode = 'incremental'): array
    {
        try {
            $mysqlSchema = $this->schemaReader->getMysqlFullSchema();

            if ($mode === 'full') {
                $sql = $this->ddlGenerator->generateAllTables($mysqlSchema);
            } else {
                $comparison = $this->schemaReader->compareFullSchema();
                $sql        = $this->ddlGenerator->generateIncrementalDdl($comparison, $mysqlSchema);
            }

            $statements = max(0, substr_count($sql, ';'));

            return [
                'success'    => true,
                'sql'        => $sql,
                'mode'       => $mode,
                'statements' => $statements,
                'error'      => null,
            ];
        } catch (\Throwable $e) {
            return [
                'success'    => false,
                'sql'        => '',
                'mode'       => $mode,
                'statements' => 0,
                'error'      => 'DDL generation failed. Check MySQL connection.',
            ];
        }
    }

    /**
     * PUSH MySQL → Supabase.
     * Requires explicit confirmation (confirmed=true) to prevent accidents.
     * Manual only — never called automatically.
     *
     * If PAT configured → executes via Management API.
     * Otherwise → SQL Export Mode (returns SQL for manual execution).
     *
     * @param bool   $confirmed  Must be true (confirmation dialog enforced in view)
     * @param string $mode       'incremental' (default) or 'full'
     * @param string $username   Current user
     * @return array{
     *   success: bool,
     *   execution_mode: string,
     *   tables_affected: int,
     *   columns_affected: int,
     *   sql_statements: int,
     *   sql_for_download: string|null,
     *   duration_ms: int,
     *   results: array,
     *   error: string|null
     * }
     */
    public function push(bool $confirmed, string $mode = 'incremental', string $username = 'super_admin'): array
    {
        if (!$confirmed) {
            return $this->errorResult('Push requires explicit confirmation.', 0, 'not_confirmed');
        }

        $startMs = (int)round(microtime(true) * 1000);

        try {
            // 1. Generate DDL
            $mysqlSchema = $this->schemaReader->getMysqlFullSchema();
            $comparison  = $this->schemaReader->compareFullSchema();

            $sql = $mode === 'full'
                ? $this->ddlGenerator->generateAllTables($mysqlSchema)
                : $this->ddlGenerator->generateIncrementalDdl($comparison, $mysqlSchema);

            $tablesAffected  = $mode === 'full'
                ? count($mysqlSchema)
                : count($comparison['missing_in_supabase']);
            $columnsAffected = 0;
            foreach ($comparison['modified'] as $tableData) {
                $columnsAffected += count($tableData['column_diffs']);
            }
            $sqlStatements = max(0, substr_count($sql, ';'));

            // 2. Execute or export
            if ($this->accessToken !== '') {
                // Management API execution
                $execResult = $this->executeSqlViaMgmtApi($sql);
                $success    = $execResult['success'];
                $errorMsg   = $execResult['error'] ?? null;
                $execMode   = 'management_api';
                $sqlForDownload = null;
            } else {
                // SQL Export Mode
                $success        = true;
                $errorMsg       = null;
                $execMode       = 'sql_export';
                $sqlForDownload = $sql;
            }

            $durationMs = (int)round(microtime(true) * 1000) - $startMs;

            // 3. Verify after push (only if actually executed)
            $verification = null;
            if ($success && $execMode === 'management_api') {
                $verification = $this->verifier->verify();
            }

            // 4. Log
            $this->logger->append([
                'user'             => $username,
                'direction'        => 'mysql_to_supabase',
                'is_dry_run'       => false,
                'tables_affected'  => $tablesAffected,
                'columns_affected' => $columnsAffected,
                'sql_statements'   => $sqlStatements,
                'result'           => $success ? 'success' : 'error',
                'duration_ms'      => $durationMs,
                'execution_mode'   => $execMode,
                'error_message'    => $errorMsg,
            ]);

            return [
                'success'          => $success,
                'execution_mode'   => $execMode,
                'tables_affected'  => $tablesAffected,
                'columns_affected' => $columnsAffected,
                'sql_statements'   => $sqlStatements,
                'sql_for_download' => $sqlForDownload,
                'verification'     => $verification,
                'duration_ms'      => $durationMs,
                'results'          => [],
                'error'            => $errorMsg,
            ];
        } catch (\Throwable $e) {
            $durationMs = (int)round(microtime(true) * 1000) - $startMs;
            $this->logger->append([
                'user'          => $username,
                'direction'     => 'mysql_to_supabase',
                'is_dry_run'    => false,
                'result'        => 'error',
                'duration_ms'   => $durationMs,
                'execution_mode'=> 'failed',
                'error_message' => 'Push failed: internal error.',
            ]);

            return $this->errorResult('Push failed. Check MySQL and Supabase connectivity.', $durationMs, 'exception');
        }
    }

    /**
     * Verify mirror integrity — delegates to MirrorVerifierService.
     */
    public function verify(): array
    {
        return $this->verifier->verify();
    }

    /**
     * Get synchronization log entries.
     */
    public function getLogs(int $limit = 50): array
    {
        return $this->logger->getRecent($limit);
    }

    /**
     * Clear sync log.
     */
    public function clearLogs(): void
    {
        $this->logger->clear();
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Private — Management API Execution
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Execute SQL via Supabase Management API.
     * Splits SQL into individual statements for better error isolation.
     * SECURITY: access_token is used server-side only, never returned.
     *
     * @return array{success:bool, executed:int, error:string|null}
     */
    private function executeSqlViaMgmtApi(string $sql): array
    {
        $url     = rtrim($this->mgmtApi, '/') . '/projects/' . $this->projectRef . '/database/query';
        $executed = 0;

        // Split into individual statements
        $statements = array_filter(
            array_map('trim', explode(';', $sql)),
            static fn($s) => !empty($s) && strpos(ltrim($s), '--') !== 0
        );

        foreach ($statements as $stmt) {
            $body    = json_encode(['query' => $stmt . ';']);
            $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);
                $httpStatus = $this->parseHttpStatus($http_response_header ?? []);

                if ($response === false || ($httpStatus >= 400)) {
                    $errorDetail = $response ? (json_decode($response, true)['message'] ?? 'HTTP ' . $httpStatus) : 'Network error';
                    return [
                        'success'  => false,
                        'executed' => $executed,
                        'error'    => 'Statement ' . ($executed + 1) . ' failed: ' . $errorDetail,
                    ];
                }

                $executed++;
            } catch (\Throwable $e) {
                return [
                    'success'  => false,
                    'executed' => $executed,
                    'error'    => 'Network error during SQL execution.',
                ];
            }
        }

        return ['success' => true, 'executed' => $executed, 'error' => null];
    }

    /**
     * Parse HTTP status from $http_response_header.
     */
    private function parseHttpStatus(array $headers): int
    {
        if (empty($headers)) return 0;
        if (preg_match('/HTTP\/\d\.\d\s+(\d{3})/', $headers[0] ?? '', $m)) {
            return (int)$m[1];
        }
        return 0;
    }

    /**
     * Build a standard error result array.
     */
    private function errorResult(string $message, int $durationMs, string $mode): array
    {
        return [
            'success'          => false,
            'execution_mode'   => $mode,
            'tables_affected'  => 0,
            'columns_affected' => 0,
            'sql_statements'   => 0,
            'sql_for_download' => null,
            'verification'     => null,
            'duration_ms'      => $durationMs,
            'results'          => [],
            'error'            => $message,
        ];
    }
}
