Excel Importer

// @ts-nocheck
import React, { useState, useEffect } from 'react';
import ExcelJS from 'exceljs';
import { Input } from "@/components/ui/input";
import { Card, CardHeader, CardTitle, CardContent, CardDescription } from "@/components/ui/card";
import { Label } from "@/components/ui/label";
import { Alert, AlertDescription, AlertTitle } from "@/components/ui/alert";
import { Table, TableBody, TableCell, TableHead, TableHeader, TableRow } from "@/components/ui/table";
import { Button } from "@/components/ui/button";
import { DropdownMenu, DropdownMenuTrigger, DropdownMenuContent, DropdownMenuCheckboxItem, DropdownMenuLabel, DropdownMenuSeparator } from "@/components/ui/dropdown-menu";
import { Select, SelectContent, SelectItem, SelectTrigger, SelectValue } from "@/components/ui/select";
import { Progress } from "@/components/ui/progress";
import { Loader2, FileWarning, FileUp, Columns, Upload, Database } from 'lucide-react';

// Helper function to convert buffer to base64 for display
function bufferToBase64(buffer) {
  let binary = '';
  const bytes = new Uint8Array(buffer);
  for (let i = 0; i < bytes.byteLength; i++) {
    binary += String.fromCharCode(bytes[i]);
  }
  return window.btoa(binary);
}

// Component to render cell content (image, link, or text)
const CellContent = ({ data }) => {
  if (data?.isImage) {
    return <img src={data.src} alt="Embedded" className="max-w-full h-auto object-contain rounded" style={{ maxWidth: '150px', maxHeight: '100px' }} />;
  }
  if (data?.hyperlink) {
    return <a href={data.hyperlink} target="_blank" rel="noopener noreferrer" className="text-blue-600 hover:underline">{data.text}</a>;
  }
  if (data?.richText) {
    return data.richText.map((rt, i) => <span key={i}>{rt.text}</span>);
  }
  return data?.toString() || '';
};

