<?php

namespace EasyLinkAutomations\Models;

if (!defined('ABSPATH')) {
    exit;
}

use EasyLinkAutomations\Auth\AuthType;
use EasyLinkAutomations\Auth\EncryptionService;
use EasyLinkAutomations\Auth\OAuth2Handler;
use EasyLinkAutomations\Auth\PlatformAuthRegistry;
use EasyLinkAutomations\Config;

/**
 * Model for connections table.
 *
 * Provides CRUD operations and credential helper methods for decrypting
 * stored credentials and building authentication headers.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from Config::withDBPrefix(), escaped via escapeIdentifier().
 */
final class Connection
{
    private const CACHE_GROUP = 'easylink_automations_connections';

    /**
     * Get table name with prefix.
     */
    private static function table(): string
    {
        return Config::withDBPrefix('connections');
    }

    /**
     * Safely escape a SQL identifier (table or column name) for use in queries.
     *
     * Wraps the identifier in backticks and escapes any internal backticks,
     * which is the MySQL identifier quoting specification. This is equivalent
     * to the %i placeholder added in WordPress 6.2, but works on all versions.
     *
     * @param string $identifier Table or column name to escape.
     * @return string Backtick-quoted and escaped identifier.
     */
    private static function escapeIdentifier(string $identifier): string
    {
        return '`' . str_replace('`', '``', $identifier) . '`';
    }

    // ─── CRUD ────────────────────────────────────────────────────

    /**
     * Find a connection by ID.
     */
    public static function find(int $id): ?array
    {
        $cache_key = 'id_' . $id;
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $table  = self::escapeIdentifier(self::table());
        $result = $wpdb->get_row(
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is identifier-escaped via escapeIdentifier()
                "SELECT * FROM {$table} WHERE id = %d",
                $id
            ),
            ARRAY_A
        );
        $row = $result ?: null;
        wp_cache_set($cache_key, $row, self::CACHE_GROUP, 300);

