<?php
namespace PSSG_Sync_Sheet\App\Http;

use PSSG_Sync_Sheet\App\Service\Products;
use PSSG_Sync_Sheet\App\Service\Standalone;

/**
 * Sheet_Style
 *
 * Applies visual formatting to the Google Sheet via the
 * Sheets API batchUpdate endpoint.
 *
 * All color and style settings live as static properties at the
 * top of this class — change them in one place to update every
 * cell that uses that setting.
 *
 * Automatically triggered after the first-page product sync
 * via the 'pssg_after_sheet_first_sync' action hook.
 *
 * @since   2.0.3
 * @package PSSG_Sync_Sheet
 */
class Sheet_Style {

    use Standalone;

    // =========================================================
    // STYLE CONFIGURATION
    // Edit values below to customise colors / font styles.
    // =========================================================

    /**
     * Background color for the entire header row (row 1).
     *
     * @var string Hex color, e.g. '#673AB7'
     */
    protected static $header_bg_color = '#673AB7';

    /**
     * Font (foreground) color for the header row.
     *
     * @var string Hex color
     */
    protected static $header_font_color = '#FFFFFF';

    /**
     * Whether to bold all text in the header row.
     *
     * @var bool
     */
    protected static $header_bold = true;

    // --- Off-white background columns (data rows only) -------

    /**
     * Column KEYS (as returned by Products::get_columns()) that
     * should receive an off-white background in the data rows
     * (row 2 and below — the header cell is handled separately).
     *
     * @var array
     */
    protected static $off_white_columns = [ 'ID', 'type' ];

    /**
     * Off-white background color applied to those data-row cells.
     *
     * @var string Hex color
     */
    protected static $off_white_color = '#F2F2F2';

    // --- Header cells that override the purple with red ------

    /**
     * Column KEYS whose header cell (row 1) should have a red
     * background, overriding the default purple header color.
     *
     * Add or remove keys here to control which header cells are red.
     * Keys that are not present in the current active columns are
     * silently skipped.
     * 'ID', 'type','_post_status','_permalink', 'post_edit_link'
     *
     * @var array
     */
    protected static $restricted_columns = [
        'ID',               // ID column header          → red
        'type',             // Type column header         → red
        'total_sales',      // Sales_Count column header  → red
        '_post_status',         // Post URL column header     → red (premium)
        '_permalink',         // Edit URL column header     → red (premium)
        'post_edit_link', // Edit URL key variant       → red (premium)
        // '_thumbnail',         // Thumbnail column header     → red (premium)
    ];

    /**
     * Background color for the red header cells.
     *
     * @var string Hex color
     */
    protected static $red_header_bg_color = '#E53935';

    /**
     * Font color for the red header cells.
     *
     * @var string Hex color
     */
    protected static $red_header_font_color = '#FFFFFF';

    // --- Stock column conditional formatting -----------------

    /**
     * Column KEY for the Stock column in Products::get_columns().
     *
     * @var string
     */
    protected static $stock_col_key = 'stock';

    /**
     * Conditional formatting rules for the Stock column.
     *
     * 'in_stock' and 'out_of_stock' use a TEXT_EQ condition.
     * 'number' uses CUSTOM_FORMULA =ISNUMBER(...) to target
     * cells that contain a numeric stock quantity.
     *
     * Keys:
     *   text     — exact string to match (TEXT_EQ rules only)
     *   fg_color — text (foreground) color in hex
     *   bold     — whether to bold the text
     *
     * @var array
     */
    protected static $stock_rules = [
        'in_stock' => [
            'text'     => 'In stock',
            'fg_color' => '#1E8E3E',  // green
            'bold'     => true,   // Stock column is always bold
        ],
        'out_of_stock' => [
            'text'     => 'Out of stock',
            'fg_color' => '#D93025',  // red
            'bold'     => true,   // Stock column is always bold
        ],
        'number' => [
            // Applied when the cell is numeric (ISNUMBER formula).
            'fg_color' => '#000000',  // black
            'bold'     => true,
        ],
    ];

    /**
     * Last data row index (exclusive, 0-based) for range-based
     * formatting requests (column backgrounds and conditional rules).
     * 1000 covers rows 2–1000 in the UI.
     *
     * Increase this if your sheet regularly has more than ~1000 rows.
     *
     * @var int
     */
    protected static $max_row_index = 1000;

    // --- Type column conditional formatting ------------------

    /**
     * Column KEY for the Type column in Products::get_columns().
     *
     * @var string
     */
    protected static $type_col_key = 'type';

