<?php
declare(strict_types=1);
require __DIR__ . '/../partials/auth.php';
require_once __DIR__ . '/../../config/database.php';
$pdo = getDbConnection();
$currentUserId = (int)($_SESSION['user_id'] ?? 0);
$currentUserRole = strtolower(trim((string)($_SESSION['user_role'] ?? $_SESSION['role'] ?? 'teacher')));
$isAdmin = ($currentUserRole === 'admin');

$pageTitle = 'Import Excel';
$pageSubtitle = 'Bulk import vocabulary into Word Library';
$activeTeacherNav = 'word-library';

function xlsxRows(string $file): array {
    if (!class_exists('ZipArchive')) throw new RuntimeException('PHP ZipArchive is required to read .xlsx files.');
    $zip = new ZipArchive();
    if ($zip->open($file) !== true) throw new RuntimeException('Could not open the Excel file.');
    $shared = [];
    $ss = $zip->getFromName('xl/sharedStrings.xml');
    if ($ss !== false) {
        $xml = simplexml_load_string($ss);
        if ($xml) foreach ($xml->si as $si) {
            $parts=[]; foreach ($si->xpath('.//t') ?: [] as $t) $parts[]=(string)$t;
            $shared[] = implode('', $parts);
        }
    }
    $sheet = $zip->getFromName('xl/worksheets/sheet1.xml');
    $zip->close();
    if ($sheet === false) throw new RuntimeException('The first worksheet could not be read.');
    $xml = simplexml_load_string($sheet);
    if (!$xml) throw new RuntimeException('Invalid worksheet data.');
    $rows=[];
    foreach ($xml->sheetData->row as $row) {
        $cells=[];
        foreach ($row->c as $c) {
            $ref=(string)$c['r']; preg_match('/^[A-Z]+/', $ref, $m); $letters=$m[0]??'A';
            $idx=0; for($i=0;$i<strlen($letters);$i++) $idx=$idx*26+(ord($letters[$i])-64); $idx--;
            $type=(string)$c['t'];
            if ($type==='inlineStr') { $v=(string)($c->is->t ?? ''); }
            else { $raw=(string)($c->v ?? ''); $v=($type==='s') ? ($shared[(int)$raw]??'') : $raw; }
            $cells[$idx]=trim($v);
        }
        if ($cells) { $max=max(array_keys($cells)); $out=[]; for($i=0;$i<=$max;$i++) $out[]=$cells[$i]??''; $rows[]=$out; }
    }
    return $rows;
}
function csvRows(string $file): array {
    $rows=[]; $h=fopen($file,'rb'); if(!$h) throw new RuntimeException('Could not open the CSV file.');
    while(($r=fgetcsv($h,0,','))!==false) { if(isset($r[0])) $r[0]=preg_replace('/^\xEF\xBB\xBF/','',$r[0]); $rows[]=$r; }
    fclose($h); return $rows;
}
function normHeader(string $s): string { $s=strtolower(trim($s)); $s=str_replace([' ','-'], '_', $s); return $s; }
function slugWord(string $s): string { $x=strtolower(trim((string)preg_replace('/[^a-zA-Z0-9]+/','-',$s),'-')); return $x!==''?$x:'word-'.time(); }
function difficulty(string $s): int { $n=mb_strlen(str_replace(' ','',$s)); return $n<=4?1:($n<=6?2:($n<=8?3:4)); }

