<?php
/**
 * Plugin Name: Inventories/Consumables Tracker
 * Description: Complete version with QR scanning, modal confirmation, PO, units, balance tracking, QR generator and reports. Detects existing log table and logs correctly. Purpose column added.
 * Version: 2.4
 * Author: Afif Azmi (with AI assistance)
 */
if (!defined('ABSPATH')) exit;

/** -----------------------
 * Activation: create tables (if not exists)
 * ----------------------- */
function ct_full_install_fixed() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $items = $wpdb->prefix . 'consumables_items';
    $logs  = $wpdb->prefix . 'consumables_logs';
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

    // Items table (normalized)
    $sql_items = "CREATE TABLE IF NOT EXISTS {$items} (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        item_code varchar(80) NOT NULL,
        item_name varchar(255) NOT NULL,
        unit varchar(40) NOT NULL DEFAULT 'pcs',
        balance float DEFAULT 0,
        active tinyint(1) DEFAULT 1,
        PRIMARY KEY (id),
        UNIQUE KEY item_code (item_code)
    ) {$charset_collate};";
    dbDelta($sql_items);

    // Normalized logs table
    $sql_logs = "CREATE TABLE IF NOT EXISTS {$logs} (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        item_id mediumint(9) NOT NULL,
        item_code varchar(80) NOT NULL,
        item_name varchar(255) NOT NULL,
        qty float NOT NULL,
        scan_action varchar(20) NOT NULL,
        po_number varchar(50) DEFAULT NULL,
        purpose varchar(255) DEFAULT NULL,
        user_id mediumint(9) NOT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) {$charset_collate};";
    dbDelta($sql_logs);
}
register_activation_hook(__FILE__, 'ct_full_install_fixed');

/** -----------------------
 * Helper: detect which logs table exists
 * ----------------------- */
function ct_detect_logs_table() {
    global $wpdb;
    $candidates = array();

    $candidates[] = $wpdb->prefix . 'consumables_logs';
    $candidates[] = 'wphl_consumables_logs';
    $candidates[] = $wpdb->prefix . 'wphl_consumables_logs';
    $candidates[] = 'consumables_logs';
    $candidates = array_unique($candidates);

    $found = null;
    foreach ($candidates as $t) {
        $res = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $t));
        if (!empty($res)) { $found = $t; break; }
    }

    if (!$found) {
        $all = $wpdb->get_col("SHOW TABLES");
        foreach ($all as $t) {
            if (stripos($t, 'consumables_logs') !== false || stripos($t, 'wphl_consumables_logs') !== false || stripos($t, 'consumable_log') !== false) {
                $found = $t;
                break;
            }
        }
    }

    if (!$found) $found = $wpdb->prefix . 'consumables_logs';

    $cols = array();
    $old_format = false;
    $normalized = false;
    if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $found))) {
        $rows = $wpdb->get_results("DESCRIBE {$found}");
        foreach ($rows as $r) $cols[] = $r->Field;
        $lower = array_map('strtolower', $cols);
        foreach ($cols as $c) {
            if (strtolower($c) === 'item code') { $old_format = true; break; }
        }
        if (in_array('item_code', $lower)) $normalized = true;
    }

    return ['table' => $found, 'old_format' => $old_format, 'normalized' => $normalized, 'columns' => $cols];
}

/** -----------------------
 * Admin menus
 * ----------------------- */
add_action('admin_menu', function() {
    add_menu_page('Consumables Tracker', 'Consumables', 'manage_options', 'ct_items', 'ct_items_page', 'dashicons-archive', 26);
    add_submenu_page('ct_items', 'All Items', 'All Items', 'manage_options', 'ct_items', 'ct_items_page');
    add_submenu_page('ct_items', 'Add New', 'Add New', 'manage_options', 'ct_add_page', 'ct_add_item_page');
    add_submenu_page('ct_items', 'Reports', 'Reports', 'manage_options', 'ct_reports', 'ct_reports_page');
});

/** -----------------------
 * All Items Page
 * ----------------------- */
