<?php
/**
 * XLSX Parser using PhpSpreadsheet.
 *
 * @package PrimeLink
 */

namespace PrimeLink\Modules\Imports\Parsers;

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

final class XlsxParser implements ParserInterface {

    /**
     * XLSX preview reader using PhpSpreadsheet (with fallback to ZipArchive + SimpleXML).
     */
    public function preview( $path, array $options = array() ) {
        $defaults = array(
            'max_rows' => 0, // 0 = unlimited
        );
        $options  = wp_parse_args( (array) $options, $defaults );

        if ( ! \PrimeLink\Helpers\FilesystemHelper::file_exists( $path ) ) {
            return array(
                'headers' => array(),
                'rows'    => array(),
                'total'   => 0,
                'message' => 'XLSX file not readable.',
            );
        }

        // PhpSpreadsheet 1.29 requires PHP 7.4+ but may have issues on older versions
        // Check if PhpSpreadsheet is available (class_exists is safe even if PHP version doesn't match)
        // But we'll try it first and fallback if it fails
        $spreadsheet_io_class = null;
        if ( class_exists( '\PrimeLink\Vendor\PhpOffice\PhpSpreadsheet\IOFactory' ) ) {
            $spreadsheet_io_class = '\PrimeLink\Vendor\PhpOffice\PhpSpreadsheet\IOFactory';
        } elseif ( class_exists( '\PhpOffice\PhpSpreadsheet\IOFactory' ) ) {
            $spreadsheet_io_class = '\PhpOffice\PhpSpreadsheet\IOFactory';
        }

        // Try PhpSpreadsheet if available (it will fallback on error)
        if ( $spreadsheet_io_class ) {
            return $this->preview_with_phpspreadsheet( $path, $options, $spreadsheet_io_class );
        }

        // Fallback to ZipArchive + SimpleXML
        return $this->preview_with_ziparchive( $path, $options );
    }

    /**
     * Preview using PhpSpreadsheet.
     *
     * @param string $path                    File path.
     * @param array  $options                 Options.
     * @param string $spreadsheet_io_class    PhpSpreadsheet IOFactory class name.
     * @return array
     */
    private function preview_with_phpspreadsheet( $path, $options, $spreadsheet_io_class ) {
        try {
            // Detect Coordinate class (prefixed or normal)
            $coordinate_class = null;
            if ( class_exists( '\PrimeLink\Vendor\PhpOffice\PhpSpreadsheet\Cell\Coordinate' ) ) {
                $coordinate_class = '\PrimeLink\Vendor\PhpOffice\PhpSpreadsheet\Cell\Coordinate';
            } elseif ( class_exists( '\PhpOffice\PhpSpreadsheet\Cell\Coordinate' ) ) {
                $coordinate_class = '\PhpOffice\PhpSpreadsheet\Cell\Coordinate';
            }
            
            if ( ! $coordinate_class ) {
                throw new \Exception( 'PhpSpreadsheet Coordinate class not found' );
            }
            
            // Load spreadsheet
            $spreadsheet = $spreadsheet_io_class::load( $path );
            
            // Get first worksheet
            $worksheet = $spreadsheet->getActiveSheet();
            
            // Get highest row and column
            $highest_row = $worksheet->getHighestRow();
            $highest_column = $worksheet->getHighestColumn();
            $highest_column_index = $coordinate_class::columnIndexFromString( $highest_column );
            
            $max_rows = (int) $options['max_rows'];
            $rows = array();
            
            // Read rows
            for ( $row = 1; $row <= $highest_row; $row++ ) {
                $row_data = array();
                
                for ( $col = 1; $col <= $highest_column_index; $col++ ) {
                    $cell = $worksheet->getCellByColumnAndRow( $col, $row );
                    $value = $cell->getFormattedValue(); // Get formatted value (handles dates, numbers, etc.)
                    $row_data[] = $value !== null ? (string) $value : '';
                }
                
                // Remove trailing empty columns
                while ( ! empty( $row_data ) && end( $row_data ) === '' ) {
                    array_pop( $row_data );
                }
                
                if ( ! empty( $row_data ) ) {
                    $rows[] = $row_data;
                }
                
                // Limit rows if specified
                if ( $max_rows > 0 && count( $rows ) >= ( $max_rows + 1 ) ) {
                    break;
                }
            }
            
            if ( empty( $rows ) ) {
                return array(
                    'headers' => array(),
                    'rows'    => array(),
                    'total'   => 0,
                    'message' => 'No rows found in first worksheet.',
                );
            }
            
            $headers = $rows[0];
            $body    = array_slice( $rows, 1 );
            
            return array(
                'headers' => $headers,
                'rows'    => $body,
                'total'   => count( $body ),
            );
        } catch ( \Exception $e ) {
            // Fallback to ZipArchive on error
            \PrimeLink\Helpers\LoggerHelper::warning(
                'PhpSpreadsheet failed, falling back to ZipArchive',
                array(
                    'file'  => $path,
                    'error' => $e->getMessage(),
                )
            );
            return $this->preview_with_ziparchive( $path, $options );
        }
    }