$preview=[]; $error=''; $summary=['valid'=>0,'duplicate'=>0,'invalid'=>0];
if ($_SERVER['REQUEST_METHOD']==='POST') {
    try {
        if (($_POST['action']??'')==='preview') {
            if (empty($_FILES['excel_file']['tmp_name']) || $_FILES['excel_file']['error']!==UPLOAD_ERR_OK) throw new RuntimeException('Please choose an Excel or CSV file.');
            $name=$_FILES['excel_file']['name']??''; $ext=strtolower(pathinfo($name,PATHINFO_EXTENSION));
            if (!in_array($ext,['xlsx','csv'],true)) throw new RuntimeException('Please use .xlsx or .csv format.');
            $rows=$ext==='xlsx'?xlsxRows($_FILES['excel_file']['tmp_name']):csvRows($_FILES['excel_file']['tmp_name']);
            if (count($rows)<2) throw new RuntimeException('The file has no vocabulary rows.');
            $headers=array_map('normHeader',$rows[0]);
            $aliases=['word'=>['word','english','vocabulary'],'meaning_vi'=>['meaning_vi','meaning','vietnamese','nghia_vi'],'phonetic'=>['phonetic','ipa'],'part_of_speech'=>['part_of_speech','pos'],'cefr_level'=>['cefr_level','cefr','level'],'example_en'=>['example_en','example','example_english'],'example_vi'=>['example_vi','example_vietnamese'],'topic'=>['topic','topics'],'notes'=>['notes','note']];
            $map=[]; foreach($aliases as $key=>$opts){ foreach($opts as $o){$p=array_search($o,$headers,true); if($p!==false){$map[$key]=$p;break;}} }
            if (!isset($map['word'])) throw new RuntimeException('A "word" column is required.');
            $dup=$pdo->prepare('SELECT id FROM words WHERE LOWER(TRIM(word))=LOWER(TRIM(?)) LIMIT 1');
            foreach(array_slice($rows,1) as $i=>$r){
                $get=fn($k)=>isset($map[$k])?trim((string)($r[$map[$k]]??'')):'';
                $item=['row'=>$i+2,'word'=>$get('word'),'meaning_vi'=>$get('meaning_vi'),'phonetic'=>$get('phonetic'),'part_of_speech'=>$get('part_of_speech'),'cefr_level'=>$get('cefr_level'),'example_en'=>$get('example_en'),'example_vi'=>$get('example_vi'),'topic'=>$get('topic'),'notes'=>$get('notes')];
                if($item['word']===''){ $item['state']='invalid'; $item['reason']='Missing word'; $summary['invalid']++; }
                else { $dup->execute([$item['word']]); if($dup->fetchColumn()){ $item['state']='duplicate'; $item['reason']='Already exists'; $summary['duplicate']++; } else { $item['state']='valid'; $item['reason']='Ready'; $summary['valid']++; } }
                $preview[]=$item;
            }
            $_SESSION['wl_excel_preview']=$preview;
        } elseif (($_POST['action']??'')==='import') {
            $preview=$_SESSION['wl_excel_preview']??[]; if(!$preview) throw new RuntimeException('Preview data expired. Please upload the file again.');
            $allowedCefr=['Pre-A1','A1','A2','B1','B2','C1','C2']; $allowedPos=['noun','verb','adjective','adverb','pronoun','preposition','conjunction','interjection','determiner','article'];
            $ins=$pdo->prepare('INSERT INTO words (word,slug,phonetic,part_of_speech,meaning_vi,image_path,audio_us_path,difficulty_level,cefr_level,status,notes,created_by_teacher_id,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,\'active\',?,?,NOW(),NOW())');
            $ex=$pdo->prepare('INSERT INTO word_examples (word_id,example_en,example_vi,sort_order,status) VALUES (?,?,?,1,\'active\')');
            $findTopic=$pdo->prepare("SELECT id FROM topics WHERE LOWER(TRIM(name))=LOWER(TRIM(?)) AND status='active' LIMIT 1");
            $link=$pdo->prepare('INSERT INTO topic_words (topic_id,word_id,sort_order) VALUES (?,?,1)');
            $count=0; $pdo->beginTransaction();
            foreach($preview as $it){ if(($it['state']??'')!=='valid')continue; $cefr=in_array($it['cefr_level'],$allowedCefr,true)?$it['cefr_level']:''; $pos=strtolower($it['part_of_speech']); if(!in_array($pos,$allowedPos,true))$pos='';
                $ins->execute([$it['word'],slugWord($it['word']),$it['phonetic'],$pos,$it['meaning_vi'],'','',difficulty($it['word']),$cefr,$it['notes'],($isAdmin ? null : $currentUserId)]); $wid=(int)$pdo->lastInsertId();
                if($it['example_en']!=='')$ex->execute([$wid,$it['example_en'],$it['example_vi']]);
                foreach(preg_split('/[,;|]+/',$it['topic'])?:[] as $tn){$tn=trim($tn);if($tn==='')continue;$findTopic->execute([$tn]);$tid=$findTopic->fetchColumn();if($tid)$link->execute([(int)$tid,$wid]);}
                $count++;
            }
            $pdo->commit(); unset($_SESSION['wl_excel_preview']); $_SESSION['wl_message']=$count.' words imported successfully.'; $_SESSION['wl_message_type']='success'; header('Location: index.php'); exit;
        }
    } catch(Throwable $e){ if($pdo->inTransaction())$pdo->rollBack(); $error=$e->getMessage(); }
}

