constant/hkust_bnb_constant.py

# hkust_bnb_constant.py

GET_ALL_NEIGHBORHOODS = """
    SELECT DISTINCT NEIGHBOURHOOD 
    FROM airbnb_master_data 
    WHERE NEIGHBOURHOOD IS NOT NULL 
    ORDER BY NEIGHBOURHOOD
"""

GET_NEIGHBORHOOD_LISTINGS = """
    SELECT m.ID, m.NAME, m.HOST_NAME, m.NEIGHBOURHOOD,
           m.LATITUDE, m.LONGITUDE, m.ROOM_TYPE, m.PRICE,
           COUNT(r.LISTING_ID) as NUMBER_OF_REVIEWS, m.REVIEWS_PER_MONTH,
           m.MINIMUM_NIGHTS, m.AVAILABILITY_365
    FROM airbnb_master_data m
    LEFT JOIN airbnb_reviews_data r ON m.ID = r.LISTING_ID
    WHERE m.LATITUDE IS NOT NULL 
    AND m.LONGITUDE IS NOT NULL 
    AND m.NEIGHBOURHOOD = :neighborhood 
    GROUP BY m.ID, m.NAME, m.HOST_NAME, m.NEIGHBOURHOOD,
             m.LATITUDE, m.LONGITUDE, m.ROOM_TYPE, m.PRICE,
             m.REVIEWS_PER_MONTH, m.MINIMUM_NIGHTS, m.AVAILABILITY_365
    ORDER BY COUNT(r.LISTING_ID) DESC, m.PRICE ASC
    FETCH FIRST :limit ROWS ONLY
"""

GET_LISTING_REVIEWS = """
    SELECT REVIEW_DATE, REVIEWER_NAME, 
           CASE 
               WHEN LENGTH(COMMENTS) > 200 
               THEN SUBSTR(COMMENTS, 1, 200) || '...'
               ELSE COMMENTS 
           END as COMMENTS
    FROM AIRBNB_REVIEWS_DATA 
    WHERE LISTING_ID = :listing_id 
    AND ROWNUM <= 10
    ORDER BY REVIEW_DATE DESC
"""

GET_LISTING_REVIEWS_FOR_SEARCH = """
    SELECT COMMENTS
    FROM AIRBNB_REVIEWS_DATA 
    WHERE LISTING_ID = :listing_id 
    AND COMMENTS IS NOT NULL
    AND ROWNUM <= 10
    ORDER BY REVIEW_DATE DESC
"""

GET_TRAFFIC_CAMERA_LOCATIONS = """
    SELECT KEY, LATITUDE, LONGITUDE 
    FROM TD_TRAFFIC_CAMERA_LOCATION 
    WHERE KEY IN ({placeholders})
    AND LATITUDE IS NOT NULL 
    AND LONGITUDE IS NOT NULL
"""

DISCOUNT_INFO_TEMPLATE = """
<div style='background-color: #e8f5e9; padding: 8px; margin: 10px 0; border-radius: 4px; border-left: 4px solid #4caf50;'>
    <p style='margin: 2px 0; font-weight: bold; color: #2e7d32;'>{discount_percentage}% ENV PROTECTION DISCOUNT!</p>
    <p style='margin: 2px 0; font-size: 0.85em;'>Avg. {avg_vehicle_count:.1f} vehicles across {observation_count} observations</p>
</div>
"""

NO_DISCOUNT_INFO_TEMPLATE = """
<div style='background-color: #e8f5e9; padding: 8px; margin: 10px 0; border-radius: 4px; border-left: 4px solid #4caf50;'>
    <p style='margin: 2px 0; font-size: 0.85em;'>Avg. {avg_vehicle_count:.1f} vehicles across {observation_count} observations</p>
</div>
"""

TRAFFIC_SPOT_INFO_TEMPLATE = """
<div class='traffic-spot-info' style='margin: 10px 0; padding: 8px; background-color: #f0f8ff; border-radius: 4px; border-left: 4px solid #4285f4;'>
    <p style='margin: 5px 0;'>
        <strong>Nearest Traffic Spot:</strong> {spot_key}
        <br/>
        <strong>Distance:</strong> {distance_str}
    </p>
</div>
"""