    /**
     * Per-type text and background color rules for the Type column.
     *
     * Keys per entry:
     *   fg_color — text (foreground) color in hex
     *   bg_color — cell background color in hex
     *   bold     — whether to bold the text
     *
     * 'others' is matched via CUSTOM_FORMULA and applies to any value
     * that is not blank and does not match the named types above it.
     * Add or remove entries here to control which types get custom styling.
     *
     * @var array
     */
    protected static $type_rules = [
        'simple'    => ['fg_color' => '#1565C0', 'bg_color' => '#E3F2FD', 'bold' => false], // blue
        'variable'  => ['fg_color' => '#6A1B9A', 'bg_color' => '#F3E5F5', 'bold' => false], // purple
        'variation' => ['fg_color' => '#4527A0', 'bg_color' => '#EDE7F6', 'bold' => false], // violet
        'grouped'   => ['fg_color' => '#2E7D32', 'bg_color' => '#E8F5E9', 'bold' => false], // green
        // 'others' must remain last — it uses CUSTOM_FORMULA to catch everything else.
        'others'    => ['fg_color' => '#795548', 'bg_color' => '#EFEBE9', 'bold' => false], // brown
    ];

    // --- Sales_Count column data-row background --------------

    /**
     * Column KEY for the Sales_Count column (registered by Sales_Handle).
     *
     * @var string
     */
    protected static $sales_count_col_key = 'total_sales';

    /**
     * Background color for the Sales_Count data rows (row 2 and below).
     * The header cell color is already handled via $restricted_columns.
     *
     * @var string Hex color
     */
    protected static $sales_count_data_bg_color = '#FFEBEE'; // light red

    // --- Alternating row stripes (banding) -------------------

    /**
     * Background color for the first band (rows 2, 4, 6, …) in the
     * alternating-stripe pattern applied to the data rows.
     *
     * @var string Hex color
     */
    protected static $stripe_first_color = '#FFFFFF'; // white

    /**
     * Background color for the second band (rows 3, 5, 7, …).
     *
     * @var string Hex color
     */
    protected static $stripe_second_color = '#F5F5F5'; // light gray

    /**
     * wp_options key prefix used to persist the banding range ID between
     * syncs so the old banding can be deleted before a new one is added.
     * The actual key appends a short hash of the spreadsheet + sheet IDs.
     *
     * @var string
     */
    protected static $banding_option_key_prefix = 'pssg_sheet_style_banding_id_';

    // =========================================================
    // END OF STYLE CONFIGURATION
    // =========================================================

    /**
     * Constructor: register the action hook so styles are applied
     * automatically after the first page of products is synced.
     */
    public function __construct() {
        add_action( 'pssg_after_sheet_first_sync', [ $this, 'on_first_sync' ], 10, 1 );
    }

    /**
     * Action callback for 'pssg_after_sheet_first_sync'.
     *
     * @param Sheet $sheet Fully initialised Sheet instance.
     * @return void
     */
    public function on_first_sync( $sheet ) {
        if ( ! ( $sheet instanceof Sheet ) ) {
            return;
        }
        $this->apply_styles( $sheet );
    }

    /**
     * Load style configuration from pssg_config (wp_options) and
     * override static class properties so every formatting request
     * uses the user's saved values instead of the hardcoded defaults.
     *
     * Config keys stored flat in pssg_config:
     *   header_bg_color, header_font_color, header_bold,
     *   off_white_color, red_header_bg_color, red_header_font_color,
     *   stripe_first_color, stripe_second_color, sales_count_data_bg_color
     *
     * Type rules stored as:
     *   type_rule_{type}_fg  e.g. type_rule_simple_fg  → '#1565C0'
     *   type_rule_{type}_bg  e.g. type_rule_simple_bg  → '#E3F2FD'
     *   type_rule_{type}_bold e.g. type_rule_simple_bold → '1' / ''
     *
     * @return void
     */
    protected function load_config() {
        $cfg = get_option( 'pssg_config', [] );
        if ( empty( $cfg ) ) {
            return;
        }

        $map = [
            'header_bg_color'           => 'header_bg_color',
            'header_font_color'         => 'header_font_color',
            'off_white_color'           => 'off_white_color',
            'red_header_bg_color'       => 'red_header_bg_color',
            'red_header_font_color'     => 'red_header_font_color',
            'stripe_first_color'        => 'stripe_first_color',
            'stripe_second_color'       => 'stripe_second_color',
            'sales_count_data_bg_color' => 'sales_count_data_bg_color',
        ];

        foreach ( $map as $cfg_key => $prop ) {
            if ( ! empty( $cfg[ $cfg_key ] ) ) {
                static::$$prop = sanitize_hex_color( $cfg[ $cfg_key ] ) ?: static::$$prop;
            }
        }

        // header_bold — checkbox sends '1' when checked, absent when unchecked.
        if ( array_key_exists( 'header_bold', $cfg ) ) {
            static::$header_bold = ! empty( $cfg['header_bold'] );
        }

        // Type column per-type rules.
        $type_keys = [ 'simple', 'variable', 'variation', 'grouped', 'others' ];
        foreach ( $type_keys as $t ) {
            if ( ! empty( $cfg[ "type_rule_{$t}_fg" ] ) ) {
                static::$type_rules[ $t ]['fg_color'] = sanitize_hex_color( $cfg[ "type_rule_{$t}_fg" ] ) ?: static::$type_rules[ $t ]['fg_color'];
            }
            if ( ! empty( $cfg[ "type_rule_{$t}_bg" ] ) ) {
                static::$type_rules[ $t ]['bg_color'] = sanitize_hex_color( $cfg[ "type_rule_{$t}_bg" ] ) ?: static::$type_rules[ $t ]['bg_color'];
            }
            if ( array_key_exists( "type_rule_{$t}_bold", $cfg ) ) {
                static::$type_rules[ $t ]['bold'] = ! empty( $cfg[ "type_rule_{$t}_bold" ] );
            }
        }
    }

