<?php
// app/helpers.php
require_once __DIR__ . '/db.php';

use App\Core\Auth;
use App\Core\Authorization;
use App\Core\Csrf;
use App\Core\Flash;
use App\Core\Redirect;
use App\Core\Session;
use App\Core\Url;
use App\Core\View;
use App\Core\SchemaInspector;
use App\Shared\Support\Format;
use App\Shared\Support\AppConfigSupport;
use App\Shared\Support\FlashSupport;
use App\Shared\Support\Html;
use App\Shared\Support\InventoryLookup;
use App\Shared\Support\ReferenceLabel;
use App\Shared\Support\QuotationSupport;
use App\Shared\Support\ChecksSupport;
use App\Shared\Support\CompanySupport;
use App\Shared\Support\BackupSupport;
use App\Shared\Support\PrintSupport;
use App\Shared\Support\AuditSupport;
use App\Shared\Security\RbacSupport;
use App\Shared\Finance\AccountBalanceSupport;
use App\Shared\Legacy\CustomerSupport;
use App\Shared\Parties\PartyLedgerSupport;
use App\Shared\Schema\ChecksSchemaSupport;
use App\Shared\Schema\FinanceSchemaSupport;
use App\Shared\Schema\PartySchemaSupport;
use App\Shared\Schema\QuotationSchemaSupport;
use App\Shared\Schema\SchemaSupport;
use App\Shared\Schema\SupplierLedgerSchemaSupport;
use App\Services\LabelDefinitionService;

/**
 * Read values from app/config.php using dot-notation.
 * Example: config('app.name')
 */
function config(string $key, $default = null) {
    return AppConfigSupport::read($key, $default);
}

// Escape helper for safe HTML output.
function e($s): string {
    return Html::escape($s);
}


/**
 * Absolute path to /public folder.
 */
function public_path(string $rel = ''): string {
    return Url::publicPath($rel);
}

/**
 * Detect base URI (folder) where app is running.
 */
function base_uri(): string {
    return Url::baseUri();
}

/**
 * Build an app URL (relative to current base folder).
 */
function url(string $path = ''): string {
    return Url::to($path);
}

/**
 * Build URL for static assets (under public/).
 */
function asset(string $path): string {
    return Url::asset($path);
}

function redirect(string $path): void {
    Redirect::to($path);
}

function require_login(): void {
    Auth::requireLogin();
}

/**
 * CSRF protection (simple session token)
 * Usage in views: <?= csrf_field() ?>
 * Usage in POST handlers: verify_csrf();
 */
function csrf_token(): string {
    return Csrf::token();
}

function csrf_field(): string {
    return Csrf::field('_csrf');
}

function verify_csrf(): void {
    Csrf::verify();
}

function current_user_id(): int {
    return (int) (Auth::id() ?? 0);
}

/**
 * Current user's display name.
 */
function current_user_name(): string {
    return Auth::name();
}


function current_user_cashbox_id(PDO $pdo): int {
    return Auth::cashboxId($pdo);
}

/**
 * Render a print page using a shared layout.
 *
 * Example:
 *   print_view('a4', 'purchase_invoice', ['title'=>'...', 'meta'=>[], 'lines'=>[]]);
 */
function print_view(string $format, string $bodyView, array $data = []): void {
    View::renderPrint($format, $bodyView, $data);
}

/**
 * Simple chooser page for selecting print format (A4 / Receipt).
 * Use when user opens print page without ?format=...
 */
function print_format_chooser(string $a4Url, string $receiptUrl = '', string $title = 'اختر نوع الطباعة', string $preferredFormat = 'a4'): void {
    PrintSupport::formatChooser($a4Url, $receiptUrl, $title, $preferredFormat);
}


function print_section_config(array $printOptions, string $area, string $section, array $defaults = []): array {
    $sections = is_array($printOptions['template_sections'] ?? null) ? $printOptions['template_sections'] : [];
    $areaSections = is_array($sections[$area] ?? null) ? $sections[$area] : [];
    $config = is_array($areaSections[$section] ?? null) ? $areaSections[$section] : [];

    return [
        'enabled' => array_key_exists('enabled', $config) ? !empty($config['enabled']) : ($defaults['enabled'] ?? true),
        'title' => trim((string)($config['title'] ?? ($defaults['title'] ?? ''))),
        'order' => (int)($config['order'] ?? ($defaults['order'] ?? 999)),
    ];
}

function print_section_visible(array $printOptions, string $area, string $section, bool $default = true): bool {
    $cfg = print_section_config($printOptions, $area, $section, ['enabled' => $default]);
    return !empty($cfg['enabled']);
}

function print_section_title(array $printOptions, string $area, string $section, string $fallback = ''): string {
    $cfg = print_section_config($printOptions, $area, $section, ['title' => $fallback]);
    return $cfg['title'] !== '' ? $cfg['title'] : $fallback;
}

function print_sections_ordered(array $definitions, array $printOptions, string $area): array {
    $normalized = [];
    foreach ($definitions as $key => $def) {
        $defaults = is_array($def) ? $def : [];
        $cfg = print_section_config($printOptions, $area, (string)$key, $defaults);
        $normalized[$key] = array_merge($defaults, $cfg, ['key' => (string)$key]);
    }

    uasort($normalized, static function (array $a, array $b): int {
        $orderCmp = ((int)$a['order']) <=> ((int)$b['order']);
        if ($orderCmp !== 0) {
            return $orderCmp;
        }
        return strcmp((string)($a['key'] ?? ''), (string)($b['key'] ?? ''));
    });

    return $normalized;
}

function flash_set(string $type, string $message): void {
    Flash::set($type, $message);
}

function flash_get(): ?array {
    return Flash::get();
}

function flash_has(?string $type = null): bool {
    return FlashSupport::has($type);
}




function label_def(string $key, string $category = ''): string {
    try {
        return LabelDefinitionService::translate(db(), $key, $category);
    } catch (Throwable $e) {
        return str_replace(['_', '-'], ' ', $key);
    }
}

function label_meta(?string $json): string {
    try {
        return LabelDefinitionService::formatMeta(db(), $json);
    } catch (Throwable $e) {
        return (string)$json;
    }
}

function format_money($v): string {
    return Format::money($v);
}


