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

$pageTitle = 'Occupancy Forecast';

if (!function_exists('h')) {
    function h($value): string
    {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('money_fmt')) {
    function money_fmt($amount): string
    {
        return number_format((float)$amount, 2);
    }
}

if (!function_exists('pct_fmt')) {
    function pct_fmt($value): string
    {
        return number_format((float)$value, 1) . '%';
    }
}

if (!function_exists('of_column_exists')) {
    function of_column_exists(PDO $pdo, string $table, string $column): bool
    {
        static $cache = [];
        $key = $table . '.' . $column;

        if (array_key_exists($key, $cache)) {
            return $cache[$key];
        }

        try {
            $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' => $table,
                ':column_name' => $column,
            ]);
            $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            $cache[$key] = false;
        }

        return $cache[$key];
    }
}

$today = date('Y-m-d');
$dateFrom = trim((string)($_GET['date_from'] ?? $today));
$dateTo = trim((string)($_GET['date_to'] ?? date('Y-m-d', strtotime($today . ' +13 days'))));
$propertyId = (int)($_GET['property_id'] ?? 0);

$errors = [];
$properties = [];
$totalRooms = 0;
$days = [];
$dailyData = [];

if (strtotime($dateFrom) === false || strtotime($dateTo) === false) {
    $errors[] = 'Invalid date range.';
}
if (!$errors && $dateFrom > $dateTo) {
    $errors[] = 'Date from cannot be after date to.';
}

/* Prevent extremely large ranges */
if (!$errors) {
    $d1 = new DateTime($dateFrom);
    $d2 = new DateTime($dateTo);
    $diffDays = (int)$d1->diff($d2)->days;
    if ($diffDays > 92) {
        $errors[] = 'Please choose a range of 93 days or less.';
    }
}