    /**
     * Build and send all formatting requests in a single batchUpdate call.
     *
     * Request order (later requests override earlier ones for the same cells):
     *
     *   0. Freeze the header row and the first two columns (ID, Type).
     *   1. Delete ALL existing banding ranges on the sheet tab (fetched live
     *      from the API so stale wp_options values cannot cause failures),
     *      then add fresh alternating row stripes.
     *   2. Purple background + white bold text for the full header row.
     *   3. Off-white background for ID / Type column data rows.
     *   4. Light-red background for Sales_Count column data rows.
     *   5. Bold base format for all Stock column data rows.
     *   6. Red background for specific header cells (overrides purple).
     *   7. Conditional text-color rules for the Stock column.
     *   8. Conditional text + background color rules for the Type column.
     *
     * @param Sheet $sheet Fully initialised Sheet instance.
     * @return array Google Sheets API response array.
     */
    public function apply_styles( Sheet $sheet ) {

        // Load user-saved style config from pssg_config (overrides static defaults).
        $this->load_config();

        if ( ! $sheet->configured ) {
            return [ 'status' => 'failed', 'error' => 'sheet_not_configured' ];
        }

        // Resolve the numeric sheet tab ID (GID).
        $sheet_info = $sheet->get_current_sheet_name();
        $sheet_id   = isset( $sheet_info['gid'] ) ? intval( $sheet_info['gid'] ) : 0;

        // Get the active column list to resolve column indexes dynamically.
        $products   = new Products();
        $columns    = $products->get_active_columns();
        $col_keys   = array_keys( $columns );
        $total_cols = count( $col_keys );

        $requests = [];

        // ----------------------------------------------------------
        // Step 0: Freeze header row + first two columns (ID, Type).
        // ----------------------------------------------------------
        $requests[] = $this->build_freeze_request( $sheet_id );

        // ----------------------------------------------------------
        // Step 1: Alternating row stripes (banding).
        // Query the Sheets API for ALL banding IDs currently on this
        // sheet tab and delete each one before adding a fresh banding.
        // This avoids the "already has alternating background colors"
        // error caused by stale or missing stored IDs.
        // ----------------------------------------------------------
        $existing_banding_ids = $this->get_existing_banding_ids( $sheet, $sheet_id );
        foreach ( $existing_banding_ids as $bid ) {
            $requests[] = [ 'deleteBanding' => [ 'bandedRangeId' => $bid ] ];
        }
        $requests[] = $this->build_banding_request( $sheet_id, $total_cols );

        // ----------------------------------------------------------
        // Step 2: Purple background + white bold text — full header row.
        // Placed after banding so it overrides banding on row 1.
        // ----------------------------------------------------------
        $requests[] = $this->build_full_row_format_request(
            $sheet_id,
            0,           // startRowIndex (0-based) → row 1 in the UI
            1,           // endRowIndex   (exclusive)
            $total_cols,
            static::$header_bg_color,
            static::$header_font_color,
            static::$header_bold
        );

        // ----------------------------------------------------------
        // Step 3: Off-white background for ID / Type column data rows
        // (rows 2 → max_row_index).  Overrides banding for those columns.
        // ----------------------------------------------------------
        foreach ( static::$off_white_columns as $col_key ) {
            $col_index = array_search( $col_key, $col_keys, true );
            if ( $col_index === false ) {
                continue; // Column is not active — skip silently.
            }
            $requests[] = $this->build_column_bg_request(
                $sheet_id,
                $col_index,
                1,                        // startRowIndex → row 2 in UI (skip header)
                static::$max_row_index,
                static::$off_white_color
            );
        }

        // ----------------------------------------------------------
        // Step 4: Light-red background for Sales_Count data rows.
        // Overrides banding for that column.
        // ----------------------------------------------------------
        $sales_col_index = array_search( static::$sales_count_col_key, $col_keys, true );
        if ( $sales_col_index !== false ) {
            $requests[] = $this->build_column_bg_request(
                $sheet_id,
                $sales_col_index,
                1,
                static::$max_row_index,
                static::$sales_count_data_bg_color
            );
        }

        // ----------------------------------------------------------
        // Step 5: Bold base format for all Stock column data rows.
        // This ensures every Stock value is bold regardless of which
        // conditional rule (if any) applies to it.
        // ----------------------------------------------------------
        $stock_col_index = array_search( static::$stock_col_key, $col_keys, true );
        if ( $stock_col_index !== false ) {
            $requests[] = $this->build_stock_bold_request( $sheet_id, $stock_col_index );
        }

        // ----------------------------------------------------------
        // Step 6: Red background + white bold text — specific header
        // cells.  These override the purple set in Step 2.
        // ----------------------------------------------------------
        foreach ( static::$restricted_columns as $col_key ) {
            $col_index = array_search( $col_key, $col_keys, true );
            if ( $col_index === false ) {
                continue; // Column is not active — skip silently.
            }
            $requests[] = $this->build_full_row_format_request(
                $sheet_id,
                0,
                1,
                $total_cols,   // endColumnIndex is overridden below
                static::$red_header_bg_color,
                static::$red_header_font_color,
                true           // always bold for red header cells
            );
            // Narrow the range to a single cell.
            $last_idx = count( $requests ) - 1;
            $requests[ $last_idx ]['repeatCell']['range']['startColumnIndex'] = $col_index;
            $requests[ $last_idx ]['repeatCell']['range']['endColumnIndex']   = $col_index + 1;
        }

        // ----------------------------------------------------------
        // Step 7: Stock column conditional formatting.
        // "In stock" → green bold | "Out of stock" → red bold | number → black bold.
        // ----------------------------------------------------------
        if ( $stock_col_index !== false ) {
            $stock_col_letter = chr( 65 + $stock_col_index );
            foreach ( $this->build_stock_conditional_requests( $sheet_id, $stock_col_index, $stock_col_letter ) as $req ) {
                $requests[] = $req;
            }
        }

        // ----------------------------------------------------------
        // Step 8: Type column conditional formatting.
        // simple/variable/variation/grouped/others → each gets its own
        // text color and background color.
        // ----------------------------------------------------------
        $type_col_index = array_search( static::$type_col_key, $col_keys, true );
        if ( $type_col_index !== false ) {
            $type_col_letter = chr( 65 + $type_col_index );
            foreach ( $this->build_type_conditional_requests( $sheet_id, $type_col_index, $type_col_letter ) as $req ) {
                $requests[] = $req;
            }
        }

        if ( empty( $requests ) ) {
            return [ 'status' => 'failed', 'error' => 'no_style_requests_built' ];
        }

        // Push steps 0–8 first, then handle protected ranges in a
        // separate batchUpdate so we can safely delete stale ranges
        // before adding fresh ones (deletions and additions of the
        // same resource cannot live in the same request array).
        $style_result = $this->push_style_batch( $sheet, [ 'requests' => $requests ] );

        // ----------------------------------------------------------
        // Step 9: Protect restricted columns so editing triggers a
        // warning dialog: "Cell Edit is Restricted".
        // Existing protected ranges on this sheet tab are removed
        // first to prevent duplicates when syncing more than once.
        // ----------------------------------------------------------
        // $protection_requests = [];

        // $existing_protected_ids = $this->get_existing_protected_range_ids( $sheet, $sheet_id );
        // foreach ( $existing_protected_ids as $pid ) {
        //     $protection_requests[] = [ 'deleteProtectedRange' => [ 'protectedRangeId' => $pid ] ];
        // }

        // foreach ( $this->build_protected_range_requests( $sheet_id, $col_keys ) as $req ) {
        //     $protection_requests[] = $req;
        // }

        // if ( ! empty( $protection_requests ) ) {
        //     $this->push_style_batch( $sheet, [ 'requests' => $protection_requests ] );
        // }

        return $style_result;
    }