RELEVANCE_INFO_TEMPLATE = """
<div class='relevance-info' style='margin: 10px 0; padding: 8px; background-color: #f8f9fa; border-radius: 4px;'>
    <p style='margin: 5px 0;'>
        <strong>Match Score:</strong> {relevance_percentage:.0f}%
        <br/>
        <strong>Relevance:</strong> {relevance_features}
        <br/>
        <strong>Match Type:</strong> {matching_features}
    </p>
</div>
"""

POPUP_CONTENT_TEMPLATE = """
<div style='min-width: 280px; max-width: 320px; padding: 15px;'>
    <h4 style='margin: 0 0 10px 0; color: #2c3e50;'>{listing_name}</h4>
    <p style='margin: 5px 0;'><strong>Host:</strong> {host_name}</p>
    <p style='margin: 5px 0;'><strong>Room Type:</strong> {room_type}</p>
    <p style='margin: 5px 0;'>{price_display}</p>
    <p style='margin: 5px 0;'><strong>Reviews:</strong> {review_count:.0f}</p>
    {discount_info}
    {traffic_spot_info}
    {relevance_info}
</div>
"""

MAP_SCRIPT = """
<script>
function showTrafficSpot(lat, lng) {
    var map = document.querySelector('.folium-map')._leaflet_map;
    map.setView([lat, lng], 18);
    map.eachLayer(function(layer) {
        if (layer instanceof L.Marker) {
            var latLng = layer.getLatLng();
            if (Math.abs(latLng.lat - lat) < 0.0001 && Math.abs(latLng.lng - lng) < 0.0001) {
                layer.openPopup();
            }
        }
    });
}
</script>
"""

# HTML Templates for Streamlit UI
SIDEBAR_HEADER = ('<p class="sidebar-header">HKUST BNB+ </p>')

SIDEBAR_DIVIDER = '<hr style="margin: 20px 0; border: none; border-top: 1px solid #e0e0e0;">'

TRAFFIC_EXPLANATION = """
### How HKUST BNB+ Acheived (E)SG - Environment Pillar, use Traffic Spot from Department of Transport and do traffic analysis hence provided discount according to the average traffic on the previous days.

We use real-time traffic data to offer you the best possible rates:

* **Blue Camera Icons**: Areas with very low traffic (less than 2 vehicles detected)
  * Enjoy a peaceful stay with **20% DISCOUNT** on these properties!

* **Orange Camera Icons**: Areas with moderate traffic (2-5 vehicles detected)
  * Get a **10% DISCOUNT** on these properties!

* **Purple Camera Icons**: Areas with heavier traffic (more than 5 vehicles)
  * Standard rates apply for these properties

Look for the blue connecting lines on the map to see which traffic spot affects each property!

Remark : Currently only few traffic spot avaliable, in the future will provide more.
"""

SEARCH_EXPLANATION = """
### How HKUST BNB+ Acheived E(S)G - Social Pillar , use keyword to provided keyword relevance analysis to matches the require need from HKUST Student

Our advanced search technology goes beyond simple keyword matching to understand the meaning behind your search terms:

When you search for terms like "muslim" , "convenient " or "girl only" our system:
1. Analyzes both listing titles and actual guest reviews
2. Understands the context and meaning (not just matching exact words)
3. Ranks listings based on overall relevance to your search


This helps you find properties that truly match what you're looking for, even if they don't use the exact words in your search!
"""

REVIEW_CARD_TEMPLATE = """
<div class="review-card">
    <div class="review-header">
        {reviewer_name} - {review_date}
    </div>
    <div class="review-content">
        {highlighted_comments}
    </div>
</div>
"""

LISTINGS_COUNT_INFO = "<p style='text-align:center; color:#4285f4;'>Showing {listings_limit} listings in {neighborhood}</p>"

LISTING_CARD_TEMPLATE = """
<div class="listing-card" style="background-color: {background_color}">
    <h4 class="listing-title">{listing_name}</h4>
    {price_display}
    <p class="listing-info"> Room Type: {room_type}</p>
    <p class="listing-info"> Reviews: {review_count:.0f}</p>
    {relevance_info}
</div>
"""

PRICE_DISPLAY_WITH_DISCOUNT = """<p class="listing-info"> Price : <span class="original-price">${original_price:.0f}</span> <span class="discounted-price">${discounted_price:.0f}</span> {discount_tag}</p>"""

