<?php
if (session_status() === PHP_SESSION_NONE) { session_start(); }

if (!function_exists('mb_strtolower')) { function mb_strtolower($s, $e = null) { return strtolower((string)$s); } }
if (!function_exists('mb_strlen')) { function mb_strlen($s, $e = null) { return strlen((string)$s); } }
if (!function_exists('mb_strpos')) { function mb_strpos($h, $n, $o = 0, $e = null) { $r = strpos((string)$h, (string)$n, (int)$o); return $r === false ? false : $r; } }
if (!function_exists('mb_substr')) { function mb_substr($s, $start, $len = null, $e = null) { return $len === null ? substr((string)$s, (int)$start) : substr((string)$s, (int)$start, (int)$len); } }

function pti3_e($v): string { return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }
function pti3_now(): string { return date('Y-m-d H:i:s'); }
function pti3_admin_id(): ?int { if (function_exists('current_admin_id')) { $id=(int)current_admin_id(); return $id>0?$id:null; } return isset($_SESSION['admin_id'])?(int)$_SESSION['admin_id']:null; }
function pti3_redirect(string $url): void { header('Location: '.$url); exit; }
function pti3_flash(string $type, string $msg): void { $_SESSION['pti3_'.$type] = $msg; }
function pti3_take_flash(string $type): string { $k='pti3_'.$type; $m=(string)($_SESSION[$k]??''); unset($_SESSION[$k]); return $m; }

function pti3_table_exists(mysqli $conn, string $table): bool { $t=$conn->real_escape_string($table); $r=$conn->query("SHOW TABLES LIKE '{$t}'"); return $r && $r->num_rows>0; }
function pti3_col_exists(mysqli $conn, string $table, string $col): bool { $t=$conn->real_escape_string($table); $c=$conn->real_escape_string($col); $r=$conn->query("SHOW COLUMNS FROM `{$t}` LIKE '{$c}'"); return $r && $r->num_rows>0; }