    // =========================================================
    // Request builders
    // =========================================================

    /**
     * Build a repeatCell request that applies a background color
     * and text format to every cell in a contiguous row range,
     * spanning all columns from 0 to $total_cols.
     *
     * @param int    $sheet_id        Numeric Google Sheet tab ID.
     * @param int    $start_row_index 0-based start row (inclusive).
     * @param int    $end_row_index   0-based end row (exclusive).
     * @param int    $total_cols      Number of columns to cover.
     * @param string $bg_hex          Background color in hex.
     * @param string $fg_hex          Font color in hex.
     * @param bool   $bold            Whether to bold the text.
     * @return array
     */
    protected function build_full_row_format_request(
        $sheet_id,
        $start_row_index,
        $end_row_index,
        $total_cols,
        $bg_hex,
        $fg_hex,
        $bold = false
    ) {
        return [
            'repeatCell' => [
                'range' => [
                    'sheetId'          => $sheet_id,
                    'startRowIndex'    => $start_row_index,
                    'endRowIndex'      => $end_row_index,
                    'startColumnIndex' => 0,
                    'endColumnIndex'   => $total_cols,
                ],
                'cell' => [
                    'userEnteredFormat' => [
                        'backgroundColor' => $this->hex_to_color_object( $bg_hex ),
                        'textFormat'      => [
                            'foregroundColor' => $this->hex_to_color_object( $fg_hex ),
                            'bold'            => (bool) $bold,
                        ],
                    ],
                ],
                'fields' => 'userEnteredFormat(backgroundColor,textFormat)',
            ],
        ];
    }

