<?php
// Suppress default mysqli exception throwing to handle errors gracefully
mysqli_report(MYSQLI_REPORT_OFF);

$host = "localhost";
$username = "root";
$password = "";
$dbname = "aluaa";

$db_connection_error = null;
$conn = null;

try {
    // Attempt connection directly to database
    $conn = @new mysqli($host, $username, $password, $dbname);

    // If connection failed, fallback to connecting without database select
    if ($conn->connect_error) {
        $conn = @new mysqli($host, $username, $password);
        if ($conn->connect_error) {
            $db_connection_error = "فشل الاتصال بخادم MySQL: يرجى إدخال بيانات قاعدة البيانات الصحيحة في ملف includes/db.php";
            $conn = false;
        } else {
            // Create database if not exists
            @$conn->query("CREATE DATABASE IF NOT EXISTS `$dbname` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
            if (!@$conn->select_db($dbname)) {
                $db_connection_error = "تعذر الوصول لقاعدة البيانات '$dbname'. يرجى إنشاؤها عبر لوحة التحكم (cPanel) وتحديث بيانات includes/db.php";
                $conn = false;
            }
        }
    }
} catch (Throwable $e) {
    $db_connection_error = "خطأ في الاتصال بقاعدة البيانات: " . $e->getMessage();
    $conn = false;
}

// Prevent HTTP caching on all API & database operations
if (!headers_sent()) {
    header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
    header('Cache-Control: post-check=0, pre-check=0', false);
    header('Pragma: no-cache');
}

// Enforce Iraq / Basra Local Timezone (UTC+3) for PHP and MySQL
date_default_timezone_set('Asia/Baghdad');

if ($conn && !$db_connection_error) {
    // Ensure connection charset & timezone
    @$conn->set_charset("utf8mb4");
    @$conn->query("SET time_zone = '+03:00'");
    @$conn->query("CREATE TABLE IF NOT EXISTS `system_settings` (`id` INT PRIMARY KEY, `org_name` VARCHAR(255) NOT NULL, `org_email` VARCHAR(255) NULL, `currency` VARCHAR(50) DEFAULT 'د.ع') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    @$conn->query("INSERT INTO `system_settings` (`id`, `org_name`, `org_email`, `currency`) VALUES (1, 'مكتب الحاج أمجد سهل عبدالامير', 'info@aluaa.com', 'د.ع') ON DUPLICATE KEY UPDATE `org_name` = IF(`org_name` IS NULL OR `org_name` = '' OR `org_name` = 'شركة عوا للتجارة' OR `org_name` = 'اسم المنشأة' OR `org_name` = 'مستودع الألمنيوم', 'مكتب الحاج أمجد سهل عبدالامير', `org_name`)");
}

// Create gatherers table if not exists
$sql_table = "CREATE TABLE IF NOT EXISTS `gatherers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table);

// Check if notes column exists, if not create it
$check_col = $conn->query("SHOW COLUMNS FROM `gatherers` LIKE 'notes'");
if ($check_col && $check_col->num_rows == 0) {
    $conn->query("ALTER TABLE `gatherers` ADD `notes` TEXT NULL AFTER `phone`");
}

// Create materials table if not exists
$sql_table_materials = "CREATE TABLE IF NOT EXISTS `materials` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `accounting_type` VARCHAR(100) NOT NULL,
    `commission_type` VARCHAR(100) NOT NULL,
    `worker_share_type` VARCHAR(100) NOT NULL,
    `jallab_id` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table_materials);

// Create safes table if not exists
$sql_table_safes = "CREATE TABLE IF NOT EXISTS `safes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `type` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table_safes);

// Create safe_transactions table if not exists
$sql_table_transactions = "CREATE TABLE IF NOT EXISTS `safe_transactions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `safe_id` INT NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `type` VARCHAR(50) NOT NULL,
    `date` DATE NOT NULL,
    `amount` DECIMAL(15, 2) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`safe_id`) REFERENCES `safes`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table_transactions);

// Seeding logic removed to allow clean database execution.

// Check if debtor_id column exists, if not create it
$check_tx_col = $conn->query("SHOW COLUMNS FROM `safe_transactions` LIKE 'debtor_id'");
if ($check_tx_col && $check_tx_col->num_rows == 0) {
    $conn->query("ALTER TABLE `safe_transactions` ADD `debtor_id` VARCHAR(50) NULL AFTER `amount`");
}

// Check if form_id column exists, if not create it
$check_tx_form_col = $conn->query("SHOW COLUMNS FROM `safe_transactions` LIKE 'form_id'");
if ($check_tx_form_col && $check_tx_form_col->num_rows == 0) {
    $conn->query("ALTER TABLE `safe_transactions` ADD `form_id` VARCHAR(50) NULL AFTER `debtor_id`");
}

// Create customers table if not exists
$sql_table_customers = "CREATE TABLE IF NOT EXISTS `customers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(50) NULL,
    `notes` TEXT NULL,
    `status` VARCHAR(20) DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table_customers);

