from flask import Flask, render_template, request, jsonify, send_from_directory
import os, re, json, sqlite3, logging

app = Flask(__name__)

# ────────────────────────── 1. CONFIGURATION ──────────────────────────
# Use absolute paths in the persistent directory for Hugging Face
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_FILE = os.path.join(BASE_DIR, "favorite_sites.json")  # JSON file for backward compatibility
SQLITE_DB = os.path.join(BASE_DIR, "favorite_sites.db")  # SQLite database for persistence
TEMPLATE_DIR = os.path.join(BASE_DIR, "templates")

# Setup logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# Domains that commonly block iframes
BLOCKED_DOMAINS = [
    "naver.com", "daum.net", "google.com",
    "facebook.com", "instagram.com", "kakao.com",
    "ycombinator.com"
]

# ────────────────────────── 2. CURATED CATEGORIES ──────────────────────────
CATEGORIES = {
    "Free AI: Productivity": [
        "https://huggingface.co/spaces/ginigen/perflexity-clone",
        "https://huggingface.co/spaces/ginipick/IDEA-DESIGN",
        "https://huggingface.co/spaces/VIDraft/mouse-webgen",
        "https://huggingface.co/spaces/openfree/Vibe-Game",
        "https://huggingface.co/spaces/openfree/Game-Gallery",
        "https://huggingface.co/spaces/aiqtech/Contributors-Leaderboard",
        "https://huggingface.co/spaces/fantaxy/Model-Leaderboard",
        "https://huggingface.co/spaces/fantaxy/Space-Leaderboard",
        "https://huggingface.co/spaces/openfree/Korean-Leaderboard",
    ],
    "Free AI: Multimodal": [
        "https://huggingface.co/spaces/openfree/DreamO-video",
        "https://huggingface.co/spaces/Heartsync/NSFW-Uncensored-photo",
        "https://huggingface.co/spaces/Heartsync/NSFW-Uncensored",
        "https://huggingface.co/spaces/fantaxy/Sound-AI-SFX",
        "https://huggingface.co/spaces/ginigen/SFX-Sound-magic",
        "https://huggingface.co/spaces/ginigen/VoiceClone-TTS",
        "https://huggingface.co/spaces/aiqcamp/MCP-kokoro",
        "https://huggingface.co/spaces/aiqcamp/ENGLISH-Speaking-Scoring",
    ],
    "Free AI: Professional": [
        "https://huggingface.co/spaces/ginigen/blogger",
        "https://huggingface.co/spaces/VIDraft/money-radar",
        "https://huggingface.co/spaces/immunobiotech/drug-discovery",
        "https://huggingface.co/spaces/immunobiotech/Gemini-MICHELIN",
        "https://huggingface.co/spaces/Heartsync/Papers-Leaderboard",
        "https://huggingface.co/spaces/VIDraft/PapersImpact",
        "https://huggingface.co/spaces/ginipick/AgentX-Papers",
        "https://huggingface.co/spaces/openfree/Cycle-Navigator",
    ],
    "Free AI: Image": [
        "https://huggingface.co/spaces/ginigen/interior-design",
        "https://huggingface.co/spaces/ginigen/Workflow-Canvas",
        "https://huggingface.co/spaces/ginigen/Multi-LoRAgen",
        "https://huggingface.co/spaces/ginigen/Every-Text",
        "https://huggingface.co/spaces/ginigen/text3d-r1",
        "https://huggingface.co/spaces/ginipick/FLUXllama",
        "https://huggingface.co/spaces/Heartsync/FLUX-Vision",
        "https://huggingface.co/spaces/ginigen/VisualCloze",
        "https://huggingface.co/spaces/seawolf2357/Ghibli-Multilingual-Text-rendering",
        "https://huggingface.co/spaces/ginigen/Ghibli-Meme-Studio",
        "https://huggingface.co/spaces/VIDraft/Open-Meme-Studio",
        "https://huggingface.co/spaces/ginigen/3D-LLAMA",
    ],
    "Free AI: LLM / VLM": [
        "https://huggingface.co/spaces/VIDraft/Gemma-3-R1984-4B",
        "https://huggingface.co/spaces/VIDraft/Gemma-3-R1984-12B",
        "https://huggingface.co/spaces/ginigen/Mistral-Perflexity",
        "https://huggingface.co/spaces/aiqcamp/gemini-2.5-flash-preview",
        "https://huggingface.co/spaces/openfree/qwen3-30b-a3b-research",
        "https://huggingface.co/spaces/openfree/qwen3-235b-a22b-research",
        "https://huggingface.co/spaces/openfree/Llama-4-Maverick-17B-Research",
    ],
}

