import duckdb
from fastapi import FastAPI, Request, Response
from fastapi.responses import HTMLResponse
from fastapi.staticfiles import StaticFiles
from fastapi.templating import Jinja2Templates
from pathlib import Path

app = FastAPI()

# Mount the static directory
app.mount("/static", StaticFiles(directory="static"), name="static")

# Set up templates
templates = Jinja2Templates(directory="templates")

def get_db_connection():
    return duckdb.connect("travel.db", read_only=True)

ISO3_TO_ISO2 = {
    'AFG': 'af', 'ALB': 'al', 'DZA': 'dz', 'AND': 'ad', 'AGO': 'ao', 'ATG': 'ag', 'ARG': 'ar', 'ARM': 'am', 'AUS': 'au',
    'AUT': 'at', 'AZE': 'az', 'BHS': 'bs', 'BHR': 'bh', 'BGD': 'bd', 'BRB': 'bb', 'BLR': 'by', 'BEL': 'be', 'BLZ': 'bz',
    'BEN': 'bj', 'BTN': 'bt', 'BOL': 'bo', 'BIH': 'ba', 'BWA': 'bw', 'BRA': 'br', 'BRN': 'bn', 'BGR': 'bg', 'BFA': 'bf',
    'BDI': 'bi', 'CPV': 'cv', 'KHM': 'kh', 'CMR': 'cm', 'CAN': 'ca', 'CAF': 'cf', 'TCD': 'td', 'CHL': 'cl', 'CHN': 'cn',
    'COL': 'co', 'COM': 'km', 'COG': 'cg', 'COD': 'cd', 'CRI': 'cr', 'CIV': 'ci', 'HRV': 'hr', 'CUB': 'cu', 'CYP': 'cy',
    'CZE': 'cz', 'DNK': 'dk', 'DJI': 'dj', 'DMA': 'dm', 'DOM': 'do', 'ECU': 'ec', 'EGY': 'eg', 'SLV': 'sv', 'GNQ': 'gq',
    'ERI': 'er', 'EST': 'ee', 'SWZ': 'sz', 'ETH': 'et', 'FJI': 'fj', 'FIN': 'fi', 'FRA': 'fr', 'GAB': 'ga', 'GMB': 'gm',
    'GEO': 'ge', 'DEU': 'de', 'GHA': 'gh', 'GRC': 'gr', 'GRD': 'gd', 'GTM': 'gt', 'GIN': 'gn', 'GNB': 'gw', 'GUY': 'gy',
    'HTI': 'ht', 'HND': 'hn', 'HUN': 'hu', 'ISL': 'is', 'IND': 'in', 'IDN': 'id', 'IRN': 'ir', 'IRQ': 'iq', 'IRL': 'ie',
    'ISR': 'il', 'ITA': 'it', 'JAM': 'jm', 'JPN': 'jp', 'JOR': 'jo', 'KAZ': 'kz', 'KEN': 'ke', 'KIR': 'ki', 'KOR': 'kr',
    'KWT': 'kw', 'KGZ': 'kg', 'LAO': 'la', 'LVA': 'lv', 'LBN': 'lb', 'LSO': 'ls', 'LBR': 'lr', 'LBY': 'ly', 'LIE': 'li',
    'LTU': 'lt', 'LUX': 'lu', 'MDG': 'mg', 'MWI': 'mw', 'MYS': 'my', 'MDV': 'mv', 'MLI': 'ml', 'MLT': 'mt', 'MHL': 'mh',
    'MRT': 'mr', 'MUS': 'mu', 'MEX': 'mx', 'FSM': 'fm', 'MDA': 'md', 'MCO': 'mc', 'MNG': 'mn', 'MNE': 'me', 'MAR': 'ma',
    'MOZ': 'mz', 'MMR': 'mm', 'NAM': 'na', 'NRU': 'nr', 'NPL': 'np', 'NLD': 'nl', 'NZL': 'nz', 'NIC': 'ni', 'NER': 'ne',
    'NGA': 'ng', 'MKD': 'mk', 'NOR': 'no', 'OMN': 'om', 'PAK': 'pk', 'PLW': 'pw', 'PAN': 'pa', 'PNG': 'pg', 'PRY': 'py',
    'PER': 'pe', 'PHL': 'ph', 'POL': 'pl', 'PRT': 'pt', 'QAT': 'qa', 'ROU': 'ro', 'RUS': 'ru', 'RWA': 'rw', 'KNA': 'kn',
    'LCA': 'lc', 'VCG': 'vc', 'WSM': 'ws', 'SMR': 'sm', 'STP': 'st', 'SAU': 'sa', 'SEN': 'sn', 'SRB': 'rs', 'SYC': 'sc',
    'SLE': 'sl', 'SGP': 'sg', 'SVK': 'sk', 'SVN': 'si', 'SLB': 'sb', 'SOM': 'so', 'ZAF': 'za', 'SSD': 'ss', 'ESP': 'es',
    'LKA': 'lk', 'SDN': 'sd', 'SUR': 'sr', 'SWE': 'se', 'CHE': 'ch', 'SYR': 'sy', 'TJK': 'tj', 'TZA': 'tz', 'THA': 'th',
    'TLS': 'tl', 'TGO': 'tg', 'TON': 'to', 'TTO': 'tt', 'TUN': 'tn', 'TUR': 'tr', 'TKM': 'tm', 'TUV': 'tv', 'UGA': 'ug',
    'UKR': 'ua', 'ARE': 'ae', 'GBR': 'gb', 'USA': 'us', 'URY': 'uy', 'UZB': 'uz', 'VUT': 'vu', 'VAT': 'va', 'VEN': 've',
    'VNM': 'vn', 'YEM': 'ye', 'ZMB': 'zm', 'ZWE': 'zw'
}

