<?php
/**
 * Report Engine -- ingests rows for a report definition, validates them,
 * stores them as JSON in report_data, then materializes outputs.
 *
 * High level flow:
 *
 *   $engine = new ReportEngine($reportId);
 *   $engine->ingest($filePath, $mode, $uploadedBy);   // 'append' | 'replace_all' | 'replace_by_period' | 'upsert'
 *   $engine->materializeOutputs($uploadId);
 */

require_once __DIR__ . '/xlsx-reader.php';

class ReportEngine
{
    private PDO $pdo;
    private array $report;
    /** @var array<int,array> col_name => column meta */
    private array $columns;
    private array $colByLabel;
    private array $colByName;

    public function __construct(int $reportId)
    {
        $this->pdo = db();
        $stmt = $this->pdo->prepare('SELECT * FROM reports WHERE id = ?');
        $stmt->execute([$reportId]);
        $this->report = $stmt->fetch();
        if (!$this->report) {
            throw new RuntimeException("Report $reportId not found.");
        }

        $colStmt = $this->pdo->prepare('SELECT * FROM report_columns WHERE report_id = ? ORDER BY col_order');
        $colStmt->execute([$reportId]);
        $this->columns = $colStmt->fetchAll();
        $this->colByLabel = [];
        $this->colByName = [];
        foreach ($this->columns as $c) {
            $this->colByLabel[strtolower(trim($c['col_label']))] = $c;
            $this->colByName[$c['col_name']] = $c;
        }
    }

