<?php
// index.php - Fix NULL Values and Update Demander/District Fields

error_reporting(E_ALL);
ini_set('display_errors', 1);

// Database connection
$servername = "localhost";
$username   = "root";
$password   = "";
$dbname     = "paymentpack";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$conn->set_charset("utf8mb4");

// ---- Handle messages ----
$message = '';
$message_type = '';
if (isset($_GET['msg']) && isset($_GET['msgtype'])) {
    $message = urldecode($_GET['msg']);
    $message_type = $_GET['msgtype'];
}

// ---- Handle POST: Update NULL values with selected del_point ----
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'update_records') {
    $selected_facility = isset($_POST['facility']) ? trim($_POST['facility']) : '';
    $del_point_selected = isset($_POST['del_point']) ? trim($_POST['del_point']) : '';
    $update_type = isset($_POST['update_type']) ? trim($_POST['update_type']) : 'null_only';
    $update_field = isset($_POST['update_field']) ? trim($_POST['update_field']) : 'demander';
    
    if (empty($selected_facility)) {
        header("Location: index.php?msg=" . urlencode("No facility selected.") . "&msgtype=warning");
        exit;
    }
    
    if ($update_field === 'demander' && empty($del_point_selected)) {
        header("Location: index.php?msg=" . urlencode("Please select a delivery point to update with.") . "&msgtype=warning");
        exit;
    }
    
    $conn->begin_transaction();
    try {
        $dem_name = '';
        if ($update_field === 'demander') {
            // Get the dem_name for the selected del_point
            $del_sql = "SELECT dem_name FROM del_point WHERE del_point = ?";
            $del_stmt = $conn->prepare($del_sql);
            $del_stmt->bind_param("s", $del_point_selected);
            $del_stmt->execute();
            $del_result = $del_stmt->get_result();
            $del_row = $del_result->fetch_assoc();
            $del_stmt->close();
            
            if (!$del_row) {
                header("Location: index.php?msg=" . urlencode("Invalid delivery point selected.") . "&msgtype=error");
                exit;
            }
            $dem_name = $del_row['dem_name'];
        }
        
        // Build WHERE clause based on update type
        $where_clause = "facility = ?";
        $params = [$selected_facility];
        $types = "s";
        
        if ($update_type === 'null_only') {
            $where_clause .= " AND (Demander IS NULL OR Demander = '' OR DemanderCode_str IS NULL OR DemanderCode_str = '')";
        } elseif ($update_type === 'all_records') {
            $where_clause .= " AND 1=1";
        } elseif ($update_type === 'filled_only') {
            $where_clause .= " AND Demander IS NOT NULL AND Demander != '' AND DemanderCode_str IS NOT NULL AND DemanderCode_str != ''";
        } elseif ($update_type === 'selected_ids') {
            if (isset($_POST['selected_ids']) && is_array($_POST['selected_ids'])) {
                $selected_ids = array_map('intval', $_POST['selected_ids']);
                if (empty($selected_ids)) {
                    header("Location: index.php?msg=" . urlencode("No records selected.") . "&msgtype=warning");
                    exit;
                }
                $placeholders = implode(',', array_fill(0, count($selected_ids), '?'));
                $where_clause = "id IN ($placeholders)";
                $params = $selected_ids;
                $types = str_repeat('i', count($selected_ids));
            } else {
                header("Location: index.php?msg=" . urlencode("No records selected.") . "&msgtype=warning");
                exit;
            }
        }
        
        // Build the update query based on field being updated
        if ($update_field === 'district') {
            $district_selected = isset($_POST['district']) ? trim($_POST['district']) : '';
            if (empty($district_selected)) {
                header("Location: index.php?msg=" . urlencode("Please select a district.") . "&msgtype=warning");
                exit;
            }
            $update_sql = "UPDATE receipts SET district = ? WHERE $where_clause";
            $update_stmt = $conn->prepare($update_sql);
            
            if ($update_type === 'selected_ids') {
                $all_params = array_merge([$district_selected], $params);
                $all_types = "s" . $types;
                $update_stmt->bind_param($all_types, ...$all_params);
            } else {
                $update_stmt->bind_param("ss", $district_selected, $selected_facility);
            }
        } else {
            // Update Demander and DemanderCode_str
            $update_sql = "UPDATE receipts SET Demander = ?, DemanderCode_str = ? WHERE $where_clause";
            $update_stmt = $conn->prepare($update_sql);
            
            if ($update_type === 'selected_ids') {
                $all_params = array_merge([$dem_name, $del_point_selected], $params);
                $all_types = "ss" . $types;
                $update_stmt->bind_param($all_types, ...$all_params);
            } else {
                $update_stmt->bind_param("sss", $dem_name, $del_point_selected, $selected_facility);
            }
        }
        
        $update_stmt->execute();
        $updated_count = $update_stmt->affected_rows;
        $update_stmt->close();
        
        $conn->commit();
        
        $field_label = $update_field === 'district' ? 'District' : 'Demander and DemanderCode_str';
        $msg = "Successfully updated " . $updated_count . " record(s) for facility: " . $selected_facility;
        $msg .= " with " . $field_label . " values.";
        $type = 'success';
        
        header("Location: index.php?msg=" . urlencode($msg) . "&msgtype=" . $type . "&updated=" . $updated_count . "&facility=" . urlencode($selected_facility) . "&section=filled");
        exit;
        
    } catch (Exception $e) {
        $conn->rollback();
        header("Location: index.php?msg=" . urlencode("Error: " . $e->getMessage()) . "&msgtype=error");
        exit;
    }
}

// ---- Get facilities with NULL Demander or DemanderCode_str ----
$null_facilities = [];
$null_facility_counts = [];

$sql = "SELECT facility, COUNT(*) as count 
        FROM receipts 
        WHERE (Demander IS NULL OR Demander = '' OR DemanderCode_str IS NULL OR DemanderCode_str = '')
        AND facility IS NOT NULL AND facility != ''
        GROUP BY facility 
        ORDER BY facility";
$result = $conn->query($sql);
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $null_facilities[] = $row['facility'];
        $null_facility_counts[$row['facility']] = $row['count'];
    }
}

