<?php

namespace EasyLinkAutomations\Actions\GoogleSheets;

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

use EasyLinkAutomations\Actions\BaseAction;

/**
 * Action that updates a matching row in a Google Sheet, or appends a new one
 * when no match is found (an upsert).
 *
 * The row is located by matching a lookup column against a value.
 */
final class AppendOrUpdateRowAction extends BaseAction
{
    public static function getSlug(): string
    {
        return 'google_sheets/append_or_update_row';
    }

    public static function getName(): string
    {
        return __('Append or Update Row', 'easylink-automations');
    }

    public static function getDescription(): string
    {
        return __('Update a matching row in a Google Sheet, or append a new row if none is found', 'easylink-automations');
    }

    public static function getCategory(): string
    {
        return 'Google Sheets';
    }

    public static function getIcon(): string
    {
        return 'google-sheets';
    }

    public static function getRequiresConnection(): ?string
    {
        return 'google_sheets';
    }

    public static function getConfigFields(): array
    {
        return [
            'spreadsheet_id' => [
                'label'         => 'Spreadsheet',
                'type'          => 'remote_select',
                'required'      => true,
                'placeholder'   => 'Select a spreadsheet...',
                'remote_source' => 'google-sheets/spreadsheets',
                'depends_on'    => ['connection_id'],
                'options_key'   => 'spreadsheets',
            ],
            'sheet_name' => [
                'label'         => 'Sheet',
                'type'          => 'remote_select',
                'required'      => true,
                'placeholder'   => 'Select a sheet...',
                'remote_source' => 'google-sheets/sheets',
                'depends_on'    => ['connection_id', 'spreadsheet_id'],
                'options_key'   => 'sheets',
            ],
            'header_row' => [
                'label'       => 'Header Row',
                'type'        => 'number',
                'default'     => 1,
                'required'    => false,
                'placeholder' => '1',
                'description' => 'Row number containing column headers',
            ],
            'lookup_column' => [
                'label'         => 'Lookup Column',
                'type'          => 'remote_select',
                'required'      => true,
                'placeholder'   => 'Select a column to match on...',
                'remote_source' => 'google-sheets/columns',
                'depends_on'    => ['connection_id', 'spreadsheet_id', 'sheet_name', 'header_row'],
                'options_key'   => 'columns',
                'description'   => 'The column used to find an existing row',
            ],
            'lookup_value' => [
                'label'            => 'Lookup Value',
                'type'             => 'text',
                'required'         => true,
                'placeholder'      => '{{user_email}}',
                'supports_mapping' => true,
                'description'      => 'The value to search for in the lookup column',
            ],
            'field_mappings' => [
                'label'            => 'Column Mappings',
                'type'             => 'field_mapping',
                'required'         => true,
                'remote_source'    => 'google-sheets/columns',
                'depends_on'       => ['connection_id', 'spreadsheet_id', 'sheet_name', 'header_row'],
                'options_key'      => 'columns',
                'supports_mapping' => true,
            ],
            'value_input_option' => [
                'label'   => 'Value Input Option',
                'type'    => 'select',
                'options' => [
                    ['value' => 'RAW', 'label' => 'Raw — Values are stored as-is'],
                    ['value' => 'USER_ENTERED', 'label' => 'User Entered — Parsed as if typed into the UI'],
                ],
                'default' => 'USER_ENTERED',
            ],
        ];
    }