    /** @return array{upload_id:int,total:int,valid:int,errors:int} */
    public function ingest(string $filePath, string $mode, ?int $uploadedBy = null): array
    {
        if (!in_array($mode, ['append','replace_all','replace_by_period','upsert'], true)) {
            throw new InvalidArgumentException("Bad upload mode $mode");
        }

        // 1. Open file
        $ext = strtolower(pathinfo($filePath, PATHINFO_EXTENSION));
        $rowsIter = match ($ext) {
            'xlsx', 'xls'  => $this->iterateXlsx($filePath),
            'csv', 'tsv'   => $this->iterateCsv($filePath, $ext === 'tsv' ? "\t" : ','),
            default        => throw new InvalidArgumentException("Unsupported file type .$ext"),
        };

        // 2. Header row -> column index mapping
        $rowsIter->rewind();
        $header = $rowsIter->current();
        if (!$header) throw new RuntimeException('File is empty.');
        $headerToCol = [];
        foreach ($header as $colIdx => $h) {
            $key = strtolower(trim((string) $h));
            if ($key === '') continue;
            // Match by label (case insensitive)
            if (isset($this->colByLabel[$key])) {
                $headerToCol[$colIdx] = $this->colByLabel[$key];
            }
        }

        // 3. Create upload record
        $insUpload = $this->pdo->prepare(
            'INSERT INTO report_uploads
             (report_id, file_name, file_path, file_size, file_hash, uploaded_by,
              upload_mode_used, total_rows, valid_rows, error_rows,
              status, started_at, created_at, updated_at)
             VALUES (?,?,?,?,?,?,?,0,0,0,"processing",NOW(),NOW(),NOW())'
        );
        $insUpload->execute([
            $this->report['id'],
            basename($filePath),
            $filePath,
            filesize($filePath) ?: 0,
            hash_file('sha256', $filePath),
            $uploadedBy,
            $mode,
        ]);
        $uploadId = (int) $this->pdo->lastInsertId();
        $this->upLog($uploadId, 'started', "mode=$mode header_cols=" . count($headerToCol));

        // 4. Iterate body rows, validate, queue for insert
        $rowsIter->next();
        $total = $valid = $errCount = 0;
        $validRows  = []; // [['period_label'=>..,'key_hash'=>..,'data'=>[...]]]
        $errInsert  = $this->pdo->prepare(
            'INSERT INTO upload_errors (upload_id, row_index, col_name, error_code, error_message, created_at)
             VALUES (?,?,?,?,?,NOW())'
        );

        $periodCol = $this->report['period_column'];                       // label
        $keyCols   = json_decode($this->report['unique_key_columns'] ?? '[]', true) ?: [];

        while ($rowsIter->valid()) {
            $row = $rowsIter->current();
            $total++;
            $isEmpty = true;
            foreach ($row as $v) { if ($v !== '' && $v !== null) { $isEmpty = false; break; } }
            if ($isEmpty) { $rowsIter->next(); continue; }

            $rowData = [];
            $rowErrors = [];
            $period = null;
            $keyParts = [];

            foreach ($headerToCol as $colIdx => $colMeta) {
                $rawVal = $row[$colIdx] ?? '';
                [$cleaned, $err] = $this->validateValue($rawVal, $colMeta);
                if ($err) {
                    $rowErrors[] = [$colMeta['col_name'], 'invalid', $err];
                    $rowData[$colMeta['col_name']] = $rawVal;
                } else {
                    $rowData[$colMeta['col_name']] = $cleaned;
                }
                if ($periodCol && strcasecmp($colMeta['col_label'], $periodCol) === 0) {
                    $period = $this->derivePeriodLabel($cleaned, $colMeta['data_type']);
                }
                if (in_array($colMeta['col_label'], $keyCols, true) || in_array($colMeta['col_name'], $keyCols, true)) {
                    $keyParts[] = (string) ($cleaned ?? '');
                }
            }
            // Required fields not in header -> error
            foreach ($this->columns as $c) {
                if ($c['is_required'] && !array_key_exists($c['col_name'], $rowData)) {
                    $rowErrors[] = [$c['col_name'], 'missing_column', 'Required column not in upload'];
                }
            }

            // Lenient ingestion: log per-cell warnings BUT always insert the row.
            // Only data-type fatal failures (e.g. unparseable date returned a literal error)
            // mark the row warning count - the row data is still preserved as-is.
            if ($rowErrors) {
                foreach ($rowErrors as $e) {
                    $errInsert->execute([$uploadId, $total, $e[0], $e[1], $e[2]]);
                }
                $errCount++;
            }
            $valid++;
            $validRows[] = [
                'period_label' => $period,
                'key_hash'     => $keyParts ? md5(implode('|', $keyParts)) : null,
                'data'         => $rowData,
            ];
            $rowsIter->next();
        }

        // 5. Apply upload mode
        $replacedCount = $this->applyMode($uploadId, $mode, $validRows);

        // 6. Insert valid rows
        $insRow = $this->pdo->prepare(
            'INSERT INTO report_data (report_id, upload_id, row_index, period_label, key_hash, data_json, is_active, created_at)
             VALUES (?,?,?,?,?,?,1,NOW())'
        );
        $i = 0;
        $this->pdo->beginTransaction();
        try {
            foreach ($validRows as $r) {
                $i++;
                $insRow->execute([
                    $this->report['id'], $uploadId, $i,
                    $r['period_label'], $r['key_hash'],
                    json_encode($r['data'], JSON_UNESCAPED_UNICODE),
                ]);
            }
            $this->pdo->commit();
        } catch (Throwable $t) {
            $this->pdo->rollBack();
            $this->markUploadFailed($uploadId, 'Insert failed: ' . $t->getMessage());
            throw $t;
        }

        // 7. Finalize upload
        $this->pdo->prepare(
            'UPDATE report_uploads
             SET total_rows=?, valid_rows=?, error_rows=?, replaced_row_count=?,
                 status="completed", completed_at=NOW(), updated_at=NOW()
             WHERE id=?'
        )->execute([$total, $valid, $errCount, $replacedCount, $uploadId]);
        $this->upLog($uploadId, 'completed', "valid=$valid errors=$errCount replaced=$replacedCount");

        // 8. Materialize outputs
        $this->materializeOutputs($uploadId);

        return ['upload_id' => $uploadId, 'total' => $total, 'valid' => $valid, 'errors' => $errCount];
    }

