import sqlite3
from datetime import datetime
from flask import g, current_app


def get_db():
    if "db" not in g:
        g.db = sqlite3.connect(current_app.config["DATABASE"])  # type: ignore[attr-defined]
        g.db.row_factory = sqlite3.Row
    return g.db


def close_db(_=None):
    db = g.pop("db", None)
    if db is not None:
        db.close()


def init_db():
    db = get_db()
    db.execute(
        """
        CREATE TABLE IF NOT EXISTS pages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            uuid TEXT NOT NULL UNIQUE,
            title TEXT NOT NULL DEFAULT '',
            html TEXT NOT NULL,
            created_at TEXT NOT NULL
        );
        """
    )
    # Migrate: ensure title exists
    try:
        cols = {row[1] for row in db.execute("PRAGMA table_info(pages)").fetchall()}
        if "title" not in cols:
            db.execute("ALTER TABLE pages ADD COLUMN title TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    db.commit()


def init_app(app):
    @app.before_request
    def _before_request():  # noqa: D401
        init_db()

    @app.teardown_appcontext
    def _teardown(_=None):  # noqa: D401
        close_db()