"""
db.py

Purpose:
  Encapsulate SQLite connection lifecycle, providing `get_db()` for obtaining a
  request-scoped connection and `register_teardown(app)` to close it after the
  request. This mirrors Flask's application context pattern and keeps DB logic
  out of `app.py`.

Exports:
  - get_db(): returns a sqlite3.Row-based connection stored on flask.g
  - register_teardown(app): registers a teardown handler to close the DB
"""

import sqlite3
import os
from flask import g
from .paths import ROOT_DIR


def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        # Use environment variable if set (for testing), otherwise use default
        db_path = os.getenv('DATABASE_PATH') or os.path.join(ROOT_DIR, 'theialogin.db')
        db = sqlite3.connect(db_path)
        db.row_factory = sqlite3.Row
        # Connection-wide pragmas for reliability and performance
        try:
            db.execute('PRAGMA journal_mode=WAL')
            db.execute('PRAGMA synchronous=NORMAL')
            db.execute('PRAGMA foreign_keys=ON')
        except Exception:
            pass
        g._database = db
    return db


def register_teardown(app):
    @app.teardown_appcontext
    def close_connection(exception):
        db = getattr(g, '_database', None)
        if db is not None:
            db.close()