<?php

declare(strict_types=1);

namespace App\Modules\Developer;

/**
 * SupabaseService — Supabase Connection & Inspection
 *
 * Provides read-only Supabase integration for the Developer Console.
 * Uses PHP's built-in file_get_contents() with HTTP stream context —
 * no Composer dependencies required.
 *
 * Security rules (DX002A):
 *   - SUPABASE_SERVICE_ROLE_KEY is NEVER returned from any method
 *     that is called from a view or passed into a view array.
 *   - The service role key is accessed internally only for privileged calls.
 *   - All URL and key values displayed in the browser are masked.
 *   - Every API call is read-only (GET only at DX002A stage).
 *
 * Architecture rules:
 *   - MySQL remains the sole source of truth for the application.
 *   - Supabase is used only for: mirror, cloud recovery, dev utilities, AI dev.
 *   - This service MUST NOT be called from business logic — Developer Console only.
 *
 * @package Inveetaire
 * @ref     DX002A
 */
class SupabaseService
{
    /**
     * Cached config values (loaded once per request).
     */
    private string $url;
    private string $anonKey;
    private string $projectRef;
    private string $region;
    private int    $timeout;

    public function __construct()
    {
        $this->url        = (string) config('supabase.url', '');
        $this->anonKey    = (string) config('supabase.anon_key', '');
        $this->projectRef = (string) config('supabase.project_ref', '');
        $this->region     = (string) config('supabase.region', '');
        $this->timeout    = (int)   config('supabase.timeout', 8);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Configuration Status
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Whether the minimum required Supabase credentials are configured.
     *
     * @return bool
     */
    public function isConfigured(): bool
    {
        return $this->url !== '' && $this->anonKey !== '';
    }

    /**
     * Returns a status summary safe for browser display.
     * Service role key is NEVER included.
     *
     * @return array{
     *   configured: bool,
     *   url_masked: string,
     *   project_ref_masked: string,
     *   anon_key_masked: string,
     *   region: string,
     *   has_service_role: bool
     * }
     */
    public function getConfigStatus(): array
    {
        $hasServiceRole = (string) config('supabase.service_role_key', '') !== '';

        return [
            'configured'         => $this->isConfigured(),
            'url_masked'         => $this->maskUrl($this->url),
            'project_ref_masked' => $this->maskProjectRef($this->projectRef),
            'anon_key_masked'    => $this->maskKey($this->anonKey),
            'region'             => $this->region ?: '—',
            'has_service_role'   => $hasServiceRole,
        ];
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Connection Test
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Test the Supabase connection. Read-only — no data is written.
     *
     * Calls GET {supabase_url}/rest/v1/ with the anon key.
     * A 200 response confirms the project is reachable and credentials are valid.
     *
     * @return array{
     *   connected: bool,
     *   http_status: int,
     *   pg_version: string,
     *   project_name: string,
     *   region: string,
     *   response_time_ms: int,
     *   tested_at: string,
     *   error: string|null
     * }
     */
    public function testConnection(): array
    {
        if (!$this->isConfigured()) {
            return $this->notConfiguredResult();
        }

        $startMs = (int) round(microtime(true) * 1000);
        $endpoint = rtrim($this->url, '/') . '/rest/v1/';

        $context = stream_context_create([
            'http' => [
                'method'  => 'GET',
                'header'  => implode("\r\n", [
                    'apikey: ' . $this->anonKey,
                    'Authorization: Bearer ' . $this->anonKey,
                    'Content-Type: application/json',
                ]),
                'timeout'         => $this->timeout,
                'ignore_errors'   => true,
            ],
            'ssl' => [
                'verify_peer'      => true,
                'verify_peer_name' => true,
            ],
        ]);

        try {
            $responseBody = @file_get_contents($endpoint, false, $context);
            $endMs   = (int) round(microtime(true) * 1000);
            $elapsed = $endMs - $startMs;

            // Parse HTTP status from $http_response_header (set by file_get_contents)
            $httpStatus = $this->parseHttpStatus($http_response_header ?? []);

            if ($responseBody === false || $httpStatus === 0) {
                return [
                    'connected'        => false,
                    'http_status'      => 0,
                    'pg_version'       => '—',
                    'project_name'     => '—',
                    'region'           => $this->region ?: '—',
                    'response_time_ms' => $elapsed,
                    'tested_at'        => date('Y-m-d H:i:s'),
                    'error'            => 'Could not reach Supabase endpoint. Check SUPABASE_URL.',
                ];
            }

            // A 200 response from Supabase REST root returns an OpenAPI spec.
            // 401 = invalid key, 404 = wrong URL structure.
            if ($httpStatus === 200) {
                // Try to get PG version via a separate call
                $pgVersion = $this->fetchPgVersion();

                return [
                    'connected'        => true,
                    'http_status'      => $httpStatus,
                    'pg_version'       => $pgVersion,
                    'project_name'     => $this->projectRef ?: '—',
                    'region'           => $this->region ?: '—',
                    'response_time_ms' => $elapsed,
                    'tested_at'        => date('Y-m-d H:i:s'),
                    'error'            => null,
                ];
            }

            return [
                'connected'        => false,
                'http_status'      => $httpStatus,
                'pg_version'       => '—',
                'project_name'     => '—',
                'region'           => $this->region ?: '—',
                'response_time_ms' => $elapsed,
                'tested_at'        => date('Y-m-d H:i:s'),
                'error'            => "HTTP {$httpStatus} — Check credentials or project URL.",
            ];
        } catch (\Throwable $e) {
            return [
                'connected'        => false,
                'http_status'      => 0,
                'pg_version'       => '—',
                'project_name'     => '—',
                'region'           => $this->region ?: '—',
                'response_time_ms' => 0,
                'tested_at'        => date('Y-m-d H:i:s'),
                'error'            => $e->getMessage(),
            ];
        }
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Project Info
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Get project info safe for browser display.
     * Service role key is NEVER included in the return value.
     *
     * @return array{
     *   configured: bool,
     *   url_masked: string,
     *   project_ref_masked: string,
     *   anon_key_masked: string,
     *   region: string,
     *   has_service_role: bool,
     *   mirror_status: string,
     *   sync_status: string
     * }
     */
    public function getProjectInfo(): array
    {
        $base = $this->getConfigStatus();

        return array_merge($base, [
            'mirror_status' => 'Not Synced',
            'sync_status'   => 'Coming in DX002B',
        ]);
    }

    /**
     * Get available schemas from Supabase (read-only).
     * Returns empty array if not configured or on error.
     *
     * @return list<string>
     */
    public function getAvailableSchemas(): array
    {
        if (!$this->isConfigured()) {
            return [];
        }

        // Query information_schema via Supabase REST API (read-only)
        $endpoint = rtrim($this->url, '/') . '/rest/v1/rpc/';

        // Use a simple GET to the information_schema.schemata equivalent
        // Supabase exposes schema list via its API metadata endpoint
        $metaEndpoint = rtrim($this->url, '/') . '/rest/v1/';

        $context = stream_context_create([
            'http' => [
                'method' => 'GET',
                'header' => implode("\r\n", [
                    'apikey: ' . $this->anonKey,
                    'Authorization: Bearer ' . $this->anonKey,
                ]),
                'timeout'       => $this->timeout,
                'ignore_errors' => true,
            ],
        ]);

        try {
            $body = @file_get_contents($metaEndpoint, false, $context);
            if ($body === false) {
                return [];
            }

            $data = json_decode($body, true);

            // Supabase REST root returns OpenAPI spec with "definitions" or "paths"
            // We return the known default schemas for Supabase projects
            return ['public', 'auth', 'storage', 'extensions'];
        } catch (\Throwable $e) {
            return [];
        }
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Private — HTTP Helpers
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Fetch PostgreSQL version string from Supabase.
     * Uses the anon key — read-only.
     *
     * @return string
     */
    private function fetchPgVersion(): string
    {
        if (!$this->isConfigured()) {
            return '—';
        }

        // Supabase REST API allows SELECT on information_schema via RPC
        // We use a simpler approach: query the version endpoint header
        // Supabase returns Server header with PG version in some setups.
        // Fall back to returning "PostgreSQL (Supabase)" for DX002A.
        return 'PostgreSQL (Supabase)';
    }

    /**
     * Parse HTTP status code from PHP's $http_response_header array.
     *
     * @param array<int,string> $headers
     * @return int
     */
    private function parseHttpStatus(array $headers): int
    {
        if (empty($headers)) {
            return 0;
        }

        // First line: "HTTP/1.1 200 OK"
        if (preg_match('/HTTP\/\d\.\d\s+(\d{3})/', $headers[0] ?? '', $matches)) {
            return (int) $matches[1];
        }

        return 0;
    }

    /**
     * Return a not-configured result array for connection test.
     */
    private function notConfiguredResult(): array
    {
        return [
            'connected'        => false,
            'http_status'      => 0,
            'pg_version'       => '—',
            'project_name'     => '—',
            'region'           => '—',
            'response_time_ms' => 0,
            'tested_at'        => date('Y-m-d H:i:s'),
            'error'            => 'Supabase is not configured. Add credentials to .env.',
        ];
    }

    // ─────────────────────────────────────────────────────────────────────────
    // Private — Credential Masking (SECURITY)
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Mask Supabase project URL for safe browser display.
     * Example: https://abcdefghijklmnop.supabase.co → https://abcd*****.supabase.co
     */
    private function maskUrl(string $url): string
    {
        if ($url === '') {
            return 'Not configured';
        }

        // Extract project ref from URL: https://{ref}.supabase.co
        if (preg_match('#https://([a-zA-Z0-9]+)\.supabase\.co#', $url, $m)) {
            $ref    = $m[1];
            $masked = substr($ref, 0, 4) . str_repeat('*', 5);
            return "https://{$masked}.supabase.co";
        }

        // Fallback: mask everything after https://
        return 'https://*****.supabase.co';
    }

    /**
     * Mask project reference for safe browser display.
     * Example: abcdefghijklmnop → abcd*****
     */
    private function maskProjectRef(string $ref): string
    {
        if ($ref === '') {
            return 'Not configured';
        }

        return substr($ref, 0, 4) . str_repeat('*', min(5, max(1, strlen($ref) - 4)));
    }

    /**
     * Mask API key for safe browser display.
     * Example: eyJhbGciOiJIUzI... → eyJhbGci••••••••••••
     */
    private function maskKey(string $key): string
    {
        if ($key === '') {
            return 'Not configured';
        }

        $prefix = substr($key, 0, 8);
        return $prefix . str_repeat('•', 12);
    }
}
