
    :j`                    $   d Z ddlmZmZmZmZ ddlmZmZ ddl	m
Z
 ddlZ eded      Zej                  d	      d
        Zej                  d      d        Zej                  d      d        Zej                  d      d        Zej                  d      d        Zej                  d      d        Zej                  d      d        Zej                  d      d        Zej                  d      d        Zej                  d      d        Zej                  d      d        Zej                  ddg       d!        Zej                  dd"g       d#        Zej                  d$d%g       d&        Zej                  d'd(g       d)        Zy)*zG
API Routes Blueprint
Contains all /api/ endpoints for the application
    )	Blueprintrequestjsonifycurrent_appcreate_enginetextsessionmakerNapiz/api)
url_prefixz/beaches-gridc            
         	 t        t        j                  j                  dd            } t        t        j                  j                  dd            }t        t        j                  j                  dd            }t        j                  j                  dd      }t        j                  j                  d	d
      }t        j                  j                  dd
      }t        j                  j                  dd
      }t        j                  j                  dd
      }|dkD  r|}n| dz
  |z  }ddlm}	m}
 ddlm} ddl	m
} |d   } |	|      } |dd|      } |       }|j                   |
d            j                         }|r|d   nd}g }d}d}|rd}g }i }|r|j                  d       d| d|d	<   |r|j                  d       d| d|d<   |r|j                  d       d| d|d<   |dk(  r|j                  d       nD|dk(  r|j                  d       n-|dk(  r|j                  d       n|d k(  r|j                  d!       |r|d"d"j                  |      z   z  }|d#z  }|dk(  r|d$z  }n|dk(  r|d%z  }n|d$z  }d&| d'}|j                   |
|      |      j                         }|r|d   nd}|d(| d)| z   }|j                   |
|      |      j                         }||z   |k  }|D ]  }|j                   xs d*}t#        |      d+kD  r|d,d+ j%                  d-d      d   d.z   }d/}|j&                  rI|j&                  dkD  r:|j(                  r|j(                  d0k(  rd1nd2} d3|  t        |j&                         d4}g }!|j*                  r|!j                  |j*                         |j,                  r|!j                  |j,                         |!rd5j                  |!      n|j.                  xs d6}"i d7t1        |j2                        d8|j4                  xs d9d:|d;|"d<|j6                  xs d=d|j,                  xs d
d>|j8                  xs dd?|j:                  xs d@dA|dB|j&                  rt=        |j&                        nd,dC|j>                  rt=        |j>                        nddD|j(                  xs dEdF|j@                  rt=        |j@                        nddG|jB                  rt        |jB                        nddH|jD                  r|jD                  jG                         nd
dI|jH                  r|jH                  jG                         nd
}#|j                  |#        |jK                          tM        || ||||dJ|||||dKdL      S # tN        $ rM}$tP        jR                  jU                  dMt1        |$              tM        dNt1        |$      i      dOfcY d,}$~$S d,}$~$ww xY w)Pz@Get beaches data for the grid page with filtering and paginationpage   offsetr   per_page   filterallsearch countrycitycategoryr   r
   ADMIN_CONFIGDATABASE_URLF
autocommit	autoflushbind
            SELECT EXISTS (
                SELECT FROM information_schema.tables 
                WHERE table_schema = 'public' 
                AND table_name = 'beach_places'
            )
        a  
                SELECT bp.beach_place_id, bp.beach_name, bp.address, bp.city, bp.beach_information,
                       c.company_name, co.country_name,
                       COALESCE(booking_counts.booking_count, 0) as popularity,
                       bpp.photo_path,
                       bps.price, cur.currency_code,
                       MIN(CASE WHEN bpt.bed_price > 0 THEN bpt.bed_price ELSE NULL END) as min_bed_price,
                       ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating,
                       COUNT(r.review_id) as review_count,
                       bp.created_at, bp.updated_at
                FROM beach_places bp
                LEFT JOIN companies c ON bp.company_id = c.company_id
                LEFT JOIN countries co ON bp.country_id = co.country_id
                LEFT JOIN (
                    SELECT beach_place_id, COUNT(*) as booking_count
                    FROM bookings 
                    WHERE status IN ('confirmed', 'completed')
                    GROUP BY beach_place_id
                ) booking_counts ON bp.beach_place_id = booking_counts.beach_place_id
                LEFT JOIN (
                    SELECT DISTINCT ON (beach_place_id) 
                           beach_place_id, photo_path
                    FROM beach_places_photos 
                    WHERE photo_primary = true
                    ORDER BY beach_place_id, sort_order
                ) bpp ON bp.beach_place_id = bpp.beach_place_id
                LEFT JOIN (
                    SELECT DISTINCT ON (beach_place_id)
                           beach_place_id, price, currency_id
                    FROM beach_places_schedules
                    WHERE from_date <= CURRENT_DATE AND to_date >= CURRENT_DATE
                    ORDER BY beach_place_id, from_date
                ) bps ON bp.beach_place_id = bps.beach_place_id
                LEFT JOIN currencies cur ON bps.currency_id = cur.currency_id
                LEFT JOIN beach_place_terrains bpt ON bp.beach_place_id = bpt.beach_place_id
                LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true
                WHERE bp.enable_beach = true
            zr(bp.beach_name ILIKE :search OR bp.address ILIKE :search OR bp.city ILIKE :search OR c.company_name ILIKE :search)%co.country_name ILIKE :countryzbp.city ILIKE :citypopular.COALESCE(booking_counts.booking_count, 0) >= 5latestz2bp.created_at >= CURRENT_DATE - INTERVAL '30 days'tropicalzj(co.country_name IN ('Maldives', 'Thailand', 'Indonesia', 'Philippines') OR bp.address ILIKE '%tropical%')mediterraneanzk(co.country_name IN ('Greece', 'Italy', 'Spain', 'Turkey', 'Cyprus') OR bp.address ILIKE '%mediterranean%') AND a/  
                GROUP BY bp.beach_place_id, bp.beach_name, bp.address, bp.city, bp.beach_information,
                         c.company_name, co.country_name, booking_counts.booking_count, bpp.photo_path,
                         bps.price, cur.currency_code, bp.created_at, bp.updated_at
            z( ORDER BY popularity DESC, bp.beach_namez+ ORDER BY bp.created_at DESC, bp.beach_nameSELECT COUNT(*) FROM () as count_query LIMIT  OFFSET 4Experience stunning beauty and crystal clear waters.x   N ...N/AEUR   €$from 
 / per day, Beautiful LocationidnameBeachdescriptionlocationcompanyPremium Beach
popularityimage/static/img/tour_1.jpgprice_displaymin_bed_pricepricecurrencyUSDratingreviews
created_at
updated_atr   r   r   has_moretotalr   r   r   r   r   data
paginationfilterszError in beaches grid API: error  )+intr   argsget
sqlalchemyr   r	   sqlalchemy.ormr   configr   executefetchoneappendjoinfetchallbeach_informationlenrsplitrF   currency_coder   country_nameaddressstrbeach_place_id
beach_namecompany_namerB   
photo_pathfloatrG   
avg_ratingreview_countrL   	isoformatrM   closer   	Exceptionr   loggerrV   )%r   r   r   filter_typer   r   r   r   actual_offsetr   r	   r   r   r   engineSessionLocaldbbeach_places_checkbeach_places_existsbeaches_datarO   total_count
base_query
conditionsparamscount_querytotal_resultpaginated_querybeachesbeachr>   rE   currency_symbollocation_partsr?   
beach_dataes%                                        7/var/www/bookbeach.app/backend/app/routes/api_routes.pyget_beaches_gridr      s~   M/7<<##FA./W\\%%h23w||''
B78ll&&x7!!(B/,,""9b1||+<<##J3 A:"M!AX1M2/ 	(#N3|,#uFS^  ZZ . )  hj 	 8J03u%JP JF!!  #W  X%&vha=x !!"BC&'yNy!!!"78#$TF!v i'!!"RS(!!"VW
*!!  #O  P/!!  #P  Q gZ(@@@
   J i'HH
(KK
HH
 3:,>NOK::d;&7@IIKL-9,q/qK )WXJh}o+VVO jjo!6?HHJG &0K?H ! &0#55o9o{#c)"-ds"3":":3"B1"E"MK !&&&5+>+>+B050C0CH[H[_dHdeknO&+O+<SATAT=U<VV`$aM "$::"))%**5%%"))%*<*<=8F499^4U]]Mr^r#e223E,,7 "; 	
 u11D_ u117R !%"2"2"7a U--I1I $] $5CVCVU5+>+>%?\` 5;;U5;;/A  3 3 <u 9I9IeE$4$45q %:L:Ls5#5#56RS !%BRBR%"2"2"<"<">XZ  !%BRBR%"2"2"<"<">XZ!
$ ##J/M&0P 	
 $'$$ & "$
  	$  /  #>s1vh!GHQ()3../s   V!V$ $	W:-AW5/W:5W:z/popular-beachesc                   
   	 t         j                  j                  dd      } ddlm}m} ddlm} ddlm	} |d   } ||      } |dd|	      } |       }|j                   |d
            j                         }	|	r|	d   nd}
