<?php

namespace EasyLinkAutomations\Actions\GoogleSheets;

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

use EasyLinkAutomations\Models\Connection;

/**
 * Shared helper for Google Sheets actions.
 *
 * Centralises Google Sheets API v4 requests, sheet/row resolution, and value
 * parsing so each action class can stay focused on its own operation.
 */
final class GoogleSheetsHelper
{
    public const API_BASE = 'https://sheets.googleapis.com/v4/spreadsheets';

    /**
     * Retrieve a valid OAuth2 access token for the given connection.
     */
    public static function getToken(int $connectionId): string
    {
        return (string) Connection::getAccessToken($connectionId);
    }

    /**
     * Perform a Google Sheets API request.
     *
     * @return array{success: bool, status: int, body: array, error: string}
     */
    public static function request(string $method, string $url, string $token, ?array $body = null): array
    {
        $args = [
            'method'  => $method,
            'headers' => [
                'Content-Type'  => 'application/json',
                'Authorization' => 'Bearer ' . $token,
            ],
            'timeout' => 30,
        ];

        if ($body !== null) {
            $args['body'] = wp_json_encode($body);
        }

        $response = wp_remote_request($url, $args);

        if (is_wp_error($response)) {
            return [
                'success' => false,
                'status'  => 0,
                'body'    => [],
                'error'   => $response->get_error_message(),
            ];
        }

        $status  = (int) wp_remote_retrieve_response_code($response);
        $decoded = json_decode(wp_remote_retrieve_body($response), true);
        $decoded = is_array($decoded) ? $decoded : [];
        $success = $status >= 200 && $status < 300;

        return [
            'success' => $success,
            'status'  => $status,
            'body'    => $decoded,
            'error'   => $success ? '' : ($decoded['error']['message'] ?? 'Unknown error'),
        ];
    }

    /**
     * Fetch spreadsheet metadata (title and sheet properties).
     *
     * @return array{success: bool, status: int, body: array, error: string}
     */
    public static function getSpreadsheetMeta(string $token, string $spreadsheetId): array
    {
        return self::request(
            'GET',
            self::API_BASE . '/' . rawurlencode($spreadsheetId)
                . '?fields=spreadsheetId,properties.title,sheets.properties',
            $token
        );
    }

    /**
     * Resolve a sheet/tab title to its numeric sheetId (gid).
     */
    public static function resolveSheetId(string $token, string $spreadsheetId, string $sheetTitle): ?int
    {
        $meta = self::getSpreadsheetMeta($token, $spreadsheetId);
        if (!$meta['success']) {
            return null;
        }

        foreach ($meta['body']['sheets'] ?? [] as $sheet) {
            $props = $sheet['properties'] ?? [];
            if (($props['title'] ?? '') === $sheetTitle && isset($props['sheetId'])) {
                return (int) $props['sheetId'];
            }
        }

        return null;
    }

    /**
     * Read the values of a sheet range as a 2D array (row 1 at index 0).
     *
     * @return array<int, array<int, string>>
     */
    public static function readRange(string $token, string $spreadsheetId, string $range): array
    {
        $result = self::request(
            'GET',
            self::API_BASE . '/' . rawurlencode($spreadsheetId) . '/values/' . rawurlencode($range),
            $token
        );

        if (!$result['success']) {
            return [];
        }

        $values = $result['body']['values'] ?? [];

        return is_array($values) ? $values : [];
    }

    /**
     * Locate the 1-based spreadsheet row number whose lookup column matches a value.
     *
     * @param array<int, array<int, string>> $values    Full sheet values (row 1 at index 0).
     * @param int                            $headerRow 1-based header row number.
     */
    public static function findRowNumber(array $values, int $headerRow, string $lookupColumn, string $lookupValue): ?int
    {
        $headerIndex = max(0, $headerRow - 1);
        $headers     = $values[$headerIndex] ?? [];
        $columnIndex = array_search($lookupColumn, $headers, true);

        if ($columnIndex === false) {
            return null;
        }

        $needle = trim((string) $lookupValue);

        foreach ($values as $rowIndex => $row) {
            if ($rowIndex <= $headerIndex) {
                continue;
            }
            if (trim((string) ($row[$columnIndex] ?? '')) === $needle) {
                return $rowIndex + 1;
            }
        }

        return null;
    }

    /**
     * Convert a 0-based column index to its A1 letter (0 = A, 26 = AA).
     */
    public static function numberToColumn(int $index): string
    {
        $letter = '';
        while ($index >= 0) {
            $letter = chr(($index % 26) + 65) . $letter;
            $index  = intdiv($index, 26) - 1;
        }

        return $letter;
    }

    /**
     * Build a row array ordered by the sheet header row.
     *
     * @param array<string, mixed> $fieldMappings
     * @param array<int, mixed>    $headers
     *
     * @return array<int, mixed>
     */
    public static function buildRowValues(array $fieldMappings, array $headers): array
    {
        if (empty($headers)) {
            return array_values($fieldMappings);
        }

        $rowValues = array_fill(0, count($headers), '');

        foreach ($fieldMappings as $header => $value) {
            $columnIndex = array_search($header, $headers, true);
            if ($columnIndex !== false) {
                $rowValues[$columnIndex] = $value;
            }
        }

        return $rowValues;
    }

    /**
     * Quote a sheet/tab title for safe use inside an A1 range reference.
     */
    public static function quoteSheetName(string $name): string
    {
        return "'" . str_replace("'", "''", $name) . "'";
    }

    /**
     * Decode a field_mapping config value into an associative array.
     *
     * @return array<string, mixed>
     */
    public static function parseMappings(mixed $value): array
    {
        if (is_string($value)) {
            $value = json_decode($value, true);
        }

        return is_array($value) ? $value : [];
    }
}