    /**
     * Build a repeatCell request that sets bold=true for every
     * data cell in the Stock column (rows 2 → max_row_index).
     *
     * This is the base format so bold applies even to cells that
     * are not matched by any conditional format rule.
     *
     * @param int $sheet_id  Numeric Google Sheet tab ID.
     * @param int $col_index 0-based column index for the Stock column.
     * @return array
     */
    protected function build_stock_bold_request( $sheet_id, $col_index ) {
        return [
            'repeatCell' => [
                'range' => [
                    'sheetId'          => $sheet_id,
                    'startRowIndex'    => 1,                   // row 2 in UI (skip header)
                    'endRowIndex'      => static::$max_row_index,
                    'startColumnIndex' => $col_index,
                    'endColumnIndex'   => $col_index + 1,
                ],
                'cell'   => [
                    'userEnteredFormat' => [
                        'textFormat' => [ 'bold' => true ],
                    ],
                ],
                'fields' => 'userEnteredFormat.textFormat.bold',
            ],
        ];
    }

    /**
     * Build an addBanding request that applies alternating row colors
     * (stripe_first_color / stripe_second_color) to the data rows.
     *
     * The header row (index 0) is excluded so it does not inherit the
     * banding colors and the purple header format remains intact.
     *
     * @param int $sheet_id  Numeric Google Sheet tab ID.
     * @param int $total_cols Total number of active columns.
     * @return array
     */
    protected function build_banding_request( $sheet_id, $total_cols ) {
        return [
            'addBanding' => [
                'bandedRange' => [
                    'range' => [
                        'sheetId'          => $sheet_id,
                        'startRowIndex'    => 1,                   // row 2 in UI (skip header)
                        'endRowIndex'      => static::$max_row_index,
                        'startColumnIndex' => 0,
                        'endColumnIndex'   => $total_cols,
                    ],
                    'rowProperties' => [
                        // firstBandColor  → rows 2, 4, 6, …
                        'firstBandColor'  => $this->hex_to_color_object( static::$stripe_first_color ),
                        // secondBandColor → rows 3, 5, 7, …
                        'secondBandColor' => $this->hex_to_color_object( static::$stripe_second_color ),
                    ],
                ],
            ],
        ];
    }

    /**
     * Build an updateSheetProperties request that freezes the first
     * row (header) and the first two columns (ID and Type) so they
     * remain visible while scrolling horizontally or vertically.
     *
     * ID and Type are always the first two columns in this plugin, so
     * frozenColumnCount is hard-coded to 2.
     *
     * @param int $sheet_id Numeric Google Sheet tab ID.
     * @return array
     */
    protected function build_freeze_request( $sheet_id ) {
        return [
            'updateSheetProperties' => [
                'properties' => [
                    'sheetId'        => $sheet_id,
                    'gridProperties' => [
                        'frozenRowCount'    => 1, // Freeze header row
                        'frozenColumnCount' => 2, // Freeze ID + Type columns
                    ],
                ],
                'fields' => 'gridProperties.frozenRowCount,gridProperties.frozenColumnCount',
            ],
        ];
    }

    /**
     * Fetch all banding range IDs that currently exist on a specific
     * sheet tab by querying the Sheets API directly.
     *
     * This is used instead of a cached wp_options value so that the
     * list is always accurate — even when the option is missing, stale,
     * or was never stored (e.g. first sync that failed mid-way).
     *
     * @param Sheet $sheet    Sheet instance (provides credentials and GET helper).
     * @param int   $sheet_id Numeric sheet tab GID to filter by.
     * @return int[]          Array of banding range IDs; empty when none found.
     */
    protected function get_existing_banding_ids( Sheet $sheet, $sheet_id ) {
        $spreadsheet_id = $sheet->spreadsheet_id;
        $api_key        = $sheet->API_KEY;
        $api_url        = "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheet_id}"
                        . '?fields=sheets(properties.sheetId,bandedRanges.bandedRangeId)'
                        . "&key={$api_key}";

        $result = $sheet->get_from_sheet( $api_url );

        if ( empty( $result['data_response']->sheets ) ) {
            return [];
        }

        $ids = [];
        foreach ( $result['data_response']->sheets as $sheet_obj ) {
            if ( isset( $sheet_obj->properties->sheetId )
                 && intval( $sheet_obj->properties->sheetId ) === $sheet_id
            ) {
                foreach ( $sheet_obj->bandedRanges ?? [] as $br ) {
                    if ( isset( $br->bandedRangeId ) ) {
                        $ids[] = intval( $br->bandedRangeId );
                    }
                }
                break;
            }
        }

        return $ids;
    }