g }|
rd}| rJ|j                  dd      }|dz  }|j                   ||      d| i      j                         }t        |      }t        |      dk  rdt        |      z
  }|D cg c]  }t!        |j"                         }}|}|r=dj%                  t'        t        |            D cg c]  }d| 	 c}      }|d| dz  }|dz  }d|i}|rXdt)        |      i}t+        |      D ]  \  }}t)        |      |d| <    |j                   ||      |      j                         }n&|j                   ||      |      j                         }|j-                  |       t        |      dk  rdt        |      z
  }|D cg c]  }t!        |j"                         }}|}|r=dj%                  t'        t        |            D cg c]  }d| 	 c}      }|d| dz  }|dz  }d|i}|rXdt)        |      i}t+        |      D ]  \  }}t)        |      |d| <    |j                   ||      |      j                         }n&|j                   ||      |      j                         }|j-                  |       |j/                          g }|D ]  }|j0                  xs d}t        |      dkD  r|dd j3                  dd      d   dz   }d}|j4                  rI|j4                  dkD  r:|j6                  r|j6                  dk(  rd nd!}d"| t)        |j4                         d#}t!        |j"                        |j8                  |j:                  r'|j<                  r|j:                   d$|j<                   n|j>                  xs d%|j@                  xs d&|jB                  xs d|jD                  xs d'|||j4                  rtG        |j4                        nd|j6                  xs d(|jH                  rtG        |jH                        nd|jJ                  rt)        |jJ                        ndd)}|jM                  |        d*|d+S c c}w c c}w c c}w c c}w # tN        $ r} tQ        d,|         d*g d+cY d} ~ S d} ~ ww xY w)-z2Get popular beaches based on location and bookingsr   r   r   r   r
   r   r   Fr   r"   a	  
                SELECT bp.beach_place_id, bp.beach_name, bp.address, bp.city, bp.beach_information,
                       c.company_name, co.country_name,
                       COALESCE(booking_counts.booking_count, 0) as popularity,
                       bpp.photo_path,
                       bps.price, cur.currency_code,
                       MIN(CASE WHEN bpt.bed_price > 0 THEN bpt.bed_price ELSE NULL END) as min_bed_price,
                       ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating,
                       COUNT(r.review_id) as review_count
                FROM beach_places bp
                LEFT JOIN companies c ON bp.company_id = c.company_id
                LEFT JOIN countries co ON bp.country_id = co.country_id
                LEFT JOIN (
                    SELECT bb.beach_place_id, COUNT(*) as booking_count
                    FROM beach_bookings bb
                    JOIN main_bookings b ON bb.main_booking_id = b.booking_id
                    WHERE b.status IN ('confirmed', 'completed')
                    GROUP BY bb.beach_place_id
                ) booking_counts ON bp.beach_place_id = booking_counts.beach_place_id
                LEFT JOIN (
                    SELECT DISTINCT ON (beach_place_id) 
                           beach_place_id, photo_path
                    FROM beach_places_photos 
                    WHERE photo_primary = true
                    ORDER BY beach_place_id, sort_order
                ) bpp ON bp.beach_place_id = bpp.beach_place_id
                LEFT JOIN (
                    SELECT DISTINCT ON (beach_place_id)
                           beach_place_id, price, currency_id
                    FROM beach_places_schedules
                    WHERE from_date <= CURRENT_DATE AND to_date >= CURRENT_DATE
                    ORDER BY beach_place_id, from_date
                ) bps ON bp.beach_place_id = bps.beach_place_id
                LEFT JOIN currencies cur ON bps.currency_id = cur.currency_id
                LEFT JOIN beach_place_terrains bpt ON bp.beach_place_id = bpt.beach_place_id
                LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true
                WHERE bp.enable_beach = true
                GROUP BY bp.beach_place_id, bp.beach_name, bp.address, bp.city, bp.beach_information,
                         c.company_name, co.country_name, booking_counts.booking_count, bpp.photo_path,
                         bps.price, cur.currency_code
            zWHERE bp.enable_beach = truez;WHERE bp.enable_beach = true AND co.country_name = :countryz
                    ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, bp.beach_name
                    LIMIT 10
                
   ,z
:beach_id_z AND bp.beach_place_id NOT IN ()zT ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, bp.beach_name LIMIT :limitlimit	beach_id_ ORDER BY RANDOM() LIMIT :limitr/   r0   Nr1   r   r2   r3   r4   r5   r6   r7   r8   r9   r:   rA   rD   rI   )r;   r<   r?   r@   rB   rC   r>   rE   rF   rH   rJ   rK   T)successr   zError getting popular beaches: ))r   rY   rZ   r[   r   r	   r\   r   r]   r   r^   r_   replacerb   listrd   ri   rj   ra   rangerX   	enumerateextendrr   rc   re   rF   rf   rk   r   rg   rh   rl   rB   rm   rn   ro   rp   r`   rs   print)!user_countryr   r	   r   r   r   rw   rx   ry   beach_places_resultr{   popular_beachescountry_beaches_querycountry_specific_querycountry_beachesremaining_neededr   existing_idsadditional_beaches_queryiplaceholdersr   
param_dictbeach_idadditional_beachesrandom_beaches_queryrandom_beachesr|   r>   rE   r   r   r   s!                                    r   get_popular_beachesr      sg   j0||''	262/ 	(#N3|,#uFS^ !jj / *  hj 	 9L1!4QV(%!T )>)F)F2Q*& ' + & #%**T2H-IIWcKd"e"n"n"p"&"7 ?#b(#%O(<#< GVWeE$8$8 9WW+@(#&88uSQ]M^G_,`!z!-=,`#aL,2QR^Q__`0aa,(  -C  C(!#34")3/?+@!AJ'0'> D869(m
Yqc?3D)+D9Q4RT^)_)h)h)j&)+D9Q4RTZ)[)d)d)f&&&'9: ?#b(#%O(<#< GVWeE$8$8 9WW'<$#&88uSQ]M^G_,`!z!-=,`#aL(.Ml^[\,]]($(II$!#34")3/?+@!AJ'0'> D869(m
Yqc?3D%'ZZ5I0JJ%W%`%`%bN%'ZZ5I0JF%S%\%\%^N&&~6

 $ 	,E11k5kK;#%)$3/66sA>qAEI "M""u':':Q'>,1,?,?EDWDW[`D`%gj"''8U=P=P9Q8RR\ ] %../((EJZZTYTfTfuzzl"U-?-?,@Alqlyly  mR  ~R --@#..3!))E-E*!.?D?R?Ru':':!;X\!//855:5E5E% 0 0116;6H6H3u112aJ 
+5	,8  L99[  X
 -a&  X
 -ad  0/s34B//0sV   C5S' 7S'S' :SCS' S-'S' S" I2S' S' '	T0TTTz/popular-marketsc                  	   	 t         j                  j                  dd      } ddlm}m} ddlm} ddlm	} |d   } ||      } |dd|	      } |       }|j                   |d
            j                         }	|	r|	d   nd}
g }|
rd}g }| r=|dz   }|dz  }|j                   ||      d| i      j                         }t        |      }t        |      dk  rdt        |      z
  }|D cg c]  }t        |j                          }}|}|r=dj#                  t%        t        |            D cg c]  }d| 	 c}      }|d| dz  }|dz  }dt'        |      i}|rXdt'        |      i}t)        |      D ]  \  }}t'        |      |d| <    |j                   ||      |      j                         }n&|j                   ||      |      j                         }|j+                  |       t        |      dk  rdt        |      z
  }|D cg c]  }t        |j                          }}|}|r=dj#                  t%        t        |            D cg c]  }d| 	 c}      }|d| dz  }|dz  }dt'        |      i}|rXdt'        |      i}t)        |      D ]  \  }}t'        |      |d| <    |j                   ||      |      j                         }n&|j                   ||      |      j                         }|j+                  |       |D ]U  }|j,                  xs d}t        |      dkD  r|dd j/                  dd      d   dz   }d}|j0                  r#|j0                  dkD  rd}d| |j0                  d }t        |j                         |j2                  |j4                  r'|j6                  r|j4                   d!|j6                   n|j8                  xs d"|j:                  xs d"|j<                  xs d|j>                  xs d#|||j@                  rtC        |j@                        nd|jD                  rt'        |jD                        ndd$
}|jG                  |       X |jI                          tK        d%|d&      S c c}w c c}w c c}w c c}w # tL        $ r&} tO        d'|         tK        d%g d&      cY d} ~ S d} ~ ww xY w)(z2Get popular markets based on location and bookingsr   r   r   r   r
   r   r   Fr   
            SELECT EXISTS (
                SELECT FROM information_schema.tables 
                WHERE table_schema = 'public' 
                AND table_name = 'markets'
            )
        a  
                SELECT m.market_id, m.market_name, m.address, m.city, m.description,
                       c.company_name, co.country_name,
                       COALESCE(booking_counts.booking_count, 0) as popularity,
                       mp.photo_path,
                       ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating,
                       COUNT(r.review_id) as review_count,
                       m.min_order_amount
                FROM markets m
                LEFT JOIN companies c ON m.company_id = c.company_id
                LEFT JOIN countries co ON m.country_id = co.country_id
                LEFT JOIN (
                    SELECT mb.market_id, COUNT(*) as booking_count
                    FROM market_bookings mb
                    JOIN main_bookings b ON mb.main_booking_id = b.booking_id
                    WHERE b.status IN ('confirmed', 'completed')
                    GROUP BY mb.market_id
                ) booking_counts ON m.market_id = booking_counts.market_id
                LEFT JOIN (
                    SELECT DISTINCT ON (market_id) 
                           market_id, photo_path
                    FROM market_photos 
                    ORDER BY market_id, created_at
                ) mp ON m.market_id = mp.market_id
                LEFT JOIN beach_places bp ON m.beach_place_id = bp.beach_place_id
                LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true
                WHERE m.is_active = true
            z AND co.country_name = :countrya  
                    GROUP BY m.market_id, m.market_name, m.address, m.city, m.description,
                             c.company_name, co.country_name, booking_counts.booking_count, mp.photo_path,
                             m.min_order_amount
                    ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, m.market_name
                    LIMIT 10
                r   r   z:market_id_z AND m.market_id NOT IN (r   ao  
                    GROUP BY m.market_id, m.market_name, m.address, m.city, m.description,
                             c.company_name, co.country_name, booking_counts.booking_count, mp.photo_path,
                             m.min_order_amount
                    ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, m.market_name LIMIT :limit
                r   
market_id_a:  
                    GROUP BY m.market_id, m.market_name, m.address, m.city, m.description,
                             c.company_name, co.country_name, booking_counts.booking_count, mp.photo_path,
                             m.min_order_amount
                    ORDER BY RANDOM() LIMIT :limit
                0Experience authentic local markets and shopping.r0   Nr1   r   r2   r3   r5   r7   z.0fr9   Local Market/static/img/market_1.jpg)