try {
    $stmt = $pdo->query("
        SELECT id, name, code
        FROM properties
        WHERE is_active = 1
        ORDER BY name ASC
    ");
    $properties = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Throwable $e) {
    $properties = [];
}

/* ---------------------------------
   Total room stock
--------------------------------- */
if (!$errors) {
    try {
        $sql = "SELECT COUNT(*) FROM rooms WHERE 1=1";
        $params = [];

        // Only sellable physical rooms belong in the occupancy denominator.
        // Maintenance, blocked, inactive and out-of-order rooms must not make
        // the forecast appear to have more available stock than operations do.
        if (of_column_exists($pdo, 'rooms', 'status')) {
            $sql .= " AND LOWER(TRIM(COALESCE(status,''))) NOT IN
                      ('out_of_order','out of order','maintenance','inactive','blocked')";
        }
        if (of_column_exists($pdo, 'rooms', 'is_active')) {
            $sql .= " AND COALESCE(is_active,1) = 1";
        }

        if ($propertyId > 0) {
            $sql .= " AND property_id = ?";
            $params[] = $propertyId;
        }

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $totalRooms = (int)$stmt->fetchColumn();
    } catch (Throwable $e) {
        $errors[] = 'Room Stock: ' . $e->getMessage();
    }
}

/* ---------------------------------
   Build date series
--------------------------------- */
if (!$errors) {
    $start = new DateTime($dateFrom);
    $end = new DateTime($dateTo);
    $endInclusive = clone $end;
    $endInclusive->modify('+1 day');

    $period = new DatePeriod($start, new DateInterval('P1D'), $endInclusive);
    foreach ($period as $dt) {
        $days[] = $dt->format('Y-m-d');
    }
}

/* ---------------------------------
   Load occupancy / arrivals / departures
--------------------------------- */
if (!$errors && $days) {
    try {
        $propertyOrderFilter = $propertyId > 0 ? " AND o.property_id = ? " : "";
        $propertyRoomFilter  = $propertyId > 0 ? " AND r.property_id = ? " : "";

        $activeOrderClause = "
            AND LOWER(TRIM(CAST(COALESCE(o.status,1) AS CHAR))) NOT IN
                ('0','5','cancelled','canceled','void','deleted','no_show','noshow')
        ";

        $activeRoomingClause = "";
        if (of_column_exists($pdo, 'order_rooming', 'released_status')) {
            $activeRoomingClause .= " AND COALESCE(rmg.released_status,0) = 0 ";
        }
        if (of_column_exists($pdo, 'order_rooming', 'checked_out_status')) {
            $activeRoomingClause .= " AND COALESCE(rmg.checked_out_status,0) = 0 ";
        }

        foreach ($days as $day) {
            $dailyData[$day] = [
                'occupied_rooms' => 0,
                'vacant_rooms'   => 0,
                'arrivals'       => 0,
                'departures'     => 0,
                'occupancy_pct'  => 0,
            ];

            /* Occupied rooms */
            $sql = "
                SELECT COUNT(DISTINCT rmg.room_id)
                FROM order_rooming rmg
                INNER JOIN orders o ON o.id = rmg.order_id
                INNER JOIN rooms r ON r.id = rmg.room_id
                WHERE DATE(COALESCE(rmg.check_in, o.check_in)) <= ?
                  AND DATE(COALESCE(rmg.check_out, o.check_out)) > ?
                  AND rmg.room_id IS NOT NULL
                  AND rmg.room_id > 0
                  $activeOrderClause
                  $activeRoomingClause
                  $propertyRoomFilter
            ";
            $params = [$day, $day];
            if ($propertyId > 0) {
                $params[] = $propertyId;
            }

            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $occupiedRooms = (int)$stmt->fetchColumn();

            /* Arrivals */
            $sql = "
                SELECT COUNT(*)
                FROM order_rooming rmg
                INNER JOIN orders o ON o.id = rmg.order_id
                INNER JOIN rooms r ON r.id = rmg.room_id
                WHERE DATE(COALESCE(rmg.check_in, o.check_in)) = ?
                  AND rmg.room_id IS NOT NULL
                  AND rmg.room_id > 0
                  $activeOrderClause
                  $activeRoomingClause
                  $propertyRoomFilter
            ";
            $params = [$day];
            if ($propertyId > 0) {
                $params[] = $propertyId;
            }

            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $arrivals = (int)$stmt->fetchColumn();

            /* Departures */
            $sql = "
                SELECT COUNT(*)
                FROM order_rooming rmg
                INNER JOIN orders o ON o.id = rmg.order_id
                INNER JOIN rooms r ON r.id = rmg.room_id
                WHERE DATE(COALESCE(rmg.check_out, o.check_out)) = ?
                  AND rmg.room_id IS NOT NULL
                  AND rmg.room_id > 0
                  $activeOrderClause
                  $activeRoomingClause
                  $propertyRoomFilter
            ";
            $params = [$day];
            if ($propertyId > 0) {
                $params[] = $propertyId;
            }

            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            $departures = (int)$stmt->fetchColumn();

            $vacantRooms = max(0, $totalRooms - $occupiedRooms);
            $occupancyPct = $totalRooms > 0 ? ($occupiedRooms / $totalRooms) * 100 : 0;

            $dailyData[$day] = [
                'occupied_rooms' => $occupiedRooms,
                'vacant_rooms'   => $vacantRooms,
                'arrivals'       => $arrivals,
                'departures'     => $departures,
                'occupancy_pct'  => $occupancyPct,
            ];
        }
    } catch (Throwable $e) {
        $errors[] = 'Forecast Data: ' . $e->getMessage();
    }
}

/* ---------------------------------
   Summary
--------------------------------- */
$avgOcc = 0;
$maxOcc = 0;
$minOcc = 0;
$totalArrivals = 0;
$totalDepartures = 0;

if ($dailyData) {
    $occValues = array_map(function ($r) {
        return (float)$r['occupancy_pct'];
    }, $dailyData);

    $avgOcc = array_sum($occValues) / max(1, count($occValues));
    $maxOcc = max($occValues);
    $minOcc = min($occValues);

    foreach ($dailyData as $r) {
        $totalArrivals += (int)$r['arrivals'];
        $totalDepartures += (int)$r['departures'];
    }
}

require_once __DIR__ . '/../../includes/header.php';
?>

<div class="hero-band">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-calendar3"></i> Planning</div>
            <div class="hero-title mt-2">Occupancy Forecast</div>
            <div class="hero-subtitle mt-2">
                View forward room usage, arrivals, departures, and occupancy percentages across your selected period.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="/rooms/modules/payments/frontdesk_dashboard.php" class="btn btn-outline-light">
                <i class="bi bi-arrow-left me-2"></i>Front Desk
            </a>
        </div>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger">
        <div class="fw-bold mb-2">Please fix the following:</div>
        <ul class="mb-0">
            <?php foreach ($errors as $err): ?>
                <li><?php echo h($err); ?></li>
            <?php endforeach; ?>
        </ul>
    </div>
<?php endif; ?>

<div class="card panel-card mb-4">
    <div class="card-header">
        <h5 class="mb-0">Forecast Filters</h5>
    </div>
    <div class="card-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-md-3">
                <label class="form-label">Date From</label>
                <input type="date" name="date_from" class="form-control" value="<?php echo h($dateFrom); ?>">
            </div>

            <div class="col-md-3">
                <label class="form-label">Date To</label>
                <input type="date" name="date_to" class="form-control" value="<?php echo h($dateTo); ?>">
            </div>

            <div class="col-md-4">
                <label class="form-label">Property</label>
                <select name="property_id" class="form-select">
                    <option value="0">All properties</option>
                    <?php foreach ($properties as $property): ?>
                        <option value="<?php echo (int)$property['id']; ?>" <?php echo $propertyId === (int)$property['id'] ? 'selected' : ''; ?>>
                            <?php echo h($property['name']); ?><?php echo !empty($property['code']) ? ' - ' . h($property['code']) : ''; ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-md-2 d-grid">
                <button class="btn btn-primary">Run Forecast</button>
            </div>
        </form>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-primary-subtle text-primary">
                <i class="bi bi-grid"></i>
            </div>
            <div>
                <div class="metric-label">Room Stock</div>
                <div class="metric-value"><?php echo number_format($totalRooms); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-success-subtle text-success">
                <i class="bi bi-bar-chart-line"></i>
            </div>
            <div>
                <div class="metric-label">Avg Occ.</div>
                <div class="metric-value"><?php echo pct_fmt($avgOcc); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-danger-subtle text-danger">
                <i class="bi bi-graph-up-arrow"></i>
            </div>
            <div>
                <div class="metric-label">Peak Occ.</div>
                <div class="metric-value"><?php echo pct_fmt($maxOcc); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-info-subtle text-info">
                <i class="bi bi-graph-down-arrow"></i>
            </div>
            <div>
                <div class="metric-label">Low Occ.</div>
                <div class="metric-value"><?php echo pct_fmt($minOcc); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-warning-subtle text-warning">
                <i class="bi bi-box-arrow-in-right"></i>
            </div>
            <div>
                <div class="metric-label">Arrivals</div>
                <div class="metric-value"><?php echo number_format($totalArrivals); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-secondary-subtle text-secondary">
                <i class="bi bi-box-arrow-right"></i>
            </div>
            <div>
                <div class="metric-label">Departures</div>
                <div class="metric-value"><?php echo number_format($totalDepartures); ?></div>
            </div>
        </div>
    </div>
</div>

<div class="card panel-card mb-4">
    <div class="card-header d-flex justify-content-between align-items-center">
        <div>
            <h5 class="mb-0">Daily Occupancy Forecast</h5>
            <small><?php echo h($dateFrom); ?> to <?php echo h($dateTo); ?></small>
        </div>
    </div>

    <div class="card-body p-0">
        <div class="table-responsive">
            <table class="table table-modern align-middle mb-0">
                <thead>
                    <tr>
                        <th>Date</th>
                        <th>Day</th>
                        <th class="text-end">Rooms</th>
                        <th class="text-end">Occupied</th>
                        <th class="text-end">Vacant</th>
                        <th class="text-end">Arrivals</th>
                        <th class="text-end">Departures</th>
                        <th class="text-end">Occ. %</th>
                        <th class="text-end">Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (!$dailyData): ?>
                        <tr>
                            <td colspan="9" class="text-center text-muted py-5">
                                No forecast data available.
                            </td>
                        </tr>
                    <?php else: ?>
                        <?php foreach ($dailyData as $day => $row): ?>
                            <?php
                            $occPct = (float)$row['occupancy_pct'];
                            $badgeClass = 'success';
                            if ($occPct >= 85) {
                                $badgeClass = 'danger';
                            } elseif ($occPct >= 65) {
                                $badgeClass = 'warning';
                            } elseif ($occPct >= 40) {
                                $badgeClass = 'primary';
                            }
                            ?>
                            <tr>
                                <td><strong><?php echo h($day); ?></strong></td>
                                <td><?php echo h(date('D', strtotime($day))); ?></td>
                                <td class="text-end"><?php echo number_format($totalRooms); ?></td>
                                <td class="text-end"><?php echo number_format($row['occupied_rooms']); ?></td>
                                <td class="text-end"><?php echo number_format($row['vacant_rooms']); ?></td>
                                <td class="text-end"><?php echo number_format($row['arrivals']); ?></td>
                                <td class="text-end"><?php echo number_format($row['departures']); ?></td>
                                <td class="text-end">
                                    <span class="badge text-bg-<?php echo h($badgeClass); ?> px-3 py-2">
                                        <?php echo pct_fmt($occPct); ?>
                                    </span>
                                </td>
                                <td class="text-end">
                                    <div class="d-inline-flex gap-2 flex-wrap justify-content-end">
                                        <a href="/rooms/modules/payments/room_rack.php?view_date=<?php echo urlencode($day); ?><?php echo $propertyId > 0 ? '&property_id=' . $propertyId : ''; ?>"
                                           class="btn btn-sm btn-outline-primary">
                                            Room Rack
                                        </a>

                                        <a href="/rooms/modules/payments/arrivals_today.php?arrival_date=<?php echo urlencode($day); ?><?php echo $propertyId > 0 ? '&property_id=' . $propertyId : ''; ?>"
                                           class="btn btn-sm btn-outline-success">
                                            Arrivals
                                        </a>

                                        <a href="/rooms/modules/payments/departures_today.php?departure_date=<?php echo urlencode($day); ?><?php echo $propertyId > 0 ? '&property_id=' . $propertyId : ''; ?>"
                                           class="btn btn-sm btn-outline-secondary">
                                            Departures
                                        </a>
                                    </div>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
                <?php if ($dailyData): ?>
                    <tfoot>
                        <tr>
                            <th colspan="5" class="text-end">Range Totals</th>
                            <th class="text-end"><?php echo number_format($totalArrivals); ?></th>
                            <th class="text-end"><?php echo number_format($totalDepartures); ?></th>
                            <th class="text-end"><?php echo pct_fmt($avgOcc); ?></th>
                            <th></th>
                        </tr>
                    </tfoot>
                <?php endif; ?>
            </table>
        </div>
    </div>
</div>

<?php require_once __DIR__ . '/../../includes/footer.php'; ?>