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

$pageTitle = 'Owner Profit 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('opd_table_exists')) {
    function opd_table_exists(PDO $pdo, string $table): bool {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_name = :table
        ");
        $stmt->execute([':table' => $table]);
        return (int)$stmt->fetchColumn() > 0;
    }
}
if (!function_exists('opd_column_exists')) {
    function opd_column_exists(PDO $pdo, string $table, string $column): bool {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = :table
              AND column_name = :column
        ");
        $stmt->execute([':table' => $table, ':column' => $column]);
        return (int)$stmt->fetchColumn() > 0;
    }
}
if (!function_exists('opd_first_existing_column')) {
    function opd_first_existing_column(PDO $pdo, string $table, array $columns): ?string {
        foreach ($columns as $col) {
            if (opd_column_exists($pdo, $table, $col)) return $col;
        }
        return null;
    }
}

$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_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)
    ) 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 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)
    ) 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)
    ) 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)
    ) 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");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_recipes (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        recipe_item_id INT UNSIGNED NOT NULL,
        recipe_name VARCHAR(180) NOT NULL,
        yield_qty DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
        default_store_id INT UNSIGNED NULL,
        preparation_notes TEXT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        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_recipe_item (recipe_item_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_recipe_lines (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        recipe_id BIGINT UNSIGNED NOT NULL,
        ingredient_item_id INT UNSIGNED NOT NULL,
        qty_required DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        wastage_percent DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
        notes TEXT NULL,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
} catch (Throwable $e) {
    $errors[] = 'Could not prepare owner 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 is_pos_location DESC, location_name ASC")->fetchAll(PDO::FETCH_ASSOC);
} catch (Throwable $e) {}

$kpi = [
    'room_revenue' => 0,
    'pos_revenue' => 0,
    'pos_cogs' => 0,
    'pos_gross_profit' => 0,
    'total_revenue' => 0,
    'gross_profit' => 0,
    'gross_margin' => 0,
    'room_charges' => 0,
    'cash_pos' => 0,
    'supplier_purchases' => 0,
    'supplier_payments' => 0,
    'supplier_balance' => 0,
    'stock_value' => 0,
    'low_stock_count' => 0,
    'low_margin_recipes' => 0,
    'void_sales' => 0,
    'orders_count' => 0,
    'pos_orders_count' => 0,
];

try {
    /*
     * Room revenue: tries folio_transactions first; falls back to orders total columns if present.
     */
    if (opd_table_exists($pdo, 'folio_transactions')) {
        $dateCol = opd_first_existing_column($pdo, 'folio_transactions', ['transaction_date','date','created_at']);
        $amountCol = opd_first_existing_column($pdo, 'folio_transactions', ['amount','debit']);
        if ($dateCol && $amountCol) {
            $typeFilter = "";
            if (opd_column_exists($pdo, 'folio_transactions', 'type')) {
                $typeFilter = " AND LOWER(COALESCE(type,'')) IN ('charge','room','accommodation','accommodation_charge','revenue') ";
            } elseif (opd_column_exists($pdo, 'folio_transactions', 'transaction_type')) {
                $typeFilter = " AND LOWER(COALESCE(transaction_type,'')) IN ('charge','room','accommodation','accommodation_charge','revenue') ";
            }

            $stmt = $pdo->prepare("
                SELECT COALESCE(SUM($amountCol),0)
                FROM folio_transactions
                WHERE DATE($dateCol) BETWEEN :date_from AND :date_to
                $typeFilter
            ");
            $stmt->execute([':date_from' => $dateFrom, ':date_to' => $dateTo]);
            $kpi['room_revenue'] = (float)$stmt->fetchColumn();
        }
    }

    if ($kpi['room_revenue'] <= 0 && opd_table_exists($pdo, 'orders')) {
        $dateCol = opd_first_existing_column($pdo, 'orders', ['check_in','arrival_date','created_at','date_created']);
        $amountCol = opd_first_existing_column($pdo, 'orders', ['total_amount','grand_total','booking_total','amount','total']);
        if ($dateCol && $amountCol) {
            $stmt = $pdo->prepare("
                SELECT COALESCE(SUM($amountCol),0), COUNT(*)
                FROM orders
                WHERE DATE($dateCol) BETWEEN :date_from AND :date_to
            ");
            $stmt->execute([':date_from' => $dateFrom, ':date_to' => $dateTo]);
            $row = $stmt->fetch(PDO::FETCH_NUM);
            $kpi['room_revenue'] = (float)($row[0] ?? 0);
            $kpi['orders_count'] = (int)($row[1] ?? 0);
        }
    }

    /*
     * POS revenue and cost.
     */
    $posWhere = "DATE(po.created_at) BETWEEN :date_from AND :date_to";
    $posParams = [':date_from' => $dateFrom, ':date_to' => $dateTo];

    if ($storeId > 0) {
        $posWhere .= " AND po.store_id = :store_id";
        $posParams[':store_id'] = $storeId;
    }

    $stmt = $pdo->prepare("
        SELECT
            COUNT(DISTINCT po.id) AS order_count,
            COALESCE(SUM(CASE WHEN po.status <> 'void' THEN po.total_amount ELSE 0 END),0) AS pos_revenue,
            COALESCE(SUM(CASE WHEN po.status = 'void' THEN po.total_amount ELSE 0 END),0) AS void_sales,
            COALESCE(SUM(CASE WHEN po.status <> 'void' AND po.payment_method='room_charge' THEN po.total_amount ELSE 0 END),0) AS room_charges,
            COALESCE(SUM(CASE WHEN po.status <> 'void' AND COALESCE(po.payment_method,'') <> 'room_charge' THEN po.total_amount ELSE 0 END),0) AS cash_pos
        FROM pos_orders po
        WHERE $posWhere
    ");
    $stmt->execute($posParams);
    $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

    $kpi['pos_orders_count'] = (int)($row['order_count'] ?? 0);
    $kpi['pos_revenue'] = (float)($row['pos_revenue'] ?? 0);
    $kpi['void_sales'] = (float)($row['void_sales'] ?? 0);
    $kpi['room_charges'] = (float)($row['room_charges'] ?? 0);
    $kpi['cash_pos'] = (float)($row['cash_pos'] ?? 0);

    $stmt = $pdo->prepare("
        SELECT COALESCE(SUM(poi.qty * poi.unit_cost),0)
        FROM pos_order_items poi
        INNER JOIN pos_orders po ON po.id = poi.pos_order_id
        WHERE $posWhere
          AND po.status <> 'void'
    ");
    $stmt->execute($posParams);
    $kpi['pos_cogs'] = (float)$stmt->fetchColumn();

    /*
     * More accurate COGS from actual stock deductions where available.
     */
    if (opd_table_exists($pdo, 'pos_stock_deductions')) {
        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(d.total_cost),0)
            FROM pos_stock_deductions d
            INNER JOIN pos_orders po ON po.id = d.pos_order_id
            WHERE $posWhere
              AND po.status <> 'void'
        ");
        $stmt->execute($posParams);
        $deductionCost = (float)$stmt->fetchColumn();
        if ($deductionCost > 0) $kpi['pos_cogs'] = $deductionCost;
    }

    $kpi['pos_gross_profit'] = $kpi['pos_revenue'] - $kpi['pos_cogs'];
    $kpi['total_revenue'] = $kpi['room_revenue'] + $kpi['pos_revenue'];
    $kpi['gross_profit'] = $kpi['room_revenue'] + $kpi['pos_gross_profit'];
    $kpi['gross_margin'] = $kpi['total_revenue'] > 0 ? ($kpi['gross_profit'] / $kpi['total_revenue']) * 100 : 0;

    /*
     * Supplier purchases and payments.
     */
    $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['supplier_purchases'] = (float)$stmt->fetchColumn();

    $stmt = $pdo->prepare("
        SELECT COALESCE(SUM(amount),0)
        FROM inventory_supplier_payments
        WHERE payment_date BETWEEN :date_from AND :date_to
          AND status='posted'
    ");
    $stmt->execute([':date_from' => $dateFrom, ':date_to' => $dateTo]);
    $kpi['supplier_payments'] = (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();

    /*
     * Stock value and low stock.
     */
    $stockStoreJoin = $storeId > 0 ? "AND sm.store_id = :store_id" : "";
    $stockParams = [];
    if ($storeId > 0) $stockParams[':store_id'] = $storeId;

    $stmt = $pdo->prepare("
        SELECT COALESCE(SUM(balance_qty * avg_cost),0)
        FROM (
            SELECT
                ii.id,
                COALESCE(NULLIF(ii.avg_cost,0), ii.cost_price, 0) AS 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 $stockStoreJoin
            WHERE ii.is_active=1
              AND ii.is_stock_item=1
            GROUP BY ii.id
        ) x
    ");
    $stmt->execute($stockParams);
    $kpi['stock_value'] = (float)$stmt->fetchColumn();

    $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 $stockStoreJoin
            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($stockParams);
    $kpi['low_stock_count'] = (int)$stmt->fetchColumn();

    /*
     * Low margin recipes.
     */
    $stmt = $pdo->query("
        SELECT COUNT(*)
        FROM (
            SELECT
                r.id,
                recipe_item.selling_price,
                COALESCE(SUM((rl.qty_required + (rl.qty_required * (rl.wastage_percent / 100))) * COALESCE(NULLIF(ing.avg_cost,0), ing.cost_price, 0)),0) / GREATEST(r.yield_qty,1) AS cost_per_sale
            FROM inventory_recipes r
            INNER JOIN inventory_items recipe_item ON recipe_item.id = r.recipe_item_id
            LEFT JOIN inventory_recipe_lines rl ON rl.recipe_id = r.id
            LEFT JOIN inventory_items ing ON ing.id = rl.ingredient_item_id
            WHERE r.is_active=1
            GROUP BY r.id
            HAVING recipe_item.selling_price > 0
               AND ((recipe_item.selling_price - cost_per_sale) / recipe_item.selling_price) * 100 < 35
        ) lowm
    ");
    $kpi['low_margin_recipes'] = (int)$stmt->fetchColumn();

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

$posByStore = [];
$posByPayment = [];
$topItems = [];
$supplierBalances = [];
$lowMarginRecipes = [];
$lowStockItems = [];
$dailyRevenue = [];

try {
    $storeFilter = $storeId > 0 ? "AND po.store_id = :store_id" : "";
    $baseParams = [':date_from' => $dateFrom, ':date_to' => $dateTo];
    if ($storeId > 0) $baseParams[':store_id'] = $storeId;

    $stmt = $pdo->prepare("
        SELECT
            sl.location_name,
            COUNT(DISTINCT po.id) AS orders,
            COALESCE(SUM(CASE WHEN po.status <> 'void' THEN po.total_amount ELSE 0 END),0) AS sales,
            COALESCE(SUM(CASE WHEN po.status <> 'void' THEN poi.qty * poi.unit_cost ELSE 0 END),0) AS cost
        FROM pos_orders po
        LEFT JOIN store_locations sl ON sl.id = po.store_id
        LEFT JOIN pos_order_items poi ON poi.pos_order_id = po.id
        WHERE DATE(po.created_at) BETWEEN :date_from AND :date_to
        $storeFilter
        GROUP BY po.store_id, sl.location_name
        ORDER BY sales DESC
    ");
    $stmt->execute($baseParams);
    $posByStore = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $pdo->prepare("
        SELECT
            COALESCE(payment_method,'unknown') AS payment_method,
            COUNT(*) AS orders,
            COALESCE(SUM(CASE WHEN status <> 'void' THEN total_amount ELSE 0 END),0) AS sales
        FROM pos_orders po
        WHERE DATE(created_at) BETWEEN :date_from AND :date_to
        " . ($storeId > 0 ? "AND store_id = :store_id" : "") . "
        GROUP BY payment_method
        ORDER BY sales DESC
    ");
    $stmt->execute($baseParams);
    $posByPayment = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $pdo->prepare("
        SELECT
            poi.item_name,
            COALESCE(SUM(poi.qty),0) AS qty,
            COALESCE(SUM(poi.line_total),0) AS sales,
            COALESCE(SUM(poi.qty * poi.unit_cost),0) AS cost,
            COALESCE(SUM(poi.line_total) - SUM(poi.qty * poi.unit_cost),0) AS 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'
          $storeFilter
        GROUP BY poi.item_id, poi.item_name
        ORDER BY sales DESC
        LIMIT 10
    ");
    $stmt->execute($baseParams);
    $topItems = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $pdo->query("
        SELECT
            s.id,
            s.supplier_name,
            s.payment_terms,
            COALESCE(g.purchase_total,0) AS purchases,
            COALESCE(p.payment_total,0) AS payments,
            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 8
    ");
    $supplierBalances = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $pdo->query("
        SELECT *
        FROM (
            SELECT
                r.id,
                r.recipe_name,
                recipe_item.item_name,
                recipe_item.selling_price,
                COALESCE(SUM((rl.qty_required + (rl.qty_required * (rl.wastage_percent / 100))) * COALESCE(NULLIF(ing.avg_cost,0), ing.cost_price, 0)),0) / GREATEST(r.yield_qty,1) AS cost_per_sale,
                ((recipe_item.selling_price -
                    (COALESCE(SUM((rl.qty_required + (rl.qty_required * (rl.wastage_percent / 100))) * COALESCE(NULLIF(ing.avg_cost,0), ing.cost_price, 0)),0) / GREATEST(r.yield_qty,1))
                ) / NULLIF(recipe_item.selling_price,0)) * 100 AS margin_percent
            FROM inventory_recipes r
            INNER JOIN inventory_items recipe_item ON recipe_item.id = r.recipe_item_id
            LEFT JOIN inventory_recipe_lines rl ON rl.recipe_id = r.id
            LEFT JOIN inventory_items ing ON ing.id = rl.ingredient_item_id
            WHERE r.is_active=1
            GROUP BY r.id
            HAVING recipe_item.selling_price > 0
            ORDER BY margin_percent ASC
            LIMIT 8
        ) x
    ");
    $lowMarginRecipes = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stockStoreJoin = $storeId > 0 ? "AND sm.store_id = :store_id" : "";
    $stockParams = [];
    if ($storeId > 0) $stockParams[':store_id'] = $storeId;

    $stmt = $pdo->prepare("
        SELECT
            ii.item_name,
            ii.item_code,
            ii.reorder_level,
            ii.reorder_qty,
            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 $stockStoreJoin
        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 8
    ");
    $stmt->execute($stockParams);
    $lowStockItems = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt = $pdo->prepare("
        SELECT
            d.sale_date,
            COALESCE(r.room_revenue,0) AS room_revenue,
            COALESCE(p.pos_revenue,0) AS pos_revenue,
            COALESCE(p.pos_cost,0) AS pos_cost
        FROM (
            SELECT DATE(:date_from) + INTERVAL seq DAY AS sale_date
            FROM (
                SELECT 0 seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13
                UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
                UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27
                UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
            ) s
            WHERE DATE(:date_from) + INTERVAL seq DAY <= DATE(:date_to)
        ) d
        LEFT JOIN (
            SELECT DATE(created_at) AS dt, SUM(CASE WHEN status <> 'void' THEN total_amount ELSE 0 END) AS pos_revenue,
                   SUM(CASE WHEN status <> 'void' THEN total_amount * 0 ELSE 0 END) AS pos_cost
            FROM pos_orders
            WHERE DATE(created_at) BETWEEN :date_from2 AND :date_to2
            " . ($storeId > 0 ? "AND store_id = :store_id" : "") . "
            GROUP BY DATE(created_at)
        ) p ON p.dt = d.sale_date
        LEFT JOIN (
            SELECT DATE(:date_from3) AS dt, 0 AS room_revenue
        ) r ON r.dt = d.sale_date
        ORDER BY d.sale_date ASC
    ");
    $dailyParams = [
        ':date_from' => $dateFrom,
        ':date_to' => $dateTo,
        ':date_from2' => $dateFrom,
        ':date_to2' => $dateTo,
        ':date_from3' => $dateFrom,
    ];
    if ($storeId > 0) $dailyParams[':store_id'] = $storeId;
    $stmt->execute($dailyParams);
    $dailyRevenue = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

$chartLabels = [];
$chartPos = [];
foreach ($dailyRevenue as $d) {
    $chartLabels[] = date('d M', strtotime($d['sale_date']));
    $chartPos[] = round((float)$d['pos_revenue'], 2);
}

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

<div class="hero-band no-print">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-gem"></i> Executive View</div>
            <div class="hero-title mt-2">Owner Profit Dashboard</div>
            <div class="hero-subtitle mt-2">
                Rooms, POS, stock cost, supplier exposure, margins and operational alerts in one screen.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <button onclick="window.print()" class="btn btn-light me-2"><i class="bi bi-printer me-2"></i>Print</button>
            <a href="inventory_dashboard.php" class="btn btn-outline-light"><i class="bi bi-speedometer2 me-2"></i>Inventory</a>
        </div>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger no-print">
        <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 mb-4 no-print">
    <div class="card-body">
        <form method="get" class="row g-3 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">POS Store</label>
                <select name="store_id" class="form-select">
                    <option value="0">All stores</option>
                    <?php foreach ($stores as $s): ?>
                        <option value="<?php echo (int)$s['id']; ?>" <?php echo $storeId === (int)$s['id'] ? 'selected' : ''; ?>>
                            <?php echo h($s['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="owner-score mb-4">
    <div>
        <div class="text-white-50 text-uppercase fw-bold small">Estimated Gross Profit</div>
        <div class="owner-score-value"><?php echo money_fmt($kpi['gross_profit']); ?></div>
        <div class="text-white-50">Gross margin: <?php echo money_fmt($kpi['gross_margin']); ?>%</div>
    </div>
    <div class="owner-score-side">
        <div>Total Revenue<br><strong><?php echo money_fmt($kpi['total_revenue']); ?></strong></div>
        <div>Stock Value<br><strong><?php echo money_fmt($kpi['stock_value']); ?></strong></div>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-building"></i></div><div><div class="metric-label">Room Revenue</div><div class="metric-value small-value"><?php echo money_fmt($kpi['room_revenue']); ?></div></div></div></div>
    <div class="col-md-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-success-subtle text-success"><i class="bi bi-cash-register"></i></div><div><div class="metric-label">POS Revenue</div><div class="metric-value small-value"><?php echo money_fmt($kpi['pos_revenue']); ?></div></div></div></div>
    <div class="col-md-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-arrow-down-circle"></i></div><div><div class="metric-label">POS COGS</div><div class="metric-value small-value"><?php echo money_fmt($kpi['pos_cogs']); ?></div></div></div></div>
    <div class="col-md-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-graph-up"></i></div><div><div class="metric-label">POS Gross Profit</div><div class="metric-value small-value"><?php echo money_fmt($kpi['pos_gross_profit']); ?></div></div></div></div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-info-subtle text-info"><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></div></div>
    <div class="col-md-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-secondary-subtle text-secondary"><i class="bi bi-truck"></i></div><div><div class="metric-label">Purchases</div><div class="metric-value small-value"><?php echo money_fmt($kpi['supplier_purchases']); ?></div></div></div></div>
    <div class="col-md-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-dark-subtle text-dark"><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></div></div>
    <div class="col-md-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-exclamation-triangle"></i></div><div><div class="metric-label">Alerts</div><div class="metric-value"><?php echo (int)$kpi['low_stock_count'] + (int)$kpi['low_margin_recipes']; ?></div></div></div></div>
</div>

<div class="row g-4 mb-4">
    <div class="col-xl-8">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">POS Revenue Trend</h5></div>
            <div class="card-body">
                <canvas id="revenueChart" height="260"></canvas>
            </div>
        </div>
    </div>
    <div class="col-xl-4">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Payment Mix</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0">
                    <thead><tr><th>Payment</th><th class="text-end">Orders</th><th class="text-end">Sales</th></tr></thead>
                    <tbody>
                    <?php if (!$posByPayment): ?>
                        <tr><td colspan="3" class="text-center text-muted py-4">No POS sales.</td></tr>
                    <?php else: foreach ($posByPayment as $p): ?>
                        <tr>
                            <td><strong><?php echo h(ucwords(str_replace('_',' ', $p['payment_method']))); ?></strong></td>
                            <td class="text-end"><?php echo (int)$p['orders']; ?></td>
                            <td class="text-end fw-bold"><?php echo money_fmt($p['sales']); ?></td>
                        </tr>
                    <?php endforeach; endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4">
    <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">Top POS Items</h5>
                <a href="pos_sales_report.php" class="btn btn-sm btn-outline-primary no-print">Report</a>
            </div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0">
                    <thead><tr><th>Item</th><th class="text-end">Qty</th><th class="text-end">Sales</th><th class="text-end">Profit</th></tr></thead>
                    <tbody>
                    <?php if (!$topItems): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No item sales yet.</td></tr>
                    <?php else: foreach ($topItems as $item): ?>
                        <tr>
                            <td><strong><?php echo h($item['item_name']); ?></strong></td>
                            <td class="text-end"><?php echo money_fmt($item['qty']); ?></td>
                            <td class="text-end"><?php echo money_fmt($item['sales']); ?></td>
                            <td class="text-end fw-bold <?php echo (float)$item['profit'] < 0 ? 'text-danger' : 'text-success'; ?>"><?php echo money_fmt($item['profit']); ?></td>
                        </tr>
                    <?php endforeach; endif; ?>
                    </tbody>
                </table>
            </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 Exposure</h5>
                <a href="supplier_ledger.php" class="btn btn-sm btn-outline-primary no-print">Ledger</a>
            </div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0">
                    <thead><tr><th>Supplier</th><th>Terms</th><th class="text-end">Purchases</th><th class="text-end">Balance</th></tr></thead>
                    <tbody>
                    <?php if (!$supplierBalances): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No supplier balances.</td></tr>
                    <?php else: foreach ($supplierBalances as $s): ?>
                        <tr>
                            <td><strong><?php echo h($s['supplier_name']); ?></strong></td>
                            <td><?php echo h($s['payment_terms'] ?: '—'); ?></td>
                            <td class="text-end"><?php echo money_fmt($s['purchases']); ?></td>
                            <td class="text-end fw-bold <?php echo (float)$s['balance'] > 0 ? 'text-danger' : 'text-success'; ?>"><?php echo money_fmt($s['balance']); ?></td>
                        </tr>
                    <?php endforeach; endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<div class="row g-4">
    <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 Margin Recipes</h5>
                <a href="recipe_profit_report.php?margin_band=low" class="btn btn-sm btn-outline-primary no-print">Recipe Report</a>
            </div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0">
                    <thead><tr><th>Recipe</th><th class="text-end">Selling</th><th class="text-end">Cost</th><th class="text-end">Margin</th></tr></thead>
                    <tbody>
                    <?php if (!$lowMarginRecipes): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No recipe margin alerts.</td></tr>
                    <?php else: foreach ($lowMarginRecipes as $r): ?>
                        <tr class="<?php echo (float)$r['margin_percent'] < 35 ? 'table-warning' : ''; ?>">
                            <td><strong><?php echo h($r['recipe_name']); ?></strong><div class="small text-muted"><?php echo h($r['item_name']); ?></div></td>
                            <td class="text-end"><?php echo money_fmt($r['selling_price']); ?></td>
                            <td class="text-end"><?php echo money_fmt($r['cost_per_sale']); ?></td>
                            <td class="text-end fw-bold <?php echo (float)$r['margin_percent'] < 35 ? 'text-danger' : 'text-success'; ?>"><?php echo money_fmt($r['margin_percent']); ?>%</td>
                        </tr>
                    <?php endforeach; endif; ?>
                    </tbody>
                </table>
            </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">Low Stock Alerts</h5>
                <a href="low_stock_report.php" class="btn btn-sm btn-outline-primary no-print">Low Stock</a>
            </div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0">
                    <thead><tr><th>Item</th><th class="text-end">Balance</th><th class="text-end">Reorder</th><th class="text-end">Order Qty</th></tr></thead>
                    <tbody>
                    <?php if (!$lowStockItems): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No low stock alerts.</td></tr>
                    <?php else: foreach ($lowStockItems as $i): ?>
                        <tr class="table-warning">
                            <td><strong><?php echo h($i['item_name']); ?></strong><div class="small text-muted"><?php echo h($i['item_code'] ?: ''); ?></div></td>
                            <td class="text-end fw-bold"><?php echo money_fmt($i['balance_qty']); ?></td>
                            <td class="text-end"><?php echo money_fmt($i['reorder_level']); ?></td>
                            <td class="text-end"><?php echo money_fmt($i['reorder_qty']); ?></td>
                        </tr>
                    <?php endforeach; endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<div class="alert alert-info mt-4 no-print">
    <strong>Note:</strong> This dashboard estimates profit using rooms revenue + POS revenue minus POS stock cost. Overheads like salaries, utilities and commissions can be added next.
</div>

<style>
.small-value{font-size:1rem!important;letter-spacing:0!important}
.owner-score{background:linear-gradient(135deg,#0f172a,#1d4ed8);border-radius:28px;padding:28px;color:#fff;display:flex;align-items:center;justify-content:space-between;gap:20px;box-shadow:0 20px 50px rgba(15,23,42,.25)}
.owner-score-value{font-size:2.7rem;font-weight:950;line-height:1.05}
.owner-score-side{display:grid;grid-template-columns:1fr 1fr;gap:14px}
.owner-score-side>div{background:rgba(255,255,255,.12);border:1px solid rgba(255,255,255,.18);border-radius:18px;padding:14px;min-width:150px;color:rgba(255,255,255,.75);font-weight:800}
.owner-score-side strong{font-size:1.2rem;color:#fff}
@media(max-width:768px){.owner-score{flex-direction:column;align-items:flex-start}.owner-score-side{grid-template-columns:1fr;width:100%}}
@media print{
    .no-print,.sidebar,.enterprise-sidebar,.app-sidebar,.topbar,.navbar,.header,.footer{display:none!important}
    .panel-card,.card,.metric-card,.owner-score{box-shadow:none!important}
    body{background:#fff!important}
    .table{font-size:10px}
}
</style>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
document.addEventListener('DOMContentLoaded', function(){
    const labels = <?php echo json_encode($chartLabels); ?>;
    const values = <?php echo json_encode($chartPos); ?>;
    const el = document.getElementById('revenueChart');

    if (el && labels.length) {
        new Chart(el, {
            type: 'line',
            data: {
                labels: labels,
                datasets: [{
                    label: 'POS Revenue',
                    data: values,
                    tension: 0.35,
                    fill: false,
                    borderWidth: 2
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                scales: { y: { beginAtZero: true } }
            }
        });
    }
});
</script>

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