    /**
     * Build addConditionalFormatRule requests for the Type column.
     *
     * One TEXT_EQ rule is created for each named type (simple, variable,
     * variation, grouped).  The 'others' rule uses CUSTOM_FORMULA to
     * catch any non-blank value that does not match those four types.
     *
     * Rules are inserted at increasing indexes so Google Sheets evaluates
     * them in the declared order.
     *
     * @param int    $sheet_id         Numeric Google Sheet tab ID.
     * @param int    $col_index        0-based column index for the Type column.
     * @param string $col_letter       Column letter (e.g. 'B') for the CUSTOM_FORMULA.
     * @return array Array of addConditionalFormatRule request objects.
     */
    protected function build_type_conditional_requests( $sheet_id, $col_index, $col_letter ) {

        $range = [
            'sheetId'          => $sheet_id,
            'startRowIndex'    => 1,                    // row 2 in UI (skip header)
            'endRowIndex'      => static::$max_row_index,
            'startColumnIndex' => $col_index,
            'endColumnIndex'   => $col_index + 1,
        ];

        $rules    = static::$type_rules;
        $requests = [];
        $index    = 0;

        // Named types — TEXT_EQ condition.
        $named_types = [ 'simple', 'variable', 'variation', 'grouped' ];
        foreach ( $named_types as $type_key ) {
            if ( ! isset( $rules[ $type_key ] ) ) {
                continue;
            }
            $rule = $rules[ $type_key ];
            $requests[] = [
                'addConditionalFormatRule' => [
                    'rule'  => [
                        'ranges'      => [ $range ],
                        'booleanRule' => [
                            'condition' => [
                                'type'   => 'TEXT_EQ',
                                'values' => [ [ 'userEnteredValue' => $type_key ] ],
                            ],
                            'format' => [
                                'backgroundColor' => $this->hex_to_color_object( $rule['bg_color'] ),
                                'textFormat'      => [
                                    'foregroundColor' => $this->hex_to_color_object( $rule['fg_color'] ),
                                    'bold'            => (bool) $rule['bold'],
                                ],
                            ],
                        ],
                    ],
                    'index' => $index++,
                ],
            ];
        }

        // 'others' — CUSTOM_FORMULA to catch any non-blank, unrecognised type.
        // References the first data cell in the range (row 2 in the UI).
        if ( isset( $rules['others'] ) ) {
            $rule = $rules['others'];
            // Build a NOT(EXACT(...)) chain for every named type.
            $not_conditions = implode(
                ',',
                array_map(
                    fn( $t ) => "NOT(EXACT({$col_letter}2,\"{$t}\"))",
                    $named_types
                )
            );
            $formula = "=AND({$col_letter}2<>\"\",{$not_conditions})";

            $requests[] = [
                'addConditionalFormatRule' => [
                    'rule'  => [
                        'ranges'      => [ $range ],
                        'booleanRule' => [
                            'condition' => [
                                'type'   => 'CUSTOM_FORMULA',
                                'values' => [ [ 'userEnteredValue' => $formula ] ],
                            ],
                            'format' => [
                                'backgroundColor' => $this->hex_to_color_object( $rule['bg_color'] ),
                                'textFormat'      => [
                                    'foregroundColor' => $this->hex_to_color_object( $rule['fg_color'] ),
                                    'bold'            => (bool) $rule['bold'],
                                ],
                            ],
                        ],
                    ],
                    'index' => $index,
                ],
            ];
        }

        return $requests;
    }

    /**
     * Build a repeatCell request that sets the background color
     * for a single column across a row range.
     *
     * @param int    $sheet_id        Numeric Google Sheet tab ID.
     * @param int    $col_index       0-based column index.
     * @param int    $start_row_index 0-based start row index (inclusive).
     * @param int    $end_row_index   0-based end row index (exclusive).
     * @param string $bg_hex          Background color in hex.
     * @return array
     */
    protected function build_column_bg_request( $sheet_id, $col_index, $start_row_index, $end_row_index, $bg_hex ) {
        return [
            'repeatCell' => [
                'range' => [
                    'sheetId'          => $sheet_id,
                    'startRowIndex'    => $start_row_index,
                    'endRowIndex'      => $end_row_index,
                    'startColumnIndex' => $col_index,
                    'endColumnIndex'   => $col_index + 1,
                ],
                'cell' => [
                    'userEnteredFormat' => [
                        'backgroundColor' => $this->hex_to_color_object( $bg_hex ),
                    ],
                ],
                'fields' => 'userEnteredFormat.backgroundColor',
            ],
        ];
    }

