<?php
/**
 * Plugin Name: Consumables System FINAL V9 (Clean ERP Dashboard)
 * Version: 9.0
 */

if (!defined('ABSPATH')) exit;

/* =========================================================
   META TABLE (CATEGORY + LOCATION)
========================================================= */
register_activation_hook(__FILE__, function () {
    global $wpdb;

    $table = $wpdb->prefix . 'consumables_meta';
    $charset = $wpdb->get_charset_collate();

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

    dbDelta("CREATE TABLE IF NOT EXISTS $table (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        item_id mediumint(9) NOT NULL,
        category varchar(100) DEFAULT '',
        location varchar(100) DEFAULT '',
        PRIMARY KEY (id),
        UNIQUE KEY item_id (item_id)
    ) $charset;");
});

/* =========================================================
   MENU
========================================================= */
add_action('admin_menu', function () {

    add_menu_page(
        'Consumables FINAL',
        'Consumables ERP',
        'manage_options',
        'consumables_final',
        'cons_final_inventory_page',
        'dashicons-archive',
        27
    );

    add_submenu_page(
        'consumables_final',
        'Dashboard',
        'Dashboard',
        'manage_options',
        'consumables_final_dashboard',
        'cons_final_dashboard_page'
    );
});

/* =========================================================
   SAVE CATEGORY + LOCATION
========================================================= */
add_action('admin_post_save_meta_final', function () {

    if (!current_user_can('manage_options')) wp_die('Not allowed');
    check_admin_referer('save_meta_final');

    global $wpdb;

    $table = $wpdb->prefix . 'consumables_meta';

    $item_id = intval($_POST['item_id']);
    $category = sanitize_text_field($_POST['category']);
    $location = sanitize_text_field($_POST['location']);

    $exists = $wpdb->get_var($wpdb->prepare(
        "SELECT id FROM $table WHERE item_id=%d",
        $item_id
    ));

    if ($exists) {
        $wpdb->update($table, [
            'category'=>$category,
            'location'=>$location
        ], ['item_id'=>$item_id]);
    } else {
        $wpdb->insert($table, [
            'item_id'=>$item_id,
            'category'=>$category,
            'location'=>$location
        ]);
    }

    wp_redirect(admin_url('admin.php?page=consumables_final'));
    exit;
});