function amount_to_arabic_words($amount, string $currency = 'جنيه مصري', string $fraction = 'قرش'): string {
    $amount = round((float)$amount, 2);
    $integer = (int)floor(abs($amount));
    $fractionValue = (int)round((abs($amount) - $integer) * 100);
    if ($fractionValue === 100) {
        $integer += 1;
        $fractionValue = 0;
    }

    $numberToWords = static function (int $number) use (&$numberToWords): string {
        $ones = [
            0 => '', 1 => 'واحد', 2 => 'اثنان', 3 => 'ثلاثة', 4 => 'أربعة', 5 => 'خمسة',
            6 => 'ستة', 7 => 'سبعة', 8 => 'ثمانية', 9 => 'تسعة', 10 => 'عشرة',
            11 => 'أحد عشر', 12 => 'اثنا عشر', 13 => 'ثلاثة عشر', 14 => 'أربعة عشر',
            15 => 'خمسة عشر', 16 => 'ستة عشر', 17 => 'سبعة عشر', 18 => 'ثمانية عشر', 19 => 'تسعة عشر'
        ];
        $tens = [2 => 'عشرون', 3 => 'ثلاثون', 4 => 'أربعون', 5 => 'خمسون', 6 => 'ستون', 7 => 'سبعون', 8 => 'ثمانون', 9 => 'تسعون'];
        $hundreds = [1 => 'مائة', 2 => 'مائتان', 3 => 'ثلاثمائة', 4 => 'أربعمائة', 5 => 'خمسمائة', 6 => 'ستمائة', 7 => 'سبعمائة', 8 => 'ثمانمائة', 9 => 'تسعمائة'];

        if ($number === 0) {
            return 'صفر';
        }
        if ($number < 20) {
            return $ones[$number];
        }
        if ($number < 100) {
            $unit = $number % 10;
            $ten = intdiv($number, 10);
            return $unit > 0 ? $ones[$unit] . ' و' . $tens[$ten] : $tens[$ten];
        }
        if ($number < 1000) {
            $hundred = intdiv($number, 100);
            $rest = $number % 100;
            return $rest > 0 ? $hundreds[$hundred] . ' و' . $numberToWords($rest) : $hundreds[$hundred];
        }

        $groups = [
            1000000000 => ['singular' => 'مليار', 'dual' => 'ملياران', 'plural' => 'مليارات'],
            1000000 => ['singular' => 'مليون', 'dual' => 'مليونان', 'plural' => 'ملايين'],
            1000 => ['singular' => 'ألف', 'dual' => 'ألفان', 'plural' => 'آلاف'],
        ];

        foreach ($groups as $value => $labels) {
            if ($number >= $value) {
                $count = intdiv($number, $value);
                $rest = $number % $value;
                if ($count === 1) {
                    $groupText = $labels['singular'];
                } elseif ($count === 2) {
                    $groupText = $labels['dual'];
                } elseif ($count >= 3 && $count <= 10) {
                    $groupText = $numberToWords($count) . ' ' . $labels['plural'];
                } else {
                    $groupText = $numberToWords($count) . ' ' . $labels['singular'];
                }
                return $rest > 0 ? $groupText . ' و' . $numberToWords($rest) : $groupText;
            }
        }

        return (string)$number;
    };

    $parts = [];
    $parts[] = $numberToWords($integer) . ' ' . $currency;
    if ($fractionValue > 0) {
        $parts[] = $numberToWords($fractionValue) . ' ' . $fraction;
    }

    return 'فقط ' . implode(' و', $parts) . ' لا غير';
}


// Short alias used in some views.
// nf = number format (money) with 2 decimals.
function nf($v): string {
    return format_money($v);
}

/**
 * Customer A/R balance = opening_balance + SUM(debit-credit) from customer_ledger.
 * Positive => customer owes us.
 */
function customer_balance(PDO $pdo, int $customer_id, ?string $as_of = null): float {
    return CustomerSupport::balance($pdo, $customer_id, $as_of);
}

function format_qty($v, int $max_decimals = 3): string {
    if ($v === null || !is_numeric($v)) {
        return '-';
    }
    return Format::qty($v, $max_decimals);
}

// Warehouse document type labels
function doc_type_label(string $type): string {
    return Format::docTypeLabel($type);
}

/**
 * Render a view inside the main layout.
 * Example: view('pages/items/index', ['items'=>$items]);
 */
function view(string $viewPath, array $data = []): void {
    View::render($viewPath, $data);
}


/** Warehouse helpers **/
function main_warehouse_id(PDO $pdo): int {
    return InventoryLookup::mainWarehouseId($pdo);
}

function get_warehouses(PDO $pdo): array {
    return InventoryLookup::getWarehouses($pdo);
}

/**
 * Check whether a column exists on a table (MySQL/MariaDB).
 * Used to keep the app working even if optional migrations haven't been applied.
 */
function table_has_column(PDO $pdo, string $table, string $column): bool {
    return SchemaSupport::tableHasColumn($pdo, $table, $column);
}

/**
 * Per-warehouse policy: allow negative stock or not.
 * Defaults to false if column doesn't exist.
 */
function warehouse_allows_negative(PDO $pdo, int $warehouse_id): bool {
    return InventoryLookup::warehouseAllowsNegative($pdo, $warehouse_id);
}

/**
 * Get current on-hand balance from stock_balances.
 */
function get_stock_balance(PDO $pdo, int $warehouse_id, int $item_id): float {
    return InventoryLookup::stockBalance($pdo, $warehouse_id, $item_id);
}

function get_categories(PDO $pdo): array {
    return InventoryLookup::getCategories($pdo);
}

function get_units(PDO $pdo): array {
    return InventoryLookup::getUnits($pdo);
}

/** Suppliers helpers **/
function get_suppliers(PDO $pdo): array {
    return InventoryLookup::getSuppliers($pdo);
}

function supplier_label(array $s): string {
    return InventoryLookup::supplierLabel($s);
}


/**
 * Supplier ledger schema (minimal) - used by cash payments / purchases
 */
function ensure_supplier_ledger_schema(PDO $pdo): void {
    SupplierLedgerSchemaSupport::ensureSupplierLedgerSchema($pdo);
}

/**
 * Update stock balance (qty) atomically; insert row if missing.
 * delta may be positive (in) or negative (out).
 */
function stock_balance_apply(PDO $pdo, int $warehouse_id, int $item_id, float $delta): void {
    InventoryLookup::applyStockBalance($pdo, $warehouse_id, $item_id, $delta);
}