@app.get("/api/search")
async def search_attractions(q: str = ""):
    con = get_db_connection()
    try:
        query = """
            SELECT 
                a.name, 
                a.description, 
                a.image_filename, 
                c.name as country_name,
                a.category,
                c.iso_code,
                a.latitude,
                a.longitude,
                a.id,
                a.slug
            FROM attractions a
            LEFT JOIN countries c ON a.country_iso = c.iso_code
            WHERE a.name ILIKE ? OR c.name ILIKE ?
            ORDER BY a.name
            LIMIT 20
        """
        search_pattern = f"%{q}%"
        results = con.execute(query, [search_pattern, search_pattern]).fetchall()
        
        attractions = []
        for r in results:
            attr_id = r[8]
            nearby = con.execute("SELECT name, type, link FROM nearby_places WHERE attraction_id = ?", [attr_id]).fetchall()
            restaurants = [{"name": n[0], "link": n[2]} for n in nearby if n[1] == 'restaurant']
            hotels = [{"name": n[0], "link": n[2]} for n in nearby if n[1] == 'hotel']
            
            stories = con.execute("SELECT story FROM attraction_stories WHERE attraction_id = ?", [attr_id]).fetchall()
            stories = [s[0] for s in stories]
            
            attractions.append({
                "name": r[0],
                "description": r[1],
                "image_filename": r[2],
                "country_name": r[3],
                "category": r[4],
                "iso_code": ISO3_TO_ISO2.get(r[5].upper(), r[5].lower()) if r[5] else None,
                "lat": r[6],
                "lon": r[7],
                "restaurants": restaurants,
                "hotels": hotels,
                "stories": stories,
                "slug": r[9]
            })
        return {"attractions": attractions}
    finally:
        con.close()

@app.get("/api/discover")
async def discover_attractions(limit: int = 8, exclude: str = ""):
    con = get_db_connection()
    try:
        exclude_ids = [int(i) for i in exclude.split(",") if i.strip().isdigit()]
        
        where_clause = ""
        params = []
        if exclude_ids:
            placeholders = ",".join(["?"] * len(exclude_ids))
            where_clause = f"WHERE a.id NOT IN ({placeholders})"
            params = exclude_ids
        
        params.append(limit)

        query = f"""
            SELECT 
                a.name, 
                a.description, 
                a.image_filename, 
                c.name as country_name,
                a.category,
                c.iso_code,
                a.latitude,
                a.longitude,
                a.id,
                a.slug
            FROM attractions a
            LEFT JOIN countries c ON a.country_iso = c.iso_code
            {where_clause}
            ORDER BY RANDOM()
            LIMIT ?
        """
        results = con.execute(query, params).fetchall()
        
        attractions = []
        for r in results:
            attr_id = r[8]
            nearby = con.execute("SELECT name, type, link FROM nearby_places WHERE attraction_id = ?", [attr_id]).fetchall()
            restaurants = [{"name": n[0], "link": n[2]} for n in nearby if n[1] == 'restaurant']
            hotels = [{"name": n[0], "link": n[2]} for n in nearby if n[1] == 'hotel']
            
            stories = con.execute("SELECT story FROM attraction_stories WHERE attraction_id = ?", [attr_id]).fetchall()
            stories = [s[0] for s in stories]
            
            attractions.append({
                "name": r[0],
                "description": r[1],
                "image_filename": r[2],
                "country_name": r[3],
                "category": r[4],
                "iso_code": ISO3_TO_ISO2.get(r[5].upper(), r[5].lower()) if r[5] else None,
                "lat": r[6],
                "lon": r[7],
                "restaurants": restaurants,
                "hotels": hotels,
                "stories": stories,
                "slug": r[9]
            })
        return {"attractions": attractions}
    finally:
        con.close()

