<?php
/**
 * send_alerts.php
 * Place: /rooms/modules/reports/send_alerts.php
 *
 * Sends daily PMS alerts by:
 * - Email
 * - WhatsApp
 *
 * Required files in same folder:
 * - forecast_alerts.php
 * - whatsapp_alerts.php
 *
 * Browser test:
 * /rooms/modules/reports/send_alerts.php?test=1
 *
 * Force resend:
 * /rooms/modules/reports/send_alerts.php?force=1
 *
 * WhatsApp only:
 * /rooms/modules/reports/send_alerts.php?whatsapp_only=1&force=1
 *
 * Email only:
 * /rooms/modules/reports/send_alerts.php?email_only=1&force=1
 *
 * Cron:
 * 0 6 * * * /usr/local/bin/php /home/macsoftl/public_html/rooms/modules/reports/send_alerts.php >/dev/null 2>&1
 */

require_once __DIR__ . '/../../includes/bootstrap.php';
require_once __DIR__ . '/forecast_alerts.php';
require_once __DIR__ . '/whatsapp_alerts.php';

if (!function_exists('sa_table_exists')) {
    function sa_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('sa_column_exists')) {
    function sa_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('sa_money')) {
    function sa_money($amount): string
    {
        return number_format((float)$amount, 2);
    }
}

if (!function_exists('sa_out')) {
    function sa_out(array $payload): void
    {
        if (PHP_SAPI === 'cli') {
            echo json_encode($payload, JSON_PRETTY_PRINT) . PHP_EOL;
            return;
        }

        header('Content-Type: application/json; charset=utf-8');
        echo json_encode($payload, JSON_PRETTY_PRINT);
    }
}

if (!function_exists('sa_create_log_table')) {
    function sa_create_log_table(PDO $pdo): void
    {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS alert_send_log (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                alert_date DATE NOT NULL,
                property_id INT UNSIGNED NOT NULL DEFAULT 0,
                channel VARCHAR(30) NOT NULL DEFAULT 'email',
                recipient VARCHAR(255) NOT NULL,
                subject VARCHAR(255) NOT NULL,
                sent_at DATETIME NOT NULL,
                status VARCHAR(30) NOT NULL DEFAULT 'sent',
                notes TEXT NULL,
                UNIQUE KEY uq_alert_day_channel_recipient (alert_date, property_id, channel, recipient)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    }
}

if (!function_exists('sa_get_setting')) {
    function sa_get_setting(PDO $pdo, string $key, $default = null)
    {
        if (!sa_table_exists($pdo, 'settings')) {
            return $default;
        }

        $possibleKeyColumns = ['setting_key', 'name', 'setting_name', '`key`'];
        $possibleValueColumns = ['setting_value', 'value', 'setting'];

        foreach ($possibleKeyColumns as $keyCol) {
            $cleanKeyCol = trim($keyCol, '`');
            if (!sa_column_exists($pdo, 'settings', $cleanKeyCol)) {
                continue;
            }

            foreach ($possibleValueColumns as $valueCol) {
                if (!sa_column_exists($pdo, 'settings', $valueCol)) {
                    continue;
                }

                try {
                    $stmt = $pdo->prepare("SELECT $valueCol FROM settings WHERE $keyCol = :setting_key LIMIT 1");
                    $stmt->execute([':setting_key' => $key]);
                    $value = $stmt->fetchColumn();

                    if ($value !== false && trim((string)$value) !== '') {
                        return $value;
                    }
                } catch (Throwable $e) {
                    continue;
                }
            }
        }

        return $default;
    }
}

if (!function_exists('sa_get_recipients')) {
    function sa_get_recipients(PDO $pdo): array
    {
        $configured = sa_get_setting($pdo, 'alert_email_recipients', '');

        $recipients = [];
        if (trim((string)$configured) !== '') {
            $recipients = preg_split('/[,;]+/', (string)$configured);
        }

        if (!$recipients) {
            $recipients = [
                // EDIT THIS if you do not use settings table:
                'manager@example.com',
            ];
        }

        $clean = [];
        foreach ($recipients as $email) {
            $email = trim((string)$email);
            if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $clean[] = $email;
            }
        }

        return array_values(array_unique($clean));
    }
}