function stock_balance_deduct_or_fail(PDO $pdo, int $warehouse_id, int $item_id, float $qty): void {
    InventoryLookup::deductStockOrFail($pdo, $warehouse_id, $item_id, $qty);
}


/**
 * Get conversion factor from selected unit to item's base unit (items.default_unit_id).
 * Uses item_units mapping; if not found returns 1.
 */
function item_unit_factor(PDO $pdo, int $item_id, ?int $unit_id): float {
    return InventoryLookup::itemUnitFactor($pdo, $item_id, $unit_id);
}

/**
 * Barcode lookup: supports pack barcode in item_units; fallback to items.barcode.
 * Returns array [item_id, unit_id|null, factor]
 */
function lookup_by_barcode(PDO $pdo, string $barcode): array {
    return InventoryLookup::lookupByBarcode($pdo, $barcode);
}

/** =============================
 * RBAC (Roles & Permissions)
 * ============================= */

function table_exists(PDO $pdo, string $table): bool {
    return SchemaSupport::tableExists($pdo, $table);
}

function column_exists(PDO $pdo, string $table, string $column): bool {
    return SchemaSupport::columnExists($pdo, $table, $column);
}

function ensure_column(PDO $pdo, string $table, string $column, string $definitionSql): void {
    SchemaSupport::ensureColumn($pdo, $table, $column, $definitionSql);
}

function license_state(): array {
    $state = \App\Core\Session::get('_license_state', []);
    return is_array($state) ? $state : [];
}

function license_is_valid(): bool {
    return (bool) (license_state()['valid'] ?? false);
}

function license_is_read_only(): bool {
    return (bool) (license_state()['read_only'] ?? true);
}

function license_message(): ?string {
    $message = license_state()['message'] ?? null;
    return is_string($message) && $message !== '' ? $message : null;
}

function license_fingerprint(): string {
    return (string) (license_state()['fingerprint'] ?? '');
}


function license_admin_enabled(): bool {
    $config = require APP_PATH . '/config.php';
    $manager = new \App\Services\License\LicenseManager($config);
    return $manager->isDeveloperConsoleEnabled();
}

function rbac_enabled(PDO $pdo): bool {
    return RbacSupport::enabled($pdo);
}

function current_user_roles(PDO $pdo): array {
    return RbacSupport::currentUserRoles($pdo, current_user_id());
}

function current_user_permissions(PDO $pdo): array {
    return RbacSupport::currentUserPermissions($pdo, current_user_id());
}


/**
 * Ensure core permissions exist (idempotent).
 * This is used to keep menus working when new modules are deployed.
 */
function ensure_permissions_seed(\PDO $pdo): void {
    RbacSupport::ensurePermissionsSeed($pdo);
}

function can(string $permission_code): bool {
    return Authorization::can($permission_code);
}

function require_permission(string $permission_code): void {
    Authorization::requirePermission($permission_code);
}

function can_manage_parties(): bool {
    return Authorization::canManageParties();
}

function can_view_parties(): bool {
    return Authorization::canViewParties();
}

function require_view_parties(): void {
    Authorization::requireViewParties();
}

function ensure_customer_receipts_schema(PDO $pdo): void {
    FinanceSchemaSupport::ensureCustomerReceiptsSchema($pdo);
}

function ensure_customer_receipts_party_schema(PDO $pdo): void {
    FinanceSchemaSupport::ensureCustomerReceiptsPartySchema($pdo);
}

/** =============================
 * Cashboxes: schema + helpers
 * ============================= */

function ensure_cashboxes_schema(PDO $pdo): void {
    FinanceSchemaSupport::ensureCashboxesSchema($pdo);
}

function cashbox_balance(PDO $pdo, int $cashbox_id, ?string $to_date = null): float {
    return AccountBalanceSupport::cashboxBalance($pdo, $cashbox_id, $to_date);
}

function cashbox_add_txn(PDO $pdo, int $cashbox_id, string $trx_date, string $trx_type, float $amount,
                         ?string $ref_type, ?int $ref_id, ?string $notes, int $user_id): int {
    return AccountBalanceSupport::addCashboxTransaction($pdo, $cashbox_id, $trx_date, $trx_type, $amount, $ref_type, $ref_id, $notes, $user_id);
}

/** =============================
 * Cash: Payment vouchers (Sندات الصرف)
 * ============================= */

function ensure_cash_payments_schema(PDO $pdo): void {
    FinanceSchemaSupport::ensureCashPaymentsSchema($pdo);
}

function ensure_cash_payments_party_schema(PDO $pdo): void {
    FinanceSchemaSupport::ensureCashPaymentsPartySchema($pdo);
}

function next_cash_payment_no(PDO $pdo): string {
    return AccountBalanceSupport::nextCashPaymentNo($pdo);
}

function next_receipt_no(PDO $pdo): string {
    return FinanceSchemaSupport::nextReceiptNo($pdo);
}


function company_profile(PDO $pdo): array {
    return CompanySupport::profile($pdo);
}


function rbac_flush_cache(): void {
    RbacSupport::flushCache();
}

/** =============================
 * Audit logging
 * ============================= */

function audit_log(string $action, ?string $entity = null, $entity_id = null, array $meta = []): void {
    AuditSupport::log($action, $entity, $entity_id, $meta);
}



/** =============================
 * Backup helpers
 * ============================= */

function backups_dir(): string {
    return BackupSupport::backupsDir();
}

function db_list_tables(PDO $pdo): array {
    return BackupSupport::listTables($pdo);
}

function sql_escape_identifier(string $name): string {
    return BackupSupport::escapeIdentifier($name);
}

/**
 * Create SQL backup (schema + data) using pure PHP.
 * Returns absolute path to .sql file.
 */
function create_sql_backup(PDO $pdo, string $note = ''): string {
    return BackupSupport::createSqlBackup($pdo, $note);
}

/**
 * Restore SQL backup file. Returns [ok(bool), message].
 */
function restore_sql_backup(PDO $pdo, string $sql_file_path): array {
    return BackupSupport::restoreSqlBackup($pdo, $sql_file_path);
}


// =============================================================
// Supplier <-> Customer linking helpers
// (A supplier can also be a customer, with auto-create if missing)
// =============================================================

function ensure_customers_schema(PDO $pdo): void {
    PartySchemaSupport::ensureCustomersSchema($pdo);
}

