<?php
/**
 * aida-preview.php – Vorschau-Daten einer AIDA-Liste (xlsx) als JSON für das Vorschau-Modal
 * inkl. Live-Suche. Auth über die Haupt-Session (LOGIN_USER).
 *
 *   GET md5=<file_md5>[&rows=N]  -> { ok, name, download, headers:[...], rows:[[...],...] }
 *
 * Datei wird über pacim_files (file_md5 -> file_path + file_name) aufgelöst; Pfad muss
 * innerhalb von datas/ liegen.
 */
require_once( __DIR__ . '/includes/config.inc.php' );
@ini_set( 'display_errors', '0' );
error_reporting( 0 );
@ini_set( 'memory_limit', '1024M' );
@set_time_limit( 120 );
ob_start();

function ap_json( $arr ) { while ( ob_get_level() > 0 ) ob_end_clean(); header( 'Content-Type: application/json; charset=utf-8' ); echo json_encode( $arr ); exit(); }

if ( ! isset( $_SESSION['user'] ) || $_SESSION['user'] != LOGIN_USER ) {
    http_response_code( 403 );
    ap_json( array( 'ok' => false, 'error' => 'unauthorized' ) );
}

$md5 = isset( $_GET['md5'] ) ? strtolower( trim( $_GET['md5'] ) ) : '';
if ( ! preg_match( '/^[a-f0-9]{32}$/', $md5 ) ) ap_json( array( 'ok' => false, 'error' => 'bad_params' ) );

$oDB = new MysqliDb( MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB );
$row = $oDB->rawQueryOne( 'SELECT file_name, file_path FROM pacim_files WHERE file_md5 = ? LIMIT 1', array( $md5 ) );
if ( ! $row || empty( $row['file_name'] ) ) ap_json( array( 'ok' => false, 'error' => 'not_found' ) );

$name = $row['file_name'];
$path = is_file( $row['file_path'] ) ? $row['file_path'] : rtrim( $row['file_path'], '/' ) . '/' . $name;
$real = realpath( $path );
$base = realpath( PATH_DATAS );
if ( $real === false || $base === false || strpos( $real, $base ) !== 0 || ! is_file( $real ) ) ap_json( array( 'ok' => false, 'error' => 'file_missing' ) );
if ( ! in_array( strtolower( pathinfo( $real, PATHINFO_EXTENSION ) ), array( 'xls', 'xlsx' ), true ) ) ap_json( array( 'ok' => false, 'error' => 'bad_format' ) );

if ( ! class_exists( 'PHPExcel_IOFactory' ) ) ap_json( array( 'ok' => false, 'error' => 'no_excel' ) );

$maxRows = isset( $_GET['rows'] ) ? (int) $_GET['rows'] : 2000;
if ( $maxRows < 100 ) $maxRows = 100;
if ( $maxRows > 5000 ) $maxRows = 5000;

try {
    PHPExcel_Settings::setCacheStorageMethod( PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp, array( 'memoryCacheSize' => '96MB' ) );
    $reader = PHPExcel_IOFactory::createReaderForFile( $real );
    $reader->setReadDataOnly( false ); // Zahlenformate laden -> Datumswerte korrekt (AIDA-Listen sind klein)
    $xls = $reader->load( $real );
    $ws  = $xls->getActiveSheet();

    $hiRow = min( (int) $ws->getHighestRow(), $maxRows + 2 );
    $hiCol = PHPExcel_Cell::columnIndexFromString( $ws->getHighestColumn() );

    // Kopfzeile (Zeile 2, wie in den AIDA-Listen) + Datums-/Zeitspalten erkennen
    $headers  = array();
    $colDate  = array();
    $colTime  = array();
    for ( $c = 0; $c < $hiCol; $c++ ) {
        $v = $ws->getCellByColumnAndRow( $c, 2 );
        $v = $v ? (string) $v->getFormattedValue() : '';
        $headers[] = ( $v !== '' ) ? $v : ( 'Spalte ' . ( $c + 1 ) );
        $colDate[$c] = (bool) preg_match( '/datum|date/i', $v );
        $colTime[$c] = (bool) preg_match( '/zeit|uhr|time/i', $v );
    }

    // Datenzeilen ab Zeile 3 – Datums-/Zeitwerte einheitlich formatieren (d.m.Y / H:i)
    $rows = array();
    for ( $r = 3; $r <= $hiRow; $r++ ) {
        $vals = array();
        $has = false;
        for ( $c = 0; $c < $hiCol; $c++ ) {
            $cell = $ws->getCellByColumnAndRow( $c, $r );
            if ( ! $cell ) { $vals[] = ''; continue; }
            $raw  = $cell->getValue();
            $isDT = $colDate[$c] || $colTime[$c] || PHPExcel_Shared_Date::isDateTime( $cell );
            if ( $isDT && is_numeric( $raw ) && (float) $raw > 0 ) {
                $ts = PHPExcel_Shared_Date::ExcelToPHP( (float) $raw );
                if ( $colTime[$c] && ! $colDate[$c] ) {
                    $fmt = 'H:i';
                } elseif ( $colDate[$c] && ! $colTime[$c] ) {
                    $fmt = 'd.m.Y';
                } else {
                    $fl   = floor( (float) $raw );
                    $frac = (float) $raw - $fl;
                    $fmt  = ( $fl == 0 ) ? 'H:i' : ( $frac > 0.0000001 ? 'd.m.Y H:i' : 'd.m.Y' );
                }
                $val = gmdate( $fmt, $ts ); // gmdate: keine Zeitzonen-Verschiebung -> Originalwert aus Excel
            } else {
                $val = (string) $cell->getFormattedValue();
            }
            if ( $val !== '' ) $has = true;
            $vals[] = $val;
        }
        if ( $has ) $rows[] = $vals;
    }

    $xls->disconnectWorksheets();
    unset( $ws, $xls, $reader );
} catch ( Exception $e ) {
    ap_json( array( 'ok' => false, 'error' => 'read_failed' ) );
}

ap_json( array(
    'ok'       => true,
    'name'     => $name,
    'download' => SITE_URL . '/file.php?token=YIz8HqUPWQ7cwcVHE57O&type=download&file=' . $md5,
    'headers'  => $headers,
    'rows'     => $rows,
    'total'    => count( $rows ),
) );