Moms Nature: Manager (PB Edition)

import React, { useState, useEffect, useCallback, useRef, useMemo } from 'react';
import PocketBase from 'pocketbase';
import { Upload, Download, FileText, CheckCircle, Clock, AlertCircle, Loader2, Database, FileUp, FileSpreadsheet, Camera, Copy, Info, History } from 'lucide-react';

// ==========================================
// [설정] 서버 경로 구성
const INTERNAL_URL = 'http://192.168.0.26:8090';
const EXTERNAL_URL = 'https://smartidea2023.synology.me:9090';
const COLLECTION_NAME = 'mapping_storage';

// 접속 환경에 따른 서버 주소 자동 결정 로직 개선
const getPBUrl = () => {
    const hostname = window.location.hostname;
    if (!hostname || hostname.includes('synology.me')) return EXTERNAL_URL;
    if (hostname === '192.168.0.26') return INTERNAL_URL;
    return EXTERNAL_URL; 
};

const PB_URL = getPBUrl();
// ==========================================

const pb = new PocketBase(PB_URL);
pb.autoCancellation(false);

// 접속 환경 판단 (UI 표시용)
  const isInternal = window.location.hostname === '192.168.0.26';

useEffect(() => {
    const loadScript = (url, key) => {
      const script = document.createElement('script');
      script.src = url;
      script.async = true;
      script.onload = () => setLibsLoaded(prev => ({ ...prev, [key]: true }));
      document.head.appendChild(script);
    };
    loadScript("https://cdn.jsdelivr.net/npm/xlsx-js-style@1.2.0/dist/xlsx.bundle.min.js", 'xlsx');
  }, []);

useEffect(() => {
    const initPB = async () => {
      try {
        const records = await pb.collection(COLLECTION_NAME).getFullList({
            sort: '-updated',
        });

records.forEach(record => {
            if (record.data_type === 'db') {
                setRecordIds(prev => ({ ...prev, db: record.id }));
                setDbData(record.json_content || []);
                setLastSaved(record.meta_info?.updatedAt ? new Date(record.meta_info.updatedAt) : new Date(record.updated));
            } else if (record.data_type === 'result') {
                setRecordIds(prev => ({ ...prev, result: record.id }));
                setProcessedResult(record.json_content || []);
                setProcessedAt(record.meta_info?.processedAt ? new Date(record.meta_info.processedAt) : new Date(record.updated));
                if (record.meta_info?.colHeaders) {
                    setColHeaders(record.meta_info.colHeaders);
                }
            }
        });

pb.collection(COLLECTION_NAME).subscribe('*', function (e) {
            if (e.action === 'create' || e.action === 'update') {
                const record = e.record;
                if (record.data_type === 'db') {
                    setRecordIds(prev => ({ ...prev, db: record.id }));
                    setDbData(record.json_content || []);
                    setLastSaved(record.meta_info?.updatedAt ? new Date(record.meta_info.updatedAt) : new Date(record.updated));
                } else if (record.data_type === 'result') {
                    setRecordIds(prev => ({ ...prev, result: record.id }));
                    setProcessedResult(record.json_content || []);
                    setProcessedAt(record.meta_info?.processedAt ? new Date(record.meta_info.processedAt) : new Date(record.updated));
                    if (record.meta_info?.colHeaders) {
                        setColHeaders(record.meta_info.colHeaders);
                    }
                }
            }
        });

} catch (err) {
        console.error("PB Load Error:", err);
        setError("서버 연결 실패. PocketBase가 실행 중인지 확인하세요. (" + PB_URL + ")");
      }
    };

initPB();
    return () => {
      pb.collection(COLLECTION_NAME).unsubscribe();
    };
  }, []);

const saveResultToPB = async (data, headers) => {
    try {
      const payload = {
          data_type: 'result',
          json_content: data,
          meta_info: {
              processedAt: new Date().toISOString(),
              colHeaders: headers
          }
      };

if (recordIds.result) {
          await pb.collection(COLLECTION_NAME).update(recordIds.result, payload);
      } else {
          const existing = await pb.collection(COLLECTION_NAME).getList(1, 1, { filter: 'data_type="result"' });
          if (existing.items.length > 0) {
              const id = existing.items[0].id;
              setRecordIds(prev => ({ ...prev, result: id }));
              await pb.collection(COLLECTION_NAME).update(id, payload);
          } else {
              const newRec = await pb.collection(COLLECTION_NAME).create(payload);
              setRecordIds(prev => ({ ...prev, result: newRec.id }));
          }
      }
    } catch (err) {
      console.error("Cloud Save Error:", err);
      setError("저장 실패: " + err.message);
    }
  };

