<?php
/**
 * Google Sheets Manager Class
 *
 * @package ShopExplorer\GoogleSheets
 * @since 1.0.0
 */

namespace SHOPEXPLORER\GoogleSheets;

use SHOPEXPLORER\Exceptions\GoogleSheetsException;
use SHOPEXPLORER\Exceptions\ConfigurationException;

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly.
}

/**
 * Google Sheets Manager class
 *
 * Handles Google Sheets API operations with OAuth authentication
 *
 * @since 1.0.0
 */
class SHOPEX_GoogleSheetsManager {

	/**
	 * Google Sheets API service
	 *
	 * @var \Google_Service_Sheets|null
	 */
	private $sheets_service;

	/**
	 * Google Drive API service
	 *
	 * @var \Google_Service_Drive|null
	 */
	private $drive_service;

	/**
	 * Google Client instance
	 *
	 * @var \Google_Client|null
	 */
	private $client;

	/**
	 * Authentication manager
	 *
	 * @var SHOPEX_GoogleSheetsAuth
	 */
	private $auth_manager;

	/**
	 * Rate limiter
	 *
	 * @var array
	 */
	private $rate_limiter = [
		'requests' => 0,
		'last_reset' => 0,
		'max_requests' => 100,
		'window' => 100, // seconds
	];

	/**
	 * Cache for sheet data
	 *
	 * @var array
	 */
	private $cache = [];

	/**
	 * Cache TTL in seconds
	 *
	 * @var int
	 */
	private $cache_ttl = 300; // 5 minutes

	/**
	 * Constructor
	 */
	public function __construct() {
		$this->auth_manager = new SHOPEX_GoogleSheetsAuth();
		$this->init_services();
	}

	/**
	 * Initialize Google API services
	 */
	private function init_services() {
		try {
			$this->client = $this->auth_manager->get_authenticated_client();

			if ( $this->client ) {
				$this->sheets_service = new \Google_Service_Sheets( $this->client );
				$this->drive_service = new \Google_Service_Drive( $this->client );
			}
		} catch ( \Exception $e ) {
			// Services will be null if authentication fails
			return false;
		}
	}

	/**
	 * Check if services are available
	 *
	 * @return bool
	 */
	public function is_authenticated() {
		return $this->sheets_service !== null && $this->drive_service !== null;
	}

	/**
	 * Create a new spreadsheet
	 *
	 * @param string $title Spreadsheet title
	 * @param array  $sheets Array of sheet configurations
	 * @return string Spreadsheet ID
	 * @throws GoogleSheetsException
	 */
	public function create_spreadsheet( $title, $sheets = [] ) {
		if ( ! $this->is_authenticated() ) {
			return false;
		}

		try {
			$this->check_rate_limit();

			$spreadsheet = new \Google_Service_Sheets_Spreadsheet();
			$spreadsheet->setProperties(
				( new \Google_Service_Sheets_SpreadsheetProperties() )->setTitle( $title )
			);

			// Add sheets if provided
			if ( ! empty( $sheets ) ) {
				$sheet_objects = [];
				foreach ( $sheets as $sheet_config ) {
					$sheet = new \Google_Service_Sheets_Sheet();
					$properties = new \Google_Service_Sheets_SheetProperties();
					$properties->setTitle( $sheet_config['title'] ?? 'Sheet1' );

					if ( isset( $sheet_config['headers'] ) ) {
						$properties->setGridProperties(
							( new \Google_Service_Sheets_GridProperties() )
								->setRowCount( 1000 )
								->setColumnCount( count( $sheet_config['headers'] ) )
						);
					}

					$sheet->setProperties( $properties );
					$sheet_objects[] = $sheet;
				}
				$spreadsheet->setSheets( $sheet_objects );
			}

			$response = $this->sheets_service->spreadsheets->create( $spreadsheet );
			$spreadsheet_id = $response->getSpreadsheetId();

			// Add headers if provided
			if ( ! empty( $sheets ) ) {
				foreach ( $sheets as $index => $sheet_config ) {
					if ( isset( $sheet_config['headers'] ) ) {
						$sheet_title = $sheet_config['title'] ?? 'Sheet1';
						$this->update_sheet_headers( $spreadsheet_id, $sheet_title, $sheet_config['headers'] );
					}
				}
			}

			// Make spreadsheet accessible
			$this->share_spreadsheet( $spreadsheet_id );

			return $spreadsheet_id;

		} catch ( \Exception $e ) {
			return false;
		}
	}

