<?php
/**
 * Forecast Alerts Engine
 * Place: /rooms/modules/reports/forecast_alerts.php
 *
 * This file is designed to be included by alerts.php and alerts_count.php.
 * It returns forecast alerts based on the next 14 days:
 * - Overbooked days
 * - Fewer than 5 rooms left
 * - Fewer than 10 rooms left
 * - Occupancy >= 95%
 */

if (!function_exists('forecast_alerts_table_exists')) {
    function forecast_alerts_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;
    }
}

if (!function_exists('forecast_alerts_column_exists')) {
    function forecast_alerts_column_exists(PDO $pdo, string $tableName, string $columnName): bool
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
              AND column_name = :column_name
        ");
        $stmt->execute([
            ':table_name' => $tableName,
            ':column_name' => $columnName,
        ]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

if (!function_exists('get_forecast_alerts')) {
    function get_forecast_alerts(PDO $pdo, int $propertyId = 0, int $daysAhead = 14): array
    {
        $alerts = [
            'critical' => [],
            'warning' => [],
            'info' => [],
            'counts' => [
                'critical' => 0,
                'warning' => 0,
                'info' => 0,
                'total' => 0,
                'overbooked_days' => 0,
                'low_inventory_days' => 0,
                'high_occupancy_days' => 0,
            ],
        ];

        foreach (['orders', 'order_rooming', 'rooms', 'room_types', 'properties'] as $table) {
            if (!forecast_alerts_table_exists($pdo, $table)) {
                return $alerts;
            }
        }

        $hasOrderStatus = forecast_alerts_column_exists($pdo, 'orders', 'status');
        $hasOrderCancelled = forecast_alerts_column_exists($pdo, 'orders', 'cancelled');
        $hasRoomingCancelled = forecast_alerts_column_exists($pdo, 'order_rooming', 'cancelled');
        $hasRoomingProperty = forecast_alerts_column_exists($pdo, 'order_rooming', 'property_id');
        $hasRoomActive = forecast_alerts_column_exists($pdo, 'rooms', 'is_active');
        $hasRoomStatus = forecast_alerts_column_exists($pdo, 'rooms', 'status');

        $today = date('Y-m-d');
        $end = date('Y-m-d', strtotime('+' . max(1, $daysAhead - 1) . ' days'));

        $orderActiveSql = '';
        if ($hasOrderCancelled) {
            $orderActiveSql .= " AND COALESCE(o.cancelled, 0) = 0 ";
        }
        if ($hasOrderStatus) {
            $orderActiveSql .= " AND LOWER(COALESCE(o.status, '')) NOT IN ('cancelled','canceled','void','deleted') ";
        }

        $roomingActiveSql = '';
        if ($hasRoomingCancelled) {
            $roomingActiveSql .= " AND COALESCE(orm.cancelled, 0) = 0 ";
        }

        $roomWhere = " 1 = 1 ";
        $roomParams = [];

        if ($propertyId > 0) {
            $roomWhere .= " AND COALESCE(r.property_id, rt.property_id) = :room_property_id ";
            $roomParams[':room_property_id'] = $propertyId;
        }

        if ($hasRoomActive) {
            $roomWhere .= " AND COALESCE(r.is_active, 1) = 1 ";
        }

        if ($hasRoomStatus) {
            $roomWhere .= " AND (r.status IS NULL OR r.status NOT IN ('inactive','out_of_order','blocked')) ";
        }

        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM rooms r
            LEFT JOIN room_types rt ON rt.id = r.room_type_id
            WHERE $roomWhere
        ");
        $stmt->execute($roomParams);
        $totalRooms = (int)($stmt->fetchColumn() ?: 0);

        if ($totalRooms <= 0) {
            return $alerts;
        }

        $cursor = new DateTime($today);
        $last = new DateTime($end);

        while ($cursor <= $last) {
            $day = $cursor->format('Y-m-d');
            $daySql = $pdo->quote($day);

            $where = "
                orm.check_in <= $daySql
                AND orm.check_out > $daySql
                $roomingActiveSql
                $orderActiveSql
            ";

            $params = [];

            if ($propertyId > 0) {
                if ($hasRoomingProperty) {
                    $where .= " AND COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id) = :property_id ";
                } else {
                    $where .= " AND COALESCE(o.property_id, r.property_id, rt.property_id) = :property_id ";
                }
                $params[':property_id'] = $propertyId;
            }

            $stmt = $pdo->prepare("
                SELECT
                    COUNT(DISTINCT orm.id) AS booked_rooms,
                    COUNT(DISTINCT o.id) AS bookings
                FROM order_rooming orm
                LEFT JOIN orders o ON o.id = orm.order_id
                LEFT JOIN rooms r ON r.id = orm.room_id
                LEFT JOIN room_types rt ON rt.id = orm.room_type_id
                WHERE $where
            ");
            $stmt->execute($params);
            $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

            $booked = (int)($row['booked_rooms'] ?? 0);
            $bookings = (int)($row['bookings'] ?? 0);
            $roomsLeft = $totalRooms - $booked;
            $occupancy = $totalRooms > 0 ? ($booked / $totalRooms) * 100 : 0;

            $alert = [
                'date' => $day,
                'day_name' => date('D', strtotime($day)),
                'available_rooms' => $totalRooms,
                'booked_rooms' => $booked,
                'rooms_left' => $roomsLeft,
                'occupancy_percent' => round($occupancy, 2),
                'bookings' => $bookings,
                'url' => 'forecast_report.php?date_from=' . urlencode($day) . '&date_to=' . urlencode($day) . ($propertyId > 0 ? '&property_id=' . (int)$propertyId : ''),
            ];

            if ($roomsLeft < 0) {
                $alert['severity'] = 'critical';
                $alert['title'] = 'Overbooking risk detected';
                $alert['message'] = abs($roomsLeft) . ' room(s) over capacity on ' . $day . '.';
                $alerts['critical'][] = $alert;
                $alerts['counts']['overbooked_days']++;
            } elseif ($roomsLeft < 5) {
                $alert['severity'] = 'critical';
                $alert['title'] = 'Very low room inventory';
                $alert['message'] = 'Only ' . $roomsLeft . ' room(s) left on ' . $day . '.';
                $alerts['critical'][] = $alert;
                $alerts['counts']['low_inventory_days']++;
            } elseif ($roomsLeft < 10) {
                $alert['severity'] = 'warning';
                $alert['title'] = 'Low room inventory';
                $alert['message'] = 'Only ' . $roomsLeft . ' room(s) left on ' . $day . '.';
                $alerts['warning'][] = $alert;
                $alerts['counts']['low_inventory_days']++;
            } elseif ($occupancy >= 95) {
                $alert['severity'] = 'warning';
                $alert['title'] = 'High occupancy forecast';
                $alert['message'] = number_format($occupancy, 2) . '% occupancy forecast on ' . $day . '.';
                $alerts['warning'][] = $alert;
                $alerts['counts']['high_occupancy_days']++;
            }

            $cursor->modify('+1 day');
        }

        $alerts['counts']['critical'] = count($alerts['critical']);
        $alerts['counts']['warning'] = count($alerts['warning']);
        $alerts['counts']['info'] = count($alerts['info']);
        $alerts['counts']['total'] = $alerts['counts']['critical'] + $alerts['counts']['warning'] + $alerts['counts']['info'];

        return $alerts;
    }
}
