<?php
/**
 * Elementor Widget: Excel Table
 *
 * This widget allows users to embed data from an uploaded Excel file into an Elementor layout.
 *
 * @package ExcelToElementor
 */

use Elementor\Widget_Base;
use Elementor\Controls_Manager;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

/**
 * Elementor widget to render Excel sheet content as a styled HTML table.
 */
class VWPETE_ExcelTable_To_Elementor_Widget extends Widget_Base {


	/**
	 * Get the widget's name.
	 *
	 * @return string The widget name.
	 */
	public function get_name() {
		return 'vwpte_excel_widget';
	}
	/**
	 * Get the widget's title.
	 *
	 * @return string The widget title, translated.
	 */
	public function get_title() {
		return __( 'Excel Table', 'excel-to-elementor' );
	}
	/**
	 * Get the widget's icon.
	 *
	 * @return string The icon class(es) for the widget.
	 */
	public function get_icon() {
		return 'eicon-table-of-contents vwpte-widget-icon';
	}
	/**
	 * Retrieve the list of styles that the widget depends on.
	 *
	 * @return array An array of registered style handles.
	 */
	public function get_style_depends() {
		return array( 'vwpete-wp-widget-style' );
	}
	/**
	 * Get the widget's categories.
	 *
	 * @return array A list of categories the widget belongs to.
	 */
	public function get_categories() {
		return array( 'general' );
	}
	/**
	 * Register the widget controls.
	 *
	 * Adds different control sections to the widget, allowing users to customize its content and appearance.
	 *
	 * @since 1.0.0
	 * @access protected
	 */
	protected function register_controls() {
		$this->start_controls_section(
			'content_section',
			array(
				'label' => __( 'Content', 'excel-to-elementor' ),
				'tab'   => \Elementor\Controls_Manager::TAB_CONTENT,
			)
		);

		// Excel file selection from Media Library.
		$this->add_control(
			'vwpete_excel_widget',
			[
				'label' => esc_html__( 'Select Excel File', 'excel-to-elementor' ),
				'type' => \Elementor\Controls_Manager::MEDIA,
				'media_types' => [
					'application',
				],
			]
		);

		// Sheet selection.
		$this->add_control(
			'sheet_select',
			array(
				'label'       => __( 'Select Sheet', 'excel-to-elementor' ),
				'type'        => \Elementor\Controls_Manager::SELECT,
				'options'     => array(
					'0' => __( 'Sheet 1', 'excel-to-elementor' ), // Default first sheet.
					'1' => __( 'Sheet 2', 'excel-to-elementor' ),
					'2' => __( 'Sheet 3', 'excel-to-elementor' ),
					'3' => __( 'Sheet 4', 'excel-to-elementor' ),
					'4' => __( 'Sheet 5', 'excel-to-elementor' ),
					'5' => __( 'Sheet 6', 'excel-to-elementor' ),
					'6' => __( 'Sheet 7', 'excel-to-elementor' ),
					'7' => __( 'Sheet 8', 'excel-to-elementor' ),
					'8' => __( 'Sheet 9', 'excel-to-elementor' ),
				),
				'default'     => '0', // Set the default value to Sheet 1.
				'description' => __( 'Select the sheet to display', 'excel-to-elementor' ),
				'condition'   => array(
					'vwpete_excel_widget[url]!' => '', // Only show when an Excel file is selected.
				),
			)
		);

		// Add toggle to enable/disable styles.
		$this->add_control(
			'enable_styles',
			array(
				'label'        => __( 'Use Spreadsheet Colors', 'excel-to-elementor' ),
				'type'         => \Elementor\Controls_Manager::SWITCHER,
				'label_on'     => __( 'Yes', 'excel-to-elementor' ),
				'label_off'    => __( 'No', 'excel-to-elementor' ),
				'return_value' => 'yes',
				'default'      => 'no',
			)
		);

		// Header Background Color.
		$this->add_control(
			'header_background_color',
			array(
				'label'       => __( 'Header Background Color', 'excel-to-elementor' ),
				'type'        => \Elementor\Controls_Manager::COLOR,
				'default'     => '#EFEFEF', // Default color.
				'selectors'   => array(
					'{{WRAPPER}} .excel-header' => 'background-color: {{VALUE}};',
				),
				'render_type' => 'template',
				'description' => __( 'Select the background color for the header row.', 'excel-to-elementor' ),
			)
		);

		// Header Text Color.
		$this->add_control(
			'header_text_color',
			array(
				'label'       => __( 'Header Text Color', 'excel-to-elementor' ),
				'type'        => \Elementor\Controls_Manager::COLOR,
				'default'     => '#111111', // Default text color.
				'selectors'   => array(
					'{{WRAPPER}} .excel-header' => 'color: {{VALUE}};',
				),
				'render_type' => 'template',
				'description' => __( 'Select the text color for the header row.', 'excel-to-elementor' ),
			)
		);

		// Added toggle to enable/disable search filter.
		$this->add_control(
			'enable_search',
			array(
				'label'        => __( 'Enable Search Filter', 'excel-to-elementor' ),
				'type'         => \Elementor\Controls_Manager::SWITCHER,
				'label_on'     => __( 'Yes', 'excel-to-elementor' ),
				'label_off'    => __( 'No', 'excel-to-elementor' ),
				'return_value' => 'yes',
				'default'      => 'yes',
			)
		);

		$this->end_controls_section();
	}

