<?php

namespace BitApps\PiPro\src\Integrations\MicrosoftExcel;

// Prevent direct script access
if (!\defined('ABSPATH')) {
    exit;
}

use BitApps\PiPro\Deps\BitApps\WPKit\Helpers\JSON;
use BitApps\PiPro\Deps\BitApps\WPKit\Http\Client\HttpClient;

final class MicrosoftExcelService
{
    private const BASE_URL = 'https://graph.microsoft.com/v1.0';

    private $http;

    private $sessionId;

    /**
     * MicrosoftExcelService constructor.
     *
     * @param HttpClient $httpClient
     */
    public function __construct($httpClient)
    {
        $this->http = $httpClient;
        $this->sessionId = null;
    }

    /**
     * Get the used range of a worksheet.
     *
     * @param string $workbookId
     * @param string $worksheetId
     *
     * @return string
     */
    public function getUsedRange($workbookId, $worksheetId)
    {
        $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/worksheets/{$worksheetId}/usedRange?\$select=address";
        $response = $this->http->request($endPoint, 'GET', []);

        if (isset($response->address)) {
            $parts = explode('!', $response->address);

            return isset($parts[1]) ? $parts[1] : $response->address;
        }

        return '';
    }

    /**
     * Add a new worksheet to a workbook.
     *
     * @param string $workbookId
     * @param null|mixed $fieldMapData
     *
     * @return array
     */
    public function addWorksheetToWorkbook($workbookId, $fieldMapData = null)
    {
        $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/worksheets/add";
        $response = $this->http->request($endPoint, 'POST', JSON::encode($fieldMapData));

        return [
            'response'    => $response,
            'payload'     => $fieldMapData,
            'status_code' => $this->http->getResponseCode()
        ];
    }

    /**
     * Create a new table.
     *
     * @param string $workbookId
     * @param string $worksheetId
     * @param string $selectRange
     * @param mixed $fieldMapData
     *
     * @return array
     */
    public function createTable($workbookId, $worksheetId, $selectRange, $fieldMapData)
    {
        $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/worksheets/{$worksheetId}/tables/add";

        if ($selectRange === 'auto') {
            $address = $this->getUsedRange($workbookId, $worksheetId);
            $fieldMapData = array_merge($fieldMapData, ['address' => $address]);
        }

        $response = $this->http->request($endPoint, 'POST', JSON::encode($fieldMapData));

        return [
            'response'    => $response,
            'payload'     => $fieldMapData,
            'status_code' => $this->http->getResponseCode()
        ];
    }

    /**
     * Delete a table.
     *
     * @param string $workbookId
     * @param string $worksheetId
     * @param string $tableId
     *
     * @return array
     */
    public function deleteTable($workbookId, $worksheetId, $tableId)
    {
        $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/tables/{$tableId}";
        $response = $this->http->request($endPoint, 'DELETE', []);

        return [
            'response'    => $response,
            'payload'     => ['workbookId' => $workbookId, 'worksheetId' => $worksheetId, 'tableId' => $tableId],
            'status_code' => $this->http->getResponseCode()
        ];
    }

    /**
     * Delete a worksheet.
     *
     * @param string $workbookId
     * @param string $worksheetId
     *
     * @return array
     */
    public function deleteWorksheet($workbookId, $worksheetId)
    {
        $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/worksheets('{$worksheetId}')";
        $response = $this->http->request($endPoint, 'DELETE', []);

        return [
            'response'    => $response,
            'payload'     => ['workbookId' => $workbookId, 'worksheetId' => $worksheetId],
            'status_code' => $this->http->getResponseCode()
        ];
    }

    /**
     * Clear a worksheet or range.
     *
     * @param string $workbookId
     * @param string $worksheetId
     * @param string $range
     * @param mixed $fieldMapData
     *
     * @return array
     */
    public function clearWorksheet($workbookId, $worksheetId, $fieldMapData, $range)
    {
        if ($range === true) {
            $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/worksheets/{$worksheetId}/" . $fieldMapData['range'] . '/clear';
        } else {
            $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/worksheets/{$worksheetId}/range/clear";
        }

        $response = $this->http->request($endPoint, 'POST', JSON::encode($fieldMapData));

        return [
            'response'    => $response,
            'payload'     => $fieldMapData,
            'status_code' => $this->http->getResponseCode()
        ];
    }