PRICE_DISPLAY_NORMAL = """<p class="listing-info"> Price : ${price:.0f}</p>"""

RELEVANCE_INFO_LISTING = """<p class="listing-info">  Relevance: {relevance_percentage:.0f}% </p>"""

TRAFFIC_DISCOUNT_DISPLAY = """
<div style='background-color: #e8f5e9; padding: 5px; margin: 5px 0; border-radius: 4px; border-left: 3px solid #4caf50;'>
    <p style='margin: 2px 0; color: blue;'><strong>{discount_info}</strong></p>
    <p style='margin: 2px 0; font-size: 0.9em;'>Avg. {avg_vehicle_count:.1f} vehicles across {observation_count} observations</p>
</div>
"""

TRAFFIC_POPUP_BASE = """
<div style='min-width: 600px; padding: 10px;'>
    <p style='margin: 5px 0;'><strong>Location ID:</strong> {location_id}</p>
    {discount_display}
"""

TRAFFIC_RECORDS_HEADER = "<h4>Recent Records (showing {recent_count} of {total_count} total):</h4>"

TRAFFIC_RECORD_ENTRY = """
<div style='border-top: 1px solid #ccc; padding: 5px 0;'>
    <p style='margin: 2px 0;'><strong>Time:</strong> {capture_time}</p>
    <p style='margin: 2px 0;'><strong>Vehicles:</strong> {vehicle_count}</p>
    {image_html}
</div>
"""

TRAFFIC_IMAGE_HTML = """
<div style="position: relative; display: inline-block;">
    <img src='data:image/jpeg;base64,{base64_encoded}' 
         style='max-width: 100px; max-height: 100px; margin: 5px 0; cursor: pointer; border: 2px solid transparent; transition: all 0.3s ease;' 
         onmouseover="this.style.transform='scale(1.05)'; this.style.borderColor='#4285f4';" 
         onmouseout="this.style.transform='scale(1)'; this.style.borderColor='transparent';"
         onclick="this.parentElement.querySelector('.enlarged-view').style.display='flex';"
         alt='Traffic Image'
         title='Click to enlarge'>
    <div class="enlarged-view" style="display: none; position: fixed; top: 0; left: 0; width: 100%; height: 100%; background: rgba(0,0,0,0.8); z-index: 1000; justify-content: center; align-items: center; flex-direction: column;">
        <img src='data:image/jpeg;base64,{base64_encoded}' 
             style='max-width: 90%; max-height: 80vh; margin: 0 auto; border: 3px solid white;'>
        <div style="color: white; margin-top: 15px; background: rgba(0,0,0,0.7); padding: 8px 16px; border-radius: 4px; cursor: pointer; font-weight: bold;"
             onclick="this.parentElement.style.display='none';">
            Close
        </div>
    </div>
</div>
"""

TRAFFIC_NO_RECORDS = "<p>No records available</p>"