// Create forms table if not exists
$sql_table_forms = "CREATE TABLE IF NOT EXISTS `forms` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `customer_name` VARCHAR(255) NOT NULL,
    `materials_count` INT DEFAULT 0,
    `status` VARCHAR(50) DEFAULT '0',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table_forms);

// Check if grand_total column exists in forms table, if not create it
$check_forms_grand = $conn->query("SHOW COLUMNS FROM `forms` LIKE 'grand_total'");
if ($check_forms_grand && $check_forms_grand->num_rows == 0) {
    $conn->query("ALTER TABLE `forms` ADD `grand_total` DECIMAL(15, 2) DEFAULT 1000.00 AFTER `status`");
}

// Check if user_id column exists in forms table, if not create it
$check_forms_user = $conn->query("SHOW COLUMNS FROM `forms` LIKE 'user_id'");
if ($check_forms_user && $check_forms_user->num_rows == 0) {
    $conn->query("ALTER TABLE `forms` ADD `user_id` INT NULL AFTER `customer_name`");
}

// Create form_items table if not exists
$sql_table_form_items = "CREATE TABLE IF NOT EXISTS `form_items` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `form_id` INT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `quantity` INT DEFAULT 0,
    `weight` DECIMAL(10,2) NULL,
    `price` DECIMAL(15,2) DEFAULT 0,
    `worker_share` DECIMAL(15,2) DEFAULT 0,
    `commission` DECIMAL(15,2) DEFAULT 0,
    `commission_10` DECIMAL(15,2) DEFAULT 0,
    `total` DECIMAL(15,2) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`form_id`) REFERENCES `forms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table_form_items);

// Alter form_items: drop pieces if exists
$check_pieces = $conn->query("SHOW COLUMNS FROM `form_items` LIKE 'pieces'");
if ($check_pieces && $check_pieces->num_rows > 0) {
    $conn->query("ALTER TABLE `form_items` DROP COLUMN `pieces`");
}

// Alter form_items: add commission_10 if not exists
$check_comm10 = $conn->query("SHOW COLUMNS FROM `form_items` LIKE 'commission_10'");
if ($check_comm10 && $check_comm10->num_rows == 0) {
    $conn->query("ALTER TABLE `form_items` ADD `commission_10` DECIMAL(15, 2) DEFAULT 0.00 AFTER `commission`");
}

// Create debtors table if not exists
$sql_table_debtors = "CREATE TABLE IF NOT EXISTS `debtors` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table_debtors);

// Create users table if not exists
$sql_table_users = "CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `username` VARCHAR(150) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `status` ENUM('active', 'suspended') DEFAULT 'active',
    `permissions` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table_users);

// Create calibrations table if not exists
$sql_table_calibrations = "CREATE TABLE IF NOT EXISTS `calibrations` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `device_name` VARCHAR(255) NOT NULL,
    `device_code` VARCHAR(100) NULL,
    `calibration_type` VARCHAR(100) NOT NULL,
    `reference_value` DECIMAL(15, 3) NOT NULL DEFAULT 0.000,
    `measured_value` DECIMAL(15, 3) NOT NULL DEFAULT 0.000,
    `variance` DECIMAL(15, 3) NOT NULL DEFAULT 0.000,
    `unit` VARCHAR(50) DEFAULT 'كجم',
    `status` VARCHAR(50) DEFAULT 'calibrated',
    `calibration_date` DATE NOT NULL,
    `next_calibration_date` DATE NULL,
    `technician` VARCHAR(255) NULL,
    `notes` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table_calibrations);

// Seed sample calibrations if table is empty
$check_calibrations = $conn->query("SELECT COUNT(*) AS c FROM `calibrations`");
$calib_count = $check_calibrations ? (int)$check_calibrations->fetch_assoc()['c'] : 0;
if ($calib_count == 0) {
    $conn->query("INSERT INTO `calibrations` (`device_name`, `device_code`, `calibration_type`, `reference_value`, `measured_value`, `variance`, `unit`, `status`, `calibration_date`, `next_calibration_date`, `technician`, `notes`) VALUES
    ('الميزان الإلكتروني الرئيسي', 'SCALE-001', 'معايرة وزن قياسي', 100.000, 100.000, 0.000, 'كجم', 'calibrated', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 6 MONTH), 'مهندس الصيانة', 'تم إجراء ضبط الصفر والمعايرة بنجاح'),
    ('ميزان المنصة رقم 2', 'SCALE-002', 'معايرة حمل ثقيل', 500.000, 500.200, 0.200, 'كجم', 'calibrated', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 3 MONTH), 'قسم الجودة', 'نسبة الانحراف ضمن الحد المسموح به'),
    ('ميزان القبانات الفرعي', 'SCALE-003', 'معايرة حساسية', 50.000, 50.800, 0.800, 'needs_adjustment', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 MONTH), 'فني المعايرة', 'يحتاج إعادة ضبط الخلايا الحساسة')");
}
// Auto-migration: ensure calibration permission key exists for existing users
$users_res = $conn->query("SELECT id, permissions FROM users");
if ($users_res) {
    while ($u = $users_res->fetch_assoc()) {
        $p = json_decode($u['permissions'], true) ?: [];
        if (!isset($p['calibration'])) {
            $p['calibration'] = ['view' => true, 'add' => true, 'edit' => true, 'delete' => true];
            $p_json = json_encode($p, JSON_UNESCAPED_UNICODE);
            $conn->query("UPDATE users SET permissions = '" . $conn->real_escape_string($p_json) . "' WHERE id = " . intval($u['id']));
        }
    }
}

// Create commission_options table if not exists
$sql_table_commission_options = "CREATE TABLE IF NOT EXISTS `commission_options` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `option_value` VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table_commission_options);

// Create worker_share_options table if not exists
$sql_table_worker_share_options = "CREATE TABLE IF NOT EXISTS `worker_share_options` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `option_value` VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$conn->query($sql_table_worker_share_options);

// Seed default commissions if table is empty
$check_comm_opts = $conn->query("SELECT COUNT(*) AS c FROM `commission_options`");
$comm_opts_count = $check_comm_opts ? (int)$check_comm_opts->fetch_assoc()['c'] : 0;
if ($comm_opts_count == 0) {
    $default_comms = ["اضافة 10%", "عدد * 1000", "عدد * 250", "عدد * 1500", "الوزن * 30"];
    foreach ($default_comms as $val) {
        $stmt = $conn->prepare("INSERT INTO `commission_options` (option_value) VALUES (?)");
        $stmt->bind_param('s', $val);
        $stmt->execute();
        $stmt->close();
    }
}

// Seed default worker shares if table is empty
$check_worker_opts = $conn->query("SELECT COUNT(*) AS c FROM `worker_share_options`");
$worker_opts_count = $check_worker_opts ? (int)$check_worker_opts->fetch_assoc()['c'] : 0;
if ($worker_opts_count == 0) {
    $default_workers = ["العدد * 100", "العدد * 200", "العدد * 50", "الوزن * 30"];
    foreach ($default_workers as $val) {
        $stmt = $conn->prepare("INSERT INTO `worker_share_options` (option_value) VALUES (?)");
        $stmt->bind_param('s', $val);
        $stmt->execute();
        $stmt->close();
    }
}

// Seed default administrator if users table is empty
$check_users = $conn->query("SELECT COUNT(*) AS c FROM `users`");
$users_count = $check_users ? (int)$check_users->fetch_assoc()['c'] : 0;
if ($users_count == 0) {
    $default_perms = json_encode([
        'index' => ['view' => true, 'add' => false, 'edit' => false, 'delete' => false],
        'gatherers' => ['view' => true, 'add' => true, 'edit' => true, 'delete' => true],
        'customers' => ['view' => true, 'add' => true, 'edit' => true, 'delete' => true],
        'safes' => ['view' => true, 'add' => true, 'edit' => true, 'delete' => true],
        'materials' => ['view' => true, 'add' => true, 'edit' => true, 'delete' => true],
        'materials_report' => ['view' => true, 'add' => false, 'edit' => false, 'delete' => false],
        'gatherers_report' => ['view' => true, 'add' => false, 'edit' => false, 'delete' => false],
        'settlements' => ['view' => true, 'add' => true, 'edit' => true, 'delete' => true],
        'forms' => ['view' => true, 'add' => true, 'edit' => true, 'delete' => true],
        'pricing' => ['view' => true, 'add' => false, 'edit' => true, 'delete' => false],
        'debts' => ['view' => true, 'add' => true, 'edit' => true, 'delete' => true],
        'users' => ['view' => true, 'add' => true, 'edit' => true, 'delete' => true],
        'activity_logs' => ['view' => true, 'add' => false, 'edit' => false, 'delete' => false],
        'settings' => ['view' => true, 'add' => true, 'edit' => true, 'delete' => true]
    ], JSON_UNESCAPED_UNICODE);
    
    $hashed_pass = password_hash('admin', PASSWORD_DEFAULT);
    $stmt = $conn->prepare("INSERT INTO `users` (name, username, password, status, permissions) VALUES ('المدير العام', 'admin', ?, 'active', ?)");
    $stmt->bind_param('ss', $hashed_pass, $default_perms);
    $stmt->execute();
    $stmt->close();
}

// Dynamic Migration: Ensure existing admin has the new page permissions
$check_admin_perms = $conn->query("SELECT id, permissions FROM `users` WHERE `username` = 'admin'");
if ($check_admin_perms && $row = $check_admin_perms->fetch_assoc()) {
    $perms = json_decode($row['permissions'], true);
    if ($perms && !isset($perms['settlements'])) {
        $perms['settlements'] = ['view' => true, 'add' => true, 'edit' => true, 'delete' => true];
        $perms['pricing'] = ['view' => true, 'add' => false, 'edit' => true, 'delete' => false];
        $perms['activity_logs'] = ['view' => true, 'add' => false, 'edit' => false, 'delete' => false];
        $new_perms = json_encode($perms, JSON_UNESCAPED_UNICODE);
        $stmt = $conn->prepare("UPDATE `users` SET `permissions` = ? WHERE `id` = ?");
        $stmt->bind_param('si', $new_perms, $row['id']);
        $stmt->execute();
        $stmt->close();
    }
}

// Arabic Name Normalization Helper Function
if (!function_exists('normalizeArabicName')) {
    function normalizeArabicName($name) {
        if (empty($name)) return '';
        $name = mb_strtolower($name, 'UTF-8');
        // Remove Arabic Tashkeel / Diacritics
        $name = preg_replace('/[\x{064B}-\x{0652}\x{0670}]/u', '', $name);
        // Replace Alif variations (أ, إ, آ, ٱ) with plain Alif (ا)
        $name = preg_replace('/[أإآٱ]/u', 'ا', $name);
        // Replace Taa Marbouta (ة) with Ha (ه)
        $name = preg_replace('/ة/u', 'ه', $name);
        // Replace Alif Maqsura (ى) with Ya (ي)
        $name = preg_replace('/ى/u', 'ي', $name);
        // Replace Hamza variations (ؤ, ئ)
        $name = preg_replace('/ؤ/u', 'و', $name);
        $name = preg_replace('/ئ/u', 'ي', $name);
        // Normalize multiple whitespace into single space
        $name = preg_replace('/\s+/u', ' ', $name);
        return trim($name);
    }
}

// Create database indexes for performance optimization
if (!function_exists('createIndexIfNotExists')) {
    function createIndexIfNotExists($conn, $table, $indexName, $columns) {
        $check = $conn->query("SHOW INDEX FROM `$table` WHERE Key_name = '$indexName'");
        if ($check && $check->num_rows == 0) {
            @$conn->query("CREATE INDEX `$indexName` ON `$table` ($columns)");
        }
    }
}

createIndexIfNotExists($conn, 'safe_transactions', 'idx_st_debtor_id', '`debtor_id`');
createIndexIfNotExists($conn, 'safe_transactions', 'idx_st_form_id', '`form_id`');
createIndexIfNotExists($conn, 'safe_transactions', 'idx_st_type', '`type`');
createIndexIfNotExists($conn, 'forms', 'idx_forms_status', '`status`');
createIndexIfNotExists($conn, 'forms', 'idx_forms_customer_name', '`customer_name`');
createIndexIfNotExists($conn, 'debtors', 'idx_debtors_name', '`name`');

function calcFormulaPHP($formula, $qty, $weight, $price, $accounting_type = '') {
    if (empty($formula) || trim($formula) === '---') return 0.0;
    
    if (strpos($formula, '+') !== false) {
        $parts = explode('+', $formula);
        $sum = 0.0;
        foreach ($parts as $part) {
            $sum += calcFormulaPHP($part, $qty, $weight, $price, $accounting_type);
        }
        return $sum;
    }
    
    $f = trim($formula);

    // Pattern: "اضافة X%" or "X%"
    if (preg_match('/(\d+(?:\.\d+)?)\s*%/', $f, $matches)) {
        $subtotal = $accounting_type === "وزن * سعر" ? $weight * $price : ($accounting_type === "عدد * سعر" ? $qty * $price : ($weight > 0 ? $weight * $price : $qty * $price));
        return $subtotal * (float)$matches[1] / 100.0;
    }

    // Pattern: "عدد * X" or "العدد * X"
    if (preg_match('/(?:عدد|العدد)\s*[\*×x]\s*(\d+(?:\.\d+)?)/u', $f, $matches)) {
        return $qty * (float)$matches[1];
    }

    // Pattern: "وزن * X" or "الوزن * X"
    if (preg_match('/(?:وزن|الوزن)\s*[\*×x]\s*(\d+(?:\.\d+)?)/u', $f, $matches)) {
        $val = $weight > 0 ? $weight : $qty;
        return $val * (float)$matches[1];
    }

    // Pattern: plain number
    if (preg_match('/^(\d+(?:\.\d+)?)$/', $f, $matches)) {
        return (float)$matches[1];
    }

    return 0.0;
}

function calculateCommissionPartsPHP($formula, $qty, $weight, $price, $accounting_type = '') {
    if (empty($formula) || trim($formula) === '---') {
        return ['commission' => 0.0, 'commission_10' => 0.0];
    }
    
    $parts = explode('+', $formula);
    $commission = 0.0;
    $commission_10 = 0.0;
    
    foreach ($parts as $part) {
        $trimmed = trim($part);
        // فحص إذا كان هذا الجزء يحتوي على نسبة مئوية (مثل 10% أو 20% أو إضافة 10% أو إضافة 20%)
        if (preg_match('/(\d+(?:\.\d+)?)\s*%/', $trimmed, $matches)) {
            $pct = (float)$matches[1];
            if ($accounting_type === "وزن * سعر") {
                $base = $weight * $price;
            } elseif ($accounting_type === "عدد * سعر") {
                $base = $qty * $price;
            } else {
                $base = ($weight > 0) ? ($weight * $price) : ($qty * $price);
            }
            $commission_10 += $base * $pct / 100.0;
        } else {
            $commission += calcFormulaPHP($trimmed, $qty, $weight, $price, $accounting_type);
        }
    }
    
    return ['commission' => $commission, 'commission_10' => $commission_10];
}

function calculateFormTotalPHP($form_id, $conn) {
    $stmt = $conn->prepare("
        SELECT fi.*,
               m.accounting_type,
               m.commission_type,
               m.worker_share_type
        FROM form_items fi
        LEFT JOIN (
            SELECT m1.name, m1.accounting_type, m1.commission_type, m1.worker_share_type
            FROM materials m1
            INNER JOIN (
                SELECT name, MAX(id) AS max_id
                FROM materials
                GROUP BY name
            ) m2 ON m1.id = m2.max_id
        ) m ON fi.name = m.name
        WHERE fi.form_id = ?
    ");
    if (!$stmt) return 1000.0;
    
    $stmt->bind_param('i', $form_id);
    $stmt->execute();
    $res = $stmt->get_result();
    $stmt->close();

    $tot_subtotal = 0.0;
    $tot_worker = 0.0;
    $tot_commission = 0.0;
    $tot_commission_10 = 0.0;
    
    while ($item = $res->fetch_assoc()) {
        $weight       = (float)($item['weight'] ?? 0);
        $qty          = (int)($item['quantity'] ?? 0);
        $price        = (float)($item['price'] ?? 0);
        $acc_type_mat = $item['accounting_type'] ?? '';
        $comm_type    = $item['commission_type']  ?? '';
        $work_type    = $item['worker_share_type']?? '';

        // إذا كانت المادة لها نوع محاسبة من جدول المواد → أعد الحساب بدقة
        if (!empty($acc_type_mat)) {
            $subtotal      = ($acc_type_mat === "وزن * سعر") ? ($weight * $price) : ($qty * $price);
            $comm_parts    = calculateCommissionPartsPHP($comm_type, $qty, $weight, $price, $acc_type_mat);
            $commission    = $comm_parts['commission'];
            $commission_10 = $comm_parts['commission_10'];
            $worker        = calcFormulaPHP($work_type, $qty, $weight, $price, $acc_type_mat);
        } else {
            // مادة يدوية (غير مسجلة في جدول المواد) — استخدم القيم المخزونة
            $commission    = (float)($item['commission']    ?? 0.0);
            $commission_10 = (float)($item['commission_10'] ?? 0.0);
            $worker        = (float)($item['worker_share']  ?? 0.0);
            // حساب الـ subtotal من الـ total المخزون أو من عدد × سعر
            if (isset($item['total']) && (float)$item['total'] > 0.01) {
                $subtotal = (float)$item['total'] - $commission - $commission_10;
            } else {
                $subtotal = $qty * $price;
            }
        }
        
        $tot_subtotal      += $subtotal;
        $tot_commission    += $commission;
        $tot_commission_10 += $commission_10;
        $tot_worker        += $worker;
    }
    
    $FORM_FEE = 1000.0;
    return $tot_subtotal + $tot_commission + $tot_commission_10 + $FORM_FEE;
}

function allocateCustomerPayment($conn, $customer_name, $amount, $safe_id, $date, $description, $debtor_id = null, $target_form_id = null) {
    $norm_target_name = normalizeArabicName($customer_name);

    // 1. Fetch all active (non-cancelled) forms for this customer name, oldest first
    $res = $conn->query("
        SELECT f.id, f.customer_name, f.grand_total, COALESCE(paid_sum.paid, 0) AS total_paid
        FROM forms f
        LEFT JOIN (
            SELECT form_id, SUM(CASE WHEN type = 'deposit' OR type = 'repayment' THEN amount WHEN type = 'withdrawal' OR type = 'debt' THEN -amount ELSE 0 END) AS paid
            FROM safe_transactions
            WHERE form_id IS NOT NULL AND form_id != ''
            GROUP BY form_id
        ) paid_sum ON f.id = paid_sum.form_id
        WHERE f.status != 'cancelled'
        ORDER BY f.created_at ASC, f.id ASC
    ");
    $forms_to_pay = [];
    if ($res) {
        while ($f = $res->fetch_assoc()) {
            if (normalizeArabicName($f['customer_name']) === $norm_target_name) {
                $grand_total = calculateFormTotalPHP($f['id'], $conn);
                $total_paid = (float)$f['total_paid'];
                $balance = $grand_total - $total_paid;
                if ($balance > 0.01) {
                    $forms_to_pay[] = [
                        'id' => $f['id'],
                        'balance' => $balance,
                        'grand_total' => $grand_total
                    ];
                }
            }
        }
    }

    // If a target form is specified, prioritize it first
    if ($target_form_id) {
        usort($forms_to_pay, function($a, $b) use ($target_form_id) {
            if ((string)$a['id'] === (string)$target_form_id) return -1;
            if ((string)$b['id'] === (string)$target_form_id) return 1;
            return 0;
        });
    }

    // Calculate total forms debt
    $total_forms_debt = 0.0;
    foreach ($forms_to_pay as $ftp) {
        $total_forms_debt += $ftp['balance'];
    }

    // Resolve debtor ID to a real ID to associate with safe transactions (if not provided)
    if (empty($debtor_id)) {
        $deb_res = $conn->query("SELECT id, name FROM debtors");
        if ($deb_res) {
            while ($dr = $deb_res->fetch_assoc()) {
                if (normalizeArabicName($dr['name']) === $norm_target_name) {
                    $debtor_id = (string)$dr['id'];
                    break;
                }
            }
        }
        if (empty($debtor_id)) {
            // Find phone from customers
            $phone = '';
            $cust_res = $conn->query("SELECT name, phone FROM customers WHERE phone IS NOT NULL AND phone != ''");
            if ($cust_res) {
                while ($c_row = $cust_res->fetch_assoc()) {
                    if (normalizeArabicName($c_row['name']) === $norm_target_name) {
                        $phone = $c_row['phone'];
                        break;
                    }
                }
            }
            if (empty($phone)) {
                $phone = 'من سجل الفواتير';
            }
            
            $stmt = $conn->prepare("INSERT INTO debtors (name, phone, user_id) VALUES (?, ?, ?)");
            $my_user_id = isset($_SESSION['user']['id']) ? (int)$_SESSION['user']['id'] : 1;
            $stmt->bind_param('ssi', $customer_name, $phone, $my_user_id);
            $stmt->execute();
            $debtor_id = (string)$conn->insert_id;
            $stmt->close();
        }
    }

    // Calculate manual debt balance for this debtor
    $manual_debt = 0.0;
    if (!empty($debtor_id)) {
        $r_m = $conn->query("
            SELECT COALESCE(SUM(CASE WHEN type = 'withdrawal' OR type = 'debt' THEN amount ELSE -amount END), 0) AS m_bal
            FROM safe_transactions
            WHERE debtor_id = '" . $conn->real_escape_string($debtor_id) . "'
            AND (form_id IS NULL OR form_id = '' OR form_id = '0')
        ");
        if ($r_m && $row_m = $r_m->fetch_assoc()) {
            $manual_debt = max(0.0, (float)$row_m['m_bal']);
        }
    }

    $total_debt = $total_forms_debt + $manual_debt;

    if ($amount > ($total_debt + 0.01)) {
        $fmt_amount = number_format($amount, 0);
        $fmt_debt = number_format(max(0, $total_debt), 0);
        throw new Exception("المبلغ المدخل ($fmt_amount) أكبر من إجمالي الدين المستحق ($fmt_debt). لا يمكن تسديد مبلغ أكبر من قيمة الدين.");
    }

    $remaining_amount = $amount;

    foreach ($forms_to_pay as $ftp) {
        if ($remaining_amount <= 0.01) {
            break;
        }

        $pay_to_this_form = min($remaining_amount, $ftp['balance']);
        
        // Insert safe transaction for this form payment
        $stmt = $conn->prepare("
            INSERT INTO safe_transactions (safe_id, description, type, date, amount, debtor_id, form_id, user_id)
            VALUES (?, ?, 'deposit', ?, ?, ?, ?, ?)
        ");
        
        $desc = $description . " (تسديد جزء من الفاتورة #" . $ftp['id'] . ")";
        if (abs($pay_to_this_form - $ftp['balance']) <= 0.01) {
            $desc = $description . " (تسديد كامل الفاتورة #" . $ftp['id'] . ")";
        }
        $my_user_id = isset($_SESSION['user']['id']) ? (int)$_SESSION['user']['id'] : 1;
        $stmt->bind_param('issdsii', $safe_id, $desc, $date, $pay_to_this_form, $debtor_id, $ftp['id'], $my_user_id);
        $stmt->execute();
        $stmt->close();

        // Calculate and update status
        $form_total_paid = ($ftp['grand_total'] - $ftp['balance']) + $pay_to_this_form;
        if ($form_total_paid >= ($ftp['grand_total'] - 0.01)) {
            $status = '1';
        } else {
            $status = '2';
        }
        $conn->query("UPDATE forms SET status = '$status', grand_total = '{$ftp['grand_total']}' WHERE id = '{$ftp['id']}'");

        $remaining_amount -= $pay_to_this_form;
    }

    // If there is still remaining amount, save it to manual debt up to the actual manual debt amount
    if ($remaining_amount > 0.01) {
        $pay_to_manual = min($remaining_amount, $manual_debt);
        if ($pay_to_manual > 0.01) {
            $stmt = $conn->prepare("
                INSERT INTO safe_transactions (safe_id, description, type, date, amount, debtor_id, form_id, user_id)
                VALUES (?, ?, 'deposit', ?, ?, ?, NULL, ?)
            ");
            $my_user_id = isset($_SESSION['user']['id']) ? (int)$_SESSION['user']['id'] : 1;
            $stmt->bind_param('issdsi', $safe_id, $description, $date, $pay_to_manual, $debtor_id, $my_user_id);
            $stmt->execute();
            $stmt->close();
        }
    }
}

/**
 * Log user activity in activity_logs
 */
function logActivity($conn, $action, $tableName, $recordId, $oldData = null, $newData = null) {
    ob_start();
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
    $userId = isset($_SESSION['user']['id']) ? (int)$_SESSION['user']['id'] : 1;

    $details = [];
    if ($action === 'update' && is_array($oldData) && is_array($newData)) {
        $details = getDiffData($oldData, $newData);
        // If no changes, skip logging
        if (empty($details['new'])) {
            return;
        }
    } else {
        if ($oldData !== null || $newData !== null) {
            $details = [
                'old' => $oldData,
                'new' => $newData
            ];
        }
    }

    $detailsJson = json_encode($details, JSON_UNESCAPED_UNICODE);
    
    $stmt = $conn->prepare("INSERT INTO activity_logs (user_id, action, table_name, record_id, details) VALUES (?, ?, ?, ?, ?)");
    $stmt->bind_param('issss', $userId, $action, $tableName, $recordId, $detailsJson);
    $stmt->execute();
    $stmt->close();
}

/**
 * Helper to compute differences between two associative arrays
 */
function getDiffData($old, $new) {
    $oldDiff = [];
    $newDiff = [];
    foreach ($new as $key => $val) {
        if (!array_key_exists($key, $old) || $old[$key] !== $val) {
            $newDiff[$key] = $val;
            $oldDiff[$key] = isset($old[$key]) ? $old[$key] : null;
        }
    }
    return [
        'old' => $oldDiff,
        'new' => $newDiff
    ];
}

/**
 * Update the last_updated timestamp for a table in system_states
 */
function updateSystemState($conn, $tableName) {
    if (!$conn) return;
    $stmt = $conn->prepare("INSERT INTO system_states (table_name, last_updated, version) VALUES (?, CURRENT_TIMESTAMP, 1) ON DUPLICATE KEY UPDATE last_updated = CURRENT_TIMESTAMP, version = version + 1");
    if ($stmt) {
        $stmt->bind_param('s', $tableName);
        $stmt->execute();
        $stmt->close();
    }
}

/**
 * Fetch Organization / Facility Name dynamically from system_settings table
 */
function getOrgName($conn = null) {
    global $conn;
    static $cachedOrgName = null;
    if ($cachedOrgName !== null) {
        return $cachedOrgName;
    }
    
    if (isset($conn) && $conn) {
        $res = $conn->query("SELECT org_name FROM system_settings WHERE id = 1 LIMIT 1");
        if ($res && $row = $res->fetch_assoc()) {
            if (!empty(trim($row['org_name']))) {
                $cachedOrgName = trim($row['org_name']);
                return $cachedOrgName;
            }
        }
    }
    $cachedOrgName = "مكتب الحاج أمجد سهل عبدالامير";
    return $cachedOrgName;
}

/**
 * Generates a full MySQL Database SQL Dump string with CREATE TABLE & INSERT statements
 */
if (!function_exists('generateSqlDump')) {
    function generateSqlDump($conn) {
        if (!$conn) return '';
        $tables = ['system_settings', 'users', 'gatherers', 'materials', 'commission_options', 'worker_share_options', 'safes', 'safe_transactions', 'customers', 'forms', 'form_items', 'debtors', 'activity_logs', 'calibrations', 'system_states'];
        
        $sql  = "-- ========================================================\n";
        $sql .= "-- Aluaa ERP System MySQL Database Backup Dump\n";
        $sql .= "-- Generated: " . date('Y-m-d H:i:s') . "\n";
        $sql .= "-- Timezone: Asia/Baghdad (+03:00)\n";
        $sql .= "-- ========================================================\n\n";
        $sql .= "SET FOREIGN_KEY_CHECKS = 0;\n";
        $sql .= "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\n";
        $sql .= "SET time_zone = \"+03:00\";\n\n";

        foreach ($tables as $tbl) {
            $tblCheck = $conn->query("SHOW TABLES LIKE '$tbl'");
            if (!$tblCheck || $tblCheck->num_rows == 0) continue;

            $createRes = $conn->query("SHOW CREATE TABLE `$tbl`");
            if ($createRes && $row = $createRes->fetch_row()) {
                $sql .= "-- --------------------------------------------------------\n";
                $sql .= "-- Table structure for `$tbl`\n";
                $sql .= "-- --------------------------------------------------------\n";
                $sql .= "DROP TABLE IF EXISTS `$tbl`;\n";
                $sql .= $row[1] . ";\n\n";
            }

            $res = $conn->query("SELECT * FROM `$tbl` ORDER BY id ASC");
            if ($res && $res->num_rows > 0) {
                $sql .= "-- Dumping data for `$tbl`\n";
                while ($r = $res->fetch_assoc()) {
                    $keys = array_keys($r);
                    $escapedKeys = array_map(function($k) { return "`$k`"; }, $keys);
                    $escapedValues = array_map(function($v) use ($conn) {
                        if ($v === null) return "NULL";
                        return "'" . $conn->real_escape_string($v) . "'";
                    }, array_values($r));
                    
                    $sql .= "INSERT INTO `$tbl` (" . implode(', ', $escapedKeys) . ") VALUES (" . implode(', ', $escapedValues) . ");\n";
                }
                $sql .= "\n";
            }
        }

        $sql .= "SET FOREIGN_KEY_CHECKS = 1;\n";
        return $sql;
    }
}

/**
 * Automatically perform daily database backup into backups/ directory (saves both JSON & MySQL SQL)
 */
function performAutoBackupIfNeeded($dbConn = null) {
    global $conn;
    $activeConn = $dbConn ?? $conn;
    if (!$activeConn) return false;

    $today = date('Y-m-d');
    $backupDir = __DIR__ . '/../backups';
    if (!file_exists($backupDir)) {
        @mkdir($backupDir, 0755, true);
    }
    
    $autoBackupFile = $backupDir . "/auto_backup_{$today}.json";
    $autoBackupSqlFile = $backupDir . "/auto_backup_{$today}.sql";

    if (!file_exists($autoBackupFile)) {
        $tables = ['system_settings', 'users', 'gatherers', 'materials', 'commission_options', 'worker_share_options', 'safes', 'safe_transactions', 'customers', 'forms', 'form_items', 'debtors', 'activity_logs', 'calibrations', 'system_states'];
        $backup = [];
        $counts = [];

        foreach ($tables as $tbl) {
            $tblCheck = $activeConn->query("SHOW TABLES LIKE '$tbl'");
            if ($tblCheck && $tblCheck->num_rows > 0) {
                $res = $activeConn->query("SELECT * FROM `$tbl` ORDER BY id ASC");
                $rows = [];
                if ($res) {
                    while ($r = $res->fetch_assoc()) {
                        $rows[] = $r;
                    }
                }
                $backup[$tbl] = $rows;
                $counts[$tbl] = count($rows);
            }
        }

        $payload = [
            'meta' => [
                'exported_at' => date('Y-m-d H:i:s'),
                'type' => 'auto',
                'system' => 'Aluaa ERP System Auto-Backup',
                'record_counts' => $counts
            ],
            'data' => $backup
        ];

        @file_put_contents($autoBackupFile, json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));
    }

    if (!file_exists($autoBackupSqlFile)) {
        @file_put_contents($autoBackupSqlFile, generateSqlDump($activeConn));
    }
    
    // Maintain maximum 30 backup files history (both JSON and SQL)
    $files = array_merge(glob($backupDir . "/*.json") ?: [], glob($backupDir . "/*.sql") ?: []);
    if (count($files) > 40) {
        usort($files, function($a, $b) {
            return filemtime($a) - filemtime($b);
        });
        while (count($files) > 40) {
            $oldest = array_shift($files);
            @unlink($oldest);
        }
    }
    return true;
}

// Silently trigger auto backup check on DB connection load
performAutoBackupIfNeeded($conn);

/**
 * Ensures index exists on table column for query performance optimization
 */
function ensureIndexExists($conn, $table, $indexName, $columnDef) {
    if (!$conn) return;
    $res = @$conn->query("SHOW INDEX FROM `$table` WHERE Key_name = '$indexName'");
    if ($res && $res->num_rows == 0) {
        @$conn->query("ALTER TABLE `$table` ADD INDEX `$indexName` ($columnDef)");
    }
}

// Database query performance optimization indexes
ensureIndexExists($conn, 'forms', 'idx_forms_created_at', '`created_at`');
ensureIndexExists($conn, 'forms', 'idx_forms_status', '`status`');
ensureIndexExists($conn, 'form_items', 'idx_form_items_form_id', '`form_id`');
ensureIndexExists($conn, 'safe_transactions', 'idx_safe_tx_debtor_id', '`debtor_id`');
ensureIndexExists($conn, 'safe_transactions', 'idx_safe_tx_form_id', '`form_id`');

/**
 * Calculates current active shift boundaries dynamically from database table system_settings
 */
function getSystemShiftRange($conn) {
    $start_hour = 17; // Default 5:00 PM
    if ($conn) {
        $res = @$conn->query("SELECT workday_start_hour FROM system_settings WHERE id = 1");
        if ($res && $row = $res->fetch_assoc()) {
            $start_hour = intval($row['workday_start_hour']);
        }
    }

    $now = new DateTime('now', new DateTimeZone('Asia/Baghdad'));
    $hour = (int)$now->format('H');

    if ($hour >= $start_hour) {
        $start = (clone $now)->setTime($start_hour, 0, 0);
        $end   = (clone $now)->modify('+1 day')->setTime($start_hour, 0, 0);
    } else {
        $start = (clone $now)->modify('-1 day')->setTime($start_hour, 0, 0);
        $end   = (clone $now)->setTime($start_hour, 0, 0);
    }

    return [
        'start_hour' => $start_hour,
        'start'      => $start->format('Y-m-d H:i:s'),
        'end'        => $end->format('Y-m-d H:i:s')
    ];
}