function ct_items_page() {
    global $wpdb;
    $table = $wpdb->prefix . 'consumables_items';
    $items = $wpdb->get_results("SELECT * FROM $table ORDER BY item_name ASC");
    ?>
    <div class="wrap">
      <h1>Consumable Items</h1>
      <table class="widefat fixed striped">
        <thead>
          <tr><th>Code</th><th>Name</th><th>Unit</th><th>Balance</th><th>QR</th></tr>
        </thead>
        <tbody>
        <?php if($items): foreach($items as $it): ?>
          <tr>
            <td><?php echo esc_html($it->item_code); ?></td>
            <td><?php echo esc_html($it->item_name); ?></td>
            <td><?php echo esc_html($it->unit); ?></td>
            <td><?php echo esc_html($it->balance); ?></td>
            <td><button class="button ct-gen-qr" data-code="<?php echo esc_attr($it->item_code); ?>" data-name="<?php echo esc_attr($it->item_name); ?>">Generate QR</button></td>
          </tr>
        <?php endforeach; else: ?>
          <tr><td colspan="5">No items yet.</td></tr>
        <?php endif; ?>
        </tbody>
      </table>
    </div>

    <div id="ct-qr-modal" style="display:none; position:fixed; right:20px; top:80px; background:#fff; border:1px solid #ddd; padding:12px; z-index:99999; border-radius:8px; box-shadow:0 6px 24px rgba(0,0,0,0.15);">
      <div style="display:flex; align-items:center; gap:12px;">
        <div id="ct-qr-canvas" style="width:220px; height:220px;"></div>
        <div>
          <div id="ct-qr-meta" style="font-size:14px; margin-bottom:8px;"></div>
          <p>
            <button id="ct-download-png" class="button">Download PNG</button>
            <button id="ct-print-9" class="button">Print 9-per-A4</button>
            <button id="ct-close-qr" class="button">Close</button>
          </p>
        </div>
      </div>
    </div>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/qrcodejs/1.0.0/qrcode.min.js"></script>
    <script>
    document.addEventListener('DOMContentLoaded', function(){
        var modal = document.getElementById('ct-qr-modal');
        var canvas = document.getElementById('ct-qr-canvas');
        var currentCode = null;
        document.querySelectorAll('.ct-gen-qr').forEach(function(btn){
            btn.addEventListener('click', function(){
                currentCode = this.getAttribute('data-code');
                var name = this.getAttribute('data-name');
                canvas.innerHTML = '';
                new QRCode(canvas, { text: currentCode, width: 200, height: 200 });
                document.getElementById('ct-qr-meta').innerText = currentCode + ' — ' + name;
                modal.style.display = 'block';
            });
        });
        document.getElementById('ct-close-qr').addEventListener('click', function(){ modal.style.display='none'; });
        document.getElementById('ct-download-png').addEventListener('click', function(){
            var img = canvas.querySelector('img');
            if(img){
                var link = document.createElement('a'); link.href = img.src; link.download = (currentCode||'qr')+'.png'; link.click();
            } else { alert('Download not supported'); }
        });
        document.getElementById('ct-print-9').addEventListener('click', function(){
            var html = '<html><head><title>Print QR Labels</title><style>body{font-family:sans-serif}.sheet{display:grid;grid-template-columns:repeat(3,1fr);gap:10px;padding:20px}.label{text-align:center;border:1px solid #ccc;padding:8px}.label img{max-width:120px}</style></head><body><div class="sheet">';
            var img = canvas.querySelector('img'); var src = img?img.src:'';
            for(var i=0;i<9;i++){ html+='<div class="label"><img src="'+src+'"><div style="font-weight:bold;margin-top:8px;">'+(currentCode||'')+'</div></div>'; }
            html+='</div></body></html>'; var w=window.open('','_blank'); w.document.write(html); w.document.close(); w.focus();
        });
    });
    </script>
<?php
}

/** -----------------------
 * Add Item Page
 * ----------------------- */