    private function applyMode(int $uploadId, string $mode, array &$validRows): int
    {
        $reportId = $this->report['id'];
        $replaced = 0;

        if ($mode === 'replace_all') {
            $cnt = $this->pdo->prepare('SELECT COUNT(*) FROM report_data WHERE report_id=? AND is_active=1');
            $cnt->execute([$reportId]); $replaced = (int) $cnt->fetchColumn();
            $this->pdo->prepare(
                'INSERT INTO report_data_archive (report_id, upload_id, original_upload_id, row_index, period_label, key_hash, data_json, is_active, archived_at, archived_reason, created_at)
                 SELECT report_id, ?, upload_id, row_index, period_label, key_hash, data_json, 0, NOW(), "replace_all", created_at
                 FROM report_data WHERE report_id=?'
            )->execute([$uploadId, $reportId]);
            $this->pdo->prepare('DELETE FROM report_data WHERE report_id=?')->execute([$reportId]);
        } elseif ($mode === 'replace_by_period') {
            $periods = array_unique(array_filter(array_column($validRows, 'period_label')));
            if ($periods) {
                $in = implode(',', array_fill(0, count($periods), '?'));
                $cnt = $this->pdo->prepare("SELECT COUNT(*) FROM report_data WHERE report_id=? AND period_label IN ($in) AND is_active=1");
                $cnt->execute(array_merge([$reportId], $periods)); $replaced = (int) $cnt->fetchColumn();
                $this->pdo->prepare(
                    "INSERT INTO report_data_archive (report_id, upload_id, original_upload_id, row_index, period_label, key_hash, data_json, is_active, archived_at, archived_reason, created_at)
                     SELECT report_id, ?, upload_id, row_index, period_label, key_hash, data_json, 0, NOW(), 'replace_by_period', created_at
                     FROM report_data WHERE report_id=? AND period_label IN ($in)"
                )->execute(array_merge([$uploadId, $reportId], $periods));
                $this->pdo->prepare("DELETE FROM report_data WHERE report_id=? AND period_label IN ($in)")->execute(array_merge([$reportId], $periods));
            }
        } elseif ($mode === 'upsert') {
            $hashes = array_unique(array_filter(array_column($validRows, 'key_hash')));
            if ($hashes) {
                $in = implode(',', array_fill(0, count($hashes), '?'));
                $cnt = $this->pdo->prepare("SELECT COUNT(*) FROM report_data WHERE report_id=? AND key_hash IN ($in) AND is_active=1");
                $cnt->execute(array_merge([$reportId], $hashes)); $replaced = (int) $cnt->fetchColumn();
                $this->pdo->prepare(
                    "INSERT INTO report_data_archive (report_id, upload_id, original_upload_id, row_index, period_label, key_hash, data_json, is_active, archived_at, archived_reason, created_at)
                     SELECT report_id, ?, upload_id, row_index, period_label, key_hash, data_json, 0, NOW(), 'upsert', created_at
                     FROM report_data WHERE report_id=? AND key_hash IN ($in)"
                )->execute(array_merge([$uploadId, $reportId], $hashes));
                $this->pdo->prepare("DELETE FROM report_data WHERE report_id=? AND key_hash IN ($in)")->execute(array_merge([$reportId], $hashes));
            }
        }
        // 'append' does nothing here.
        return $replaced;
    }

    public function materializeOutputs(int $uploadId): void
    {
        $reportId = $this->report['id'];
        $outputs = $this->pdo->prepare('SELECT * FROM report_outputs WHERE report_id=? ORDER BY display_order, id');
        $outputs->execute([$reportId]);
        $outputs = $outputs->fetchAll();

        // Try to bump packet size for this session in case the user
        // already raised the global. Silent-fail if not allowed.
        try { $this->pdo->exec("SET SESSION max_allowed_packet = 268435456"); } catch (Throwable $t) {}

        // Load all active rows once
        $stmt = $this->pdo->prepare('SELECT data_json FROM report_data WHERE report_id=? AND is_active=1');
        $stmt->execute([$reportId]);
        $allRows = [];
        while ($row = $stmt->fetch()) {
            $allRows[] = json_decode($row['data_json'], true) ?: [];
        }

        $insOut = $this->pdo->prepare(
            'INSERT INTO generated_outputs (report_id, output_id, upload_id, generated_at, data_json) VALUES (?,?,?,NOW(),?)'
        );
        $del = $this->pdo->prepare('DELETE FROM generated_outputs WHERE report_id=? AND output_id=?');

        foreach ($outputs as $o) {
            // Skip 'detail' materialization — it's the full row dump and can blow
            // through max_allowed_packet on big files. The viewer pulls detail
            // rows live from report_data, so we don't need a cached copy.
            if ($o['output_type'] === 'detail') {
                $del->execute([$reportId, $o['id']]);
                continue;
            }

            $payload = $this->computeOutput($o, $allRows);
            $json = json_encode($payload, JSON_UNESCAPED_UNICODE);

            // Defensive: if even an aggregated payload exceeds 8 MB,
            // store an empty marker rather than crash the entire ingest.
            if (strlen($json) > 8 * 1024 * 1024) {
                $json = json_encode(['_truncated' => true, 'reason' => 'payload too large for max_allowed_packet']);
            }

            $del->execute([$reportId, $o['id']]);
            $insOut->execute([$reportId, $o['id'], $uploadId, $json]);
        }
    }