    /**
     * Preview using ZipArchive + SimpleXML (fallback).
     *
     * @param string $path    File path.
     * @param array  $options Options.
     * @return array
     */
    private function preview_with_ziparchive( $path, array $options = array() ) {
        // Detect legacy XLS (BIFF) and bail with a clear message.
        $contents = \PrimeLink\Helpers\FilesystemHelper::read_file( $path );
        $sig = $contents ? substr( $contents, 0, 2 ) : '';
        if ( 'PK' !== $sig ) {
            return array(
                'headers' => array(),
                'rows'    => array(),
                'total'   => 0,
                'message' => 'This looks like XLS (binary). Please save as XLSX or CSV to preview.',
            );
        }

        if ( ! class_exists( '\ZipArchive' ) ) {
            return array(
                'headers' => array(),
                'rows'    => array(),
                'total'   => 0,
                'message' => 'PHP ZipArchive extension is missing. XLSX preview unavailable.',
            );
        }

        $zip = new \ZipArchive();
        if ( true !== $zip->open( $path ) ) {
            return array(
                'headers' => array(),
                'rows'    => array(),
                'total'   => 0,
                'message' => 'Unable to open XLSX archive.',
            );
        }

        // Shared strings
        $shared_strings = array();
        $shared_xml     = $zip->getFromName( 'xl/sharedStrings.xml' );
        if ( $shared_xml ) {
            $shared_doc = $this->xml( $shared_xml );
            if ( $shared_doc && isset( $shared_doc->si ) ) {
                foreach ( $shared_doc->si as $si ) {
                    $text = '';
                    if ( isset( $si->t ) ) {
                        $text = (string) $si->t;
                    } elseif ( isset( $si->r ) ) {
                        foreach ( $si->r as $r ) {
                            if ( isset( $r->t ) ) {
                                $text .= (string) $r->t;
                            }
                        }
                    }
                    $shared_strings[] = $text;
                }
            }
        }

        // Locate first worksheet
        $sheet_xml = $zip->getFromName( 'xl/worksheets/sheet1.xml' );
        if ( ! $sheet_xml ) {
            for ( $i = 0; $i < $zip->numFiles; $i++ ) {
                $stat = $zip->statIndex( $i );
                if ( isset( $stat['name'] ) && 0 === strpos( $stat['name'], 'xl/worksheets/sheet' ) ) {
                    $sheet_xml = $zip->getFromIndex( $i );
                    break;
                }
            }
        }

        if ( ! $sheet_xml ) {
            $zip->close();
            return array(
                'headers' => array(),
                'rows'    => array(),
                'total'   => 0,
                'message' => 'No worksheet found in XLSX.',
            );
        }

        $sheet = $this->xml( $sheet_xml );
        if ( ! $sheet ) {
            $zip->close();
            return array(
                'headers' => array(),
                'rows'    => array(),
                'total'   => 0,
                'message' => 'Unable to parse worksheet XML.',
            );
        }

        $rows_xml = $sheet->xpath('//main:sheetData/main:row');
        if ( empty( $rows_xml ) ) {
            $rows_xml = $sheet->xpath('//row');
        }

        $rows   = array();
        $maxRow = (int) $options['max_rows'];
        $count  = 0;

        foreach ( (array) $rows_xml as $row ) {
            $row_values = array();
            $max_col    = 0;
            foreach ( $row->c as $c ) {
                $ref       = (string) $c['r'];
                $col_index = $this->col_to_index( $ref );
                if ( $col_index > $max_col ) {
                    $max_col = $col_index;
                }

                $type = (string) $c['t'];
                $val  = '';
                if ( 's' === $type && isset( $c->v ) ) {
                    $idx = (int) $c->v;
                    $val = isset( $shared_strings[ $idx ] ) ? $shared_strings[ $idx ] : '';
                } elseif ( 'inlineStr' === $type && isset( $c->is->t ) ) {
                    $val = (string) $c->is->t;
                } elseif ( isset( $c->v ) ) {
                    $val = (string) $c->v;
                }
                $row_values[ $col_index ] = $val;
            }

            $normalized = array();
            for ( $i = 0; $i <= $max_col; $i++ ) {
                $normalized[] = isset( $row_values[ $i ] ) ? $row_values[ $i ] : '';
            }
            $rows[] = $normalized;
            $count++;

            if ( $maxRow > 0 && $count >= ( $maxRow + 1 ) ) {
                break;
            }
        }

        $zip->close();

        if ( empty( $rows ) ) {
            return array(
                'headers' => array(),
                'rows'    => array(),
                'total'   => 0,
                'message' => 'No rows found in first worksheet. Check that the file has data in Sheet1 or save as CSV.',
            );
        }

        $headers = $rows[0];
        $body    = array_slice( $rows, 1 );

        return array(
            'headers' => $headers,
            'rows'    => $body,
            'total'   => count( $body ),
        );
    }

    private function xml( $xml_string ) {
        $xml = @simplexml_load_string( $xml_string, 'SimpleXMLElement', LIBXML_NOCDATA );
        if ( $xml ) {
            $xml->registerXPathNamespace( 'main', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' );
        }
        return $xml;
    }

    /**
     * Convert column letters (e.g. "BC10") to zero-based index.
     */
    private function col_to_index( $cell_ref ) {
        $letters = preg_replace( '/[^A-Z]/i', '', (string) $cell_ref );
        $letters = strtoupper( $letters );
        $len     = strlen( $letters );
        $num     = 0;
        for ( $i = 0; $i < $len; $i++ ) {
            $num = $num * 26 + ( ord( $letters[ $i ] ) - 64 );
        }
        return max( 0, $num - 1 );
    }
}