function ct_add_item_page() { ?>
<div class="wrap"><h1>Add New Item</h1>
<form method="post" action="<?php echo admin_url('admin-post.php'); ?>">
<input type="hidden" name="action" value="ct_add_item">
<?php wp_nonce_field('ct_add_item_nonce'); ?>
<table class="form-table">
<tr><th><label>Code</label></th><td><input name="item_code" type="text" required></td></tr>
<tr><th><label>Name</label></th><td><input name="item_name" type="text" required></td></tr>
<tr><th><label>Unit</label></th>
<td>
<select name="unit_select" id="unit_select">
<option value="pcs">Pieces (pcs)</option>
<option value="kg">Kilograms (kg)</option>
<option value="mtr">Meters (mtr)</option>
<option value="ltr">Liters (ltr)</option>
<option value="box">Box</option>
<option value="custom">Other</option>
</select>
<input type="text" name="unit_custom" id="unit_custom" placeholder="Custom unit" style="margin-left:8px; display:none;">
</td></tr>
<tr><th><label>Starting Balance</label></th><td><input name="balance" type="number" step="0.01" value="0"></td></tr>
</table>
<?php submit_button('Add Item'); ?>
</form>
<script>
document.getElementById('unit_select').addEventListener('change', function(){
  document.getElementById('unit_custom').style.display = this.value=='custom'?'inline-block':'none';
});
</script>
</div>
<?php }

/** -----------------------
 * Handle Add Item
 * ----------------------- */
add_action('admin_post_ct_add_item', function(){
    if(!current_user_can('manage_options')) wp_die('Not allowed');
    check_admin_referer('ct_add_item_nonce');
    global $wpdb;
    $table = $wpdb->prefix.'consumables_items';
    $unit = $_POST['unit_select']=='custom'?sanitize_text_field($_POST['unit_custom']):sanitize_text_field($_POST['unit_select']);
    $wpdb->insert($table,[
        'item_code'=>sanitize_text_field($_POST['item_code']),
        'item_name'=>sanitize_text_field($_POST['item_name']),
        'unit'=>$unit,
        'balance'=>floatval($_POST['balance']),
        'active'=>1
    ],['%s','%s','%s','%f','%d']);
    wp_redirect(admin_url('admin.php?page=ct_items&added=1')); exit;
});

/** -----------------------
 * Reports Page
 * ----------------------- */