// ---- Get facilities with NON-NULL (filled) Demander and DemanderCode_str ----
$filled_facilities = [];
$filled_facility_counts = [];

$sql = "SELECT facility, COUNT(*) as count 
        FROM receipts 
        WHERE Demander IS NOT NULL AND Demander != '' 
        AND DemanderCode_str IS NOT NULL AND DemanderCode_str != ''
        AND facility IS NOT NULL AND facility != ''
        GROUP BY facility 
        ORDER BY facility";
$result = $conn->query($sql);
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $filled_facilities[] = $row['facility'];
        $filled_facility_counts[$row['facility']] = $row['count'];
    }
}

// ---- Get all districts for dropdown ----
$district_list = [];
$sql = "SELECT id_district, district FROM district ORDER BY district";
$result = $conn->query($sql);
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $district_list[] = $row;
    }
}

// ---- Get selected facility from URL ----
$selected_facility = isset($_GET['facility']) ? trim($_GET['facility']) : '';
$selected_facility_display = '';
$active_section = isset($_GET['section']) ? trim($_GET['section']) : 'null';
$active_field = isset($_GET['field']) ? trim($_GET['field']) : 'demander';

// ---- If a facility is selected, get its records ----
$records = [];
$record_count = 0;
$null_record_count = 0;
$filled_record_count = 0;
$del_point_list = [];
$selected_del_point = isset($_GET['del_point']) ? trim($_GET['del_point']) : '';

if (!empty($selected_facility)) {
    // Get all delivery points for the dropdown
    $dp_sql = "SELECT del_point, dem_name FROM del_point ORDER BY dem_name";
    $dp_result = $conn->query($dp_sql);
    if ($dp_result) {
        while ($row = $dp_result->fetch_assoc()) {
            $del_point_list[] = $row;
        }
    }
    
    // Get ALL records for this facility
    $record_sql = "SELECT id, InvoiceNo_str, del_point, Demander, DemanderCode_str, facility, district,
                   upload_date, OrderNo_str, packDescription_Str, QtyReceived_int
                   FROM receipts 
                   WHERE facility = ? 
                   ORDER BY id DESC";
    $record_stmt = $conn->prepare($record_sql);
    $record_stmt->bind_param("s", $selected_facility);
    $record_stmt->execute();
    $record_result = $record_stmt->get_result();
    $records = $record_result->fetch_all(MYSQLI_ASSOC);
    $record_count = count($records);
    $record_stmt->close();
    
    // Count records with NULL Demander values
    $null_sql = "SELECT COUNT(*) as count 
                 FROM receipts 
                 WHERE facility = ? 
                 AND (Demander IS NULL OR Demander = '' OR DemanderCode_str IS NULL OR DemanderCode_str = '')";
    $null_stmt = $conn->prepare($null_sql);
    $null_stmt->bind_param("s", $selected_facility);
    $null_stmt->execute();
    $null_result = $null_stmt->get_result();
    $null_record_count = $null_result->fetch_assoc()['count'];
    $null_stmt->close();
    
    // Count records with filled (non-NULL) Demander values
    $filled_sql = "SELECT COUNT(*) as count 
                   FROM receipts 
                   WHERE facility = ? 
                   AND Demander IS NOT NULL AND Demander != '' 
                   AND DemanderCode_str IS NOT NULL AND DemanderCode_str != ''";
    $filled_stmt = $conn->prepare($filled_sql);
    $filled_stmt->bind_param("s", $selected_facility);
    $filled_stmt->execute();
    $filled_result = $filled_stmt->get_result();
    $filled_record_count = $filled_result->fetch_assoc()['count'];
    $filled_stmt->close();
    
    // Get facility display name from first record or use selected
    if ($record_count > 0) {
        $selected_facility_display = $records[0]['facility'];
    } else {
        $selected_facility_display = $selected_facility;
    }
}

// ---- Get total records with NULL values ----
$total_null_sql = "SELECT COUNT(*) as total 
                   FROM receipts 
                   WHERE (Demander IS NULL OR Demander = '' OR DemanderCode_str IS NULL OR DemanderCode_str = '')";
$total_null_result = $conn->query($total_null_sql);
$total_null_records = 0;
if ($total_null_result) {
    $total_null_records = $total_null_result->fetch_assoc()['total'];
}

// ---- Get total records with filled values ----
$total_filled_sql = "SELECT COUNT(*) as total 
                     FROM receipts 
                     WHERE Demander IS NOT NULL AND Demander != '' 
                     AND DemanderCode_str IS NOT NULL AND DemanderCode_str != ''";
$total_filled_result = $conn->query($total_filled_sql);
$total_filled_records = 0;
if ($total_filled_result) {
    $total_filled_records = $total_filled_result->fetch_assoc()['total'];
}