r;   r<   r?   r@   rB   rC   r>   rE   rJ   rK   T)r   marketszError getting popular markets: )(r   rY   rZ   r[   r   r	   r\   r   r]   r   r^   r_   rb   r   rd   ri   	market_idra   r   rX   r   r   r>   re   min_order_amountmarket_namer   rg   rh   rl   rB   rm   ro   rn   rp   r`   rr   r   rs   r   )!r   r   r	   r   r   r   rw   rx   ry   markets_checkmarkets_existsmarkets_datacountry_markets_querypopular_marketsr   country_marketsr   marketr   additional_markets_queryr   r   r   r   r   additional_marketsrandom_markets_queryrandom_marketsr>   rE   r   market_datar   s!                                    r   get_popular_marketsr     s6   h9||''	262/ 	(#N3|,#uFS^ 

4 ) $  hj 	 .;q)%!: !O )>Ab)b&& + & #%**T2H-IIWcKd"e"n"n"p"&"7 ?#b(#%O(<#< DST&F$4$4 5TT+@(#&88cR^N_H`,a1{1#->,a#bL,2KL>YZ0[[,( - ( "3'7#89")3/?+@!AJ(1,(? F97:9~
Zs#34F)+D9Q4RT^)_)h)h)j&)+D9Q4RTZ)[)d)d)f&&&'9: ?#b(#%O(<#< DST&F$4$4 5TT'<$#&88cR^N_H`,a1{1#->,a#bL(.G~UV,WW($ ) $ "3'7#89")3/?+@!AJ(1,(? F97:9~
Zs#34F%'ZZ5I0JJ%W%`%`%bN%'ZZ5I0JF%S%\%\%^N&&~6 * 1$00f4f{#c)"-ds"3":":3"B1"E"MK !&**v/F/F/J&+O&+O+<V=T=TUX<Y$ZM f../"..KQ;;[a[n[n6;;-r&2E2E1F Gtz  uC  uC  uU  GU%22Dn"("3"3"8q#..L2L#.%2:@:K:KeF$5$56QR;A;N;Ns6#6#67TU ##K0316 	
4LABBk  U
 -b0  U
 -bj  9/s344B7889sV   C*R ,R
'R /R;CR R+'R RH+R 
R 	S'SSSz/popular-adventuresc                  <   	 t         j                  j                  dd      } ddlm}m} ddlm} ddlm	} |d   } ||      } |dd|	      } |       }|j                   |d
            j                         }	|	r|	d   nd}
|j                   |d            j                         }|r|d   nd}|j                   |d            j                         }|r|d   nd}g }|
rd}| rL|rJ|j                  dd      }|dz  }|j                   ||      d| i      j                         }t        |      }t        |      dk  rdt        |      z
  }|D cg c]  }t!        |j"                         }}|}|r=dj%                  t'        t        |            D cg c]  }d| 	 c}      }|d| dz  }|dz  }dt)        |      i}|rXdt)        |      i}t+        |      D ]  \  }}t)        |      |d| <    |j                   ||      |      j                         }n&|j                   ||      |      j                         }|j-                  |       t        |      dk  rdt        |      z
  }|D cg c]  }t!        |j"                         }}|}|r=dj%                  t'        t        |            D cg c]  }d| 	 c}      }|d| dz  }|dz  }dt)        |      i}|rXdt)        |      i}t+        |      D ]  \  }}t)        |      |d| <    |j                   ||      |      j                         }n&|j                   ||      |      j                         }|j-                  |       |j/                          g }|D ]  }|j0                  xs d} t        |       dkD  r| dd j3                  dd      d   dz   } d }!|j4                  rd|j4                  dkD  rUt7        |d!d      }"|"s+t9        |d"      r|j:                  rd#|j:                  v rd$}"nd%}"d&|" t)        |j4                         d'}!t!        |j"                        |j<                  |j>                  r'|j:                  r|j>                   d(|j:                   n|j@                  xs d)|jB                  xs d*|jD                  xs d|jF                  xs d+| |!|j4                  rtI        |j4                        nd|jJ                  rtI        |jJ                        nd|jL                  rt)        |jL                        ndd,}#|jO                  |#        tQ        d-|d.      S c c}w c c}w c c}w c c}w # tR        $ r&}$tU        d/|$        tQ        d-g d.      cY d}$~$S d}$~$ww xY w)0z5Get popular adventures based on location and bookingsr   r   r   r   r
   r   r   Fr   
            SELECT EXISTS (
                SELECT FROM information_schema.tables 
                WHERE table_schema = 'public' 
                AND table_name = 'adventures'
            )
        z
            SELECT EXISTS (
                SELECT FROM information_schema.tables 
                WHERE table_schema = 'public' 
                AND table_name = 'main_bookings'
            )
        z
            SELECT EXISTS (
                SELECT FROM information_schema.tables 
                WHERE table_schema = 'public' 
                AND table_name = 'countries'
            )
        a~  
                SELECT a.adventure_id, a.adventure_name, a.address, a.city, a.description,
                       c.company_name, co.country_name,
                       COALESCE(booking_counts.booking_count, 0) as popularity,
                       ap.photo_path,
                       a.price,
                       cur.currency_symbol,
                       ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating,
                       COUNT(r.review_id) as review_count
                FROM adventures a
                LEFT JOIN companies c ON a.company_id = c.company_id
                LEFT JOIN countries co ON a.country_id = co.country_id
                LEFT JOIN currencies cur ON a.currency_id = cur.currency_id
                LEFT JOIN (
                    SELECT ab.adventure_id, COUNT(*) as booking_count
                    FROM adventure_bookings ab
                    JOIN main_bookings b ON ab.main_booking_id = b.booking_id
                    WHERE b.status IN ('confirmed', 'completed')
                    GROUP BY ab.adventure_id
                ) booking_counts ON a.adventure_id = booking_counts.adventure_id
                LEFT JOIN (
                    SELECT DISTINCT ON (adventure_id) 
                           adventure_id, photo_path
                    FROM adventure_photos 
                    ORDER BY adventure_id, created_at
                ) ap ON a.adventure_id = ap.adventure_id
                LEFT JOIN beach_places bp ON a.beach_place_id = bp.beach_place_id
                LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true
                WHERE a.is_active = true
                GROUP BY a.adventure_id, a.adventure_name, a.address, a.city, a.description,
                         c.company_name, co.country_name, booking_counts.booking_count, ap.photo_path, a.price, cur.currency_symbol
            zWHERE a.is_active = truez7WHERE a.is_active = true AND co.country_name = :countryz
                    ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, a.adventure_name
                    LIMIT 10
                r   r   z:adventure_id_z AND a.adventure_id NOT IN (r   zW ORDER BY COALESCE(booking_counts.booking_count, 0) DESC, a.adventure_name LIMIT :limitr   adventure_id_r   7Experience thrilling adventures and outdoor activities.r0   Nr1   r   r2   r3   r   rg   Greecer5   r6   r7    / per personr9   Adventure LocationAdventure Company/static/img/adventure_1.jpgr;   r<   r?   r@   rB   rC   r>   rE   rG   rJ   rK   Tr   
adventures"Error getting popular adventures: )+r   rY   rZ   r[   r   r	   r\   r   r]   r   r^   r_   r   rb   r   rd   ri   adventure_idra   r   rX   r   r   rr   r>   re   rG   getattrhasattrrg   adventure_namer   rh   rl   rB   rm   rn   ro   rp   r`   r   rs   r   )%r   r   r	   r   r   r   rw   rx   ry   adventures_exists_resultadventures_existsmain_bookings_exists_resultmain_bookings_existscountries_exists_resultcountries_existspopular_adventurescountry_adventures_queryr   country_adventuresr   	adventurer   additional_adventures_queryr   r   r   r   r   additional_adventuresrandom_adventures_queryrandom_adventuresadventures_datar>   rE   r   adventure_datar   s%                                        r   get_popular_adventuresr   =  s   y<||''	262/ 	(#N3|,#uFS^ $&::d 4 / $ hj 	! <T4Q7Y^&(jj 7 2 ' hj 	$ B]:1=bg"$**T 3 . # hj 	  :Q215V[($B  0)A)I)I.M*& ' + & &(ZZ5K0LyZfNg%h%q%q%s"%)*<%=" %&+#%,>(?#? M_`	I$:$: ;``.F+#&885QTUaQbKc,da~aS-A,d#eL/5QR^Q__`3aa/+  0I  I+!3'7#89")3/?+@!AJ+4\+B L<:=l:K
]1##67L,.JJt<W7XZd,e,n,n,p),.JJt<W7XZ`,a,j,j,l)"))*?@ %&+#%,>(?#? M_`	I$:$: ;``*B'#&885QTUaQbKc,da~aS-A,d#eL+1Ml^[\/]]+'+LL'!3'7#89")3/?+@!AJ+4\+B L<:=l:K
]1##67L(*

