<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;

class AnalyzeAccountingData extends Command
{
    protected $signature = 'analyze:accounting';
    protected $description = 'Analyze accounting data files';

    public function handle()
    {
        $this->info('=== 協會帳務資料分析 ===');
        $this->newLine();

        $files = [
            '2024尤塞氏症及視聽雙弱協會帳務.xlsx' => '協會行政資料/協會帳務/2024尤塞氏症及視聽雙弱協會帳務.xlsx',
            '2025 收入支出總表 (含會計科目編號).xlsx' => '協會行政資料/協會帳務/2025 收入支出總表 (含會計科目編號).xlsx',
            '2025 協會預算試編.xlsx' => '協會行政資料/協會帳務/2025 協會預算試編.xlsx',
        ];

        foreach ($files as $name => $path) {
            if (!file_exists($path)) {
                $this->error("檔案不存在: {$name}");
                continue;
            }

            $this->info("📊 分析檔案: {$name}");
            $this->info("📅 最後更新: " . date('Y-m-d H:i:s', filemtime($path)));
            $this->info("📦 檔案大小: " . number_format(filesize($path)) . " bytes");
            $this->newLine();

            try {
                $spreadsheet = IOFactory::load($path);

                $this->info("工作表列表:");
                foreach ($spreadsheet->getAllSheets() as $index => $sheet) {
                    $sheetName = $sheet->getTitle();
                    $highestRow = $sheet->getHighestRow();
                    $highestColumn = $sheet->getHighestColumn();

                    $this->line("  - {$sheetName} (範圍: A1:{$highestColumn}{$highestRow}, 共 {$highestRow} 列)");

                    // 讀取前5行作為預覽
                    if ($highestRow > 0) {
                        $this->info("    前5行預覽:");
                        for ($row = 1; $row <= min(5, $highestRow); $row++) {
                            $rowData = [];
                            for ($col = 'A'; $col <= min('J', $highestColumn); $col++) {
                                $cellValue = $sheet->getCell($col . $row)->getValue();
                                if (!empty($cellValue)) {
                                    $rowData[] = substr($cellValue, 0, 30);
                                }
                            }
                            if (!empty($rowData)) {
                                $this->line("      Row {$row}: " . implode(' | ', $rowData));
                            }
                        }
                        $this->newLine();
                    }
                }

            } catch (\Exception $e) {
                $this->error("讀取失敗: " . $e->getMessage());
            }

            $this->info(str_repeat('─', 80));
            $this->newLine();
        }

        return 0;
    }
}