$conn->close();
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Fix & Update Fields | KZN Health</title>
    <link href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css" rel="stylesheet">
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
    <style>
        * { box-sizing: border-box; margin: 0; padding: 0; }
        body {
            font-family: 'Inter', -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
            background: #f0f4fb;
            padding: 30px 20px;
            min-height: 100vh;
            display: flex;
            justify-content: center;
        }
        .container {
            max-width: 1400px;
            width: 100%;
            background: #fff;
            border-radius: 32px;
            box-shadow: 0 20px 60px rgba(0,20,40,0.15);
            padding: 32px 28px;
        }
        .header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            flex-wrap: wrap;
            gap: 12px;
            margin-bottom: 8px;
        }
        h1 {
            font-size: 28px;
            font-weight: 700;
            color: #0b1b2f;
            display: flex;
            align-items: center;
            gap: 12px;
        }
        .sub {
            color: #5e6f88;
            font-size: 14px;
            margin-bottom: 24px;
        }
        .btn {
            display: inline-flex;
            align-items: center;
            gap: 8px;
            padding: 10px 24px;
            border-radius: 12px;
            font-weight: 600;
            font-size: 14px;
            border: none;
            cursor: pointer;
            transition: all 0.2s ease;
            font-family: inherit;
            text-decoration: none;
        }
        .btn-primary {
            background: #1a4f8a;
            color: #fff;
        }
        .btn-primary:hover {
            background: #0f3d6b;
            transform: translateY(-2px);
            box-shadow: 0 4px 12px rgba(26, 79, 138, 0.3);
        }
        .btn-success {
            background: #1a8a4a;
            color: #fff;
        }
        .btn-success:hover {
            background: #12703b;
            transform: translateY(-2px);
            box-shadow: 0 4px 12px rgba(26, 138, 74, 0.3);
        }
        .btn-success:disabled {
            opacity: 0.5;
            cursor: not-allowed;
            transform: none;
            box-shadow: none;
        }
        .btn-secondary {
            background: #eef3fa;
            color: #1a2d44;
        }
        .btn-secondary:hover {
            background: #e2ecf9;
        }
        .btn-outline {
            background: transparent;
            color: #1a4f8a;
            border: 1.5px solid #d0d9e6;
        }
        .btn-outline:hover {
            background: #f0f6fe;
            border-color: #1a4f8a;
        }
        .btn-danger {
            background: #c0392b;
            color: #fff;
        }
        .btn-danger:hover {
            background: #a93226;
        }
        .btn-warning {
            background: #f39c12;
            color: #fff;
        }
        .btn-warning:hover {
            background: #d68910;
        }
        .btn-info {
            background: #17a2b8;
            color: #fff;
        }
        .btn-info:hover {
            background: #138496;
        }
        .message {
            padding: 14px 18px;
            border-radius: 12px;
            font-size: 14px;
            font-weight: 500;
            margin: 16px 0 20px 0;
        }
        .message.success {
            background: #e6f5ed;
            color: #0f6e3a;
            border: 1px solid #b8e0cc;
        }
        .message.error {
            background: #fde9e9;
            color: #a32d2d;
            border: 1px solid #f5cdcd;
        }
        .message.warning {
            background: #fef7e0;
            color: #8a6d1b;
            border: 1px solid #f5e3b8;
        }
        .stats-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(150px, 1fr));
            gap: 16px;
            margin-bottom: 24px;
        }
        .stat-card {
            background: #f8faff;
            border-radius: 16px;
            padding: 16px 20px;
            border: 1px solid #e9eef4;
        }
        .stat-card .label {
            font-size: 12px;
            color: #5e6f88;
            font-weight: 500;
            text-transform: uppercase;
            letter-spacing: 0.03em;
        }
        .stat-card .value {
            font-size: 28px;
            font-weight: 700;
            color: #0b1b2f;
            margin-top: 4px;
        }
        .stat-card .value.danger { color: #c0392b; }
        .stat-card .value.success { color: #1a8a4a; }
        .stat-card .value.warning { color: #f39c12; }
        .stat-card .value.info { color: #17a2b8; }
        .info-box {
            background: #f8faff;
            border: 1px solid #e9eef4;
            border-radius: 16px;
            padding: 20px 24px;
            margin-bottom: 24px;
        }
        .info-box h3 {
            font-size: 16px;
            color: #0b1b2f;
            margin-bottom: 8px;
        }
        .info-box p {
            color: #5e6f88;
            font-size: 14px;
            line-height: 1.6;
        }
        .info-box ul {
            margin: 8px 0 0 20px;
            color: #1a2d44;
            font-size: 14px;
            line-height: 1.8;
        }
        .section-tabs {
            display: flex;
            gap: 8px;
            margin: 20px 0;
            border-bottom: 2px solid #e9eef4;
            padding-bottom: 0;
            flex-wrap: wrap;
        }
        .section-tab {
            padding: 12px 24px;
            border-radius: 12px 12px 0 0;
            font-weight: 600;
            font-size: 14px;
            cursor: pointer;
            transition: all 0.2s ease;
            background: transparent;
            color: #5e6f88;
            border: 2px solid transparent;
            border-bottom: none;
            text-decoration: none;
        }
        .section-tab:hover {
            background: #f0f4fb;
            color: #0b1b2f;
        }
        .section-tab.active {
            background: #fff;
            color: #1a4f8a;
            border-color: #e9eef4;
            border-bottom: 2px solid #fff;
            margin-bottom: -2px;
        }
        .section-tab .badge {
            display: inline-block;
            padding: 2px 8px;
            border-radius: 12px;
            font-size: 11px;
            font-weight: 600;
            margin-left: 6px;
        }
        .section-tab .badge.null-badge {
            background: #fde9e9;
            color: #c0392b;
        }
        .section-tab .badge.filled-badge {
            background: #e6f5ed;
            color: #0f6e3a;
        }
        .field-tabs {
            display: flex;
            gap: 8px;
            margin: 12px 0 16px 0;
            flex-wrap: wrap;
        }
        .field-tab {
            padding: 8px 16px;
            border-radius: 8px;
            font-weight: 600;
            font-size: 13px;
            cursor: pointer;
            transition: all 0.2s ease;
            background: #f0f4fb;
            color: #5e6f88;
            border: 2px solid transparent;
            text-decoration: none;
        }
        .field-tab:hover {
            background: #e6edf8;
            color: #0b1b2f;
        }
        .field-tab.active {
            background: #1a4f8a;
            color: #fff;
            border-color: #1a4f8a;
        }
        .facility-grid {
            display: grid;
            grid-template-columns: repeat(auto-fill, minmax(280px, 1fr));
            gap: 12px;
            margin: 16px 0 20px 0;
        }
        .facility-item {
            background: #fafcff;
            border: 2px solid #e9eef4;
            border-radius: 12px;
            padding: 14px 18px;
            display: flex;
            align-items: center;
            gap: 12px;
            transition: all 0.2s ease;
            cursor: pointer;
            text-decoration: none;
            color: inherit;
        }
        .facility-item:hover {
            border-color: #1a4f8a;
            background: #e6edf8;
            transform: translateY(-2px);
            box-shadow: 0 4px 12px rgba(26, 79, 138, 0.1);
        }
        .facility-item.selected {
            border-color: #1a4f8a;
            background: #e6edf8;
        }
        .facility-item .facility-info {
            flex: 1;
        }
        .facility-item .facility-name {
            font-weight: 600;
            color: #0b1b2f;
            font-size: 14px;
        }
        .facility-item .facility-badge {
            font-size: 12px;
            font-weight: 600;
            padding: 2px 10px;
            border-radius: 20px;
        }
        .facility-item .facility-badge.null-badge {
            background: #fde9e9;
            color: #c0392b;
        }
        .facility-item .facility-badge.filled-badge {
            background: #e6f5ed;
            color: #0f6e3a;
        }
        .facility-item .facility-arrow {
            color: #1a4f8a;
            font-size: 20px;
        }
        .badge {
            display: inline-block;
            padding: 2px 10px;
            border-radius: 20px;
            font-size: 12px;
            font-weight: 600;
            background: #e6edf8;
            color: #1a4f8a;
        }
        .back-link {
            display: inline-block;
            color: #1a4f8a;
            text-decoration: none;
            font-weight: 600;
            font-size: 14px;
            margin-top: 12px;
        }
        .back-link:hover {
            text-decoration: underline;
        }
        .records-section {
            margin: 20px 0;
        }
        .records-section .table-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            flex-wrap: wrap;
            gap: 12px;
            margin-bottom: 16px;
            padding: 12px 0;
            border-bottom: 2px solid #e9eef4;
        }
        .records-section .table-header h3 {
            font-size: 18px;
            color: #0b1b2f;
        }
        .records-section .table-header .badge-count {
            background: #1a4f8a;
            color: #fff;
            padding: 4px 12px;
            border-radius: 20px;
            font-size: 13px;
            font-weight: 600;
        }
        .dataTables_wrapper {
            padding: 0;
        }
        .dataTables_wrapper .dataTables_filter input {
            border: 1.5px solid #d0d9e6;
            border-radius: 8px;
            padding: 6px 12px;
            margin-left: 8px;
        }
        .dataTables_wrapper .dataTables_length select {
            border: 1.5px solid #d0d9e6;
            border-radius: 8px;
            padding: 4px 8px;
        }
        .dataTables_wrapper .dataTables_info {
            color: #5e6f88;
            font-size: 14px;
        }
        .dataTables_wrapper .dataTables_paginate .paginate_button {
            padding: 4px 12px;
            border-radius: 6px;
            border: 1px solid #d0d9e6;
            margin: 0 2px;
        }
        .dataTables_wrapper .dataTables_paginate .paginate_button.current {
            background: #1a4f8a;
            color: #fff !important;
            border-color: #1a4f8a;
        }
        table.dataTable {
            border-radius: 12px;
            overflow: hidden;
            border: 1px solid #e9eef4;
        }
        table.dataTable thead th {
            background: #eef3fa;
            color: #1a2d44;
            font-weight: 600;
            padding: 12px 14px;
            border-bottom: 2px solid #d6e0eb;
            font-size: 12px;
            text-transform: uppercase;
            letter-spacing: 0.03em;
        }
        table.dataTable tbody td {
            padding: 10px 14px;
            border-bottom: 1px solid #eef3f8;
            color: #1f334a;
            vertical-align: middle;
            font-size: 14px;
        }
        table.dataTable tbody tr:hover td {
            background: #f8fbff;
        }
        table.dataTable tbody tr.selected-row td {
            background: #e6edf8;
        }
        .status-badge {
            display: inline-block;
            padding: 2px 10px;
            border-radius: 20px;
            font-size: 12px;
            font-weight: 600;
        }
        .status-badge.null {
            background: #fde9e9;
            color: #c0392b;
        }
        .status-badge.filled {
            background: #e6f5ed;
            color: #0f6e3a;
        }
        .update-area {
            background: #e6f5ed;
            border: 2px solid #b8e0cc;
            border-radius: 16px;
            padding: 24px;
            margin: 20px 0;
        }
        .update-area .update-label {
            font-weight: 600;
            color: #0b1b2f;
            font-size: 15px;
            margin-bottom: 12px;
        }
        .update-area .update-info {
            display: flex;
            flex-wrap: wrap;
            gap: 20px;
            align-items: center;
        }
        .update-area .update-info .info-item {
            background: #fff;
            padding: 10px 16px;
            border-radius: 8px;
            border: 1px solid #d0e6d8;
        }
        .update-area .update-info .info-item strong {
            color: #0b1b2f;
        }
        .update-area .update-info .info-item .value {
            color: #1a8a4a;
            font-weight: 600;
        }
        .empty-state {
            text-align: center;
            padding: 40px 20px;
            color: #5e6f88;
        }
        .empty-state .icon {
            font-size: 48px;
            margin-bottom: 12px;
        }
        .empty-state h3 {
            color: #0b1b2f;
            margin-bottom: 8px;
        }
        .empty-state p {
            font-size: 15px;
            line-height: 1.6;
        }
        .update-type-section {
            background: #f8faff;
            border: 1px solid #d0d9e6;
            border-radius: 12px;
            padding: 16px 20px;
            margin: 16px 0;
        }
        .update-type-section .radio-group {
            display: flex;
            flex-wrap: wrap;
            gap: 20px;
            margin-top: 8px;
        }
        .update-type-section .radio-group label {
            display: flex;
            align-items: center;
            gap: 8px;
            font-size: 14px;
            color: #0b1b2f;
            cursor: pointer;
        }
        .update-type-section .radio-group input[type="radio"] {
            width: 18px;
            height: 18px;
            cursor: pointer;
        }
        .update-type-section .radio-group .help-text {
            font-size: 12px;
            color: #5e6f88;
            margin-left: 26px;
        }
        .select-all-bar {
            display: flex;
            align-items: center;
            gap: 16px;
            padding: 8px 0 12px 0;
            border-bottom: 1px solid #e9eef4;
            margin-bottom: 12px;
            flex-wrap: wrap;
        }
        .select-all-bar label {
            font-weight: 600;
            color: #0b1b2f;
            font-size: 14px;
            cursor: pointer;
            display: flex;
            align-items: center;
            gap: 8px;
        }
        .select-all-bar input[type="checkbox"] {
            width: 18px;
            height: 18px;
            cursor: pointer;
        }
        .checkbox-col {
            width: 40px;
            text-align: center;
        }
        .select-field-section {
            background: #f8faff;
            border: 1px solid #d0d9e6;
            border-radius: 12px;
            padding: 16px 20px;
            margin: 16px 0;
        }
        .select-field-section .radio-group {
            display: flex;
            flex-wrap: wrap;
            gap: 20px;
            margin-top: 8px;
        }
        .select-field-section .radio-group label {
            display: flex;
            align-items: center;
            gap: 8px;
            font-size: 14px;
            color: #0b1b2f;
            cursor: pointer;
        }
        .select-field-section .radio-group input[type="radio"] {
            width: 18px;
            height: 18px;
            cursor: pointer;
        }
        @media (max-width: 600px) {
            .container { padding: 20px 12px; }
            .facility-grid { grid-template-columns: 1fr; }
            .stats-grid { grid-template-columns: 1fr 1fr; }
            .header { flex-direction: column; align-items: flex-start; }
            .update-area .update-info { flex-direction: column; align-items: stretch; }
            .update-type-section .radio-group { flex-direction: column; gap: 12px; }
            table.dataTable { font-size: 12px; }
            table.dataTable thead th, table.dataTable tbody td { padding: 6px 8px; }
            .checkbox-col { width: 30px; }
            .section-tabs { flex-wrap: wrap; }
            .section-tab { flex: 1; text-align: center; padding: 10px 12px; font-size: 12px; }
            .field-tabs { flex-wrap: wrap; }
            .field-tab { flex: 1; text-align: center; }
            .select-field-section .radio-group { flex-direction: column; gap: 12px; }
        }
    </style>
</head>
<body>
<div class="container">
    <!-- Header -->
    <div class="header">
        <div>
            <h1>🔧 Fix & Update Fields</h1>
            <p class="sub">Update <strong>Demander</strong>, <strong>DemanderCode_str</strong>, or <strong>District</strong> fields using reference data.</p>
        </div>
        <div style="display:flex; gap:10px; flex-wrap:wrap;">
            <a href="index.php" class="btn btn-secondary">🏠 Dashboard</a>
            <a href="delete.php" class="btn btn-outline">📋 Manage Records</a>
        </div>
    </div>

    <!-- Message -->
    <?php if ($message): ?>
        <div class="message <?php echo $message_type; ?>">
            <?php echo htmlspecialchars($message); ?>
            <?php if (isset($_GET['updated']) && $_GET['updated'] > 0): ?>
                <br><small>✅ <?php echo number_format($_GET['updated']); ?> records were successfully updated.</small>
            <?php endif; ?>
        </div>
    <?php endif; ?>

    <!-- Stats -->
    <div class="stats-grid">
        <div class="stat-card">
            <div class="label">Total Records with NULL</div>
            <div class="value danger"><?php echo number_format($total_null_records); ?></div>
        </div>
        <div class="stat-card">
            <div class="label">Total Filled Records</div>
            <div class="value success"><?php echo number_format($total_filled_records); ?></div>
        </div>
        <div class="stat-card">
            <div class="label">Facilities with NULL</div>
            <div class="value warning"><?php echo number_format(count($null_facilities)); ?></div>
        </div>
        <div class="stat-card">
            <div class="label">Facilities with Filled</div>
            <div class="value info"><?php echo number_format(count($filled_facilities)); ?></div>
        </div>
    </div>

    <!-- Section Tabs -->
    <div class="section-tabs">
        <a href="?section=null<?php echo !empty($selected_facility) ? '&facility=' . urlencode($selected_facility) : ''; ?><?php echo '&field=' . $active_field; ?>" 
           class="section-tab <?php echo $active_section === 'null' ? 'active' : ''; ?>">
            🔴 NULL Records
            <span class="badge null-badge"><?php echo count($null_facilities); ?> facilities</span>
        </a>
        <a href="?section=filled<?php echo !empty($selected_facility) ? '&facility=' . urlencode($selected_facility) : ''; ?><?php echo '&field=' . $active_field; ?>" 
           class="section-tab <?php echo $active_section === 'filled' ? 'active' : ''; ?>">
            ✅ Filled Records
            <span class="badge filled-badge"><?php echo count($filled_facilities); ?> facilities</span>
        </a>
    </div>

    <!-- Field Selection Tabs -->
    <div class="field-tabs">
        <a href="?section=<?php echo $active_section; ?><?php echo !empty($selected_facility) ? '&facility=' . urlencode($selected_facility) : ''; ?>&field=demander" 
           class="field-tab <?php echo $active_field === 'demander' ? 'active' : ''; ?>">
            📝 Update Demander
        </a>
        <a href="?section=<?php echo $active_section; ?><?php echo !empty($selected_facility) ? '&facility=' . urlencode($selected_facility) : ''; ?>&field=district" 
           class="field-tab <?php echo $active_field === 'district' ? 'active' : ''; ?>">
            📍 Update District
        </a>
    </div>

    <!-- Info Box -->
    <div class="info-box">
        <h3>📌 How It Works</h3>
        <p>
            This tool allows you to update <strong>Demander</strong>, <strong>DemanderCode_str</strong>, or <strong>District</strong> fields.
        </p>
        <ul>
            <li><strong>NULL Records Tab:</strong> Shows facilities with NULL/empty Demander fields</li>
            <li><strong>Filled Records Tab:</strong> Shows facilities with existing values that you can change</li>
            <li><strong>Update Demander:</strong> Updates Demander and DemanderCode_str using del_point mapping</li>
            <li><strong>Update District:</strong> Updates the district column using the district dropdown list</li>
            <li><strong>Select a facility</strong> to view its records</li>
            <li><strong>Choose update type:</strong> NULL Only, Filled Only, All Records, or Selected Records</li>
            <li><strong>Select a value</strong> from the dropdown and click "Update Records"</li>
        </ul>
    </div>

    <?php 
    // Determine which facilities to show based on active section
    $display_facilities = [];
    $display_counts = [];
    
    if ($active_section === 'null') {
        $display_facilities = $null_facilities;
        $display_counts = $null_facility_counts;
        $section_label = 'NULL';
        $section_icon = '🔴';
    } else {
        $display_facilities = $filled_facilities;
        $display_counts = $filled_facility_counts;
        $section_label = 'Filled';
        $section_icon = '✅';
    }
    
    // If we have a selected facility, ensure it's in the list
    if (!empty($selected_facility) && !in_array($selected_facility, $display_facilities)) {
        if ($active_section === 'null') {
            $check_sql = "SELECT COUNT(*) as count FROM receipts WHERE facility = ? AND (Demander IS NULL OR Demander = '' OR DemanderCode_str IS NULL OR DemanderCode_str = '')";
        } else {
            $check_sql = "SELECT COUNT(*) as count FROM receipts WHERE facility = ? AND Demander IS NOT NULL AND Demander != '' AND DemanderCode_str IS NOT NULL AND DemanderCode_str != ''";
        }
        $check_stmt = $conn->prepare($check_sql);
        $check_stmt->bind_param("s", $selected_facility);
        $check_stmt->execute();
        $check_result = $check_stmt->get_result();
        $check_count = $check_result->fetch_assoc()['count'];
        $check_stmt->close();
        
        if ($check_count > 0) {
            $display_facilities[] = $selected_facility;
            $display_counts[$selected_facility] = $check_count;
        }
    }
    ?>

    <!-- Facility Selection -->
    <div style="margin-bottom:20px;">
        <h3 style="font-size:18px; color:#0b1b2f; margin-bottom:12px;">
            <?php echo $section_icon; ?> <?php echo $section_label; ?> Facilities
            <span class="badge"><?php echo count($display_facilities); ?> facilities</span>
        </h3>
        
        <?php if (empty($display_facilities)): ?>
            <div class="empty-state">
                <div class="icon">🎉</div>
                <h3>No <?php echo strtolower($section_label); ?> Records Found</h3>
                <p>All records in this category are already populated correctly.</p>
                <?php if ($active_section === 'null'): ?>
                    <p style="margin-top:8px;">Try the <strong>"Filled Records"</strong> tab to update existing values.</p>
                <?php else: ?>
                    <p style="margin-top:8px;">Try the <strong>"NULL Records"</strong> tab to fill missing values.</p>
                <?php endif; ?>
            </div>
        <?php else: ?>
            <div class="facility-grid">
                <?php foreach ($display_facilities as $facility): 
                    $count = $display_counts[$facility] ?? 0;
                    $is_selected = ($selected_facility === $facility);
                ?>
                    <a href="?section=<?php echo $active_section; ?>&facility=<?php echo urlencode($facility); ?>&field=<?php echo $active_field; ?>" 
                       class="facility-item <?php echo $is_selected ? 'selected' : ''; ?>"
                       style="<?php echo $is_selected ? 'border-color:#1a4f8a; background:#e6edf8;' : ''; ?>">
                        <div class="facility-info">
                            <div class="facility-name"><?php echo htmlspecialchars($facility); ?></div>
                        </div>
                        <span class="facility-badge <?php echo $active_section === 'null' ? 'null-badge' : 'filled-badge'; ?>">
                            <?php echo number_format($count); ?> records
                        </span>
                        <span class="facility-arrow"><?php echo $is_selected ? '✓' : '→'; ?></span>
                    </a>
                <?php endforeach; ?>
            </div>
        <?php endif; ?>
    </div>

    <!-- Records Display -->
    <?php if (!empty($selected_facility) && $record_count > 0): ?>
        <div class="records-section">
            <div class="table-header">
                <div>
                    <h3>📄 Records for <span style="color:#1a4f8a;"><?php echo htmlspecialchars($selected_facility_display); ?></span></h3>
                    <span class="badge-count">
                        <?php echo $record_count; ?> total records 
                        (<?php echo $null_record_count; ?> NULL, <?php echo $filled_record_count; ?> filled)
                    </span>
                </div>
                <div>
                    <a href="?section=<?php echo $active_section; ?>&field=<?php echo $active_field; ?>" class="btn btn-secondary">✕ Clear Selection</a>
                </div>
            </div>
            
            <form id="recordsForm">
            <table id="recordsTable" class="display" style="width:100%;">
                <thead>
                    <tr>
                        <th class="checkbox-col">
                            <input type="checkbox" id="selectAll" title="Select all rows">
                        </th>
                        <th>ID</th>
                        <th>Invoice No.</th>
                        <th>Order No.</th>
                        <th>Description</th>
                        <th>Qty Received</th>
                        <th>Current Demander</th>
                        <th>Current DemanderCode</th>
                        <th>Current District</th>
                        <th>Status</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($records as $record): 
                        $is_null = (empty($record['Demander']) && empty($record['DemanderCode_str']));
                        $is_filled = !$is_null;
                        $current_district = $record['district'] ?? 'Not Set';
                    ?>
                        <tr>
                            <td class="checkbox-col">
                                <input type="checkbox" name="selected_ids[]" value="<?php echo $record['id']; ?>" 
                                       class="row-checkbox" <?php echo $active_section === 'null' ? ($is_null ? 'checked' : '') : ($is_filled ? 'checked' : ''); ?>>
                            </td>
                            <td><strong><?php echo $record['id']; ?></strong></td>
                            <td><?php echo htmlspecialchars($record['InvoiceNo_str'] ?? 'N/A'); ?></td>
                            <td><?php echo htmlspecialchars($record['OrderNo_str'] ?? 'N/A'); ?></td>
                            <td><?php echo htmlspecialchars(substr($record['packDescription_Str'] ?? '', 0, 50)) . (strlen($record['packDescription_Str'] ?? '') > 50 ? '...' : ''); ?></td>
                            <td><?php echo number_format($record['QtyReceived_int'] ?? 0); ?></td>
                            <td>
                                <?php if ($is_null): ?>
                                    <span style="color:#c0392b;">NULL</span>
                                <?php else: ?>
                                    <span style="color:#1a4f8a;"><?php echo htmlspecialchars($record['Demander']); ?></span>
                                <?php endif; ?>
                            </td>
                            <td>
                                <?php if ($is_null): ?>
                                    <span style="color:#c0392b;">NULL</span>
                                <?php else: ?>
                                    <span style="color:#1a4f8a;"><?php echo htmlspecialchars($record['DemanderCode_str']); ?></span>
                                <?php endif; ?>
                            </td>
                            <td>
                                <span style="color:#6c757d;"><?php echo htmlspecialchars($current_district); ?></span>
                            </td>
                            <td>
                                <?php if ($is_null): ?>
                                    <span class="status-badge null">⚠️ NULL</span>
                                <?php else: ?>
                                    <span class="status-badge filled">✅ Filled</span>
                                <?php endif; ?>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
            </form>
        </div>

        <!-- Update Form -->
        <form method="post" action="index.php" onsubmit="return confirmUpdate()">
            <input type="hidden" name="action" value="update_records">
            <input type="hidden" name="facility" value="<?php echo htmlspecialchars($selected_facility); ?>">
            <input type="hidden" name="update_type" id="updateTypeInput" value="null_only">
            <input type="hidden" name="section" value="<?php echo $active_section; ?>">
            <input type="hidden" name="update_field" id="updateFieldInput" value="<?php echo $active_field; ?>">
            
            <div class="update-area">
                <div class="update-label">
                    <?php if ($active_field === 'district'): ?>
                        📍 Step 3: Update District for Selected Records
                    <?php else: ?>
                        📦 Step 3: Update Demander & DemanderCode_str for Selected Records
                    <?php endif; ?>
                </div>
                
                <!-- Update Type Selection -->
                <div class="update-type-section">
                    <label style="font-weight:600; color:#0b1b2f; display:block; margin-bottom:8px;">
                        Select what to update:
                    </label>
                    <div class="radio-group">
                        <?php if ($active_field === 'demander'): ?>
                            <label>
                                <input type="radio" name="update_type_radio" value="null_only" <?php echo $active_section === 'null' ? 'checked' : ''; ?> onchange="updateTypeChanged('null_only')">
                                <strong>NULL Only</strong>
                                <span style="color:#5e6f88; font-weight:normal;">(<?php echo $null_record_count; ?> records)</span>
                            </label>
                            <label>
                                <input type="radio" name="update_type_radio" value="filled_only" <?php echo $active_section === 'filled' ? 'checked' : ''; ?> onchange="updateTypeChanged('filled_only')">
                                <strong>Filled Only</strong>
                                <span style="color:#5e6f88; font-weight:normal;">(<?php echo $filled_record_count; ?> records)</span>
                            </label>
                        <?php endif; ?>
                        <label>
                            <input type="radio" name="update_type_radio" value="all_records" onchange="updateTypeChanged('all_records')">
                            <strong>All Records</strong>
                            <span style="color:#5e6f88; font-weight:normal;">(<?php echo $record_count; ?> records)</span>
                        </label>
                        <label>
                            <input type="radio" name="update_type_radio" value="selected_ids" onchange="updateTypeChanged('selected_ids')">
                            <strong>Selected Records Only</strong>
                            <span class="help-text">Only update records you check in the table above</span>
                        </label>
                    </div>
                </div>
                
                <!-- Value Selection -->
                <div style="margin:16px 0;">
                    <?php if ($active_field === 'district'): ?>
                        <label style="font-weight:600; color:#0b1b2f; display:block; margin-bottom:8px;">
                            Choose a District:
                        </label>
                        <select name="district" id="valueSelect" required style="width:100%; max-width:500px; padding:12px 16px; border:2px solid #d0d9e6; border-radius:10px; font-size:14px;">
                            <option value="">-- Select District --</option>
                            <?php foreach ($district_list as $district): ?>
                                <option value="<?php echo htmlspecialchars($district['district']); ?>">
                                    <?php echo htmlspecialchars($district['district']); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                        <div id="valuePreview" style="display:none; background:#fff; padding:12px 16px; border-radius:8px; margin:12px 0; border:1px solid #d0e6d8;">
                            <strong>Preview:</strong><br>
                            <span style="color:#1a8a4a; font-weight:600;">District will be set to: <span id="previewValue"></span></span>
                            <span style="margin-left:16px; color:#5e6f88; font-size:13px;" id="previewCount"></span>
                        </div>
                    <?php else: ?>
                        <label style="font-weight:600; color:#0b1b2f; display:block; margin-bottom:8px;">
                            Choose a Delivery Point:
                        </label>
                        <select name="del_point" id="valueSelect" required style="width:100%; max-width:500px; padding:12px 16px; border:2px solid #d0d9e6; border-radius:10px; font-size:14px;">
                            <option value="">-- Select Delivery Point --</option>
                            <?php foreach ($del_point_list as $dp): ?>
                                <option value="<?php echo htmlspecialchars($dp['del_point']); ?>" 
                                        data-demname="<?php echo htmlspecialchars($dp['dem_name']); ?>">
                                    <?php echo htmlspecialchars($dp['dem_name']); ?> (<?php echo htmlspecialchars($dp['del_point']); ?>)
                                </option>
                            <?php endforeach; ?>
                        </select>
                        <div id="valuePreview" style="display:none; background:#fff; padding:12px 16px; border-radius:8px; margin:12px 0; border:1px solid #d0e6d8;">
                            <strong>Preview Mapping:</strong><br>
                            <span style="color:#1a8a4a; font-weight:600;" id="previewDemander"></span>
                            <span style="margin:0 8px;">←</span>
                            <span style="color:#1a4f8a; font-weight:600;" id="previewDemanderCode"></span>
                            <span style="margin-left:16px; color:#5e6f88; font-size:13px;" id="previewCount"></span>
                        </div>
                    <?php endif; ?>
                </div>
                
                <div class="update-info">
                    <div class="info-item">
                        <strong>Facility:</strong> 
                        <span class="value"><?php echo htmlspecialchars($selected_facility_display); ?></span>
                    </div>
                    <div class="info-item">
                        <strong>Records to Update:</strong> 
                        <span class="value" id="recordsToUpdateCount"><?php echo $active_section === 'null' ? $null_record_count : $filled_record_count; ?></span>
                    </div>
                    <div class="info-item">
                        <strong>Field to Update:</strong><br>
                        <span style="color:#1a4f8a; font-weight:600;">
                            <?php echo $active_field === 'district' ? 'District' : 'Demander & DemanderCode_str'; ?>
                        </span>
                    </div>
                </div>
                
                <div style="margin-top:16px;">
                    <button type="submit" class="btn btn-success" style="font-size:16px; padding:12px 32px;" id="updateButton">
                        ⚡ Update Records
                    </button>
                    <button type="button" class="btn btn-secondary" onclick="window.location.href='index.php?section=<?php echo $active_section; ?>&field=<?php echo $active_field; ?>'">
                        Cancel
                    </button>
                </div>
            </div>
        </form>

    <?php elseif (!empty($selected_facility) && $record_count == 0): ?>
        <div class="message warning">
            ⚠️ No records found for facility: <strong><?php echo htmlspecialchars($selected_facility_display); ?></strong>
            <br><a href="index.php?section=<?php echo $active_section; ?>&field=<?php echo $active_field; ?>" class="btn btn-secondary" style="margin-top:8px; display:inline-block;">← Back to Facilities</a>
        </div>
    <?php endif; ?>

    <a href="index.php" class="back-link">← Return to Dashboard</a>
</div>

<script>
$(document).ready(function() {
    <?php if (!empty($selected_facility) && $record_count > 0): ?>
    // Initialize DataTable
    var table = $('#recordsTable').DataTable({
        pageLength: 25,
        responsive: true,
        order: [[1, 'desc']],
        language: {
            search: "Search:",
            lengthMenu: "Show _MENU_ entries",
            info: "Showing _START_ to _END_ of _TOTAL_ entries"
        },
        columnDefs: [
            { orderable: false, targets: 0 } // Disable sorting on checkbox column
        ]
    });
    
    // Handle select all checkbox
    $('#selectAll').on('change', function() {
        const isChecked = this.checked;
        $('.row-checkbox').each(function() {
            this.checked = isChecked;
        });
        updateSelectedCount();
    });
    
    // Handle individual row checkboxes
    $('.row-checkbox').on('change', function() {
        updateSelectedCount();
        const total = $('.row-checkbox').length;
        const checked = $('.row-checkbox:checked').length;
        $('#selectAll').prop('checked', checked === total);
        $('#selectAll').prop('indeterminate', checked > 0 && checked < total);
    });
    
    // Function to update selected count
    function updateSelectedCount() {
        const checked = $('.row-checkbox:checked').length;
        const total = $('.row-checkbox').length;
        const nullRecords = <?php echo $null_record_count; ?>;
        const filledRecords = <?php echo $filled_record_count; ?>;
        
        const updateType = $('input[name="update_type_radio"]:checked').val();
        let count = 0;
        if (updateType === 'null_only') {
            count = nullRecords;
        } else if (updateType === 'filled_only') {
            count = filledRecords;
        } else if (updateType === 'all_records') {
            count = total;
        } else if (updateType === 'selected_ids') {
            count = checked;
        }
        $('#recordsToUpdateCount').text(count);
        $('#previewCount').text('(' + count + ' records will be updated)');
        $('#updateTypeInput').val(updateType);
    }
    
    updateSelectedCount();
    <?php endif; ?>
    
    // Preview when selecting value
    $('#valueSelect').on('change', function() {
        <?php if ($active_field === 'district'): ?>
        const value = $(this).val();
        if (value) {
            $('#previewValue').text(value);
            $('#valuePreview').show();
        } else {
            $('#valuePreview').hide();
        }
        <?php else: ?>
        const selected = $(this).find('option:selected');
        const demName = selected.data('demname');
        const delPoint = selected.val();
        if (delPoint) {
            $('#previewDemander').text(demName);
            $('#previewDemanderCode').text(delPoint);
            $('#valuePreview').show();
        } else {
            $('#valuePreview').hide();
        }
        <?php endif; ?>
    });
    
    <?php if (isset($_GET['updated']) && $_GET['updated'] > 0): ?>
    // Show success animation
    $('.update-area').css('border-color', '#1a8a4a');
    <?php endif; ?>
});

// Update type changed
function updateTypeChanged(type) {
    $('#updateTypeInput').val(type);
    
    const nullRecords = <?php echo $null_record_count; ?>;
    const filledRecords = <?php echo $filled_record_count; ?>;
    const totalRecords = <?php echo $record_count; ?>;
    const checked = $('.row-checkbox:checked').length;
    
    let count = 0;
    if (type === 'null_only') {
        count = nullRecords;
    } else if (type === 'filled_only') {
        count = filledRecords;
    } else if (type === 'all_records') {
        count = totalRecords;
    } else if (type === 'selected_ids') {
        count = checked;
        if (checked === 0) {
            alert('Please select at least one record to update.');
        }
    }
    
    $('#recordsToUpdateCount').text(count);
    $('#previewCount').text('(' + count + ' records will be updated)');
}

// Confirm update
function confirmUpdate() {
    const valueSelect = document.getElementById('valueSelect');
    if (!valueSelect.value) {
        alert('Please select a value first.');
        return false;
    }
    
    const updateType = document.querySelector('input[name="update_type_radio"]:checked');
    if (!updateType) {
        alert('Please select an update type.');
        return false;
    }
    
    if (updateType.value === 'selected_ids') {
        const checked = document.querySelectorAll('.row-checkbox:checked');
        if (checked.length === 0) {
            alert('Please select at least one record to update.');
            return false;
        }
    }
    
    const recordCount = document.getElementById('recordsToUpdateCount').textContent;
    const fieldLabel = '<?php echo $active_field === 'district' ? 'District' : 'Demander & DemanderCode_str'; ?>';
    const valueLabel = valueSelect.options[valueSelect.selectedIndex].text;
    
    let updateTypeText = '';
    if (updateType.value === 'null_only') {
        updateTypeText = 'NULL/empty records only';
    } else if (updateType.value === 'filled_only') {
        updateTypeText = 'Filled (non-NULL) records only';
    } else if (updateType.value === 'all_records') {
        updateTypeText = 'ALL records for this facility';
    } else if (updateType.value === 'selected_ids') {
        updateTypeText = 'selected records only';
    }
    
    return confirm(
        '⚠️ WARNING: You are about to update ' + recordCount + ' record(s).\n\n' +
        'Facility: <?php echo htmlspecialchars($selected_facility_display); ?>\n' +
        'Field: ' + fieldLabel + '\n' +
        'Update Type: ' + updateTypeText + '\n' +
        'New Value: ' + valueLabel + '\n\n' +
        'This action is permanent and cannot be undone.\n\n' +
        '✅ Proceed with update?'
    );
}
</script>
</body>
</html>