48O3PR\(](f(f(h%(*

48O3PRX(Y(b(b(d%"))*;<

 + !	3I#//l3lK;#%)$3/66sA>qAEI "M9??Q#6"))5F"M&y.9i>T>TYaene{e{Y{*/*-"''8Y__9M8Nm \ )001!00MV^^`i`v`vy~~.b1G1G0HI  }F  }N  }N  }f  Rf$11H5H'227a"--N1N*!.3<??y/9B9M9M%	 4 45ST:C:P:P3y556VWN "">2C!	3F 4GHHe  a -e&  a -er  <21#674r:;;<sV   EU, U1'U, U$CU, 8U"'U, ;U'JU, U, ,	V5VVVz/adventures-grid-oldc                  
   	 t        t        j                  j                  dd            } t        t        j                  j                  dd            }t        t        j                  j                  dd            }t        j                  j                  dd      }t        j                  j                  d	d
      }t        j                  j                  dd
      }t        j                  j                  dd
      }t        j                  j                  dd
      }|dkD  r|}n| dz
  |z  }ddlm}	m}
 ddlm} ddl	m
} |d   } |	|      } |dd|      } |       }|j                   |
d            j                         }|r|d   nd}g }d}d}|r>d}g }i }|r|j                  d       d| d|d	<   |r|j                  d       d| d|d<   |r|j                  d       d| d|d<   |dk(  r|j                  d       n|dk(  r|j                  d       |r|ddj                  |      z   z  }|dk(  r|dz  }n|dk(  r|d z  }n|dz  }d!| d"}|j                   |
|      |      j                         }|r|d   nd}|d#| d$| z   }|j                   |
|      |      j                         }||z   |k  }|D ]  }|j                   xs d%}t#        |      d&kD  r|d'd& j%                  d(d      d   d)z   }d*}|j&                  r(|j&                  dkD  rd+t        |j&                         d,}g } |j(                  r| j                  |j(                         |j*                  r| j                  |j*                         | rd-j                  |       n|j,                  xs d.}!t/        |j0                        |j2                  xs d/||!|j4                  xs d0|j*                  xs d
|j6                  xs d|j8                  xs d1||j&                  rt;        |j&                        nd|j<                  rt;        |j<                        nd|j>                  rt        |j>                        nd|j@                  r|j@                  jC                         nd
|jD                  r|jD                  jC                         nd
d2}"|j                  |"        |jG                          tI        || ||||d3|||||d4d5      S # tJ        $ r;}#tL        jN                  jQ                  d6|#        tI        d7g dd8      cY d'}#~#S d'}#~#ww xY w)9zQGet adventures data for the grid page with filtering and pagination (old version)r   r   r   r   r   r   r   r   r   r   r   r   r   r   r
   r   r   Fr   r   r  
                SELECT a.adventure_id, a.adventure_name, a.address, a.city, a.description,
                       a.price,
                       c.company_name, co.country_name,
                       COALESCE(booking_counts.booking_count, 0) as popularity,
                       ap.photo_path,
                       ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating,
                       COUNT(r.review_id) as review_count,
                       a.created_at, a.updated_at
                FROM adventures a
                LEFT JOIN companies c ON a.company_id = c.company_id
                LEFT JOIN countries co ON a.country_id = co.country_id
                LEFT JOIN (
                    SELECT ab.adventure_id, COUNT(*) as booking_count
                    FROM adventure_bookings ab
                    JOIN main_bookings b ON ab.main_booking_id = b.booking_id
                    WHERE b.status IN ('confirmed', 'completed')
                    GROUP BY ab.adventure_id
                ) booking_counts ON a.adventure_id = booking_counts.adventure_id
                LEFT JOIN (
                    SELECT DISTINCT ON (adventure_id) 
                           adventure_id, photo_path
                    FROM adventure_photos 
                    ORDER BY adventure_id, created_at
                ) ap ON a.adventure_id = ap.adventure_id
                LEFT JOIN beach_places bp ON a.beach_place_id = bp.beach_place_id
                LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true
                WHERE a.is_active = true
                GROUP BY a.adventure_id, a.adventure_name, a.address, a.city, a.description,
                         a.price,
                         c.company_name, co.country_name, booking_counts.booking_count, ap.photo_path,
                         a.created_at, a.updated_at
            w(a.adventure_name ILIKE :search OR a.description ILIKE :search OR a.city ILIKE :search OR c.company_name ILIKE :search)r#   r$   a.city ILIKE :cityr%   r&   r'   1a.created_at >= CURRENT_DATE - INTERVAL '30 days'r*   + ORDER BY popularity DESC, a.adventure_name- ORDER BY a.created_at DESC, a.adventure_namer+   r,   r-   r.   r   r0   Nr1   r2   r3   from $r   r9   r   	Adventurer   r   r;   r<   r>   r?   r@   r   rB   rC   rE   rG   rJ   rK   rL   rM   rN   rQ   rR   Error getting adventures grid: Tr   r   rO   )rX   r   rY   rZ   r[   r   r	   r\   r   r]   r   r^   r_   r`   ra   rb   r>   rd   re   rG   r   rg   rh   ri   r   r   rl   rB   rm   rn   ro   rp   rL   rq   rM   rr   r   rs   r   rt   rV   $r   r   r   ru   r   r   r   r   rv   r   r	   r   r   r   rw   rx   ry   adventures_checkr   r   rO   r}   r~   r   r   r   r   r   r   r   r>   rE   r   r?   r   r   s$                                       r   get_adventures_gridr     sT   zO7<<##FA./W\\%%h23w||''