if (!function_exists('sa_get_from_email')) {
    function sa_get_from_email(PDO $pdo): string
    {
        $from = sa_get_setting($pdo, 'alert_from_email', '');
        if ($from && filter_var($from, FILTER_VALIDATE_EMAIL)) {
            return (string)$from;
        }

        $host = $_SERVER['HTTP_HOST'] ?? 'macsoftlabs.com';
        $host = preg_replace('/[^a-zA-Z0-9\.\-]/', '', $host);
        if ($host === '') {
            $host = 'macsoftlabs.com';
        }

        return 'no-reply@' . $host;
    }
}

if (!function_exists('sa_url')) {
    function sa_url(string $path): string
    {
        $base = defined('BASE_URL') ? BASE_URL : '/rooms';
        $host = $_SERVER['HTTP_HOST'] ?? '';

        if ($host !== '') {
            $scheme = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? 'https' : 'http';
            return $scheme . '://' . $host . rtrim($base, '/') . '/' . ltrim($path, '/');
        }

        return rtrim($base, '/') . '/' . ltrim($path, '/');
    }
}

if (!function_exists('sa_load_operational_alerts')) {
    function sa_load_operational_alerts(PDO $pdo, int $propertyId = 0): array
    {
        $today = date('Y-m-d');
        $todaySql = $pdo->quote($today);
        $next7Sql = $pdo->quote(date('Y-m-d', strtotime('+7 days')));

        $alerts = [
            'unpaid_departures' => [],
            'in_house_balances' => [],
            'unassigned_rooms' => [],
            'overbooking_risks' => [],
            'long_stay_no_payment' => [],
            'agent_outstanding' => [],
            'arrivals_today' => [],
        ];

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

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

        $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 ";
        }

        $propertyFilterOrders = '';
        $orderParams = [];

        if ($propertyId > 0) {
            $propertyFilterOrders = " AND o.property_id = :property_id ";
            $orderParams[':property_id'] = $propertyId;
        }

        $propertyFilterRooming = '';
        $roomingParams = [];

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

        $stmt = $pdo->prepare("
            SELECT
                o.id,
                o.order_no,
                o.check_in,
                o.check_out,
                p.name AS property_name,
                TRIM(CONCAT(COALESCE(g.firstname,''), ' ', COALESCE(g.lastname,''))) AS guest_name,
                TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))) AS agent_name,
                COALESCE(SUM(f.balance), 0) AS balance
            FROM orders o
            LEFT JOIN guests g ON g.id = o.guest_id
            LEFT JOIN agents a ON a.id = o.agent_id
            LEFT JOIN properties p ON p.id = o.property_id
            LEFT JOIN folios f ON f.order_id = o.id
            WHERE DATE(o.check_out) = $todaySql
              $propertyFilterOrders
              $orderActiveSql
            GROUP BY o.id, o.order_no, o.check_in, o.check_out, property_name, guest_name, agent_name
            HAVING balance > 0.00001
            ORDER BY balance DESC
            LIMIT 50
        ");
        $stmt->execute($orderParams);
        $alerts['unpaid_departures'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare("
            SELECT
                o.id,
                o.order_no,
                o.check_in,
                o.check_out,
                p.name AS property_name,
                TRIM(CONCAT(COALESCE(g.firstname,''), ' ', COALESCE(g.lastname,''))) AS guest_name,
                TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))) AS agent_name,
                COALESCE(SUM(f.balance), 0) AS balance
            FROM orders o
            LEFT JOIN guests g ON g.id = o.guest_id
            LEFT JOIN agents a ON a.id = o.agent_id
            LEFT JOIN properties p ON p.id = o.property_id
            LEFT JOIN folios f ON f.order_id = o.id
            WHERE o.check_in <= $todaySql
              AND o.check_out > $todaySql
              $propertyFilterOrders
              $orderActiveSql
            GROUP BY o.id, o.order_no, o.check_in, o.check_out, property_name, guest_name, agent_name
            HAVING balance > 0.00001
            ORDER BY balance DESC
            LIMIT 50
        ");
        $stmt->execute($orderParams);
        $alerts['in_house_balances'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $propertyJoinExpr = $hasRoomingProperty
            ? "COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id)"
            : "COALESCE(o.property_id, r.property_id, rt.property_id)";

        $stmt = $pdo->prepare("
            SELECT
                orm.id AS rooming_id,
                orm.order_id,
                orm.check_in,
                orm.check_out,
                rt.title AS room_type,
                p.name AS property_name,
                o.order_no,
                TRIM(CONCAT(COALESCE(g.firstname,''), ' ', COALESCE(g.lastname,''))) AS guest_name,
                TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))) AS agent_name
            FROM order_rooming orm
            LEFT JOIN orders o ON o.id = orm.order_id
            LEFT JOIN guests g ON g.id = o.guest_id
            LEFT JOIN agents a ON a.id = o.agent_id
            LEFT JOIN room_types rt ON rt.id = orm.room_type_id
            LEFT JOIN rooms r ON r.id = orm.room_id
            LEFT JOIN properties p ON p.id = $propertyJoinExpr
            WHERE orm.check_in <= $next7Sql
              AND orm.check_out > $todaySql
              AND (orm.room_id IS NULL OR orm.room_id = 0)
              $roomingActiveSql
              $propertyFilterRooming
              $orderActiveSql
            ORDER BY orm.check_in ASC
            LIMIT 50
        ");
        $stmt->execute($roomingParams);
        $alerts['unassigned_rooms'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $pdo->prepare("
            SELECT COUNT(DISTINCT o.id) AS arrivals
            FROM orders o
            WHERE DATE(o.check_in) = $todaySql
              $propertyFilterOrders
              $orderActiveSql
        ");
        $stmt->execute($orderParams);
        $alerts['arrivals_today'] = [['arrivals' => (int)$stmt->fetchColumn()]];

        if (sa_table_exists($pdo, 'payment')) {
            $stmt = $pdo->prepare("
                SELECT
                    o.id,
                    o.order_no,
                    o.check_in,
                    o.check_out,
                    DATEDIFF($todaySql, o.check_in) AS days_in_house,
                    p.name AS property_name,
                    TRIM(CONCAT(COALESCE(g.firstname,''), ' ', COALESCE(g.lastname,''))) AS guest_name,
                    TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))) AS agent_name
                FROM orders o
                LEFT JOIN guests g ON g.id = o.guest_id
                LEFT JOIN agents a ON a.id = o.agent_id
                LEFT JOIN properties p ON p.id = o.property_id
                WHERE o.check_in <= $todaySql
                  AND o.check_out > $todaySql
                  AND DATEDIFF($todaySql, o.check_in) >= 3
                  $propertyFilterOrders
                  $orderActiveSql
                  AND NOT EXISTS (
                      SELECT 1
                      FROM payment pmt
                      WHERE pmt.order_id = o.id
                        AND COALESCE(pmt.amount,0) > 0
                  )
                ORDER BY days_in_house DESC
                LIMIT 50
            ");
            $stmt->execute($orderParams);
            $alerts['long_stay_no_payment'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        $stmt = $pdo->prepare("
            SELECT
                COALESCE(a.id, 0) AS agent_id,
                COALESCE(NULLIF(TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))), ''), 'Direct / No Agent') AS agent_name,
                COUNT(DISTINCT o.id) AS booking_count,
                COALESCE(SUM(f.balance), 0) AS balance
            FROM folios f
            INNER JOIN orders o ON o.id = f.order_id
            LEFT JOIN agents a ON a.id = o.agent_id
            WHERE f.balance > 0.00001
              $propertyFilterOrders
            GROUP BY agent_id, agent_name
            HAVING balance > 0.00001
            ORDER BY balance DESC
            LIMIT 10
        ");
        $stmt->execute($orderParams);
        $alerts['agent_outstanding'] = $stmt->fetchAll(PDO::FETCH_ASSOC);

        /* same-day room-type overbooking risk */
        $roomFilter = '';
        $roomParams = [];
        if ($propertyId > 0) {
            $roomFilter = " AND COALESCE(r.property_id, rt.property_id) = :property_id ";
            $roomParams[':property_id'] = $propertyId;
        }

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

        $stmtTypes = $pdo->prepare("
            SELECT
                rt.id AS room_type_id,
                rt.title AS room_type,
                COALESCE(p.name, 'No Property') AS property_name,
                COALESCE(p.id, 0) AS property_id,
                COUNT(r.id) AS rooms_count
            FROM room_types rt
            LEFT JOIN rooms r ON r.room_type_id = rt.id
            LEFT JOIN properties p ON p.id = COALESCE(r.property_id, rt.property_id)
            WHERE 1=1
              $roomFilter
              $activeRoomSql
            GROUP BY rt.id, rt.title, property_name, property_id
            HAVING rooms_count > 0
        ");
        $stmtTypes->execute($roomParams);
        $roomTypeCapacity = $stmtTypes->fetchAll(PDO::FETCH_ASSOC);

        foreach ($roomTypeCapacity as $type) {
            $params = [':room_type_id' => (int)$type['room_type_id']];

            $where = "
                orm.room_type_id = :room_type_id
                AND orm.check_in <= $todaySql
                AND orm.check_out > $todaySql
                $roomingActiveSql
                $orderActiveSql
            ";

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

            $stmtOcc = $pdo->prepare("
                SELECT COUNT(DISTINCT orm.id)
                FROM order_rooming orm
                LEFT JOIN orders o ON o.id = orm.order_id
                WHERE $where
            ");
            $stmtOcc->execute($params);
            $occupied = (int)$stmtOcc->fetchColumn();
            $capacity = (int)$type['rooms_count'];

            if ($occupied > $capacity) {
                $alerts['overbooking_risks'][] = [
                    'property_name' => $type['property_name'],
                    'room_type' => $type['room_type'],
                    'capacity' => $capacity,
                    'occupied' => $occupied,
                    'over_by' => $occupied - $capacity,
                ];
            }
        }

        return $alerts;
    }
}

