<?php
declare(strict_types=1);

function hv4_generate_insights(): array {
    $pdo = db();
    $insights = [];

    $rev = (float)$pdo->query("SELECT COALESCE(SUM(total),0) FROM bookings WHERE status NOT IN ('cancelled','no_show') AND check_in >= DATE_FORMAT(CURDATE(),'%Y-%m-01')")->fetchColumn();
    $exp = (float)$pdo->query("SELECT COALESCE(SUM(amount),0) FROM expenses WHERE expense_date >= DATE_FORMAT(CURDATE(),'%Y-%m-01')")->fetchColumn();
    $margin = $rev > 0 ? round((($rev - $exp) / $rev) * 100, 1) : 0;
    $insights[] = [
        'insight_type' => 'revenue',
        'title' => 'Revenue intelligence',
        'summary' => "MTD revenue " . number_format($rev, 2) . " with {$margin}% margin after expenses.",
        'score' => min(100, max(0, $margin)),
    ];

    $occ = $pdo->query("SELECT p.name, COUNT(b.id) AS c FROM properties p LEFT JOIN bookings b ON b.property_id=p.id AND b.status IN ('confirmed','checked_in') AND MONTH(b.check_in)=MONTH(CURDATE()) WHERE p.deleted_at IS NULL GROUP BY p.id ORDER BY c ASC LIMIT 1")->fetch();
    if ($occ && (int)$occ['c'] === 0) {
        $insights[] = [
            'insight_type' => 'occupancy',
            'title' => 'Occupancy alert',
            'summary' => ($occ['name'] ?? 'A property') . ' has zero confirmed bookings this month — consider pricing or channel push.',
            'score' => 25,
        ];
    }

    $urgent = (int)$pdo->query("SELECT COUNT(*) FROM tickets WHERE priority='urgent' AND status NOT IN ('completed','cancelled')")->fetchColumn();
    if ($urgent > 0) {
        $insights[] = [
            'insight_type' => 'maintenance',
            'title' => 'Operations risk',
            'summary' => "{$urgent} urgent maintenance ticket(s) require immediate attention.",
            'score' => 90,
        ];
    }

    if (hv4_table_exists('ai_insights')) {
        $del = $pdo->prepare('DELETE FROM ai_insights WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY)');
        $del->execute();
        $ins = $pdo->prepare('INSERT INTO ai_insights (insight_type, title, summary, score) VALUES (?,?,?,?)');
        foreach ($insights as $i) {
            $ins->execute([$i['insight_type'], $i['title'], $i['summary'], $i['score']]);
        }
    }
    return $insights;
}
