<?php
if (!function_exists('pic_slugify_text')) {
    function pic_slugify_text($text) {
        $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');
    }
}

if (!function_exists('pic_col_exists')) {
    function pic_col_exists(mysqli $conn, string $table, string $column): bool {
        $table = $conn->real_escape_string($table);
        $column = $conn->real_escape_string($column);
        $res = $conn->query("SHOW COLUMNS FROM `{$table}` LIKE '{$column}'");
        return $res && $res->num_rows > 0;
    }
}

if (!function_exists('pic_table_exists')) {
    function pic_table_exists(mysqli $conn, string $table): bool {
        $table = $conn->real_escape_string($table);
        $res = $conn->query("SHOW TABLES LIKE '{$table}'");
        return $res && $res->num_rows > 0;
    }
}

if (!function_exists('pic_index_exists')) {
    function pic_index_exists(mysqli $conn, string $table, string $index): bool {
        $tableEsc = $conn->real_escape_string($table);
        $indexEsc = $conn->real_escape_string($index);
        $res = $conn->query("SHOW INDEX FROM `{$tableEsc}` WHERE Key_name = '{$indexEsc}'");
        return $res && $res->num_rows > 0;
    }
}

if (!function_exists('pic_ensure_schema')) {
    function pic_ensure_schema(mysqli $conn): void {
        $conn->query("CREATE TABLE IF NOT EXISTS `product_import_catalog` (
            `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
            `internal_name` varchar(255) NOT NULL,
            `internal_slug` varchar(275) NOT NULL,
            `barcode` varchar(160) NOT NULL,
            `price` decimal(12,2) NOT NULL DEFAULT 0.00,
            `notes` text DEFAULT NULL,
            `is_used` tinyint(1) NOT NULL DEFAULT 0,
            `linked_product_id` int(10) UNSIGNED DEFAULT NULL,
            `last_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`),
            KEY `idx_pic_internal_slug` (`internal_slug`),
            KEY `idx_pic_barcode` (`barcode`),
            KEY `idx_pic_used` (`is_used`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        if (pic_table_exists($conn, 'products')) {
            if (!pic_col_exists($conn, 'products', 'import_catalog_id')) {
                $conn->query("ALTER TABLE `products` ADD COLUMN `import_catalog_id` int(10) UNSIGNED DEFAULT NULL AFTER `id`");
            }
            if (!pic_col_exists($conn, 'products', 'barcode')) {
                $conn->query("ALTER TABLE `products` ADD COLUMN `barcode` varchar(160) DEFAULT NULL AFTER `sku`");
            }
            if (!pic_col_exists($conn, 'products', 'internal_name')) {
                $conn->query("ALTER TABLE `products` ADD COLUMN `internal_name` varchar(255) DEFAULT NULL AFTER `barcode`");
            }
            if (!pic_index_exists($conn, 'products', 'idx_products_import_catalog_id')) {
                $conn->query("ALTER TABLE `products` ADD INDEX `idx_products_import_catalog_id` (`import_catalog_id`)");
            }
            if (!pic_index_exists($conn, 'products', 'idx_products_barcode')) {
                $conn->query("ALTER TABLE `products` ADD INDEX `idx_products_barcode` (`barcode`)");
            }
        }

        if (pic_table_exists($conn, 'product_variants')) {
            if (!pic_col_exists($conn, 'product_variants', 'barcode')) {
                $conn->query("ALTER TABLE `product_variants` ADD COLUMN `barcode` varchar(160) DEFAULT NULL AFTER `sku`");
            }
            if (!pic_index_exists($conn, 'product_variants', 'idx_product_variants_barcode')) {
                $conn->query("ALTER TABLE `product_variants` ADD INDEX `idx_product_variants_barcode` (`barcode`)");
            }
        }
    }
}

if (!function_exists('pic_normalize_header')) {
    function pic_normalize_header($h): string {
        $h = trim(mb_strtolower((string)$h, 'UTF-8'));
        $h = str_replace(["\xEF\xBB\xBF", ' ', '-', '.', ':'], ['','_','_','',''], $h);
        return $h;
    }
}

if (!function_exists('pic_header_to_key')) {
    function pic_header_to_key($h): ?string {
        $h = pic_normalize_header($h);
        $map = [
            'internal_name' => 'internal_name', 'internal' => 'internal_name', 'name' => 'internal_name', 'product_name' => 'internal_name', 'slug' => 'internal_name',
            'اسم_المنتج' => 'internal_name', 'الاسم_الداخلي' => 'internal_name', 'اسم_داخلي' => 'internal_name', 'المادة' => 'internal_name', 'المنتج' => 'internal_name',
            'barcode' => 'barcode', 'bar_code' => 'barcode', 'code' => 'barcode', 'sku' => 'barcode', 'باركود' => 'barcode', 'الباركود' => 'barcode', 'كود' => 'barcode',
            'price' => 'price', 'sale_price' => 'price', 'cost' => 'price', 'السعر' => 'price', 'سعر' => 'price',
            'notes' => 'notes', 'note' => 'notes', 'ملاحظات' => 'notes', 'ملاحظة' => 'notes',
        ];
        return $map[$h] ?? null;
    }
}

if (!function_exists('pic_parse_csv')) {
    function pic_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;
    }
}

if (!function_exists('pic_xlsx_col_index')) {
    function pic_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);
    }
}

if (!function_exists('pic_parse_xlsx')) {
    function pic_parse_xlsx(string $path): array {
        if (!class_exists('ZipArchive')) {
            throw new Exception('السيرفر لا يدعم قراءة XLSX مباشرة. احفظ ملف Excel بصيغة CSV وارفعه.');
        }
        $zip = new ZipArchive();
        if ($zip->open($path) !== true) {
            throw new Exception('تعذر فتح ملف XLSX.');
        }
        $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) {
                $ref = (string)$c['r'];
                $idx = pic_xlsx_col_index($ref);
                $type = (string)$c['t'];
                $value = '';
                if ($type === 's') {
                    $si = (int)($c->v ?? 0);
                    $value = $shared[$si] ?? '';
                } elseif ($type === 'inlineStr') {
                    $value = (string)($c->is->t ?? '');
                } else {
                    $value = (string)($c->v ?? '');
                }
                $row[$idx] = $value;
            }
            if (!empty($row)) {
                ksort($row);
                $max = max(array_keys($row));
                $normalized = [];
                for ($i = 0; $i <= $max; $i++) { $normalized[] = $row[$i] ?? ''; }
                $rows[] = $normalized;
            }
        }
        return $rows;
    }
}

if (!function_exists('pic_parse_uploaded_rows')) {
    function pic_parse_uploaded_rows(array $file): array {
        if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
            throw new Exception('فشل رفع الملف.');
        }
        $name = strtolower((string)($file['name'] ?? ''));
        $tmp = (string)($file['tmp_name'] ?? '');
        if (str_ends_with($name, '.xlsx')) {
            return pic_parse_xlsx($tmp);
        }
        if (str_ends_with($name, '.csv') || str_ends_with($name, '.txt')) {
            return pic_parse_csv($tmp);
        }
        throw new Exception('الصيغة المدعومة: CSV أو XLSX.');
    }
}

if (!function_exists('pic_import_rows')) {
    function pic_import_rows(mysqli $conn, array $rows): array {
        pic_ensure_schema($conn);
        $imported = 0; $updated = 0; $skipped = 0; $errors = [];
        if (count($rows) < 2) { throw new Exception('الملف فارغ أو لا يحتوي بيانات.'); }

        $headers = array_shift($rows);
        $indexMap = [];
        foreach ($headers as $i => $h) {
            $key = pic_header_to_key($h);
            if ($key && !isset($indexMap[$key])) { $indexMap[$key] = $i; }
        }
        if (!isset($indexMap['internal_name'], $indexMap['barcode'], $indexMap['price'])) {
            throw new Exception('لازم أول صف يحتوي أعمدة: internal_name, barcode, price أو ما يقابلها بالعربي.');
        }

        $select = $conn->prepare("SELECT id FROM product_import_catalog WHERE barcode = ? OR internal_slug = ? LIMIT 1");
        $insert = $conn->prepare("INSERT INTO product_import_catalog (internal_name, internal_slug, barcode, price, notes, last_imported_at) VALUES (?, ?, ?, ?, ?, NOW())");
        $update = $conn->prepare("UPDATE product_import_catalog SET internal_name=?, internal_slug=?, barcode=?, price=?, notes=?, last_imported_at=NOW() WHERE id=?");


        foreach ($rows as $rNo => $r) {
            $internalName = trim((string)($r[$indexMap['internal_name']] ?? ''));
            $barcode = trim((string)($r[$indexMap['barcode']] ?? ''));
            $priceRaw = trim((string)($r[$indexMap['price']] ?? '0'));
            $notes = isset($indexMap['notes']) ? trim((string)($r[$indexMap['notes']] ?? '')) : '';
            $price = (float)str_replace([',', 'د.ع', 'IQD', 'iqd', ' '], ['', '', '', '', ''], $priceRaw);
            if ($internalName === '' && $barcode === '') { continue; }
            if ($internalName === '' || $barcode === '') { continue; }
            $slug = pic_slugify_text($internalName);
            if ($slug === '') { continue; }
            $select->bind_param('ss', $barcode, $slug);
            $select->execute();
            $res = $select->get_result();
            if ($res && ($found = $res->fetch_assoc())) {
                $id = (int)$found['id'];
                $update->bind_param('sssdsi', $internalName, $slug, $barcode, $price, $notes, $id);
                $update->execute();
                $updated++;
            } else {
                $insert->bind_param('sssds', $internalName, $slug, $barcode, $price, $notes);
                $insert->execute();
                $imported++;
            }
        }
        $select->close(); $insert->close(); $update->close();
        pic_refresh_used_flags($conn);
        return compact('imported','updated','skipped','errors');
    }
}

if (!function_exists('pic_refresh_used_flags')) {
    function pic_refresh_used_flags(mysqli $conn): void {
        pic_ensure_schema($conn);
        $conn->query("UPDATE product_import_catalog c SET c.is_used = 0, c.linked_product_id = NULL");
        $conn->query("UPDATE product_import_catalog c INNER JOIN products p ON (p.import_catalog_id = c.id OR p.slug = c.internal_slug OR p.sku = c.barcode OR p.barcode = c.barcode) SET c.is_used = 1, c.linked_product_id = p.id");
    }
}

if (!function_exists('pic_catalog_lookup')) {
    function pic_catalog_lookup(mysqli $conn, int $id): ?array {
        pic_ensure_schema($conn);
        pic_refresh_used_flags($conn);
        $stmt = $conn->prepare("SELECT * FROM product_import_catalog WHERE id = ? LIMIT 1");
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $res = $stmt->get_result();
        $row = $res ? $res->fetch_assoc() : null;
        $stmt->close();
        return $row ?: null;
    }
}
?>