	/**
	 * Render the widget output on the frontend.
	 *
	 * This method is responsible for retrieving the widget settings, validating the selected Excel file,
	 * and then displaying the Excel content based on the user's choices.
	 *
	 * @since 1.0.0
	 * @access protected
	 */
	protected function render() {

		$settings = $this->get_settings_for_display();

		$vwpete_excel_widget     = $settings['vwpete_excel_widget'] ?? array();
		$vwpete_excel_widget_url = $vwpete_excel_widget['url'] ?? '';

		if ( empty( $vwpete_excel_widget_url ) ) {
			echo esc_html__( 'Please select an Excel file.', 'excel-to-elementor' );
			return;
		}

		$filetype = wp_check_filetype( $vwpete_excel_widget_url );

		if ( 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' !== $filetype['type'] && 'application/vnd.ms-excel' !== $filetype['type'] ) {
			echo '<div class="elementor-alert elementor-alert-warning">' . esc_html__( 'Selected file is not a valid Excel (.xls, .xlsx) file. Please choose a valid Excel (.xls, .xlsx) file.', 'excel-to-elementor' ) . '</div>';
			return;
		}

		$upload_dir      = wp_get_upload_dir();
		$upload_base_url = $upload_dir['baseurl'];
		$upload_base_dir = wp_normalize_path( $upload_dir['basedir'] );

		if ( strpos( $vwpete_excel_widget_url, $upload_base_url ) !== 0 ) {
			echo esc_html__( 'Invalid file location. File must be within the uploads directory.', 'excel-to-elementor' );
			return;
		}

		$relative_path = ltrim( str_replace( $upload_base_url, '', $vwpete_excel_widget_url ), '/\\' );
		$file_path     = wp_normalize_path( trailingslashit( $upload_base_dir ) . $relative_path );

		if ( ! file_exists( $file_path ) ) {
			echo esc_html__( 'The selected file does not exist.', 'excel-to-elementor' );
			return;
		}

		// adding an additional check to ensure file is an attachment in the media library.
		$attachment_id = $vwpete_excel_widget['id'] ?? 0;
		$file_path     = get_attached_file( $attachment_id );

		if ( ! $attachment_id || ! $file_path ) {
			echo esc_html__( 'Invalid or missing file attachment.', 'excel-to-elementor' );
			return;
		}

		$meta = wp_get_attachment_metadata( $attachment_id );
		if ( empty( $meta ) ) {
			echo esc_html__( 'Unable to read attachment metadata.', 'excel-to-elementor' );
			return;
		}

		// Ensure it's an Excel file.
		$filetype = wp_check_filetype( $file_path );
		if ( 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' !== $filetype['type']  && 'application/vnd.ms-excel' !== $filetype['type'] ) {
			echo esc_html__( 'Selected file is not a valid Excel (.xls, .xlsx) file.', 'excel-to-elementor' );
			return;
		}

		// Once all checks are passed, we can render the table.
		$selected_sheet = absint( $settings['sheet_select'] ?? 0 );
		$this->display_excel_content( $file_path, $selected_sheet, $settings );
	}
	/**
	 * Renders the Excel table HTML and outputs it.
	 *
	 * @param string $file_path       Absolute path to the Excel file.
	 * @param int    $selected_sheet  The sheet index to render.
	 * @param array  $settings        The widget settings from Elementor.
	 */
	private function display_excel_content( $file_path, $selected_sheet, $settings ) {
		$cache_key_data = array(
			'file_path'      => $file_path,
			'selected_sheet' => $selected_sheet,
			'settings'       => $settings,
		);
		$cache_key      = VWPETE_EXCEL_CACHE_PREFIX . sha1( wp_json_encode( $cache_key_data ) );

		$allowed_tags = array(
			'table' => array(
				'class' => true,
				'id'    => true,
				'style' => true,
			),
			'thead' => array(
				'class' => true,
				'style' => true,
			),
			'tbody' => array(
				'class' => true,
				'style' => true,
			),
			'tr'    => array(
				'class' => true,
				'style' => true,
			),
			'td'    => array(
				'class'   => true,
				'colspan' => true,
				'rowspan' => true,
				'style'   => true,
			),
			'th'    => array(
				'class'   => true,
				'colspan' => true,
				'rowspan' => true,
				'scope'   => true,
				'style'   => true,
			),
			'input' => array(
				'type'        => true,
				'name'        => true,
				'value'       => true,
				'placeholder' => true,
				'class'       => true,
				'id'          => true,
				'style'       => true,
				'checked'     => true,
			),
			'label' => array(
				'for'   => true,
				'class' => true,
				'style' => true,
			),
			'div'   => array(
				'class' => true,
				'id'    => true,
				'style' => true,
			),
			'span'  => array(
				'class' => true,
				'style' => true,
			),
			'p'     => array(
				'class' => true,
				'style' => true,
			),
		);

		$cached_html = get_transient( $cache_key );

		if ( false !== $cached_html ) {

			wp_enqueue_style( 'exceltable-public-css', VWPETE_PLUGIN_URL . 'public/public.css', array(), VWPETE_VERSION );
			wp_enqueue_script( 'exceltable-public-js', VWPETE_PLUGIN_URL . 'public/public.js', array(), VWPETE_VERSION, true );

			echo wp_kses( $cached_html, $allowed_tags );
			return;
		}

		if ( ! is_readable( $file_path ) ) {
			echo esc_html__( 'The file cannot be read.', 'excel-to-elementor' );
			return;
		}

		// Enqueue assets.
		wp_enqueue_style( 'exceltable-public-css', VWPETE_PLUGIN_URL . 'public/public.css', array(), VWPETE_VERSION );
		wp_enqueue_script( 'exceltable-public-js', VWPETE_PLUGIN_URL . 'public/public.js', array(), VWPETE_VERSION, true );
		ob_start(); // Start output buffering.
		try {
			$spreadsheet = IOFactory::load( $file_path );
			$sheet       = $spreadsheet->getSheet( $selected_sheet );

			$use_styles = ( $settings['enable_styles'] ?? '' ) === 'yes';
			$use_search = ( $settings['enable_search'] ?? '' ) === 'yes';

			$header_bg    = sanitize_hex_color( $settings['header_background_color'] ?? '#EFEFEF' );
			$header_color = sanitize_hex_color( $settings['header_text_color'] ?? '#111111' );

			// Get merged cells information
			$merged_cells = $sheet->getMergeCells();
			$merged_ranges = array();
			
			// Parse merged cells into a more usable format
			foreach ($merged_cells as $range) {
				$range_parts = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::rangeBoundaries($range);
				if (count($range_parts) === 2) {
					$start_col = $range_parts[0][0];
					$start_row = $range_parts[0][1];
					$end_col = $range_parts[1][0];
					$end_row = $range_parts[1][1];
					
					// Calculate colspan and rowspan
					$colspan = $end_col - $start_col + 1;
					$rowspan = $end_row - $start_row + 1;
					
					// Store the top-left cell as the key with colspan/rowspan info
					$start_cell = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($start_col) . $start_row;
					$merged_ranges[$start_cell] = array(
						'colspan' => $colspan,
						'rowspan' => $rowspan,
						'end_col' => $end_col,
						'end_row' => $end_row
					);
					
					// Mark all cells in the merged range (except top-left) as merged
					for ($row = $start_row; $row <= $end_row; $row++) {
						for ($col = $start_col; $col <= $end_col; $col++) {
							$cell_coord = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row;
							if ($cell_coord !== $start_cell) {
								$merged_ranges[$cell_coord] = array('merged' => true);
							}
						}
					}
				}
			}

			echo '<div class="vwpexcel-table-container">';
			if ( $use_search ) {
				echo '<input id="vwpte-excel-table-filter" type="text" placeholder="' . esc_attr__( 'Search...', 'excel-to-elementor' ) . '">';
			}

			echo '<div class="vwpexcel-table-responsive">';
			echo '<table id="vwpte-excel-table">';

			// Detect the actual data range to skip empty rows/columns
			$highest_row = $sheet->getHighestDataRow();
			$highest_col = $sheet->getHighestDataColumn();
			$highest_col_index = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highest_col);
			
			// Find the first non-empty row
			$first_data_row = 1;
			for ($i = 1; $i <= $highest_row; $i++) {
				$row_empty = true;
				for ($col_idx = 1; $col_idx <= $highest_col_index; $col_idx++) {
					$col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col_idx);
					$cell_value = trim((string)$sheet->getCell($col . $i)->getValue());
					if (!empty($cell_value)) {
						$row_empty = false;
						break;
					}
				}
				if (!$row_empty) {
					$first_data_row = $i;
					break;
				}
			}
			