B78ll&&x7!!(B/,,""9b1||+<<##J3 A:"M!AX1M2/ 	(#N3|,#uFS^ ::d , '  hj 	 4D,Q/ JF JF!!  #\  ]%&vha=x !!"BC&'yNy!!!"67#$TF!v i'!!"RS(!!"UV gZ(@@@
 i'KK
(MM
KK
 3:,>NOK::d;&7@IIKL-9,q/qK )WXJh}o+VVO D$96BKKMJ &0K?H ( #7	'33p7p{#c)"-ds"3":":3"B1"E"MK !&??y':&,S-A,B-$PM "$>>")))..9))")))*@*@A8F499^4YM^M^Mvbv i445%44C#. ((55L9L(55;"+"6"6";!&11R5R%27@U9??3A=F=Q=QeI$8$89WX>G>T>Ts9#9#9:Z[FOFZFZ)"6"6"@"@"B`bFOFZFZ)"6"6"@"@"B`b"   &&~6G#7J 	
#$'$$ & "$
  	$  O  #B1#!FG4ruMNNOs   S=T   	U	0T?9U?Uz/markets-gridc                   
   	 t        t        j                  j                  dd            } t        t        j                  j                  dd            }t        t        j                  j                  dd            }t        j                  j                  dd      }t        j                  j                  d	d
      }t        j                  j                  dd
      }t        j                  j                  dd
      }t        j                  j                  dd
      }|dkD  r|}n| dz
  |z  }ddlm}	m}
 ddlm} ddl	m
} |d   } |	|      } |dd|      } |       }|j                   |
d            j                         }|r|d   nd}g }d}d}|rd}g }i }|r|j                  d       d| d|d	<   |r|j                  d       d| d|d<   |r|j                  d       d| d|d<   |dk(  r|j                  d       n|dk(  r|j                  d       |r|ddj                  |      z   z  }|dk(  r|dz  }n|dk(  r|d z  }n|dz  }d!| d"}|j                   |
|      |      j                         }|r|d   nd}|d#| d$| z   }|j                   |
|      |      j                         }||z   |k  }|D ]  }|j                   xs d%}t#        |      d&kD  r|d'd& j%                  d(d      d   d)z   }g }|j&                  r|j                  |j&                         |j(                  r|j                  |j(                         |rd*j                  |      n|j*                  xs d+} t-        |j.                        |j0                  xs d,|| |j2                  xs d+|j(                  xs d
|j4                  xs d|j6                  xs d-|j8                  rt;        |j8                        nd|j<                  rt        |j<                        nd|j>                  r|j>                  jA                         nd
|jB                  r|jB                  jA                         nd
d.}!|j                  |!        |jE                          tG        || ||||d/|||||d0d1      S # tH        $ rM}"tJ        jL                  jO                  d2t-        |"              tG        d3t-        |"      i      d4fcY d'}"~"S d'}"~"ww xY w)5z@Get markets data for the grid page with filtering and paginationr   r   r   r   r   r   r   r   r   r   r   r   r   r   r
   r   r   Fr   r   a   
                SELECT m.market_id, m.market_name, m.address, m.city, m.description,
                       c.company_name, co.country_name,
                       COALESCE(booking_counts.booking_count, 0) as popularity,
                       mp.photo_path,
                       ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating,
                       COUNT(r.review_id) as review_count,
                       m.created_at, m.updated_at
                FROM markets m
                LEFT JOIN companies c ON m.company_id = c.company_id
                LEFT JOIN countries co ON m.country_id = co.country_id
                LEFT JOIN (
                    SELECT mb.market_id, COUNT(*) as booking_count
                    FROM market_bookings mb
                    JOIN main_bookings b ON mb.main_booking_id = b.booking_id
                    WHERE b.status IN ('confirmed', 'completed')
                    GROUP BY mb.market_id
                ) booking_counts ON m.market_id = booking_counts.market_id
                LEFT JOIN (
                    SELECT DISTINCT ON (market_id) 
                           market_id, photo_path
                    FROM market_photos 
                    ORDER BY market_id, created_at
                ) mp ON m.market_id = mp.market_id
                LEFT JOIN beach_places bp ON m.beach_place_id = bp.beach_place_id
                LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true
                WHERE m.is_active = true
                GROUP BY m.market_id, m.market_name, m.address, m.city, m.description,
                         c.company_name, co.country_name, booking_counts.booking_count, mp.photo_path,
                         m.created_at, m.updated_at
            zt(m.market_name ILIKE :search OR m.description ILIKE :search OR m.city ILIKE :search OR c.company_name ILIKE :search)r#   r$   zm.city ILIKE :cityr%   r&   r'   z1m.created_at >= CURRENT_DATE - INTERVAL '30 days'r*   z( ORDER BY popularity DESC, m.market_namez* ORDER BY m.created_at DESC, m.market_namer+   r,   r-   r.   r   r0   Nr1   r2   r9   r   Marketr   )r;   r<   r>   r?   r@   r   rB   rC   rJ   rK   rL   rM   rN   rQ   rR   zError in markets grid API: rV   rW   )(rX   r   rY   rZ   r[   r   r	   r\   r   r]   r   r^   r_   r`   ra   rb   r>   rd   re   r   rg   rh   ri   r   r   rl   rB   rm   ro   rn   rp   rL   rq   rM   rr   r   rs   r   rt   rV   )#r   r   r   ru   r   r   r   r   rv   r   r	   r   r   r   rw   rx   ry   r   r   r   rO   r}   r~   r   r   r   r   r   r   r   r>   r   r?   r   r   s#                                      r   get_markets_gridr     s   q/7<<##FA./W\\%%h23w||''
B78ll&&x7!!(B/,,""9b1||+<<##J3 A:"M!AX1M2/ 	(#N3|,#uFS^ 

4 ) $  hj 	 .;q)JB JF!!  #Y  Z%&vha=x !!"BC&'yNy!!!"67#$TF!v i'!!"RS(!!"UV gZ(@@@
 i'HH
(JJ
HH
 3:,>NOK::d;&7@IIKL-9,q/qK )WXJh}o+VVO jjo!6?HHJG &0K?H " 1$00f4f{#c)"-ds"3":":3"B1"E"MK "$;;"))&++6&&"))&*=*=>8F499^4V^^Mm_m f../"..:(#. (%22Dn%228b"("3"3"8q#..L2L:@:K:KeF$5$56QR;A;N;Ns6#6#67TUCICTCT&"3"3"="="?Z\CICTCT&"3"3"="="?Z\ ##K091< 	
 $'$$ & "$
  	$  /  #>s1vh!GHQ()3../s   R$R' '	S=0AS82S=8S=z/popular-adventures-realc                     	 ddl m} m} ddlm} ddlm} |d   } | |      } |dd|      } |       }|j                   |d            j                         }|r|d   nd}	g }
|	r|j                   |d	            j                         }|D ]  }|j                  xs d
}t        |      dkD  r|dd j                  dd      d   dz   }d}|j                  r:|j                  dkD  r+|j                  xs d}d| t        |j                         d}t!        |j"                        |j$                  |j&                  r'|j(                  r|j&                   d|j(                   n|j*                  xs d|j,                  xs d|j.                  xs d|j0                  xs d|||j                  rt3        |j                        nd|j4                  rt3        |j4                        nd|j6                  rt        |j6                        ndd}|
j9                  |        |j;                          t=        d|
d      S # t>        $ r&}tA        d|        t=        dg d      cY d}~S d}~ww xY w)z,Get popular adventures for homepage carouselr   r   r
   r   r   Fr   r   a,  
                SELECT a.adventure_id, a.adventure_name, a.description, a.address, a.city,
                       a.price, a.currency_id,
                       c.company_name, co.country_name,
                       COALESCE(booking_counts.booking_count, 0) as popularity,
                       ap.photo_path,
                       ROUND(AVG(CASE WHEN r.rating > 0 THEN r.rating ELSE NULL END), 1) as avg_rating,
                       COUNT(r.review_id) as review_count,
                       cur.currency_symbol
                FROM adventures a
                LEFT JOIN companies c ON a.company_id = c.company_id
                LEFT JOIN countries co ON a.country_id = co.country_id
                LEFT JOIN currencies cur ON a.currency_id = cur.currency_id
                LEFT JOIN (
                    SELECT ab.adventure_id, COUNT(*) as booking_count
                    FROM adventure_bookings ab
                    JOIN main_bookings b ON ab.main_booking_id = b.booking_id
                    WHERE b.status IN ('confirmed', 'completed')
                    GROUP BY ab.adventure_id
                ) booking_counts ON a.adventure_id = booking_counts.adventure_id
                LEFT JOIN (
                    SELECT DISTINCT ON (adventure_id) 
                           adventure_id, photo_path
                    FROM adventure_photos 
                    ORDER BY adventure_id, created_at
                ) ap ON a.adventure_id = ap.adventure_id
                LEFT JOIN beach_places bp ON a.beach_place_id = bp.beach_place_id
                LEFT JOIN reviews r ON bp.beach_place_id = r.beach_place_id AND r.is_approved = true
                WHERE a.is_active = true
                GROUP BY a.adventure_id, a.adventure_name, a.description, a.address, a.city,
                         a.price, a.currency_id,
                         c.company_name, co.country_name, booking_counts.booking_count, ap.photo_path,
                         cur.currency_symbol
                ORDER BY popularity DESC, avg_rating DESC NULLS LAST
                LIMIT 12
            r   r0   Nr1   r   r2   r3   r6   r7   r   r9   r   r   r   r   Tr   r   )!r[   r   r	   r\   r   r]   r   r^   r_   rb   r>   rd   re   rG   r   rX   ri   r   r   r   rg   rh   rl   rB   rm   rn   ro   rp   r`   rr   r   rs   r   )r   r	   r   r   r   rw   rx   ry   r   r   r   adventures_resultr   r>   rE   r   r   r   s                     r   get_popular_adventures_realr   p  s   a<2/ 	(#N3|,#uFS^ ::d , '  hj 	 4D,Q/ "

4 #1 #, #!F (*G J / 7	'33p7p{#c)"-ds"3":":3"B1"E"MK !&??y':&/&?&?&F3O&+O+<S=Q<RR_$`M i445%44QZQ_Q_dmdzdz9>>"2"Y5K5K4L M  AJ  AR  AR  Aj  Vj(55L9L"+"6"6";!&11R5R#.%27@U9??3A=F=Q=QeI$8$89WX>G>T>Ts9#9#9:Z["  &&~6376 	
4GHH <21#674r:;;<s   H<H? ?	I.I)#I.)I.z/adventures-gridc                  
   	 t        t        j                  j                  dd            } t        t        j                  j                  dd            }t        t        j                  j                  dd            }t        j                  j                  dd      }t        j                  j                  d	d
      }t        j                  j                  dd
      }t        j                  j                  dd
      }t        j                  j                  dd
      }|dkD  r|}n| dz
  |z  }ddlm}	m}
 ddlm} ddl	m
} |d   } |	|      } |dd|      } |       }|j                   |
d            j                         }|r|d   nd}g }d}d}|r>d}g }i }|r|j                  d       d| d|d	<   |r|j                  d       d| d|d<   |r|j                  d       d| d|d<   |dk(  r|j                  d       n|dk(  r|j                  d       |r|ddj                  |      z   z  }|dk(  r|dz  }n|dk(  r|d z  }n|dz  }d!| d"}|j                   |
|      |      j                         }|r|d   nd}|d#| d$| z   }|j                   |
|      |      j                         }||z   |k  }|D ]  }|j                   xs d%}t#        |      d&kD  r|d'd& j%                  d(d      d   d)z   }d*}|j&                  r(|j&                  dkD  rd+t        |j&                         d,}g } |j(                  r| j                  |j(                         |j*                  r| j                  |j*                         | rd-j                  |       n|j,                  xs d.}!t/        |j0                        |j2                  xs d/||!|j4                  xs d0|j*                  xs d
|j6                  xs d|j8                  xs d1||j&                  rt;        |j&                        nd|j<                  rt;        |j<                        nd|j>                  rt        |j>                        nd|j@                  r|j@                  jC                         nd
|jD                  r|jD                  jC                         nd
d2}"|j                  |"        |jG                          tI        || ||||d3|||||d4d5      S # tJ        $ rM}#tL        jN                  jQ                  d6t/        |#              tI        d7t/        |#      i      d8fcY d'}#~#S d'}#~#ww xY w)9UGet adventures data for the grid page with filtering and pagination (updated version)r   r   r   r   r   r   r   r   r   r   r   r   r   r   r
   r   r   Fr   r   r   r   r#   r$   r   r%   r&   r'   r   r*   r   r   r+   r,   r-   r.   r   r0   Nr1   r2   r3   r   r   r9   r   r   r   r   r   rN   rQ   rR   zError in adventures grid API: rV   rW   r   r   s$                                       r   get_adventures_grid_updatedr     s\   z/7<<##FA./W\\%%h23w||''
