<?php
require_auth();
$pdo = db();
$today = date('Y-m-d');
$monthStart = date('Y-m-01');
$u = current_user();

$canProperties = has_permission('properties.view');
$canBookings   = has_permission('bookings.view');
$canFinance    = has_permission('finance.view');
$canTickets    = has_permission('tickets.view');
$canUsers      = has_permission('users.view');
$isAdmin       = has_permission('properties.manage') || in_array($u['role_name'] ?? '', ['super_admin', 'admin'], true);

// RBAC Filter
$propFilter = '';
$propParams = [];
if (!$isAdmin) {
    if (function_exists('hv4_table_exists') && hv4_table_exists('property_assignments')) {
        $propFilter = ' AND property_id IN (SELECT property_id FROM property_assignments WHERE user_id = ' . (int)$u['id'] . ')';
    } else {
        $propFilter = ' AND property_id IN (SELECT id FROM properties WHERE manager_id = ' . (int)$u['id'] . ')';
    }
}
$propFilterNoId = str_replace('property_id', 'id', $propFilter);

// FAST BATCHED KPI QUERY
$kpiSql = "SELECT ";
$kpiSql .= $canProperties ? "(SELECT COUNT(*) FROM properties WHERE deleted_at IS NULL {$propFilterNoId}) AS properties, " : "NULL AS properties, ";
$kpiSql .= $canBookings ? "(SELECT COUNT(*) FROM bookings WHERE status IN ('confirmed','checked_in') {$propFilter}) AS active_bookings, " : "NULL AS active_bookings, ";
$kpiSql .= $canBookings ? "(SELECT COUNT(*) FROM bookings WHERE check_in='$today' AND status IN ('confirmed','checked_in') {$propFilter}) AS arrivals_today, " : "NULL AS arrivals_today, ";
$kpiSql .= $canBookings ? "(SELECT COUNT(*) FROM bookings WHERE check_out='$today' AND status IN ('confirmed','checked_in','checked_out') {$propFilter}) AS departures_today, " : "NULL AS departures_today, ";
$kpiSql .= $canFinance ? "(SELECT COALESCE(SUM(total),0) FROM bookings WHERE status NOT IN ('cancelled','no_show') AND check_in >= '$monthStart' {$propFilter}) AS revenue_mtd, " : "NULL AS revenue_mtd, ";
$kpiSql .= $canFinance ? "(SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date >= '$monthStart' {$propFilter}) AS expenses_mtd, " : "NULL AS expenses_mtd, ";
$kpiSql .= $canTickets ? "(SELECT COUNT(*) FROM tickets WHERE status IN ('open','assigned','in_progress') {$propFilter}) AS open_tickets, " : "NULL AS open_tickets, ";
$kpiSql .= $canTickets ? "(SELECT COUNT(*) FROM tickets WHERE priority='urgent' AND status NOT IN ('completed','cancelled') {$propFilter}) AS urgent_tickets, " : "NULL AS urgent_tickets, ";
$kpiSql .= $canUsers ? "(SELECT COUNT(*) FROM users WHERE deleted_at IS NULL) AS users" : "NULL AS users";

$kpiData = $pdo->query($kpiSql)->fetch();
$kpi = [
    'properties'       => $kpiData['properties'] !== null ? (int)$kpiData['properties'] : null,
    'active_bookings'  => $kpiData['active_bookings'] !== null ? (int)$kpiData['active_bookings'] : null,
    'arrivals_today'   => $kpiData['arrivals_today'] !== null ? (int)$kpiData['arrivals_today'] : null,
    'departures_today' => $kpiData['departures_today'] !== null ? (int)$kpiData['departures_today'] : null,
    'revenue_mtd'      => $kpiData['revenue_mtd'] !== null ? (float)$kpiData['revenue_mtd'] : null,
    'expenses_mtd'     => $kpiData['expenses_mtd'] !== null ? (float)$kpiData['expenses_mtd'] : null,
    'open_tickets'     => $kpiData['open_tickets'] !== null ? (int)$kpiData['open_tickets'] : null,
    'urgent_tickets'   => $kpiData['urgent_tickets'] !== null ? (int)$kpiData['urgent_tickets'] : null,
    'users'            => $kpiData['users'] !== null ? (int)$kpiData['users'] : null,
];