if (!function_exists('sa_count_alerts')) {
    function sa_count_alerts(array $operational, array $forecast): array
    {
        $critical =
            count($operational['unpaid_departures'] ?? []) +
            count($operational['overbooking_risks'] ?? []) +
            count($forecast['critical'] ?? []);

        $warning =
            count($operational['in_house_balances'] ?? []) +
            count($operational['unassigned_rooms'] ?? []) +
            count($operational['long_stay_no_payment'] ?? []) +
            count($operational['agent_outstanding'] ?? []) +
            count($forecast['warning'] ?? []);

        $info = (int)($operational['arrivals_today'][0]['arrivals'] ?? 0);

        return [
            'critical' => $critical,
            'warning' => $warning,
            'info' => $info,
            'total' => $critical + $warning + $info,
        ];
    }
}

if (!function_exists('sa_build_plain_text')) {
    function sa_build_plain_text(array $operational, array $forecastPack, array $counts, string $propertyLabel = 'All Properties'): string
    {
        $lines = [];
        $lines[] = 'PMS Daily Alerts Summary';
        $lines[] = date('Y-m-d') . ' - ' . $propertyLabel;
        $lines[] = '';
        $lines[] = 'Critical: ' . (int)$counts['critical'];
        $lines[] = 'Warnings: ' . (int)$counts['warning'];
        $lines[] = 'Information: ' . (int)$counts['info'];
        $lines[] = '';

        $forecastAll = array_merge($forecastPack['critical'] ?? [], $forecastPack['warning'] ?? []);
        $lines[] = 'FORECAST ALERTS';
        if (!$forecastAll) {
            $lines[] = '- No forecast alerts.';
        } else {
            foreach ($forecastAll as $a) {
                $lines[] = '- ' . ($a['date'] ?? '') . ' ' . ($a['title'] ?? 'Forecast Alert') . ': ' . ($a['message'] ?? '');
            }
        }

        $lines[] = '';
        $lines[] = 'OPERATIONAL ALERTS';
        $lines[] = '- Unpaid departures: ' . count($operational['unpaid_departures'] ?? []);
        $lines[] = '- Unassigned rooms: ' . count($operational['unassigned_rooms'] ?? []);
        $lines[] = '- In-house balances: ' . count($operational['in_house_balances'] ?? []);
        $lines[] = '- Long stay without payment: ' . count($operational['long_stay_no_payment'] ?? []);
        $lines[] = '- Agent outstanding groups: ' . count($operational['agent_outstanding'] ?? []);

        return implode("\n", $lines);
    }
}