function ct_reports_page(){
    global $wpdb;
    $log_table  = $wpdb->prefix.'consumables_logs';
    $item_table = $wpdb->prefix.'consumables_items';

    $logs=$wpdb->get_results("SELECT l.*, i.item_name, i.item_code, u.user_login 
    FROM $log_table l 
    LEFT JOIN $item_table i ON l.item_id=i.id 
    LEFT JOIN {$wpdb->users} u ON l.user_id=u.ID 
    ORDER BY l.created_at DESC LIMIT 200");


    ?>
    <div class="wrap"><h1>Recent Scans (last 200)</h1>
    <table class="widefat fixed striped">
        <thead>
          <tr><th>Item Code</th><th>Item Name</th><th>Qty</th><th>Action</th><th>PO</th><th>Purpose</th><th>User</th><th>Time</th></tr>
        </thead>
        <tbody>
        <?php foreach($logs as $l): ?>
          <tr>
            <td><?php echo esc_html($l->item_code); ?></td>
            <td><?php echo esc_html($l->item_name); ?></td>
            <td><?php echo esc_html($l->qty); ?></td>
            <td><?php echo esc_html($l->scan_action); ?></td>
            <td><?php echo esc_html($l->po_number ?? ''); ?></td>
            <td><?php echo esc_html($l->purpose ?? ''); ?></td>
            <td><?php echo esc_html($l->user_login); ?></td>
            <td><?php echo esc_html($l->created_at); ?></td>
          </tr>
        <?php endforeach; ?>
        </tbody>
    </table></div>
    <?php
}

/** -----------------------
 * Shortcode: ct_scan (inline scanner UI)
 * ----------------------- */
add_shortcode('ct_scan', function(){
    if(!is_user_logged_in()){ auth_redirect(); return; }
    global $wpdb;
    $items_table = $wpdb->prefix.'consumables_items';
    $items = $wpdb->get_results("SELECT * FROM $items_table WHERE active=1 ORDER BY item_name ASC");

    ob_start(); ?>
    <div id="ct-scan-wrapper">
    <p>
        <label>
            Select Item:
            <select id="ct-item">
                <?php foreach($items as $it): ?>
                    <option value="<?php echo esc_attr($it->item_code); ?>">
                        <?php echo esc_html($it->item_code.' - '.$it->item_name); ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </label>

        <label>
            Action:
            <select id="ct-action">
                <option value="in">In</option>
                <option value="out">Out</option>
            </select>
        </label>

        <label id="po-label">
            PO:
            <input type="text" id="ct-po">
        </label>

        <label>
            Qty:
            <input type="number" id="ct-qty" value="1" min="1">
        </label>

        <label>
            Purpose:
            <input type="text" id="ct-purpose" placeholder="Enter purpose">
        </label>

        <button id="ct-start-scan" class="button">Start Scanning</button>
    </p>

    <div id="qr-reader" style="width:100%; display:none;"></div>
    <div id="ct-last" style="margin-top:12px;font-weight:bold;"></div>

    <div id="ct-scan-modal">
        <div id="ct-scan-modal-content"></div>
        <button id="ct-close-modal" class="button">Close</button>
    </div>
</div>

<script src="https://unpkg.com/html5-qrcode"></script>
<script>
(function(){
    var modal = document.getElementById('ct-scan-modal');
    var modalContent = document.getElementById('ct-scan-modal-content');
    var closeBtn = document.getElementById('ct-close-modal');
    var scanning = false;
    var h5 = null;

    function showModal(text){
        modalContent.innerHTML = text;
        modal.style.display = 'block';
    }

    closeBtn.addEventListener('click', function(){
        modal.style.display = 'none';
        scanning = false;
        document.getElementById('ct-start-scan').disabled = false;
    });

    var poLabel = document.getElementById('po-label');
    var actionSelect = document.getElementById('ct-action');
    actionSelect.addEventListener('change', function(){ poLabel.style.display = this.value=='in' ? 'inline-block' : 'none'; });
    poLabel.style.display = actionSelect.value=='in' ? 'inline-block' : 'none';

    document.getElementById('ct-start-scan').addEventListener('click', function(){
        if(scanning) return;
        scanning = true;
        this.disabled = true;
        document.getElementById('qr-reader').style.display='block';
        h5 = new Html5Qrcode("qr-reader");

        Html5Qrcode.getCameras().then(function(devices){
            var cam = devices.find(d => /back|rear|environment/i.test(d.label)) || devices[0];
            h5.start(cam.id, {fps:10, qrbox:250}, function(decodedText){
                h5.stop().then(()=>{});
                var dropdown = document.getElementById('ct-item');
                var scannedCode = decodedText.trim();
                var found=false;
                for(var i=0;i<dropdown.options.length;i++){
                    if(dropdown.options[i].value === scannedCode){ dropdown.selectedIndex=i; found=true; break; }
                }
                if(!found){
                    showModal('<b>Error:</b> scanned code not found in items');
                    return;
                }

                var item_code = dropdown.value;
                var action = document.getElementById('ct-action').value;
                var qty = parseFloat(document.getElementById('ct-qty').value)||1;
                var po = document.getElementById('ct-po').value||'';
                var purpose = document.getElementById('ct-purpose').value||'';

                var itemText = dropdown.options[dropdown.selectedIndex].text;
                var html = '<p><strong>Item:</strong> '+itemText+'</p>';
                html += '<p><strong>Action:</strong> '+action+'</p>';
                html += '<p><strong>Qty:</strong> '+qty+'</p>';
                if(action==='in') html += '<p><strong>PO:</strong> '+(po?po:'(none)')+'</p>';
                html += '<p><strong>Purpose:</strong> '+(purpose?purpose:'(none)')+'</p>';
                html += '<p>Confirm to save this scan to the log.</p>';

                showModal(html);

                var confirmBtn = document.createElement('button');
                confirmBtn.className = 'button';
                confirmBtn.style.marginTop = '8px';
                confirmBtn.innerText = 'Confirm';
                var cancelBtn = document.createElement('button');
                cancelBtn.className = 'button';
                cancelBtn.style.marginLeft = '8px';
                cancelBtn.style.marginTop = '8px';
                cancelBtn.innerText = 'Cancel';
                modalContent.appendChild(confirmBtn);
                modalContent.appendChild(cancelBtn);

                confirmBtn.addEventListener('click', function(){
                    fetch('<?php echo admin_url('admin-ajax.php'); ?>?action=ct_process_scan&item_code='+encodeURIComponent(item_code)+'&qty='+qty+'&action_type='+action+'&po_number='+encodeURIComponent(po)+'&purpose='+encodeURIComponent(purpose))
                    .then(r=>r.json())
                    .then(d=>{
                        if(d.success){
                            document.getElementById('ct-last').innerText = "Scanned: "+d.data.item_code+" | "+d.data.item_name+" | Qty: "+d.data.qty+" | Balance: "+d.data.balance;
                            showModal('<b>Saved:</b> '+d.data.item_code+' | '+d.data.item_name+' | New balance: '+d.data.balance);
                        } else {
                            showModal('<b>Error:</b> '+d.data);
                        }
                    }).catch(e=>{ showModal('<b>Error:</b> scan failed'); });
                });

                cancelBtn.addEventListener('click', function(){
                    modal.style.display='none';
                    scanning = false;
                    document.getElementById('ct-start-scan').disabled = false;
                });

            }, function(err){ console.log(err); });
        }).catch(function(err){
            showModal('<b>Error:</b> camera not available');
            scanning=false;
            document.getElementById('ct-start-scan').disabled=false;
        });
    });
})();
</script>
<?php
return ob_get_clean();
});

/** -----------------------
 * AJAX handler: process scan & log
 * ----------------------- */
add_action('wp_ajax_ct_process_scan', function() {
    if (!is_user_logged_in()) wp_send_json_error("Not logged in");
    global $wpdb;

    // Scan data from frontend
    $item_code = isset($_GET['item_code']) ? trim(sanitize_text_field($_GET['item_code'])) : '';
    $qty       = isset($_GET['qty']) ? floatval($_GET['qty']) : 1;
    $action    = isset($_GET['action_type']) ? sanitize_text_field($_GET['action_type']) : 'out';
    $po        = isset($_GET['po_number']) ? sanitize_text_field($_GET['po_number']) : '';
    $purpose   = isset($_GET['purpose']) ? sanitize_text_field($_GET['purpose']) : '';
    $user_id   = get_current_user_id();

    if (empty($item_code)) wp_send_json_error("No item code provided");

    // Get item
    $item_table = $wpdb->prefix . 'consumables_items';
    $item = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$item_table} WHERE item_code=%s", $item_code));
    if (!$item) wp_send_json_error("Item not found");

    // Update item balance
    $new_balance = $item->balance + ($action === 'in' ? $qty : -$qty);
    $wpdb->update($item_table, ['balance'=>$new_balance], ['id'=>$item->id], ['%f'], ['%d']);

    // Detect log table format
    $detect = ct_detect_logs_table();
    $log_table = $detect['table'];
    $old_format = $detect['old_format'];
    $normalized = $detect['normalized'];

    // PO only for "in" action
    $po_number = ($action === 'in') ? $po : null;

    if ($old_format) {
        // Old format with spaced columns
        $sql = $wpdb->prepare(
            "INSERT INTO {$log_table} (`Item Code`, `Item Name`, `Qty`, `Action`, `PO`, `Purpose`, `User`, `Time`)
             VALUES (%s, %s, %f, %s, %s, %s, %s, %s)",
            $item->item_code,
            $item->item_name,
            $qty,
            $action,
            $po_number,
            $purpose,
            wp_get_current_user()->user_login,
            current_time('mysql')
        );
        $res = $wpdb->query($sql);
        if ($res === false) wp_send_json_error('Logging failed: '.$wpdb->last_error);

    } else {
        // Normalized table
        if (!$wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $log_table))) {
            $charset_collate = $wpdb->get_charset_collate();
            $create_sql = "CREATE TABLE IF NOT EXISTS {$log_table} (
                id mediumint(9) NOT NULL AUTO_INCREMENT,
                item_id mediumint(9) NOT NULL,
                qty float NOT NULL,
                scan_action varchar(20) NOT NULL,
                po_number varchar(50) DEFAULT NULL,
                purpose varchar(255) DEFAULT NULL,
                user_id mediumint(9) NOT NULL,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id)
            ) {$charset_collate};";
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($create_sql);
        }

        $result = $wpdb->insert(
            $log_table,
            [
                'item_id'     => $item->id,
                'qty'         => $qty,
                'scan_action' => $action,
                'po_number'   => $po_number,
                'purpose'     => $purpose,
                'user_id'     => $user_id,
                'created_at'  => current_time('mysql')
            ],
            ['%d','%f','%s','%s','%s','%d','%s']
        );

        if ($result === false) wp_send_json_error('Logging failed: ' . $wpdb->last_error);
    }

    wp_send_json_success([
        'item_name' => $item->item_name,
        'item_code' => $item->item_code,
        'qty'       => $qty,
        'balance'   => $new_balance
    ]);
});