if (recordIds.db) {
            await pb.collection(COLLECTION_NAME).update(recordIds.db, payload);
        } else {
             const existing = await pb.collection(COLLECTION_NAME).getList(1, 1, { filter: 'data_type="db"' });
             if (existing.items.length > 0) {
                 const id = existing.items[0].id;
                 setRecordIds(prev => ({ ...prev, db: id }));
                 await pb.collection(COLLECTION_NAME).update(id, payload);
             } else {
                 const newRec = await pb.collection(COLLECTION_NAME).create(payload);
                 setRecordIds(prev => ({ ...prev, db: newRec.id }));
             }
        }

setIsDbUploading(false);
      } catch (err) {
        setError("파일 처리/업로드 중 오류: " + err.message);
        setIsDbUploading(false);
      }
    };
    reader.readAsBinaryString(file);
  };

const processMapping = (sourceData, headerIndices) => {
    if (!dbData || dbData.length === 0) {
      setError("먼저 데이터베이스를 업로드해 주세요.");
      return null;
    }

const mappedRows = sourceData.map(row => {
      const key1 = String(row['쇼핑몰상품코드'] || '').trim();
      const key2 = String(row['옵션(수집)'] || '').trim();
      const matched = dbData.filter(dbRow => 
        String(dbRow['상품코드(쇼핑몰)'] || '').trim() === key1 && 
        String(dbRow['옵션(수집)'] || '').trim() === key2
      );
      
      const count = matched.length;
      const lines = matched.map(m => `${m['옵션(확정)'] || ''} (${m['EA(확정)'] || '0'})`).join('\n');
      const orderEA = Number(row['EA'] || row['수량'] || row['주문수량'] || 0);
      const optionAlias = row['옵션별칭'] || row['별칭'] || row['옵션코드'] || '';
      
      const colJVal = headerIndices.J_Key ? String(row[headerIndices.J_Key] || '').trim() : '';
      const colBVal = headerIndices.B_Key ? String(row[headerIndices.B_Key] || '').trim() : '';
      const orderNum = headerIndices.Order_Key ? String(row[headerIndices.Order_Key] || '').trim() : '';

return { 
          ...row,
          '_colJVal': colJVal,
          '쇼핑몰상품코드': key1,
          '_orderNum': orderNum,
          '_colBVal': colBVal,
          '옵션(수집)': String(row['옵션(수집)'] || '').trim(),
          '지정단품명': lines,
          '구성품수량': count,
          '주문수량_분석': orderEA,
          '옵션별칭_분석': optionAlias
      };
    });

const grouped = {};
    mappedRows.forEach(row => {
        const keyA = row['쇼핑몰상품코드'];
        const keyB = row['_colBVal'];
        const compositeKey = `${keyA}_||_${keyB}`;

if (!grouped[compositeKey]) {
            grouped[compositeKey] = {
                baseRow: row,
                orderEAs: [],
                options: new Set(),
                aliases: new Set(),
                mappings: new Set(),
                orderNums: new Set(),
                jVals: new Set()
            };
        }

const g = grouped[compositeKey];
        g.orderEAs.push(String(row['주문수량_분석']));
        
        if (row['옵션(수집)']) g.options.add(row['옵션(수집)']);
        if (row['옵션별칭_분석']) g.aliases.add(row['옵션별칭_분석']);
        if (row['지정단품명']) g.mappings.add(row['지정단품명']);
        if (row['_orderNum']) g.orderNums.add(row['_orderNum']);
        if (row['_colJVal']) g.jVals.add(row['_colJVal']);
    });

finalResult.sort((a, b) => {
        const mallA = String(a['_colJVal'] || '');
        const mallB = String(b['_colJVal'] || '');
        const isKakaoA = mallA.includes('카카오');
        const isKakaoB = mallB.includes('카카오');
        if (isKakaoA && !isKakaoB) return -1;
        if (!isKakaoA && isKakaoB) return 1;
        if (mallA !== mallB) return mallA.localeCompare(mallB, 'ko', { sensitivity: 'base' });
        const aliasA = String(a['옵션별칭_분석'] || '');
        const aliasB = String(b['옵션별칭_분석'] || '');
        return aliasA.localeCompare(aliasB, 'ko', { sensitivity: 'base' });
    });

return finalResult;
  };

const getByteLen = (str) => {
        let len = 0; str = String(str || "");
        for (let i = 0; i < str.length; i++) { len += (str.charCodeAt(i) > 128) ? 2 : 1; }
        return len;
    }