function find_existing_customer_for_supplier(PDO $pdo, array $supplierRow): ?array {
    return PartySchemaSupport::findExistingCustomerForSupplier($pdo, $supplierRow);
}

function create_customer_from_supplier(PDO $pdo, array $supplierRow): int {
    return PartySchemaSupport::createCustomerFromSupplier($pdo, $supplierRow);
}

function link_supplier_to_customer(PDO $pdo, int $supplierId, int $customerId): void {
    PartySchemaSupport::linkSupplierToCustomer($pdo, $supplierId, $customerId);
}

function unlink_supplier_customer(PDO $pdo, int $supplierId): void {
    PartySchemaSupport::unlinkSupplierCustomer($pdo, $supplierId);
}


// === Parties (Unified Customer/Supplier) ===
function ensure_parties_schema(PDO $pdo): void {
    PartySchemaSupport::ensurePartiesSchema($pdo);
}

function ensure_party_ledger_schema(PDO $pdo): void {
    PartySchemaSupport::ensurePartyLedgerSchema($pdo);
}


function ensure_purchase_invoices_party_schema(PDO $pdo): void {
    PartySchemaSupport::ensurePurchaseInvoicesPartySchema($pdo);
}

/**
 * Drop foreign keys on a table that reference a specific referenced table.
 * Safe to call multiple times.
 */
function drop_fks_referencing(PDO $pdo, string $table, string $referencedTable): void {
    PartySchemaSupport::dropForeignKeysReferencing($pdo, $table, $referencedTable);
}

function ensure_purchase_returns_party_schema(PDO $pdo): void {
    PartySchemaSupport::ensurePurchaseReturnsPartySchema($pdo);
}

function ensure_sales_invoices_party_schema(PDO $pdo): void {
    PartySchemaSupport::ensureSalesInvoicesPartySchema($pdo);
}

function ensure_items_party_schema(PDO $pdo): void {
    PartySchemaSupport::ensureItemsPartySchema($pdo);
}

function ensure_checks_schema(PDO $pdo): void {
    ChecksSchemaSupport::ensureChecksSchema($pdo);
}

function check_status_label(string $status): string {
    return Format::checkStatusLabel($status);
}

function check_holder_label(?string $type): string {
    return Format::checkHolderLabel($type);
}

function checks_alert_counts(PDO $pdo, int $daysAhead = 7): array {
    return ChecksSupport::alertCounts($pdo, $daysAhead);
}


function alerts_dashboard_counts(PDO $pdo, int $daysAhead = 7, int $agingDays = 30, float $cashMin = 1000.0, float $bankMin = 1000.0, int $draftDays = 7, int $stagnantDays = 60): array {
    $counts = [
        'returned_checks' => 0,
        'due_checks' => 0,
        'inventory_alerts' => 0,
        'customer_overdue' => 0,
        'supplier_due' => 0,
        'cashbox_alerts' => 0,
        'bank_alerts' => 0,
        'draft_documents' => 0,
        'checks' => [
            'returned' => 0,
            'due' => 0,
            'total' => 0,
        ],
        'inventory' => [
            'total' => 0,
        ],
        'parties' => [
            'customers' => 0,
            'suppliers' => 0,
            'total' => 0,
        ],
        'treasury' => [
            'cashboxes' => 0,
            'banks' => 0,
            'total' => 0,
        ],
        'drafts' => [
            'total' => 0,
        ],
        'total' => 0,
    ];

    try {
        $checkCounts = checks_alert_counts($pdo, $daysAhead);
        $counts['returned_checks'] = (int)($checkCounts['returned'] ?? 0);
        $counts['due_checks'] = (int)($checkCounts['due_today'] ?? 0) + (int)($checkCounts['due_soon'] ?? 0) + (int)($checkCounts['overdue'] ?? 0);
    } catch (Throwable) {}

    try {
        $inventory = $pdo->query("SELECT COUNT(*) FROM (SELECT i.id FROM items i LEFT JOIN stock_balances sb ON sb.item_id=i.id WHERE COALESCE(i.is_active,1)=1 GROUP BY i.id, i.low_stock_level, i.reorder_point HAVING COALESCE(SUM(sb.qty),0) <= 0 OR (COALESCE(i.low_stock_level, i.reorder_point, 0) > 0 AND COALESCE(SUM(sb.qty),0) <= COALESCE(i.low_stock_level, i.reorder_point, 0))) t")->fetchColumn();
        $counts['inventory_alerts'] = (int)$inventory;
    } catch (Throwable) {}

    try {
        $cutoff = date('Y-m-d', strtotime('-' . max(15, $agingDays) . ' days'));
        $rows = $pdo->query("SELECT p.id, COALESCE(p.is_customer,0) is_customer, COALESCE(p.is_supplier,0) is_supplier, COALESCE(SUM(pl.debit-pl.credit),0) balance, MAX(pl.doc_date) last_doc_date FROM parties p LEFT JOIN party_ledger pl ON pl.party_id=p.id GROUP BY p.id, p.is_customer, p.is_supplier")->fetchAll() ?: [];
        foreach ($rows as $row) {
            $balance = (float)($row['balance'] ?? 0);
            $lastDocDate = (string)($row['last_doc_date'] ?? '');
            $isOld = $lastDocDate === '' || $lastDocDate <= $cutoff;
            if ((int)($row['is_customer'] ?? 0) === 1 && $balance > 0 && $isOld) $counts['customer_overdue']++;
            if ((int)($row['is_supplier'] ?? 0) === 1 && $balance < 0 && $isOld) $counts['supplier_due']++;
        }
    } catch (Throwable) {}

    try {
        $rows = $pdo->query("SELECT id FROM cashboxes WHERE COALESCE(is_active,1)=1")->fetchAll() ?: [];
        foreach ($rows as $row) {
            if (cashbox_balance($pdo, (int)$row['id']) <= $cashMin) $counts['cashbox_alerts']++;
        }
    } catch (Throwable) {}

    try {
        $rows = $pdo->query("SELECT id FROM bank_accounts WHERE COALESCE(is_active,1)=1")->fetchAll() ?: [];
        foreach ($rows as $row) {
            if (bank_account_balance($pdo, (int)$row['id']) <= $bankMin) $counts['bank_alerts']++;
        }
    } catch (Throwable) {}

    try {
        $draftCutoff = date('Y-m-d H:i:s', strtotime('-' . max(1, $draftDays) . ' days'));
        foreach ([
            "SELECT COUNT(*) FROM sales_invoices WHERE UPPER(COALESCE(status,''))='DRAFT' AND COALESCE(created_at,'1970-01-01') <= " . $pdo->quote($draftCutoff),
            "SELECT COUNT(*) FROM purchase_invoices WHERE UPPER(COALESCE(status,''))='DRAFT' AND COALESCE(created_at,'1970-01-01') <= " . $pdo->quote($draftCutoff),
            "SELECT COUNT(*) FROM sales_returns WHERE UPPER(COALESCE(status,''))='DRAFT' AND COALESCE(created_at,'1970-01-01') <= " . $pdo->quote($draftCutoff),
            "SELECT COUNT(*) FROM purchase_returns WHERE UPPER(COALESCE(status,''))='DRAFT' AND COALESCE(created_at,'1970-01-01') <= " . $pdo->quote($draftCutoff)
        ] as $sql) {
            try { $counts['draft_documents'] += (int)$pdo->query($sql)->fetchColumn(); } catch (Throwable) {}
        }
    } catch (Throwable) {}

    $counts['checks'] = [
        'returned' => $counts['returned_checks'],
        'due' => $counts['due_checks'],
        'total' => $counts['returned_checks'] + $counts['due_checks'],
    ];
    $counts['inventory'] = [
        'total' => $counts['inventory_alerts'],
    ];
    $counts['parties'] = [
        'customers' => $counts['customer_overdue'],
        'suppliers' => $counts['supplier_due'],
        'total' => $counts['customer_overdue'] + $counts['supplier_due'],
    ];
    $counts['treasury'] = [
        'cashboxes' => $counts['cashbox_alerts'],
        'banks' => $counts['bank_alerts'],
        'total' => $counts['cashbox_alerts'] + $counts['bank_alerts'],
    ];
    $counts['drafts'] = [
        'total' => $counts['draft_documents'],
    ];

    $counts['total'] = $counts['checks']['total'] + $counts['inventory']['total'] + $counts['parties']['total'] + $counts['treasury']['total'] + $counts['drafts']['total'];
    return $counts;
}