        return $row;
    }

    /**
     * Get all connections.
     *
     * @return array<array>
     */
    public static function all(): array
    {
        $cache_key = 'all';
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $table = self::escapeIdentifier(self::table());
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is identifier-escaped via escapeIdentifier()
        $rows = $wpdb->get_results("SELECT * FROM {$table} ORDER BY created_at DESC", ARRAY_A) ?: [];
        wp_cache_set($cache_key, $rows, self::CACHE_GROUP, 300);

        return $rows;
    }

    /**
     * Count all connections.
     */
    public static function count(?string $status = null): int
    {
        $cache_key = 'count_' . (string) $status;
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return (int) $cached;
        }

        global $wpdb;
        $table = self::escapeIdentifier(self::table());

        if ($status) {
            $val = (int) $wpdb->get_var($wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is identifier-escaped via escapeIdentifier()
                "SELECT COUNT(*) FROM {$table} WHERE status = %s",
                $status
            ));
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is identifier-escaped via escapeIdentifier()
            $val = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table}");
        }
        wp_cache_set($cache_key, $val, self::CACHE_GROUP, 300);

        return $val;
    }

    /**
     * Find connections by platform.
     *
     * @return array<array>
     */
    public static function findByPlatform(string $platform): array
    {
        $cache_key = 'platform_' . $platform;
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $table = self::escapeIdentifier(self::table());
        $rows  = $wpdb->get_results(
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is identifier-escaped via escapeIdentifier()
                "SELECT * FROM {$table} WHERE platform = %s ORDER BY created_at DESC",
                $platform
            ),
            ARRAY_A
        ) ?: [];
        wp_cache_set($cache_key, $rows, self::CACHE_GROUP, 300);

        return $rows;
    }

    /**
     * Create a new connection.
     *
     * @return int|false Insert ID or false on failure
     */
    public static function create(array $data): int|false
    {
        global $wpdb;

        $result = $wpdb->insert(self::table(), $data);
        if ($result) {
            self::invalidateListCache();
            return $wpdb->insert_id;
        }
        return false;
    }

    /**
     * Update a connection.
     */
    public static function update(int $id, array $data): bool
    {
        global $wpdb;

        $ok = $wpdb->update(self::table(), $data, ['id' => $id]) !== false;
        if ($ok) {
            wp_cache_delete('id_' . $id, self::CACHE_GROUP);
            self::invalidateListCache();
        }
        return $ok;
    }

    /**
     * Delete a connection.
     */
    public static function delete(int $id): bool
    {
        global $wpdb;

        $ok = $wpdb->delete(self::table(), ['id' => $id]) !== false;
        if ($ok) {
            wp_cache_delete('id_' . $id, self::CACHE_GROUP);
            self::invalidateListCache();
        }
        return $ok;
    }

    /**
     * Invalidate list/count caches after create/update/delete.
     */
    private static function invalidateListCache(): void
    {
        wp_cache_delete('all', self::CACHE_GROUP);
        wp_cache_delete('count_', self::CACHE_GROUP);
    }

    /**
     * Paginated list of connections.
     */
    public static function paginate(int $page = 1, int $perPage = 20, ?string $platform = null): array
    {
        $cache_key = 'pag_' . $page . '_' . $perPage . '_' . (string) $platform;
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $offset = ($page - 1) * $perPage;
        $table  = self::escapeIdentifier(self::table());

        if ($platform) {
            $total = (int) $wpdb->get_var($wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is identifier-escaped via escapeIdentifier()
                "SELECT COUNT(*) FROM {$table} WHERE platform = %s",
                $platform
            ));
            $items = $wpdb->get_results(
                $wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is identifier-escaped via escapeIdentifier()
                    "SELECT * FROM {$table} WHERE platform = %s ORDER BY created_at DESC LIMIT %d OFFSET %d",
                    $platform,
                    $perPage,
                    $offset
                ),
                ARRAY_A
            ) ?: [];
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is identifier-escaped via escapeIdentifier()
            $total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table}");
            $items = $wpdb->get_results(
                $wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is identifier-escaped via escapeIdentifier()
                    "SELECT * FROM {$table} ORDER BY created_at DESC LIMIT %d OFFSET %d",
                    $perPage,
                    $offset
                ),
                ARRAY_A
            ) ?: [];
        }

        $result = [
            'items'       => $items,
            'total'       => $total,
            'page'        => $page,
            'per_page'    => $perPage,
            'total_pages' => (int) ceil($total / $perPage),
        ];
        wp_cache_set($cache_key, $result, self::CACHE_GROUP, 300);

        return $result;
    }

    // ─── Credential Helpers ──────────────────────────────────────

    /**
     * Decrypt the credentials column for a connection.
     *
     * @param array $connection A connection row from the database
     *
     * @return array The decrypted credential data
     */
    public static function getCredentials(array $connection): array
    {
        return EncryptionService::decrypt($connection['credentials'] ?? '');
    }

    /**
     * Decrypt the token_data column for a connection (OAuth2 tokens).
     *
     * @param array $connection A connection row from the database
     *
     * @return array The decrypted token data
     */
    public static function getTokenData(array $connection): array
    {
        return EncryptionService::decrypt($connection['token_data'] ?? '');
    }

    /**
     * Get a valid access token for a connection.
     *
     * For OAuth2 connections, this will auto-refresh the token if expired.
     * For API key / bearer / basic auth, this returns the stored token.
     *
     * @param int $connectionId The connection ID
     *
     * @return string The access token, or empty string on failure
     */
    public static function getAccessToken(int $connectionId): string
    {
        $connection = self::find($connectionId);
        if (!$connection) {
            return '';
        }

        $authType = $connection['auth_type'] ?? AuthType::API_KEY;

        if ($authType === AuthType::OAUTH2) {
            return OAuth2Handler::getValidToken($connectionId);
        }

        // For non-OAuth2 types, return the stored token
        $creds = self::getCredentials($connection);

        return $creds['access_token'] ?? $creds['api_key'] ?? '';
    }

    /**
     * Get a valid provider user token stored alongside the primary OAuth token.
     */
    public static function getUserAccessToken(int $connectionId): string
    {
        return OAuth2Handler::getValidUserToken($connectionId);
    }

    /**
     * Get ready-to-use HTTP auth headers for a connection.
     *
     * Uses the platform's authenticate() method to build the correct headers
     * based on the auth type and stored credentials.
     *
     * @param int $connectionId The connection ID
     *
     * @return array<string, string> Headers to add to HTTP requests
     */
    public static function getAuthHeaders(int $connectionId): array
    {
        $connection = self::find($connectionId);
        if (!$connection) {
            return [];
        }

        $platform  = $connection['platform'] ?? '';
        $authType  = $connection['auth_type'] ?? AuthType::API_KEY;

        // Build merged credentials for authentication
        $creds = self::getCredentials($connection);

        // For OAuth2, inject the current access token
        if ($authType === AuthType::OAUTH2) {
            $creds['access_token'] = OAuth2Handler::getValidToken($connectionId);
        }

        // Look up the platform auth definition
        $platformClass = PlatformAuthRegistry::get($platform);
        if ($platformClass) {
            return $platformClass::authenticate($creds);
        }

        // Fallback: build headers from auth type directly
        return match ($authType) {
            AuthType::BEARER_TOKEN => ['Authorization' => 'Bearer ' . ($creds['access_token'] ?? '')],
            AuthType::BASIC_AUTH   => ['Authorization' => 'Basic ' . base64_encode(($creds['username'] ?? '') . ':' . ($creds['password'] ?? ''))],
            AuthType::OAUTH2       => ['Authorization' => 'Bearer ' . ($creds['access_token'] ?? '')],
            default                => ['Authorization' => 'Bearer ' . ($creds['api_key'] ?? $creds['access_token'] ?? '')],
        };
    }
}
