<?php

namespace EasyLinkAutomations\Actions\GoogleSheets;

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

use EasyLinkAutomations\Actions\BaseAction;

/**
 * Action that reads rows from a Google Sheet, optionally filtered by a column
 * value, and exposes them as data for later automation steps.
 */
final class GetRowsAction extends BaseAction
{
    public static function getSlug(): string
    {
        return 'google_sheets/get_rows';
    }

    public static function getName(): string
    {
        return __('Get Rows', 'easylink-automations');
    }

    public static function getDescription(): string
    {
        return __('Read rows from a Google Sheet, optionally filtered by a column value', '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',
            ],
            'filter_column' => [
                'label'         => 'Filter Column (optional)',
                'type'          => 'remote_select',
                'required'      => false,
                'placeholder'   => 'Select a column to filter by...',
                'remote_source' => 'google-sheets/columns',
                'depends_on'    => ['connection_id', 'spreadsheet_id', 'sheet_name', 'header_row'],
                'options_key'   => 'columns',
                'description'   => 'Return only rows where this column matches the filter value',
            ],
            'filter_value' => [
                'label'            => 'Filter Value (optional)',
                'type'             => 'text',
                'required'         => false,
                'placeholder'      => '{{user_email}}',
                'supports_mapping' => true,
                'description'      => 'The value to match in the filter column',
            ],
            'max_rows' => [
                'label'       => 'Maximum Rows',
                'type'        => 'number',
                'required'    => false,
                'placeholder' => 'e.g. 50',
                'description' => 'Limit the number of rows returned. Leave empty for all matching rows.',
            ],
        ];
    }

    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));
        $filterColumn  = (string) ($mapped['filter_column'] ?? '');
        $filterValue   = (string) ($mapped['filter_value'] ?? '');
        $maxRows       = (int) ($mapped['max_rows'] ?? 0);

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

        $headers      = $values[$headerRow - 1] ?? [];
        $filterIndex  = $filterColumn !== '' ? array_search($filterColumn, $headers, true) : false;
        $needle       = trim($filterValue);
        $useFilter    = $filterColumn !== '' && $filterValue !== '' && $filterIndex !== false;

        $rows = [];
        foreach ($values as $rowIndex => $row) {
            if ($rowIndex < $headerRow) {
                continue;
            }

            if ($useFilter && trim((string) ($row[$filterIndex] ?? '')) !== $needle) {
                continue;
            }

            // Skip fully empty rows.
            if (count(array_filter($row, static fn($cell) => trim((string) $cell) !== '')) === 0) {
                continue;
            }

            $assoc = [];
            foreach ($headers as $colIndex => $header) {
                $key = trim((string) $header);
                if ($key === '') {
                    continue;
                }
                $assoc[$key] = $row[$colIndex] ?? '';
            }
            $assoc['_row_number'] = $rowIndex + 1;

            $rows[] = $assoc;

            if ($maxRows > 0 && count($rows) >= $maxRows) {
                break;
            }
        }

        $count = count($rows);

        return [
            'success' => true,
            'message' => $count === 1 ? '1 row retrieved' : "{$count} rows retrieved",
            'data' => [
                'spreadsheet_id' => $spreadsheetId,
                'count'          => $count,
                'rows'           => $rows,
                'first_row'      => $rows[0] ?? [],
            ],
        ];
    }
}
