<?php
/**
 * Creates and manages the StoreSheet spreadsheet structure.
 *
 * @package StoreSheet
 */

declare( strict_types=1 );

namespace StoreSheet\Sheets;

defined( 'ABSPATH' ) || exit;

use StoreSheet\API\GoogleSheetsClient;
use StoreSheet\Sync\WcToSheets\OrderPusher;

/**
 * Builds the master spreadsheet with all tabs on first setup.
 */
class SpreadsheetManager {

	private GoogleSheetsClient $client;

	public function __construct( GoogleSheetsClient $client ) {
		$this->client = $client;
	}

	/**
	 * Create a new spreadsheet and configure all tabs.
	 * Stores the spreadsheet ID in plugin settings.
	 *
	 * @param string $title Spreadsheet title (default: site name + " — StoreSheet").
	 * @return string|\WP_Error Spreadsheet ID on success.
	 */
	public function create( string $title = '' ) {
		if ( empty( $title ) ) {
			$title = get_bloginfo( 'name' ) . ' — StoreSheet';
		}

		$result = $this->client->create_spreadsheet( $title );
		if ( is_wp_error( $result ) ) {
			return $result;
		}

		$spreadsheet_id  = $result['spreadsheetId'] ?? '';
		$spreadsheet_url = $result['spreadsheetUrl'] ?? '';

		if ( empty( $spreadsheet_id ) ) {
			return new \WP_Error( 'create_failed', __( 'Spreadsheet created but ID missing from response.', 'storesheet' ) );
		}

		// Google creates one default "Sheet1" — rename to Overview, then add the rest.
		$default_sheet_id = $result['sheets'][0]['properties']['sheetId'] ?? 0;

		$build = $this->build_tabs( (int) $default_sheet_id );
		if ( is_wp_error( $build ) ) {
			return $build;
		}

		$this->save_spreadsheet_id( $spreadsheet_id, $spreadsheet_url );

		storesheet()->log( sprintf( 'Spreadsheet created: %s (%s)', $title, $spreadsheet_id ) );

		return $spreadsheet_id;
	}

	/**
	 * Verify the configured spreadsheet is still accessible.
	 *
	 * @return bool|\WP_Error
	 */
	public function validate() {
		$result = $this->client->get_spreadsheet();
		if ( is_wp_error( $result ) ) {
			return $result;
		}
		return true;
	}

	/**
	 * Adopt an existing spreadsheet: verify access, add any StoreSheet tabs it is
	 * missing, and write the column headers. Existing tabs are left in place — only
	 * missing ones are added — but the header row of the managed tabs is (re)written.
	 *
	 * The client must already be pointed at the target spreadsheet (the caller
	 * saves the ID to settings and builds a fresh client before calling this).
	 *
	 * @return true|\WP_Error
	 */
	public function ensure_structure() {
		$meta = $this->client->get_spreadsheet();
		if ( is_wp_error( $meta ) ) {
			return $meta;
		}

		$existing = array();
		foreach ( $meta['sheets'] ?? array() as $sheet ) {
			$existing[] = $sheet['properties']['title'] ?? '';
		}

		$required = array( 'Overview', 'Products', 'Variations', 'Orders', 'Order Items', 'Coupons', 'Customers', 'Reports', '_ss_settings' );

		$requests = array();
		foreach ( array_values( array_diff( $required, $existing ) ) as $tab ) {
			$requests[] = array(
				'addSheet' => array(
					'properties' => array(
						'title'          => $tab,
						'gridProperties' => array(
							'rowCount'    => 2000,
							'columnCount' => 30,
						),
					),
				),
			);
		}

		if ( ! empty( $requests ) ) {
			$result = $this->client->batch_update_spreadsheet( $requests );
			if ( is_wp_error( $result ) ) {
				return $result;
			}
		}

		$this->write_headers();
		$this->hide_settings_tab();

		return true;
	}

	/**
	 * Ensure the per-order-status tabs exist (B1). Creates any of the 7 core
	 * status tabs that are missing, writes the Orders headers into them, and
	 * freezes row 1. Idempotent — when all 7 already exist it makes zero
	 * structural writes.
	 *
	 * @return true|\WP_Error
	 */
	public function ensure_order_status_tabs() {
		$meta = $this->client->get_spreadsheet();
		if ( is_wp_error( $meta ) ) {
			return $meta;
		}

		$existing = array();
		foreach ( $meta['sheets'] ?? array() as $sheet ) {
			$existing[] = $sheet['properties']['title'] ?? '';
		}

		$missing = array_values( array_diff( array_values( OrderPusher::STATUS_TABS ), $existing ) );
		if ( empty( $missing ) ) {
			return true;
		}

		$requests = array();
		foreach ( $missing as $tab ) {
			$requests[] = array(
				'addSheet' => array(
					'properties' => array(
						'title'          => $tab,
						'gridProperties' => array(
							'rowCount'    => 2000,
							'columnCount' => 30,
						),
					),
				),
			);
		}

		$result = $this->client->batch_update_spreadsheet( $requests );
		if ( is_wp_error( $result ) ) {
			return $result;
		}

		$header_data = array();
		foreach ( $missing as $tab ) {
			$header_data[] = array(
				'range'  => $tab . '!A1:S1',
				'values' => array( $this->orders_headers() ),
			);
		}
		$this->client->batch_update( $header_data, 'RAW' );

		$this->freeze_header_rows( $result );

		return true;
	}