    /**
     * List worksheet rows.
     *
     * @param string $workbookId
     * @param string $worksheetId
     * @param mixed $range
     *
     * @return array
     */
    public function listWorksheetRows($workbookId, $worksheetId, $range)
    {
        $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/worksheets/{$worksheetId}/usedRange";

        if (!empty($range)) {
            $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/worksheets/{$worksheetId}/range(address='{$range}')";
        }

        $response = $this->http->request($endPoint, 'GET', []);

        return [
            'response'    => $response,
            'payload'     => [],
            'status_code' => $this->http->getResponseCode(),
        ];
    }

    /**
     * Append rows to a worksheet.
     *
     * @param string $workbookId
     * @param string $worksheetId
     * @param array $rowsData
     *
     * @return array
     */
    public function addWorksheetRow($workbookId, $worksheetId, $rowsData)
    {
        $usedRange = $this->getUsedRange($workbookId, $worksheetId);
        list($appendRange, $payload) = $this->prepareRowRangeAndPayload($rowsData, $usedRange);
        $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/worksheets/{$worksheetId}/range(address='{$appendRange}')";
        $response = $this->http->request($endPoint, 'PATCH', $payload);

        return [
            'response'    => $response,
            'payload'     => $payload,
            'status_code' => $this->http->getResponseCode()
        ];
    }

    /**
     * Update rows in a worksheet.
     *
     * @param string $workbookId
     * @param string $worksheetId
     * @param array $rowsData
     * @param mixed $fieldMapData
     *
     * @return array
     */
    public function updateWorksheetRow($workbookId, $worksheetId, $fieldMapData, $rowsData)
    {
        $targetRange = $fieldMapData['targetRange'] ?? '';
        $values = $this->fillMissingColumns($rowsData, $targetRange);
        $payload = JSON::encode(['values' => $values]);
        $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/worksheets/{$worksheetId}/range(address='{$targetRange}')";
        $response = $this->http->request($endPoint, 'PATCH', $payload);

        return [
            'response'    => $response,
            'payload'     => $payload,
            'status_code' => $this->http->getResponseCode()
        ];
    }

    /**
     * Get all worksheets from a workbook.
     *
     * @param string $workbookId
     * @param bool $returnAll
     * @param mixed $fieldMapData
     *
     * @return array
     */
    public function getAllWorksheets($workbookId, $returnAll = false, $fieldMapData = [])
    {
        $endPoint = self::BASE_URL . "/me/drive/items/{$workbookId}/workbook/worksheets";

        $queryParams = [
            '$top' => !$returnAll ? ($fieldMapData['limit'] ?? 100) : null,
        ];

        $queryParams = array_filter($queryParams);
        $endPoint = add_query_arg($queryParams, $endPoint);
        $response = $this->http->request($endPoint, 'GET', []);

        return [
            'response'    => $response,
            'payload'     => ['workbookId' => $workbookId, 'returnAll' => $returnAll, 'limit' => $fieldMapData['limit'] ?? 100, 'fields' => $fieldMapData['fields'] ?? ''],
            'status_code' => $this->http->getResponseCode()
        ];
    }

    /**
     * Prepare append range and payload for worksheet row operations.
     *
     * @param array $rowsData
     * @param string $range
     *
     * @return array
     */
    private function prepareRowRangeAndPayload($rowsData, $range)
    {
        $values = $this->fillMissingColumns($rowsData, $range);
        $parts = explode(':', $range);
        $endCell = end($parts);
        $nextRow = ((int) preg_replace('/[^0-9]/', '', $endCell)) + 1;
        $startCol = preg_replace('/[0-9]/', '', explode(':', $range)[0]);
        $endCol = preg_replace('/[0-9]/', '', $endCell);
        $appendRange = "{$startCol}{$nextRow}:{$endCol}{$nextRow}";
        $payload = JSON::encode(['values' => $values]);

        return [$appendRange, $payload];
    }

    /**
     * Convert column-value pairs to ordered array.
     * Example: ['A' => 'x', 'C' => 'z'] with range "A1:C1" becomes ['x', null, 'z'].
     *
     * @param array $rowsData
     * @param string $range
     *
     * @return array
     */
    private function fillMissingColumns($rowsData, $range)
    {
        $range = explode('!', $range);
        $range = end($range);
        $parts = explode(':', $range);
        $startCol = preg_replace('/[0-9]/', '', $parts[0]);
        $endCol = preg_replace('/[0-9]/', '', $parts[1] ?? $parts[0]);

        $result = [];
        for ($col = $startCol; $col <= $endCol; ++$col) {
            $result[] = $rowsData[$col] ?? null;
        }

        return [$result];
    }
}