B78ll&&x7!!(B/,,""9b1||+<<##J3 A:"M!AX1M2/ 	(#N3|,#uFS^ ::d , '  hj 	 4D,Q/ JF JF!!  #\  ]%&vha=x !!"BC&'yNy!!!"67#$TF!v i'!!"RS(!!"UV gZ(@@@
 i'KK
(MM
KK
 3:,>NOK::d;&7@IIKL-9,q/qK )WXJh}o+VVO D$96BKKMJ &0K?H ( #7	'33p7p{#c)"-ds"3":":3"B1"E"MK !&??y':&,S-A,B-$PM "$>>")))..9))")))*@*@A8F499^4YM^M^Mvbv i445%44C#. ((55L9L(55;"+"6"6";!&11R5R%27@U9??3A=F=Q=QeI$8$89WX>G>T>Ts9#9#9:Z[FOFZFZ)"6"6"@"@"B`bFOFZFZ)"6"6"@"@"B`b"   &&~6G#7J 	
#$'$$ & "$
  	$  /  #A#a&!JKQ()3../s   S=T   	U	AUUUz/popular-restaurantsc                     	 t         j                  j                  dd      } ddlm}m} ddlm} ddlm	} |d   } ||      } |dd|	      } |       }|j                   |d
            j                         }	|	r|	d   nd}
g }|
rd}| r6|j                   ||dz         d| i      j                         }t        |      }t        |      dk  rdt        |      z
  }|D cg c]  }t        |j                          }}|}|r=dj#                  t%        t        |            D cg c]  }d| 	 c}      }|d| dz  }|dz  }dt'        |      i}t)        |      D ]  \  }}t'        |      |d| <    |j                   ||      |      j                         }|j+                  |       t        |      dk  rdt        |      z
  }|D cg c]  }t        |j                          }}|}|r=dj#                  t%        t        |            D cg c]  }d| 	 c}      }|d| dz  }|dz  }dt'        |      i}t)        |      D ]  \  }}t'        |      |d| <    |j                   ||      |      j                         }|j+                  |       |j-                          g }|D ]O  }g }|j.                  r|j1                  |j.                         |j2                  r|j1                  |j2                         |rdj#                  |      n|j4                  xs d}t        |j                         |j6                  ||j8                  xs d|j:                  rt'        |j:                        nd|j<                  xs d|j>                  rtA        |j>                        nd|jB                  rt'        |jB                        nd|jD                  xs d|j8                  xs d dd	}|j1                  |       R d|dS c c}w c c}w c c}w c c}w # tF        $ r}tI        d|        dg dcY d }~S d }~ww xY w)!z6Get popular restaurants based on location and bookingsr   r   r   r   r
   r   r   Fr   z
            SELECT EXISTS (
                SELECT FROM information_schema.tables 
                WHERE table_schema = 'public' 
                AND table_name = 'restaurants'
            )
        a  
                SELECT r.restaurant_id, r.restaurant_name, r.address, r.city, r.country_id,
                       co.country_name, r.cuisine_type, r.description,
                       COALESCE(restaurant_stats.review_count, 0) as popularity,
                       rp.photo_path,
                       COALESCE(ROUND(restaurant_stats.avg_rating, 1), 0) as avg_rating,
                       COALESCE(restaurant_stats.review_count, 0) as review_count
                FROM restaurants r
                LEFT JOIN countries co ON r.country_id = co.country_id
                LEFT JOIN (
                    SELECT DISTINCT ON (restaurant_id) 
                           restaurant_id, photo_path
                    FROM restaurant_photos 
                    WHERE is_primary = true
                    ORDER BY restaurant_id, created_at ASC
                ) rp ON r.restaurant_id = rp.restaurant_id
                LEFT JOIN (
                    SELECT r.restaurant_id,
                           COUNT(rev.review_id) as review_count,
                           AVG(CASE WHEN rev.rating > 0 THEN rev.rating ELSE NULL END) as avg_rating
                    FROM restaurants r
                    LEFT JOIN beach_places bp ON r.beach_place_id = bp.beach_place_id
                    LEFT JOIN reviews rev ON bp.beach_place_id = rev.beach_place_id AND rev.is_approved = true
                    GROUP BY r.restaurant_id
                ) restaurant_stats ON r.restaurant_id = restaurant_stats.restaurant_id
                WHERE 1=1
            z
                    AND co.country_name = :country
                    ORDER BY popularity DESC, r.restaurant_name
                    LIMIT 10
                r   r   z:id_z AND r.restaurant_id NOT IN (r   z9 ORDER BY popularity DESC, r.restaurant_name LIMIT :limitr   id_r   r9   zGreat LocationInternational"/static/img/restaurant_default.jpgz
Delicious z cuisine)	r;   r<   r?   cuisine_typerB   rC   rJ   rK   r>   T)r   restaurantsz#Error getting popular restaurants: N)%r   rY   rZ   r[   r   r	   r\   r   r]   r   r^   r_   rb   r   rd   ri   restaurant_idra   r   rX   r   r   rr   r   r`   rg   rh   restaurant_namer  rB   rm   ro   rn   rp   r>   rs   r   )r   r   r	   r   r   r   rw   rx   ry   restaurants_resultrestaurants_existspopular_restaurantsr~   country_restaurantsr   rr   additional_queryr   r   r   existing_idadditional_restaurantsrandom_queryrandom_restaurantsrestaurants_data
restaurantr   r?   restaurant_datar   s                                  r   get_popular_restaurantsr    sp   H4||''	262/ 	(#N3|,#uFS^  ZZ . )  hj 	 7I/2e J8 &(jjj D 7 2 !,/	'1 2:	 $ '++>&?# &'",#%,?(@#@ >QRAOO 4RR#- #&88s<GXAY,ZAtA3Z,Z#[L$*G~UV(WW$ $__ !3'7#89&/&= 9NA{(+K(8FS9%9 *,D9I4JF)S)\)\)^&#**+AB &'",#%,?(@#@ >QRAOO 4RR)#&88s<GXAY,ZAtA3Z,Z#[L &CL>QR$SSL AA!3'7#89&/&= 9NA{(+K(8FS9%9 &(ZZ\0BF%K%T%T%V"#**+=>

 - 	5JN%%joo6&&%%j&=&=>4Btyy0I[I[Io_oH *223"22$ * 7 7 J?<F<Q<Qc*"7"78WX#..V2V:D:O:O%
 5 56UV;E;R;R3z667XY)55m:jF]F]FcacEddl9m
O ##O4'	5*  0@AAy  S -[  S -[N  43A378334sV   C P "P>'P %P1BP P*'P PG(P P 	Q #P;5Q ;Q z/restaurants/searchc                     	 ddl m} m} ddlm} ddlm} |d   } | |      } |dd|      } |       }t        j                  j                  dd	      j                         }t        j                  j                  d
d	      j                         }	t        j                  j                  dd	      j                         }
t        t        j                  j                  dd            }t        t        j                  j                  dd            }d}i }g }|r|j                  d       d| d|d<   |	r|j                  d       d|	 d|d
<   |
r|
dk7  r|j                  d       d|
 d|d<   |r|ddj                  |      z   z  }|dz  }|dz
  |z  }|dz  }||d<   ||d<   |j                   ||      |      j                         }d}i }g }|r|j                  d       |d   |d<   |	r|j                  d       |d
   |d