if (!function_exists('sa_build_email_html')) {
    function sa_build_email_html(array $operational, array $forecastPack, array $counts, string $propertyLabel = 'All Properties'): string
    {
        $date = date('Y-m-d');
        $alertsUrl = sa_url('/modules/reports/alerts.php');
        $forecastUrl = sa_url('/modules/reports/forecast_report.php');

        ob_start();
        ?>
        <!doctype html>
        <html>
        <head><meta charset="utf-8"><title>PMS Alerts</title></head>
        <body style="margin:0;padding:0;background:#f3f4f6;font-family:Arial,Helvetica,sans-serif;color:#111827;">
        <div style="max-width:920px;margin:0 auto;padding:24px;">
            <div style="background:#111827;color:#fff;border-radius:18px;padding:22px 24px;margin-bottom:18px;">
                <div style="font-size:13px;text-transform:uppercase;letter-spacing:.08em;color:#cbd5e1;">PMS Operations Intelligence</div>
                <h1 style="margin:8px 0 4px;font-size:28px;">Daily Alerts Summary</h1>
                <div style="color:#cbd5e1;"><?php echo htmlspecialchars($date); ?> • <?php echo htmlspecialchars($propertyLabel); ?></div>
            </div>

            <div style="display:flex;gap:12px;flex-wrap:wrap;margin-bottom:18px;">
                <div style="flex:1;min-width:160px;background:#fff;border-radius:16px;padding:16px;border-left:5px solid #dc2626;">
                    <div style="font-size:13px;color:#6b7280;font-weight:bold;">Critical</div>
                    <div style="font-size:28px;font-weight:bold;color:#dc2626;"><?php echo (int)$counts['critical']; ?></div>
                </div>
                <div style="flex:1;min-width:160px;background:#fff;border-radius:16px;padding:16px;border-left:5px solid #d97706;">
                    <div style="font-size:13px;color:#6b7280;font-weight:bold;">Warnings</div>
                    <div style="font-size:28px;font-weight:bold;color:#d97706;"><?php echo (int)$counts['warning']; ?></div>
                </div>
                <div style="flex:1;min-width:160px;background:#fff;border-radius:16px;padding:16px;border-left:5px solid #0284c7;">
                    <div style="font-size:13px;color:#6b7280;font-weight:bold;">Information</div>
                    <div style="font-size:28px;font-weight:bold;color:#0284c7;"><?php echo (int)$counts['info']; ?></div>
                </div>
            </div>

            <?php if ($counts['critical'] > 0): ?>
                <div style="background:#fee2e2;border:1px solid #fecaca;color:#991b1b;border-radius:14px;padding:14px 16px;margin-bottom:18px;font-weight:bold;">
                    Critical action required today. Review unpaid departures, overbooking and forecast risk immediately.
                </div>
            <?php endif; ?>

            <div style="background:#fff;border-radius:18px;padding:18px;margin-bottom:18px;">
                <h2 style="margin:0 0 12px;font-size:20px;">Forecast Alerts - Next 14 Days</h2>
                <?php $forecastAll = array_merge($forecastPack['critical'] ?? [], $forecastPack['warning'] ?? []); ?>
                <?php if (!$forecastAll): ?>
                    <p style="color:#6b7280;margin:0;">No forecast alerts.</p>
                <?php else: ?>
                    <table width="100%" cellpadding="8" cellspacing="0" style="border-collapse:collapse;">
                        <thead>
                        <tr style="background:#f9fafb;">
                            <th align="left">Date</th>
                            <th align="left">Alert</th>
                            <th align="right">Booked</th>
                            <th align="right">Left</th>
                            <th align="right">Occ %</th>
                        </tr>
                        </thead>
                        <tbody>
                        <?php foreach ($forecastAll as $a): ?>
                            <tr style="border-top:1px solid #e5e7eb;">
                                <td><?php echo htmlspecialchars(($a['date'] ?? '') . ' ' . ($a['day_name'] ?? '')); ?></td>
                                <td>
                                    <strong style="color:<?php echo ($a['severity'] ?? '') === 'critical' ? '#dc2626' : '#d97706'; ?>">
                                        <?php echo htmlspecialchars($a['title'] ?? 'Forecast Alert'); ?>
                                    </strong><br>
                                    <span style="color:#6b7280;"><?php echo htmlspecialchars($a['message'] ?? ''); ?></span>
                                </td>
                                <td align="right"><?php echo (int)($a['booked_rooms'] ?? 0); ?> / <?php echo (int)($a['available_rooms'] ?? 0); ?></td>
                                <td align="right"><strong><?php echo (int)($a['rooms_left'] ?? 0); ?></strong></td>
                                <td align="right"><?php echo number_format((float)($a['occupancy_percent'] ?? 0), 2); ?>%</td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                    </table>
                <?php endif; ?>
            </div>

            <div style="background:#fff;border-radius:18px;padding:18px;margin-bottom:18px;">
                <h2 style="margin:0 0 12px;font-size:20px;">Operational Alerts Today</h2>
                <p><strong>Unpaid Departures:</strong> <?php echo count($operational['unpaid_departures'] ?? []); ?></p>
                <p><strong>Unassigned Rooms:</strong> <?php echo count($operational['unassigned_rooms'] ?? []); ?></p>
                <p><strong>In-house Balances:</strong> <?php echo count($operational['in_house_balances'] ?? []); ?></p>
                <p><strong>Long Stay Without Payment:</strong> <?php echo count($operational['long_stay_no_payment'] ?? []); ?></p>
                <p><strong>Agent Outstanding Groups:</strong> <?php echo count($operational['agent_outstanding'] ?? []); ?></p>
            </div>

            <div style="text-align:center;margin-top:20px;">
                <a href="<?php echo htmlspecialchars($alertsUrl); ?>" style="display:inline-block;background:#dc2626;color:#fff;text-decoration:none;border-radius:999px;padding:12px 18px;font-weight:bold;margin:4px;">View Alerts</a>
                <a href="<?php echo htmlspecialchars($forecastUrl); ?>" style="display:inline-block;background:#111827;color:#fff;text-decoration:none;border-radius:999px;padding:12px 18px;font-weight:bold;margin:4px;">View Forecast</a>
            </div>
        </div>
        </body>
        </html>
        <?php
        return (string)ob_get_clean();
    }
}