    private function computeOutput(array $output, array $rows): array
    {
        $type     = $output['output_type'];
        $groupBy  = json_decode($output['group_by_cols'] ?? '[]', true) ?: [];
        $aggCols  = json_decode($output['agg_cols']      ?? '[]', true) ?: [];

        // Add synthetic period columns if any group_by uses them
        $rows = $this->addSyntheticCols($rows, $groupBy);

        if ($type === 'detail') {
            return ['rows' => $rows];
        }

        if ($type === 'kpi' || $type === 'dashboard') {
            $kpis = [];
            foreach ($aggCols as $a) {
                $kpis[] = [
                    'label'  => $a['label'] ?? $a['col'],
                    'col'    => $a['col'],
                    'agg'    => $a['agg'] ?? 'sum',
                    'value'  => $this->aggregate($rows, $a['col'], $a['agg'] ?? 'sum'),
                    'format' => $a['format'] ?? 'number',
                ];
            }
            return ['kpis' => $kpis, 'row_count' => count($rows)];
        }

        if (in_array($type, ['summary','pivot','chart'], true)) {
            // group + aggregate
            $groups = [];
            foreach ($rows as $r) {
                $key = [];
                foreach ($groupBy as $g) {
                    $key[] = (string) ($r[$g] ?? '');
                }
                $k = implode('||', $key);
                if (!isset($groups[$k])) {
                    $groups[$k] = ['_keys' => array_combine($groupBy, $key), '_rows' => []];
                }
                $groups[$k]['_rows'][] = $r;
            }
            $resultRows = [];
            foreach ($groups as $g) {
                $row = $g['_keys'];
                foreach ($aggCols as $a) {
                    $alias = $a['label'] ?? $a['col'];
                    $row[$alias] = $this->aggregate($g['_rows'], $a['col'], $a['agg'] ?? 'sum');
                }
                $resultRows[] = $row;
            }
            // Sort by first group_by column (asc)
            if ($groupBy) usort($resultRows, fn($a,$b) => strcmp((string)$a[$groupBy[0]], (string)$b[$groupBy[0]]));
            return ['group_by' => $groupBy, 'agg_cols' => $aggCols, 'rows' => $resultRows];
        }

        return ['rows' => []];
    }

    private function addSyntheticCols(array $rows, array $groupBy): array
    {
        $needsYear  = in_array('dos_year',  $groupBy, true);
        $needsMonth = in_array('dos_month', $groupBy, true);
        $needsDay   = in_array('dos_day',   $groupBy, true);
        if (!$needsYear && !$needsMonth && !$needsDay) return $rows;

        $periodCol = $this->report['period_column'];
        if (!$periodCol) return $rows;
        $periodColName = $this->labelToColName($periodCol) ?? $periodCol;

        foreach ($rows as &$r) {
            $val = $r[$periodColName] ?? null;
            if (!$val) continue;
            $ts = is_numeric($val) ? (int) $val : strtotime((string) $val);
            if ($ts === false) continue;
            if ($needsYear)  $r['dos_year']  = date('Y', $ts);
            if ($needsMonth) $r['dos_month'] = date('Y-m', $ts);
            if ($needsDay)   $r['dos_day']   = date('Y-m-d', $ts);
        }
        unset($r);
        return $rows;
    }

    private function labelToColName(string $label): ?string
    {
        $lc = strtolower(trim($label));
        return $this->colByLabel[$lc]['col_name'] ?? null;
    }

    private function aggregate(array $rows, string $col, string $agg): float|int
    {
        if ($agg === 'count') return count($rows);
        $vals = [];
        foreach ($rows as $r) {
            $v = $r[$col] ?? null;
            if (is_numeric($v)) $vals[] = (float) $v;
        }
        if (!$vals) return 0;
        return match ($agg) {
            'sum' => array_sum($vals),
            'avg' => array_sum($vals) / count($vals),
            'min' => min($vals),
            'max' => max($vals),
            default => array_sum($vals),
        };
    }