	/**
	 * Get spreadsheet data
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @param string $range Range to read (e.g., 'Sheet1!A1:Z1000')
	 * @return array
	 * @throws GoogleSheetsException
	 */
	public function get_spreadsheet_data( $spreadsheet_id, $range ) {
		if ( ! $this->is_authenticated() ) {
			return false;
		}

		$cache_key = md5( $spreadsheet_id . $range );

		// Check cache first
		if ( isset( $this->cache[ $cache_key ] ) ) {
			$cached_data = $this->cache[ $cache_key ];
			if ( time() - $cached_data['timestamp'] < $this->cache_ttl ) {
				return $cached_data['data'];
			}
		}

		try {
			$this->check_rate_limit();

			$response = $this->sheets_service->spreadsheets_values->get( $spreadsheet_id, $range );
			$values = $response->getValues() ?? [];

			// Cache the result
			$this->cache[ $cache_key ] = [
				'data' => $values,
				'timestamp' => time(),
			];

			return $values;

		} catch ( \Exception $e ) {
			return false;
		}
	}

	/**
	 * Update spreadsheet data
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @param string $range Range to update
	 * @param array  $values Values to update
	 * @param string $input_option Input option (RAW or USER_ENTERED)
	 * @return bool
	 * @throws GoogleSheetsException
	 */
	public function update_spreadsheet_data( $spreadsheet_id, $range, $values, $input_option = 'USER_ENTERED' ) {
		if ( ! $this->is_authenticated() ) {
			return false;
		}

		try {
			$this->check_rate_limit();

			$value_range = new \Google_Service_Sheets_ValueRange();
			$value_range->setValues( $values );

			$params = [
				'valueInputOption' => $input_option,
			];

			$response = $this->sheets_service->spreadsheets_values->update(
				$spreadsheet_id,
				$range,
				$value_range,
				$params
			);

			// Clear cache for this spreadsheet
			$this->clear_cache( $spreadsheet_id );

			return $response->getUpdatedCells() > 0;

		} catch ( \Exception $e ) {
			return false;
		}
	}

	/**
	 * Batch update spreadsheet data
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @param array  $updates Array of update requests
	 * @return bool
	 * @throws GoogleSheetsException
	 */
	public function batch_update_spreadsheet( $spreadsheet_id, $updates ) {
		if ( ! $this->is_authenticated() ) {
			return false;
		}

		try {
			$this->check_rate_limit();

			$value_ranges = [];
			foreach ( $updates as $update ) {
				$value_range = new \Google_Service_Sheets_ValueRange();
				$value_range->setRange( $update['range'] );
				$value_range->setValues( $update['values'] );
				$value_ranges[] = $value_range;
			}

			$batch_request = new \Google_Service_Sheets_BatchUpdateValuesRequest();
			$batch_request->setValueInputOption( 'USER_ENTERED' );
			$batch_request->setData( $value_ranges );

			$response = $this->sheets_service->spreadsheets_values->batchUpdate(
				$spreadsheet_id,
				$batch_request
			);

			// Clear cache for this spreadsheet
			$this->clear_cache( $spreadsheet_id );

			return $response->getTotalUpdatedCells() > 0;

		} catch ( \Exception $e ) {
			return false;
		}
	}

	/**
	 * Append data to spreadsheet
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @param string $range Range to append to
	 * @param array  $values Values to append
	 * @return bool
	 * @throws GoogleSheetsException
	 */
	public function append_to_spreadsheet( $spreadsheet_id, $range, $values ) {
		if ( ! $this->is_authenticated() ) {
			return false;
		}

		try {
			$this->check_rate_limit();

			$value_range = new \Google_Service_Sheets_ValueRange();
			$value_range->setValues( $values );

			$params = [
				'valueInputOption' => 'USER_ENTERED',
				'insertDataOption' => 'INSERT_ROWS',
			];

			$response = $this->sheets_service->spreadsheets_values->append(
				$spreadsheet_id,
				$range,
				$value_range,
				$params
			);

			// Clear cache for this spreadsheet
			$this->clear_cache( $spreadsheet_id );

			return $response->getUpdates()->getUpdatedCells() > 0;

		} catch ( \Exception $e ) {
			return false;
		}
	}

	/**
	 * Clear rows from spreadsheet
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @param string $range Range to clear
	 * @return bool
	 * @throws GoogleSheetsException
	 */
	public function clear_spreadsheet_range( $spreadsheet_id, $range ) {
		if ( ! $this->is_authenticated() ) {
			return false;
		}

		try {
			$this->check_rate_limit();

			$clear_request = new \Google_Service_Sheets_ClearValuesRequest();

			$response = $this->sheets_service->spreadsheets_values->clear(
				$spreadsheet_id,
				$range,
				$clear_request
			);

			// Clear cache for this spreadsheet
			$this->clear_cache( $spreadsheet_id );

			return $response->getClearedRange() !== null;

		} catch ( \Exception $e ) {
			return false;
		}
	}