# ────────────────────────── 3. DATABASE FUNCTIONS ──────────────────────────
def init_db():
    """Initialize both JSON and SQLite databases"""
    # Log database paths for debugging
    logger.info(f"JSON DB path: {DB_FILE}")
    logger.info(f"SQLite DB path: {SQLITE_DB}")
    
    # Initialize JSON file if it doesn't exist
    if not os.path.exists(DB_FILE):
        try:
            with open(DB_FILE, "w", encoding="utf-8") as f:
                json.dump([], f, ensure_ascii=False)
            logger.info("Created new JSON database file")
        except Exception as e:
            logger.error(f"Error creating JSON file: {e}")
    
    # Initialize SQLite database
    try:
        conn = sqlite3.connect(SQLITE_DB)
        cursor = conn.cursor()
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS urls (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            url TEXT UNIQUE NOT NULL,
            date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        conn.commit()
        logger.info("SQLite database initialized successfully")
        
        # If we have data in JSON but not in SQLite (first run with new SQLite DB),
        # migrate the data from JSON to SQLite
        json_urls = load_json()
        if json_urls:
            logger.info(f"Found {len(json_urls)} URLs in JSON file")
            db_urls = load_db_sqlite()
            new_urls = 0
            for url in json_urls:
                if url not in db_urls:
                    add_url_to_sqlite(url)
                    new_urls += 1
            logger.info(f"Migrated {new_urls} new URLs from JSON to SQLite")
        
        conn.close()
    except Exception as e:
        logger.error(f"Error initializing SQLite database: {e}")
        
    # Create default URLs file if none exists
    if not load_db():
        default_urls = [
            "https://huggingface.co/spaces/ginigen/perflexity-clone",
            "https://huggingface.co/spaces/openfree/Game-Gallery",
            "https://www.google.com"
        ]
        for url in default_urls:
            add_url_to_sqlite(url)
        save_json(default_urls)
        logger.info("Added default URLs to empty database")

def load_json():
    """Load URLs from JSON file (for backward compatibility)"""
    try:
        if os.path.exists(DB_FILE):
            with open(DB_FILE, "r", encoding="utf-8") as f:
                raw = json.load(f)
            return raw if isinstance(raw, list) else []
        return []
    except Exception as e:
        logger.error(f"Error loading JSON file: {e}")
        return []

def save_json(lst):
    """Save URLs to JSON file (for backward compatibility)"""
    try:
        with open(DB_FILE, "w", encoding="utf-8") as f:
            json.dump(lst, f, ensure_ascii=False, indent=2)
        logger.info(f"Saved {len(lst)} URLs to JSON file")
        return True
    except Exception as e:
        logger.error(f"Error saving to JSON file: {e}")
        return False

def load_db_sqlite():
    """Load URLs from SQLite database"""
    try:
        conn = sqlite3.connect(SQLITE_DB)
        cursor = conn.cursor()
        cursor.execute("SELECT url FROM urls ORDER BY date_added DESC")
        urls = [row[0] for row in cursor.fetchall()]
        conn.close()
        logger.info(f"Loaded {len(urls)} URLs from SQLite database")
        return urls
    except Exception as e:
        logger.error(f"Error loading from SQLite database: {e}")
        return []

def add_url_to_sqlite(url):
    """Add a URL to SQLite database"""
    try:
        conn = sqlite3.connect(SQLITE_DB)
        cursor = conn.cursor()
        cursor.execute("INSERT INTO urls (url) VALUES (?)", (url,))
        conn.commit()
        conn.close()
        logger.info(f"Added URL to SQLite: {url}")
        return True
    except sqlite3.IntegrityError:
        # URL already exists
        logger.info(f"URL already exists in SQLite: {url}")
        return False
    except Exception as e:
        logger.error(f"Error adding URL to SQLite: {e}")
        return False

def update_url_in_sqlite(old_url, new_url):
    """Update a URL in SQLite database"""
    try:
        conn = sqlite3.connect(SQLITE_DB)
        cursor = conn.cursor()
        cursor.execute("UPDATE urls SET url = ? WHERE url = ?", (new_url, old_url))
        if cursor.rowcount > 0:
            conn.commit()
            logger.info(f"Updated URL in SQLite: {old_url} -> {new_url}")
            success = True
        else:
            logger.info(f"URL not found for update in SQLite: {old_url}")
            success = False
        conn.close()
        return success
    except sqlite3.IntegrityError:
        # New URL already exists
        logger.error(f"New URL already exists in SQLite: {new_url}")
        return False
    except Exception as e:
        logger.error(f"Error updating URL in SQLite: {e}")
        return False

def delete_url_from_sqlite(url):
    """Delete a URL from SQLite database"""
    try:
        conn = sqlite3.connect(SQLITE_DB)
        cursor = conn.cursor()
        cursor.execute("DELETE FROM urls WHERE url = ?", (url,))
        if cursor.rowcount > 0:
            conn.commit()
            logger.info(f"Deleted URL from SQLite: {url}")
            success = True
        else:
            logger.info(f"URL not found for deletion in SQLite: {url}")
            success = False
        conn.close()
        return success
    except Exception as e:
        logger.error(f"Error deleting URL from SQLite: {e}")
        return False

def load_db():
    """Primary function to load URLs - prioritizes SQLite DB but falls back to JSON"""
    sqlite_urls = load_db_sqlite()
    
    # If SQLite DB is empty, try loading from JSON
    if not sqlite_urls:
        logger.info("SQLite database empty, trying JSON file")
        json_urls = load_json()
        
        # If we found URLs in JSON, migrate them to SQLite
        if json_urls:
            logger.info(f"Migrating {len(json_urls)} URLs from JSON to SQLite")
            for url in json_urls:
                add_url_to_sqlite(url)
            return json_urls
    
    return sqlite_urls

def save_db(lst):
    """Save URLs to both SQLite and JSON"""
    logger.info(f"Saving {len(lst)} URLs to database")
    
    # Clear all URLs from SQLite and add the new list
    try:
        conn = sqlite3.connect(SQLITE_DB)
        cursor = conn.cursor()
        cursor.execute("DELETE FROM urls")
        for url in lst:
            cursor.execute("INSERT INTO urls (url) VALUES (?)", (url,))
        conn.commit()
        conn.close()
        logger.info("Successfully saved to SQLite database")
    except Exception as e:
        logger.error(f"Error saving to SQLite database: {e}")
    
    # Also save to JSON for backward compatibility
    return save_json(lst)

# ────────────────────────── 4. URL HELPERS ──────────────────────────
def direct_url(hf_url):
    m = re.match(r"https?://huggingface\.co/spaces/([^/]+)/([^/?#]+)", hf_url)
    if not m:
        return hf_url
    owner, name = m.groups()
    owner = owner.lower()
    name = name.replace('.', '-').replace('_', '-').lower()
    return f"https://{owner}-{name}.hf.space"

def screenshot_url(url):
    return f"https://image.thum.io/get/fullpage/{url}"

def process_url_for_preview(url):
    """Returns (preview_url, mode)"""
    # Handle blocked domains first
    if any(d for d in BLOCKED_DOMAINS if d in url):
        return screenshot_url(url), "snapshot"
    
    # Special case handling for problematic URLs
    if "vibe-coding-tetris" in url or "World-of-Tank-GAME" in url or "Minesweeper-Game" in url:
        return screenshot_url(url), "snapshot"
    
    # General HF space handling
    try:
        if "huggingface.co/spaces" in url:
            parts = url.rstrip("/").split("/")
            if len(parts) >= 5:
                owner = parts[-2]
                name = parts[-1]
                embed_url = f"https://huggingface.co/spaces/{owner}/{name}/embed"
                return embed_url, "iframe"
    except Exception:
        return screenshot_url(url), "snapshot"
    
    # Default handling
    return url, "iframe"

# ────────────────────────── 5. API ROUTES ──────────────────────────
@app.route('/api/category')
def api_category():
    cat = request.args.get('name', '')
    urls = CATEGORIES.get(cat, [])
    return jsonify([
        {
            "title": url.split('/')[-1],
            "owner": url.split('/')[-2] if '/spaces/' in url else '',
            "iframe": direct_url(url),
            "shot": screenshot_url(url),
            "hf": url
        } for url in urls
    ])

@app.route('/api/favorites')
def api_favorites():
    # Load URLs from SQLite database
    urls = load_db()
    
    page = int(request.args.get('page', 1))
    per_page = int(request.args.get('per_page', 9))
    
    total_pages = max(1, (len(urls) + per_page - 1) // per_page)
    start = (page - 1) * per_page
    end = min(start + per_page, len(urls))
    
    urls_page = urls[start:end]
    
    result = []
    for url in urls_page:
        try:
            preview_url, mode = process_url_for_preview(url)
            result.append({
                "title": url.split('/')[-1],
                "url": url,
                "preview_url": preview_url,
                "mode": mode
            })
        except Exception:
            # Fallback to screenshot mode
            result.append({
                "title": url.split('/')[-1],
                "url": url,
                "preview_url": screenshot_url(url),
                "mode": "snapshot"
            })
    
    return jsonify({
        "items": result,
        "page": page,
        "total_pages": total_pages
    })

@app.route('/api/url/add', methods=['POST'])
def add_url():
    url = request.form.get('url', '').strip()
    if not url:
        return jsonify({"success": False, "message": "URL is required"})
    
    # Check if URL already exists in database
    if not add_url_to_sqlite(url):
        return jsonify({"success": False, "message": "URL already exists"})
    
    # Also update JSON file for backward compatibility
    data = load_json()
    if url not in data:
        data.insert(0, url)
        save_json(data)
    
    return jsonify({"success": True, "message": "URL added successfully"})

@app.route('/api/url/update', methods=['POST'])
def update_url():
    old = request.form.get('old', '')
    new = request.form.get('new', '').strip()
    
    if not new:
        return jsonify({"success": False, "message": "New URL is required"})
    
    # Update in SQLite DB
    if not update_url_in_sqlite(old, new):
        return jsonify({"success": False, "message": "URL not found or new URL already exists"})
    
    # Also update JSON file for backward compatibility
    data = load_json()
    try:
        idx = data.index(old)
        data[idx] = new
        save_json(data)
    except ValueError:
        # If URL not in JSON, add it
        data.append(new)
        save_json(data)
    
    return jsonify({"success": True, "message": "URL updated successfully"})

@app.route('/api/url/delete', methods=['POST'])
def delete_url():
    url = request.form.get('url', '')
    
    # Delete from SQLite DB
    if not delete_url_from_sqlite(url):
        return jsonify({"success": False, "message": "URL not found"})
    
    # Also update JSON file for backward compatibility
    data = load_json()
    try:
        data.remove(url)
        save_json(data)
    except ValueError:
        pass
    
    return jsonify({"success": True, "message": "URL deleted successfully"})

# ────────────────────────── 6. STATIC ROUTES ──────────────────────────
@app.route('/static/<path:filename>')
def serve_static(filename):
    static_dir = os.path.join(BASE_DIR, 'static')
    os.makedirs(static_dir, exist_ok=True)
    return send_from_directory(static_dir, filename)

# ────────────────────────── 7. HTML TEMPLATE ──────────────────────────
HTML_TEMPLATE = """<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>AI Favorite Sites</title>
<style>
@import url('https://fonts.googleapis.com/css2?family=Nunito:wght@300;600&display=swap');
body{margin:0;font-family:Nunito,sans-serif;background:#f6f8fb;}
.tabs{display:flex;flex-wrap:wrap;gap:8px;padding:16px;}
.tab{padding:6px 14px;border:none;border-radius:18px;background:#e2e8f0;font-weight:600;cursor:pointer;}
.tab.active{background:#a78bfa;color:#1a202c;}
.tab.manage{background:#ff6e91;color:white;}
.tab.manage.active{background:#ff2d62;color:white;}
.grid{display:grid;grid-template-columns:repeat(3,1fr);gap:14px;padding:0 16px 60px;}
@media(max-width:800px){.grid{grid-template-columns:1fr;}}
.card{background:#fff;border-radius:12px;box-shadow:0 2px 8px rgba(0,0,0,.08);overflow:hidden;height:420px;display:flex;flex-direction:column;position:relative;}
.frame{flex:1;position:relative;overflow:hidden;}
.frame iframe{position:absolute;width:166.667%;height:166.667%;transform:scale(.6);transform-origin:top left;border:0;}
.frame img{width:100%;height:100%;object-fit:cover;}
.card-label{position:absolute;top:10px;left:10px;padding:4px 8px;border-radius:4px;font-size:11px;font-weight:bold;z-index:100;text-transform:uppercase;letter-spacing:0.5px;box-shadow:0 2px 4px rgba(0,0,0,0.2);}
.label-live{background:linear-gradient(135deg, #00c6ff, #0072ff);color:white;}
.label-static{background:linear-gradient(135deg, #ff9a9e, #fad0c4);color:#333;}
.foot{height:44px;background:#fafafa;display:flex;align-items:center;justify-content:center;border-top:1px solid #eee;}
.foot a{font-size:.82rem;font-weight:700;color:#4a6dd8;text-decoration:none;}
.pagination{display:flex;justify-content:center;margin:20px 0;gap:10px;}
.pagination button{padding:5px 15px;border:none;border-radius:20px;background:#e2e8f0;cursor:pointer;}
.pagination button:disabled{opacity:0.5;cursor:not-allowed;}
.manage-panel{background:white;border-radius:12px;box-shadow:0 2px 8px rgba(0,0,0,.08);margin:16px;padding:20px;}
.form-group{margin-bottom:15px;}
.form-group label{display:block;margin-bottom:5px;font-weight:600;}
.form-control{width:100%;padding:8px;border:1px solid #ddd;border-radius:4px;box-sizing:border-box;}
textarea.form-control{min-height:100px;font-family:monospace;resize:vertical;}
.btn{padding:8px 15px;border:none;border-radius:4px;cursor:pointer;font-weight:600;}
.btn-primary{background:#4a6dd8;color:white;}
.btn-danger{background:#e53e3e;color:white;}
.btn-success{background:#38a169;color:white;}
.status{padding:10px;margin:10px 0;border-radius:4px;display:none;}
.status.success{display:block;background:#c6f6d5;color:#22543d;}
.status.error{display:block;background:#fed7d7;color:#822727;}
.url-list{margin:20px 0;border:1px solid #eee;border-radius:4px;max-height:300px;overflow-y:auto;}
.url-item{padding:10px;border-bottom:1px solid #eee;display:flex;justify-content:space-between;align-items:center;}
.url-item:last-child{border-bottom:none;}
.url-controls{display:flex;gap:5px;}
</style>
</head>
<body>
<header style="text-align: center; padding: 20px; background: linear-gradient(135deg, #f6f8fb, #e2e8f0); border-bottom: 1px solid #ddd;">
  <h1 style="margin-bottom: 10px;">🌟 AI Favorite Sites</h1>
  <p class="description" style="margin-bottom: 15px;">
    🚀 <strong>Free AI Spaces & Website Gallery</strong> ✨ Save and manage your favorite sites! Supports <span style="background: linear-gradient(135deg, #00c6ff, #0072ff); color: white; padding: 2px 6px; border-radius: 4px; font-size: 12px;">LIVE</span> and <span style="background: linear-gradient(135deg, #ff9a9e, #fad0c4); color: #333; padding: 2px 6px; border-radius: 4px; font-size: 12px;">Static</span> preview modes.
  </p>
  <p>
    <a href="https://discord.gg/openfreeai" target="_blank"><img src="https://img.shields.io/static/v1?label=Discord&message=Openfree%20AI&color=%230000ff&labelColor=%23800080&logo=discord&logoColor=white&style=for-the-badge" alt="badge"></a>
  </p>
</header>
<div class="tabs" id="tabs"></div>
<div id="content"></div>

<script>
// Basic configuration
const cats = %s;
const tabs = document.getElementById('tabs');
const content = document.getElementById('content');
let active = "";
let currentPage = 1;

// LocalStorage functionality for URL persistence
function saveToLocalStorage(urls) {
  try {
    localStorage.setItem('favoriteUrls', JSON.stringify(urls));
    console.log('Saved URLs to localStorage:', urls.length);
    return true;
  } catch (e) {
    console.error('Error saving to localStorage:', e);
    return false;
  }
}

function loadFromLocalStorage() {
  try {
    const data = localStorage.getItem('favoriteUrls');
    if (data) {
      const urls = JSON.parse(data);
      console.log('Loaded URLs from localStorage:', urls.length);
      return urls;
    }
  } catch (e) {
    console.error('Error loading from localStorage:', e);
  }
  return null;
}

// Export/Import functionality
function exportUrls() {
  makeRequest('/api/favorites?per_page=1000', 'GET', null, function(data) {
    if (data.items && data.items.length > 0) {
      const urls = data.items.map(item => item.url);
      
      // Save to localStorage as backup
      saveToLocalStorage(urls);
      
      // Create file for download
      const blob = new Blob([JSON.stringify(urls, null, 2)], { type: 'application/json' });
      const a = document.createElement('a');
      a.href = URL.createObjectURL(blob);
      a.download = 'favorite_urls.json';
      document.body.appendChild(a);
      a.click();
      document.body.removeChild(a);
    } else {
      alert('No URLs to export');
    }
  });
}

function importUrls() {
  document.getElementById('import-file').click();
}

function handleImportFile(files) {
  if (files.length === 0) return;
  
  const file = files[0];
  const reader = new FileReader();
  
  reader.onload = function(e) {
    try {
      const urls = JSON.parse(e.target.result);
      if (Array.isArray(urls)) {
        // Save to localStorage
        saveToLocalStorage(urls);
        
        // Add each URL to the server
        let processed = 0;
        
        function addNextUrl(index) {
          if (index >= urls.length) {
            alert(`Import complete. Added ${processed} URLs.`);
            loadUrlList();
            if (active === 'Favorites') {
              loadFavorites(currentPage);
            }
            return;
          }
          
          const formData = new FormData();
          formData.append('url', urls[index]);
          
          makeRequest('/api/url/add', 'POST', formData, function(data) {
            if (data.success) processed++;
            addNextUrl(index + 1);
          });
        }
        
        addNextUrl(0);
      } else {
        alert('Invalid format. File must contain a JSON array of URLs.');
      }
    } catch (e) {
      alert('Error parsing file: ' + e.message);
    }
  };
  
  reader.readAsText(file);
}

// Simple utility functions
function makeRequest(url, method, data, callback) {
  const xhr = new XMLHttpRequest();
  xhr.open(method, url, true);
  xhr.onreadystatechange = function() {
    if (xhr.readyState === 4 && xhr.status === 200) {
      callback(JSON.parse(xhr.responseText));
    }
  };
  if (method === 'POST') {
    xhr.send(data);
  } else {
    xhr.send();
  }
}

function updateTabs() {
  Array.from(tabs.children).forEach(b => {
    b.classList.toggle('active', b.dataset.c === active);
  });
}

// Tab handlers
function loadCategory(cat) {
  if(cat === active) return;
  active = cat;
  updateTabs();
  
  content.innerHTML = '<p style="text-align:center;padding:40px">Loading…</p>';
  
  makeRequest('/api/category?name=' + encodeURIComponent(cat), 'GET', null, function(data) {
    let html = '<div class="grid">';
    
    data.forEach(item => {
      html += `
        <div class="card">
          <div class="card-label label-live">LIVE</div>
          <div class="frame">
            <iframe src="${item.iframe}" loading="lazy" sandbox="allow-forms allow-modals allow-popups allow-same-origin allow-scripts allow-downloads"></iframe>
          </div>
          <div class="foot">
            <a href="${item.hf}" target="_blank">${item.title}</a>
          </div>
        </div>
      `;
    });
    
    html += '</div>';
    content.innerHTML = html;
  });
}

function loadFavorites(page) {
  if(active === 'Favorites' && currentPage === page) return;
  active = 'Favorites';
  currentPage = page || 1;
  updateTabs();
  
  content.innerHTML = '<p style="text-align:center;padding:40px">Loading…</p>';
  
  makeRequest('/api/favorites?page=' + currentPage, 'GET', null, function(data) {
    let html = '<div class="grid">';
    
    if(data.items.length === 0) {
      html += '<p style="grid-column:1/-1;text-align:center;padding:40px">No favorites saved yet.</p>';
    } else {
      data.items.forEach(item => {
        if(item.mode === 'snapshot') {
          html += `
            <div class="card">
              <div class="card-label label-static">Static</div>
              <div class="frame">
                <img src="${item.preview_url}" loading="lazy">
              </div>
              <div class="foot">
                <a href="${item.url}" target="_blank">${item.title}</a>
              </div>
            </div>
          `;
        } else {
          html += `
            <div class="card">
              <div class="card-label label-live">LIVE</div>
              <div class="frame">
                <iframe src="${item.preview_url}" loading="lazy" sandbox="allow-forms allow-modals allow-popups allow-same-origin allow-scripts allow-downloads"></iframe>
              </div>
              <div class="foot">
                <a href="${item.url}" target="_blank">${item.title}</a>
              </div>
            </div>
          `;
        }
      });
    }
    
    html += '</div>';
    
    // Add pagination
    html += `
      <div class="pagination">
        <button ${currentPage <= 1 ? 'disabled' : ''} onclick="loadFavorites(${currentPage-1})">« Previous</button>
        <span>Page ${currentPage} of ${data.total_pages}</span>
        <button ${currentPage >= data.total_pages ? 'disabled' : ''} onclick="loadFavorites(${currentPage+1})">Next »</button>
      </div>
    `;
    
    content.innerHTML = html;
  });
}

function loadManage() {
  if(active === 'Manage') return;
  active = 'Manage';
  updateTabs();
  
  content.innerHTML = `
    <div class="manage-panel">
      <h2>Add New URL</h2>
      <div class="form-group">
        <label for="new-url">Single URL</label>
        <input type="text" id="new-url" class="form-control" placeholder="https://example.com">
        <button onclick="addUrl()" class="btn btn-primary" style="margin-top:10px">Add URL</button>
      </div>
      
      <div class="form-group" style="margin-top:20px">
        <label for="batch-urls">Multiple URLs (up to 100 URLs, one per line)</label>
        <textarea id="batch-urls" class="form-control" rows="8" placeholder="https://example1.com&#10;https://example2.com&#10;https://example3.com"></textarea>
        <button onclick="addBatchUrls()" class="btn btn-primary" style="margin-top:10px">Add All URLs</button>
      </div>
      <div id="add-status" class="status"></div>
      <div id="progress-bar" style="display:none; margin:15px 0;">
        <div style="height:20px; background-color:#f0f0f0; border-radius:4px; overflow:hidden;">
          <div id="progress-fill" style="height:100%; width:0%; background-color:#4a6dd8; transition:width 0.3s;"></div>
        </div>
        <div id="progress-text" style="text-align:center; margin-top:5px; font-size:14px;">0%%</div>
      </div>
      
      <h2>Manage Saved URLs</h2>
      <div id="url-list" class="url-list">Loading...</div>
      
      <div style="margin-top: 30px;">
        <h3>Backup & Restore</h3>
        <p>Server storage may not persist across restarts. Use these options to save your data:</p>
        <button onclick="exportUrls()" class="btn btn-success">Export URLs</button>
        <button onclick="importUrls()" class="btn btn-primary">Import URLs</button>
        <input type="file" id="import-file" style="display:none" onchange="handleImportFile(this.files)">
      </div>
    </div>
  `;
  
  loadUrlList();
}

// URL management functions
function loadUrlList() {
  // First try to load from localStorage as a fallback
  const localUrls = loadFromLocalStorage();
  
  makeRequest('/api/favorites?per_page=100', 'GET', null, function(data) {
    const urlList = document.getElementById('url-list');
    
    // If server has no URLs but localStorage does, restore from localStorage
    if (data.items.length === 0 && localUrls && localUrls.length > 0) {
      showStatus('add-status', 'Restoring URLs from local backup...', true);
      
      // Add each URL from localStorage to the server
      let restored = 0;
      
      function restoreNextUrl(index) {
        if (index >= localUrls.length) {
          showStatus('add-status', `Restored ${restored} URLs from local backup`, true);
          // Reload the list after restoration
          setTimeout(() => {
            loadUrlList();
            if (active === 'Favorites') {
              loadFavorites(currentPage);
            }
          }, 1000);
          return;
        }
        
        const formData = new FormData();
        formData.append('url', localUrls[index]);
        
        makeRequest('/api/url/add', 'POST', formData, function(data) {
          if (data.success) restored++;
          restoreNextUrl(index + 1);
        });
      }
      
      restoreNextUrl(0);
      return;
    }
    
    if(data.items.length === 0) {
      urlList.innerHTML = '<p style="text-align:center;padding:20px">No URLs saved yet.</p>';
      return;
    }
    
    // Update localStorage with server data
    saveToLocalStorage(data.items.map(item => item.url));
    
    let html = '';
    data.items.forEach(item => {
      // Escape the URL to prevent JavaScript injection when used in onclick handlers
      const escapedUrl = item.url.replace(/'/g, "\\'");
      
      html += `
        <div class="url-item">
          <div>${item.url}</div>
          <div class="url-controls">
            <button class="btn" onclick="editUrl('${escapedUrl}')">Edit</button>
            <button class="btn btn-danger" onclick="deleteUrl('${escapedUrl}')">Delete</button>
          </div>
        </div>
      `;
    });
    
    urlList.innerHTML = html;
  });
}

function addUrl() {
  const url = document.getElementById('new-url').value.trim();
  
  if(!url) {
    showStatus('add-status', 'Please enter a URL', false);
    return;
  }
  
  const formData = new FormData();
  formData.append('url', url);
  
  makeRequest('/api/url/add', 'POST', formData, function(data) {
    showStatus('add-status', data.message, data.success);
    if(data.success) {
      document.getElementById('new-url').value = '';
      
      // Update localStorage
      const localUrls = loadFromLocalStorage() || [];
      if (!localUrls.includes(url)) {
        localUrls.unshift(url); // Add to beginning
        saveToLocalStorage(localUrls);
      }
      
      loadUrlList();
      // If currently in Favorites tab, reload to see changes immediately
      if(active === 'Favorites') {
        loadFavorites(currentPage);
      }
    }
  });
}

function addBatchUrls() {
  const textarea = document.getElementById('batch-urls');
  const text = textarea.value.trim();
  
  if (!text) {
    showStatus('add-status', 'Please enter at least one URL', false);
    return;
  }
  
  // Split by newlines and filter out empty lines
  let urls = text.split(/\r?\n/).filter(url => url.trim() !== '');
  
  // Limit to 100 URLs
  if (urls.length > 100) {
    showStatus('add-status', 'Too many URLs. Limited to 100 at once.', false);
    urls = urls.slice(0, 100);
  }
  
  if (urls.length === 0) {
    showStatus('add-status', 'No valid URLs found', false);
    return;
  }
  
  // Show progress bar
  const progressBar = document.getElementById('progress-bar');
  const progressFill = document.getElementById('progress-fill');
  const progressText = document.getElementById('progress-text');
  progressBar.style.display = 'block';
  progressFill.style.width = '0%%';
  progressText.textContent = '0%%';
  
  // Add URLs one by one
  let processed = 0;
  let succeeded = 0;
  
  function updateProgress() {
    const percentage = Math.round((processed / urls.length) * 100);
    progressFill.style.width = percentage + '%%';
    progressText.textContent = `${processed}/${urls.length} (${percentage}%%)`;
  }
  
  function addNextUrl(index) {
    if (index >= urls.length) {
      // All done
      setTimeout(() => {
        progressBar.style.display = 'none';
        textarea.value = '';
        showStatus('add-status', `Added ${succeeded} of ${urls.length} URLs successfully`, true);
        
        // Reload URL list and favorites
        loadUrlList();
        if (active === 'Favorites') {
          loadFavorites(currentPage);
        }
      }, 500);
      return;
    }
    
    const url = urls[index].trim();
    if (!url) {
      // Skip empty URLs
      processed++;
      updateProgress();
      addNextUrl(index + 1);
      return;
    }
    
    const formData = new FormData();
    formData.append('url', url);
    
    makeRequest('/api/url/add', 'POST', formData, function(data) {
      processed++;
      
      if (data.success) {
        succeeded++;
        
        // Update localStorage
        const localUrls = loadFromLocalStorage() || [];
        if (!localUrls.includes(url)) {
          localUrls.unshift(url);
          saveToLocalStorage(localUrls);
        }
      }
      
      updateProgress();
      addNextUrl(index + 1);
    });
  }
  
  // Start adding URLs
  addNextUrl(0);
}

function editUrl(url) {
  // Decode URL if it was previously escaped
  const decodedUrl = url.replace(/\\'/g, "'");
  const newUrl = prompt('Edit URL:', decodedUrl);
  
  if(!newUrl || newUrl === decodedUrl) return;
  
  const formData = new FormData();
  formData.append('old', decodedUrl);
  formData.append('new', newUrl);
  
  makeRequest('/api/url/update', 'POST', formData, function(data) {
    if(data.success) {
      // Update localStorage
      let localUrls = loadFromLocalStorage() || [];
      const index = localUrls.indexOf(decodedUrl);
      if (index !== -1) {
        localUrls[index] = newUrl;
        saveToLocalStorage(localUrls);
      }
      
      loadUrlList();
      // If currently in Favorites tab, reload to see changes immediately
      if(active === 'Favorites') {
        loadFavorites(currentPage);
      }
    } else {
      alert(data.message);
    }
  });
}

function deleteUrl(url) {
  // Decode URL if it was previously escaped
  const decodedUrl = url.replace(/\\'/g, "'");
  if(!confirm('Are you sure you want to delete this URL?')) return;
  
  const formData = new FormData();
  formData.append('url', decodedUrl);
  
  makeRequest('/api/url/delete', 'POST', formData, function(data) {
    if(data.success) {
      // Update localStorage
      let localUrls = loadFromLocalStorage() || [];
      const index = localUrls.indexOf(decodedUrl);
      if (index !== -1) {
        localUrls.splice(index, 1);
        saveToLocalStorage(localUrls);
      }
      
      loadUrlList();
      // If currently in Favorites tab, reload to see changes immediately
      if(active === 'Favorites') {
        loadFavorites(currentPage);
      }
    } else {
      alert(data.message);
    }
  });
}

function showStatus(id, message, success) {
  const status = document.getElementById(id);
  status.textContent = message;
  status.className = success ? 'status success' : 'status error';
  setTimeout(() => {
    status.className = 'status';
  }, 3000);
}

// Create tabs
// Favorites tab first
const favTab = document.createElement('button');
favTab.className = 'tab';
favTab.textContent = 'Favorites';
favTab.dataset.c = 'Favorites';
favTab.onclick = function() { loadFavorites(1); };
tabs.appendChild(favTab);

// Category tabs
cats.forEach(c => {
  const b = document.createElement('button');
  b.className = 'tab';
  b.textContent = c;
  b.dataset.c = c;
  b.onclick = function() { loadCategory(c); };
  tabs.appendChild(b);
});

// Manage tab last
const manageTab = document.createElement('button');
manageTab.className = 'tab manage';
manageTab.textContent = 'Manage';
manageTab.dataset.c = 'Manage';
manageTab.onclick = function() { loadManage(); };
tabs.appendChild(manageTab);

// Start with Favorites tab
loadFavorites(1);

// Check for localStorage on page load 
window.addEventListener('load', function() {
  // Try to load URLs from localStorage
  const localUrls = loadFromLocalStorage();
  
  // If we have URLs in localStorage, make sure they're on the server
  if (localUrls && localUrls.length > 0) {
    console.log(`Found ${localUrls.length} URLs in localStorage`);
    
    // First get server URLs to compare
    makeRequest('/api/favorites?per_page=1000', 'GET', null, function(data) {
      const serverUrls = data.items.map(item => item.url);
      
      // Find URLs that are in localStorage but not on server
      const missingUrls = localUrls.filter(url => !serverUrls.includes(url));
      
      if (missingUrls.length > 0) {
        console.log(`Found ${missingUrls.length} URLs missing from server, restoring...`);
        
        // Add missing URLs to server
        let restored = 0;
        
        function restoreNextUrl(index) {
          if (index >= missingUrls.length) {
            console.log(`Restored ${restored} URLs from localStorage`);
            if (active === 'Favorites') {
              loadFavorites(currentPage);
            }
            return;
          }
          
          const formData = new FormData();
          formData.append('url', missingUrls[index]);
          
          makeRequest('/api/url/add', 'POST', formData, function(data) {
            if (data.success) restored++;
            restoreNextUrl(index + 1);
          });
        }
        
        restoreNextUrl(0);
      }
    });
  }
});
</script>
</body>
</html>
"""

# ────────────────────────── 8. MAIN ROUTES ──────────────────────────
@app.route('/')
def home():
    # Instead of writing to a file, we'll serve the HTML directly
    categories_json = json.dumps(list(CATEGORIES.keys()))
    return HTML_TEMPLATE % categories_json

# Initialize database on startup
init_db()

# Define a function to ensure database consistency
def ensure_db_consistency():
    """Make sure both databases are in sync"""
    try:
        # Get URLs from both sources
        sqlite_urls = load_db_sqlite()
        json_urls = load_json()
        
        # Combine and deduplicate
        all_urls = list(set(sqlite_urls + json_urls))
        
        # If there are differences, update both databases
        if len(all_urls) != len(sqlite_urls) or len(all_urls) != len(json_urls):
            logger.info("Database inconsistency detected, synchronizing...")
            # Save to both databases
            save_db(all_urls)
            
            # Double-check if save was successful
            sqlite_check = load_db_sqlite()
            json_check = load_json()
            
            if len(sqlite_check) != len(all_urls) or len(json_check) != len(all_urls):
                logger.error(f"Database synchronization failed! SQLite: {len(sqlite_check)}, JSON: {len(json_check)}, Expected: {len(all_urls)}")
            else:
                logger.info("Database synchronization successful")
    except Exception as e:
        logger.error(f"Error during database consistency check: {e}")

# For Flask 2.0+ compatibility
@app.before_request
def before_request_func():
    # Use a flag to run this only once
    if not hasattr(app, '_got_first_request'):
        ensure_db_consistency()
        app._got_first_request = True
        
        # Log database status
        logger.info(f"Database status - SQLite: {len(load_db_sqlite())} URLs, JSON: {len(load_json())} URLs")

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=7860)