import json
import csv
import sqlite3
import os
import sys

def build_formats(jsonl_path):
    base_dir = os.path.dirname(jsonl_path)
    json_path = os.path.join(base_dir, "thayer_lexicon.json")
    csv_path = os.path.join(base_dir, "thayer_lexicon.csv")
    sqlite_path = os.path.join(base_dir, "thayer_lexicon.sqlite")

    print(f"Reading from {jsonl_path}...")
    entries = []
    with open(jsonl_path, 'r', encoding='utf-8') as f:
        for line in f:
            if line.strip():
                entries.append(json.loads(line))
    
    print(f"Loaded {len(entries)} entries.")

    # 1. Write JSON
    print(f"Writing {json_path}...")
    with open(json_path, 'w', encoding='utf-8') as f:
        json.dump(entries, f, indent=2, ensure_ascii=False)

    # 2. Write CSV
    print(f"Writing {csv_path}...")
    with open(csv_path, 'w', encoding='utf-8', newline='') as f:
        writer = csv.writer(f)
        writer.writerow([
            "id", "lemma", "lemma_normalized", "strongs", 
            "transliteration", "grammar", "text", "pdf_pages"
        ])
        for entry in entries:
            strongs_str = ",".join(entry.get("strongs") or [])
            pdf_pages_str = ",".join(map(str, entry.get("source", {}).get("pdf_pages", [])))
            writer.writerow([
                entry.get("id", ""),
                entry.get("lemma", ""),
                entry.get("lemma_normalized", ""),
                strongs_str,
                entry.get("transliteration", ""),
                entry.get("grammar", ""),
                entry.get("text", ""),
                pdf_pages_str
            ])

    # 3. Write SQLite
    print(f"Writing {sqlite_path}...")
    if os.path.exists(sqlite_path):
        os.remove(sqlite_path)
    conn = sqlite3.connect(sqlite_path)
    cursor = conn.cursor()
    
    cursor.execute("""
        CREATE TABLE entries (
            id TEXT,
            lemma TEXT,
            lemma_normalized TEXT,
            strongs TEXT,
            transliteration TEXT,
            grammar TEXT,
            text TEXT,
            pdf_pages TEXT,
            raw_json TEXT
        )
    """)

    for entry in entries:
        strongs_str = ",".join(entry.get("strongs") or [])
        pdf_pages_str = ",".join(map(str, entry.get("source", {}).get("pdf_pages", [])))
        raw_json = json.dumps(entry, ensure_ascii=False)
        
        cursor.execute("""
            INSERT INTO entries (id, lemma, lemma_normalized, strongs, transliteration, grammar, text, pdf_pages, raw_json)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            entry.get("id"),
            entry.get("lemma"),
            entry.get("lemma_normalized"),
            strongs_str,
            entry.get("transliteration"),
            entry.get("grammar"),
            entry.get("text"),
            pdf_pages_str,
            raw_json
        ))
        
    conn.commit()
    conn.close()

    print("All formats successfully built!")

if __name__ == "__main__":
    if len(sys.argv) > 1:
        jsonl_file = sys.argv[1]
    else:
        jsonl_file = os.path.join(os.path.dirname(os.path.abspath(__file__)), "..", "data", "thayer_lexicon.jsonl")
    
    build_formats(jsonl_file)