LOTTIE_HTML = """
<div style="
  position: fixed !important;
  top: 0 !important;
  left: 0 !important;
  width: 100vw !important;
  height: 100vh !important;
  display: flex !important;
  justify-content: center !important;
  align-items: center !important;
  background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%) !important;
  z-index: 9999 !important;
  margin: 0 !important;
  padding: 0 !important;
  overflow: hidden !important;
">
  <div style="
    display: flex !important;
    flex-direction: column !important;
    align-items: center !important;
    background-color: rgba(255, 255, 255, 0.9) !important;
    border-radius: 20px !important;
    box-shadow: 0 20px 60px rgba(0, 0, 0, 0.15) !important;
    padding: 40px !important;
    width: 90% !important;
    max-width: 600px !important;
    backdrop-filter: blur(10px) !important;
    transform: translateY(0) !important;
    animation: float 3s ease-in-out infinite !important;
    margin: 0 !important;
  ">
    <h2 style="
      font-family: 'Montserrat', -apple-system, BlinkMacSystemFont, sans-serif !important;
      font-weight: 700 !important;
      font-size: 2.2rem !important;
      color: #2c3e50 !important;
      margin-bottom: 15px !important;
      text-align: center !important;
      text-shadow: 1px 1px 0 rgba(255, 255, 255, 0.8) !important;
      letter-spacing: -0.5px !important;
      width: 100% !important;
      padding: 0 10px !important;
    ">Ready to find your dream house with HKUST BNB+?</h2>
    
    <p style="
      font-family: 'Open Sans', -apple-system, BlinkMacSystemFont, sans-serif !important;
      font-size: 1.1rem !important;
      color: #5d7aa3 !important;
      line-height: 1.6 !important;
      text-align: center !important;
      margin-bottom: 30px !important;
      max-width: 90% !important;
      width: 100% !important;
      padding: 0 !important;
    ">Wonderful platform boosted with <span style="color: #2ecc71 !important; font-weight: 600 !important;">ESG Principles</span>, let you find your dream house easily!</p>
    
    <div style="
      position: relative !important;
      width: 300px !important;
      height: 300px !important;
      margin-bottom: 20px !important;
      display: block !important;
    ">
      <script src="https://unpkg.com/@dotlottie/player-component@2.7.12/dist/dotlottie-player.mjs" type="module"></script>
      <dotlottie-player 
        src="https://lottie.host/3bb60ccd-9169-47e0-9a83-a2b5125938f5/FhiJ1l1ZYM.lottie" 
        background="transparent" 
        speed="1" 
        style="width: 100% !important; height: 100% !important; display: block !important;" 
        loop 
        autoplay
      ></dotlottie-player>
      
      <div style="
        position: absolute !important;
        bottom: -10px !important;
        left: 0 !important;
        right: 0 !important;
        height: 20px !important;
        background: radial-gradient(ellipse at center, rgba(0,0,0,0.2) 0%, rgba(0,0,0,0) 70%) !important;
        border-radius: 50% !important;
        filter: blur(5px) !important;
        animation: shadow-pulse 3s ease-in-out infinite !important;
        z-index: -1 !important;
        width: 100% !important;
      "></div>
    </div>
    
    <div style="
      display: flex !important;
      align-items: center !important;
      gap: 15px !important;
      margin-top: 10px !important;
      justify-content: center !important;
      width: 100% !important;
    ">
      <div style="
        width: 12px !important;
        height: 12px !important;
        background-color: #3498db !important;
        border-radius: 50% !important;
        animation: pulse 1.5s infinite ease-in-out !important;
        display: block !important;
      "></div>
      <div style="
        width: 12px !important;
        height: 12px !important;
        background-color: #2ecc71 !important;
        border-radius: 50% !important;
        animation: pulse 1.5s infinite ease-in-out 0.3s !important;
        display: block !important;
      "></div>
      <div style="
        width: 12px !important;
        height: 12px !important;
        background-color: #e74c3c !important;
        border-radius: 50% !important;
        animation: pulse 1.5s infinite ease-in-out 0.6s !important;
        display: block !important;
      "></div>
    </div>
  </div>
</div>

<style>
@import url('https://fonts.googleapis.com/css2?family=Montserrat:wght@500;700&family=Open+Sans:wght@400;600&display=swap');

@keyframes float {
  0%, 100% { transform: translateY(0) !important; }
  50% { transform: translateY(-15px) !important; }
}

@keyframes shadow-pulse {
  0%, 100% { opacity: 0.6 !important; transform: scale(1) !important; }
  50% { opacity: 0.3 !important; transform: scale(0.9) !important; }
}

@keyframes pulse {
  0%, 100% { transform: scale(1) !important; opacity: 1 !important; }
  50% { transform: scale(1.5) !important; opacity: 0.5 !important; }
}

/* Additional fixes for full page display */
html, body {
  margin: 0 !important;
  padding: 0 !important;
  width: 100% !important;
  height: 100% !important;
  overflow: hidden !important;
}

/* Ensure animations work properly */
* {
  box-sizing: border-box !important;
}

/* Mobile responsiveness */
@media (max-width: 768px) {
  h2[style] {
    font-size: 1.8rem !important;
  }
  
  p[style] {
    font-size: 1rem !important;
  }
  
  div[style*="width: 300px"] {
    width: 250px !important;
    height: 250px !important;
  }
}

/* Fix for some Streamlit environments */
.stApp {
  position: relative !important;
  z-index: auto !important;
}

.main .block-container {
  max-width: 100% !important;
  padding: 0 !important;
  margin: 0 !important;
}
</style>
"""