"""
Database module for TLD prices
"""

import sqlite3
from pathlib import Path

DB_PATH = Path(__file__).parent / "prices.db"


def get_connection():
    return sqlite3.connect(DB_PATH)


def init_db():
    """Initialize database and create tables"""
    conn = get_connection()
    conn.execute("""
        CREATE TABLE IF NOT EXISTS tld_prices (
            tld TEXT PRIMARY KEY,
            usd REAL NOT NULL,
            krw INTEGER NOT NULL,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS exchange_rates (
            currency TEXT PRIMARY KEY,
            rate REAL NOT NULL,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()
    conn.close()


def get_prices() -> list[dict]:
    """Get all TLD prices"""
    conn = get_connection()
    conn.row_factory = sqlite3.Row
    cursor = conn.execute(
        "SELECT tld, usd, krw, updated_at FROM tld_prices ORDER BY krw"
    )
    results = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return results


def get_exchange_rate(currency: str = "KRW") -> float | None:
    """Get exchange rate for currency"""
    conn = get_connection()
    cursor = conn.execute(
        "SELECT rate FROM exchange_rates WHERE currency = ?", (currency,)
    )
    row = cursor.fetchone()
    conn.close()
    return row[0] if row else None


if __name__ == "__main__":
    init_db()
    print(f"Database initialized: {DB_PATH}")