			// Find the last non-empty row
			$last_data_row = $highest_row;
			for ($i = $highest_row; $i >= $first_data_row; $i--) {
				$row_empty = true;
				for ($col_idx = 1; $col_idx <= $highest_col_index; $col_idx++) {
					$col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col_idx);
					$cell_value = trim((string)$sheet->getCell($col . $i)->getValue());
					if (!empty($cell_value)) {
						$row_empty = false;
						break;
					}
				}
				if (!$row_empty) {
					$last_data_row = $i;
					break;
				}
			}
			
			// Find the first non-empty column
			$first_data_col = 1;
			for ($col_idx = 1; $col_idx <= $highest_col_index; $col_idx++) {
				$col_empty = true;
				$col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col_idx);
				for ($i = $first_data_row; $i <= $last_data_row; $i++) {
					$cell_value = trim((string)$sheet->getCell($col . $i)->getValue());
					if (!empty($cell_value)) {
						$col_empty = false;
						break;
					}
				}
				if (!$col_empty) {
					$first_data_col = $col_idx;
					break;
				}
			}
			
			// Find the last non-empty column
			$last_data_col = $highest_col_index;
			for ($col_idx = $highest_col_index; $col_idx >= $first_data_col; $col_idx--) {
				$col_empty = true;
				$col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col_idx);
				for ($i = $first_data_row; $i <= $last_data_row; $i++) {
					$cell_value = trim((string)$sheet->getCell($col . $i)->getValue());
					if (!empty($cell_value)) {
						$col_empty = false;
						break;
					}
				}
				if (!$col_empty) {
					$last_data_col = $col_idx;
					break;
				}
			}

			$is_header = true;
			
			foreach ( $sheet->getRowIterator($first_data_row, $last_data_row) as $row ) {
				$row_index     = $row->getRowIndex();
				$cell_iterator = $row->getCellIterator();
				$cell_iterator->setIterateOnlyExistingCells( false );

				$row_style = $sheet->getStyle( 'A' . $row_index );
				$fill      = $row_style->getFill();
				$bg_color  = $fill->getStartColor()->getRGB();
				$bg_color  = empty( $bg_color ) || '000000' === $bg_color ? 'FFFFFF' : $bg_color;

				if ( ! $use_styles ) {
					$bg_color = 'FFFFFF';
				}

				if ( $is_header ) {
					echo '<thead class="excel-header" style="background-color:' . esc_attr( $header_bg ) . ';color:' . esc_attr( $header_color ) . ';">';
					echo '<tr>';
				} else {
					echo '<tr style="background-color: #' . esc_attr( $bg_color ) . ';">';
				}

				foreach ( $cell_iterator as $cell ) {
					$cell_coord = $cell->getCoordinate();
					$col_index = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($cell->getColumn());
					
					// Skip columns outside our data range
					if ($col_index < $first_data_col || $col_index > $last_data_col) {
						continue;
					}
					
					// Skip this cell if it's part of a merged range (but not the top-left cell)
					if (isset($merged_ranges[$cell_coord]) && isset($merged_ranges[$cell_coord]['merged'])) {
						continue;
					}
					
					$cell_value = $cell->isFormula()
					? $cell->getCalculatedValue()
					: $spreadsheet->getActiveSheet()->getCell( $cell_coord )->getFormattedValue();

					$cell_text   = esc_html( $cell_value );
					$cell_style  = $sheet->getStyle( $cell_coord );
					$font        = $cell_style->getFont();
					$cell_styles = '';

					if ( $use_styles ) {
						if ( $font->getBold() ) {
							$cell_styles .= 'font-weight: bold;';
						}
						if ( $font->getItalic() ) {
							$cell_styles .= 'font-style: italic;';
						}
						if ( $font->getUnderline() !== \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_NONE ) {
							$cell_styles .= 'text-decoration: underline;';
						}
						$font_color = $font->getColor()->getRGB();
						if ( ! empty( $font_color ) ) {
							$cell_styles .= 'color: #' . esc_attr( $font_color ) . ';';
						}
					}

					$cspan = '';
					$rspan = '';
					
					// Check if this cell is the top-left of a merged range
					if ( isset( $merged_ranges[ $cell_coord ] ) && isset( $merged_ranges[ $cell_coord ]['colspan'] ) ) {
						$cspan = $merged_ranges[ $cell_coord ]['colspan'];
						$rspan = $merged_ranges[ $cell_coord ]['rowspan'];
					}

					if ( $is_header ) {
						echo '<th';
					} else {
						echo '<td';
					}

					if ( $cell_styles ) {
						echo ' style="' . esc_attr( $cell_styles ) . '"';
					}
					if ( $cspan ) {
						echo ' colspan="' . esc_attr( $cspan ) . '"';
					}
					if ( $rspan ) {
						echo ' rowspan="' . esc_attr( $rspan ) . '"';
					}
					echo '>';
					echo esc_html( $cell_text );

					if ( $is_header ) {
						echo '</th>';
					} else {
						echo '</td>';
					}
				}

				echo '</tr>';

				if ( $is_header ) {
					echo '</thead>';
					$is_header = false;
				}
			}

			echo '</table></div></div>';

		} catch ( \Throwable $e ) {
			echo esc_html__( 'Error reading the Excel file.', 'excel-to-elementor' );
			if ( WP_DEBUG ) {
				echo '<pre>' . esc_html( $e->getMessage() ) . '</pre>';
			}
		}
		$html_content = ob_get_clean(); // Get the buffered content and clean the buffer.
		set_transient( $cache_key, $html_content, VWPETE_CACHE_PERIOD ); // Cache for your defined period.

		echo wp_kses( $html_content, $allowed_tags );
	}
}