	/**
	 * Get spreadsheet metadata
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @return array
	 * @throws GoogleSheetsException
	 */
	public function get_spreadsheet_metadata( $spreadsheet_id ) {
		if ( ! $this->is_authenticated() ) {
			return false;
		}

		try {
			$this->check_rate_limit();

			$spreadsheet = $this->sheets_service->spreadsheets->get( $spreadsheet_id );

			$metadata = [
				'title' => $spreadsheet->getProperties()->getTitle(),
				'sheets' => [],
				'url' => $spreadsheet->getSpreadsheetUrl(),
			];

			foreach ( $spreadsheet->getSheets() as $sheet ) {
				$properties = $sheet->getProperties();
				$metadata['sheets'][] = [
					'id' => $properties->getSheetId(),
					'title' => $properties->getTitle(),
					'index' => $properties->getIndex(),
					'row_count' => $properties->getGridProperties()->getRowCount(),
					'column_count' => $properties->getGridProperties()->getColumnCount(),
				];
			}

			return $metadata;

		} catch ( \Exception $e ) {
			return false;

		}
	}

	/**
	 * Update sheet headers
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @param string $sheet_name Sheet name
	 * @param array  $headers Headers array
	 * @return bool
	 */
	private function update_sheet_headers( $spreadsheet_id, $sheet_name, $headers ) {
		$range = $sheet_name . '!A1:' . $this->get_column_letter( count( $headers ) ) . '1';
		return $this->update_spreadsheet_data( $spreadsheet_id, $range, [ $headers ] );
	}

	/**
	 * Share spreadsheet with anyone with link
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 * @return bool
	 */
	private function share_spreadsheet( $spreadsheet_id ) {
		try {
			$permission = new \Google_Service_Drive_Permission();
			$permission->setType( 'anyone' );
			$permission->setRole( 'writer' );

			$this->drive_service->permissions->create( $spreadsheet_id, $permission );
			return true;

		} catch ( \Exception $e ) {

			return false;
		}
	}

	/**
	 * Get column letter from number
	 *
	 * @param int $column_number Column number (1-based)
	 * @return string
	 */
	private function get_column_letter( $column_number ) {
		$letter = '';
		while ( $column_number > 0 ) {
			$column_number--;
			$letter = chr( 65 + ( $column_number % 26 ) ) . $letter;
			$column_number = intval( $column_number / 26 );
		}
		return $letter;
	}

	/**
	 * Check rate limiting
	 *
	 * @throws GoogleSheetsException
	 */
	private function check_rate_limit() {
		$current_time = time();

		// Reset counter if window has passed
		if ( $current_time - $this->rate_limiter['last_reset'] >= $this->rate_limiter['window'] ) {
			$this->rate_limiter['requests'] = 0;
			$this->rate_limiter['last_reset'] = $current_time;
		}

		// Check if we've exceeded the limit
		if ( $this->rate_limiter['requests'] >= $this->rate_limiter['max_requests'] ) {
			$wait_time = $this->rate_limiter['window'] - ( $current_time - $this->rate_limiter['last_reset'] );
			return false;
		}

		$this->rate_limiter['requests']++;
	}

	/**
	 * Clear cache for spreadsheet
	 *
	 * @param string $spreadsheet_id Spreadsheet ID
	 */
	private function clear_cache( $spreadsheet_id ) {
		foreach ( array_keys( $this->cache ) as $cache_key ) {
			if ( strpos( $cache_key, md5( $spreadsheet_id ) ) === 0 ) {
				unset( $this->cache[ $cache_key ] );
			}
		}
	}

	/**
	 * Clear all cache
	 */
	public function clear_all_cache() {
		$this->cache = [];
	}

	/**
	 * Set cache TTL
	 *
	 * @param int $ttl TTL in seconds
	 */
	public function set_cache_ttl( $ttl ) {
		$this->cache_ttl = (int) $ttl;
	}

	/**
	 * Get authentication manager
	 *
	 * @return SHOPEX_GoogleSheetsAuth
	 */
	public function get_auth_manager() {
		return $this->auth_manager;
	}

	/**
	 * Refresh authentication
	 *
	 * @return bool
	 */
	public function refresh_authentication() {
		try {
			$this->init_services();
			return $this->is_authenticated();
		} catch ( \Exception $e ) {
			return false;
		}
	}
}