/* style scan page*/
add_action('wp_head', function() {
    ?>
    <style>
    /* ---------- Scan Wrapper ---------- */
    #ct-scan-wrapper {
        max-width: 720px;
        margin: 0 auto;
        padding: 12px;
        border: 1px solid #eee;
        border-radius: 8px;
        box-sizing: border-box;
    }

    #ct-scan-wrapper p {
        display: flex;
        flex-wrap: wrap;
        gap: 12px;
        align-items: center;
    }

    #ct-scan-wrapper label {
        display: flex;
        flex-direction: column;
        font-size: 16px;
        min-width: 120px; /* aligns PO & Qty */
    }

    #ct-scan-wrapper select,
    #ct-scan-wrapper input[type="text"],
    #ct-scan-wrapper input[type="number"],
    #ct-scan-wrapper button {
        font-size: 16px;
        padding: 8px 12px;
        min-height: 40px;
        box-sizing: border-box;
    }

    #ct-scan-wrapper button {
        cursor: pointer;
    }

    /* Mobile stacking */
    @media (max-width: 480px) {
        #ct-scan-wrapper p {
            flex-direction: column;
            align-items: stretch;
        }
        #ct-scan-wrapper label,
        #ct-scan-wrapper button {
            width: 100%;
        }
    }

    /* ---------- Modal ---------- */
    #ct-scan-modal {
        display: none;
        position: fixed;
        top: 50%;
        left: 50%;
        transform: translate(-50%, -50%);
        background: #fff;
        border-radius: 8px;
        border: 2px solid #4CAF50;
        box-shadow: 0 6px 20px rgba(0,0,0,0.2);
        z-index: 99999;
        max-width: 400px;
        width: 90%;
        padding: 16px;
        box-sizing: border-box;
    }

    #ct-scan-modal-content p {
        margin: 8px 0;
        font-size: 16px;
    }

    #ct-scan-modal button {
        font-size: 16px;
        padding: 8px 16px;
        margin-top: 10px;
        cursor: pointer;
        border-radius: 6px;
        border: none;
        background-color: #4CAF50;
        color: #fff;
    }

    #ct-scan-modal button:hover {
        background-color: #45a049;
    }

    #ct-scan-modal button + button {
        margin-left: 8px;
        background-color: #f44336;
    }

    #ct-scan-modal button + button:hover {
        background-color: #e53935;
    }

    @media (max-width: 480px) {
        #ct-scan-modal {
            width: 95%;
            padding: 16px;
        }
        #ct-scan-modal button {
            width: 100%;
            margin-left: 0;
            margin-top: 8px;
        }
    }
    </style>
    <?php
});