    /** @return array{0: mixed, 1: ?string} [cleaned, error]
     *  Lenient mode: empty values are returned cleanly; required-but-empty
     *  is logged as a soft warning but the row is still ingested. */
    private function validateValue(mixed $raw, array $col): array
    {
        $val = is_string($raw) ? trim($raw) : $raw;
        $isEmpty = ($val === '' || $val === null);
        if ($isEmpty) {
            // Soft warning for required fields; row is NOT rejected.
            return [null, $col['is_required'] ? 'Required field is empty (row kept)' : null];
        }
        $rules = json_decode($col['validation_rule'] ?? 'null', true) ?: [];

        // Lenient: when a value can't be coerced to the declared type, keep
        // the original string and return a warning. Caller still inserts the row.
        switch ($col['data_type']) {
            case 'int':
                if (!is_numeric($val)) return [$val, 'Not an integer (kept as text)'];
                return [(int) $val, null];
            case 'decimal':
                if (!is_numeric($val)) return [$val, 'Not a number (kept as text)'];
                return [(float) $val, null];
            case 'date':
            case 'datetime':
                // Excel serial fallback (e.g. 46028 = 2026-02-06). Excel epoch
                // is 1900-01-00 with the 1900-leap-year bug - mirror xlsx-reader.
                if (is_numeric($val) && $val > 1 && $val < 100000) {
                    $serial = (float) $val;
                    $unix = -2208988800 + ($serial - 1) * 86400;
                    if ($serial >= 60) $unix -= 86400;
                    $hasTime = ($serial - floor($serial)) > 1e-9;
                    return [($hasTime || $col['data_type']==='datetime')
                        ? gmdate('Y-m-d H:i:s', (int) round($unix))
                        : gmdate('Y-m-d', (int) round($unix)), null];
                }
                // Try strtotime with a few common formats first
                $s = (string) $val;
                $tryFormats = ['Y-m-d', 'Y-m-d H:i:s', 'm/d/Y', 'd/m/Y', 'd-m-Y', 'd-M-Y', 'M d, Y', 'd M Y'];
                foreach ($tryFormats as $fmt) {
                    $dt = DateTime::createFromFormat($fmt, $s);
                    if ($dt !== false) {
                        return [$col['data_type'] === 'date' ? $dt->format('Y-m-d') : $dt->format('Y-m-d H:i:s'), null];
                    }
                }
                $ts = strtotime($s);
                if ($ts === false) return [$val, 'Unparseable date (kept as text)'];
                return [$col['data_type'] === 'date' ? date('Y-m-d', $ts) : date('Y-m-d H:i:s', $ts), null];
            case 'bool':
                return [filter_var($val, FILTER_VALIDATE_BOOLEAN), null];
            case 'text':
            default:
                $val = (string) $val;
                $warn = null;
                if (isset($rules['max_length']) && strlen($val) > $rules['max_length']) $warn = "Exceeds max length {$rules['max_length']}";
                if (isset($rules['regex']) && !preg_match('/' . $rules['regex'] . '/', $val)) $warn = 'Pattern mismatch';
                if (isset($rules['in_list']) && is_array($rules['in_list']) && !in_array($val, $rules['in_list'], true)) $warn = 'Not in allowed list';
                return [$val, $warn];
        }
    }

    private function derivePeriodLabel(mixed $val, string $type): ?string
    {
        if ($val === null || $val === '') return null;
        if ($type === 'date' || $type === 'datetime') {
            $ts = strtotime((string) $val);
            return $ts !== false ? date('Y-m', $ts) : null;
        }
        return (string) $val;
    }

    private function iterateXlsx(string $path): Generator
    {
        $reader = new XlsxReader($path);
        try {
            // Find the sheet that has a non-empty header (prefer one that matches more known labels)
            $bestSheet = 0; $bestScore = -1;
            foreach ($reader->sheets() as $i => $sh) {
                $found = 0; $iter = $reader->rows($i); $iter->rewind();
                $hdr = $iter->current() ?: [];
                foreach ($hdr as $h) {
                    $key = strtolower(trim((string) $h));
                    if (isset($this->colByLabel[$key])) $found++;
                }
                if ($found > $bestScore) { $bestScore = $found; $bestSheet = $i; }
            }
            yield from $reader->rows($bestSheet);
        } finally {
            // generator close => xlsx closed
        }
    }

    private function iterateCsv(string $path, string $sep = ','): Generator
    {
        $h = fopen($path, 'r');
        if (!$h) throw new RuntimeException("Cannot open CSV: $path");
        try {
            while (($row = fgetcsv($h, 0, $sep)) !== false) {
                yield $row;
            }
        } finally {
            fclose($h);
        }
    }

    private function upLog(int $uploadId, string $action, ?string $msg = null): void
    {
        $this->pdo->prepare(
            'INSERT INTO upload_logs (upload_id, action, message, created_at) VALUES (?,?,?,NOW())'
        )->execute([$uploadId, $action, $msg]);
    }

    private function markUploadFailed(int $uploadId, string $msg): void
    {
        $this->pdo->prepare("UPDATE report_uploads SET status='failed', notes=?, completed_at=NOW() WHERE id=?")
            ->execute([$msg, $uploadId]);
        $this->upLog($uploadId, 'failed', $msg);
    }
}