/**
 * One-stop schema normalization for Parties-only mode.
 * Safe to call on every request.
 */
function ensure_parties_full_schema(PDO $pdo): void {
    PartySchemaSupport::ensurePartiesFullSchema($pdo);
}

function party_auto_code(PDO $pdo, int $partyId): void {
    PartyLedgerSupport::autoCode($pdo, $partyId);
}

function party_add_ledger(PDO $pdo, int $partyId, string $docType, int $docId, ?string $docNo, string $docDate, float $debit, float $credit, ?string $notes): void {
    PartyLedgerSupport::addLedger($pdo, $partyId, $docType, $docId, $docNo, $docDate, $debit, $credit, $notes);
}

function party_balance(PDO $pdo, int $partyId, string $beforeDate): float {
    return PartyLedgerSupport::balance($pdo, $partyId, $beforeDate);
}

function ensure_parties_migrate_from_customers_suppliers(PDO $pdo): void {
    PartySchemaSupport::ensurePartiesMigrateFromCustomersSuppliers($pdo);
}



/** =============================
 * Missing schema helpers restored
 * ============================= */
function ensure_app_settings_schema(PDO $pdo): void {
    FinanceSchemaSupport::ensureAppSettingsSchema($pdo);
}

function ensure_bank_accounts_schema(PDO $pdo): void {
    FinanceSchemaSupport::ensureBankAccountsSchema($pdo);
}


function communication_settings(PDO $pdo): array {
    ensure_app_settings_schema($pdo);
    $defaults = [
        'mail_from_name' => '',
        'mail_from_email' => '',
        'mail_transport' => 'mail',
        'mail_reply_to' => '',
        'smtp_host' => '',
        'smtp_port' => '587',
        'smtp_username' => '',
        'smtp_password' => '',
        'smtp_encryption' => 'tls',
        'smtp_timeout' => '15',
        'public_base_url' => '',
        'whatsapp_template' => '',
        'quotation_email_template' => '',
        'whatsapp_default_country_code' => '20',
    ];

    $stmt = $pdo->query("SELECT setting_key, setting_value FROM app_settings WHERE setting_key IN ('mail_from_name','mail_from_email','mail_transport','mail_reply_to','smtp_host','smtp_port','smtp_username','smtp_password','smtp_encryption','smtp_timeout','public_base_url','whatsapp_template','quotation_email_template','whatsapp_default_country_code')");
    if ($stmt) {
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $key = (string)($row['setting_key'] ?? '');
            if ($key !== '' && array_key_exists($key, $defaults)) {
                $defaults[$key] = (string)($row['setting_value'] ?? '');
            }
        }
    }

    if (trim((string)$defaults['mail_transport']) === '') {
        $defaults['mail_transport'] = 'mail';
    }
    if (trim((string)$defaults['whatsapp_default_country_code']) === '') {
        $defaults['whatsapp_default_country_code'] = '20';
    }
    if (trim((string)$defaults['smtp_port']) === '') {
        $defaults['smtp_port'] = '587';
    }
    if (trim((string)$defaults['smtp_encryption']) === '') {
        $defaults['smtp_encryption'] = 'tls';
    }
    if (trim((string)$defaults['smtp_timeout']) === '') {
        $defaults['smtp_timeout'] = '15';
    }

    return $defaults;
}

function save_communication_settings(PDO $pdo, array $settings): void {
    ensure_app_settings_schema($pdo);
    $stmt = $pdo->prepare('INSERT INTO app_settings (setting_key, setting_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)');
    foreach ([
        'mail_from_name',
        'mail_from_email',
        'mail_transport',
        'mail_reply_to',
        'smtp_host',
        'smtp_port',
        'smtp_username',
        'smtp_password',
        'smtp_encryption',
        'smtp_timeout',
        'public_base_url',
        'whatsapp_template',
        'quotation_email_template',
        'whatsapp_default_country_code',
    ] as $key) {
        $stmt->execute([$key, (string)($settings[$key] ?? '')]);
    }
}