	/**
	 * Ensure the Order Items tab exists on the linked spreadsheet, writing its
	 * header row and freezing row 1. Idempotent. Covers sheets created before
	 * this feature / the adopt path.
	 *
	 * @since 1.2.0
	 * @return true|\WP_Error
	 */
	public function ensure_order_items_tab() {
		$meta = $this->client->get_spreadsheet();
		if ( is_wp_error( $meta ) ) {
			return $meta;
		}

		$existing = array();
		foreach ( $meta['sheets'] ?? array() as $sheet ) {
			$existing[] = $sheet['properties']['title'] ?? '';
		}

		if ( in_array( 'Order Items', $existing, true ) ) {
			return true;
		}

		$result = $this->client->batch_update_spreadsheet(
			array(
				array(
					'addSheet' => array(
						'properties' => array(
							'title'          => 'Order Items',
							'gridProperties' => array(
								'rowCount'    => 2000,
								'columnCount' => 30,
							),
						),
					),
				),
			)
		);
		if ( is_wp_error( $result ) ) {
			return $result;
		}

		$this->client->batch_update(
			array(
				array(
					'range'  => 'Order Items!A1:N1',
					'values' => array( $this->order_items_headers() ),
				),
			),
			'RAW'
		);

		$this->freeze_header_rows( $result );

		return true;
	}

	// -------------------------------------------------------------------------
	// Tab construction
	// -------------------------------------------------------------------------

	/**
	 * @param int $default_sheet_id Sheet ID of the auto-created "Sheet1".
	 * @return true|\WP_Error
	 */
	private function build_tabs( int $default_sheet_id ) {
		$requests = array();

		// Rename the default sheet to Overview.
		$requests[] = array(
			'updateSheetProperties' => array(
				'properties' => array(
					'sheetId' => $default_sheet_id,
					'title'   => 'Overview',
					'index'   => 0,
				),
				'fields'     => 'title,index',
			),
		);

		// Add remaining tabs in order.
		$tabs = array( 'Products', 'Variations', 'Orders', 'Order Items', 'Coupons', 'Customers', 'Reports', '_ss_settings' );
		foreach ( $tabs as $i => $tab ) {
			$requests[] = array(
				'addSheet' => array(
					'properties' => array(
						'title'          => $tab,
						'index'          => $i + 1,
						'gridProperties' => array(
							'rowCount'    => 2000,
							'columnCount' => 30,
						),
					),
				),
			);
		}

		$result = $this->client->batch_update_spreadsheet( $requests );
		if ( is_wp_error( $result ) ) {
			return $result;
		}

		// After tab creation, write headers and freeze first row.
		$this->write_headers();
		$this->freeze_header_rows( $result );
		$this->hide_settings_tab();

		return true;
	}

	private function write_headers(): void {
		$data = array(
			array(
				'range'  => 'Products!A1:U1',
				'values' => array( $this->products_headers() ),
			),
			array(
				'range'  => 'Variations!A1:O1',
				'values' => array( $this->variations_headers() ),
			),
			array(
				'range'  => 'Orders!A1:S1',
				'values' => array( $this->orders_headers() ),
			),
			array(
				'range'  => 'Order Items!A1:N1',
				'values' => array( $this->order_items_headers() ),
			),
			array(
				'range'  => 'Coupons!A1:N1',
				'values' => array( $this->coupons_headers() ),
			),
			array(
				'range'  => 'Customers!A1:H1',
				'values' => array( $this->customers_headers() ),
			),
		);

		$this->client->batch_update( $data, 'RAW' );
	}

	private function products_headers(): array {
		$headers = array(
			'product_id',
			'sku',
			'name',
			'status',
			'regular_price',
			'sale_price',
			'stock_quantity',
			'stock_status',
			'manage_stock',
			'categories',
			'tags',
			'weight',
			'date_created',
			'date_modified',
			'_ss_synced_at',
			'_ss_locked',
			'margin_%',
			'link',
			'image_url',
			'attributes',
			'image_alt',
		);

		/**
		 * Filter the Products tab header row. The Pro build's ColumnLayout uses
		 * this to reorder/rename data columns (system columns stay pinned).
		 *
		 * @since 1.2.0
		 * @param string[] $headers
		 */
		return apply_filters( 'storesheet_products_headers', $headers );
	}

