<?php
require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();

header('Content-Type: application/json; charset=utf-8');

require_once __DIR__ . '/forecast_alerts.php';

if (!function_exists('alerts_count_table_exists')) {
    function alerts_count_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('alerts_count_column_exists')) {
    function alerts_count_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;
    }
}

$response = [
    'ok' => false,
    'date' => date('Y-m-d'),
    'critical' => 0,
    'warning' => 0,
    'info' => 0,
    'total' => 0,
    'items' => [
        'unpaid_departures' => 0,
        'overbooking_risk' => 0,
        'forecast_critical' => 0,
        'forecast_warning' => 0,
        'unassigned_rooms' => 0,
        'in_house_balances' => 0,
        'arrivals_today' => 0,
        'long_stay_no_payment' => 0,
        'agent_outstanding' => 0,
    ],
    'error' => null,
];

try {
    $today = date('Y-m-d');
    $next7 = date('Y-m-d', strtotime('+7 days'));
    $selectedPropertyId = (int)($_GET['property_id'] ?? 0);

    foreach (['orders', 'order_rooming', 'rooms', 'room_types', 'properties', 'folios'] as $table) {
        if (!alerts_count_table_exists($pdo, $table)) {
            throw new RuntimeException('Required table missing: ' . $table);
        }
    }

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

    $todaySql = $pdo->quote($today);
    $next7Sql = $pdo->quote($next7);

    $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 ($selectedPropertyId > 0) {
        $propertyFilterOrders = " AND o.property_id = :property_id ";
        $orderParams[':property_id'] = $selectedPropertyId;
    }

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

    if ($selectedPropertyId > 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'] = $selectedPropertyId;
    }

    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        FROM (
            SELECT o.id, COALESCE(SUM(f.balance), 0) AS balance
            FROM orders o
            LEFT JOIN folios f ON f.order_id = o.id
            WHERE DATE(o.check_out) = $todaySql
              $propertyFilterOrders
              $orderActiveSql
            GROUP BY o.id
            HAVING balance > 0.00001
        ) x
    ");
    $stmt->execute($orderParams);
    $response['items']['unpaid_departures'] = (int)$stmt->fetchColumn();

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

    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        FROM (
            SELECT o.id, COALESCE(SUM(f.balance), 0) AS balance
            FROM orders o
            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
            HAVING balance > 0.00001
        ) x
    ");
    $stmt->execute($orderParams);
    $response['items']['in_house_balances'] = (int)$stmt->fetchColumn();

    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        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 orm.check_in <= $next7Sql
          AND orm.check_out > $todaySql
          AND (orm.room_id IS NULL OR orm.room_id = 0)
          $roomingActiveSql
          $propertyFilterRooming
          $orderActiveSql
    ");
    $stmt->execute($roomingParams);
    $response['items']['unassigned_rooms'] = (int)$stmt->fetchColumn();

    /* Existing same-day overbooking risk */
    $roomFilter = '';
    $roomParams = [];

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

    $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, 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, property_id
        HAVING rooms_count > 0
    ");
    $stmtTypes->execute($roomParams);
    $roomTypeCapacity = $stmtTypes->fetchAll(PDO::FETCH_ASSOC);

    $overbookingCount = 0;

    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) {
            $overbookingCount++;
        }
    }

    $response['items']['overbooking_risk'] = $overbookingCount;

    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        FROM (
            SELECT COALESCE(a.id, 0) AS agent_id, 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
            HAVING balance > 0.00001
        ) x
    ");
    $stmt->execute($orderParams);
    $response['items']['agent_outstanding'] = (int)$stmt->fetchColumn();

    if ($hasPaymentTable) {
        $stmt = $pdo->prepare("
            SELECT COUNT(DISTINCT o.id)
            FROM orders o
            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
              )
        ");
        $stmt->execute($orderParams);
        $response['items']['long_stay_no_payment'] = (int)$stmt->fetchColumn();
    }

    $forecastAlerts = get_forecast_alerts($pdo, $selectedPropertyId, 14);
    $response['items']['forecast_critical'] = (int)$forecastAlerts['counts']['critical'];
    $response['items']['forecast_warning'] = (int)$forecastAlerts['counts']['warning'];

    $response['critical'] =
        $response['items']['unpaid_departures'] +
        $response['items']['overbooking_risk'] +
        $response['items']['forecast_critical'];

    $response['warning'] =
        $response['items']['unassigned_rooms'] +
        $response['items']['in_house_balances'] +
        $response['items']['long_stay_no_payment'] +
        $response['items']['agent_outstanding'] +
        $response['items']['forecast_warning'];

    $response['info'] =
        $response['items']['arrivals_today'];

    $response['total'] =
        $response['critical'] +
        $response['warning'] +
        $response['info'];

    $response['ok'] = true;

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

echo json_encode($response);