@app.get("/explore/{slug}", response_class=HTMLResponse)
async def explore_attraction(request: Request, slug: str):
    con = get_db_connection()
    try:
        query = """
            SELECT 
                a.name, a.description, a.image_filename, c.name as country_name,
                a.category, c.iso_code, a.latitude, a.longitude, a.id, a.slug
            FROM attractions a
            LEFT JOIN countries c ON a.country_iso = c.iso_code
            WHERE a.slug = ?
        """
        r = con.execute(query, [slug]).fetchone()
        
        if not r:
            return HTMLResponse(content="Attraction not found", status_code=404)
        
        attr_id = r[8]
        nearby = con.execute("SELECT name, type, link FROM nearby_places WHERE attraction_id = ?", [attr_id]).fetchall()
        restaurants = [{"name": n[0], "link": n[2]} for n in nearby if n[1] == 'restaurant']
        hotels = [{"name": n[0], "link": n[2]} for n in nearby if n[1] == 'hotel']
        
        stories = con.execute("SELECT story FROM attraction_stories WHERE attraction_id = ?", [attr_id]).fetchall()
        stories = [s[0] for s in stories]
        
        attraction = {
            "name": r[0],
            "description": r[1].capitalize() if r[1] else "A beautiful and historic destination worth exploring.",
            "image_filename": r[2],
            "country_name": r[3],
            "category": r[4],
            "iso_code": ISO3_TO_ISO2.get(r[5].upper(), r[5].lower()) if r[5] else None,
            "lat": r[6],
            "lon": r[7],
            "restaurants": restaurants,
            "hotels": hotels,
            "stories": stories,
            "slug": r[9]
        }
        
        return templates.TemplateResponse(
            request=request,
            name="attraction.html",
            context={"attraction": attraction}
        )
    finally:
        con.close()

@app.get("/sitemap.xml")
async def sitemap(request: Request):
    con = get_db_connection()
    try:
        query = "SELECT slug FROM attractions"
        slugs = con.execute(query).fetchall()
        
        base_url = "https://bostonbridge.io"
        
        # Build the XML
        xml_content = f'<?xml version="1.0" encoding="UTF-8"?>\n'
        xml_content += '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n'
        
        # Add homepage
        xml_content += f'  <url>\n    <loc>{base_url}/</loc>\n    <changefreq>daily</changefreq>\n    <priority>1.0</priority>\n  </url>\n'
        
        # Add all attractions
        for slug_tuple in slugs:
            slug = slug_tuple[0]
            xml_content += f'  <url>\n    <loc>{base_url}/explore/{slug}</loc>\n    <changefreq>weekly</changefreq>\n    <priority>0.8</priority>\n  </url>\n'
            
        xml_content += '</urlset>'
        
        return Response(content=xml_content, media_type="application/xml")
    finally:
        con.close()

@app.get("/versions", response_class=HTMLResponse)
async def versions(request: Request):
    return templates.TemplateResponse(
        request=request,
        name="versions.html",
        context={}
    )

@app.get("/", response_class=HTMLResponse)
async def read_root(request: Request):
    # Get 8 random attractions for the initial "Discovery" view
    initial_data = await discover_attractions(limit=8)
    return templates.TemplateResponse(
        request=request,
        name="index.html",
        context={"initial_attractions": initial_data["attractions"]}
    )

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app, host="0.0.0.0", port=18994)