	private function variations_headers(): array {
		$headers = array(
			'variation_id',
			'parent_id',
			'parent_sku',
			'sku',
			'status',
			'regular_price',
			'sale_price',
			'stock_quantity',
			'stock_status',
			'manage_stock',
			'weight',
			'attributes',
			'date_created',
			'_ss_synced_at',
			'_ss_locked',
		);

		/**
		 * Filter the Variations tab header row. The Pro build's ColumnLayout uses
		 * this to reorder/rename data columns (system columns stay pinned).
		 *
		 * @since 1.2.0
		 * @param string[] $headers
		 */
		return apply_filters( 'storesheet_variations_headers', $headers );
	}

	/**
	 * @since 1.2.0 Appends `shipping_address` (17) and `coupons` (18) columns.
	 * @return string[]
	 */
	private function orders_headers(): array {
		$headers = array(
			'order_id',
			'order_number',
			'status',
			'date_created',
			'date_paid',
			'customer_name',
			'customer_email',
			'total',
			'subtotal',
			'total_tax',
			'total_shipping',
			'payment_method',
			'items_summary',
			'item_count',
			'customer_note',
			'_ss_synced_at',
			'line_items',
			'shipping_address',
			'coupons',
		);

		/**
		 * Filter the Orders tab header row. The Pro build's ColumnLayout uses
		 * this to reorder/rename data columns (system columns stay pinned).
		 *
		 * @since 1.2.0
		 * @param string[] $headers
		 */
		return apply_filters( 'storesheet_orders_headers', $headers );
	}

	/**
	 * Order Items tab header row — one row per WC line item. Push-only.
	 *
	 * @since 1.2.0
	 * @return string[]
	 */
	private function order_items_headers(): array {
		$headers = array(
			'order_id',
			'order_number',
			'order_status',
			'order_date',
			'customer_email',
			'item_id',
			'product_id',
			'item_name',
			'item_sku',
			'quantity',
			'subtotal',
			'total',
			'total_tax',
			'_ss_synced_at',
		);

		/**
		 * Filter the Order Items tab header row. The Pro build's ColumnLayout
		 * uses this to reorder/rename data columns.
		 *
		 * @since 1.2.0
		 * @param string[] $headers
		 */
		return apply_filters( 'storesheet_order_items_headers', $headers );
	}

	private function coupons_headers(): array {
		$headers = array(
			'coupon_id',
			'code',
			'discount_type',
			'amount',
			'date_expires',
			'individual_use',
			'usage_limit',
			'usage_limit_per_user',
			'minimum_amount',
			'maximum_amount',
			'usage_count',
			'description',
			'_ss_action',
			'_ss_synced_at',
		);

		/**
		 * Filter the Coupons tab header row. The Pro build's ColumnLayout uses
		 * this to reorder/rename data columns (system columns stay pinned).
		 *
		 * @since 1.2.0
		 * @param string[] $headers
		 */
		return apply_filters( 'storesheet_coupons_headers', $headers );
	}

	private function customers_headers(): array {
		$headers = array(
			'customer_id',
			'email',
			'first_name',
			'last_name',
			'total_spent',
			'order_count',
			'date_registered',
			'country',
		);

		/**
		 * Filter the Customers tab header row. The Pro build's ColumnLayout uses
		 * this to reorder/rename data columns (system columns stay pinned).
		 *
		 * @since 1.2.0
		 * @param string[] $headers
		 */
		return apply_filters( 'storesheet_customers_headers', $headers );
	}

	/**
	 * Freeze row 1 on all data tabs.
	 *
	 * @param array $batch_result Result of addSheet batchUpdate — contains new sheetIds.
	 */
	private function freeze_header_rows( array $batch_result ): void {
		$replies  = $batch_result['replies'] ?? array();
		$requests = array();

		foreach ( $replies as $reply ) {
			$sheet_id = $reply['addSheet']['properties']['sheetId'] ?? null;
			if ( null === $sheet_id ) {
				continue;
			}

			$requests[] = array(
				'updateSheetProperties' => array(
					'properties' => array(
						'sheetId'        => $sheet_id,
						'gridProperties' => array( 'frozenRowCount' => 1 ),
					),
					'fields'     => 'gridProperties.frozenRowCount',
				),
			);
		}

		if ( ! empty( $requests ) ) {
			$this->client->batch_update_spreadsheet( $requests );
		}
	}

	private function hide_settings_tab(): void {
		// Find _ss_settings sheetId and hide it.
		$meta = $this->client->get_spreadsheet();
		if ( is_wp_error( $meta ) ) {
			return;
		}

		foreach ( $meta['sheets'] ?? array() as $sheet ) {
			if ( '_ss_settings' === ( $sheet['properties']['title'] ?? '' ) ) {
				$this->client->batch_update_spreadsheet(
					array(
						array(
							'updateSheetProperties' => array(
								'properties' => array(
									'sheetId' => $sheet['properties']['sheetId'],
									'hidden'  => true,
								),
								'fields'     => 'hidden',
							),
						),
					)
				);
				break;
			}
		}
	}

	private function save_spreadsheet_id( string $id, string $url ): void {
		$settings                    = get_option( 'storesheet_settings', array() );
		$settings['spreadsheet_id']  = $id;
		$settings['spreadsheet_url'] = $url;
		update_option( 'storesheet_settings', $settings );
	}
}