if (!function_exists('sa_send_mail')) {
    function sa_send_mail(string $to, string $subject, string $html, string $plainText, string $from): bool
    {
        $boundary = md5((string)time());

        $headers = [];
        $headers[] = 'From: PMS Alerts <' . $from . '>';
        $headers[] = 'Reply-To: ' . $from;
        $headers[] = 'MIME-Version: 1.0';
        $headers[] = 'Content-Type: multipart/alternative; boundary="' . $boundary . '"';

        $body = "--$boundary\r\n";
        $body .= "Content-Type: text/plain; charset=UTF-8\r\n";
        $body .= "Content-Transfer-Encoding: 8bit\r\n\r\n";
        $body .= $plainText . "\r\n\r\n";

        $body .= "--$boundary\r\n";
        $body .= "Content-Type: text/html; charset=UTF-8\r\n";
        $body .= "Content-Transfer-Encoding: 8bit\r\n\r\n";
        $body .= $html . "\r\n\r\n";
        $body .= "--$boundary--";

        return mail($to, $subject, $body, implode("\r\n", $headers));
    }
}

if (!function_exists('sa_log_send')) {
    function sa_log_send(PDO $pdo, string $channel, string $recipient, string $subject, bool $sent, int $propertyId, string $notes = ''): void
    {
        $stmt = $pdo->prepare("
            INSERT INTO alert_send_log
            (alert_date, property_id, channel, recipient, subject, sent_at, status, notes)
            VALUES
            (:alert_date, :property_id, :channel, :recipient, :subject, NOW(), :status, :notes)
            ON DUPLICATE KEY UPDATE
                sent_at = VALUES(sent_at),
                subject = VALUES(subject),
                status = VALUES(status),
                notes = VALUES(notes)
        ");
        $stmt->execute([
            ':alert_date' => date('Y-m-d'),
            ':property_id' => $propertyId,
            ':channel' => $channel,
            ':recipient' => $recipient,
            ':subject' => $subject,
            ':status' => $sent ? 'sent' : 'failed',
            ':notes' => $notes,
        ]);
    }
}

if (!function_exists('sa_already_sent')) {
    function sa_already_sent(PDO $pdo, string $channel, string $recipient, int $propertyId): bool
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM alert_send_log
            WHERE alert_date = :alert_date
              AND property_id = :property_id
              AND channel = :channel
              AND recipient = :recipient
              AND status = 'sent'
        ");
        $stmt->execute([
            ':alert_date' => date('Y-m-d'),
            ':property_id' => $propertyId,
            ':channel' => $channel,
            ':recipient' => $recipient,
        ]);

        return (int)$stmt->fetchColumn() > 0;
    }
}