function send_simple_mail(PDO $pdo, string $to, string $subject, string $htmlMessage, array $attachments = []): array {
    ensure_app_settings_schema($pdo);
    $settings = communication_settings($pdo);
    $transport = strtolower(trim((string)($settings['mail_transport'] ?? 'mail')));
    if ($transport === '') {
        $transport = 'mail';
    }

    $fromName = trim((string)($settings['mail_from_name'] ?? ''));
    $fromEmail = trim((string)($settings['mail_from_email'] ?? ''));
    $replyTo = trim((string)($settings['mail_reply_to'] ?? ''));

    if ($transport === 'disabled') {
        return ['ok' => false, 'message' => 'إرسال البريد معطل من الإعدادات.'];
    }

    $htmlMessage = trim($htmlMessage);
    if ($htmlMessage === '') {
        return ['ok' => false, 'message' => 'محتوى الرسالة فارغ.'];
    }

    $normalizedAttachments = [];
    foreach ($attachments as $attachment) {
        if (!is_array($attachment)) {
            continue;
        }
        $name = trim((string)($attachment['name'] ?? 'attachment.bin'));
        $type = trim((string)($attachment['type'] ?? 'application/octet-stream'));
        $content = $attachment['content'] ?? '';
        if ($name === '' || !is_string($content) || $content === '') {
            continue;
        }
        $normalizedAttachments[] = [
            'name' => $name,
            'type' => $type !== '' ? $type : 'application/octet-stream',
            'content' => $content,
        ];
    }

    if ($transport === 'log') {
        $dir = dirname(__DIR__) . '/storage/mail_logs';
        if (!is_dir($dir)) {
            @mkdir($dir, 0777, true);
        }
        $safeTo = preg_replace('/[^a-zA-Z0-9._-]+/', '_', $to) ?: 'recipient';
        $stamp = date('Ymd_His');
        $base = $dir . '/' . $stamp . '__' . $safeTo;
        $file = $base . '.html';
        $meta = "To: {$to}
Subject: {$subject}
From: {$fromName} <{$fromEmail}>
Reply-To: {$replyTo}
Date: " . date('c') . "
Attachments: " . count($normalizedAttachments) . "

";
        $saved = @file_put_contents($file, $meta . $htmlMessage);
        if ($saved === false) {
            return ['ok' => false, 'message' => 'تعذر حفظ نسخة البريد التجريبية في storage/mail_logs.'];
        }
        $savedAttachments = [];
        foreach ($normalizedAttachments as $index => $attachment) {
            $safeName = preg_replace('/[^A-Za-z0-9._-]+/', '_', $attachment['name']) ?: ('attachment_' . ($index + 1));
            $attachFile = $base . '__' . ($index + 1) . '__' . $safeName;
            if (@file_put_contents($attachFile, $attachment['content']) !== false) {
                $savedAttachments[] = $attachFile;
            }
        }
        return ['ok' => true, 'message' => 'تم حفظ نسخة البريد التجريبية.', 'transport' => 'log', 'file' => $file, 'attachments' => $savedAttachments];
    }

    if ($fromEmail === '' || !filter_var($fromEmail, FILTER_VALIDATE_EMAIL)) {
        return ['ok' => false, 'message' => 'بريد المرسل غير مضبوط أو غير صحيح في إعدادات البريد.'];
    }
    if ($transport === 'smtp') {
        return smtp_send_mail($settings, $to, $subject, $htmlMessage, $normalizedAttachments);
    }
    if ($replyTo !== '' && !filter_var($replyTo, FILTER_VALIDATE_EMAIL)) {
        return ['ok' => false, 'message' => 'بريد الرد غير صحيح في إعدادات البريد.'];
    }

    $fromHeader = $fromEmail;
    if ($fromName !== '') {
        $encoded = '=?UTF-8?B?' . base64_encode($fromName) . '?=';
        $fromHeader = $encoded . ' <' . $fromEmail . '>';
    }

    $encodedSubject = '=?UTF-8?B?' . base64_encode($subject) . '?=';

    if ($normalizedAttachments === []) {
        $headers = [
            'MIME-Version: 1.0',
            'Content-type: text/html; charset=UTF-8',
            'From: ' . $fromHeader,
        ];
        if ($replyTo !== '') {
            $headers[] = 'Reply-To: ' . $replyTo;
        }
        $ok = @mail($to, $encodedSubject, $htmlMessage, implode("
", $headers));
        if (!$ok) {
            return ['ok' => false, 'message' => 'فشل إرسال البريد عبر PHP mail(). على localhost استخدم وضع "حفظ نسخة فقط" أو اضبط خادم بريد فعلي.'];
        }
        return ['ok' => true, 'message' => 'تم إرسال البريد بنجاح.', 'transport' => 'mail'];
    }

    $boundary = '=_Part_' . bin2hex(random_bytes(12));
    $headers = [
        'MIME-Version: 1.0',
        'From: ' . $fromHeader,
        'Content-Type: multipart/mixed; boundary="' . $boundary . '"',
    ];
    if ($replyTo !== '') {
        $headers[] = 'Reply-To: ' . $replyTo;
    }

    $body = [];
    $body[] = '--' . $boundary;
    $body[] = 'Content-Type: text/html; charset=UTF-8';
    $body[] = 'Content-Transfer-Encoding: 8bit';
    $body[] = '';
    $body[] = $htmlMessage;
    foreach ($normalizedAttachments as $attachment) {
        $body[] = '--' . $boundary;
        $body[] = 'Content-Type: ' . $attachment['type'] . '; name="' . addslashes($attachment['name']) . '"';
        $body[] = 'Content-Transfer-Encoding: base64';
        $body[] = 'Content-Disposition: attachment; filename="' . addslashes($attachment['name']) . '"';
        $body[] = '';
        $body[] = chunk_split(base64_encode($attachment['content']));
    }
    $body[] = '--' . $boundary . '--';
    $body[] = '';

    $ok = @mail($to, $encodedSubject, implode("
", $body), implode("
", $headers));
    if (!$ok) {
        return ['ok' => false, 'message' => 'فشل إرسال البريد عبر PHP mail(). على localhost استخدم وضع "حفظ نسخة فقط" أو اضبط خادم بريد فعلي.'];
    }
    return ['ok' => true, 'message' => 'تم إرسال البريد بنجاح مع المرفق.', 'transport' => 'mail'];
}

function ensure_expenses_schema(PDO $pdo): void {
    FinanceSchemaSupport::ensureExpensesSchema($pdo);
}

function ensure_users_cashbox_schema(PDO $pdo): void {
    FinanceSchemaSupport::ensureUsersCashboxSchema($pdo);
}

function ensure_purchase_invoice_payment_schema(PDO $pdo): void {
    FinanceSchemaSupport::ensurePurchaseInvoicePaymentSchema($pdo);
}

function ensure_sales_invoice_payment_schema(PDO $pdo): void {
    FinanceSchemaSupport::ensureSalesInvoicePaymentSchema($pdo);
}

function ensure_quotations_schema(PDO $pdo): void {
    QuotationSchemaSupport::ensureQuotationsSchema($pdo);
}



function bank_account_balance(PDO $pdo, int $bankAccountId, ?string $asOf = null): float {
    return AccountBalanceSupport::bankAccountBalance($pdo, $bankAccountId, $asOf);
}

function can_endorse_check(array $check): bool {
    return ReferenceLabel::canEndorseCheck($check);
}
function can_return_check(array $check): bool {
    return ReferenceLabel::canReturnCheck($check);
}
function can_cash_settle_check(array $check): bool {
    return ReferenceLabel::canCashSettleCheck($check);
}
function can_replace_check(array $check): bool {
    return ReferenceLabel::canReplaceCheck($check);
}
function quotation_status_label(string $status): string {
    return Format::quotationStatusLabel($status);
}
function next_quotation_no(PDO $pdo): string {
    return QuotationSupport::nextQuotationNo($pdo);
}
function quotation_create_or_resolve_customer(PDO $pdo, string $name, string $phone='', string $email=''): int {
    return QuotationSupport::createOrResolveCustomer($pdo, $name, $phone, $email);
}
function quotation_effective_price(PDO $pdo, int $partyId, int $itemId, ?string $invoiceDate = null): ?array {
    return QuotationSupport::effectivePrice($pdo, $partyId, $itemId, $invoiceDate);
}
function quotation_public_print_url(PDO $pdo, int $id, string $format='a4'): string {
    return QuotationSupport::publicPrintUrl($pdo, $id, $format);
}
function quotation_public_pdf_url(PDO $pdo, int $id): string {
    return QuotationSupport::publicPdfUrl($pdo, $id);
}
function quotation_public_share_url(PDO $pdo, int $id): string {
    return QuotationSupport::publicPdfUrl($pdo, $id);
}


function can_settle_check_cash(array $check): bool {
    return can_cash_settle_check($check);
}


function reference_type_label(string $type): string {
    return ReferenceLabel::referenceType($type);
}

function human_reference_label(string $type, $id = null, string $docNo = ''): string {
    return ReferenceLabel::humanReference($type, $id, $docNo);
}

function ledger_doc_type_label(string $docType): string {
    return ReferenceLabel::ledgerDocType($docType);
}

function ledger_reference_label(array $row): string {
    return ReferenceLabel::ledgerReference($row);
}

function ledger_entry_description(array $row): string {
    return ReferenceLabel::ledgerEntryDescription($row);
}


function csrf_input(): string {
    return csrf_field();
}



/**
 * Paginate an in-memory rows array for list/report screens.
 * @return array{items:array,page:int,per_page:int,total_rows:int,total_pages:int,offset:int,from:int,to:int}
 */
function paginate_rows(array $rows, int $page = 1, int $perPage = 25): array {
    $totalRows = count($rows);
    $perPage = max(1, $perPage);
    $totalPages = max(1, (int) ceil($totalRows / $perPage));
    $page = max(1, min($page, $totalPages));
    $offset = ($page - 1) * $perPage;
    $items = array_slice($rows, $offset, $perPage);
    return [
        'items' => $items,
        'page' => $page,
        'per_page' => $perPage,
        'total_rows' => $totalRows,
        'total_pages' => $totalPages,
        'offset' => $offset,
        'from' => $totalRows > 0 ? ($offset + 1) : 0,
        'to' => min($offset + $perPage, $totalRows),
    ];
}

function pagination_url(int $page, array $overrides = []): string {
    $params = array_merge($_GET ?? [], $overrides, ['page' => $page]);
    if (($params['page'] ?? 1) <= 1) {
        unset($params['page']);
    }
    $path = strtok($_SERVER['REQUEST_URI'] ?? '', '?') ?: ($_SERVER['PHP_SELF'] ?? '');
    $qs = http_build_query($params);
    return $qs !== '' ? ($path . '?' . $qs) : $path;
}

function render_pagination(int $page, int $totalPages, int $totalRows = 0, int $from = 0, int $to = 0): string {
    if ($totalPages <= 1) {
        return '';
    }
    $start = max(1, $page - 2);
    $end = min($totalPages, $page + 2);
    ob_start();
    ?>
    <div class="d-flex flex-wrap justify-content-between align-items-center gap-2 mt-3">
      <div class="text-muted small">عرض <?= (int)$from ?> - <?= (int)$to ?> من <?= (int)$totalRows ?></div>
      <nav aria-label="Pagination">
        <ul class="pagination pagination-sm mb-0">
          <li class="page-item <?= $page <= 1 ? 'disabled' : '' ?>"><a class="page-link" href="<?= e(pagination_url(max(1, $page - 1))) ?>">السابق</a></li>
          <?php for ($i = $start; $i <= $end; $i++): ?>
            <li class="page-item <?= $i === $page ? 'active' : '' ?>"><a class="page-link" href="<?= e(pagination_url($i)) ?>"><?= $i ?></a></li>
          <?php endfor; ?>
          <li class="page-item <?= $page >= $totalPages ? 'disabled' : '' ?>"><a class="page-link" href="<?= e(pagination_url(min($totalPages, $page + 1))) ?>">التالي</a></li>
        </ul>
      </nav>
    </div>
    <?php
    return (string) ob_get_clean();
}


function smtp_send_mail(array $settings, string $to, string $subject, string $htmlMessage, array $attachments = []): array {
    $host = trim((string)($settings['smtp_host'] ?? ''));
    $port = (int)trim((string)($settings['smtp_port'] ?? '587'));
    $username = trim((string)($settings['smtp_username'] ?? ''));
    $password = (string)($settings['smtp_password'] ?? '');
    $encryption = strtolower(trim((string)($settings['smtp_encryption'] ?? 'tls')));
    $timeout = (float)trim((string)($settings['smtp_timeout'] ?? '15'));
    $fromName = trim((string)($settings['mail_from_name'] ?? ''));
    $fromEmail = trim((string)($settings['mail_from_email'] ?? ''));
    $replyTo = trim((string)($settings['mail_reply_to'] ?? ''));

    if ($host === '') {
        return ['ok' => false, 'message' => 'SMTP Host غير مضبوط.'];
    }
    if ($port <= 0) {
        return ['ok' => false, 'message' => 'SMTP Port غير صحيح.'];
    }
    if (!in_array($encryption, ['tls', 'ssl', 'none'], true)) {
        $encryption = 'tls';
    }
    if ($username === '' || $password === '') {
        return ['ok' => false, 'message' => 'اسم مستخدم أو كلمة مرور SMTP غير مضبوطة.'];
    }

    $boundary = '=_Part_' . bin2hex(random_bytes(12));
    $headers = [
        'Date: ' . date('r'),
        'From: ' . smtp_format_address($fromEmail, $fromName),
        'To: ' . $to,
        'Subject: =?UTF-8?B?' . base64_encode($subject) . '?=',
        'MIME-Version: 1.0',
    ];
    if ($replyTo !== '' && filter_var($replyTo, FILTER_VALIDATE_EMAIL)) {
        $headers[] = 'Reply-To: ' . $replyTo;
    }

    if ($attachments === []) {
        $headers[] = 'Content-Type: text/html; charset=UTF-8';
        $headers[] = 'Content-Transfer-Encoding: 8bit';
        $body = $htmlMessage;
    } else {
        $headers[] = 'Content-Type: multipart/mixed; boundary="' . $boundary . '"';
        $bodyParts = [];
        $bodyParts[] = '--' . $boundary;
        $bodyParts[] = 'Content-Type: text/html; charset=UTF-8';
        $bodyParts[] = 'Content-Transfer-Encoding: 8bit';
        $bodyParts[] = '';
        $bodyParts[] = $htmlMessage;
        foreach ($attachments as $attachment) {
            $bodyParts[] = '--' . $boundary;
            $bodyParts[] = 'Content-Type: ' . ($attachment['type'] ?? 'application/octet-stream') . '; name="' . addslashes((string)$attachment['name']) . '"';
            $bodyParts[] = 'Content-Transfer-Encoding: base64';
            $bodyParts[] = 'Content-Disposition: attachment; filename="' . addslashes((string)$attachment['name']) . '"';
            $bodyParts[] = '';
            $bodyParts[] = chunk_split(base64_encode((string)$attachment['content']), 76, "
");
        }
        $bodyParts[] = '--' . $boundary . '--';
        $bodyParts[] = '';
        $body = implode("
", $bodyParts);
    }

    $message = implode("
", $headers) . "

" . smtp_normalize_body($body);
    $scheme = $encryption === 'ssl' ? 'ssl://' : '';
    $errno = 0;
    $errstr = '';
    $socket = @stream_socket_client($scheme . $host . ':' . $port, $errno, $errstr, $timeout > 0 ? $timeout : 15);
    if (!$socket) {
        return ['ok' => false, 'message' => 'تعذر الاتصال بخادم SMTP: ' . ($errstr !== '' ? $errstr : 'خطأ غير معروف')];
    }

    stream_set_timeout($socket, (int)max(5, $timeout));

    try {
        smtp_expect($socket, [220]);
        $localHost = gethostname() ?: 'localhost';
        smtp_write($socket, 'EHLO ' . $localHost);
        smtp_expect($socket, [250]);

        if ($encryption === 'tls') {
            smtp_write($socket, 'STARTTLS');
            smtp_expect($socket, [220]);
            if (!@stream_socket_enable_crypto($socket, true, STREAM_CRYPTO_METHOD_TLS_CLIENT)) {
                throw new RuntimeException('فشل تفعيل تشفير TLS مع خادم SMTP.');
            }
            smtp_write($socket, 'EHLO ' . $localHost);
            smtp_expect($socket, [250]);
        }

        smtp_write($socket, 'AUTH LOGIN');
        smtp_expect($socket, [334]);
        smtp_write($socket, base64_encode($username));
        smtp_expect($socket, [334]);
        smtp_write($socket, base64_encode($password));
        smtp_expect($socket, [235]);

        smtp_write($socket, 'MAIL FROM:<' . $fromEmail . '>');
        smtp_expect($socket, [250]);
        smtp_write($socket, 'RCPT TO:<' . $to . '>');
        smtp_expect($socket, [250, 251]);
        smtp_write($socket, 'DATA');
        smtp_expect($socket, [354]);
        smtp_write($socket, smtp_escape_data($message) . "
.");
        smtp_expect($socket, [250]);
        smtp_write($socket, 'QUIT');
        @fclose($socket);
        return ['ok' => true, 'message' => 'تم إرسال البريد بنجاح عبر SMTP.', 'transport' => 'smtp'];
    } catch (Throwable $e) {
        @fclose($socket);
        return ['ok' => false, 'message' => 'فشل إرسال البريد عبر SMTP: ' . $e->getMessage()];
    }
}

function smtp_format_address(string $email, string $name = ''): string {
    if ($name === '') {
        return $email;
    }
    return '=?UTF-8?B?' . base64_encode($name) . '?= <' . $email . '>';
}

function smtp_normalize_body(string $body): string {
    $body = str_replace(["
", "
"], "
", $body);
    return str_replace("
", "
", $body);
}

function smtp_escape_data(string $message): string {
    $message = smtp_normalize_body($message);
    $lines = explode("\r\n", $message);
    foreach ($lines as &$line) {
        if (str_starts_with($line, '.')) {
            $line = '.' . $line;
        }
    }
    unset($line);
    return implode("\r\n", $lines);
}

function smtp_write($socket, string $command): void {
    fwrite($socket, $command . "\r\n");
}

function smtp_expect($socket, array $expectedCodes): string {
    $response = '';
    while (($line = fgets($socket, 515)) !== false) {
        $response .= $line;
        if (preg_match('/^([0-9]{3})(\s|-)/', $line, $m)) {
            if (($m[2] ?? ' ') === ' ') {
                break;
            }
        }
    }
    $code = (int)substr(trim($response), 0, 3);
    if (!in_array($code, $expectedCodes, true)) {
        throw new RuntimeException(trim($response) !== '' ? trim($response) : 'استجابة SMTP غير متوقعة');
    }
    return $response;
}
