import os
import requests
from bs4 import BeautifulSoup
import sqlite3
import time
from concurrent.futures import ThreadPoolExecutor, as_completed

BASE_URL = "https://www.bible-topics.com/"
DATA_DIR = r"d:\Anti Gravity\Tamil Study Bible\TamilStudy\data\bible_topics"

def setup_db(db_path):
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS topics (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT UNIQUE,
                    url TEXT
                )''')
    c.execute('''CREATE TABLE IF NOT EXISTS entries (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    topic_id INTEGER,
                    point_text TEXT,
                    verse_ref TEXT,
                    verse_text TEXT,
                    FOREIGN KEY(topic_id) REFERENCES topics(id)
                )''')
    conn.commit()
    conn.close()

def scrape_topic(name, url, db_path):
    try:
        r = requests.get(url, timeout=15)
        if r.status_code != 200:
            return name, False, f"HTTP {r.status_code}"
        
        soup = BeautifulSoup(r.content, 'html.parser')
        entry_div = soup.find('div', class_='torreyentry')
        if not entry_div:
            return name, False, "No entry div"

        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Insert topic
        cursor.execute("INSERT OR IGNORE INTO topics (name, url) VALUES (?, ?)", (name, url))
        cursor.execute("SELECT id FROM topics WHERE name=?", (name,))
        result = cursor.fetchone()
        if not result:
             conn.close()
             return name, False, "Failed to get topic ID"
        topic_id = result[0]

        # Parse points
        points = entry_div.find_all('li')
        entries_to_insert = []
        for li in points:
            point_text_parts = []
            for child in li.children:
                if child.name is None:
                    point_text_parts.append(str(child.string) if child.string else "")
                elif child.name not in ['p', 'ul', 'ol', 'span']:
                    point_text_parts.append(child.get_text())
                elif child.name == 'span' and 'versetag' not in child.get('class', []):
                     point_text_parts.append(child.get_text())
            
            point_text = "".join(point_text_parts).strip().rstrip(':').rstrip('.')
            
            verses = li.find_all('p', class_='versetext')
            if not verses:
                if point_text:
                    entries_to_insert.append((topic_id, point_text, "", ""))
            else:
                for v in verses:
                    tag = v.find('span', class_='versetag')
                    ref = tag.text.strip() if tag else ""
                    v_text = v.get_text().replace(ref, "").strip()
                    entries_to_insert.append((topic_id, point_text, ref, v_text))
        
        if entries_to_insert:
            cursor.executemany("INSERT INTO entries (topic_id, point_text, verse_ref, verse_text) VALUES (?, ?, ?, ?)", entries_to_insert)
        
        conn.commit()
        conn.close()
        return name, True, None
    except Exception as e:
        return name, False, str(e)

def scrape():
    if not os.path.exists(DATA_DIR):
        os.makedirs(DATA_DIR)
    
    db_path = os.path.join(DATA_DIR, "torrey_topics.db")
    setup_db(db_path)

    # Step 1: Get index pages
    alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    topic_links = []
    
    print("Fetching topic list...")
    for letter in alphabet:
        index_url = f"{BASE_URL}Topics-{letter}.html"
        try:
            r = requests.get(index_url, timeout=15)
            if r.status_code != 200:
                continue
            soup = BeautifulSoup(r.content, 'html.parser')
            content_div = soup.find('div', id='content')
            if content_div:
                for a in content_div.find_all('a', href=True):
                    href = a['href']
                    if href.endswith('.html') and not href.startswith('Topics-') and not href.startswith('index.html') and "/" not in href:
                        topic_links.append((a.text.strip(), BASE_URL + href))
        except Exception as e:
            print(f"Error indexing {letter}: {e}")

    topic_links = list(dict.fromkeys(topic_links))
    print(f"Total unique topics found: {len(topic_links)}")

    # Check already scraped
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM topics")
    scraped_names = set(row[0] for row in cursor.fetchall())
    conn.close()

    to_scrape = [t for t in topic_links if t[0] not in scraped_names]
    print(f"Remaining topics to scrape: {len(to_scrape)}")

    success_count = 0
    with ThreadPoolExecutor(max_workers=10) as executor:
        futures = {executor.submit(scrape_topic, name, url, db_path): (name, url) for name, url in to_scrape}
        for future in as_completed(futures):
            name, success, error = future.result()
            if success:
                success_count += 1
                if success_count % 10 == 0:
                    print(f"Progress: {success_count}/{len(to_scrape)} topics scraped.")
            else:
                print(f"Error scraping {name}: {error}")

    print(f"Scraping completed. New topics saved: {success_count}")

if __name__ == "__main__":
    scrape()