database.py

import sqlite3
import os
from pathlib import Path
from datetime import datetime

DB_PATH = "data/conventions.db"
SEED_SQL_PATH = "data/seed_conventions.sql"


def init_db():
    try:
        Path("data").mkdir(exist_ok=True)
        
        if os.path.exists(DB_PATH):
            conn = sqlite3.connect(DB_PATH)
            cursor = conn.cursor()
            cursor.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='conventions'")
            table_exists = cursor.fetchone()[0]
            if table_exists > 0:
                cursor.execute("SELECT COUNT(*) FROM conventions")
                count = cursor.fetchone()[0]
                conn.close()
                if count > 0:
                    print(f"Database already has {count} conventions")
                    return True
            conn.close()
        
        if not os.path.exists(SEED_SQL_PATH):
            print(f"Error: can't find {SEED_SQL_PATH}")
            return False
        
        with open(SEED_SQL_PATH, 'r') as f:
            sql_script = f.read()
        
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        cursor.executescript(sql_script)
        conn.commit()
        
        cursor.execute("SELECT COUNT(*) FROM conventions")
        count = cursor.fetchone()[0]
        conn.close()
        
        print(f"Database setup complete: {count} conventions loaded")
        return True
        
    except Exception as e:
        print(f"Database error: {str(e)}")
        return False


def get_all_conventions():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM conventions ORDER BY popularity_rank")
    rows = cursor.fetchall()
    conn.close()
    
    conventions = []
    for row in rows:
        conventions.append(dict(row))
    return conventions


def search_conventions(query):
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    search_pattern = f"%{query}%"
    cursor.execute("""
        SELECT * FROM conventions 
        WHERE name LIKE ? OR city LIKE ? OR state LIKE ?
        ORDER BY popularity_rank
    """, (search_pattern, search_pattern, search_pattern))
    
    rows = cursor.fetchall()
    conn.close()
    
    results = []
    for row in rows:
        results.append(dict(row))
    
    return results


def get_convention_by_id(convention_id):
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM conventions WHERE convention_id = ?", (convention_id,))
    row = cursor.fetchone()
    conn.close()
    
    if row:
        return dict(row)
    else:
        return None


def log_action(action, details=None):
    Path("logs").mkdir(exist_ok=True)
    
    timestamp = datetime.now().isoformat()
    log_line = f"{timestamp} | {action}"
    if details:
        log_line += f" | {details}"
    log_line += "\n"
    
    with open("logs/actions.log", "a") as f:
        f.write(log_line)


if __name__ == "__main__":
    print("Testing database module...")
    
    if init_db():
        cons = get_all_conventions()
        print(f"Found {len(cons)} total conventions")
        
        search_results = search_conventions("Anthro")
        print(f"Search for 'Anthro': {len(search_results)} matches")
        
        first_con = get_convention_by_id(1)
        if first_con:
            print(f"First convention: {first_con['name']}")
        
        log_action("test", "ran database tests")
        print("Tests completed successfully")