"""SQLite helpers (file db: data/hunter.db)."""
import os
import sqlite3
from flask import g

HERE = os.path.dirname(os.path.abspath(__file__))
DATA_DIR = os.path.join(HERE, "data")
DB_PATH = os.path.join(DATA_DIR, "hunter.db")

SCHEMA = """
CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT);
CREATE TABLE IF NOT EXISTS plans (
  id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, price INTEGER NOT NULL DEFAULT 0,
  daily_searches INTEGER NOT NULL DEFAULT 10, max_results INTEGER NOT NULL DEFAULT 20,
  emails_allowed INTEGER NOT NULL DEFAULT 0, sort INTEGER NOT NULL DEFAULT 0, featured INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS clients (
  id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, username TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL, plan_id INTEGER NOT NULL, active INTEGER NOT NULL DEFAULT 1,
  expires_at TEXT NOT NULL, whatsapp TEXT DEFAULT '', created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS hunts (
  id INTEGER PRIMARY KEY AUTOINCREMENT, client_id INTEGER NOT NULL, what TEXT, place TEXT,
  count INTEGER, file TEXT, source TEXT, created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS login_fails (ip TEXT, ts REAL);
"""

DEFAULT_SETTINGS = {
    "currency": "PKR",
    "contact_whatsapp": "",
    "tagline": "Find real customers for any business, in seconds.",
}
DEFAULT_PLANS = [  # name, price, daily searches, max results/search, emails, featured
    ("Starter", 2000, 10, 20, 0, 0),
    ("Pro", 5000, 30, 40, 1, 1),
    ("Business", 10000, 100, 60, 1, 0),
]


def get_db():
    if "db" not in g:
        g.db = sqlite3.connect(DB_PATH)
        g.db.row_factory = sqlite3.Row
    return g.db


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


def init_db():
    os.makedirs(DATA_DIR, exist_ok=True)
    con = sqlite3.connect(DB_PATH)
    con.executescript(SCHEMA)
    for k, v in DEFAULT_SETTINGS.items():
        con.execute("INSERT OR IGNORE INTO settings(key,value) VALUES(?,?)", (k, v))
    if con.execute("SELECT COUNT(*) FROM plans").fetchone()[0] == 0:
        for i, p in enumerate(DEFAULT_PLANS):
            con.execute("INSERT INTO plans(name,price,daily_searches,max_results,emails_allowed,featured,sort) "
                        "VALUES(?,?,?,?,?,?,?)", (*p[:4], p[4], p[5], i))
    con.commit()
    con.close()


def q(sql, args=(), one=False):
    cur = get_db().execute(sql, args)
    rows = cur.fetchall()
    return (rows[0] if rows else None) if one else rows


def run(sql, args=()):
    db = get_db()
    cur = db.execute(sql, args)
    db.commit()
    return cur


def settings() -> dict:
    return {r["key"]: r["value"] for r in q("SELECT key,value FROM settings")}