"""SQLite access. The schema is shared with patchbayd (schema.sql)."""

import os
import sqlite3
import time

from flask import current_app, g

_HERE = os.path.dirname(os.path.abspath(__file__))
# Installed copy next to the package, else the repository root.
SCHEMA_PATHS = [os.path.join(_HERE, "schema.sql"), os.path.join(_HERE, "..", "..", "schema.sql")]


def schema_sql():
    for p in SCHEMA_PATHS:
        if os.path.exists(p):
            with open(p, encoding="utf-8") as f:
                return f.read()
    raise FileNotFoundError("schema.sql not found")


def connect(path):
    old = os.umask(0o077)  # the DB holds password hashes
    try:
        conn = sqlite3.connect(path, timeout=5, isolation_level=None)
    finally:
        os.umask(old)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA busy_timeout = 5000")
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def init(path):
    conn = connect(path)
    conn.executescript(schema_sql())
    # Older databases lack nodes.iface / nodes.origin (same migrations as db.c).
    cols = {r["name"] for r in conn.execute("PRAGMA table_info(nodes)")}
    for col in ("iface", "origin"):
        if col not in cols:
            conn.execute(f"ALTER TABLE nodes ADD COLUMN {col} TEXT NOT NULL DEFAULT ''")
    conn.close()


def get():
    if "db" not in g:
        g.db = connect(current_app.config["PB_DB"])
    return g.db


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


def setting(key, default=None):
    row = get().execute("SELECT value FROM settings WHERE key = ?", (key,)).fetchone()
    return row["value"] if row else default


def set_setting(key, value):
    get().execute("INSERT INTO settings (key, value) VALUES (?, ?) "
                  "ON CONFLICT (key) DO UPDATE SET value = excluded.value", (key, str(value)))


def now():
    return int(time.time())