<   |
r|
dk7  r|j                  d       |d   |d<   |r|ddj                  |      z   z  }|j                   ||      |      j!                         }g }|D ]l  }t#        |j$                        |j&                  |j(                  xs d|j*                  xs d|j,                  s|j.                  r#|j,                  xs d	 d|j.                  xs d	 n|j0                  xs d	|j0                  xs d	|j2                  xs d	|j4                  xs d	|j6                  xs d	|j8                  rd|j8                  j;                  d d      z   nd!|j<                  rt?        |j<                        nd|j@                  rt        |j@                        ndtC        |d"      rtE        |jF                        ndd#}|j                  |       o |jI                          ||||z   dz
  |z  nd}|||k  nd}d%||||||d&S # tJ        $ r1}tM        d't#        |              dt#        |      d(d)fcY d$}~S d$}~ww xY w)*z3Search restaurants by menu items and other criteriar   r   r
   r   r   Fr   r   r   r   cuisiner   r   r   r   a  
            SELECT DISTINCT r.restaurant_id, r.restaurant_name as name, r.description, r.cuisine_type,
                   r.city, r.address, r.phone, r.email, r.website,
                   c.country_name, rp.photo_path,
                   CASE WHEN ri.item_name IS NOT NULL THEN true ELSE false END as menu_match,
                   ROUND(AVG(CASE WHEN rev.rating > 0 THEN rev.rating ELSE NULL END), 1) as avg_rating,
                   COUNT(rev.review_id) as review_count
            FROM restaurants r
            LEFT JOIN countries c ON r.country_id = c.country_id
            LEFT JOIN restaurant_photos rp ON r.restaurant_id = rp.restaurant_id AND rp.is_primary = true
            LEFT JOIN restaurant_items ri ON r.restaurant_id = ri.restaurant_id
            LEFT JOIN beach_places bp ON r.beach_place_id = bp.beach_place_id
            LEFT JOIN reviews rev ON bp.beach_place_id = rev.beach_place_id AND rev.is_approved = true
            WHERE r.is_active = true
        aB  
                (LOWER(r.restaurant_name) LIKE LOWER(:search) 
                 OR LOWER(r.description) LIKE LOWER(:search)
                 OR LOWER(r.cuisine_type) LIKE LOWER(:search)
                 OR LOWER(ri.item_name) LIKE LOWER(:search)
                 OR LOWER(ri.description) LIKE LOWER(:search))
            r#   z*LOWER(c.country_name) LIKE LOWER(:country)zAll Categoriesz*LOWER(r.cuisine_type) LIKE LOWER(:cuisine)r*   z
            GROUP BY r.restaurant_id, r.restaurant_name, r.description, r.cuisine_type,
                     r.city, r.address, r.phone, r.email, r.website,
                     c.country_name, rp.photo_path, ri.item_name, ri.description
        z7 ORDER BY r.restaurant_name LIMIT :limit OFFSET :offsetr   r   a_  
            SELECT COUNT(DISTINCT r.restaurant_id)
            FROM restaurants r
            LEFT JOIN countries c ON r.country_id = c.country_id
            LEFT JOIN restaurant_items ri ON r.restaurant_id = ri.restaurant_id
            LEFT JOIN beach_places bp ON r.beach_place_id = bp.beach_place_id
            WHERE r.is_active = true
        z)Delicious cuisine with excellent service.
Restaurantr9   /\r   
menu_match)r;   r<   r>   r  r?   rh   phoneemailwebsiterC   rJ   rp   r  NT)r   r  r}   current_pagetotal_pagesrO   r   zError in search_restaurants: r   messagerW   )'r[   r   r	   r\   r   r]   r   r   rY   rZ   striprX   r`   ra   r^   rb   scalarri   r  r<   r>   r  r   rg   rh   r  r  r  rm   r   ro   rn   rp   r   boolr  rr   rs   r   )r   r	   r   r   r   rw   rx   ry   search_queryr   cuisine_filterr   r   r~   r   where_conditionsr   r  r   count_paramscount_where_conditionsr}   r  r  r  r  rO   r   s                               r   search_restaurantsr(  "  s   `:2/' $N3|,#uFS^ ||''"5;;=,,""9b1779 )))R8>>@7<<##FA./w||''
B78
   ## %  "#<.2F8 ##$PQ"#G9AF9 n0@@##$PQ"#N#31 5F9 'GLL1A$BBBJ 	  	
 (h&OO
"w!x  ZZZ(8&AJJL !# ")) +  &,H%5L" "))*VW&,Y&7L# n0@@"))*VW&,Y&7L# "7W\\2H%IIIKjjk!2LAHHJ , 	0J*223")55d9d * 7 7 G<[e[j[jnx  oF  oFz4"5R
8O8O8USU7VW  LV  L^  L^  Ld  bd%--3#))/R#))/R%--3MWMbMb#
 5 5 = =dC HH  iM:D:O:O%
 5 56UV@J@W@WJ$;$; <]^=DZQ]=^d:#8#89diO /!	0$ 	
 CNBY^f^r{X-1h>xy)4)@4+%e && &  
 	
  :-c!fX67 SV4c99:s   PP 	Q&Q=QQc                     	 t        t        j                  j                  dd            } t        t        j                  j                  dd            }t        t        j                  j                  dd            }t        j                  j                  dd      }t        j                  j                  d	d
      }t        j                  j                  dd
      }t        j                  j                  dd
      }|dkD  r|}n| dz
  |z  }ddlm}m}	 ddlm}
 ddl	m
} |d   } ||      } |
dd|      } |       }|j                   |	d            j                         }|r|d   nd}g }d}d}|rd}g }i }|r|j                  d       d| d|d	<   |r|j                  d       d| d|d<   |r|j                  d       d| d|d<   |dk(  r|j                  d       n|dk(  r|j                  d       |r|ddj                  |      z   z  }|dk(  r|dz  }n|dk(  r|dz  }n|d z  }d!| d"}d!| d"}|j                   |	|      |      j                         }|r|d   nd}|d#| d$| z   }|j                   |	|      |      j                         }||z   |k  }|D ]p  }g }|j                   r|j                  |j                          |j"                  r|j                  |j"                         |rd%j                  |      n|j$                  xs d
}i d&t'        |j(                        d't'        |j(                        d(|j*                  xs d)d*|j*                  xs d)d+|j,                  xs d,d-|d.|j.                  xs d
d|j"                  xs d
d|j                   xs d
d/|j$                  xs d
d0|j0                  xs dd1|j2                  xs dd2|j4                  xs dd3|j6                  xs d4d5|j6                  xs d4d6|j8                  rt;        |j8                        ndd7d8ddd|j<                  xs d
|j>                  xs d
|j@                  xs d
|jB                  rt;        |jB                        nd9|jD                  rt;        |jD                        nd9|jF                  r|jF                  jI                         nd9|jJ                  r|jJ                  jI                         nd9d:
}|j                  |       s |jM                          tO        d;||| |||d<d=      S # tP        $ r;} tR        jT                  jW                  d>|         tO        d;g dd?      cY d9} ~ S d9} ~ ww xY w)@r   r   r   r   r   r   r   r   r   r   r   r   r   r   r
   r   r   Fr   r   aw  
                SELECT a.adventure_id, a.adventure_name, a.description, a.address, a.city,
                       a.duration_minutes, a.max_participants, a.min_participants, a.price,
                       c.company_name, co.country_name, a.latitude, a.longitude,
                       a.phone, a.email, a.website, a.created_at, a.updated_at,
                       ap.photo_path
                FROM adventures a
                LEFT JOIN companies c ON a.company_id = c.company_id
                LEFT JOIN countries co ON a.country_id = co.country_id
                LEFT JOIN (
                    SELECT DISTINCT ON (adventure_id) 
                           adventure_id, photo_path
                    FROM adventure_photos 
                    ORDER BY adventure_id, created_at ASC
                ) ap ON a.adventure_id = ap.adventure_id
                WHERE 1=1
            r   r#   r$   r   r%   z#a.price IS NOT NULL AND a.price > 0r'   r   r*   z( ORDER BY a.price DESC, a.adventure_namer   z ORDER BY a.adventure_namer+   r,   r-   r.   r9   r;   r   r   r   r<   r>   zExciting adventure experiencer?   r@   rh   duration_minutesmax_participantsmin_participantsrm   z/static/img/logo.pngrC   rG   rH   r4   N)