function pti3_ensure_schema(mysqli $conn): void {
    $conn->query("CREATE TABLE IF NOT EXISTS temp_pi3_import_batches (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        batch_type VARCHAR(40) NOT NULL,
        original_file VARCHAR(255) DEFAULT NULL,
        source_count INT NOT NULL DEFAULT 0,
        imported_count INT NOT NULL DEFAULT 0,
        updated_count INT NOT NULL DEFAULT 0,
        skipped_count INT NOT NULL DEFAULT 0,
        notes TEXT DEFAULT NULL,
        created_by INT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id), KEY idx_type(batch_type), KEY idx_created(created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $conn->query("CREATE TABLE IF NOT EXISTS temp_pi3_brand_aliases (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        brand_name VARCHAR(255) NOT NULL,
        alias_text VARCHAR(255) NOT NULL,
        alias_normalized VARCHAR(255) NOT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id), UNIQUE KEY uq_alias(alias_normalized), KEY idx_brand(brand_name), KEY idx_active(is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $conn->query("CREATE TABLE IF NOT EXISTS temp_pi3_offline_items (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        batch_id INT UNSIGNED DEFAULT NULL,
        source_file VARCHAR(255) DEFAULT NULL,
        offline_name VARCHAR(255) NOT NULL,
        search_text TEXT DEFAULT NULL,
        barcode VARCHAR(160) NOT NULL,
        price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        suggested_brand VARCHAR(255) DEFAULT NULL,
        duplicate_product_id INT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id), UNIQUE KEY uq_barcode(barcode), KEY idx_price(price), KEY idx_brand(suggested_brand), KEY idx_dup(duplicate_product_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $conn->query("CREATE TABLE IF NOT EXISTS temp_pi3_legacy_items (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        batch_id INT UNSIGNED DEFAULT NULL,
        legacy_ref VARCHAR(120) DEFAULT NULL,
        legacy_hash CHAR(40) NOT NULL,
        legacy_name VARCHAR(255) NOT NULL,
        search_text TEXT DEFAULT NULL,
        legacy_description TEXT DEFAULT NULL,
        legacy_category VARCHAR(255) DEFAULT NULL,
        legacy_sale_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        legacy_qty INT NOT NULL DEFAULT 0,
        suggested_brand VARCHAR(255) DEFAULT NULL,
        status VARCHAR(30) NOT NULL DEFAULT 'pending_match',
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id), UNIQUE KEY uq_hash(legacy_hash), KEY idx_status(status), KEY idx_brand(suggested_brand), KEY idx_price(legacy_sale_price)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    if(!pti3_col_exists($conn,'temp_pi3_legacy_items','decision_notes')) { @$conn->query("ALTER TABLE temp_pi3_legacy_items ADD COLUMN decision_notes TEXT NULL AFTER status"); }
    if(!pti3_col_exists($conn,'temp_pi3_legacy_items','decided_at')) { @$conn->query("ALTER TABLE temp_pi3_legacy_items ADD COLUMN decided_at DATETIME NULL AFTER decision_notes"); }

    $conn->query("CREATE TABLE IF NOT EXISTS temp_pi3_employees (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        name VARCHAR(190) NOT NULL,
        pin_hash VARCHAR(255) NOT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_by INT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY(id), KEY idx_active(is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $conn->query("CREATE TABLE IF NOT EXISTS temp_pi3_work_batches (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        employee_id INT UNSIGNED NOT NULL,
        title VARCHAR(190) NOT NULL,
        status VARCHAR(40) NOT NULL DEFAULT 'assigned',
        supervisor_notes TEXT DEFAULT NULL,
        returned_notes TEXT DEFAULT NULL,
        total_items INT NOT NULL DEFAULT 0,
        completed_items INT NOT NULL DEFAULT 0,
        imported_items INT NOT NULL DEFAULT 0,
        created_by INT UNSIGNED DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY(id), KEY idx_emp(employee_id), KEY idx_status(status), KEY idx_created(created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $conn->query("CREATE TABLE IF NOT EXISTS temp_pi3_product_tasks (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        work_batch_id INT UNSIGNED NOT NULL,
        legacy_id INT UNSIGNED NOT NULL,
        offline_id INT UNSIGNED NOT NULL,
        employee_id INT UNSIGNED NOT NULL,
        brand_name VARCHAR(255) NOT NULL,
        final_name VARCHAR(255) NOT NULL,
        final_description TEXT DEFAULT NULL,
        final_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        final_barcode VARCHAR(160) NOT NULL,
        final_stock_qty INT NOT NULL DEFAULT 0,
        final_category_id INT UNSIGNED DEFAULT NULL,
        final_product_type_id INT UNSIGNED DEFAULT NULL,
        main_image VARCHAR(255) DEFAULT NULL,
        extra_image VARCHAR(255) DEFAULT NULL,
        status VARCHAR(40) NOT NULL DEFAULT 'assigned',
        employee_notes TEXT DEFAULT NULL,
        supervisor_notes TEXT DEFAULT NULL,
        imported_product_id INT UNSIGNED DEFAULT NULL,
        imported_at DATETIME DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY(id), UNIQUE KEY uq_legacy(legacy_id), KEY idx_batch(work_batch_id), KEY idx_emp(employee_id), KEY idx_status(status), KEY idx_barcode(final_barcode), KEY idx_imported(imported_product_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $conn->query("CREATE TABLE IF NOT EXISTS temp_pi3_activity_logs (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        actor_type VARCHAR(30) NOT NULL DEFAULT 'admin',
        actor_id INT UNSIGNED DEFAULT NULL,
        action_key VARCHAR(100) NOT NULL,
        description TEXT DEFAULT NULL,
        payload_json LONGTEXT DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY(id), KEY idx_actor(actor_type,actor_id), KEY idx_action(action_key), KEY idx_created(created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}

function pti3_log(mysqli $conn, string $action, string $desc='', array $payload=[], string $actorType='admin', ?int $actorId=null): void {
    pti3_ensure_schema($conn); if ($actorId===null) $actorId = $actorType==='admin' ? pti3_admin_id() : (int)($_SESSION['pti3_employee_id'] ?? 0);
    $json = $payload ? json_encode($payload, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES) : null;
    $stmt=$conn->prepare("INSERT INTO temp_pi3_activity_logs (actor_type, actor_id, action_key, description, payload_json) VALUES (?, ?, ?, ?, ?)");
    $stmt->bind_param('sisss',$actorType,$actorId,$action,$desc,$json); $stmt->execute(); $stmt->close();
}

function pti3_normalize($text): string {
    $text = mb_strtolower(trim((string)$text), 'UTF-8');
    $text = str_replace(["\xEF\xBB\xBF", 'ـ', 'أ', 'إ', 'آ', 'ة', 'ى'], ['', '', 'ا', 'ا', 'ا', 'ه', 'ي'], $text);
    $text = preg_replace('/[^\p{Arabic}\p{L}\p{N}]+/u', ' ', $text);
    $text = preg_replace('/\s+/u', ' ', $text);
    return trim($text);
}
function pti3_slugify($text): string {
    $text=trim((string)$text); $text=preg_replace('/\s+/u','-',$text); $text=preg_replace('/[^\p{Arabic}\p{L}\p{N}\-_]+/u','-',$text); $text=preg_replace('/-+/u','-',$text); $text=trim($text,'-'); return mb_strtolower($text,'UTF-8');
}
function pti3_to_float($v): float { $v=str_replace([',','د.ع','IQD','iqd',' '],['','','','',''],(string)$v); return $v===''?0.0:(float)$v; }
function pti3_to_int($v): int { return max(0, (int)round(pti3_to_float($v))); }

function pti3_xlsx_col_index(string $ref): int { preg_match('/([A-Z]+)/i',$ref,$m); $letters=strtoupper($m[1]??'A'); $n=0; for($i=0;$i<strlen($letters);$i++) $n=$n*26+(ord($letters[$i])-64); return max(0,$n-1); }
function pti3_parse_xlsx(string $path): array {
    if (!class_exists('ZipArchive')) throw new Exception('السيرفر لا يدعم ZipArchive المطلوب لقراءة Excel.');
    $zip=new ZipArchive(); if($zip->open($path)!==true) throw new Exception('تعذر فتح ملف Excel.');
    $shared=[]; $sharedXml=$zip->getFromName('xl/sharedStrings.xml');
    if($sharedXml!==false){ $sx=@simplexml_load_string($sharedXml); if($sx){ foreach($sx->si as $si){ $txt=''; if(isset($si->t)) $txt=(string)$si->t; elseif(isset($si->r)) foreach($si->r as $r) $txt.=(string)$r->t; $shared[]=$txt; } } }
    $sheetXml=$zip->getFromName('xl/worksheets/sheet1.xml'); $zip->close(); if($sheetXml===false) throw new Exception('لم أجد sheet1 داخل ملف Excel.');
    $sx=@simplexml_load_string($sheetXml); if(!$sx) throw new Exception('تعذر قراءة بيانات Excel.');
    $rows=[]; foreach($sx->sheetData->row as $rowNode){ $row=[]; foreach($rowNode->c as $c){ $idx=pti3_xlsx_col_index((string)$c['r']); $type=(string)$c['t']; $val=''; if($type==='s'){ $si=(int)($c->v??0); $val=$shared[$si]??''; } elseif($type==='inlineStr'){ $val=(string)($c->is->t??''); } else { $val=(string)($c->v??''); } $row[$idx]=$val; } if($row){ ksort($row); $max=max(array_keys($row)); $norm=[]; for($i=0;$i<=$max;$i++) $norm[]=$row[$i]??''; if(trim(implode('', $norm))!=='') $rows[]=$norm; } }
    return $rows;
}
function pti3_parse_csv(string $path): array { $fh=fopen($path,'r'); if(!$fh) throw new Exception('تعذر قراءة CSV.'); $rows=[]; while(($row=fgetcsv($fh,0,','))!==false){ if(count($row)===1 && strpos((string)$row[0],';')!==false) $row=str_getcsv((string)$row[0],';'); $rows[]=$row; } fclose($fh); return $rows; }
function pti3_parse_file_rows(string $tmp, string $name): array { $lower=mb_strtolower($name,'UTF-8'); if(str_ends_with($lower,'.xlsx')) return pti3_parse_xlsx($tmp); if(str_ends_with($lower,'.csv')||str_ends_with($lower,'.txt')) return pti3_parse_csv($tmp); throw new Exception('صيغة غير مدعومة: '.$name); }

function pti3_header_key($h, string $mode): ?string {
    $h=pti3_normalize($h); $h=str_replace(' ','_',$h);
    $maps=[
        'offline'=>['price'=>'price','سعر'=>'price','السعر'=>'price','sale_price'=>'price','unit_price'=>'price','سعر_البيع'=>'price','name'=>'name','اسم'=>'name','الاسم'=>'name','اسم_المادة'=>'name','الماده'=>'name','المادة'=>'name','product_name'=>'name','barcode'=>'barcode','bar_code'=>'barcode','باركود'=>'barcode','الباركود'=>'barcode','code'=>'barcode','sku'=>'barcode','كود'=>'barcode'],
        'legacy'=>['م'=>'legacy_ref','id'=>'legacy_ref','#'=>'legacy_ref','اسم_المنتج'=>'name','المنتج'=>'name','product_name'=>'name','name'=>'name','وصف_المنتج'=>'description','الوصف'=>'description','description'=>'description','desc'=>'description','القسم_الرئيسي'=>'category','القسم'=>'category','category'=>'category','main_category'=>'category','سعر_البيع'=>'sale_price','السعر'=>'sale_price','price'=>'sale_price','sale_price'=>'sale_price','الكمية'=>'qty','كميه'=>'qty','quantity'=>'qty','qty'=>'qty','stock'=>'qty'],
        'brands'=>['الاسم'=>'brand','اسم'=>'brand','اسم_الشركة'=>'brand','الشركة'=>'brand','brand'=>'brand','brand_name'=>'brand','name'=>'brand']
    ]; return $maps[$mode][$h] ?? null;
}
function pti3_find_header_map(array $rows, string $mode, array $required): array { foreach($rows as $ri=>$row){ $map=[]; foreach($row as $i=>$h){ $key=pti3_header_key($h,$mode); if($key && !isset($map[$key])) $map[$key]=$i; } $ok=true; foreach($required as $r) if(!isset($map[$r])) $ok=false; if($ok) return [$ri,$map]; } throw new Exception('لم أتعرف على أعمدة الملف.'); }
function pti3_create_import_batch(mysqli $conn, string $type, string $file, int $source=0, string $notes=''): int { pti3_ensure_schema($conn); $admin=pti3_admin_id(); $stmt=$conn->prepare("INSERT INTO temp_pi3_import_batches (batch_type, original_file, source_count, notes, created_by) VALUES (?, ?, ?, ?, ?)"); $stmt->bind_param('ssisi',$type,$file,$source,$notes,$admin); $stmt->execute(); $id=(int)$conn->insert_id; $stmt->close(); return $id; }
function pti3_update_import_batch(mysqli $conn, int $id, int $source, int $inserted, int $updated, int $skipped, string $notes=''): void { $stmt=$conn->prepare("UPDATE temp_pi3_import_batches SET source_count=?, imported_count=?, updated_count=?, skipped_count=?, notes=? WHERE id=?"); $stmt->bind_param('iiiisi',$source,$inserted,$updated,$skipped,$notes,$id); $stmt->execute(); $stmt->close(); }

function pti3_sync_brand_aliases_from_brands(mysqli $conn): void {
    pti3_ensure_schema($conn); if(!pti3_table_exists($conn,'brands')) return; $res=$conn->query("SELECT name FROM brands WHERE name IS NOT NULL AND name<>''"); if(!$res) return;
    $stmt=$conn->prepare("INSERT INTO temp_pi3_brand_aliases (brand_name, alias_text, alias_normalized) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE brand_name=VALUES(brand_name), alias_text=VALUES(alias_text), is_active=1");
    while($r=$res->fetch_assoc()){ $b=trim((string)$r['name']); $n=pti3_normalize($b); if($b===''||$n==='') continue; $stmt->bind_param('sss',$b,$b,$n); $stmt->execute(); }
    $stmt->close();
}
function pti3_guess_brand(mysqli $conn, string $text): string { pti3_sync_brand_aliases_from_brands($conn); $norm=' '.pti3_normalize($text).' '; if(trim($norm)==='') return ''; $res=$conn->query("SELECT brand_name, alias_normalized FROM temp_pi3_brand_aliases WHERE is_active=1 ORDER BY CHAR_LENGTH(alias_normalized) DESC LIMIT 1200"); if(!$res) return ''; while($r=$res->fetch_assoc()){ $a=trim((string)$r['alias_normalized']); if($a!=='' && (mb_strpos($norm,' '.$a.' ',0,'UTF-8')!==false || (mb_strlen($a,'UTF-8')>=4 && mb_strpos($norm,$a,0,'UTF-8')!==false))) return (string)$r['brand_name']; } return ''; }
function pti3_refresh_brand_suggestions(mysqli $conn): array { $offline=0;$legacy=0; $res=$conn->query("SELECT id, offline_name FROM temp_pi3_offline_items"); if($res){ $stmt=$conn->prepare("UPDATE temp_pi3_offline_items SET suggested_brand=? WHERE id=?"); while($r=$res->fetch_assoc()){ $b=pti3_guess_brand($conn,(string)$r['offline_name']); $id=(int)$r['id']; $stmt->bind_param('si',$b,$id); $stmt->execute(); $offline++; } $stmt->close(); } $res=$conn->query("SELECT id, legacy_name, legacy_description FROM temp_pi3_legacy_items"); if($res){ $stmt=$conn->prepare("UPDATE temp_pi3_legacy_items SET suggested_brand=? WHERE id=?"); while($r=$res->fetch_assoc()){ $b=pti3_guess_brand($conn,(string)$r['legacy_name'].' '.(string)$r['legacy_description']); $id=(int)$r['id']; $stmt->bind_param('si',$b,$id); $stmt->execute(); $legacy++; } $stmt->close(); } return ['offline'=>$offline,'legacy'=>$legacy]; }

function pti3_import_brand_rows(mysqli $conn, array $rows, string $file): array { pti3_ensure_schema($conn); [$hi,$map]=pti3_find_header_map($rows,'brands',['brand']); $batch=pti3_create_import_batch($conn,'brands',$file,count($rows)); $ins=0;$upd=0;$skip=0; $stmt=$conn->prepare("INSERT INTO temp_pi3_brand_aliases (brand_name, alias_text, alias_normalized) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE brand_name=VALUES(brand_name), alias_text=VALUES(alias_text), is_active=1"); for($i=$hi+1;$i<count($rows);$i++){ $brand=trim((string)($rows[$i][$map['brand']]??'')); $norm=pti3_normalize($brand); if($brand===''||$norm===''){ $skip++; continue; } $chk=$conn->prepare("SELECT id FROM temp_pi3_brand_aliases WHERE alias_normalized=? LIMIT 1"); $chk->bind_param('s',$norm); $chk->execute(); $exists=$chk->get_result()->fetch_assoc(); $chk->close(); $stmt->bind_param('sss',$brand,$brand,$norm); $stmt->execute(); $exists?$upd++:$ins++; } $stmt->close(); $ref=pti3_refresh_brand_suggestions($conn); pti3_update_import_batch($conn,$batch,max(0,count($rows)-$hi-1),$ins,$upd,$skip); return ['imported'=>$ins,'updated'=>$upd,'skipped'=>$skip,'refresh'=>$ref]; }

function pti3_existing_product_id_by_barcode(mysqli $conn, string $barcode): ?int { $barcode=trim($barcode); if($barcode==='') return null; if(pti3_table_exists($conn,'products')){ $stmt=$conn->prepare("SELECT id FROM products WHERE barcode=? OR sku=? LIMIT 1"); $stmt->bind_param('ss',$barcode,$barcode); $stmt->execute(); $row=$stmt->get_result()->fetch_assoc(); $stmt->close(); if($row) return (int)$row['id']; } if(pti3_table_exists($conn,'product_variants')){ $stmt=$conn->prepare("SELECT product_id AS id FROM product_variants WHERE barcode=? OR sku=? LIMIT 1"); $stmt->bind_param('ss',$barcode,$barcode); $stmt->execute(); $row=$stmt->get_result()->fetch_assoc(); $stmt->close(); if($row) return (int)$row['id']; } return null; }
function pti3_import_offline_rows(mysqli $conn, array $rows, string $file): array { pti3_ensure_schema($conn); pti3_sync_brand_aliases_from_brands($conn); [$hi,$map]=pti3_find_header_map($rows,'offline',['name','barcode','price']); $batch=pti3_create_import_batch($conn,'offline',$file,count($rows)); $ins=0;$upd=0;$skip=0; $sel=$conn->prepare("SELECT id FROM temp_pi3_offline_items WHERE barcode=? LIMIT 1"); $insStmt=$conn->prepare("INSERT INTO temp_pi3_offline_items (batch_id, source_file, offline_name, search_text, barcode, price, suggested_brand, duplicate_product_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); $updStmt=$conn->prepare("UPDATE temp_pi3_offline_items SET batch_id=?, source_file=?, offline_name=?, search_text=?, price=?, suggested_brand=?, duplicate_product_id=? WHERE barcode=?"); for($i=$hi+1;$i<count($rows);$i++){ $r=$rows[$i]; $name=trim((string)($r[$map['name']]??'')); $barcode=trim((string)($r[$map['barcode']]??'')); $price=pti3_to_float($r[$map['price']]??0); if($name===''||$barcode===''){ $skip++; continue; } $search=pti3_normalize($name); $brand=pti3_guess_brand($conn,$name); $dup=pti3_existing_product_id_by_barcode($conn,$barcode); $dupVal=$dup?:null; $sel->bind_param('s',$barcode); $sel->execute(); $exists=$sel->get_result()->fetch_assoc(); if($exists){ $updStmt->bind_param('isssdsis',$batch,$file,$name,$search,$price,$brand,$dupVal,$barcode); $updStmt->execute(); $upd++; } else { $insStmt->bind_param('issssdsi',$batch,$file,$name,$search,$barcode,$price,$brand,$dupVal); $insStmt->execute(); $ins++; } } $sel->close(); $insStmt->close(); $updStmt->close(); pti3_update_import_batch($conn,$batch,max(0,count($rows)-$hi-1),$ins,$upd,$skip); return ['imported'=>$ins,'updated'=>$upd,'skipped'=>$skip]; }
function pti3_import_zip_offline(mysqli $conn, string $tmp, string $file): array { if(!class_exists('ZipArchive')) throw new Exception('السيرفر لا يدعم ZipArchive.'); $zip=new ZipArchive(); if($zip->open($tmp)!==true) throw new Exception('تعذر فتح ZIP.'); $dir=sys_get_temp_dir().'/pti3_'.uniqid(); mkdir($dir,0775,true); $files=0;$ins=0;$upd=0;$skip=0;$errors=[]; for($i=0;$i<$zip->numFiles;$i++){ $name=$zip->getNameIndex($i); if(!$name || !preg_match('/\.(xlsx|csv|txt)$/i',$name)) continue; $base=basename($name); $dest=$dir.'/'.$base; copy('zip://'.$tmp.'#'.$name,$dest); try{ $rows=pti3_parse_file_rows($dest,$base); $r=pti3_import_offline_rows($conn,$rows,$base); $files++; $ins+=$r['imported']; $upd+=$r['updated']; $skip+=$r['skipped']; }catch(Throwable $e){ $errors[]=$base.': '.$e->getMessage(); } @unlink($dest); } $zip->close(); @rmdir($dir); return ['files'=>$files,'imported'=>$ins,'updated'=>$upd,'skipped'=>$skip,'errors'=>$errors]; }
function pti3_import_legacy_rows(mysqli $conn, array $rows, string $file): array { pti3_ensure_schema($conn); pti3_sync_brand_aliases_from_brands($conn); [$hi,$map]=pti3_find_header_map($rows,'legacy',['name']); $batch=pti3_create_import_batch($conn,'legacy',$file,count($rows)); $ins=0;$upd=0;$skip=0; $sel=$conn->prepare("SELECT id FROM temp_pi3_legacy_items WHERE legacy_hash=? LIMIT 1"); $insStmt=$conn->prepare("INSERT INTO temp_pi3_legacy_items (batch_id, legacy_ref, legacy_hash, legacy_name, search_text, legacy_description, legacy_category, legacy_sale_price, legacy_qty, suggested_brand) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); $updStmt=$conn->prepare("UPDATE temp_pi3_legacy_items SET batch_id=?, legacy_ref=?, legacy_name=?, search_text=?, legacy_description=?, legacy_category=?, legacy_sale_price=?, legacy_qty=?, suggested_brand=? WHERE legacy_hash=? AND status IN ('pending_match','ignored','on_hold')"); for($i=$hi+1;$i<count($rows);$i++){ $r=$rows[$i]; $name=trim((string)($r[$map['name']]??'')); if($name===''){ $skip++; continue; } $desc=trim((string)($r[$map['description']??-1]??'')); $cat=trim((string)($r[$map['category']??-1]??'')); $price=pti3_to_float($r[$map['sale_price']??-1]??0); $qty=pti3_to_int($r[$map['qty']??-1]??0); $ref=trim((string)($r[$map['legacy_ref']??-1]??'')); $hash=sha1($name.'|'.$desc.'|'.$cat.'|'.$price.'|'.$ref); $search=pti3_normalize($name.' '.$desc.' '.$cat); $brand=pti3_guess_brand($conn,$name.' '.$desc); $sel->bind_param('s',$hash); $sel->execute(); $exists=$sel->get_result()->fetch_assoc(); if($exists){ $updStmt->bind_param('isssssdiss',$batch,$ref,$name,$search,$desc,$cat,$price,$qty,$brand,$hash); $updStmt->execute(); $upd++; } else { $insStmt->bind_param('issssssdis',$batch,$ref,$hash,$name,$search,$desc,$cat,$price,$qty,$brand); $insStmt->execute(); $ins++; } } $sel->close(); $insStmt->close(); $updStmt->close(); pti3_update_import_batch($conn,$batch,max(0,count($rows)-$hi-1),$ins,$upd,$skip); return ['imported'=>$ins,'updated'=>$upd,'skipped'=>$skip]; }

function pti3_counts(mysqli $conn): array { pti3_ensure_schema($conn); $out=[]; foreach(['offline'=>'temp_pi3_offline_items','legacy'=>'temp_pi3_legacy_items','employees'=>'temp_pi3_employees','batches'=>'temp_pi3_work_batches','tasks'=>'temp_pi3_product_tasks'] as $k=>$t){ $r=$conn->query("SELECT COUNT(*) c FROM {$t}"); $out[$k]=(int)($r->fetch_assoc()['c']??0); } foreach(['assigned','in_progress','ready_for_review','returned','imported'] as $s){ $stmt=$conn->prepare("SELECT COUNT(*) c FROM temp_pi3_product_tasks WHERE status=?"); $stmt->bind_param('s',$s); $stmt->execute(); $out[$s]=(int)($stmt->get_result()->fetch_assoc()['c']??0); $stmt->close(); } foreach(['pending_match','ignored','on_hold','assigned','imported'] as $ls){ $stmt=$conn->prepare("SELECT COUNT(*) c FROM temp_pi3_legacy_items WHERE status=?"); $stmt->bind_param('s',$ls); $stmt->execute(); $out['legacy_'.$ls]=(int)($stmt->get_result()->fetch_assoc()['c']??0); $stmt->close(); } $r=$conn->query("SELECT COUNT(*) c FROM temp_pi3_legacy_items l LEFT JOIN temp_pi3_product_tasks t ON t.legacy_id=l.id WHERE t.id IS NULL AND l.status='pending_match'"); $out['pending_match']=(int)($r->fetch_assoc()['c']??0); $out['ignored']=$out['legacy_ignored']??0; $out['on_hold']=$out['legacy_on_hold']??0; return $out; }
function pti3_get_employees(mysqli $conn, bool $activeOnly=true): array { pti3_ensure_schema($conn); $sql="SELECT * FROM temp_pi3_employees".($activeOnly?" WHERE is_active=1":"")." ORDER BY is_active DESC, id DESC"; $res=$conn->query($sql); return $res?$res->fetch_all(MYSQLI_ASSOC):[]; }
function pti3_get_brands(mysqli $conn): array { $items=[]; if(pti3_table_exists($conn,'brands')){ $res=$conn->query("SELECT id, name FROM brands WHERE status=1 ORDER BY sort_order ASC, name ASC"); if($res) while($r=$res->fetch_assoc()) $items[]=$r; } return $items; }
function pti3_create_official_brand(mysqli $conn, string $name, string $alias=''): array {
    pti3_ensure_schema($conn); $name=trim($name); $alias=trim($alias); if($name==='') throw new Exception('اكتب اسم البراند.');
    $brandId=0; $created=false;
    if(pti3_table_exists($conn,'brands')){
        $stmt=$conn->prepare("SELECT id,name FROM brands WHERE name=? LIMIT 1"); $stmt->bind_param('s',$name); $stmt->execute(); $existing=$stmt->get_result()->fetch_assoc(); $stmt->close();
        if($existing){ $brandId=(int)$existing['id']; $name=(string)$existing['name']; }
        else{
            $slug=pti3_slugify($name); if($slug==='') $slug='brand-'.time(); $base=$slug; $i=1;
            if(pti3_col_exists($conn,'brands','slug')){ while(true){ $st=$conn->prepare("SELECT id FROM brands WHERE slug=? LIMIT 1"); $st->bind_param('s',$slug); $st->execute(); $hit=$st->get_result()->fetch_assoc(); $st->close(); if(!$hit) break; $i++; $slug=$base.'-'.$i; } }
            $cols=[]; $vals=[];
            $add=function($col,$val,$num=false) use (&$cols,&$vals,$conn){ if(pti3_col_exists($conn,'brands',$col)){ $cols[]='`'.$col.'`'; $vals[]=$num?(string)$val:"'".$conn->real_escape_string((string)$val)."'"; } };
            $add('name',$name); $add('slug',$slug); $add('image',''); $add('description',''); $add('sort_order',0,true); $add('status',1,true);
            if(!$cols) throw new Exception('جدول البراندات غير واضح.');
            $sql="INSERT INTO brands (".implode(',',$cols).") VALUES (".implode(',',$vals).")"; if(!$conn->query($sql)) throw new Exception('فشل إضافة البراند: '.$conn->error);
            $brandId=(int)$conn->insert_id; $created=true;
        }
    }
    foreach(array_filter(array_unique([$name,$alias])) as $a){ $n=pti3_normalize($a); if($n==='') continue; $stmt=$conn->prepare("INSERT INTO temp_pi3_brand_aliases (brand_name, alias_text, alias_normalized, is_active) VALUES (?, ?, ?, 1) ON DUPLICATE KEY UPDATE brand_name=VALUES(brand_name), alias_text=VALUES(alias_text), is_active=1"); $stmt->bind_param('sss',$name,$a,$n); $stmt->execute(); $stmt->close(); }
    return ['id'=>$brandId,'name'=>$name,'created'=>$created];
}
function pti3_set_legacy_decision(mysqli $conn, int $legacyId, string $status, string $notes=''): void {
    pti3_ensure_schema($conn); $allowed=['pending_match','ignored','on_hold']; if(!in_array($status,$allowed,true)) throw new Exception('حالة غير صحيحة.');
    $st=$conn->prepare("SELECT t.id FROM temp_pi3_product_tasks t WHERE t.legacy_id=? LIMIT 1"); $st->bind_param('i',$legacyId); $st->execute(); $task=$st->get_result()->fetch_assoc(); $st->close(); if($task && $status!=='pending_match') throw new Exception('لا يمكن تغيير حالة مادة لديها مهمة مرسلة للموظف.');
    $stmt=$conn->prepare("UPDATE temp_pi3_legacy_items SET status=?, decision_notes=?, decided_at=NOW() WHERE id=? AND status IN ('pending_match','ignored','on_hold')"); $stmt->bind_param('ssi',$status,$notes,$legacyId); $stmt->execute(); $stmt->close();
}
function pti3_get_categories(mysqli $conn): array { $items=[]; if(pti3_table_exists($conn,'categories')){ $res=$conn->query("SELECT id, name FROM categories WHERE status=1 ORDER BY sort_order ASC, id DESC"); if($res) while($r=$res->fetch_assoc()) $items[]=$r; } return $items; }
function pti3_get_product_types(mysqli $conn): array { $items=[]; if(pti3_table_exists($conn,'product_types')){ $res=$conn->query("SELECT id, category_id, name FROM product_types WHERE status=1 ORDER BY sort_order ASC, id DESC"); if($res) while($r=$res->fetch_assoc()) $items[]=$r; } return $items; }
function pti3_pending_legacy(mysqli $conn, int $limit=20, int $offset=0, string $q=''): array { pti3_ensure_schema($conn); $where="t.id IS NULL AND l.status='pending_match'"; $params=[];$types=''; if(trim($q)!==''){ $like='%'.pti3_normalize($q).'%'; $where.=" AND l.search_text LIKE ?"; $params[]=$like; $types.='s'; } $sql="SELECT l.* FROM temp_pi3_legacy_items l LEFT JOIN temp_pi3_product_tasks t ON t.legacy_id=l.id WHERE {$where} ORDER BY l.id ASC LIMIT ? OFFSET ?"; $params[]=$limit; $params[]=$offset; $types.='ii'; $stmt=$conn->prepare($sql); $stmt->bind_param($types,...$params); $stmt->execute(); $rows=$stmt->get_result()->fetch_all(MYSQLI_ASSOC); $stmt->close(); return $rows; }
function pti3_search_offline(mysqli $conn, string $q, int $limit=30): array { pti3_ensure_schema($conn); $q=trim($q); if($q==='') return []; $norm=pti3_normalize($q); $likeNorm='%'.$norm.'%'; $likeRaw='%'.$q.'%'; $limit=max(5,min(60,$limit)); $sql="SELECT o.*, CASE WHEN o.barcode=? THEN 100 WHEN o.search_text LIKE ? THEN 80 WHEN o.offline_name LIKE ? THEN 70 WHEN o.suggested_brand LIKE ? THEN 40 ELSE 10 END AS score FROM temp_pi3_offline_items o WHERE o.barcode LIKE ? OR o.search_text LIKE ? OR o.offline_name LIKE ? OR o.suggested_brand LIKE ? ORDER BY score DESC, o.offline_name ASC LIMIT {$limit}"; $stmt=$conn->prepare($sql); $stmt->bind_param('ssssssss',$q,$likeNorm,$likeRaw,$likeRaw,$likeRaw,$likeNorm,$likeRaw,$likeRaw); $stmt->execute(); $rows=$stmt->get_result()->fetch_all(MYSQLI_ASSOC); $stmt->close(); foreach($rows as &$r){ $r['used_by']=''; $st=$conn->prepare("SELECT l.legacy_name, t.status FROM temp_pi3_product_tasks t INNER JOIN temp_pi3_legacy_items l ON l.id=t.legacy_id WHERE t.offline_id=? AND t.status<>'imported' LIMIT 1"); $oid=(int)$r['id']; $st->bind_param('i',$oid); $st->execute(); $u=$st->get_result()->fetch_assoc(); $st->close(); if($u){ $r['used_by']=(string)$u['legacy_name']; $r['used_status']=(string)$u['status']; } } unset($r); return $rows; }
function pti3_get_task(mysqli $conn, int $id): ?array { $stmt=$conn->prepare("SELECT t.*, e.name AS employee_name, l.legacy_name, l.legacy_description, l.legacy_sale_price, l.legacy_qty, l.legacy_category, o.offline_name, o.price AS offline_price, o.barcode AS offline_barcode, b.title AS batch_title FROM temp_pi3_product_tasks t INNER JOIN temp_pi3_legacy_items l ON l.id=t.legacy_id INNER JOIN temp_pi3_offline_items o ON o.id=t.offline_id INNER JOIN temp_pi3_employees e ON e.id=t.employee_id INNER JOIN temp_pi3_work_batches b ON b.id=t.work_batch_id WHERE t.id=? LIMIT 1"); $stmt->bind_param('i',$id); $stmt->execute(); $row=$stmt->get_result()->fetch_assoc(); $stmt->close(); return $row?:null; }
function pti3_update_batch_progress(mysqli $conn, int $batchId): void { $stmt=$conn->prepare("SELECT COUNT(*) total, SUM(status IN ('ready_for_review','imported')) completed, SUM(status='imported') imported FROM temp_pi3_product_tasks WHERE work_batch_id=?"); $stmt->bind_param('i',$batchId); $stmt->execute(); $r=$stmt->get_result()->fetch_assoc(); $stmt->close(); $total=(int)($r['total']??0); $completed=(int)($r['completed']??0); $imported=(int)($r['imported']??0); $status='assigned'; if($total>0 && $imported===$total) $status='imported'; elseif($completed===$total) $status='ready_for_review'; elseif($completed>0) $status='in_progress'; $up=$conn->prepare("UPDATE temp_pi3_work_batches SET total_items=?, completed_items=?, imported_items=?, status=? WHERE id=?"); $up->bind_param('iiisi',$total,$completed,$imported,$status,$batchId); $up->execute(); $up->close(); }
function pti3_employee_required(mysqli $conn): array { pti3_ensure_schema($conn); $id=(int)($_SESSION['pti3_employee_id']??0); if($id<=0) pti3_redirect('employee_login.php'); $stmt=$conn->prepare("SELECT * FROM temp_pi3_employees WHERE id=? AND is_active=1 LIMIT 1"); $stmt->bind_param('i',$id); $stmt->execute(); $emp=$stmt->get_result()->fetch_assoc(); $stmt->close(); if(!$emp){ unset($_SESSION['pti3_employee_id']); pti3_redirect('employee_login.php'); } return $emp; }

function pti3_image_slug(string $text): string { $s=pti3_slugify($text); return $s!==''?$s:'general'; }
function pti3_upload_product_image(?array $file, string $brand, string $prefix='pti3_'): ?string { if(!$file || !isset($file['name'],$file['tmp_name'],$file['error']) || $file['error']===UPLOAD_ERR_NO_FILE || empty($file['name'])) return null; if($file['error']!==UPLOAD_ERR_OK) throw new Exception('فشل رفع الصورة: '.$file['name']); $allowed=['image/jpeg'=>'jpg','image/jpg'=>'jpg','image/png'=>'png','image/webp'=>'webp']; $f=finfo_open(FILEINFO_MIME_TYPE); $mime=finfo_file($f,$file['tmp_name']); finfo_close($f); if(!isset($allowed[$mime])) throw new Exception('نوع صورة غير مدعوم: '.$file['name']); $brandSlug=pti3_image_slug($brand); $dir=dirname(__DIR__).'/uploads/products/'.$brandSlug.'/'; if(!is_dir($dir) && !mkdir($dir,0775,true) && !is_dir($dir)) throw new Exception('تعذر إنشاء مجلد الصور.'); $new=$prefix.uniqid('',true).'.'.$allowed[$mime]; if(!move_uploaded_file($file['tmp_name'],$dir.$new)) throw new Exception('تعذر حفظ الصورة.'); return $brandSlug.'/'.$new; }
function pti3_unique_slug(mysqli $conn, string $name, string $barcode, int $taskId): string { $base=pti3_slugify($name); if($base==='') $base='product-'.$taskId; $slug=$base; $i=1; while(true){ $stmt=$conn->prepare("SELECT id FROM products WHERE slug=? LIMIT 1"); $stmt->bind_param('s',$slug); $stmt->execute(); $exists=$stmt->get_result()->fetch_assoc(); $stmt->close(); if(!$exists) return $slug; $suffix=$barcode!==''?'-'.preg_replace('/[^0-9A-Za-z]+/','',$barcode):'-'.$taskId.'-'.$i; $slug=$base.$suffix; $i++; if($i>20) return $base.'-'.time().'-'.$taskId; } }
function pti3_import_task_to_products(mysqli $conn, int $taskId): int {
    $task=pti3_get_task($conn,$taskId); if(!$task) throw new Exception('المهمة غير موجودة.'); if((string)$task['status']==='imported' && (int)$task['imported_product_id']>0) return (int)$task['imported_product_id'];
    $category=(int)($task['final_category_id']??0); if($category<=0) throw new Exception('لا يمكن الإضافة بدون قسم.');
    $name=trim((string)$task['final_name']); $brand=trim((string)$task['brand_name']); $desc=(string)($task['final_description']??''); $barcode=trim((string)$task['final_barcode']); $price=(float)$task['final_price']; $stock=(int)$task['final_stock_qty']; $main=(string)($task['main_image']??''); $extra=(string)($task['extra_image']??'');
    if($name==='' || $brand==='' || $barcode==='') throw new Exception('بيانات ناقصة: الاسم/البراند/الباركود.');
    $dup=pti3_existing_product_id_by_barcode($conn,$barcode); if($dup) throw new Exception('الباركود موجود مسبقاً بمنتج رقم '.$dup);
    $slug=pti3_unique_slug($conn,$name,$barcode,$taskId); $productPart=$name; if(mb_strpos($name,$brand,0,'UTF-8')===0){ $productPart=trim(preg_replace('/^'.preg_quote($brand,'/').'\s*-?\s*/u','',$name)); if($productPart==='') $productPart=$name; }
    $hasVariants=0; $variantsSame=1; $low=0; $pre=0; $accessMode='all'; $accessScope='always'; $accessMin=0.0; $gate=0; $gateQty=0; $status=1; $sort=0; $sku=$barcode;
    $stmt=$conn->prepare("INSERT INTO products (category_id, brand_part, product_part, name, slug, sku, barcode, short_description, main_image, extra_image, price, has_variants, variants_same_price, stock_qty, low_stock_alert, allow_preorder, access_mode, access_scope, access_min_subtotal, low_stock_gate_enabled, low_stock_gate_qty, status, sort_order) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param('isssssssssdiiiiissdiiii',$category,$brand,$productPart,$name,$slug,$sku,$barcode,$desc,$main,$extra,$price,$hasVariants,$variantsSame,$stock,$low,$pre,$accessMode,$accessScope,$accessMin,$gate,$gateQty,$status,$sort);
    $stmt->execute(); $pid=(int)$conn->insert_id; $stmt->close();
    $typeId=(int)($task['final_product_type_id']??0); if($typeId>0 && pti3_table_exists($conn,'product_type_links')){ $ls=$conn->prepare("INSERT INTO product_type_links (product_id, product_type_id) VALUES (?, ?)"); $ls->bind_param('ii',$pid,$typeId); $ls->execute(); $ls->close(); }
    $up=$conn->prepare("UPDATE temp_pi3_product_tasks SET status='imported', imported_product_id=?, imported_at=NOW() WHERE id=?"); $up->bind_param('ii',$pid,$taskId); $up->execute(); $up->close();
    $legacyId=(int)$task['legacy_id']; $conn->query("UPDATE temp_pi3_legacy_items SET status='imported' WHERE id=".$legacyId);
    pti3_update_batch_progress($conn,(int)$task['work_batch_id']); pti3_log($conn,'task.import','إضافة منتج من أداة V3',['task_id'=>$taskId,'product_id'=>$pid]); return $pid;
}
?>
