<?php
declare(strict_types=1);

if (!function_exists('sc_contract_root')) {
    function sc_contract_root(): string
    {
        return dirname(__DIR__) . DIRECTORY_SEPARATOR . 'storage' . DIRECTORY_SEPARATOR . 'subscriber_contracts';
    }
}

if (!function_exists('sc_ensure_storage')) {
    function sc_ensure_storage(): void
    {
        $root = sc_contract_root();
        foreach ([$root, $root . '/templates', $root . '/generated', $root . '/assets'] as $dir) {
            if (!is_dir($dir) && !@mkdir($dir, 0770, true) && !is_dir($dir)) {
                throw new RuntimeException('تعذر إنشاء مجلد تخزين العقود.');
            }
        }
        $deny = $root . '/.htaccess';
        if (!is_file($deny)) @file_put_contents($deny, "Require all denied\n");
        $index = $root . '/index.php';
        if (!is_file($index)) @file_put_contents($index, "<?php http_response_code(404);\n");
    }
}

if (!function_exists('sc_ensure_schema')) {
    function sc_ensure_schema(mysqli $conn): void
    {
        $conn->query("CREATE TABLE IF NOT EXISTS subscriber_contract_templates (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(190) NOT NULL,
            file_path VARCHAR(500) NOT NULL,
            original_filename VARCHAR(255) NOT NULL DEFAULT '',
            is_default TINYINT(1) NOT NULL DEFAULT 0,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_by VARCHAR(120) NOT NULL DEFAULT '',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_contract_template_active (is_active, is_default)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $conn->query("CREATE TABLE IF NOT EXISTS subscriber_contracts (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            subscriber_id INT NOT NULL,
            template_id INT UNSIGNED NULL,
            contract_number VARCHAR(80) NOT NULL,
            title VARCHAR(255) NOT NULL DEFAULT '',
            custom_notes TEXT NULL,
            generated_path VARCHAR(500) NOT NULL DEFAULT '',
            generated_filename VARCHAR(255) NOT NULL DEFAULT '',
            has_stamp TINYINT(1) NOT NULL DEFAULT 0,
            has_signature TINYINT(1) NOT NULL DEFAULT 0,
            printed_count INT UNSIGNED NOT NULL DEFAULT 0,
            first_printed_at DATETIME NULL,
            last_printed_at DATETIME NULL,
            created_by VARCHAR(120) NOT NULL DEFAULT '',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_contract_number (contract_number),
            KEY idx_contract_subscriber (subscriber_id, created_at),
            KEY idx_contract_printed (printed_count, last_printed_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $conn->query("CREATE TABLE IF NOT EXISTS subscriber_contract_events (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            contract_id BIGINT UNSIGNED NULL,
            subscriber_id INT NOT NULL DEFAULT 0,
            event_type VARCHAR(40) NOT NULL,
            event_note VARCHAR(500) NOT NULL DEFAULT '',
            actor VARCHAR(120) NOT NULL DEFAULT '',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_contract_event_contract (contract_id, created_at),
            KEY idx_contract_event_subscriber (subscriber_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $conn->query("CREATE TABLE IF NOT EXISTS subscriber_contract_settings (
            setting_key VARCHAR(80) NOT NULL PRIMARY KEY,
            setting_value LONGTEXT NULL,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $conn->query("CREATE TABLE IF NOT EXISTS subscriber_contract_template_fields (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            template_id INT UNSIGNED NOT NULL,
            keyword_text VARCHAR(255) NOT NULL,
            subscriber_field VARCHAR(80) NOT NULL,
            sort_order INT UNSIGNED NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_template_keyword (template_id, keyword_text),
            KEY idx_template_fields (template_id, sort_order)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        sc_ensure_storage();
    }
}

function sc_contract_field_catalog(): array
{
    return [
        'full_name'=>'الاسم الكامل','first_name'=>'الاسم الأول','father_name'=>'اسم الأب','mother_name'=>'اسم الأم','last_name'=>'الكنية',
        'birth_date'=>'تاريخ الميلاد','nationality'=>'الجنسية','document_type'=>'نوع الوثيقة','document_number'=>'رقم الوثيقة','national_id'=>'الرقم الوطني',
        'record_number'=>'رقم القيد','phone'=>'رقم التواصل','address'=>'العنوان','pppoe_username'=>'اسم مستخدم PPPoE',
        'pppoe_password'=>'كلمة مرور PPPoE','package'=>'الباقة','speed'=>'السرعة','static_ip'=>'عنوان IP','sector'=>'القطاع',
        'email'=>'البريد الإلكتروني','notes'=>'ملاحظات المشترك','vlan_id'=>'VLAN','pppoe_profile'=>'ملف PPPoE','quota_gb'=>'الحصة',
        'current_mac'=>'عنوان MAC','device_name'=>'الجهاز','subscriber_id'=>'رقم المشترك','subscriber_created_at'=>'تاريخ إنشاء المشترك',
        'company_name'=>'اسم الشركة','contract_number'=>'رقم العقد','contract_date'=>'تاريخ العقد','contract_title'=>'عنوان العقد',
        'contract_notes'=>'ملاحظات العقد','generated_at'=>'وقت التوليد',
    ];
}

function sc_contract_keyword_aliases(): array
{
    $aliases = [
        'الاسم الكامل:'=>'full_name','اسم المشترك:'=>'full_name','اسم الفريق الثاني:'=>'full_name','اسم الأب:'=>'father_name',
        'اسم الأم:'=>'mother_name','تاريخ الميلاد:'=>'birth_date','الجنسية:'=>'nationality','نوع الوثيقة:'=>'document_type','نوع وثيقة الثبوتيات:'=>'document_type',
        'رقم الوثيقة:'=>'document_number','الرقم الوطني:'=>'national_id','رقم القيد:'=>'record_number','رقم التواصل:'=>'phone',
        'رقم الهاتف:'=>'phone','العنوان:'=>'address','اسم المستخدم:'=>'pppoe_username','حساب الخدمة:'=>'pppoe_username',
        'كلمة المرور:'=>'pppoe_password','عنوان IP:'=>'static_ip','البريد الإلكتروني:'=>'email','القطاع:'=>'sector','VLAN:'=>'vlan_id',
        'ملف PPPoE:'=>'pppoe_profile','الحصة:'=>'quota_gb','عنوان MAC:'=>'current_mac','الجهاز:'=>'device_name',
        'ملاحظات المشترك:'=>'notes','الباقة المختارة:'=>'package','السرعة المختارة:'=>'speed','رقم العقد:'=>'contract_number','تاريخ العقد:'=>'contract_date',
    ];
    foreach (array_keys(sc_contract_field_catalog()) as $field) $aliases['{{'.$field.'}}'] = $field;
    return $aliases;
}

function sc_normalize_contract_keyword_text(string $text): string
{
    $text = preg_replace('/[\s\x{00A0}]+/u', ' ', trim($text)) ?? trim($text);
    return preg_replace('/\s*[:\x{FF1A}]\s*/u', ':', $text) ?? $text;
}

function sc_contract_text_contains_keyword(string $text, string $keyword): bool
{
    if ($keyword === '') return false;
    return str_contains(
        sc_normalize_contract_keyword_text($text),
        sc_normalize_contract_keyword_text($keyword)
    );
}

function sc_contract_keyword_variants(string $keyword): array
{
    $keyword = trim($keyword);
    if ($keyword === '' || str_starts_with($keyword, '{{')) return $keyword === '' ? [] : [$keyword];
    $variants = [$keyword => $keyword];
    if (preg_match('/^(.*?)\s*[:\x{FF1A}]\s*$/u', $keyword, $match)) {
        $base = rtrim((string)$match[1]);
        foreach ([$base . ':', $base . ' :', $base . "\xEF\xBC\x9A", $base . " \xEF\xBC\x9A"] as $variant) {
            $variants[$variant] = $variant;
        }
    }
    return array_values($variants);
}

function sc_scan_template_keywords(string $relative): array
{
    $path = sc_safe_storage_path($relative);
    $zip = new ZipArchive();
    if (!is_file($path) || $zip->open($path) !== true) throw new RuntimeException('تعذر فحص ملف القالب.');
    $text = '';
    for ($i=0; $i<$zip->numFiles; $i++) {
        $name = (string)$zip->getNameIndex($i);
        if ($name === 'word/document.xml' || preg_match('#^word/(header|footer)\d+\.xml$#', $name)) {
            $xml = $zip->getFromName($name);
            if ($xml !== false) $text .= ' ' . sc_docx_xml_text((string)$xml);
        }
    }
    $zip->close();
    $found = [];
    foreach (sc_contract_keyword_aliases() as $keyword => $field) {
        if (sc_contract_text_contains_keyword($text, $keyword)) $found[] = ['keyword_text'=>$keyword,'subscriber_field'=>$field];
    }
    return $found;
}

function sc_template_field_rows(mysqli $conn, int $templateId): array
{
    $rows=[]; $stmt=$conn->prepare('SELECT id,keyword_text,subscriber_field,sort_order FROM subscriber_contract_template_fields WHERE template_id=? ORDER BY sort_order,id');
    if (!$stmt) return $rows; $stmt->bind_param('i',$templateId); $stmt->execute(); $result=$stmt->get_result();
    while($row=$result->fetch_assoc()) $rows[]=$row; $stmt->close(); return $rows;
}

function sc_seed_template_fields(mysqli $conn, int $templateId, array $suggestions): int
{
    $stmt=$conn->prepare('INSERT IGNORE INTO subscriber_contract_template_fields(template_id,keyword_text,subscriber_field,sort_order) VALUES(?,?,?,?)');
    if (!$stmt) throw new RuntimeException('تعذر حفظ اقتراحات الكلمات المفتاحية.');
    $added=0; $order=0;
    foreach($suggestions as $row){$keyword=trim((string)($row['keyword_text']??''));$field=(string)($row['subscriber_field']??'');if($keyword===''||!isset(sc_contract_field_catalog()[$field]))continue;$order++;$stmt->bind_param('issi',$templateId,$keyword,$field,$order);$stmt->execute();$added+=$stmt->affected_rows;}
    $stmt->close(); return $added;
}

function sc_apply_template_fields(array $map, array $rows): array
{
    if (!$rows) return $map;
    foreach(sc_contract_keyword_aliases() as $keyword=>$unused) if(!str_starts_with($keyword,'{{')) unset($map[$keyword]);
    foreach($rows as $row){$keyword=trim((string)$row['keyword_text']);$field=(string)$row['subscriber_field'];$value=trim((string)($map['{{'.$field.'}}']??''));if($keyword===''||$value==='')continue;foreach(sc_contract_keyword_variants($keyword) as $variant){$map[$variant]=str_starts_with($variant,'{{')?$value:$variant.' '.$value;}}
    return $map;
}

/**
 * Word does not store pages as independent objects. This layout reader uses
 * the page boundaries that Word persists in the document XML, so exporting a
 * specific page never relies on guessed paragraph counts.
 */
function sc_docx_has_renderable_content_before(DOMNode $root, DOMNode $target): bool
{
    $seenRenderable = false;
    $foundTarget = false;
    $walk = static function (DOMNode $node) use (&$walk, &$seenRenderable, &$foundTarget, $target): void {
        if ($foundTarget) return;
        if ($node->isSameNode($target)) {
            $foundTarget = true;
            return;
        }
        if ($node instanceof DOMElement) {
            $name = $node->localName;
            if (
                ($name === 't' && trim((string)$node->textContent) !== '')
                || in_array($name, ['drawing', 'pict', 'object', 'sym', 'tab'], true)
            ) {
                $seenRenderable = true;
            }
        }
        foreach ($node->childNodes as $child) {
            $walk($child);
            if ($foundTarget) return;
        }
    };
    $walk($root);
    return $foundTarget && $seenRenderable;
}

function sc_docx_node_has_renderable_content(DOMNode $node): bool
{
    if ($node instanceof DOMElement && $node->localName === 'tbl') return true;
    $walk = static function (DOMNode $current) use (&$walk): bool {
        if ($current instanceof DOMElement) {
            if ($current->localName === 't' && trim((string)$current->textContent) !== '') return true;
            if (in_array($current->localName, ['drawing', 'pict', 'object', 'sym'], true)) return true;
        }
        foreach ($current->childNodes as $child) {
            if ($walk($child)) return true;
        }
        return false;
    };
    return $walk($node);
}

function sc_docx_page_layout_dom(DOMDocument $dom): array
{
    $xp = new DOMXPath($dom);
    $xp->registerNamespace('w', 'http://schemas.openxmlformats.org/wordprocessingml/2006/main');
    $body = $xp->query('/w:document/w:body')->item(0);
    if (!$body) throw new RuntimeException('بنية صفحات قالب Word غير صالحة.');

    $page = 1;
    $maxPage = 1;
    $hasContent = false;
    $nodes = [];
    $pageTexts = [];
    foreach (iterator_to_array($body->childNodes) as $node) {
        if (!($node instanceof DOMElement) || $node->localName === 'sectPr') continue;

        $startsNewPage = $xp->query(
            './/w:pPr/w:pageBreakBefore[not(@w:val="0") and not(@w:val="false") and not(@w:val="off")]',
            $node
        )->length > 0;
        $hasTrailingExplicitBreak = false;
        foreach ($xp->query('.//w:lastRenderedPageBreak | .//w:br[@w:type="page"]', $node) as $breakNode) {
            if (sc_docx_has_renderable_content_before($node, $breakNode)) {
                $hasTrailingExplicitBreak = true;
            } else {
                // Word commonly stores lastRenderedPageBreak at the start of
                // the first paragraph on the next page. That paragraph belongs
                // to the new page, not to the page preceding the marker.
                $startsNewPage = true;
            }
        }
        if ($startsNewPage && $hasContent) $page++;

        $nodes[] = ['node' => $node, 'page' => $page];
        $maxPage = max($maxPage, $page);
        $blockText = '';
        foreach ($xp->query('.//w:t', $node) as $textNode) $blockText .= $textNode->textContent;
        $pageTexts[$page] = trim(($pageTexts[$page] ?? '') . ' ' . $blockText);
        if (trim($blockText) !== '' || $node->localName === 'tbl') $hasContent = true;

        $hasSectionBreak = false;
        foreach ($xp->query('.//w:pPr/w:sectPr', $node) as $section) {
            $type = $xp->query('./w:type', $section)->item(0);
            $value = $type instanceof DOMElement
                ? strtolower((string)$type->getAttributeNS('http://schemas.openxmlformats.org/wordprocessingml/2006/main', 'val'))
                : 'nextpage';
            if ($value !== 'continuous') {
                $hasSectionBreak = true;
                break;
            }
        }
        if ($hasTrailingExplicitBreak || $hasSectionBreak) $page++;
    }

    for ($i = 1; $i <= $maxPage; $i++) $pageTexts[$i] = trim((string)($pageTexts[$i] ?? ''));
    ksort($pageTexts);
    return [
        'body' => $body,
        'xpath' => $xp,
        'count' => $maxPage,
        'nodes' => $nodes,
        'page_texts' => $pageTexts,
    ];
}

function sc_docx_page_layout(string $xml): array
{
    $dom = new DOMDocument('1.0', 'UTF-8');
    $dom->preserveWhiteSpace = true;
    if (!@$dom->loadXML($xml, LIBXML_NONET | LIBXML_COMPACT)) {
        throw new RuntimeException('تعذر تحليل صفحات قالب Word.');
    }
    $layout = sc_docx_page_layout_dom($dom);
    unset($layout['body'], $layout['xpath'], $layout['nodes']);
    return $layout;
}

function sc_docx_declared_page_count(string $appXml): int
{
    if ($appXml === '') return 0;
    $dom = new DOMDocument('1.0', 'UTF-8');
    if (!@$dom->loadXML($appXml, LIBXML_NONET | LIBXML_COMPACT)) return 0;
    $xp = new DOMXPath($dom);
    $node = $xp->query('//*[local-name()="Pages"]')->item(0);
    return $node ? max(0, (int)trim((string)$node->textContent)) : 0;
}

function sc_docx_set_declared_page_count(ZipArchive $zip, int $pageCount): void
{
    $name = 'docProps/app.xml';
    $xml = $zip->getFromName($name);
    if ($xml === false) return;
    $dom = new DOMDocument('1.0', 'UTF-8');
    $dom->preserveWhiteSpace = true;
    if (!@$dom->loadXML($xml, LIBXML_NONET | LIBXML_COMPACT)) return;
    $xp = new DOMXPath($dom);
    $node = $xp->query('//*[local-name()="Pages"]')->item(0);
    if ($node) {
        $node->nodeValue = (string)max(1, $pageCount);
        $zip->addFromString($name, (string)$dom->saveXML());
    }
}

function sc_docx_xml_text(string $xml): string
{
    if ($xml === '') return '';
    $dom = new DOMDocument('1.0', 'UTF-8');
    if (!@$dom->loadXML($xml, LIBXML_NONET | LIBXML_COMPACT)) {
        return trim(html_entity_decode(strip_tags($xml), ENT_QUOTES | ENT_XML1, 'UTF-8'));
    }
    $xp = new DOMXPath($dom);
    $xp->registerNamespace('w', 'http://schemas.openxmlformats.org/wordprocessingml/2006/main');
    $text = '';
    foreach ($xp->query('//w:t') as $node) $text .= ' ' . $node->textContent;
    return trim($text);
}

function sc_contract_subscriber_fields(): array
{
    $fields = array_keys(sc_contract_field_catalog());
    $generated = [
        'company_name', 'contract_number', 'contract_date',
        'contract_title', 'contract_notes', 'generated_at',
    ];
    return array_values(array_diff($fields, $generated));
}

function sc_template_required_fields_for_text(string $text, array $templateFields): array
{
    $allowed = array_fill_keys(sc_contract_subscriber_fields(), true);
    $required = [];
    $hasCustomMappings = count($templateFields) > 0;

    foreach (sc_contract_keyword_aliases() as $keyword => $field) {
        if (!isset($allowed[$field])) continue;
        if ($hasCustomMappings && !str_starts_with($keyword, '{{')) continue;
        if (sc_contract_text_contains_keyword($text, $keyword)) $required[$field] = true;
    }
    foreach ($templateFields as $row) {
        $keyword = trim((string)($row['keyword_text'] ?? ''));
        $field = (string)($row['subscriber_field'] ?? '');
        if ($keyword !== '' && isset($allowed[$field]) && sc_contract_text_contains_keyword($text, $keyword)) {
            $required[$field] = true;
        }
    }

    $ordered = [];
    foreach (array_keys(sc_contract_field_catalog()) as $field) {
        if (isset($required[$field])) $ordered[] = $field;
    }
    return $ordered;
}

function sc_template_export_catalog(string $templateRelative, array $templateFields = []): array
{
    $path = sc_safe_storage_path($templateRelative);
    $zip = new ZipArchive();
    if (!is_file($path) || $zip->open($path) !== true) {
        throw new RuntimeException('تعذر فحص صفحات قالب Word.');
    }
    try {
        $documentXml = (string)($zip->getFromName('word/document.xml') ?: '');
        if ($documentXml === '') throw new RuntimeException('قالب Word لا يحتوي مستنداً صالحاً.');
        $layout = sc_docx_page_layout($documentXml);
        $declaredCount = sc_docx_declared_page_count((string)($zip->getFromName('docProps/app.xml') ?: ''));
        $sharedText = '';
        for ($i = 0; $i < $zip->numFiles; $i++) {
            $name = (string)$zip->getNameIndex($i);
            if (preg_match('#^word/(header|footer)\d+\.xml$#', $name)) {
                $sharedText .= ' ' . sc_docx_xml_text((string)($zip->getFromName($name) ?: ''));
            }
        }
    } finally {
        $zip->close();
    }

    $layoutCount = max(1, (int)$layout['count']);
    $pageCount = max($layoutCount, $declaredCount);
    $pageSelectable = $declaredCount <= $layoutCount;
    $allText = trim($sharedText . ' ' . implode(' ', $layout['page_texts']));
    $pageRequired = [];
    for ($page = 1; $page <= $layoutCount; $page++) {
        $pageRequired[$page] = sc_template_required_fields_for_text(
            trim($sharedText . ' ' . (string)($layout['page_texts'][$page] ?? '')),
            $templateFields
        );
    }

    $warning = '';
    if (!$pageSelectable) {
        $warning = 'القالب يعلن عن ' . $declaredCount . ' صفحات، لكنه لا يحفظ فواصل صفحات قابلة للفصل. يتاح تصدير العقد كاملاً فقط.';
    }
    return [
        'page_count' => $pageCount,
        'layout_page_count' => $layoutCount,
        'page_selectable' => $pageSelectable,
        'page_required_fields' => $pageRequired,
        'all_required_fields' => sc_template_required_fields_for_text($allText, $templateFields),
        'warning' => $warning,
    ];
}

function sc_contract_missing_fields(array $subscriber, array $requiredFields): array
{
    $map = sc_subscriber_placeholders($subscriber, []);
    $missing = [];
    foreach (array_values(array_unique($requiredFields)) as $field) {
        if (trim((string)($map['{{' . $field . '}}'] ?? '')) === '') $missing[] = $field;
    }
    return $missing;
}

function sc_partition_contract_export_rows(
    array $subscriberRows,
    array $requiredFields,
    bool $excludeIncomplete,
    array $fieldLabels = []
): array {
    $eligible = [];
    $excluded = [];
    foreach ($subscriberRows as $subscriber) {
        if (!is_array($subscriber)) continue;
        $subscriberId = (int)($subscriber['id'] ?? 0);
        if ($subscriberId < 1) continue;

        $missingFields = sc_contract_missing_fields($subscriber, $requiredFields);
        if ($excludeIncomplete && $missingFields) {
            $excluded[] = [
                'id' => $subscriberId,
                'name' => trim((string)($subscriber['full_name'] ?? '')) ?: 'مشترك #' . $subscriberId,
                'fields' => array_map(
                    static fn(string $field): string => (string)($fieldLabels[$field] ?? $field),
                    $missingFields
                ),
            ];
            continue;
        }
        $eligible[] = $subscriber;
    }

    return [
        'eligible' => $eligible,
        'excluded' => $excluded,
        'selected_count' => count($subscriberRows),
        'eligible_count' => count($eligible),
        'excluded_count' => count($excluded),
    ];
}

function sc_contract_export_filter(string $filter): string
{
    $filter = strtolower(trim($filter));
    return in_array($filter, ['all', 'complete', 'incomplete'], true) ? $filter : 'all';
}

function sc_contract_export_max_count(): int
{
    // The bulk exporter processes contracts incrementally and persists its
    // progress, so the former synchronous limit of 300 is no longer needed.
    return 1000;
}

function sc_filter_contract_export_rows(
    array $subscriberRows,
    array $requiredFields,
    string $filter,
    array $fieldLabels = []
): array {
    $filter = sc_contract_export_filter($filter);
    $eligible = [];
    $omitted = [];
    $completeCount = 0;
    $incompleteCount = 0;

    foreach ($subscriberRows as $subscriber) {
        if (!is_array($subscriber)) continue;
        $subscriberId = (int)($subscriber['id'] ?? 0);
        if ($subscriberId < 1) continue;

        $missingFields = sc_contract_missing_fields($subscriber, $requiredFields);
        $isComplete = $missingFields === [];
        if ($isComplete) $completeCount++;
        else $incompleteCount++;

        $matches = $filter === 'all'
            || ($filter === 'complete' && $isComplete)
            || ($filter === 'incomplete' && !$isComplete);

        if ($matches) {
            $eligible[] = $subscriber;
            continue;
        }

        $omitted[] = [
            'id' => $subscriberId,
            'name' => trim((string)($subscriber['full_name'] ?? '')) ?: 'مشترك #' . $subscriberId,
            'fields' => array_map(
                static fn(string $field): string => (string)($fieldLabels[$field] ?? $field),
                $missingFields
            ),
        ];
    }

    return [
        'filter' => $filter,
        'eligible' => $eligible,
        'omitted' => $omitted,
        'selected_count' => $completeCount + $incompleteCount,
        'eligible_count' => count($eligible),
        'omitted_count' => count($omitted),
        'complete_count' => $completeCount,
        'incomplete_count' => $incompleteCount,
    ];
}

function sc_contract_selected_pages(array $catalog, string $scope): ?array
{
    $scope = trim($scope);
    if ($scope === '' || $scope === 'all') return null;
    if (preg_match('/^pages:([0-9,\-\s]+)$/', $scope, $match)) {
        $pages = [];
        foreach (preg_split('/[\s,\-]+/', $match[1], -1, PREG_SPLIT_NO_EMPTY) as $value) {
            $page = (int)$value;
            if ($page > 0) $pages[$page] = $page;
        }
        sort($pages, SORT_NUMERIC);
        $pageCount = (int)($catalog['layout_page_count'] ?? 0);
        if (empty($catalog['page_selectable']) || !$pages || max($pages) > $pageCount) {
            throw new RuntimeException('أرقام الصفحات المطلوبة غير صالحة أو غير موجودة في القالب.');
        }
        return array_values($pages);
    }
    if (!preg_match('/^page:(\d+)$/', $scope, $match)) {
        throw new RuntimeException('نطاق صفحات العقد غير صالح.');
    }
    $page = (int)$match[1];
    if (empty($catalog['page_selectable']) || $page < 1 || $page > (int)($catalog['layout_page_count'] ?? 0)) {
        throw new RuntimeException('لا يمكن فصل الصفحة المحددة لأن القالب لا يحفظ فواصل صفحات موثوقة.');
    }
    return [$page];
}

function sc_contract_filter_required_fields(array $catalog): array
{
    return array_values(array_unique((array)($catalog['all_required_fields'] ?? [])));
}

/**
 * Load the canonical completeness definition used by the contracts page.
 * Consumers outside that page must use this instead of maintaining a second
 * list of "complete subscriber" fields.
 */
function sc_contract_filter_definition(mysqli $conn, int $templateId = 0): array
{
    if ($templateId > 0) {
        $stmt = $conn->prepare('SELECT id,name,file_path,is_default FROM subscriber_contract_templates WHERE id=? AND is_active=1 LIMIT 1');
        if (!$stmt) throw new RuntimeException('تعذر قراءة قالب العقود.');
        $stmt->bind_param('i', $templateId);
        $stmt->execute();
        $template = $stmt->get_result()->fetch_assoc() ?: null;
        $stmt->close();
    } else {
        $result = $conn->query('SELECT id,name,file_path,is_default FROM subscriber_contract_templates WHERE is_active=1 ORDER BY is_default DESC,id DESC LIMIT 1');
        $template = $result ? ($result->fetch_assoc() ?: null) : null;
    }

    if (!$template) {
        return ['template'=>null, 'required_fields'=>[], 'field_labels'=>sc_contract_field_catalog()];
    }

    $resolvedTemplateId = (int)$template['id'];
    $templateFields = sc_template_field_rows($conn, $resolvedTemplateId);
    $catalog = sc_template_export_catalog((string)$template['file_path'], $templateFields);
    return [
        'template' => $template,
        'required_fields' => sc_contract_filter_required_fields($catalog),
        'field_labels' => sc_contract_field_catalog(),
    ];
}

function sc_contract_render_required_fields(array $catalog, ?array $selectedPages): array
{
    if ($selectedPages === null) {
        return sc_contract_filter_required_fields($catalog);
    }
    $fields = [];
    foreach ($selectedPages as $page) {
        $fields = array_merge($fields, (array)($catalog['page_required_fields'][max(0, (int)$page)] ?? []));
    }
    return array_values(array_unique($fields));
}

if (!function_exists('sc_setting_get')) {
    function sc_setting_get(mysqli $conn, string $key, string $default = ''): string
    {
        $stmt = $conn->prepare('SELECT setting_value FROM subscriber_contract_settings WHERE setting_key=? LIMIT 1');
        if (!$stmt) return $default;
        $stmt->bind_param('s', $key);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return $row ? (string)($row['setting_value'] ?? '') : $default;
    }
}

if (!function_exists('sc_setting_set')) {
    function sc_setting_set(mysqli $conn, string $key, string $value): void
    {
        $stmt = $conn->prepare('INSERT INTO subscriber_contract_settings(setting_key,setting_value) VALUES(?,?) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value)');
        if (!$stmt) throw new RuntimeException('تعذر حفظ إعدادات العقود.');
        $stmt->bind_param('ss', $key, $value);
        if (!$stmt->execute()) throw new RuntimeException('تعذر حفظ إعدادات العقود.');
        $stmt->close();
    }
}

if (!function_exists('sc_safe_storage_path')) {
    function sc_safe_storage_path(string $relative): string
    {
        $relative = str_replace(['\\', "\0"], ['/', ''], trim($relative));
        if ($relative === '' || str_contains($relative, '..') || str_starts_with($relative, '/')) {
            throw new RuntimeException('مسار ملف العقد غير صالح.');
        }
        return sc_contract_root() . DIRECTORY_SEPARATOR . str_replace('/', DIRECTORY_SEPARATOR, $relative);
    }
}

if (!function_exists('sc_upload_docx')) {
    function sc_upload_docx(array $file): array
    {
        if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) throw new RuntimeException('اختر ملف Word بصيغة DOCX.');
        if ((int)($file['size'] ?? 0) <= 0 || (int)$file['size'] > 12 * 1024 * 1024) throw new RuntimeException('حجم قالب Word يجب ألا يتجاوز 12 MB.');
        $original = trim((string)($file['name'] ?? 'template.docx'));
        if (strtolower(pathinfo($original, PATHINFO_EXTENSION)) !== 'docx') throw new RuntimeException('القوالب المقبولة هي ملفات DOCX فقط.');
        $probe = new ZipArchive();
        if ($probe->open((string)$file['tmp_name']) !== true || $probe->locateName('word/document.xml') === false) {
            if ($probe instanceof ZipArchive) $probe->close();
            throw new RuntimeException('ملف Word غير صالح أو تالف.');
        }
        $probe->close();
        $relative = 'templates/' . date('Ymd-His') . '-' . bin2hex(random_bytes(6)) . '.docx';
        $target = sc_safe_storage_path($relative);
        if (!@move_uploaded_file((string)$file['tmp_name'], $target)) throw new RuntimeException('تعذر حفظ قالب Word.');
        return [$relative, $original];
    }
}

if (!function_exists('sc_upload_image')) {
    function sc_upload_image(array $file, string $kind): string
    {
        if (($file['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) return '';
        if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) throw new RuntimeException('تعذر رفع صورة ' . $kind . '.');
        if ((int)($file['size'] ?? 0) > 4 * 1024 * 1024) throw new RuntimeException('حجم الصورة يجب ألا يتجاوز 4 MB.');
        $info = @getimagesize((string)$file['tmp_name']);
        if (!$info || !in_array((int)$info[2], [IMAGETYPE_PNG, IMAGETYPE_JPEG], true)) throw new RuntimeException('الصورة يجب أن تكون PNG أو JPG.');
        $ext = ((int)$info[2] === IMAGETYPE_PNG) ? 'png' : 'jpg';
        $relative = 'assets/' . preg_replace('/[^a-z0-9_-]+/i', '-', $kind) . '-' . bin2hex(random_bytes(6)) . '.' . $ext;
        if (!@move_uploaded_file((string)$file['tmp_name'], sc_safe_storage_path($relative))) throw new RuntimeException('تعذر حفظ الصورة.');
        return $relative;
    }
}

if (!function_exists('sc_event')) {
    function sc_event(mysqli $conn, ?int $contractId, int $subscriberId, string $type, string $note = ''): void
    {
        $actor = (string)($_SESSION['admin_username'] ?? $_SESSION['admin_user'] ?? 'admin');
        $stmt = $conn->prepare('INSERT INTO subscriber_contract_events(contract_id,subscriber_id,event_type,event_note,actor) VALUES(?,?,?,?,?)');
        if (!$stmt) return;
        $stmt->bind_param('iisss', $contractId, $subscriberId, $type, $note, $actor);
        @$stmt->execute();
        $stmt->close();
    }
}

if (!function_exists('sc_contract_number')) {
    function sc_contract_number(mysqli $conn, int $subscriberId): string
    {
        $prefix = 'CTR-' . date('Ym') . '-' . max(1, $subscriberId) . '-';
        for ($i = 1; $i <= 999; $i++) {
            $candidate = $prefix . str_pad((string)$i, 3, '0', STR_PAD_LEFT);
            $esc = $conn->real_escape_string($candidate);
            $q = $conn->query("SELECT 1 FROM subscriber_contracts WHERE contract_number='$esc' LIMIT 1");
            if (!$q || $q->num_rows === 0) return $candidate;
        }
        return $prefix . bin2hex(random_bytes(3));
    }
}

if (!function_exists('sc_subscriber_placeholders')) {
    function sc_subscriber_placeholders(array $sub, array $extra): array
    {
        $fullName = trim((string)($sub['full_name'] ?? ''));
        if ($fullName === '') $fullName = trim((string)($sub['first_name'] ?? '') . ' ' . (string)($sub['father_name'] ?? '') . ' ' . (string)($sub['last_name'] ?? ''));
        $pppUser = trim((string)($sub['ppp_username'] ?? $sub['username'] ?? ''));
        $pppPass = trim((string)($sub['ppp_password'] ?? $sub['password_plain'] ?? ''));
        $map = [
            '{{company_name}}' => (string)($extra['company_name'] ?? 'الشركة'),
            '{{contract_number}}' => (string)($extra['contract_number'] ?? ''),
            '{{contract_date}}' => (string)($extra['contract_date'] ?? date('Y-m-d')),
            '{{contract_title}}' => (string)($extra['contract_title'] ?? 'عقد تقديم خدمة إنترنت'),
            '{{contract_notes}}' => (string)($extra['contract_notes'] ?? ''),
            '{{subscriber_id}}' => (string)($sub['id'] ?? ''),
            '{{full_name}}' => $fullName,
            '{{first_name}}' => (string)($sub['first_name'] ?? ''),
            '{{father_name}}' => (string)($sub['father_name'] ?? ''),
            '{{mother_name}}' => (string)($sub['mother_name'] ?? ''),
            '{{last_name}}' => (string)($sub['last_name'] ?? ''),
            '{{birth_date}}' => (string)($sub['birth_date'] ?? ''),
            '{{nationality}}' => (string)($sub['nationality'] ?? ''),
            '{{document_type}}' => (string)($sub['document_type'] ?? ''),
            '{{national_id}}' => (string)($sub['national_id'] ?? ''),
            '{{document_number}}' => (string)($sub['document_number'] ?? ''),
            '{{record_number}}' => (string)($sub['record_number'] ?? ''),
            '{{phone}}' => (string)($sub['phone'] ?? ''),
            '{{address}}' => (string)($sub['address_area'] ?? $sub['address'] ?? ''),
            '{{pppoe_username}}' => $pppUser,
            '{{pppoe_password}}' => $pppPass,
            '{{speed}}' => (string)($sub['speed_name'] ?? $sub['speed'] ?? ''),
            '{{package}}' => (string)($sub['package_name'] ?? $sub['package'] ?? ''),
            '{{static_ip}}' => (string)($sub['ip_address'] ?? ''),
            '{{sector}}' => (string)($sub['sector_name'] ?? $sub['sector_id'] ?? ''),
            '{{email}}' => (string)($sub['email'] ?? ''),
            '{{notes}}' => (string)($sub['notes'] ?? ''),
            '{{vlan_id}}' => (string)($sub['vlan_id'] ?? ''),
            '{{pppoe_profile}}' => (string)($sub['pppoe_profile'] ?? $sub['ppp_profile'] ?? ''),
            '{{quota_gb}}' => (string)($sub['quota_gb'] ?? ''),
            '{{current_ip}}' => (string)($sub['current_ip'] ?? $sub['ip_address'] ?? ''),
            '{{current_mac}}' => (string)($sub['current_caller_id'] ?? $sub['last_caller_id'] ?? ''),
            '{{device_name}}' => (string)($sub['airmax_device_name'] ?? ''),
            '{{subscriber_created_at}}' => (string)($sub['created_at'] ?? ''),
            '{{generated_at}}' => date('Y-m-d H:i:s'),
        ];
        $labelValues = [
            'الاسم الكامل:' => $fullName,
            'اسم المشترك:' => $fullName,
            'اسم الفريق الثاني:' => $fullName,
            'اسم الأب:' => (string)($sub['father_name'] ?? ''),
            'اسم الأم:' => (string)($sub['mother_name'] ?? ''),
            'تاريخ الميلاد:' => (string)($sub['birth_date'] ?? ''),
            'الجنسية:' => (string)($sub['nationality'] ?? ''),
            'نوع الوثيقة:' => (string)($sub['document_type'] ?? ''),
            'نوع وثيقة الثبوتيات:' => (string)($sub['document_type'] ?? ''),
            'رقم الوثيقة:' => (string)($sub['document_number'] ?? ''),
            'الرقم الوطني:' => (string)($sub['national_id'] ?? ''),
            'رقم القيد:' => (string)($sub['record_number'] ?? ''),
            'رقم التواصل:' => (string)($sub['phone'] ?? ''),
            'رقم الهاتف:' => (string)($sub['phone'] ?? ''),
            'العنوان:' => (string)($sub['address_area'] ?? $sub['address'] ?? ''),
            'اسم المستخدم:' => $pppUser,
            'حساب الخدمة:' => $pppUser,
            'كلمة المرور:' => $pppPass,
            'عنوان IP:' => (string)($sub['ip_address'] ?? ''),
            'البريد الإلكتروني:' => (string)($sub['email'] ?? ''),
            'القطاع:' => (string)($sub['sector_name'] ?? $sub['sector_id'] ?? ''),
            'VLAN:' => (string)($sub['vlan_id'] ?? ''),
            'ملف PPPoE:' => (string)($sub['pppoe_profile'] ?? $sub['ppp_profile'] ?? ''),
            'الحصة:' => (string)($sub['quota_gb'] ?? ''),
            'عنوان MAC:' => (string)($sub['current_caller_id'] ?? $sub['last_caller_id'] ?? ''),
            'الجهاز:' => (string)($sub['airmax_device_name'] ?? ''),
            'ملاحظات المشترك:' => (string)($sub['notes'] ?? ''),
            'الباقة المختارة:' => (string)($sub['package_name'] ?? $sub['package'] ?? ''),
            'السرعة المختارة:' => (string)($sub['speed_name'] ?? $sub['speed'] ?? ''),
            'رقم العقد:' => (string)($extra['contract_number'] ?? ''),
            'تاريخ العقد:' => (string)($extra['contract_date'] ?? date('Y-m-d')),
        ];
        foreach ($labelValues as $label => $value) {
            $value = trim($value);
            if ($value !== '') {
                foreach (sc_contract_keyword_variants($label) as $variant) {
                    $map[$variant] = $variant . ' ' . $value;
                }
            }
        }
        return $map;
    }
}

if (!function_exists('sc_replace_docx_xml')) {
    function sc_docx_paragraph_edits(string $text, array $map): array
    {
        $keys = array_values(array_filter(array_keys($map), static fn($key): bool => (string)$key !== ''));
        usort($keys, static fn($left, $right): int => strlen((string)$right) <=> strlen((string)$left));
        $edits = [];
        $length = strlen($text);
        for ($offset = 0; $offset < $length;) {
            $matched = false;
            foreach ($keys as $key) {
                $key = (string)$key;
                $keyLength = strlen($key);
                if ($keyLength > 0 && substr($text, $offset, $keyLength) === $key) {
                    $replacement = (string)$map[$key];
                    $advanceLength = $keyLength;
                    if ($replacement !== $key) {
                        $editLength = $keyLength;
                        $growth = max(0, mb_strlen($replacement, 'UTF-8') - mb_strlen($key, 'UTF-8'));
                        if ($growth > 0) {
                            $tail = substr($text, $offset + $keyLength);
                            if (preg_match('/^[\x20\t\x{00A0}]+/u', $tail, $spaceMatch)) {
                                $reserved = (string)$spaceMatch[0];
                                // Keep one separator before the next visible value.
                                $consume = min($growth, max(0, mb_strlen($reserved, 'UTF-8') - 1));
                                if ($consume > 0) {
                                    $editLength += strlen(mb_substr($reserved, 0, $consume, 'UTF-8'));
                                }
                            }
                        }
                        $edits[] = ['start'=>$offset, 'length'=>$editLength, 'replacement'=>$replacement];
                        $advanceLength = $editLength;
                    }
                    $offset += $advanceLength;
                    $matched = true;
                    break;
                }
            }
            if (!$matched) $offset++;
        }
        return $edits;
    }

    function sc_docx_apply_paragraph_edit(array $nodes, int $start, int $length, string $replacement): void
    {
        $cursor = 0;
        $startIndex = null;
        $endIndex = null;
        $startOffset = 0;
        $endOffset = 0;
        $end = $start + $length;
        foreach ($nodes as $index => $node) {
            $nodeText = (string)$node->textContent;
            $nodeEnd = $cursor + strlen($nodeText);
            if ($startIndex === null && $start >= $cursor && $start < $nodeEnd) {
                $startIndex = $index;
                $startOffset = $start - $cursor;
            }
            if ($endIndex === null && $end > $cursor && $end <= $nodeEnd) {
                $endIndex = $index;
                $endOffset = $end - $cursor;
                break;
            }
            $cursor = $nodeEnd;
        }
        if ($startIndex === null || $endIndex === null) return;

        $startText = (string)$nodes[$startIndex]->textContent;
        if ($startIndex === $endIndex) {
            $nodes[$startIndex]->nodeValue = substr($startText, 0, $startOffset)
                . $replacement
                . substr($startText, $endOffset);
            return;
        }

        $endText = (string)$nodes[$endIndex]->textContent;
        $nodes[$startIndex]->nodeValue = substr($startText, 0, $startOffset) . $replacement;
        for ($index = $startIndex + 1; $index < $endIndex; $index++) {
            $nodes[$index]->nodeValue = '';
        }
        $nodes[$endIndex]->nodeValue = substr($endText, $endOffset);
    }

    function sc_replace_docx_xml(string $xml, array $map): string
    {
        $dom = new DOMDocument('1.0', 'UTF-8');
        $dom->preserveWhiteSpace = true;
        if (!@$dom->loadXML($xml, LIBXML_NONET | LIBXML_COMPACT)) return strtr($xml, $map);
        $xp = new DOMXPath($dom);
        $xp->registerNamespace('w', 'http://schemas.openxmlformats.org/wordprocessingml/2006/main');
        foreach ($xp->query('//w:p') as $paragraph) {
            $nodeList = $xp->query('.//w:t', $paragraph);
            if (!$nodeList || $nodeList->length === 0) continue;
            $nodes = iterator_to_array($nodeList);
            $text = '';
            foreach ($nodes as $node) {
                $normalized = str_replace("\u{00FF}", '☐', (string)$node->textContent);
                if ($normalized !== $node->textContent) $node->nodeValue = $normalized;
                $text .= $normalized;
            }
            // Word Symbol/Wingdings checkbox markers can surface as U+00FF
            // after OOXML parsing. Normalize them to a portable Unicode box.
            $edits = sc_docx_paragraph_edits($text, $map);
            for ($index = count($edits) - 1; $index >= 0; $index--) {
                $edit = $edits[$index];
                sc_docx_apply_paragraph_edit(
                    $nodes,
                    (int)$edit['start'],
                    (int)$edit['length'],
                    (string)$edit['replacement']
                );
            }
        }
        return (string)$dom->saveXML();
    }
}

if (!function_exists('sc_keep_docx_pages')) {
    function sc_keep_docx_pages(string $xml, array $selectedPages): string
    {
        $selected = [];
        foreach ($selectedPages as $page) {
            $page = (int)$page;
            if ($page > 0) $selected[$page] = true;
        }
        if (!$selected) throw new RuntimeException('اختر صفحة واحدة على الأقل من قالب Word.');

        $dom = new DOMDocument('1.0', 'UTF-8');
        $dom->preserveWhiteSpace = true;
        if (!@$dom->loadXML($xml, LIBXML_NONET | LIBXML_COMPACT)) {
            throw new RuntimeException('تعذر تحليل صفحات قالب Word.');
        }
        $layout = sc_docx_page_layout_dom($dom);
        $body = $layout['body'];
        $xp = $layout['xpath'];
        $pageCount = (int)$layout['count'];
        foreach (array_keys($selected) as $page) {
            if ($page > $pageCount) throw new RuntimeException('الصفحة المحددة غير موجودة ضمن فواصل القالب المحفوظة.');
        }

        // Empty spacer paragraphs immediately before a persisted page boundary
        // are useful in the full template, but make an extracted page overflow
        // after subscriber values are filled. Trim only the trailing empty
        // blocks of each selected page; never remove its visible content.
        $trimNodes = [];
        foreach (array_keys($selected) as $selectedPage) {
            $pageEntries = array_values(array_filter(
                $layout['nodes'],
                static fn(array $entry): bool => (int)$entry['page'] === (int)$selectedPage
            ));
            for ($index = count($pageEntries) - 1; $index >= 0; $index--) {
                $candidate = $pageEntries[$index]['node'];
                if (sc_docx_node_has_renderable_content($candidate)) break;
                $trimNodes[spl_object_id($candidate)] = true;
            }
        }

        foreach ($layout['nodes'] as $entry) {
            $node = $entry['node'];
            $page = (int)$entry['page'];
            if (!isset($selected[$page]) || isset($trimNodes[spl_object_id($node)])) {
                $body->removeChild($node);
                continue;
            }

            if (!isset($selected[$page - 1])) {
                foreach (iterator_to_array($xp->query('.//w:pPr/w:pageBreakBefore', $node)) as $breakNode) {
                    $breakNode->parentNode?->removeChild($breakNode);
                }
            }
            if (!isset($selected[$page + 1])) {
                foreach (iterator_to_array($xp->query('.//w:lastRenderedPageBreak | .//w:br[@w:type="page"]', $node)) as $breakNode) {
                    $breakNode->parentNode?->removeChild($breakNode);
                }
                foreach (iterator_to_array($xp->query('.//w:pPr/w:sectPr', $node)) as $section) {
                    $type = $xp->query('./w:type', $section)->item(0);
                    if (!($type instanceof DOMElement)) {
                        $type = $dom->createElementNS(
                            'http://schemas.openxmlformats.org/wordprocessingml/2006/main',
                            'w:type'
                        );
                        $section->appendChild($type);
                    }
                    $type->setAttributeNS(
                        'http://schemas.openxmlformats.org/wordprocessingml/2006/main',
                        'w:val',
                        'continuous'
                    );
                }
            }
        }
        return (string)$dom->saveXML();
    }
}

if (!function_exists('sc_keep_changed_docx_pages')) {
    function sc_keep_changed_docx_pages(string $xml, array $map): string
    {
        $dom = new DOMDocument('1.0', 'UTF-8');
        $dom->preserveWhiteSpace = true;
        if (!@$dom->loadXML($xml, LIBXML_NONET | LIBXML_COMPACT)) throw new RuntimeException('تعذر تحليل صفحات قالب Word.');
        $layout = sc_docx_page_layout_dom($dom);
        $xp = $layout['xpath'];
        $body = $layout['body'];
        $changedPages = [];
        foreach ($layout['nodes'] as $entry) {
            $node = $entry['node'];
            $page = (int)$entry['page'];
            $blockText = '';
            foreach ($xp->query('.//w:t', $node) as $textNode) $blockText .= $textNode->textContent;
            foreach ($map as $keyword => $replacement) {
                if ($keyword !== '' && $replacement !== $keyword && str_contains($blockText, (string)$keyword)) {
                    $changedPages[$page] = true;
                    break;
                }
            }
        }
        if (!$changedPages) throw new RuntimeException('لم يعثر Word على صفحات تحتوي كلمات تمت تعبئتها في هذا القالب.');
        foreach ($layout['nodes'] as $entry) {
            if (empty($changedPages[(int)$entry['page']])) $body->removeChild($entry['node']);
        }
        return (string)$dom->saveXML();
    }
}

if (!function_exists('sc_docx_embed_images')) {
    function sc_docx_embed_images(ZipArchive $zip, string $documentXml, array $images): string
    {
        if (!$images) return $documentXml;
        $relsName = 'word/_rels/document.xml.rels';
        $relsXml = $zip->getFromName($relsName);
        if ($relsXml === false) {
            $relsXml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"></Relationships>';
        }
        $relsDom = new DOMDocument('1.0', 'UTF-8');
        $relsDom->preserveWhiteSpace = true;
        if (!@$relsDom->loadXML($relsXml, LIBXML_NONET | LIBXML_COMPACT)) return $documentXml;

        $contentTypesXml = $zip->getFromName('[Content_Types].xml');
        $typesDom = new DOMDocument('1.0', 'UTF-8');
        if ($contentTypesXml === false || !@$typesDom->loadXML($contentTypesXml, LIBXML_NONET | LIBXML_COMPACT)) return $documentXml;

        $dom = new DOMDocument('1.0', 'UTF-8');
        $dom->preserveWhiteSpace = true;
        if (!@$dom->loadXML($documentXml, LIBXML_NONET | LIBXML_COMPACT)) return $documentXml;
        $xp = new DOMXPath($dom);
        $xp->registerNamespace('w', 'http://schemas.openxmlformats.org/wordprocessingml/2006/main');

        $docPrId = 8100;
        foreach ($images as $marker => $relative) {
            if ($relative === '') continue;
            $path = sc_safe_storage_path($relative);
            if (!is_file($path)) continue;
            $info = @getimagesize($path);
            if (!$info || (int)$info[0] <= 0 || (int)$info[1] <= 0) continue;
            $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));
            if ($ext === 'jpeg') $ext = 'jpg';
            if (!in_array($ext, ['png', 'jpg'], true)) continue;
            $key = str_contains($marker, 'stamp') ? 'stamp' : 'signature';
            $mediaName = 'contract-' . $key . '-' . bin2hex(random_bytes(4)) . '.' . $ext;
            $zip->addFile($path, 'word/media/' . $mediaName);
            $rid = 'rIdContract' . ucfirst($key) . bin2hex(random_bytes(3));

            $rel = $relsDom->createElementNS('http://schemas.openxmlformats.org/package/2006/relationships', 'Relationship');
            $rel->setAttribute('Id', $rid);
            $rel->setAttribute('Type', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/image');
            $rel->setAttribute('Target', 'media/' . $mediaName);
            $relsDom->documentElement->appendChild($rel);

            $typesXp = new DOMXPath($typesDom);
            $typesXp->registerNamespace('ct', 'http://schemas.openxmlformats.org/package/2006/content-types');
            if ($typesXp->query('/ct:Types/ct:Default[@Extension="' . $ext . '"]')->length === 0) {
                $def = $typesDom->createElementNS('http://schemas.openxmlformats.org/package/2006/content-types', 'Default');
                $def->setAttribute('Extension', $ext);
                $def->setAttribute('ContentType', $ext === 'png' ? 'image/png' : 'image/jpeg');
                $typesDom->documentElement->appendChild($def);
            }

            $maxWidthIn = $key === 'stamp' ? 1.25 : 1.65;
            $widthEmu = (int)round($maxWidthIn * 914400);
            $heightEmu = (int)round($widthEmu * ((int)$info[1] / (int)$info[0]));
            $maxHeightEmu = (int)round(0.85 * 914400);
            if ($heightEmu > $maxHeightEmu) {
                $widthEmu = (int)round($widthEmu * ($maxHeightEmu / $heightEmu));
                $heightEmu = $maxHeightEmu;
            }

            foreach ($xp->query('//w:p') as $paragraph) {
                $textNodes = $xp->query('.//w:t', $paragraph);
                if (!$textNodes || $textNodes->length === 0) continue;
                $text = '';
                foreach ($textNodes as $node) $text .= $node->textContent;
                if (!str_contains($text, $marker)) continue;
                $newText = str_replace($marker, '', $text);
                $textNodes->item(0)->nodeValue = $newText;
                for ($i = 1; $i < $textNodes->length; $i++) $textNodes->item($i)->nodeValue = '';
                $drawing = '<w:r xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:pic="http://schemas.openxmlformats.org/drawingml/2006/picture"><w:drawing><wp:inline distT="0" distB="0" distL="0" distR="0"><wp:extent cx="' . $widthEmu . '" cy="' . $heightEmu . '"/><wp:effectExtent l="0" t="0" r="0" b="0"/><wp:docPr id="' . (++$docPrId) . '" name="' . htmlspecialchars($key, ENT_XML1) . '"/><wp:cNvGraphicFramePr><a:graphicFrameLocks noChangeAspect="1"/></wp:cNvGraphicFramePr><a:graphic><a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/picture"><pic:pic><pic:nvPicPr><pic:cNvPr id="0" name="' . htmlspecialchars($mediaName, ENT_XML1) . '"/><pic:cNvPicPr/></pic:nvPicPr><pic:blipFill><a:blip r:embed="' . htmlspecialchars($rid, ENT_XML1) . '"/><a:stretch><a:fillRect/></a:stretch></pic:blipFill><pic:spPr><a:xfrm><a:off x="0" y="0"/><a:ext cx="' . $widthEmu . '" cy="' . $heightEmu . '"/></a:xfrm><a:prstGeom prst="rect"><a:avLst/></a:prstGeom></pic:spPr></pic:pic></a:graphicData></a:graphic></wp:inline></w:drawing></w:r>';
                $fragment = $dom->createDocumentFragment();
                if (@$fragment->appendXML($drawing)) $paragraph->appendChild($fragment);
                break;
            }
        }

        $zip->addFromString($relsName, (string)$relsDom->saveXML());
        $zip->addFromString('[Content_Types].xml', (string)$typesDom->saveXML());
        return (string)$dom->saveXML();
    }
}

if (!function_exists('sc_generate_docx')) {
    function sc_generate_docx(
        string $templateRelative,
        string $outputRelative,
        array $map,
        array $images = [],
        ?array $verificationValues = null,
        bool $changedPagesOnly = false,
        ?array $selectedPages = null
    ): void
    {
        $template = sc_safe_storage_path($templateRelative);
        $output = sc_safe_storage_path($outputRelative);
        if (!is_file($template)) throw new RuntimeException('قالب العقد غير موجود.');
        if (!@copy($template, $output)) throw new RuntimeException('تعذر إنشاء نسخة العقد.');
        $zip = new ZipArchive();
        if ($zip->open($output) !== true) throw new RuntimeException('تعذر فتح ملف العقد الناتج.');
        $targets = [];
        for ($i = 0; $i < $zip->numFiles; $i++) {
            $name = (string)$zip->getNameIndex($i);
            if ($name === 'word/document.xml' || preg_match('#^word/(header|footer)\d+\.xml$#', $name)) $targets[] = $name;
        }
        $generatedDocumentXml = '';
        foreach ($targets as $name) {
            $xml = $zip->getFromName($name);
            if ($xml !== false) {
                if ($name === 'word/document.xml' && $selectedPages !== null) {
                    $xml = sc_keep_docx_pages($xml, $selectedPages);
                } elseif ($name === 'word/document.xml' && $changedPagesOnly) {
                    $xml = sc_keep_changed_docx_pages($xml, $map);
                }
                if ($name === 'word/document.xml' && $images) $xml = sc_docx_embed_images($zip, $xml, $images);
                $replacedXml = sc_replace_docx_xml($xml, $map);
                $zip->addFromString($name, $replacedXml);
                if ($name === 'word/document.xml') $generatedDocumentXml = $replacedXml;
            }
        }
        $requiredValues = $verificationValues ?? [
            'اسم المشترك' => trim((string)($map['{{full_name}}'] ?? '')),
            'رقم التواصل' => trim((string)($map['{{phone}}'] ?? '')),
        ];
        foreach ($requiredValues as $label => $value) {
            if ($value !== '' && !str_contains($generatedDocumentXml, htmlspecialchars($value, ENT_XML1 | ENT_QUOTES, 'UTF-8')) && !str_contains($generatedDocumentXml, $value)) {
                $zip->close();
                @unlink($output);
                throw new RuntimeException('فشل التحقق من تعبئة ' . $label . ' داخل القالب. تأكد أن القالب يحتوي المفتاح المناسب.');
            }
        }
        if ($selectedPages !== null) sc_docx_set_declared_page_count($zip, count(array_unique($selectedPages)));
        $zip->close();
    }
}

if (!function_exists('sc_mark_printed')) {
    function sc_mark_printed(mysqli $conn, int $contractId): bool
    {
        $stmt = $conn->prepare('UPDATE subscriber_contracts SET printed_count=printed_count+1, first_printed_at=COALESCE(first_printed_at,NOW()), last_printed_at=NOW() WHERE id=? LIMIT 1');
        if (!$stmt) return false;
        $stmt->bind_param('i', $contractId);
        $ok = $stmt->execute() && $stmt->affected_rows > 0;
        $stmt->close();
        if ($ok) {
            $row = $conn->query('SELECT subscriber_id FROM subscriber_contracts WHERE id=' . $contractId . ' LIMIT 1');
            $sid = ($row && ($r = $row->fetch_assoc())) ? (int)$r['subscriber_id'] : 0;
            sc_event($conn, $contractId, $sid, 'PRINT', 'تم تسجيل طباعة العقد.');
        }
        return $ok;
    }
}

if (!function_exists('sc_default_template')) {
    function sc_default_template(mysqli $conn): ?array {
        sc_ensure_schema($conn);
        $rs = @$conn->query("SELECT * FROM subscriber_contract_templates WHERE is_active=1 ORDER BY is_default DESC,id DESC LIMIT 1");
        return ($rs && $rs->num_rows) ? $rs->fetch_assoc() : null;
    }
}

if (!function_exists('sc_resolve_contract_template')) {
    /**
     * Resolve a template selected by a possibly stale browser page.
     * Long-lived subscriber tabs can keep an id after a template is replaced;
     * in that case continue with the current approved default instead of
     * rejecting an otherwise valid contract export.
     */
    function sc_resolve_contract_template(mysqli $conn, ?int $templateId = null): ?array {
        sc_ensure_schema($conn);
        $templateId = max(0, (int)$templateId);
        if ($templateId > 0) {
            $stmt = $conn->prepare("SELECT * FROM subscriber_contract_templates WHERE id=? AND is_active=1 LIMIT 1");
            if ($stmt) {
                $stmt->bind_param('i', $templateId);
                $stmt->execute();
                $template = $stmt->get_result()->fetch_assoc() ?: null;
                $stmt->close();
                if ($template) return $template;
            }
        }
        return sc_default_template($conn);
    }
}

if (!function_exists('sc_generate_subscriber_contract_record')) {
    function sc_generate_subscriber_contract_record(mysqli $conn, int $subscriberId, string $mode = 'full', string $actor = 'admin', ?int $templateId = null): array {
        sc_ensure_schema($conn);
        $mode = strtolower(trim($mode));
        if (!in_array($mode, ['full','changed'], true)) return ['ok'=>false,'error'=>'نمط تصدير العقد غير صالح.'];
        if ($subscriberId <= 0) return ['ok'=>false,'error'=>'المشترك غير صالح.'];

        $stmt = $conn->prepare("SELECT s.*,COALESCE(sec.name,CAST(s.sector_id AS CHAR),'') sector_name
          FROM subscribers s LEFT JOIN sectors sec ON sec.id=s.sector_id
          WHERE s.id=? AND COALESCE(s.is_deleted,0)=0 LIMIT 1");
        if (!$stmt) return ['ok'=>false,'error'=>'تعذر قراءة بيانات المشترك.'];
        $stmt->bind_param('i', $subscriberId);
        $stmt->execute();
        $subscriber = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        if (!$subscriber) return ['ok'=>false,'error'=>'المشترك المحدد غير موجود.'];

        $template = sc_resolve_contract_template($conn, $templateId);
        if (!$template) return ['ok'=>false,'error'=>'لا يوجد قالب عقد معتمد في النسخة الرئيسية.'];

        try {
            $templateId = (int)$template['id'];
            $contractNumber = sc_contract_number($conn, $subscriberId);
            $title = 'عقد تقديم خدمة إنترنت';
            $notes = '';
            $companyName = sc_setting_get($conn, 'company_name', 'الشركة');
            $map = sc_subscriber_placeholders($subscriber, [
                'company_name'=>$companyName,
                'contract_number'=>$contractNumber,
                'contract_date'=>date('Y-m-d'),
                'contract_title'=>$title,
                'contract_notes'=>$notes,
            ]);
            $templateFields = sc_template_field_rows($conn, $templateId);
            $map = sc_apply_template_fields($map, $templateFields);
            $map['{{company_stamp}}'] = '';
            $map['{{company_signature}}'] = '';

            $images = [];
            $stamp = sc_setting_get($conn, 'company_stamp');
            $signature = sc_setting_get($conn, 'company_signature');
            if ($stamp !== '') $images['{{company_stamp}}'] = $stamp;
            if ($signature !== '') $images['{{company_signature}}'] = $signature;

            $relativeDir = 'generated/' . date('Y/m');
            $directory = sc_safe_storage_path($relativeDir);
            if (!is_dir($directory) && !@mkdir($directory, 0770, true) && !is_dir($directory)) {
                throw new RuntimeException('تعذر تجهيز مجلد العقود.');
            }
            $outputRelative = $relativeDir . '/contract-agent-' . $subscriberId . '-' . bin2hex(random_bytes(5)) . '.docx';
            $verificationValues = [];
            foreach ($templateFields as $fieldRow) {
                $value = trim((string)($map['{{'.(string)$fieldRow['subscriber_field'].'}}'] ?? ''));
                if ($value !== '') $verificationValues[] = $value;
            }
            sc_generate_docx(
                (string)$template['file_path'],
                $outputRelative,
                $map,
                $images,
                $templateFields ? array_values(array_unique($verificationValues)) : null,
                $mode === 'changed'
            );

            $filename = 'عقد-' . preg_replace('/[^\p{L}\p{N}._-]+/u', '-', trim((string)($subscriber['full_name'] ?? $subscriberId))) . '-' . $contractNumber . '.docx';
            $stampInt = $stamp !== '' ? 1 : 0;
            $signatureInt = $signature !== '' ? 1 : 0;
            $actor = mb_substr(trim($actor) !== '' ? trim($actor) : 'agent', 0, 190, 'UTF-8');
            $stmt = $conn->prepare("INSERT INTO subscriber_contracts
              (subscriber_id,template_id,contract_number,title,custom_notes,generated_path,generated_filename,has_stamp,has_signature,created_by)
              VALUES(?,?,?,?,?,?,?,?,?,?)");
            $stmt->bind_param('iisssssiis', $subscriberId,$templateId,$contractNumber,$title,$notes,$outputRelative,$filename,$stampInt,$signatureInt,$actor);
            if (!$stmt->execute()) throw new RuntimeException('تعذر إنشاء سجل العقد.');
            $contractId = (int)$stmt->insert_id;
            $stmt->close();
            sc_event($conn, $contractId, $subscriberId, 'AGENT_GENERATE', $mode === 'changed' ? 'تصدير الصفحات المعبأة فقط عبر واجهة المعتمد' : 'تصدير العقد الكامل عبر واجهة المعتمد');

            return [
                'ok'=>true,
                'contract_id'=>$contractId,
                'path'=>$outputRelative,
                'absolute_path'=>sc_safe_storage_path($outputRelative),
                'filename'=>$filename,
                'template_id'=>$templateId,
                'template_name'=>(string)$template['name'],
                'mode'=>$mode,
                'error'=>'',
            ];
        } catch (Throwable $e) {
            return ['ok'=>false,'error'=>$e->getMessage()];
        }
    }
}