    /**
     * Build addConditionalFormatRule requests for the Stock column.
     *
     * Three rules are created (in priority order):
     *   index 0 — TEXT_EQ "In stock"     → green text
     *   index 1 — TEXT_EQ "Out of stock" → red text
     *   index 2 — CUSTOM_FORMULA ISNUMBER → black bold text (numeric quantity)
     *
     * Note: Calling batchUpdate multiple times (e.g. syncing again after a
     * clear) will accumulate conditional format rules. The rules themselves
     * are identical, so formatting remains correct. To fully reset, clear
     * the sheet and re-sync.
     *
     * @param int    $sheet_id         Numeric Google Sheet tab ID.
     * @param int    $col_index        0-based column index for Stock.
     * @param string $col_letter       Column letter for the ISNUMBER formula, e.g. 'D'.
     * @return array Array of addConditionalFormatRule request objects.
     */
    protected function build_stock_conditional_requests( $sheet_id, $col_index, $col_letter ) {

        // Range: data rows only (skip header at row index 0).
        $range = [
            'sheetId'          => $sheet_id,
            'startRowIndex'    => 1,
            'endRowIndex'      => static::$max_row_index,
            'startColumnIndex' => $col_index,
            'endColumnIndex'   => $col_index + 1,
        ];

        $rules    = static::$stock_rules;
        $requests = [];

        // Rule 0: "In stock" → green text.
        $requests[] = [
            'addConditionalFormatRule' => [
                'rule'  => [
                    'ranges'      => [ $range ],
                    'booleanRule' => [
                        'condition' => [
                            'type'   => 'TEXT_EQ',
                            'values' => [ [ 'userEnteredValue' => $rules['in_stock']['text'] ] ],
                        ],
                        'format' => [
                            'textFormat' => [
                                'foregroundColor' => $this->hex_to_color_object( $rules['in_stock']['fg_color'] ),
                                'bold'            => (bool) $rules['in_stock']['bold'],
                            ],
                        ],
                    ],
                ],
                'index' => 0,
            ],
        ];

        // Rule 1: "Out of stock" → red text.
        $requests[] = [
            'addConditionalFormatRule' => [
                'rule'  => [
                    'ranges'      => [ $range ],
                    'booleanRule' => [
                        'condition' => [
                            'type'   => 'TEXT_EQ',
                            'values' => [ [ 'userEnteredValue' => $rules['out_of_stock']['text'] ] ],
                        ],
                        'format' => [
                            'textFormat' => [
                                'foregroundColor' => $this->hex_to_color_object( $rules['out_of_stock']['fg_color'] ),
                                'bold'            => (bool) $rules['out_of_stock']['bold'],
                            ],
                        ],
                    ],
                ],
                'index' => 1,
            ],
        ];

        // Rule 2: Numeric stock quantity → black bold text.
        // CUSTOM_FORMULA references the first data cell in the range (row 2 in UI = index 1).
        $requests[] = [
            'addConditionalFormatRule' => [
                'rule'  => [
                    'ranges'      => [ $range ],
                    'booleanRule' => [
                        'condition' => [
                            'type'   => 'CUSTOM_FORMULA',
                            'values' => [ [ 'userEnteredValue' => "=ISNUMBER({$col_letter}2)" ] ],
                        ],
                        'format' => [
                            'textFormat' => [
                                'foregroundColor' => $this->hex_to_color_object( $rules['number']['fg_color'] ),
                                'bold'            => (bool) $rules['number']['bold'],
                            ],
                        ],
                    ],
                ],
                'index' => 2,
            ],
        ];

        return $requests;
    }

    // =========================================================
    // Protected-range helpers
    // =========================================================

    /**
     * Fetch all protected-range IDs that currently exist on a specific
     * sheet tab by querying the Sheets API directly.
     *
     * Used to delete stale protected ranges before adding fresh ones,
     * so re-syncing never accumulates duplicate protections.
     *
     * @param Sheet $sheet    Sheet instance (provides credentials and GET helper).
     * @param int   $sheet_id Numeric sheet tab GID to filter by.
     * @return int[]          Array of protected range IDs; empty when none found.
     */
    protected function get_existing_protected_range_ids( Sheet $sheet, $sheet_id ) {
        $spreadsheet_id = $sheet->spreadsheet_id;
        $api_key        = $sheet->API_KEY;
        $api_url        = "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheet_id}"
                        . '?fields=sheets(properties.sheetId,protectedRanges.protectedRangeId)'
                        . "&key={$api_key}";

        $result = $sheet->get_from_sheet( $api_url );

        if ( empty( $result['data_response']->sheets ) ) {
            return [];
        }

        $ids = [];
        foreach ( $result['data_response']->sheets as $sheet_obj ) {
            if ( isset( $sheet_obj->properties->sheetId )
                 && intval( $sheet_obj->properties->sheetId ) === $sheet_id
            ) {
                foreach ( $sheet_obj->protectedRanges ?? [] as $pr ) {
                    if ( isset( $pr->protectedRangeId ) ) {
                        $ids[] = intval( $pr->protectedRangeId );
                    }
                }
                break;
            }
        }

        return $ids;
    }

