<?php
require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();

$pageTitle = 'Inventory Dashboard';

if (!function_exists('h')) {
    function h($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('money_fmt')) {
    function money_fmt($amount): string { return number_format((float)$amount, 2); }
}
if (!function_exists('dash_table_exists')) {
    function dash_table_exists(PDO $pdo, string $tableName): bool {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
        ");
        $stmt->execute([':table_name' => $tableName]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

$errors = [];

try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS store_locations (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            location_code VARCHAR(50) NULL,
            location_name VARCHAR(150) NOT NULL,
            location_type ENUM('main_store','bar','restaurant','curio','kitchen','housekeeping','maintenance','other') NOT NULL DEFAULT 'main_store',
            is_pos_location TINYINT(1) NOT NULL DEFAULT 0,
            allow_room_charge TINYINT(1) NOT NULL DEFAULT 1,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            notes TEXT NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_store_location_code (location_code)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS inventory_item_categories (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            category_name VARCHAR(120) NOT NULL,
            category_type ENUM('stock','food','bar','curio','consumable','service','other') NOT NULL DEFAULT 'stock',
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_inventory_category_name (category_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS inventory_units (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            unit_code VARCHAR(30) NOT NULL,
            unit_name VARCHAR(80) NOT NULL,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_inventory_unit_code (unit_code)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS inventory_items (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            item_code VARCHAR(60) NULL,
            item_name VARCHAR(180) NOT NULL,
            category_id INT UNSIGNED NULL,
            unit_id INT UNSIGNED NULL,
            item_type ENUM('stock','food','bar','curio','consumable','service','recipe','other') NOT NULL DEFAULT 'stock',
            barcode VARCHAR(100) NULL,
            description TEXT NULL,
            cost_price DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            avg_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            selling_price DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            tax_rate DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
            reorder_level DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            reorder_qty DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            is_stock_item TINYINT(1) NOT NULL DEFAULT 1,
            is_pos_item TINYINT(1) NOT NULL DEFAULT 0,
            allow_room_charge TINYINT(1) NOT NULL DEFAULT 1,
            track_expiry TINYINT(1) NOT NULL DEFAULT 0,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_inventory_item_code (item_code),
            INDEX idx_inventory_item_name (item_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS stock_movements (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            item_id INT UNSIGNED NOT NULL,
            store_id INT UNSIGNED NOT NULL,
            movement_type ENUM('OPENING','GRN','ISSUE','SALE','TRANSFER_IN','TRANSFER_OUT','ADJUSTMENT_IN','ADJUSTMENT_OUT','WASTAGE','RETURN') NOT NULL,
            qty_in DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            qty_out DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            total_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            reference_type VARCHAR(60) NULL,
            reference_id BIGINT UNSIGNED NULL,
            notes TEXT NULL,
            movement_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_stock_item_store (item_id, store_id),
            INDEX idx_stock_movement_date (movement_date),
            INDEX idx_stock_reference (reference_type, reference_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS inventory_suppliers (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            supplier_code VARCHAR(60) NULL,
            supplier_name VARCHAR(180) NOT NULL,
            contact_person VARCHAR(150) NULL,
            phone VARCHAR(80) NULL,
            email VARCHAR(150) NULL,
            address TEXT NULL,
            payment_terms VARCHAR(120) NULL,
            tax_number VARCHAR(120) NULL,
            notes TEXT NULL,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_inventory_supplier_name (supplier_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS inventory_grns (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            grn_no VARCHAR(60) NOT NULL,
            supplier_id INT UNSIGNED NULL,
            supplier_name VARCHAR(180) NULL,
            store_id INT UNSIGNED NOT NULL,
            po_id BIGINT UNSIGNED NULL,
            invoice_no VARCHAR(100) NULL,
            grn_date DATE NOT NULL,
            subtotal DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            tax_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            status ENUM('posted','void') NOT NULL DEFAULT 'posted',
            notes TEXT NULL,
            created_by INT UNSIGNED NULL,
            void_reason TEXT NULL,
            voided_at DATETIME NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_grn_no (grn_no),
            INDEX idx_grn_supplier (supplier_id),
            INDEX idx_grn_store (store_id),
            INDEX idx_grn_date (grn_date)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS pos_orders (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            pos_no VARCHAR(60) NOT NULL,
            store_id INT UNSIGNED NOT NULL,
            order_id INT UNSIGNED NULL,
            folio_id INT UNSIGNED NULL,
            guest_name VARCHAR(180) NULL,
            room_label VARCHAR(80) NULL,
            sale_type ENUM('cash','card','transfer','room_charge','complimentary','void') NOT NULL DEFAULT 'cash',
            payment_method VARCHAR(40) NULL,
            subtotal DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            tax_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            discount_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            status ENUM('open','paid','charged_to_room','void') NOT NULL DEFAULT 'open',
            notes TEXT NULL,
            void_reason TEXT NULL,
            voided_at DATETIME NULL,
            voided_by INT UNSIGNED NULL,
            created_by INT UNSIGNED NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_pos_no (pos_no),
            INDEX idx_pos_store (store_id),
            INDEX idx_pos_status (status),
            INDEX idx_pos_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS pos_order_items (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            pos_order_id BIGINT UNSIGNED NOT NULL,
            item_id INT UNSIGNED NOT NULL,
            item_name VARCHAR(180) NOT NULL,
            qty DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
            unit_price DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
            tax_rate DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
            line_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_pos_item_order (pos_order_id),
            INDEX idx_pos_item_item (item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS inventory_supplier_payments (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            payment_no VARCHAR(60) NOT NULL,
            supplier_id INT UNSIGNED NOT NULL,
            payment_date DATE NOT NULL,
            payment_method VARCHAR(50) NULL,
            reference_no VARCHAR(120) NULL,
            amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            notes TEXT NULL,
            status ENUM('posted','void') NOT NULL DEFAULT 'posted',
            created_by INT UNSIGNED NULL,
            void_reason TEXT NULL,
            voided_at DATETIME NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_supplier_payment_no (payment_no)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
} catch (Throwable $e) {
    $errors[] = 'Could not prepare dashboard tables: ' . $e->getMessage();
}

$dateFrom = trim((string)($_GET['date_from'] ?? date('Y-m-01')));
$dateTo = trim((string)($_GET['date_to'] ?? date('Y-m-d')));
$storeId = (int)($_GET['store_id'] ?? 0);

if (strtotime($dateFrom) === false) $dateFrom = date('Y-m-01');
if (strtotime($dateTo) === false) $dateTo = date('Y-m-d');
if (strtotime($dateFrom) > strtotime($dateTo)) {
    $tmp = $dateFrom; $dateFrom = $dateTo; $dateTo = $tmp;
}

$stores = [];
try {
    $stores = $pdo->query("SELECT * FROM store_locations WHERE is_active=1 ORDER BY location_name ASC")->fetchAll(PDO::FETCH_ASSOC);
} catch (Throwable $e) {}

$storeFilterSql = "";
$params = [':date_from' => $dateFrom, ':date_to' => $dateTo];
if ($storeId > 0) {
    $storeFilterSql = " AND store_id = :store_id";
    $params[':store_id'] = $storeId;
}

$kpi = [
    'stock_value' => 0,
    'stock_items' => 0,
    'low_stock' => 0,
    'pos_sales' => 0,
    'room_charges' => 0,
    'grn_total' => 0,
    'supplier_balance' => 0,
    'open_pos_orders' => 0,
];

try {
    $stmt = $pdo->prepare("
        SELECT
            COUNT(*) AS stock_items,
            COALESCE(SUM(balance_qty * avg_cost),0) AS stock_value
        FROM (
            SELECT
                ii.id,
                ii.avg_cost,
                COALESCE(SUM(sm.qty_in - sm.qty_out),0) AS balance_qty
            FROM inventory_items ii
            LEFT JOIN stock_movements sm ON sm.item_id = ii.id " . ($storeId > 0 ? "AND sm.store_id = :store_id" : "") . "
            WHERE ii.is_active=1
              AND ii.is_stock_item=1
            GROUP BY ii.id
        ) x
    ");
    $bind = [];
    if ($storeId > 0) $bind[':store_id'] = $storeId;
    $stmt->execute($bind);
    $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    $kpi['stock_items'] = (int)($row['stock_items'] ?? 0);
    $kpi['stock_value'] = (float)($row['stock_value'] ?? 0);

    $stmt = $pdo->prepare("
        SELECT COUNT(*) FROM (
            SELECT
                ii.id,
                ii.reorder_level,
                COALESCE(SUM(sm.qty_in - sm.qty_out),0) AS balance_qty
            FROM inventory_items ii
            LEFT JOIN stock_movements sm ON sm.item_id = ii.id " . ($storeId > 0 ? "AND sm.store_id = :store_id" : "") . "
            WHERE ii.is_active=1
              AND ii.is_stock_item=1
              AND ii.reorder_level > 0
            GROUP BY ii.id
            HAVING balance_qty <= reorder_level
        ) lowx
    ");
    $stmt->execute($bind);
    $kpi['low_stock'] = (int)$stmt->fetchColumn();

    $posWhere = "DATE(created_at) BETWEEN :date_from AND :date_to AND status <> 'void'";
    $posParams = [':date_from' => $dateFrom, ':date_to' => $dateTo];
    if ($storeId > 0) {
        $posWhere .= " AND store_id = :store_id";
        $posParams[':store_id'] = $storeId;
    }

    $stmt = $pdo->prepare("
        SELECT
            COALESCE(SUM(total_amount),0) AS total_sales,
            COALESCE(SUM(CASE WHEN payment_method='room_charge' THEN total_amount ELSE 0 END),0) AS room_charges,
            COALESCE(SUM(CASE WHEN status='open' THEN 1 ELSE 0 END),0) AS open_orders
        FROM pos_orders
        WHERE $posWhere
    ");
    $stmt->execute($posParams);
    $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    $kpi['pos_sales'] = (float)($row['total_sales'] ?? 0);
    $kpi['room_charges'] = (float)($row['room_charges'] ?? 0);
    $kpi['open_pos_orders'] = (int)($row['open_orders'] ?? 0);

    $grnWhere = "grn_date BETWEEN :date_from AND :date_to AND status='posted'";
    $grnParams = [':date_from' => $dateFrom, ':date_to' => $dateTo];
    if ($storeId > 0) {
        $grnWhere .= " AND store_id = :store_id";
        $grnParams[':store_id'] = $storeId;
    }

    $stmt = $pdo->prepare("SELECT COALESCE(SUM(total_amount),0) FROM inventory_grns WHERE $grnWhere");
    $stmt->execute($grnParams);
    $kpi['grn_total'] = (float)$stmt->fetchColumn();

    $stmt = $pdo->query("
        SELECT
            COALESCE(g.purchase_total,0) - COALESCE(p.payment_total,0) AS balance
        FROM (
            SELECT COALESCE(SUM(total_amount),0) AS purchase_total
            FROM inventory_grns
            WHERE status='posted'
        ) g
        CROSS JOIN (
            SELECT COALESCE(SUM(amount),0) AS payment_total
            FROM inventory_supplier_payments
            WHERE status='posted'
        ) p
    ");
    $kpi['supplier_balance'] = (float)$stmt->fetchColumn();

} catch (Throwable $e) {
    $errors[] = 'Could not calculate dashboard KPIs: ' . $e->getMessage();
}

$stockByStore = [];
$lowStock = [];
$topSelling = [];
$recentMovements = [];
$recentGrns = [];
$supplierBalances = [];
$posByMethod = [];

try {
    $stmt = $pdo->prepare("
        SELECT
            sl.id,
            sl.location_name,
            sl.location_type,
            COALESCE(COUNT(DISTINCT sm.item_id),0) AS item_count,
            COALESCE(SUM((sm.qty_in - sm.qty_out) * sm.unit_cost),0) AS stock_value,
            COALESCE(SUM(sm.qty_in - sm.qty_out),0) AS qty_balance
        FROM store_locations sl
        LEFT JOIN stock_movements sm ON sm.store_id = sl.id
        WHERE sl.is_active=1
          " . ($storeId > 0 ? "AND sl.id = :store_id" : "") . "
        GROUP BY sl.id
        ORDER BY stock_value DESC, sl.location_name ASC
        LIMIT 20
    ");
    $bind = [];
    if ($storeId > 0) $bind[':store_id'] = $storeId;
    $stmt->execute($bind);
    $stockByStore = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $pdo->prepare("
        SELECT
            ii.id,
            ii.item_name,
            ii.item_code,
            ii.reorder_level,
            ii.reorder_qty,
            u.unit_code,
            c.category_name,
            COALESCE(SUM(sm.qty_in - sm.qty_out),0) AS balance_qty,
            COALESCE(SUM((sm.qty_in - sm.qty_out) * sm.unit_cost),0) AS stock_value
        FROM inventory_items ii
        LEFT JOIN inventory_units u ON u.id = ii.unit_id
        LEFT JOIN inventory_item_categories c ON c.id = ii.category_id
        LEFT JOIN stock_movements sm ON sm.item_id = ii.id " . ($storeId > 0 ? "AND sm.store_id = :store_id" : "") . "
        WHERE ii.is_active=1
          AND ii.is_stock_item=1
          AND ii.reorder_level > 0
        GROUP BY ii.id
        HAVING balance_qty <= reorder_level
        ORDER BY (balance_qty - reorder_level) ASC, ii.item_name ASC
        LIMIT 20
    ");
    $stmt->execute($bind);
    $lowStock = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $pdo->prepare("
        SELECT
            poi.item_id,
            poi.item_name,
            COALESCE(SUM(poi.qty),0) AS qty_sold,
            COALESCE(SUM(poi.line_total),0) AS sales_total,
            COALESCE(SUM(poi.qty * poi.unit_cost),0) AS cost_total,
            COALESCE(SUM(poi.line_total) - SUM(poi.qty * poi.unit_cost),0) AS gross_profit
        FROM pos_order_items poi
        INNER JOIN pos_orders po ON po.id = poi.pos_order_id
        WHERE DATE(po.created_at) BETWEEN :date_from AND :date_to
          AND po.status <> 'void'
          " . ($storeId > 0 ? "AND po.store_id = :store_id" : "") . "
        GROUP BY poi.item_id, poi.item_name
        ORDER BY sales_total DESC
        LIMIT 12
    ");
    $stmt->execute($params);
    $topSelling = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $pdo->prepare("
        SELECT
            payment_method,
            COUNT(*) AS order_count,
            COALESCE(SUM(total_amount),0) AS total_amount
        FROM pos_orders
        WHERE DATE(created_at) BETWEEN :date_from AND :date_to
          AND status <> 'void'
          " . ($storeId > 0 ? "AND store_id = :store_id" : "") . "
        GROUP BY payment_method
        ORDER BY total_amount DESC
    ");
    $stmt->execute($params);
    $posByMethod = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $pdo->prepare("
        SELECT
            sm.*,
            ii.item_name,
            ii.item_code,
            sl.location_name
        FROM stock_movements sm
        LEFT JOIN inventory_items ii ON ii.id = sm.item_id
        LEFT JOIN store_locations sl ON sl.id = sm.store_id
        WHERE 1=1 " . ($storeId > 0 ? "AND sm.store_id = :store_id" : "") . "
        ORDER BY sm.movement_date DESC, sm.id DESC
        LIMIT 15
    ");
    $bind = [];
    if ($storeId > 0) $bind[':store_id'] = $storeId;
    $stmt->execute($bind);
    $recentMovements = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $pdo->prepare("
        SELECT
            g.*,
            sl.location_name
        FROM inventory_grns g
        LEFT JOIN store_locations sl ON sl.id = g.store_id
        WHERE g.status='posted'
          " . ($storeId > 0 ? "AND g.store_id = :store_id" : "") . "
        ORDER BY g.grn_date DESC, g.id DESC
        LIMIT 10
    ");
    $stmt->execute($bind);
    $recentGrns = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $pdo->query("
        SELECT
            s.id,
            s.supplier_name,
            s.payment_terms,
            COALESCE(g.purchase_total,0) AS purchase_total,
            COALESCE(p.payment_total,0) AS payment_total,
            COALESCE(g.purchase_total,0) - COALESCE(p.payment_total,0) AS balance
        FROM inventory_suppliers s
        LEFT JOIN (
            SELECT supplier_id, SUM(total_amount) AS purchase_total
            FROM inventory_grns
            WHERE status='posted' AND supplier_id IS NOT NULL
            GROUP BY supplier_id
        ) g ON g.supplier_id = s.id
        LEFT JOIN (
            SELECT supplier_id, SUM(amount) AS payment_total
            FROM inventory_supplier_payments
            WHERE status='posted'
            GROUP BY supplier_id
        ) p ON p.supplier_id = s.id
        ORDER BY balance DESC, s.supplier_name ASC
        LIMIT 10
    ");
    $supplierBalances = $stmt->fetchAll(PDO::FETCH_ASSOC);

} catch (Throwable $e) {
    $errors[] = 'Could not load dashboard lists: ' . $e->getMessage();
}

$chartStores = [];
$chartStoreValues = [];
foreach ($stockByStore as $row) {
    $chartStores[] = $row['location_name'];
    $chartStoreValues[] = round((float)$row['stock_value'], 2);
}

$chartMethods = [];
$chartMethodValues = [];
foreach ($posByMethod as $row) {
    $chartMethods[] = ucwords(str_replace('_', ' ', (string)($row['payment_method'] ?: 'Unknown')));
    $chartMethodValues[] = round((float)$row['total_amount'], 2);
}

$chartTopItems = [];
$chartTopSales = [];
foreach (array_slice($topSelling, 0, 8) as $row) {
    $chartTopItems[] = $row['item_name'];
    $chartTopSales[] = round((float)$row['sales_total'], 2);
}

require_once __DIR__ . '/../../includes/header.php';
?>

<div class="hero-band">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-speedometer2"></i> Stores &amp; POS Inventory</div>
            <div class="hero-title mt-2">Inventory Dashboard</div>
            <div class="hero-subtitle mt-2">
                Control centre for stock value, POS sales, supplier balances, GRNs, low stock and movement activity.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="inventory_items.php" class="btn btn-light me-2"><i class="bi bi-box-seam me-2"></i>Items</a>
            <a href="/rooms/modules/pos/pos.php" class="btn btn-outline-light"><i class="bi bi-cash-register me-2"></i>Open POS</a>
        </div>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger">
        <div class="fw-bold mb-2">Please fix the following:</div>
        <ul class="mb-0"><?php foreach ($errors as $err): ?><li><?php echo h($err); ?></li><?php endforeach; ?></ul>
    </div>
<?php endif; ?>

<div class="card panel-card compact-filter-card mb-2">
    <div class="card-body">
        <form method="get" class="row g-2 align-items-end">
            <div class="col-md-3">
                <label class="form-label">From</label>
                <input type="date" name="date_from" class="form-control" value="<?php echo h($dateFrom); ?>">
            </div>
            <div class="col-md-3">
                <label class="form-label">To</label>
                <input type="date" name="date_to" class="form-control" value="<?php echo h($dateTo); ?>">
            </div>
            <div class="col-md-4">
                <label class="form-label">Store</label>
                <select name="store_id" class="form-select">
                    <option value="0">All stores</option>
                    <?php foreach ($stores as $store): ?>
                        <option value="<?php echo (int)$store['id']; ?>" <?php echo $storeId === (int)$store['id'] ? 'selected' : ''; ?>>
                            <?php echo h($store['location_name']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2 d-grid">
                <button class="btn btn-primary"><i class="bi bi-funnel me-2"></i>Apply</button>
            </div>
        </form>
    </div>
</div>

<div class="row g-2 mb-2">
    <div class="col-md-6 col-xl-3">
        <a href="stock_movements.php" class="metric-card metric-link">
            <div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-boxes"></i></div>
            <div><div class="metric-label">Stock Value</div><div class="metric-value small-value"><?php echo money_fmt($kpi['stock_value']); ?></div></div>
        </a>
    </div>
    <div class="col-md-6 col-xl-3">
        <a href="low_stock_report.php" class="metric-card metric-link">
            <div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-exclamation-triangle"></i></div>
            <div><div class="metric-label">Low Stock Items</div><div class="metric-value"><?php echo (int)$kpi['low_stock']; ?></div></div>
        </a>
    </div>
    <div class="col-md-6 col-xl-3">
        <a href="/rooms/modules/pos/pos_orders.php?date_from=<?php echo h($dateFrom); ?>&date_to=<?php echo h($dateTo); ?>" class="metric-card metric-link">
            <div class="metric-icon bg-success-subtle text-success"><i class="bi bi-cash-register"></i></div>
            <div><div class="metric-label">POS Sales</div><div class="metric-value small-value"><?php echo money_fmt($kpi['pos_sales']); ?></div></div>
        </a>
    </div>
    <div class="col-md-6 col-xl-3">
        <a href="supplier_ledger.php" class="metric-card metric-link">
            <div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-journal-text"></i></div>
            <div><div class="metric-label">Supplier Balance</div><div class="metric-value small-value"><?php echo money_fmt($kpi['supplier_balance']); ?></div></div>
        </a>
    </div>
</div>

<div class="row g-2 mb-2">
    <div class="col-md-6 col-xl-3">
        <a href="grn.php?date_from=<?php echo h($dateFrom); ?>&date_to=<?php echo h($dateTo); ?>" class="metric-card metric-link">
            <div class="metric-icon bg-info-subtle text-info"><i class="bi bi-truck"></i></div>
            <div><div class="metric-label">GRN Purchases</div><div class="metric-value small-value"><?php echo money_fmt($kpi['grn_total']); ?></div></div>
        </a>
    </div>
    <div class="col-md-6 col-xl-3">
        <a href="/rooms/modules/pos/pos_orders.php?payment_method=room_charge&date_from=<?php echo h($dateFrom); ?>&date_to=<?php echo h($dateTo); ?>" class="metric-card metric-link">
            <div class="metric-icon bg-purple-subtle text-purple"><i class="bi bi-door-open"></i></div>
            <div><div class="metric-label">Room Charges</div><div class="metric-value small-value"><?php echo money_fmt($kpi['room_charges']); ?></div></div>
        </a>
    </div>
    <div class="col-md-6 col-xl-3">
        <a href="inventory_items.php" class="metric-card metric-link">
            <div class="metric-icon bg-secondary-subtle text-secondary"><i class="bi bi-tags"></i></div>
            <div><div class="metric-label">Stock Items</div><div class="metric-value"><?php echo (int)$kpi['stock_items']; ?></div></div>
        </a>
    </div>
    <div class="col-md-6 col-xl-3">
        <a href="/rooms/modules/pos/pos_orders.php?status=open" class="metric-card metric-link">
            <div class="metric-icon bg-dark-subtle text-dark"><i class="bi bi-hourglass-split"></i></div>
            <div><div class="metric-label">Open POS Orders</div><div class="metric-value"><?php echo (int)$kpi['open_pos_orders']; ?></div></div>
        </a>
    </div>
</div>

<div class="quick-actions mb-2">
    <a href="purchase_orders.php"><i class="bi bi-clipboard-plus"></i><span>Purchase Orders</span></a>
    <a href="grn.php"><i class="bi bi-truck"></i><span>GRN / Purchases</span></a>
    <a href="stock_take.php"><i class="bi bi-clipboard-check"></i><span>Stock Take</span></a>
    <a href="stock_movements.php"><i class="bi bi-arrow-left-right"></i><span>Movements</span></a>
    <a href="suppliers.php"><i class="bi bi-people"></i><span>Suppliers</span></a>
    <a href="supplier_payments.php"><i class="bi bi-wallet2"></i><span>Supplier Payments</span></a>
    <a href="/rooms/modules/pos/pos.php"><i class="bi bi-cash-register"></i><span>POS</span></a>
    <a href="/rooms/modules/pos/pos_orders.php"><i class="bi bi-receipt"></i><span>POS Orders</span></a>
</div>

<div class="row g-2 mb-2">
    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Stock Value by Store</h5></div>
            <div class="card-body">
                <canvas id="storeChart" height="168"></canvas>
            </div>
        </div>
    </div>
    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">POS Sales by Method</h5></div>
            <div class="card-body">
                <canvas id="methodChart" height="168"></canvas>
            </div>
        </div>
    </div>
    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Top Selling Items</h5></div>
            <div class="card-body">
                <canvas id="topItemsChart" height="168"></canvas>
            </div>
        </div>
    </div>
</div>

<div class="row g-2">
    <div class="col-xl-6">
        <div class="card panel-card h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0">Low Stock Alerts</h5>
                <a href="low_stock_report.php" class="btn btn-sm btn-outline-primary">View Report</a>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead><tr><th>Item</th><th>Category</th><th class="text-end">Balance</th><th class="text-end">Reorder</th><th class="text-end">Value</th></tr></thead>
                        <tbody>
                        <?php if (!$lowStock): ?>
                            <tr><td colspan="5" class="text-center text-muted py-4">No low stock alerts.</td></tr>
                        <?php else: ?>
                            <?php foreach ($lowStock as $row): ?>
                                <tr class="table-warning">
                                    <td><strong><?php echo h($row['item_name']); ?></strong><div class="small text-muted"><?php echo h($row['item_code'] ?: ''); ?> <?php echo h($row['unit_code'] ?: ''); ?></div></td>
                                    <td><?php echo h($row['category_name'] ?: '—'); ?></td>
                                    <td class="text-end fw-bold"><?php echo money_fmt($row['balance_qty']); ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['reorder_level']); ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['stock_value']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
    <div class="col-xl-6">
        <div class="card panel-card h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0">Supplier Balances</h5>
                <a href="supplier_ledger.php" class="btn btn-sm btn-outline-primary">View Ledger</a>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead><tr><th>Supplier</th><th>Terms</th><th class="text-end">Purchases</th><th class="text-end">Payments</th><th class="text-end">Balance</th></tr></thead>
                        <tbody>
                        <?php if (!$supplierBalances): ?>
                            <tr><td colspan="5" class="text-center text-muted py-4">No supplier balances yet.</td></tr>
                        <?php else: ?>
                            <?php foreach ($supplierBalances as $row): ?>
                                <tr>
                                    <td><strong><?php echo h($row['supplier_name']); ?></strong></td>
                                    <td><?php echo h($row['payment_terms'] ?: '—'); ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['purchase_total']); ?></td>
                                    <td class="text-end"><?php echo money_fmt($row['payment_total']); ?></td>
                                    <td class="text-end fw-bold <?php echo (float)$row['balance'] > 0 ? 'text-danger' : 'text-success'; ?>"><?php echo money_fmt($row['balance']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<div class="row g-2 mt-1">
    <div class="col-xl-6">
        <div class="card panel-card h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0">Recent Stock Movements</h5>
                <a href="stock_movements.php" class="btn btn-sm btn-outline-primary">View All</a>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead><tr><th>Date</th><th>Item</th><th>Store</th><th>Type</th><th class="text-end">In</th><th class="text-end">Out</th></tr></thead>
                        <tbody>
                        <?php if (!$recentMovements): ?>
                            <tr><td colspan="6" class="text-center text-muted py-4">No movements yet.</td></tr>
                        <?php else: ?>
                            <?php foreach ($recentMovements as $row): ?>
                                <tr>
                                    <td><?php echo h(date('Y-m-d', strtotime($row['movement_date']))); ?></td>
                                    <td><strong><?php echo h($row['item_name'] ?: 'Item'); ?></strong></td>
                                    <td><?php echo h($row['location_name'] ?: 'Store'); ?></td>
                                    <td><span class="badge text-bg-light border"><?php echo h(str_replace('_',' ', $row['movement_type'])); ?></span></td>
                                    <td class="text-end text-success"><?php echo money_fmt($row['qty_in']); ?></td>
                                    <td class="text-end text-danger"><?php echo money_fmt($row['qty_out']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
    <div class="col-xl-6">
        <div class="card panel-card h-100">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0">Recent GRNs</h5>
                <a href="grn.php" class="btn btn-sm btn-outline-primary">View All</a>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead><tr><th>Date</th><th>GRN</th><th>Supplier</th><th>Store</th><th class="text-end">Total</th></tr></thead>
                        <tbody>
                        <?php if (!$recentGrns): ?>
                            <tr><td colspan="5" class="text-center text-muted py-4">No GRNs yet.</td></tr>
                        <?php else: ?>
                            <?php foreach ($recentGrns as $row): ?>
                                <tr>
                                    <td><?php echo h($row['grn_date']); ?></td>
                                    <td><strong><?php echo h($row['grn_no']); ?></strong></td>
                                    <td><?php echo h($row['supplier_name'] ?: '—'); ?></td>
                                    <td><?php echo h($row['location_name'] ?: 'Store'); ?></td>
                                    <td class="text-end fw-bold"><?php echo money_fmt($row['total_amount']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<style>
.small-value{font-size:1rem!important;letter-spacing:0!important}
.metric-link{text-decoration:none;color:inherit;transition:.15s ease}
.metric-link:hover{transform:translateY(-2px);box-shadow:0 16px 34px rgba(15,23,42,.12)}
.quick-actions{display:grid;grid-template-columns:repeat(8,minmax(0,1fr));gap:12px}
.quick-actions a{text-decoration:none;color:#0f172a;background:#fff;border:1px solid rgba(15,23,42,.08);border-radius:18px;padding:14px 10px;text-align:center;box-shadow:0 10px 24px rgba(15,23,42,.06);font-weight:900;display:flex;flex-direction:column;gap:7px;align-items:center;justify-content:center;min-height:86px}
.quick-actions a i{font-size:1.35rem;color:#2563eb}
.text-purple{color:#7c3aed!important}.bg-purple-subtle{background:#f3e8ff!important}
@media(max-width:1200px){.quick-actions{grid-template-columns:repeat(4,minmax(0,1fr))}}
@media(max-width:768px){.quick-actions{grid-template-columns:repeat(2,minmax(0,1fr))}}

/* ===== ULTRA COMPACT DASHBOARD OVERRIDES ===== */
.hero-band{
    padding:10px 14px !important;
    margin-bottom:8px !important;
    border-radius:16px !important;
}
.hero-band .row{
    --bs-gutter-y:.35rem !important;
}
.page-eyebrow{
    font-size:.68rem !important;
    letter-spacing:.08em !important;
    margin-bottom:1px !important;
}
.hero-title{
    font-size:1.22rem !important;
    line-height:1.12 !important;
    margin-top:2px !important;
    margin-bottom:0 !important;
}
.hero-subtitle{
    font-size:.76rem !important;
    line-height:1.15 !important;
    margin-top:3px !important;
    max-width:900px;
}
.hero-band .btn{
    padding:.32rem .62rem !important;
    font-size:.78rem !important;
    border-radius:10px !important;
}
.compact-filter-card{
    margin-bottom:8px !important;
}
.compact-filter-card .card-body,
.panel-card .card-body{
    padding:8px 10px !important;
}
.form-label{
    font-size:.68rem !important;
    font-weight:800 !important;
    margin-bottom:1px !important;
    color:#64748b !important;
}
.form-control,
.form-select{
    height:30px !important;
    min-height:30px !important;
    padding:.22rem .5rem !important;
    font-size:.78rem !important;
    border-radius:9px !important;
}
.btn{
    border-radius:9px;
}
.metric-card{
    padding:7px 9px !important;
    gap:8px !important;
    min-height:54px !important;
    border-radius:14px !important;
}
.metric-icon{
    width:30px !important;
    height:30px !important;
    min-width:30px !important;
    border-radius:10px !important;
    font-size:13px !important;
}
.metric-label{
    font-size:.68rem !important;
    line-height:1.05 !important;
    margin-bottom:1px !important;
}
.metric-value{
    font-size:1rem !important;
    line-height:1.05 !important;
}
.metric-value.small-value,
.small-value{
    font-size:.86rem !important;
}
.quick-actions{
    gap:6px !important;
    margin-bottom:8px !important;
}
.quick-actions a{
    padding:7px 5px !important;
    min-height:52px !important;
    border-radius:12px !important;
    font-size:.68rem !important;
    gap:3px !important;
    box-shadow:0 5px 14px rgba(15,23,42,.055) !important;
}
.quick-actions a i{
    font-size:.95rem !important;
}
.card.panel-card{
    border-radius:14px !important;
    margin-bottom:8px !important;
}
.panel-card .card-header{
    padding:8px 10px !important;
    min-height:auto !important;
}
.panel-card .card-header h5{
    font-size:.92rem !important;
    margin:0 !important;
}
.panel-card .card-header .btn{
    padding:.22rem .48rem !important;
    font-size:.72rem !important;
}
.panel-card .card-body canvas{
    max-height:168px !important;
}
.table-responsive{
    max-height:260px;
    overflow:auto;
}
.table-modern thead th{
    position:sticky;
    top:0;
    z-index:10;
    background:#fff !important;
    box-shadow:0 1px 0 #e5e7eb;
}
.table-modern th,
.table-modern td{
    padding:6px 8px !important;
    font-size:.78rem !important;
    vertical-align:middle !important;
}
.table-modern thead th{
    font-size:.68rem !important;
    text-transform:uppercase;
    letter-spacing:.04em;
}
.table-modern .small,
.table-modern small{
    font-size:.66rem !important;
}
.badge{
    font-size:.66rem !important;
    padding:.25em .48em !important;
}
.alert{
    padding:.55rem .75rem !important;
    margin-bottom:8px !important;
    font-size:.82rem !important;
}
@media(min-width:1400px){
    .row.g-2.mb-2 > .col-md-6.col-xl-3{
        flex:0 0 12.5%;
        max-width:12.5%;
    }
}
@media(max-width:1200px){
    .quick-actions{grid-template-columns:repeat(4,minmax(0,1fr)) !important;}
}
@media(max-width:768px){
    .quick-actions{grid-template-columns:repeat(2,minmax(0,1fr)) !important;}
    .hero-band .text-lg-end{text-align:left !important;}
}

</style>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
document.addEventListener('DOMContentLoaded', function(){
    const storeLabels = <?php echo json_encode($chartStores); ?>;
    const storeValues = <?php echo json_encode($chartStoreValues); ?>;
    const methodLabels = <?php echo json_encode($chartMethods); ?>;
    const methodValues = <?php echo json_encode($chartMethodValues); ?>;
    const topLabels = <?php echo json_encode($chartTopItems); ?>;
    const topValues = <?php echo json_encode($chartTopSales); ?>;

    function makeChart(id, type, labels, values) {
        const el = document.getElementById(id);
        if (!el) return;

        if (!labels.length) {
            const ctx = el.getContext('2d');
            ctx.font = '14px Arial';
            ctx.fillStyle = '#64748b';
            ctx.fillText('No data yet', 20, 40);
            return;
        }

        new Chart(el, {
            type: type,
            data: {
                labels: labels,
                datasets: [{
                    data: values,
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: { legend: { display: type === 'doughnut' } },
                scales: type === 'doughnut' ? {} : {
                    y: { beginAtZero: true }
                }
            }
        });
    }

    makeChart('storeChart', 'bar', storeLabels, storeValues);
    makeChart('methodChart', 'doughnut', methodLabels, methodValues);
    makeChart('topItemsChart', 'bar', topLabels, topValues);
});
</script>

<?php require_once __DIR__ . '/../../includes/footer.php'; ?>
