math_cms/app/Services/Import/TextbookExcelImporter.php

<?php

namespace App\Services\Import;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Log;
use App\Services\TextbookApiService;

class TextbookExcelImporter
{
    protected $apiService;

    public function __construct(TextbookApiService $apiService)
    {
        $this->apiService = $apiService;
    }

    /**
     * 生成教材系列Excel模板
     */
    public function generateTextbookSeriesTemplate(): string
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置列标题
        $headers = [
            'A1' => '系列名称',
            'B1' => '别名',
            'C1' => '出版社',
            'D1' => '适用地区',
            'E1' => '适用学段',
            'F1' => '是否启用',
            'G1' => '排序',
            'H1' => '扩展信息(JSON)',
        ];

        foreach ($headers as $cell => $header) {
            $sheet->setCellValue($cell, $header);
        }

        // 设置表头样式
        $headerStyle = [
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'color' => ['rgb' => '0EA5E9']],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
        ];
        $sheet->getStyle('A1:H1')->applyFromArray($headerStyle);

        // 设置列宽
        $sheet->getColumnDimension('A')->setWidth(20);
        $sheet->getColumnDimension('B')->setWidth(15);
        $sheet->getColumnDimension('C')->setWidth(25);
        $sheet->getColumnDimension('D')->setWidth(20);
        $sheet->getColumnDimension('E')->setWidth(20);
        $sheet->getColumnDimension('F')->setWidth(12);
        $sheet->getColumnDimension('G')->setWidth(10);
        $sheet->getColumnDimension('H')->setWidth(30);

        // 添加示例数据
        $examples = [
            ['人教版', 'pep', '人民教育出版社', '全国', '["primary","junior","senior"]', '是', '1', '{"website":"http://www.pep.com.cn"}'],
            ['北师大版', 'bsd', '北京师范大学出版社', '全国', '["primary","junior","senior"]', '是', '2', '{}'],
            ['苏教版', 'js', '江苏教育出版社', '江苏省', '["primary","junior"]', '是', '3', '{}'],
        ];

        for ($i = 0; $i < count($examples); $i++) {
            $row = $i + 2;
            for ($j = 0; $j < count($examples[$i]); $j++) {
                $column = chr(65 + $j); // A, B, C...
                $sheet->setCellValue($column . $row, $examples[$i][$j]);
            }
        }

        // 添加说明信息
        $sheet->setCellValue('A6', '填写说明:');
        $sheet->setCellValue('A7', '1. 系列名称: 必填，如"人教版"、"北师大版"');
        $sheet->setCellValue('A8', '2. 别名: 必填，唯一标识，如"pep"、"bsd"');
        $sheet->setCellValue('A9', '3. 出版社: 必填，如"人民教育出版社"');
        $sheet->setCellValue('A10', '4. 适用地区: 可选，如"全国"、"江苏省"');
        $sheet->setCellValue('A11', '5. 适用学段: 必填，JSON格式["primary","junior","senior"]');
        $sheet->setCellValue('A12', '6. 是否启用: 填"是"或"否"');
        $sheet->setCellValue('A13', '7. 排序: 数字，越小越靠前');
        $sheet->setCellValue('A14', '8. 扩展信息: JSON格式，可选');

        // 保存文件
        $fileName = 'textbook_series_template_' . date('Ymd_His') . '.xlsx';
        $filePath = storage_path('app/templates/' . $fileName);

        // 确保目录存在
        if (!is_dir(dirname($filePath))) {
            mkdir(dirname($filePath), 0755, true);
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($filePath);

        return $filePath;
    }

    /**
     * 生成教材Excel模板
     */
    public function generateTextbookTemplate(): string
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置列标题
        $headers = [
            'A1' => '系列ID',
            'B1' => '学段',
            'C1' => '年级',
            'D1' => '学期',
            'E1' => '命名体系',
            'F1' => '版本',
            'G1' => '模块类型',
            'H1' => '册次',
            'I1' => '旧体系编码',
            'J1' => '课标年代',
            'K1' => '修订年份',
            'L1' => '审定年份',
            'M1' => '版次标识',
            'N1' => 'ISBN',
            'O1' => '封面路径',
            'P1' => '官方书名',
            'Q1' => '展示名称',
            'R1' => '别名(JSON)',
            'S1' => '状态',
            'T1' => '扩展信息(JSON)',
        ];

        foreach ($headers as $cell => $header) {
            $sheet->setCellValue($cell, $header);
        }

        // 设置表头样式
        $headerStyle = [
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'color' => ['rgb' => '0EA5E9']],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
        ];
        $sheet->getStyle('A1:T1')->applyFromArray($headerStyle);

        // 设置列宽
        $columnWidths = [10, 12, 10, 10, 12, 10, 15, 10, 15, 12, 12, 12, 15, 15, 20, 30, 30, 20, 12, 30];
        foreach ($columnWidths as $i => $width) {
            $sheet->getColumnDimensionByColumn($i + 1)->setWidth($width);
        }

        // 添加示例数据
        $examples = [
            ['1', '小学', '1', '上册', '', '', '', '', '', '2011', '', '', '', '', '', '人教版小学数学一年级上册', '', '[]', '草稿', '{}'],
            ['1', '小学', '1', '下册', '', '', '', '', '', '2011', '', '', '', '', '', '人教版小学数学一年级下册', '', '[]', '草稿', '{}'],
            ['1', '初中', '7', '上册', '', '', '', '', '', '2022', '', '', '', '', '', '人教版初中数学七年级上册', '', '[]', '草稿', '{}'],
            ['1', '高中', '10', '', '新体系', 'A版', '必修', '1', '', '2017', '2020', '', '', '', '', '人教A版高中数学必修第一册', '', '[]', '草稿', '{}'],
        ];

        for ($i = 0; $i < count($examples); $i++) {
            $row = $i + 2;
            for ($j = 0; $j < count($examples[$i]); $j++) {
                $column = chr(65 + $j); // A, B, C...
                $sheet->setCellValue($column . $row, $examples[$i][$j]);
            }
        }

        // 添加说明信息
        $sheet->setCellValue('A7', '填写说明:');
        $sheet->setCellValue('A8', '1. 系列ID: 必填，对应教材系列的ID（可在教材系列列表查看）');
        $sheet->setCellValue('A9', '2. 学段: 必填，小学/初中/高中');
        $sheet->setCellValue('A10', '3. 年级: 数字，小学1-6，初中7-9，高中10-12');
        $sheet->setCellValue('A11', '4. 学期: 上册/下册（高中可留空）');
        $sheet->setCellValue('A12', '5. 命名体系: 新体系/旧体系，高中适用');
        $sheet->setCellValue('A13', '6. 版本: A版/B版，高中新体系适用');
        $sheet->setCellValue('A14', '7. 模块类型: 必修/选择性必修/选修，高中适用');
        $sheet->setCellValue('A15', '8. 册次: 数字，高中新体系适用');
        $sheet->setCellValue('A16', '9. 旧体系编码: 高中旧体系适用，如"必修1"');
        $sheet->setCellValue('A17', '10. 课标年代: 义务教育2011/2022，高中2017');
        $sheet->setCellValue('A18', '11. 修订年份: 高中为2020');
        $sheet->setCellValue('A19', '12. 审定年份: 如2024');
        $sheet->setCellValue('A20', '13. 版次标识: 如"2024秋版"');
        $sheet->setCellValue('A21', '14. ISBN: 可选');
        $sheet->setCellValue('A22', '15. 封面路径: 可选');
        $sheet->setCellValue('A23', '16. 官方书名: 可选，系统自动生成');
        $sheet->setCellValue('A24', '17. 展示名称: 可选，站内显示');
        $sheet->setCellValue('A25', '18. 别名: JSON格式，可选');
        $sheet->setCellValue('A26', '19. 状态: 草稿/已发布/已归档');
        $sheet->setCellValue('A27', '20. 扩展信息: JSON格式，可选');

        // 保存文件
        $fileName = 'textbook_template_' . date('Ymd_His') . '.xlsx';
        $filePath = storage_path('app/templates/' . $fileName);

        if (!is_dir(dirname($filePath))) {
            mkdir(dirname($filePath), 0755, true);
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($filePath);

        return $filePath;
    }

    /**
     * 导入教材系列Excel文件
     */
    public function importTextbookSeries(string $filePath): array
    {
        try {
            $spreadsheet = IOFactory::load($filePath);
            $sheet = $spreadsheet->getActiveSheet();
            $data = $sheet->toArray();

            // 跳过标题行
            $rows = array_slice($data, 1);
            $successCount = 0;
            $errorCount = 0;
            $errors = [];

            foreach ($rows as $index => $row) {
                try {
                    // 验证必填字段
                    if (empty($row[0]) || empty($row[1]) || empty($row[2]) || empty($row[4])) {
                        throw new \Exception('必填字段不能为空');
                    }

                    // 解析适用学段
                    $stages = json_decode($row[4], true);
                    if (!is_array($stages)) {
                        throw new \Exception('适用学段必须是JSON数组格式');
                    }

                    // 解析是否启用
                    $isActive = $row[5] === '是' || $row[5] === 'true' || $row[5] === '1';

                    // 解析扩展信息
                    $meta = !empty($row[7]) ? json_decode($row[7], true) : [];
                    if (!is_array($meta)) {
                        $meta = [];
                    }

                    $textbookSeriesData = [
                        'name' => $row[0],
                        'slug' => $row[1],
                        'publisher' => $row[2],
                        'region' => $row[3] ?: null,
                        'stages' => json_encode($stages),
                        'is_active' => $isActive,
                        'sort_order' => (int)($row[6] ?: 0),
                        'meta' => json_encode($meta),
                    ];

                    // 通过API创建教材系列
                    $result = $this->apiService->createTextbookSeries($textbookSeriesData);

                    if ($result && isset($result['data'])) {
                        $successCount++;
                    } else {
                        throw new \Exception('API创建失败');
                    }

                } catch (\Exception $e) {
                    $errorCount++;
                    $errors[] = "第" . ($index + 2) . "行: " . $e->getMessage();
                }
            }

            return [
                'success' => true,
                'success_count' => $successCount,
                'error_count' => $errorCount,
                'errors' => $errors,
            ];

        } catch (\Exception $e) {
            Log::error('Excel导入失败', ['error' => $e->getMessage()]);
            return [
                'success' => false,
                'message' => '文件解析失败: ' . $e->getMessage(),
            ];
        }
    }

    /**
     * 导入教材Excel文件
     */
    public function importTextbook(string $filePath): array
    {
        // 中文到英文的映射
        $stageMap = [
            '小学' => 'primary',
            '初中' => 'junior',
            '高中' => 'senior',
            // 兼容英文输入
            'primary' => 'primary',
            'junior' => 'junior',
            'senior' => 'senior',
        ];

        $semesterMap = [
            '上册' => 1,
            '下册' => 2,
            '1' => 1,
            '2' => 2,
        ];

        $namingSchemeMap = [
            '新体系' => 'new',
            '旧体系' => 'old',
            'new' => 'new',
            'old' => 'old',
        ];

        $trackMap = [
            'A版' => 'A',
            'B版' => 'B',
            'A' => 'A',
            'B' => 'B',
        ];

        $statusMap = [
            '草稿' => 'draft',
            '已发布' => 'published',
            '已归档' => 'archived',
            'draft' => 'draft',
            'published' => 'published',
            'archived' => 'archived',
        ];

        try {
            $spreadsheet = IOFactory::load($filePath);
            $sheet = $spreadsheet->getActiveSheet();
            $data = $sheet->toArray();

            // 跳过标题行
            $rows = array_slice($data, 1);
            $successCount = 0;
            $errorCount = 0;
            $errors = [];

            foreach ($rows as $index => $row) {
                try {
                    // 跳过空行
                    if (empty($row[0]) && empty($row[1])) {
                        continue;
                    }

                    // 验证必填字段
                    if (empty($row[0]) || empty($row[1])) {
                        throw new \Exception('系列ID和学段不能为空');
                    }

                    // 转换学段
                    $stageInput = trim($row[1]);
                    $stage = $stageMap[$stageInput] ?? null;
                    if (!$stage) {
                        throw new \Exception("无效的学段: {$stageInput}，请填写：小学/初中/高中");
                    }

                    // 转换学期
                    $semester = null;
                    if (!empty($row[3])) {
                        $semesterInput = trim($row[3]);
                        $semester = $semesterMap[$semesterInput] ?? null;
                        if ($semester === null) {
                            throw new \Exception("无效的学期: {$semesterInput}，请填写：上册/下册");
                        }
                    }

                    // 转换命名体系
                    $namingScheme = null;
                    if (!empty($row[4])) {
                        $namingInput = trim($row[4]);
                        $namingScheme = $namingSchemeMap[$namingInput] ?? null;
                    }

                    // 转换版本
                    $track = null;
                    if (!empty($row[5])) {
                        $trackInput = trim($row[5]);
                        $track = $trackMap[$trackInput] ?? $trackInput;
                    }

                    // 转换状态
                    $statusInput = trim($row[18] ?? 'draft');
                    $status = $statusMap[$statusInput] ?? 'draft';

                    // 解析别名
                    $aliases = !empty($row[17]) ? json_decode($row[17], true) : [];
                    if (!is_array($aliases)) {
                        $aliases = [];
                    }

                    // 解析扩展信息
                    $meta = !empty($row[19]) ? json_decode($row[19], true) : [];
                    if (!is_array($meta)) {
                        $meta = [];
                    }

                    $textbookData = [
                        'series_id' => (int)$row[0],
                        'stage' => $stage,
                        'grade' => $row[2] ?: null,
                        'semester' => $semester,
                        'naming_scheme' => $namingScheme,
                        'track' => $track,
                        'module_type' => $row[6] ?: null,
                        'volume_no' => $row[7] ? (int)$row[7] : null,
                        'legacy_code' => $row[8] ?: null,
                        'curriculum_standard_year' => $row[9] ?: null,
                        'curriculum_revision_year' => $row[10] ?: null,
                        'approval_year' => $row[11] ?: null,
                        'edition_label' => $row[12] ?: null,
                        'isbn' => $row[13] ?: null,
                        'cover_path' => $row[14] ?: null,
                        'official_title' => $row[15] ?: null,
                        'display_title' => $row[16] ?: null,
                        'aliases' => json_encode($aliases),
                        'status' => $status,
                        'meta' => json_encode($meta),
                    ];

                    // 通过API创建教材
                    $result = $this->apiService->createTextbook($textbookData);

                    if ($result && isset($result['data'])) {
                        $successCount++;
                    } else {
                        throw new \Exception('API创建失败');
                    }

                } catch (\Exception $e) {
                    $errorCount++;
                    $errors[] = "第" . ($index + 2) . "行: " . $e->getMessage();
                }
            }

            return [
                'success' => true,
                'success_count' => $successCount,
                'error_count' => $errorCount,
                'errors' => $errors,
            ];

        } catch (\Exception $e) {
            Log::error('Excel导入失败', ['error' => $e->getMessage()]);
            return [
                'success' => false,
                'message' => '文件解析失败: ' . $e->getMessage(),
            ];
        }
    }

    /**
     * 生成教材目录Excel模板
     */
    public function generateTextbookCatalogTemplate(): string
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置列标题
        $headers = [
            'A1' => '教材ID',
            'B1' => '目录标题',
            'C1' => '显示编号',
            'D1' => '节点类型',
            'E1' => '层级深度',
            'F1' => '排序',
            'G1' => '父级ID',
            'H1' => '路径键',
            'I1' => '起始页码',
            'J1' => '结束页码',
            'K1' => '是否必修',
            'L1' => '是否选修',
            'M1' => '标签(JSON)',
            'N1' => '扩展信息(JSON)',
        ];

        foreach ($headers as $cell => $header) {
            $sheet->setCellValue($cell, $header);
        }

        // 设置表头样式
        $headerStyle = [
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'color' => ['rgb' => '0EA5E9']],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
        ];
        $sheet->getStyle('A1:N1')->applyFromArray($headerStyle);

        // 设置列宽
        $columnWidths = [12, 30, 15, 15, 12, 10, 12, 25, 12, 12, 12, 12, 20, 30];
        foreach ($columnWidths as $i => $width) {
            $sheet->getColumnDimensionByColumn($i + 1)->setWidth($width);
        }

        // 添加示例数据（使用中文节点类型）
        $examples = [
            ['1', '第一章 有理数', '1', '章', '1', '1', '', 'chapter-1', '1', '20', '是', '否', '[]', '{}'],
            ['1', '1.1 正数和负数', '1.1', '节', '2', '1', '', 'chapter-1-section-1', '2', '5', '是', '否', '[]', '{}'],
            ['1', '1.2 有理数', '1.2', '节', '2', '2', '', 'chapter-1-section-2', '6', '10', '是', '否', '[]', '{}'],
            ['1', '1.2.1 有理数的概念', '1.2.1', '小节', '3', '1', '', 'chapter-1-section-2-sub-1', '6', '7', '是', '否', '[]', '{}'],
            ['1', '第二章 整式的加减', '2', '章', '1', '2', '', 'chapter-2', '21', '40', '是', '否', '[]', '{}'],
        ];

        for ($i = 0; $i < count($examples); $i++) {
            $row = $i + 2;
            for ($j = 0; $j < count($examples[$i]); $j++) {
                $column = chr(65 + $j); // A, B, C...
                $sheet->setCellValue($column . $row, $examples[$i][$j]);
            }
        }

        // 添加说明信息
        $sheet->setCellValue('A8', '填写说明:');
        $sheet->setCellValue('A9', '1. 教材ID: 必填，对应教材的ID（可在教材列表查看）');
        $sheet->setCellValue('A10', '2. 目录标题: 必填，如"第一章 有理数"');
        $sheet->setCellValue('A11', '3. 显示编号: 可选，如"1"、"1.1"、"1.2.1"');
        $sheet->setCellValue('A12', '4. 节点类型: 章/节/小节/条目/项目学习');
        $sheet->setCellValue('A13', '5. 层级深度: 数字，1=章，2=节，3=小节');
        $sheet->setCellValue('A14', '6. 排序: 数字，同级内排序');
        $sheet->setCellValue('A15', '7. 父级ID: 可选，上级节点的ID（首次导入可留空，系统自动处理）');
        $sheet->setCellValue('A16', '8. 路径键: 可选，层级路径标识');
        $sheet->setCellValue('A17', '9. 起始页码: 数字，可选');
        $sheet->setCellValue('A18', '10. 结束页码: 数字，可选');
        $sheet->setCellValue('A19', '11. 是否必修: 是/否');
        $sheet->setCellValue('A20', '12. 是否选修: 是/否');
        $sheet->setCellValue('A21', '13. 标签: JSON格式，可选');
        $sheet->setCellValue('A22', '14. 扩展信息: JSON格式，可选');

        // 保存文件
        $fileName = 'textbook_catalog_template_' . date('Ymd_His') . '.xlsx';
        $filePath = storage_path('app/templates/' . $fileName);

        if (!is_dir(dirname($filePath))) {
            mkdir(dirname($filePath), 0755, true);
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($filePath);

        return $filePath;
    }

    /**
     * 导入教材目录Excel文件
     */
    public function importTextbookCatalog(string $filePath, int $textbookId): array
    {
        // 中文到英文的映射
        $nodeTypeMap = [
            '章' => 'chapter',
            '节' => 'section',
            '小节' => 'subsection',
            '条目' => 'item',
            '项目学习' => 'project',
            // 兼容英文输入
            'chapter' => 'chapter',
            'section' => 'section',
            'subsection' => 'subsection',
            'item' => 'item',
            'project' => 'project',
        ];

        try {
            // 首先验证Excel文件
            $spreadsheet = IOFactory::load($filePath);
            $sheet = $spreadsheet->getActiveSheet();
            $data = $sheet->toArray();

            // 跳过标题行
            $rows = array_slice($data, 1);
            $successCount = 0;
            $errorCount = 0;
            $errors = [];

            foreach ($rows as $index => $row) {
                try {
                    // 跳过空行
                    if (empty($row[0]) && empty($row[1])) {
                        continue;
                    }

                    // 验证必填字段
                    if (empty($row[0]) || empty($row[1])) {
                        throw new \Exception('教材ID和目录标题不能为空');
                    }

                    // 验证教材ID是否匹配
                    if ((int)$row[0] !== $textbookId) {
                        throw new \Exception('教材ID必须与选择的教材ID一致');
                    }

                    // 转换节点类型
                    $nodeTypeInput = trim($row[3] ?? '章');
                    $nodeType = $nodeTypeMap[$nodeTypeInput] ?? 'chapter';

                    // 解析是否必修/选修
                    $isRequired = $row[10] === '是' || $row[10] === 'true' || $row[10] === '1';
                    $isElective = $row[11] === '是' || $row[11] === 'true' || $row[11] === '1';

                    // 解析标签和扩展信息
                    $tags = !empty($row[12]) ? json_decode($row[12], true) : [];
                    if (!is_array($tags)) {
                        $tags = [];
                    }

                    $meta = !empty($row[13]) ? json_decode($row[13], true) : [];
                    if (!is_array($meta)) {
                        $meta = [];
                    }

                    // 构建目录数据（这里我们只是验证，实际导入通过API完成）
                    $catalogData = [
                        'textbook_id' => (int)$row[0],
                        'title' => $row[1],
                        'display_no' => $row[2] ?: null,
                        'node_type' => $nodeType,
                        'depth' => $row[4] ? (int)$row[4] : 1,
                        'sort_order' => $row[5] ? (int)$row[5] : 0,
                        'parent_id' => $row[6] ?: null,
                        'path_key' => $row[7] ?: null,
                        'page_start' => $row[8] ? (int)$row[8] : null,
                        'page_end' => $row[9] ? (int)$row[9] : null,
                        'is_required' => $isRequired,
                        'is_elective' => $isElective,
                        'tags' => json_encode($tags),
                        'meta' => json_encode($meta),
                    ];

                    // 验证数据（实际创建通过API完成）
                    $successCount++;

                } catch (\Exception $e) {
                    $errorCount++;
                    $errors[] = "第" . ($index + 2) . "行: " . $e->getMessage();
                }
            }

            return [
                'success' => true,
                'success_count' => $successCount,
                'error_count' => $errorCount,
                'errors' => $errors,
            ];

        } catch (\Exception $e) {
            Log::error('Excel导入失败', ['error' => $e->getMessage()]);
            return [
                'success' => false,
                'message' => '文件解析失败: ' . $e->getMessage(),
            ];
        }
    }
}