export default function ExcelImporterBlock() {
  const [sheets, setSheets] = useState([]);
  const [isLoading, setIsLoading] = useState(false);
  const [error, setError] = useState(null);
  const [fileName, setFileName] = useState('');

  const [availableTables, setAvailableTables] = useState([]);
  const [isFetchingTables, setIsFetchingTables] = useState(false);
  const [tableId, setTableId] = useState('');
  const [tableFields, setTableFields] = useState([]);
  const [isFetchingFields, setIsFetchingFields] = useState(false);
  const [fieldError, setFieldError] = useState(null);

  const [columnMappings, setColumnMappings] = useState({});
  const [importingSheet, setImportingSheet] = useState(null);
  const [importProgress, setImportProgress] = useState(0);
  const [importResults, setImportResults] = useState({});

  const resetState = () => {
    setSheets([]);
    setError(null);
    setFileName('');
    setTableId('');
    setTableFields([]);
    setAvailableTables([]);
    setFieldError(null);
    setColumnMappings({});
    setImportingSheet(null);
    setImportResults({});
  };

  const fetchAvailableTables = async () => {
    setIsFetchingTables(true);
    try {
      const tables = await eidos.currentSpace.tree.list({ type: 'table', is_deleted: false });
      setAvailableTables(tables);
    } catch (err) {
      console.error("Error fetching tables:", err);
      setFieldError("无法加载可用表格列表。");
    } finally {
      setIsFetchingTables(false);
    }
  };

  useEffect(() => {
    const fetchTableFields = async () => {
      if (!tableId) {
        setTableFields([]);
        setFieldError(null);
        return;
      }
      setIsFetchingFields(true);
      setFieldError(null);
      setTableFields([]);
      try {
        // Assuming the table name in eidos__columns is prefixed with 'tb_'
        const query = `select * from eidos__columns where table_name = 'tb_${tableId}'`;
        const fields = await eidos.currentSpace.sqlQuery2(query);
        setTableFields(fields);
      } catch (err) {
        console.error("Error fetching table fields:", err);
        setFieldError(`加载字段失败。请检查表格 "${tableId}" 是否正确以及您是否有查询权限。`);
      } finally {
        setIsFetchingFields(false);
      }
    };

    fetchTableFields();
  }, [tableId]);

  const handleFileChange = async (event) => {
    const file = event.target.files[0];
    if (!file) return;

    resetState();
    setFileName(file.name);
    setIsLoading(true);

    try {
      const arrayBuffer = await file.arrayBuffer();
      const workbook = new ExcelJS.Workbook();
      await workbook.xlsx.load(arrayBuffer);

      const parsedSheets = workbook.worksheets.map(worksheet => {
        const rows = [];
        worksheet.eachRow({ includeEmpty: true }, (row) => {
          const rowValues = [...row.values];
          rowValues.shift(); // Remove the empty first element
          rows.push(rowValues);
        });

        worksheet.getImages().forEach(image => {
          const img = workbook.getImage(image.imageId);
          if (img) {
            const dataUrl = `data:image/${img.extension};base64,${bufferToBase64(img.buffer)}`;
            const rowIdx = image.range.tl.row;
            const colIdx = image.range.tl.col;
            if (rows[rowIdx]) {
              rows[rowIdx][colIdx] = { isImage: true, src: dataUrl };
            }
          }
        });

        const headerRow = rows[0] || [];
        const columns = headerRow.map((name, index) => ({
          name: name?.toString() || `列 ${index + 1}`,
          index: index
        }));

        return {
          name: worksheet.name,
          rows: rows,
          columns: columns,
          selectedColumns: new Set(columns.map(c => c.name))
        };
      });

      setSheets(parsedSheets);
      fetchAvailableTables(); // Fetch tables after successful parsing
    } catch (err) {
      console.error("Error parsing Excel file:", err);
      setError("解析失败。请确保这是一个有效的 .xlsx 文件。");
    } finally {
      setIsLoading(false);
    }
  };

  const handleColumnToggle = (sheetIndex, columnName) => {
    setSheets(prev => {
      const newSheets = [...prev];
      const sheet = newSheets[sheetIndex];
      const newSelectedColumns = new Set(sheet.selectedColumns);
      newSelectedColumns.has(columnName) ? newSelectedColumns.delete(columnName) : newSelectedColumns.add(columnName);
      sheet.selectedColumns = newSelectedColumns;
      return newSheets;
    });
  };

  const handleMappingChange = (sheetIndex, columnIndex, eidosFieldName) => {
    setColumnMappings(prev => ({
      ...prev,
      [sheetIndex]: { ...prev[sheetIndex], [columnIndex]: eidosFieldName }
    }));
  };

  const handleImport = async (sheetIndex) => {
    const sheet = sheets[sheetIndex];
    const mapping = columnMappings[sheetIndex] || {};
    const rowsToImport = sheet.rows.slice(1);
    const totalRows = rowsToImport.length;

    if (totalRows === 0 || Object.keys(mapping).length === 0) {
      setFieldError("没有数据或未设置字段映射。");
      return;
    }

    setImportingSheet(sheetIndex);
    setImportProgress(0);
    setImportResults(prev => ({ ...prev, [sheetIndex]: null }));
    let successCount = 0, failedCount = 0;

    for (let i = 0; i < totalRows; i++) {
      const row = rowsToImport[i];
      const newRecord = {};
      let hasData = false;

      for (const colIdxStr in mapping) {
        const colIdx = parseInt(colIdxStr, 10);
        const eidosFieldName = mapping[colIdx];
        if (eidosFieldName) {
          let cellData = row[colIdx];
          if (cellData !== undefined && cellData !== null && cellData !== '') {
            hasData = true;
            if (cellData.isImage) {
              newRecord[eidosFieldName] = cellData.src;
            } else {
              newRecord[eidosFieldName] = cellData.hyperlink ? cellData.hyperlink : (cellData.richText ? cellData.richText.map(rt => rt.text).join('') : cellData);
            }
          }
        }
      }

      if (hasData) {
        try {
          await eidos.currentSpace.table(tableId).rows.create(newRecord);
          successCount++;
        } catch (createError) {
          console.error("Error creating record:", createError, "Data:", newRecord);
          failedCount++;
        }
      }
      setImportProgress(((i + 1) / totalRows) * 100);
    }

    setImportResults(prev => ({ ...prev, [sheetIndex]: { success: successCount, failed: failedCount, total: totalRows } }));
    setImportingSheet(null);
  };

  const selectedTableName = availableTables.find(t => t.id === tableId)?.name || tableId;

  return (
    <div className="p-4 space-y-6 bg-slate-50 min-h-screen">
      <Card className="overflow-hidden">
        <CardHeader>
          <CardTitle className="flex items-center gap-2"><FileUp className="w-6 h-6" />Excel 数据导入工具</CardTitle>
          <CardDescription>上传 .xlsx 文件，将其数据导入到指定的 Eidos 表格中。</CardDescription>
        </CardHeader>
        <CardContent className="space-y-4">
          <div className="grid w-full max-w-lg items-center gap-2">
            <Label htmlFor="excel-file">1. 上传文件</Label>
            <Input id="excel-file" type="file" onChange={handleFileChange} accept=".xlsx" disabled={isLoading} className="file:mr-4 file:py-2 file:px-4 file:rounded-full file:border-0 file:text-sm file:font-semibold file:bg-primary/10 file:text-primary hover:file:bg-primary/20" />
          </div>
          {sheets.length > 0 && (
            <div className="grid w-full max-w-lg items-center gap-2">
              <Label htmlFor="table-select">2. 选择目标表格</Label>
              <Select
                id="table-select"
                value={tableId}
                onValueChange={(value) => setTableId(value)}
                disabled={isLoading || isFetchingTables}
              >
                <SelectTrigger className="w-full">
                  <SelectValue placeholder={isFetchingTables ? "正在加载表格..." : "选择一个表格..."} />
                </SelectTrigger>
                <SelectContent>
                  {availableTables.map(table => (
                    <SelectItem key={table.id} value={table.id}>
                      {table.name}
                    </SelectItem>
                  ))}
                </SelectContent>
              </Select>
              {isFetchingFields && <div className="flex items-center text-sm text-muted-foreground"><Loader2 className="mr-2 h-4 w-4 animate-spin" />正在加载字段...</div>}
              {fieldError && <p className="text-sm text-red-500 mt-1">{fieldError}</p>}
            </div>
          )}
        </CardContent>
      </Card>

      {isLoading && <div className="flex items-center justify-center p-10 rounded-lg bg-white border"><Loader2 className="h-8 w-8 animate-spin text-primary" /><span className="ml-4 text-lg font-medium text-gray-600">正在解析 "{fileName}"...</span></div>}
      {error && <Alert variant="destructive"><FileWarning className="h-4 w-4" /><AlertTitle>出错了</AlertTitle><AlertDescription>{error}</AlertDescription></Alert>}

      {!isLoading && !error && sheets.map((sheet, sheetIndex) => (
        <Card key={sheetIndex}>
          <CardHeader className="flex flex-row items-center justify-between">
            <CardTitle>表格预览: {sheet.name}</CardTitle>
            <DropdownMenu>
              <DropdownMenuTrigger asChild><Button variant="outline" size="sm"><Columns className="mr-2 h-4 w-4" />选择列</Button></DropdownMenuTrigger>
              <DropdownMenuContent align="end">
                <DropdownMenuLabel>显示的列</DropdownMenuLabel>
                <DropdownMenuSeparator />
                {sheet.columns.map(c => <DropdownMenuCheckboxItem key={c.name} checked={sheet.selectedColumns.has(c.name)} onCheckedChange={() => handleColumnToggle(sheetIndex, c.name)}>{c.name}</DropdownMenuCheckboxItem>)}
              </DropdownMenuContent>
            </DropdownMenu>
          </CardHeader>
          <CardContent>
            <div className="overflow-x-auto border rounded-lg">
              <Table>
                <TableHeader>{sheet.rows[0] && <TableRow>{sheet.columns.filter(c => sheet.selectedColumns.has(c.name)).map(c => <TableHead key={c.index}><CellContent data={sheet.rows[0][c.index]} /></TableHead>)}</TableRow>}</TableHeader>
                <TableBody>{sheet.rows.slice(1, 6).map((row, rowIndex) => <TableRow key={rowIndex}>{sheet.columns.filter(c => sheet.selectedColumns.has(c.name)).map(c => <TableCell key={c.index} className="align-top"><CellContent data={row[c.index]} /></TableCell>)}</TableRow>)}</TableBody>
              </Table>
            </div>
            {sheet.rows.length > 6 && <p className="text-sm text-center text-muted-foreground mt-2">仅显示前 5 行数据作为预览。</p>}

            {tableFields.length > 0 && (
              <div className="mt-6 pt-6 border-t">
                <h3 className="text-lg font-semibold mb-2">3. 字段映射与导入</h3>
                <p className="text-sm text-muted-foreground mb-4">将 Excel 列映射到表格字段，然后开始导入。</p>
                <div className="overflow-x-auto border rounded-lg mb-4">
                  <Table>
                    <TableHeader><TableRow><TableHead>Excel 列</TableHead><TableHead>表格字段</TableHead></TableRow></TableHeader>
                    <TableBody>
                      {sheet.columns.map(col => (
                        <TableRow key={col.index}>
                          <TableCell className="font-medium">{col.name}</TableCell>
                          <TableCell>
                            <Select onValueChange={(value) => handleMappingChange(sheetIndex, col.index, value === 'null' ? null : value)}>
                              <SelectTrigger><SelectValue placeholder="选择一个字段..." /></SelectTrigger>
                              <SelectContent>
                                <SelectItem value="null">-- 不导入 --</SelectItem>
                                {tableFields.map(field => <SelectItem key={field.name} value={field.name}>{field.name} ({field.type})</SelectItem>)}
                              </SelectContent>
                            </Select>
                          </TableCell>
                        </TableRow>
                      ))}
                    </TableBody>
                  </Table>
                </div>
                <div className="flex items-center gap-4">
                  <Button onClick={() => handleImport(sheetIndex)} disabled={importingSheet !== null}>
                    {importingSheet === sheetIndex ? <Loader2 className="mr-2 h-4 w-4 animate-spin" /> : <Upload className="mr-2 h-4 w-4" />}
                    导入到 {selectedTableName}
                  </Button>
                  {importingSheet === sheetIndex && <Progress value={importProgress} className="w-[200px]" />}
                </div>
                {importResults[sheetIndex] && (
                  <Alert className="mt-4">
                    <AlertTitle>导入完成</AlertTitle>
                    <AlertDescription>成功: {importResults[sheetIndex].success}, 失败: {importResults[sheetIndex].failed}, 总计: {importResults[sheetIndex].total}.</AlertDescription>
                  </Alert>
                )}
              </div>
            )}
          </CardContent>
        </Card>
      ))}
    </div>
  );
}