<?php
// =========================================================================
// MASTER CATALOG LIST FETCHER
// Strictly Database Responsive - Aggregates Products, Brands, and Tariffs
// =========================================================================
require_once dirname(__DIR__, 2) . '/config/config.php';
require_once dirname(__DIR__, 2) . '/includes/db.php';

// Wipe any previous PHP warnings/notices to prevent JSON corruption
if (ob_get_length()) ob_clean(); 
header('Content-Type: application/json');

if (!isset($conn) && isset($db)) { $conn = $db; }

try {
    // 1. LIVE ANALYTICAL DATAPOINTS (KPIs)
    $kpi_total  = $conn->query("SELECT COUNT(*) as total FROM products")->fetch_assoc()['total'] ?? 0;
    $kpi_active = $conn->query("SELECT COUNT(*) as total FROM products WHERE is_active = 1")->fetch_assoc()['total'] ?? 0;
    $kpi_nhis   = $conn->query("SELECT COUNT(*) as total FROM products WHERE nhis_drug_code IS NOT NULL OR gdrg_tariff > 0")->fetch_assoc()['total'] ?? 0;
    $kpi_brands = $conn->query("SELECT COUNT(*) as total FROM product_variants WHERE is_active = 1")->fetch_assoc()['total'] ?? 0;

    // 2. QUERY MASTER LEDGER GRID WITH COMMERCIAL VARIANTS
    $query = "
        SELECT p.id as product_id, p.generic_name, p.strength_value, p.is_active, p.nhis_drug_code, p.gdrg_tariff, p.is_copay_required, p.copay_amount, p.prescribing_level,
               pv.id as variant_id, pv.pack_size, pv.purchase_price, pv.selling_price, pv.barcode,
               b.brand_name, m.manufacturer_name,
               pt.type_name, ic.category_name, df.formulation_name,
               u.unit_symbol as strength_unit, u2.unit_symbol as base_uom
        FROM products p
        LEFT JOIN product_types pt ON p.product_type_id = pt.id
        LEFT JOIN item_categories ic ON p.category_id = ic.id
        LEFT JOIN drug_formulations df ON p.formulation_id = df.id
        LEFT JOIN units_of_measure u ON p.strength_unit_id = u.id
        LEFT JOIN units_of_measure u2 ON p.base_uom_id = u2.id
        LEFT JOIN product_variants pv ON p.id = pv.product_id AND pv.is_active = 1
        LEFT JOIN brand_dictionary b ON pv.brand_id = b.id
        LEFT JOIN manufacturers m ON pv.manufacturer_id = m.id
        ORDER BY p.id DESC, b.brand_name ASC
    ";
    
    $result = $conn->query($query);
    $products_map = [];

    while ($row = $result->fetch_assoc()) {
        $pid = $row['product_id'];
        
        if (!isset($products_map[$pid])) {
            $products_map[$pid] = [
                'product_id'        => (int)$pid,
                'generic_name'      => $row['generic_name'],
                'strength_value'    => $row['strength_value'],
                'strength_unit'     => $row['strength_unit'],
                'formulation_name'  => $row['formulation_name'],
                'type_name'         => $row['type_name'],
                'category_name'     => $row['category_name'],
                'base_uom'          => $row['base_uom'] ?? 'Units',
                'is_active'         => (int)$row['is_active'],
                'nhis_drug_code'    => $row['nhis_drug_code'],
                'gdrg_tariff'       => (float)$row['gdrg_tariff'],
                'is_copay_required' => (int)$row['is_copay_required'],
                'copay_amount'      => (float)$row['copay_amount'],
                'prescribing_level' => $row['prescribing_level'],
                'brands'            => [],
                'corporate_tariffs' => []
            ];
        }

        if (!empty($row['variant_id'])) {
            $products_map[$pid]['brands'][] = [
                'variant_id'        => (int)$row['variant_id'],
                'brand_name'        => $row['brand_name'] ?? 'Generic Variant',
                'manufacturer_name' => $row['manufacturer_name'] ?? 'N/A',
                'pack_size'         => (int)$row['pack_size'],
                'purchase_price'    => (float)$row['purchase_price'],
                'selling_price'     => (float)$row['selling_price'],
                'barcode'           => $row['barcode']
            ];
        }
    }

    // 3. RELATIONAL MAPPING FOR SERVICE CORPORATE TARIFFS
    if (!empty($products_map)) {
        $corp_query = "
            SELECT sct.service_id, sct.tariff_amount, sct.is_copay_required, sct.copay_amount_adult, it.insurance_name, sc.service_name
            FROM service_corporate_tariffs sct
            INNER JOIN service_catalog sc ON sct.service_id = sc.id
            INNER JOIN insurance_type it ON sct.insurance_type_id = it.id
            WHERE sc.service_type = 'Drug'
        ";
        $corp_res = $conn->query($corp_query);
        
        while ($c_row = $corp_res->fetch_assoc()) {
            foreach ($products_map as $pid => &$p_ref) {
                // Safely map based on clinical name to service name bridging
                $gen_name = strtolower(trim($p_ref['generic_name']));
                $serv_name = strtolower(trim($c_row['service_name']));
                
                if (strpos($serv_name, $gen_name) !== false) {
                     $p_ref['corporate_tariffs'][] = [
                         'provider'   => $c_row['insurance_name'],
                         'tariff'     => (float)$c_row['tariff_amount'],
                         'copay_req'  => (int)$c_row['is_copay_required'],
                         'copay_amt'  => (float)$c_row['copay_amount_adult']
                     ];
                }
            }
        }
    }

    echo json_encode([
        'status' => 'success',
        'kpis' => [
            'total'  => $kpi_total,
            'active' => $kpi_active,
            'nhis'   => $kpi_nhis,
            'brands' => $kpi_brands
        ],
        'data' => array_values($products_map)
    ]);

} catch (Exception $e) {
    echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
}
?>