    /**
     * Build addProtectedRange requests for each restricted column.
     *
     * Each request uses warningOnly = true so that any user who tries
     * to edit one of these cells sees a warning dialog from Google Sheets
     * before the edit is applied.  The description field stores the custom
     * message "Cell Edit is Restricted", which is also displayed inside
     * the protected-ranges panel (Data → Protected sheets and ranges).
     *
     * Columns that are not present in the current active column list are
     * silently skipped (same behaviour as Step 6 for red header cells).
     *
     * @param int      $sheet_id  Numeric Google Sheet tab ID.
     * @param string[] $col_keys  Ordered array of active column keys.
     * @return array              Array of addProtectedRange request objects.
     */
    protected function build_protected_range_requests( $sheet_id, array $col_keys ) {
        $requests = [];

        // ----------------------------------------------------------
        // 1. Protect the entire header row (row 1 in the UI).
        //    No column header text should be editable, regardless of
        //    whether the column is restricted or not.
        // ----------------------------------------------------------
        $requests[] = [
            'addProtectedRange' => [
                'protectedRange' => [
                    'range' => [
                        'sheetId'          => $sheet_id,
                        'startRowIndex'    => 0,              // row 1 in UI (header)
                        'endRowIndex'      => 1,              // only the single header row
                        'startColumnIndex' => 0,
                        'endColumnIndex'   => count( $col_keys ),
                    ],
                    'description' => 'Cell Edit is Restricted',
                    'warningOnly' => true,
                ],
            ],
        ];

        // ----------------------------------------------------------
        // 2. Protect each restricted column's data rows (row 2 →
        //    max_row_index).  Row 1 is already covered above.
        // ----------------------------------------------------------
        foreach ( static::$restricted_columns as $col_key ) {
            $col_index = array_search( $col_key, $col_keys, true );
            if ( $col_index === false ) {
                continue; // Column is not active — skip silently.
            }

            $requests[] = [
                'addProtectedRange' => [
                    'protectedRange' => [
                        'range' => [
                            'sheetId'          => $sheet_id,
                            'startRowIndex'    => 1,                    // row 2 in UI (skip header, already protected above)
                            'endRowIndex'      => static::$max_row_index,
                            'startColumnIndex' => $col_index,
                            'endColumnIndex'   => $col_index + 1,
                        ],
                        'description' => 'Cell Edit is Restricted',
                        'warningOnly' => true,
                    ],
                ],
            ];
        }

        return $requests;
    }

    // =========================================================
    // Utility helpers
    // =========================================================

    /**
     * Convert a hex color string to a Google Sheets API color object.
     *
     * The Sheets API expects color components as floats between 0.0 and 1.0.
     *
     * @param string $hex  Hex color with or without leading '#', e.g. '#673AB7'.
     * @return array       e.g. [ 'red' => 0.404, 'green' => 0.227, 'blue' => 0.718 ]
     */
    protected function hex_to_color_object( $hex ) {
        $hex = ltrim( $hex, '#' );

        // Expand 3-char shorthand to 6 chars: 'FFF' → 'FFFFFF'.
        if ( strlen( $hex ) === 3 ) {
            $hex = $hex[0] . $hex[0] . $hex[1] . $hex[1] . $hex[2] . $hex[2];
        }

        return [
            'red'   => round( hexdec( substr( $hex, 0, 2 ) ) / 255, 6 ),
            'green' => round( hexdec( substr( $hex, 2, 2 ) ) / 255, 6 ),
            'blue'  => round( hexdec( substr( $hex, 4, 2 ) ) / 255, 6 ),
        ];
    }

    /**
     * Send a batchUpdate request (formatting only) to the Sheets API.
     *
     * Reuses Sheet::push_to_sheet() to keep auth/error handling consistent.
     *
     * @param Sheet $sheet Sheet instance (provides credentials and push method).
     * @param array $data  Full request body, must contain a 'requests' key.
     * @return array       Google Sheets API response array.
     */
    protected function push_style_batch( Sheet $sheet, array $data ) {
        $spreadsheet_id = $sheet->spreadsheet_id;
        $api_key        = $sheet->API_KEY;
        $api_url        = "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheet_id}:batchUpdate?key={$api_key}";

        return $sheet->push_to_sheet( $api_url, $data, 'POST' );
    }
}