$payload = [
    'ok' => false,
    'mode' => PHP_SAPI === 'cli' ? 'cli' : 'web',
    'email_sent' => [],
    'whatsapp_sent' => [],
    'skipped' => [],
    'errors' => [],
];

try {
    $isCli = PHP_SAPI === 'cli';

    $testMode = (!$isCli && isset($_GET['test'])) || ($isCli && in_array('--test', $argv ?? [], true));
    $force = (!$isCli && isset($_GET['force'])) || ($isCli && in_array('--force', $argv ?? [], true));
    $emailOnly = (!$isCli && isset($_GET['email_only'])) || ($isCli && in_array('--email-only', $argv ?? [], true));
    $whatsappOnly = (!$isCli && isset($_GET['whatsapp_only'])) || ($isCli && in_array('--whatsapp-only', $argv ?? [], true));

    $sendEmail = !$whatsappOnly;
    $sendWhatsApp = !$emailOnly;

    $propertyId = 0;

    if ($isCli) {
        foreach (($argv ?? []) as $arg) {
            if (preg_match('/^--property_id=(\d+)$/', $arg, $m)) {
                $propertyId = (int)$m[1];
            }
        }
    } else {
        $propertyId = (int)($_GET['property_id'] ?? 0);
    }

    sa_create_log_table($pdo);

    $propertyLabel = 'All Properties';
    if ($propertyId > 0 && sa_table_exists($pdo, 'properties')) {
        $stmt = $pdo->prepare("SELECT CONCAT(name, ' - ', code) FROM properties WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $propertyId]);
        $label = $stmt->fetchColumn();
        if ($label !== false && trim((string)$label) !== '') {
            $propertyLabel = (string)$label;
        }
    }

    $operational = sa_load_operational_alerts($pdo, $propertyId);
    $forecastPack = function_exists('get_forecast_alerts') ? get_forecast_alerts($pdo, $propertyId, 14) : ['critical' => [], 'warning' => [], 'info' => []];
    $counts = sa_count_alerts($operational, $forecastPack);

    $payload['counts'] = $counts;
    $payload['property_id'] = $propertyId;
    $payload['property_label'] = $propertyLabel;

    $subjectPrefix = $counts['critical'] > 0 ? 'CRITICAL PMS Alerts' : 'PMS Daily Alerts';
    $subject = $subjectPrefix . ' - ' . date('Y-m-d') . ' - ' . $propertyLabel;

    $html = sa_build_email_html($operational, $forecastPack, $counts, $propertyLabel);
    $plainText = sa_build_plain_text($operational, $forecastPack, $counts, $propertyLabel);
    $from = sa_get_from_email($pdo);

    $waMessage = "⚠️ {$subject}\n\n" . $plainText;

    if ($testMode) {
        $payload['ok'] = true;
        $payload['test_mode'] = true;
        $payload['send_email'] = $sendEmail;
        $payload['send_whatsapp'] = $sendWhatsApp;
        $payload['email_recipients'] = sa_get_recipients($pdo);
        $payload['whatsapp_numbers'] = function_exists('wa_alert_numbers') ? wa_alert_numbers($pdo) : [];
        $payload['subject'] = $subject;
        $payload['plain_text_preview'] = $plainText;
        $payload['whatsapp_preview'] = $waMessage;
        sa_out($payload);
        exit;
    }

    if ($sendEmail) {
        $recipients = sa_get_recipients($pdo);

        if (!$recipients) {
            $payload['errors'][] = 'No valid email recipients configured.';
        }

        foreach ($recipients as $recipient) {
            if (!$force && sa_already_sent($pdo, 'email', $recipient, $propertyId)) {
                $payload['skipped'][] = [
                    'channel' => 'email',
                    'recipient' => $recipient,
                    'reason' => 'Already sent today. Use force=1 or --force to resend.',
                ];
                continue;
            }

            $sent = sa_send_mail($recipient, $subject, $html, $plainText, $from);
            sa_log_send($pdo, 'email', $recipient, $subject, $sent, $propertyId, $sent ? 'Mail accepted by server.' : 'mail() returned false.');

            if ($sent) {
                $payload['email_sent'][] = $recipient;
            } else {
                $payload['errors'][] = 'Failed to send email to ' . $recipient;
            }
        }
    }

    if ($sendWhatsApp) {
        if (!function_exists('wa_alert_numbers') || !function_exists('wa_send_message')) {
            $payload['errors'][] = 'WhatsApp helper functions not available.';
        } else {
            $numbers = wa_alert_numbers($pdo);

            if (!$numbers) {
                $payload['errors'][] = 'No WhatsApp alert numbers configured. Add whatsapp_alert_numbers to settings.';
            }

            foreach ($numbers as $number) {
                if (!$force && sa_already_sent($pdo, 'whatsapp', $number, $propertyId)) {
                    $payload['skipped'][] = [
                        'channel' => 'whatsapp',
                        'recipient' => $number,
                        'reason' => 'Already sent today. Use force=1 or --force to resend.',
                    ];
                    continue;
                }

                $result = wa_send_message($pdo, $number, $waMessage, [
                    'message_type' => 'daily_alert',
                    'related_table' => 'alert_send_log',
                    'related_id' => 0,
                ]);

                $sent = !empty($result['ok']);
                sa_log_send(
                    $pdo,
                    'whatsapp',
                    $number,
                    $subject,
                    $sent,
                    $propertyId,
                    json_encode($result)
                );

                if ($sent) {
                    $payload['whatsapp_sent'][] = $number;
                } else {
                    $payload['errors'][] = 'Failed to send WhatsApp to ' . $number . ': ' . ($result['error'] ?? 'Unknown error');
                }
            }
        }
    }

    $payload['ok'] = empty($payload['errors']);

} catch (Throwable $e) {
    http_response_code(500);
    $payload['errors'][] = $e->getMessage();
}

sa_out($payload);