rJ   rp   
view_countr  r  r  latitude	longituderL   rM   T)r   r   r   rP   )r   r   rO   rT   r   r   ),rX   r   rY   rZ   r[   r   r	   r\   r   r]   r   r^   r_   r`   ra   rb   r   rg   rh   ri   r   r   r>   rl   r*  r+  r,  rm   rG   rn   r  r  r  r.  r/  rL   rq   rM   rr   r   rs   r   rt   rV   )!r   r   r   ru   r   r   r   rv   r   r	   r   r   r   rw   rx   ry   r   r   r   rO   r}   r~   r   r   r   r   r   r   r   r   r?   r   r   s!                                    r   get_adventures_grid_apir0    s   hO7<<##FA./W\\%%h23w||''
B78ll&&x7!!(B/,,""9b1||+ A:"M!AX1M2/ 	(#N3|,#uFS^ ::d , '  hj 	 4D,Q/J& JF!!  #\  ]%&vha=x !!"BC&'yNy!!!"67#$TF!v i'!!"GH(!!"UV gZ(@@@
 i'HH
(MM
::
 3:,>NOK2:,>NOK::d;&7@IIKL-9,q/qK )WXJh}o+VVO D$96BKKMJ &0K?H ( %7	!#>>")))..9))")))*@*@A8F499^4YM^M^Mdbd"#i445""C	(>(>$?" %i&>&>&M+" I44C	"
 "9#8#8#[<[" " y55;" y55;" INN0b" y006B" '	(B(B(Ga" '	(B(B(Ga" '	(B(B(Ga" !)"6"6"P:P" Y11K5K"  yU9??3A!"" #"$  $%"#&__2&__2(006B=F=O=Oi&8&8 9UY?H?R?Ry':':!;X\FOFZFZ)"6"6"@"@"B`dFOFZFZ)"6"6"@"@"B`d7":  &&~6K%7N 	
) $'$		

 
 
	  O  #B1#!FG4ruMNNOs   T>U 	V
0V :V Vz/beach-schedules/<beach_id>GET)methodsc                    	 ddl m}m} ddlm} ddlm} |d   } ||      } |dd|      } |       }|j                   |d      d	| i      j                         }	g }
|	D ]  }|
j                  |j                  |j                  j                         |j                  j                         |j                  j                  d
      |j                   j                  d
      |j"                  |j$                  |j&                  |j(                  t+        |j,                        t+        |j.                        |j0                  xs d|j2                  |j4                  d        |j7                          t9        |
      S # t:        $ r;}t<        j>                  jA                  d|        t9        ddi      dfcY d}~S d}~ww xY w)z#Get all schedules for a beach placer   r   r
   r   r   Fr   z
            SELECT s.*, c.currency_symbol 
            FROM beach_places_schedules s
            LEFT JOIN currencies c ON s.currency_id = c.currency_id
            WHERE s.beach_place_id = :beach_id
            ORDER BY s.from_date DESC
        r   z%H:%Mr5   )schedule_id	from_dateto_date	from_timeto_timevalid_dates	min_hours
can_refundrefund_before_hoursrG   	price_vipr   extras
extras_vipzError getting beach schedules: rV   zFailed to load schedulesrW   N)!r[   r   r	   r\   r   r]   r   r^   rb   r`   r4  r5  rq   r6  r7  strftimer8  r9  r:  r;  r<  rn   rG   r=  r   r>  r?  rr   r   rs   r   rt   rV   )r   r   r	   r   r   r   rw   rx   ry   	schedulesschedules_datascheduler   s                r   get_beach_schedulesrD  u  s   -C2/' $N3|,#uFS^JJt %   8$& '/hj 	 ! 	H!!'33%//99;#++557%//88A#++44W='33%//&11'/'C'Cx~~."8#5#56#+#;#;#Du"//&11# 	$ 	
~&& C  #B1#!FG!;<=sBBCs   E;E> >	G0F=7G=GPOSTc                    	 ddl m}m} ddlm} ddlm} |d   } ||      } |dd|      }t        j                         } |       }	|j                  dd	      }
|	j                   |d
      | |d   |d   |d   |d   |d   |d   |d   |d   |
|d   |d   |j                  dd      |j                  dd      d      }|j                         }|r|d   nd}|	j                          |	j                          t        d|dd      S # t        $ r;}t         j"                  j%                  d|        t        ddi      d fcY d}~S d}~ww xY w)!z$Add a new schedule for a beach placer   r   r
   r   r   Fr   currency_idr   a#  
            INSERT INTO beach_places_schedules (
                beach_place_id, from_date, to_date, from_time, to_time,
                valid_dates, min_hours, can_refund, refund_before_hours,
                currency_id, price, price_vip, extras, extras_vip
            ) VALUES (
                :beach_id, :from_date, :to_date, :from_time, :to_time,
                :valid_dates, :min_hours, :can_refund, :refund_before_hours,
                :currency_id, :price, :price_vip, :extras, :extras_vip
            ) RETURNING schedule_id
        r5  r6  r7  r8  r9  r:  r;  r<  rG   r=  r>  r   r?  )r   r5  r6  r7  r8  r9  r:  r;  r<  rG  rG   r=  r>  r?  NTzSchedule created successfully)r   r4  r  zError adding beach schedule: rV   zFailed to create schedulerW   )r[   r   r	   r\   r   r]   r   r   get_jsonrZ   r^   r_   commitrr   r   rs   r   rt   rV   )r   r   r	   r   r   r   rw   rx   rS   ry   rG  result
result_rowr4  r   s                  r   add_beach_schedulerL    s}   :D2/' $N3|,#uFS!^ hh}a0 D 
" 
 !k*Ik*I.k*|,#'(=#>&']k*hhx,((<4
6 __&
'1jmt
		

&6
  	  D  #@!DE!<=>CCDs   DD 	E0EEEz"/beach-schedules/<int:schedule_id>PUTc                    	 ddl m}m} ddlm} ddlm} |d   } ||      } |dd|      }t        j                         } |       }	|	j                   |d      d	| i      j                         }
|
s|	j                          t        d
di      dfS |j                  dd      }|	j                   |d      | |d   |d   |d   |d   |d   |d   |d   |d   ||d   |d   |j                  dd      |j                  dd      d       |	j                          |	j                          t        ddd       S # t        $ r;}t         j"                  j%                  d!|        t        d
d"i      d#fcY d$}~S d$}~ww xY w)%z!Update an existing beach scheduler   r   r
   r   r   Fr   r
            SELECT schedule_id FROM beach_places_schedules 
            WHERE schedule_id = :schedule_id
        r4  rV   Schedule not found  rG  r   au  
            UPDATE beach_places_schedules SET
                from_date = :from_date,
                to_date = :to_date,
                from_time = :from_time,
                to_time = :to_time,
                valid_dates = :valid_dates,
                min_hours = :min_hours,
                can_refund = :can_refund,
                refund_before_hours = :refund_before_hours,
                currency_id = :currency_id,
                price = :price,
                price_vip = :price_vip,
                extras = :extras,
                extras_vip = :extras_vip
            WHERE schedule_id = :schedule_id
        r5  r6  r7  r8  r9  r:  r;  r<  rG   r=  r>  r   r?  )r4  r5  r6  r7  r8  r9  r:  r;  r<  rG  rG   r=  r>  r?  TzSchedule updated successfullyr  zError updating beach schedule: zFailed to update schedulerW   N)r[   r   r	   r\   r   r]   r   r   rH  r^   r_   rr   r   rZ   rI  rs   r   rt   rV   )r4  r   r	   r   r   r   rw   rx   rS   ry   existingrG  r   s                r   update_beach_schedulerS    s   GD2/' $N3|,#uFS!^ ::d $  k*, -5HJ 	
 HHJG%9:;S@@ hh}a0 	

4  " 'k*Ik*I.k*|,#'(=#>&']k*hhx,((<4
!	B 			

6
  	
  D  #B1#!FG!<=>CCDs%   BD7 B$D7 7	E; 0E60E;6E;z)/beach-schedules/delete/<int:schedule_id>DELETEc                    	 ddl m}m} ddlm} ddlm} |d   } ||      } |dd|      } |       }|j                   |d      d	| i      j                         }	|	s|j                          t        d
di      dfS |j                   |d      d	| i       |j                          |j                          t        ddd      S # t        $ r;}
t        j                  j                  d|
        t        d
di      dfcY d}
~
S d}
~
ww xY w)zDelete a beach scheduler   r   r
   r   r   Fr   rO  r4  rV   rP  rQ  zf
            DELETE FROM beach_places_schedules 
            WHERE schedule_id = :schedule_id
        TzSchedule deleted successfullyr  zError deleting beach schedule: zFailed to delete schedulerW   N)r[   r   r	   r\   r   r]   r   r^   r_   rr   r   rI  rs   r   rt   rV   )r4  r   r	   r   r   r   rw   rx   ry   rJ  r   s              r   delete_beach_schedulerV  5  s(   &D2/' $N3|,#uFS^ D "  k*, -5HJ 	
 HHJG%9:;S@@ 	

4   k*	,
 			

6
  	
  D  #B1#!FG!<=>CCDs%   A;C >AC 	D
0D?D
D
)__doc__flaskr   r   r   r   r[   r   r	   r\   r   os__name__api_bprouter   r   r   r   r   r   r   r   r  r(  r0  rD  rL  rS  rV       r   <module>r_     s5   ; : * ' 	 
5(v	6oO/ O/f  !l0 "l0\  !j9 "j9X #${< %{<z $%|O &|O| os/ s/j ()c< *c<J  !|/ "|/| $%J4 &J4X #$b: %b:H  !jO "jOZ +eW=/C >/Cd +fX><D ?<D~ 2UGDID EIDX 9H:N(D O(Dr^  