ob_start();
?>
<style>
.wlx-head{display:flex;justify-content:space-between;align-items:center;gap:16px;margin-bottom:18px}.wlx-kicker{color:#2563eb;font-size:11px;font-weight:850;letter-spacing:.12em;text-transform:uppercase}.wlx-head h2{margin:5px 0 0;font-size:20px}.wlx-card{background:#fff;border:1px solid #e5e7eb;border-radius:16px;padding:20px;margin-bottom:16px}.wlx-upload{display:flex;gap:12px;align-items:center;flex-wrap:wrap}.wlx-file{flex:1;min-width:260px;padding:11px;border:1px solid #dbe2ea;border-radius:10px}.wlx-help{margin-top:12px;color:#64748b;font-size:12px;line-height:1.6}.wlx-alert{padding:12px 14px;border-radius:10px;margin-bottom:14px;background:#fef2f2;color:#b91c1c;border:1px solid #fecaca}.wlx-stats{display:grid;grid-template-columns:repeat(3,1fr);gap:10px;margin-bottom:14px}.wlx-stat{padding:13px;border-radius:12px;text-align:center;font-size:12px;font-weight:750}.wlx-stat strong{display:block;font-size:22px;margin-bottom:3px}.ok{background:#ecfdf5;color:#047857}.dup{background:#fff7ed;color:#c2410c}.bad{background:#fef2f2;color:#b91c1c}.wlx-table-wrap{overflow:auto;border:1px solid #e5e7eb;border-radius:12px}.wlx-table{width:100%;border-collapse:collapse;font-size:12px}.wlx-table th{background:#f8fafc;color:#64748b;text-align:left;padding:10px;white-space:nowrap}.wlx-table td{padding:10px;border-top:1px solid #eef2f7;vertical-align:top}.wlx-badge{display:inline-flex;padding:4px 8px;border-radius:999px;font-weight:800;font-size:10px}.wlx-actions{display:flex;justify-content:flex-end;gap:10px;margin-top:14px}@media(max-width:700px){.wlx-stats{grid-template-columns:1fr}.wlx-head{align-items:flex-start;flex-direction:column}}
</style>
<div class="wlx-head"><div><span class="wlx-kicker">Word Library</span><h2>Import Excel</h2></div><div style="display:flex;gap:10px;flex-wrap:wrap"><a href="word-library-import-template.xlsx" class="button button-secondary" download>⬇ Download Excel Template</a><a href="index.php" class="button button-secondary">← Back to Word Library</a></div></div>
<?php if($error):?><div class="wlx-alert"><?=htmlspecialchars($error)?></div><?php endif;?>
<div class="wlx-card">
<form method="post" enctype="multipart/form-data"><input type="hidden" name="action" value="preview"><div class="wlx-upload"><input class="wlx-file" type="file" name="excel_file" accept=".xlsx,.csv" required><button class="button button-primary" type="submit">Preview Import</button></div></form>
<div class="wlx-help"><strong>Tip:</strong> Download the Excel template above, replace the sample rows with your vocabulary, then upload it here.<br><strong>Supported columns:</strong> word, meaning_vi, ipa/phonetic, part_of_speech/POS, cefr_level, example_en, example_vi, topic, notes.<br><strong>Required:</strong> word. Existing words are skipped. Topic names must already exist in Topic Library.</div>
</div>
<?php if($preview):?><div class="wlx-card"><div class="wlx-stats"><div class="wlx-stat ok"><strong><?=$summary['valid']?></strong>Ready</div><div class="wlx-stat dup"><strong><?=$summary['duplicate']?></strong>Duplicate</div><div class="wlx-stat bad"><strong><?=$summary['invalid']?></strong>Invalid</div></div><div class="wlx-table-wrap"><table class="wlx-table"><thead><tr><th>Row</th><th>Word</th><th>IPA</th><th>Meaning</th><th>POS</th><th>CEFR</th><th>Topic</th><th>Status</th></tr></thead><tbody><?php foreach($preview as $r):?><tr><td><?=$r['row']?></td><td><strong><?=htmlspecialchars($r['word'])?></strong></td><td><?=htmlspecialchars($r['phonetic'])?></td><td><?=htmlspecialchars($r['meaning_vi'])?></td><td><?=htmlspecialchars($r['part_of_speech'])?></td><td><?=htmlspecialchars($r['cefr_level'])?></td><td><?=htmlspecialchars($r['topic'])?></td><td><span class="wlx-badge <?=$r['state']==='valid'?'ok':($r['state']==='duplicate'?'dup':'bad')?>"><?=htmlspecialchars($r['reason'])?></span></td></tr><?php endforeach;?></tbody></table></div><?php if($summary['valid']>0):?><form method="post" class="wlx-actions"><input type="hidden" name="action" value="import"><a class="button button-secondary" href="import-excel.php">Cancel</a><button class="button button-primary" type="submit">Import <?=$summary['valid']?> Valid Words</button></form><?php endif;?></div><?php endif;?>
<?php $content=ob_get_clean(); require __DIR__ . '/../layouts/app.php';