const missingKakaoCodes = useMemo(() => {
    if (!processedResult) return [];
    const targets = processedResult.filter(row => 
        String(row['_colJVal']).includes('카카오') && 
        (!row['지정단품명'] || String(row['지정단품명']).trim() === '')
    );
    return Array.from(new Set(targets.map(row => row['쇼핑몰상품코드'])));
  }, [processedResult]);

return (
    <div className="min-h-screen bg-[#f8f9fc] p-4 md:p-8 font-sans text-slate-800">
      {(!libsLoaded.xlsx) && !error && (
        <div className="fixed inset-0 bg-white/90 z-50 flex flex-col items-center justify-center gap-4">
          <Loader2 className="animate-spin text-orange-600" size={48} />
          <p className="font-bold text-slate-600">서버 연결 중...</p>
        </div>
      )}

<div className="max-w-7xl mx-auto space-y-6">
        <div className="flex items-center justify-between mb-2 px-2">
            <div className="flex items-center gap-4">
              <div className="w-12 h-12 bg-slate-900 rounded-2xl flex items-center justify-center text-white shadow-xl">
                <Database size={24} />
              </div>
              <div>
                <h1 className="text-2xl font-black text-slate-900 tracking-tight flex items-center gap-2 italic">
                  MAPPING MANAGER
                  <span className="text-[10px] bg-orange-50 text-orange-600 px-3 py-1 rounded-full font-bold uppercase tracking-widest not-italic">PocketBase</span>
                </h1>
                <p className="text-slate-400 text-xs font-semibold">동일 주문 건 병합 및 자동 상품 매핑 시스템 (Dual Path)</p>
              </div>
            </div>

{/* 요청에 따라 이동된 서버 상태 표시 정보 */}
            <div className="flex items-center gap-4 bg-white border border-slate-200 px-4 py-2 rounded-2xl shadow-sm">
                <span className="flex items-center gap-2 text-[11px] font-bold text-slate-600">
                    <span className={`w-2 h-2 rounded-full animate-pulse ${isInternal ? 'bg-emerald-500' : 'bg-blue-500'}`}></span>
                    Connected ({isInternal ? 'Internal: 192.168.0.26' : 'External: Synology'})
                </span>
                <div className="h-4 w-[1px] bg-slate-200"></div>
                <span className={`text-[10px] px-2 py-0.5 rounded-full font-bold border ${isInternal ? 'border-emerald-100 text-emerald-600 bg-emerald-50' : 'border-blue-100 text-blue-600 bg-blue-50'}`}>
                    {isInternal ? 'Local LAN' : 'Remote WAN'}
                </span>
            </div>
        </div>

<div className="grid grid-cols-1 md:grid-cols-2 gap-6">
          <div onDrop={(e) => { e.preventDefault(); processSourceFile(e.dataTransfer.files[0]); }} onDragOver={(e) => e.preventDefault()} className="bg-white p-6 rounded-[2rem] border border-slate-200 shadow-sm transition-all relative group">
            <div className="flex items-center justify-between mb-4">
              <div className="flex items-center gap-3">
                <div className="w-10 h-10 bg-emerald-100 text-emerald-600 rounded-xl flex items-center justify-center"><FileUp size={20} /></div>
                <div>
                  <h3 className="text-sm font-black text-slate-900 uppercase">Step 1. 주문서 분석</h3>
                  <p className="text-[11px] text-slate-400 font-medium italic">드래그 앤 드롭으로 업로드</p>
                </div>
              </div>
            </div>
            <label className="w-full cursor-pointer bg-emerald-600 hover:bg-emerald-700 text-white px-4 py-3.5 rounded-2xl text-xs font-bold transition-all flex items-center justify-center gap-2 shadow-lg shadow-emerald-100 active:scale-95">
              {isProcessing ? <Loader2 className="animate-spin" size={16} /> : <Upload size={16} />}
              엑셀 주문서 분석 시작
              <input type="file" className="hidden" accept=".xlsx,.xls" onChange={(e) => processSourceFile(e.target.files[0])} />
            </label>
          </div>

<div onDrop={(e) => { e.preventDefault(); processDbFile(e.dataTransfer.files[0]); }} onDragOver={(e) => e.preventDefault()} className="bg-white p-6 rounded-[2rem] border border-slate-200 shadow-sm transition-all relative">
            <div className="flex items-center justify-between mb-4">
              <div className="flex items-center gap-3">
                <div className="w-10 h-10 bg-slate-100 text-slate-600 rounded-xl flex items-center justify-center"><Database size={20} /></div>
                <div>
                  <h3 className="text-sm font-black text-slate-900 uppercase">Step 2. 매핑 DB 관리</h3>
                  {lastSaved ? (<p className="text-[10px] text-blue-600 font-bold flex items-center gap-1"><Clock size={10} /> {lastSaved.toLocaleString()} 최신화됨</p>) : <p className="text-[11px] text-slate-400 font-medium">기준 엑셀 파일 업로드</p>}
                </div>
              </div>
              {dbData.length > 0 && (<button onClick={() => downloadExcel(dbData, 'Mapping_Database_Backup.xlsx', true)} className="p-2 text-slate-400 hover:text-indigo-600 transition-colors" title="현재 DB 백업 다운로드"><Download size={18} /></button>)}
            </div>
            <label className="w-full cursor-pointer bg-slate-900 hover:bg-black text-white px-4 py-3.5 rounded-2xl text-xs font-bold transition-all flex items-center justify-center gap-2 shadow-lg shadow-slate-200 active:scale-95">
              {isDbUploading ? <Loader2 className="animate-spin" size={16} /> : <Upload size={16} />}
              매핑 기준 데이터 업데이트
              <input type="file" className="hidden" accept=".xlsx,.xls" onChange={(e) => processDbFile(e.target.files[0])} />
            </label>
          </div>
        </div>

{error && (<div className="bg-red-50 border border-red-200 text-red-700 p-4 rounded-2xl flex items-center gap-3 shadow-sm"><AlertCircle size={20} /><p className="text-xs font-bold">{error}</p></div>)}

<div className="bg-white rounded-[2.5rem] shadow-sm border border-slate-200 flex flex-col overflow-hidden min-h-[600px]">
          <div className="p-6 border-b border-slate-100 flex flex-wrap justify-between items-center bg-white gap-4">
            <div className="flex flex-col gap-1 flex-1">
                <div className="flex items-center gap-3">
                  <div className="w-2.5 h-2.5 bg-indigo-500 rounded-full animate-pulse shadow-sm shadow-indigo-100"></div>
                  <h3 className="text-lg font-black text-slate-800 italic uppercase">Dashboard</h3>
                  {processedResult && (
                    <div className="flex items-center gap-2">
                        <span className="bg-slate-100 text-slate-500 text-[10px] font-black px-3 py-1 rounded-full border border-slate-200 uppercase tracking-tight">
                            {processedResult.length} 건 분석 (병합 완료)
                        </span>
                        {processedAt && (
                            <span className="text-[10px] text-slate-400 font-bold flex items-center gap-1">
                                <History size={12} /> 최근 분석: {processedAt.toLocaleString()}
                            </span>
                        )}
                    </div>
                  )}
                </div>
                {missingKakaoCodes.length > 0 && (
                   <div className="mt-2 p-3 bg-red-50 rounded-xl border border-red-100 flex items-start justify-between gap-4">
                     <div className="flex-1">
                        <p className="text-[10px] text-red-600 font-black flex items-center gap-1 mb-1">
                            <AlertCircle size={12} /> [카카오 선물하기] 미입력 코드 {missingKakaoCodes.length}건 (DB 등록 필요)
                        </p>
                        <p className="text-[9px] text-red-400 font-mono break-all leading-tight italic">
                            대상: {missingKakaoCodes.join(', ')}
                        </p>
                     </div>
                     <button 
                        onClick={handleCopyCodes}
                        className={`shrink-0 flex items-center gap-1.5 px-3 py-1.5 rounded-lg text-[10px] font-bold transition-all shadow-sm ${copyFeedback ? 'bg-green-600 text-white' : 'bg-white text-slate-600 border border-slate-200 hover:bg-slate-50'}`}
                     >
                        {copyFeedback ? <CheckCircle size={12} /> : <Copy size={12} />}
                        {copyFeedback ? '복사됨' : '코드 복사'}
                     </button>
                   </div>
                )}
            </div>
            
            {processedResult && (
              <div className="flex items-center gap-2">
                <button onClick={() => downloadExcel(processedResult, '매핑분석_최종결과.xlsx')} className="text-xs bg-indigo-600 hover:bg-indigo-700 text-white px-6 py-3 rounded-xl flex items-center gap-2 font-bold transition-all shadow-lg shadow-indigo-100">
                  <Download size={16} /> 분석 결과 다운로드
                </button>
              </div>
            )}
          </div>
          
          <div className="flex-1 overflow-auto bg-white custom-scrollbar" ref={resultsRef}>
            {!processedResult ? (
              <div className="h-full flex flex-col items-center justify-center text-slate-300 p-20">
                <div className="p-8 bg-slate-50 rounded-full mb-6"><FileSpreadsheet size={64} strokeWidth={1} className="opacity-20" /></div>
                <p className="text-xl font-black opacity-30 tracking-tight italic uppercase">Wait for orders...</p>
                <p className="text-[10px] font-bold text-slate-400 mt-2 uppercase tracking-widest">파일을 업로드하여 분석을 시작하세요</p>
              </div>
            ) : (
              <div className="p-6 md:p-10">
                <div className="overflow-hidden rounded-[2rem] border border-slate-200 shadow-md bg-white">
                  <table className="w-full text-[11px] text-left border-collapse">
                    <thead className="bg-slate-50">
                      <tr className="border-b-2 border-slate-900 bg-slate-900 text-white">
                        <th className="p-4 font-black uppercase tracking-widest text-[9px] w-[5%]">{colHeaders.J}</th>
                        <th className="p-4 font-black uppercase tracking-widest text-[9px] w-[6%]">쇼핑몰코드</th>
                        <th className="p-4 font-black uppercase tracking-widest text-[9px] w-[8%]">주문번호</th>
                        <th className="p-4 font-black uppercase tracking-widest text-[9px] w-[8%]">{colHeaders.B}</th>
                        <th className="p-4 font-black uppercase tracking-widest text-[9px] w-[18%]">수집 옵션명</th>
                        <th className="p-4 font-black uppercase tracking-widest text-[9px] w-[20%] text-indigo-400">지정 단품 매핑 상세</th>
                        <th className="p-4 font-black uppercase tracking-widest text-[9px] w-[6%] text-center text-indigo-400">매핑수량</th>
                        <th className="p-4 font-black uppercase tracking-widest text-[9px] w-[19%]">옵션별칭</th>
                        <th className="p-4 font-black uppercase tracking-widest text-[9px] w-[5%] text-center">주문EA</th>
                      </tr>
                    </thead>
                    <tbody className="divide-y divide-slate-200">
                      {processedResult.map((row, idx) => {
                         const isKakao = String(row['_colJVal']).includes('카카오');
                         return (
                        <tr key={idx} className={`transition-all duration-150 group ${isKakao ? 'bg-yellow-50/70 hover:bg-yellow-100' : 'hover:bg-slate-50'}`}>
                          <td className="p-0 text-slate-600 font-mono text-[9px] border-r border-slate-200">{renderInnerCells(row['_colJVal'])}</td>
                          <td className="p-0 text-slate-600 font-mono text-[10px] border-r border-slate-200">{renderInnerCells(row['쇼핑몰상품코드'])}</td>
                          <td className="p-0 text-slate-600 font-mono text-[9px] border-r border-slate-200">{renderInnerCells(row['_orderNum'])}</td>
                          <td className="p-0 text-slate-700 font-bold border-r border-slate-200">{renderInnerCells(row['_colBVal'])}</td>
                          <td className="p-0 text-slate-700 font-bold border-r border-slate-200">{renderInnerCells(row['옵션(수집)'])}</td>
                          <td className="p-5 whitespace-pre-wrap text-[10px] text-slate-600 font-bold italic group-hover:text-slate-900 transition-colors align-middle border-r border-slate-200">{row['지정단품명']}</td>
                          <td className="p-5 text-center align-middle border-r border-slate-200 bg-indigo-50/5">
                            <span className={`inline-flex items-center justify-center w-6 h-6 rounded-lg font-black text-[10px] shadow-sm transition-all ${row['구성품수량'] > 0 ? 'bg-indigo-600 text-white' : 'bg-slate-100 text-slate-300'}`}>{row['구성품수량']}</span>
                          </td>
                          <td className="p-0 text-slate-600 font-bold border-r border-slate-200">{renderInnerCells(row['옵션별칭_분석'])}</td>
                          <td className="p-0 text-center font-mono font-bold text-slate-600">{renderInnerCells(row['주문수량_분석'])}</td>
                        </tr>
                      )})}
                    </tbody>
                  </table>
                </div>
              </div>
            )}
          </div>
        </div>
      </div>
      
      <style>{`
        .custom-scrollbar::-webkit-scrollbar { width: 6px; height: 6px; } 
        .custom-scrollbar::-webkit-scrollbar-track { background: transparent; } 
        .custom-scrollbar::-webkit-scrollbar-thumb { background: #e2e8f0; border-radius: 10px; }
      `}</style>
    </div>
  );
};

export default App;