$trend = [];
if ($canFinance) {
    $trend = $pdo->query("
        SELECT DATE(check_in) AS d, COALESCE(SUM(total),0) AS revenue, COUNT(*) AS bookings
        FROM bookings
        WHERE status NOT IN ('cancelled','no_show') AND check_in >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) {$propFilter}
        GROUP BY DATE(check_in) ORDER BY d
    ")->fetchAll();
}

$occ = [];
if ($canProperties && $canBookings) {
    $occ = $pdo->query("
        SELECT p.name, COALESCE(SUM(DATEDIFF(LEAST(b.check_out, LAST_DAY(CURDATE())), GREATEST(b.check_in, DATE_FORMAT(CURDATE(),'%Y-%m-01')))),0) AS booked_nights
        FROM properties p
        LEFT JOIN bookings b ON b.property_id=p.id AND b.status NOT IN ('cancelled','no_show')
            AND b.check_in <= LAST_DAY(CURDATE()) AND b.check_out >= DATE_FORMAT(CURDATE(),'%Y-%m-01')
        WHERE p.deleted_at IS NULL " . str_replace('property_id', 'p.id', $propFilter) . "
        GROUP BY p.id, p.name
        ORDER BY booked_nights DESC LIMIT 10
    ")->fetchAll();
}

$insights = [];
if ($canTickets && (int)$kpi['urgent_tickets'] > 0) $insights[] = ['type'=>'alert','text'=>"{$kpi['urgent_tickets']} urgent ticket(s) need immediate attention."];
if ($canFinance && (float)$kpi['revenue_mtd'] > 0 && (float)$kpi['expenses_mtd'] > 0) {
    $margin = round(((float)$kpi['revenue_mtd']-(float)$kpi['expenses_mtd'])/max(1,(float)$kpi['revenue_mtd'])*100,1);
    $insights[] = ['type'=>'info','text'=>"Profit margin this month: {$margin}%."];
}
if ($canBookings && (int)$kpi['arrivals_today'] > 0) $insights[] = ['type'=>'success','text'=>"{$kpi['arrivals_today']} guest arrival(s) scheduled today."];
if (empty($insights)) $insights[] = ['type'=>'info','text'=>'All permitted systems normal.'];

$activity = [];
if ($canUsers && $isAdmin) {
    $activity = $pdo->query('SELECT a.*, u.name AS user_name FROM activity_log a LEFT JOIN users u ON u.id=a.user_id ORDER BY a.id DESC LIMIT 15')->fetchAll();
}

// Open tickets list (for dashboard feed — max 6)
$open_tickets_list = [];
if ($canTickets) {
    $open_tickets_list = $pdo->query("
        SELECT t.id, t.title, t.description, t.priority, t.created_at,
               p.name AS property_name
        FROM tickets t
        LEFT JOIN properties p ON p.id = t.property_id
        WHERE t.status IN ('open','assigned','in_progress') {$propFilter}
        ORDER BY FIELD(t.priority,'urgent','high','medium','low'), t.created_at DESC
        LIMIT 6
    ")->fetchAll();
}

// Pending reports (for dashboard feed — max 5)
$pending_reports = [];
if (hv4_table_exists('staff_reports') && ($isAdmin || has_permission('reports.view'))) {
    $pending_reports = $pdo->query("
        SELECT r.id, r.title, r.body, r.type, r.urgent, r.created_at,
               u.name AS author_name
        FROM staff_reports r
        JOIN users u ON u.id = r.created_by
        WHERE r.status = 'pending'
        ORDER BY r.urgent DESC, r.created_at DESC
        LIMIT 5
    ")->fetchAll();
}

json_ok(compact('kpi','trend','occ','insights','activity','open_tickets_list','pending_reports'));
