<?php
// app/Controllers/ReportController.php

class ReportController {
    private function requireAdmin() {
        if (!isset($_SESSION['user_id']) || !in_array($_SESSION['role'], ['admin','superadmin'])) {
            header('Location: ' . BASE_URL . '/lsign'); exit;
        }
        FeatureGate::enforceSubscription($_SESSION['restaurant_id']);
    }

    public function index() {
        $this->requireAdmin();
        $db      = getDB();
        $rest_id = $_SESSION['restaurant_id'];
        $pageTitle = 'Sales Reports';
        $currency = FeatureGate::currency($rest_id);

        $today     = date('Y-m-d');
        $thisMonth = date('Y-m-01');
        $from      = $_GET['from'] ?? $thisMonth;
        $to        = $_GET['to']   ?? $today;
        
        $limit     = $_GET['limit'] ?? '10';
        $sqlLimit  = ($limit === 'all') ? 999999 : max(1, intval($limit));

        // ── KPI ──────────────────────────────────────────────────
        $kpi = [];

        $r = $db->prepare('SELECT COALESCE(SUM(total),0) AS rev, COUNT(*) AS cnt FROM orders WHERE restaurant_id=? AND payment_status="paid" AND DATE(created_at) BETWEEN ? AND ?');
        $r->execute([$rest_id, $from, $to]); $r = $r->fetch();
        $kpi['revenue'] = $r['rev']; $kpi['orders'] = $r['cnt'];

        $r = $db->prepare('SELECT COALESCE(SUM(amount),0) FROM expenses WHERE restaurant_id=? AND expense_date BETWEEN ? AND ?');
        $r->execute([$rest_id, $from, $to]);
        $kpi['expenses'] = $r->fetchColumn();
        $kpi['profit']   = $kpi['revenue'] - $kpi['expenses'];

        $r = $db->prepare('SELECT COALESCE(AVG(total),0) FROM orders WHERE restaurant_id=? AND payment_status="paid" AND DATE(created_at) BETWEEN ? AND ?');
        $r->execute([$rest_id, $from, $to]);
        $kpi['avg_order'] = $r->fetchColumn();

        // ── Daily breakdown ────────────────────────────────────
        $daily = $db->prepare('SELECT DATE(created_at) AS day, COUNT(*) AS orders, SUM(total) AS revenue FROM orders WHERE restaurant_id=? AND payment_status="paid" AND DATE(created_at) BETWEEN ? AND ? GROUP BY day ORDER BY day ASC');
        $daily->execute([$rest_id, $from, $to]);
        $daily = $daily->fetchAll();

        // ── Payment method split ──────────────────────────────
        $pmethods = $db->prepare('SELECT p.payment_method, COUNT(*) AS cnt, SUM(p.amount) AS total FROM payments p WHERE p.restaurant_id=? AND DATE(p.created_at) BETWEEN ? AND ? GROUP BY p.payment_method');
        $pmethods->execute([$rest_id, $from, $to]);
        $pmethods = $pmethods->fetchAll();

        // ── Top products ──────────────────────────────────────
        $topProducts = $db->prepare('SELECT pr.name, SUM(oi.quantity) AS qty, SUM(oi.subtotal) AS revenue FROM order_items oi JOIN products pr ON pr.id=oi.product_id JOIN orders o ON o.id=oi.order_id WHERE o.restaurant_id=? AND o.payment_status="paid" AND DATE(o.created_at) BETWEEN ? AND ? GROUP BY pr.id, pr.name ORDER BY revenue DESC LIMIT ' . $sqlLimit);
        $topProducts->execute([$rest_id, $from, $to]);
        $topProducts = $topProducts->fetchAll();

        // ── Top categories ────────────────────────────────────
        $topCats = $db->prepare('SELECT c.name, SUM(oi.subtotal) AS revenue FROM order_items oi JOIN products pr ON pr.id=oi.product_id JOIN categories c ON c.id=pr.category_id JOIN orders o ON o.id=oi.order_id WHERE o.restaurant_id=? AND o.payment_status="paid" AND DATE(o.created_at) BETWEEN ? AND ? GROUP BY c.id, c.name ORDER BY revenue DESC LIMIT ' . $sqlLimit);
        $topCats->execute([$rest_id, $from, $to]);
        $topCats = $topCats->fetchAll();

        // ── Cashier sales summary ─────────────────────────────
        $cashierSales = $db->prepare('
            SELECT COALESCE(u.name, "Online QR Order / System") AS cashier_name, o.cashier_id, COUNT(o.id) AS total_orders, SUM(o.total) AS total_revenue
            FROM orders o
            LEFT JOIN users u ON u.id = o.cashier_id
            WHERE o.restaurant_id = ? AND o.payment_status = "paid" AND DATE(o.created_at) BETWEEN ? AND ?
            GROUP BY o.cashier_id, u.name
            ORDER BY total_revenue DESC
        ');
        $cashierSales->execute([$rest_id, $from, $to]);
        $cashierSales = $cashierSales->fetchAll();

        // ── Cashier Sessions ─────────────────────────────────
        $sessions = $db->prepare('SELECT s.*, u.name AS cashier_name, (SELECT COALESCE(SUM(total),0) FROM orders WHERE session_id=s.id AND payment_status="paid") AS session_revenue, (SELECT COUNT(*) FROM orders WHERE session_id=s.id AND payment_status="paid") AS session_orders FROM sessions s JOIN users u ON u.id=s.user_id WHERE s.restaurant_id=? AND (s.status = "open" OR DATE(s.start_time) BETWEEN ? AND ?) ORDER BY (s.status = "open") DESC, s.start_time DESC');
        $sessions->execute([$rest_id, $from, $to]);
        $sessions = $sessions->fetchAll();

        // ── Expenses list ─────────────────────────────────────
        $expenses = $db->prepare('SELECT e.*, u.name AS added_by FROM expenses e JOIN users u ON u.id=e.user_id WHERE e.restaurant_id=? AND e.expense_date BETWEEN ? AND ? ORDER BY e.expense_date DESC');
        $expenses->execute([$rest_id, $from, $to]);
        $expenses = $expenses->fetchAll();

        // ── Void Logs ─────────────────────────────────────────
        $session_id_filter = $_GET['session_id'] ?? null;
        if ($session_id_filter) {
            $voids = $db->prepare('SELECT v.*, u.name AS voided_by_name, o.total, o.order_number FROM void_logs v JOIN users u ON u.id=v.voided_by JOIN orders o ON o.id=v.order_id WHERE v.restaurant_id=? AND o.session_id=? ORDER BY v.created_at DESC LIMIT 20');
            $voids->execute([$rest_id, $session_id_filter]);
        } else {
            $voids = $db->prepare('SELECT v.*, u.name AS voided_by_name, o.total, o.order_number FROM void_logs v JOIN users u ON u.id=v.voided_by JOIN orders o ON o.id=v.order_id WHERE v.restaurant_id=? AND DATE(v.created_at) BETWEEN ? AND ? ORDER BY v.created_at DESC LIMIT 20');
            $voids->execute([$rest_id, $from, $to]);
        }
        $voids = $voids->fetchAll();

        ob_start();
        require 'app/Views/admin/reports/index.php';
        $content = ob_get_clean();
        require 'app/Views/layouts/admin.php';
    }

    public function downloadCustomers() {
        $this->requireAdmin();
        $db = getDB();
        $rest_id = $_SESSION['restaurant_id'];

        $stmt = $db->prepare('
            SELECT DISTINCT name, phone, source FROM (
                SELECT customer_name AS name, phone, "Feedback" AS source 
                FROM feedbacks 
                WHERE restaurant_id = ? AND phone IS NOT NULL AND phone != ""
                UNION
                SELECT delivery_customer_name AS name, delivery_customer_phone AS phone, "Delivery / POS Order" AS source 
                FROM orders 
                WHERE restaurant_id = ? AND delivery_customer_phone IS NOT NULL AND delivery_customer_phone != ""
            ) AS combined_customers 
            ORDER BY name
        ');
        $stmt->execute([$rest_id, $rest_id]);
        $customers = $stmt->fetchAll();

        // Generate CSV
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=customers_list_' . date('Ymd') . '.csv');
        
        $output = fopen('php://output', 'w');
        fputcsv($output, ['Customer Name', 'Phone Number', 'Source Channel']);
        
        foreach ($customers as $c) {
            fputcsv($output, [$c['name'], $c['phone'], $c['source']]);
        }
        fclose($output);
        exit;
    }

    public function storeExpense() {
        $this->requireAdmin();
        $db = getDB();
        $db->prepare('INSERT INTO expenses (restaurant_id, user_id, category, description, amount, expense_date) VALUES (?,?,?,?,?,?)')
           ->execute([$_SESSION['restaurant_id'], $_SESSION['user_id'], $_POST['category'], $_POST['description'] ?? null, $_POST['amount'], $_POST['expense_date']]);
        header('Location: ' . BASE_URL . '/admin/reports?msg=Expense+added'); exit;
    }

    public function deleteExpense() {
        $this->requireAdmin();
        $db = getDB();
        $db->prepare('DELETE FROM expenses WHERE id=? AND restaurant_id=?')->execute([$_POST['id'], $_SESSION['restaurant_id']]);
        header('Location: ' . BASE_URL . '/admin/reports?msg=Expense+deleted'); exit;
    }

    public function deleteVoidOrder() {
        $this->requireAdmin();
        $db = getDB();
        $void_id = $_POST['id'];
        
        $db->beginTransaction();
        try {
            // Find order_id associated with this void log
            $stmt = $db->prepare('SELECT order_id FROM void_logs WHERE id = ? AND restaurant_id = ?');
            $stmt->execute([$void_id, $_SESSION['restaurant_id']]);
            $order_id = $stmt->fetchColumn();

            if ($order_id) {
                // Delete order items
                $db->prepare('DELETE FROM order_items WHERE order_id = ?')->execute([$order_id]);
                // Delete order
                $db->prepare('DELETE FROM orders WHERE id = ? AND restaurant_id = ?')->execute([$order_id, $_SESSION['restaurant_id']]);
            }
            // Delete void log
            $db->prepare('DELETE FROM void_logs WHERE id = ? AND restaurant_id = ?')->execute([$void_id, $_SESSION['restaurant_id']]);
            
            $db->commit();
            header('Location: ' . BASE_URL . '/admin/reports?msg=Void+order+deleted'); exit;
        } catch (\Exception $e) {
            $db->rollBack();
            header('Location: ' . BASE_URL . '/admin/reports?error=' . urlencode($e->getMessage())); exit;
        }
    }

    public function deleteAllVoidOrders() {
        $this->requireAdmin();
        $db = getDB();
        $rest_id = $_SESSION['restaurant_id'];
        
        $db->beginTransaction();
        try {
            // Find all order_ids associated with void logs for this restaurant
            $stmt = $db->prepare('SELECT order_id FROM void_logs WHERE restaurant_id = ?');
            $stmt->execute([$rest_id]);
            $order_ids = $stmt->fetchAll(PDO::FETCH_COLUMN);

            if (!empty($order_ids)) {
                $inQuery = implode(',', array_fill(0, count($order_ids), '?'));
                // Delete order items
                $db->prepare("DELETE FROM order_items WHERE order_id IN ($inQuery)")->execute($order_ids);
                // Delete orders
                $db->prepare("DELETE FROM orders WHERE id IN ($inQuery) AND restaurant_id = ?")->execute(array_merge($order_ids, [$rest_id]));
            }
            // Delete all void logs for this restaurant
            $db->prepare('DELETE FROM void_logs WHERE restaurant_id = ?')->execute([$rest_id]);
            
            $db->commit();
            header('Location: ' . BASE_URL . '/admin/reports?msg=All+void+orders+deleted'); exit;
        } catch (\Exception $e) {
            $db->rollBack();
            header('Location: ' . BASE_URL . '/admin/reports?error=' . urlencode($e->getMessage())); exit;
        }
    }

    public function getSessionOrdersJson() {
        $this->requireAdmin();
        $db = getDB();
        $rest_id = $_SESSION['restaurant_id'];
        $sessionId = $_GET['session_id'] ?? null;
        
        if (!$sessionId) {
            header('Content-Type: application/json');
            echo json_encode([]); exit;
        }
        
        $stmt = $db->prepare('
            SELECT o.id, o.order_number, o.total, o.created_at, o.payment_method, o.table_name,
                   (SELECT GROUP_CONCAT(CONCAT(oi.quantity, "x ", p.name) SEPARATOR ", ") 
                    FROM order_items oi 
                    JOIN products p ON p.id = oi.product_id 
                    WHERE oi.order_id = o.id) AS items_summary
            FROM orders o
            WHERE o.restaurant_id = ? AND o.session_id = ? AND o.payment_status = "paid"
            ORDER BY o.created_at DESC
        ');
        $stmt->execute([$rest_id, $sessionId]);
        
        header('Content-Type: application/json');
        echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC)); exit;
    }

    public function getCashierOrdersJson() {
        $this->requireAdmin();
        $db = getDB();
        $rest_id = $_SESSION['restaurant_id'];
        $cashierId = $_GET['cashier_id'] ?? null;
        $from = $_GET['from'] ?? date('Y-m-01');
        $to = $_GET['to'] ?? date('Y-m-d');
        
        if ($cashierId === 'null' || $cashierId === 'system' || $cashierId === '') {
            $stmt = $db->prepare('
                SELECT o.id, o.order_number, o.total, o.created_at, o.payment_method, o.table_name,
                       (SELECT GROUP_CONCAT(CONCAT(oi.quantity, "x ", p.name) SEPARATOR ", ") 
                        FROM order_items oi 
                        JOIN products p ON p.id = oi.product_id 
                        WHERE oi.order_id = o.id) AS items_summary
                FROM orders o
                WHERE o.restaurant_id = ? AND o.cashier_id IS NULL AND o.payment_status = "paid" AND DATE(o.created_at) BETWEEN ? AND ?
                ORDER BY o.created_at DESC
            ');
            $stmt->execute([$rest_id, $from, $to]);
        } else {
            $stmt = $db->prepare('
                SELECT o.id, o.order_number, o.total, o.created_at, o.payment_method, o.table_name,
                       (SELECT GROUP_CONCAT(CONCAT(oi.quantity, "x ", p.name) SEPARATOR ", ") 
                        FROM order_items oi 
                        JOIN products p ON p.id = oi.product_id 
                        WHERE oi.order_id = o.id) AS items_summary
                FROM orders o
                WHERE o.restaurant_id = ? AND o.cashier_id = ? AND o.payment_status = "paid" AND DATE(o.created_at) BETWEEN ? AND ?
                ORDER BY o.created_at DESC
            ');
            $stmt->execute([$rest_id, $cashierId, $from, $to]);
        }
        
        header('Content-Type: application/json');
        echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC)); exit;
    }
}