    public function execute(array $triggerData, array $config): array
    {
        $missing = $this->validateRequired($config);
        if ($missing) {
            return ['success' => false, 'message' => 'Missing required fields: ' . implode(', ', $missing)];
        }

        $connectionId = (int) ($config['connection_id'] ?? 0);
        $token        = GoogleSheetsHelper::getToken($connectionId);
        if (empty($token)) {
            return ['success' => false, 'message' => 'No valid Google connection found or token expired'];
        }

        $mapped           = $this->mapConfig($config, $triggerData);
        $spreadsheetId    = $mapped['spreadsheet_id'];
        $sheetName        = $mapped['sheet_name'] ?? 'Sheet1';
        $headerRow        = max(1, (int) ($mapped['header_row'] ?? 1));
        $lookupColumn     = (string) ($mapped['lookup_column'] ?? '');
        $lookupValue      = (string) ($mapped['lookup_value'] ?? '');
        $valueInputOption = $config['value_input_option'] ?? 'USER_ENTERED';

        $fieldMappings = GoogleSheetsHelper::parseMappings($mapped['field_mappings'] ?? '{}');
        if (empty($fieldMappings)) {
            return ['success' => false, 'message' => 'Column mappings are required'];
        }

        $range  = GoogleSheetsHelper::quoteSheetName($sheetName) . '!A:ZZ';
        $values = GoogleSheetsHelper::readRange($token, $spreadsheetId, $range);

        $headers   = $values[$headerRow - 1] ?? [];
        $rowNumber = empty($values)
            ? null
            : GoogleSheetsHelper::findRowNumber($values, $headerRow, $lookupColumn, $lookupValue);

        return $rowNumber === null
            ? $this->appendRow($token, $spreadsheetId, $sheetName, $headers, $fieldMappings, $valueInputOption)
            : $this->updateRow($token, $spreadsheetId, $sheetName, $headers, $rowNumber, $fieldMappings, $valueInputOption);
    }

    /**
     * Update the located row using one cell write per mapped column.
     */
    private function updateRow(
        string $token,
        string $spreadsheetId,
        string $sheetName,
        array $headers,
        int $rowNumber,
        array $fieldMappings,
        string $valueInputOption
    ): array {
        $data = [];
        foreach ($fieldMappings as $header => $value) {
            $columnIndex = array_search($header, $headers, true);
            if ($columnIndex === false) {
                continue;
            }
            $letter = GoogleSheetsHelper::numberToColumn((int) $columnIndex);
            $data[] = [
                'range'  => GoogleSheetsHelper::quoteSheetName($sheetName) . "!{$letter}{$rowNumber}",
                'values' => [[$value]],
            ];
        }

        if (empty($data)) {
            return ['success' => false, 'message' => 'None of the mapped columns were found in the header row'];
        }

        $result = GoogleSheetsHelper::request(
            'POST',
            GoogleSheetsHelper::API_BASE . '/' . rawurlencode($spreadsheetId) . '/values:batchUpdate',
            $token,
            [
                'valueInputOption' => $valueInputOption,
                'data'             => $data,
            ]
        );

        return [
            'success' => $result['success'],
            'message' => $result['success']
                ? "Existing row {$rowNumber} updated successfully"
                : 'Failed to update row (HTTP ' . $result['status'] . '): ' . $result['error'],
            'data' => [
                'spreadsheet_id' => $spreadsheetId,
                'operation'      => 'update',
                'row_number'     => $rowNumber,
            ],
        ];
    }

    /**
     * Append a new row, ordering values by the existing header row when present.
     */
    private function appendRow(
        string $token,
        string $spreadsheetId,
        string $sheetName,
        array $headers,
        array $fieldMappings,
        string $valueInputOption
    ): array {
        $rowValues = GoogleSheetsHelper::buildRowValues($fieldMappings, $headers);

        $range = rawurlencode(GoogleSheetsHelper::quoteSheetName($sheetName) . '!A:ZZ');
        $url   = GoogleSheetsHelper::API_BASE . '/' . rawurlencode($spreadsheetId) . "/values/{$range}:append"
               . "?valueInputOption={$valueInputOption}&insertDataOption=INSERT_ROWS";

        $result = GoogleSheetsHelper::request('POST', $url, $token, ['values' => [$rowValues]]);

        $updatedRange = $result['body']['updates']['updatedRange'] ?? '';

        return [
            'success' => $result['success'],
            'message' => $result['success']
                ? "No match found — new row appended ({$updatedRange})"
                : 'Failed to append row (HTTP ' . $result['status'] . '): ' . $result['error'],
            'data' => [
                'spreadsheet_id' => $spreadsheetId,
                'operation'      => 'append',
                'updated_range'  => $updatedRange,
            ],
        ];
    }
}
