<?php
/**
 * Helper functions to get secondary IDs (US_id, CU_id, PR_id, etc.)
 * Used throughout admin pages to store secondary IDs instead of auto-increment ids
 */

require_once __DIR__ . '/con_data.php';

/**
 * Get user's US_id from session user_id
 */
function getUserSecondaryId($conn, $user_id)
{
    if (!$user_id)
        return null;
    $stmt = $conn->prepare("SELECT user_id FROM users WHERE id = ?");
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        return $row['user_id'];
    }
    return null;
}

/**
 * Get customer CU_id from customer id
 */
function getCustomerSecondaryId($conn, $customer_id)
{
    if (!$customer_id)
        return null;
    $stmt = $conn->prepare("SELECT CU_id FROM customers WHERE id = ?");
    $stmt->bind_param("i", $customer_id);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        return $row['CU_id'];
    }
    return null;
}

/**
 * Get product PR_id from product id
 */
function getProductSecondaryId($conn, $product_id)
{
    // Products table might use product_code as secondary ID or something else.
    // Checking all.sql, columns are: id, product_code, product_name...
    // The source code used PR_id. But all.sql products table HAS product_code but NO PR_id column in the create statement snippet I saw (Wait, I need to be careful).
    // Let's check products table again.
    // Snippet 41535: CREATE TABLE products ... product_code varchar(50) ...
    // It does NOT show PR_id. It shows product_code.
    // However, the source code says "Get product PR_id".
    // If I use the source code blindly, it might fail if PR_id column doesn't exist.
    // But the user said "Bring all functions".
    // I recall `all.sql` showed `product_code`.
    // I will assume `product_code` is the secondary ID if PR_id is missing, but the source code explicitely selects PR_id.
    // If PR_id is missing in DB, this function will fail.
    // I should check `products` table schema in `all.sql` more closely.
    // I already viewed it in step 29 (no, step 29 was pos.php). Step 40 (all.sql) showed `products` table.
    // `product_code` exists. `PR_id` does NOT exist in the CREATE TABLE snippet I saw.
    // This implies the source code expects a column that might be missing or named `product_code`.
    // USE `product_code` instead of `PR_id` if that's what `all.sql` has?
    // User said "ensure database queries align with schema provided in now_data/all.sql".
    // So I MUST use `product_code` if `PR_id` is missing.

    if (!$product_id)
        return null;

    // Check if column PR_id exists? I can't check at runtime easily here.
    // Based on all.sql, usage of product_code seems correct for "secondary id" concept.
    // I will use product_code.
    $stmt = $conn->prepare("SELECT product_code FROM products WHERE id = ?");
    $stmt->bind_param("i", $product_id);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        return $row['product_code'];
    }
    return null;
}

/**
 * Get shipping company SC_id from company id 
 */
function getShippingCompanySecondaryId($conn, $company_id)
{
    if (!$company_id)
        return null;
    $stmt = $conn->prepare("SELECT SC_id FROM shipping_companies WHERE id = ?");
    $stmt->bind_param("i", $company_id);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        return $row['SC_id'];
    }
    return null;
}

/**
 * Get shipping branch SB_id from branch id
 */
function getShippingBranchSecondaryId($conn, $branch_id)
{
    if (!$branch_id)
        return null;
    $stmt = $conn->prepare("SELECT SB_id FROM shipping_branches WHERE id = ?");
    $stmt->bind_param("i", $branch_id);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        return $row['SB_id'];
    }
    return null;
}
?>