/* =========================================================
   INVENTORY PAGE (FILTER + EDITABLE)
========================================================= */
function cons_final_inventory_page()
{
    global $wpdb;

    $items = $wpdb->prefix . 'consumables_items';
    $meta  = $wpdb->prefix . 'consumables_meta';

    $search = $_GET['search'] ?? '';
    $category = $_GET['category'] ?? '';
    $location = $_GET['location'] ?? '';
    $sort = $_GET['sort'] ?? 'name_asc';

    $order = "i.item_name ASC";
    if ($sort=='name_desc') $order="i.item_name DESC";
    if ($sort=='code') $order="i.item_code ASC";
    if ($sort=='stock_high') $order="i.balance DESC";
    if ($sort=='stock_low') $order="i.balance ASC";

    $where = "WHERE 1=1";

    if ($search) {
        $where .= $wpdb->prepare(
            " AND (i.item_name LIKE %s OR i.item_code LIKE %s)",
            "%$search%",
            "%$search%"
        );
    }

    if ($category) {
        $where .= $wpdb->prepare(" AND m.category=%s", $category);
    }

    if ($location) {
        $where .= $wpdb->prepare(" AND m.location=%s", $location);
    }

    $data = $wpdb->get_results("
        SELECT i.*,
               COALESCE(m.category,'') category,
               COALESCE(m.location,'') location
        FROM $items i
        LEFT JOIN $meta m ON i.id=m.item_id
        $where
        ORDER BY $order
    ");

    $cats = $wpdb->get_col("SELECT DISTINCT category FROM $meta WHERE category!=''");
    $locs = $wpdb->get_col("SELECT DISTINCT location FROM $meta WHERE location!=''");

    ?>

    <div class="wrap">
        <h1>Consumables ERP FINAL</h1>

        <!-- FILTER -->
        <form method="get" style="margin-bottom:15px;">
            <input type="hidden" name="page" value="consumables_final">

            <input name="search" placeholder="Search code / name">

            <select name="category">
                <option value="">Category</option>
                <?php foreach($cats as $c): ?>
                    <option value="<?php echo esc_attr($c); ?>"><?php echo esc_html($c); ?></option>
                <?php endforeach; ?>
            </select>

            <select name="location">
                <option value="">Location</option>
                <?php foreach($locs as $l): ?>
                    <option value="<?php echo esc_attr($l); ?>"><?php echo esc_html($l); ?></option>
                <?php endforeach; ?>
            </select>

            <select name="sort">
                <option value="name_asc">Name ↑</option>
                <option value="name_desc">Name ↓</option>
                <option value="code">Code</option>
                <option value="stock_high">Stock High</option>
                <option value="stock_low">Stock Low</option>
            </select>

            <button class="button">Filter</button>
        </form>

        <!-- TABLE -->
        <table class="widefat striped">
            <thead>
                <tr>
                    <th>Code</th>
                    <th>Name</th>
                    <th>Category</th>
                    <th>Location</th>
                    <th>Balance</th>
                </tr>
            </thead>

            <tbody>
            <?php foreach($data as $i): ?>
                <tr>

                    <td><?php echo esc_html($i->item_code); ?></td>
                    <td><?php echo esc_html($i->item_name); ?></td>

                    <!-- EDIT CATEGORY -->
                    <td>
                        <form method="post" action="<?php echo admin_url('admin-post.php'); ?>">
                            <?php wp_nonce_field('save_meta_final'); ?>
                            <input type="hidden" name="action" value="save_meta_final">
                            <input type="hidden" name="item_id" value="<?php echo $i->id; ?>">
                            <input name="category"
                                   value="<?php echo esc_attr($i->category); ?>"
                                   style="width:90px;">
                    </td>

                    <!-- EDIT LOCATION -->
                    <td>
                            <input name="location"
                                   value="<?php echo esc_attr($i->location); ?>"
                                   style="width:90px;">
                            <button class="button">Save</button>
                        </form>
                    </td>

                    <td><?php echo esc_html($i->balance); ?></td>

                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <?php
}

/* =========================================================
   DASHBOARD (FIXED LOGIC)
========================================================= */
function cons_final_dashboard_page()
{
    global $wpdb;

    $items = $wpdb->prefix . 'consumables_items';
    $logs  = $wpdb->prefix . 'consumables_logs';

    /* KPI */
    $total = $wpdb->get_var("SELECT COUNT(*) FROM $items");
    $zero  = $wpdb->get_var("SELECT COUNT(*) FROM $items WHERE COALESCE(balance,0)=0");

    /* LOW STOCK (TOP 10 ONLY, NO ZERO) */
    $low = $wpdb->get_results("
        SELECT item_name, COALESCE(balance,0) b
        FROM $items
        WHERE COALESCE(balance,0) > 0
        ORDER BY b ASC
        LIMIT 10
    ");

    /* TOP USAGE */
    $top = $wpdb->get_results("
        SELECT i.item_name, SUM(l.qty) t
        FROM $logs l
        LEFT JOIN $items i ON i.id=l.item_id
        WHERE l.scan_action='out'
        GROUP BY l.item_id
        ORDER BY t DESC
        LIMIT 10
    ");

    $ln=[];$lv=[];
    foreach($low as $l){
        $ln[]=$l->item_name.' ('.$l->b.')';
        $lv[]=$l->b;
    }

    $tn=[];$tv=[];
    foreach($top as $t){
        $tn[]=$t->item_name;
        $tv[]=$t->t;
    }

    ?>

    <div class="wrap">
        <h1>Dashboard FINAL</h1>

        <!-- KPI -->
        <div style="display:flex;gap:10px;margin-bottom:15px;">
            <div>Total Items: <?php echo $total; ?></div>
            <div>Zero Stock Items: <?php echo $zero; ?></div>
        </div>

        <!-- CHARTS -->
        <div style="display:grid;grid-template-columns:1fr 1fr;gap:15px;max-width:900px;">
            <canvas id="lowChart" height="230"></canvas>
            <canvas id="topChart" height="230"></canvas>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

    <script>
    new Chart(document.getElementById('lowChart'), {
        type: 'bar',
        data: {
            labels: <?php echo json_encode($ln); ?>,
            datasets: [{
                label: 'Lowest Stock (Top 10)',
                data: <?php echo json_encode($lv); ?>
            }]
        }
    });

    new Chart(document.getElementById('topChart'), {
        type: 'bar',
        data: {
            labels: <?php echo json_encode($tn); ?>,
            datasets: [{
                label: 'Usage (OUT)',
                data: <?php echo json_encode($tv); ?>
            }]
        }
    });
    </script>

    <?php
}