"""SQLite storage for the biosync licensing portal (users, subscriptions,
payments, licenses). Pure stdlib sqlite3; one file, easy to back up."""

from __future__ import annotations

import sqlite3
import time
from pathlib import Path

SCHEMA = """
CREATE TABLE IF NOT EXISTS users(
  id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL, name TEXT, org TEXT,
  pw_hash TEXT NOT NULL, is_admin INTEGER DEFAULT 0, created REAL,
  verified INTEGER DEFAULT 0);
CREATE TABLE IF NOT EXISTS tokens(
  token TEXT PRIMARY KEY, user_id INTEGER, kind TEXT, expires REAL, used INTEGER DEFAULT 0);
CREATE TABLE IF NOT EXISTS subscriptions(
  id INTEGER PRIMARY KEY, user_id INTEGER, plan TEXT, status TEXT,
  started REAL, renews REAL, provider_ref TEXT);
CREATE TABLE IF NOT EXISTS payments(
  id INTEGER PRIMARY KEY, user_id INTEGER, subscription_id INTEGER,
  amount REAL, currency TEXT, status TEXT, provider TEXT, provider_ref TEXT,
  created REAL, paid_at REAL);
CREATE TABLE IF NOT EXISTS licenses(
  id INTEGER PRIMARY KEY, user_id INTEGER, license_id TEXT UNIQUE, key TEXT,
  plan TEXT, issued TEXT, expires TEXT, status TEXT, created REAL);
"""


class DB:
    def __init__(self, path: str):
        Path(path).parent.mkdir(parents=True, exist_ok=True)
        self.path = path
        self._conn().executescript(SCHEMA)

    def _conn(self):
        c = sqlite3.connect(self.path)
        c.row_factory = sqlite3.Row
        return c

    def q(self, sql, args=(), one=False):
        c = self._conn()
        cur = c.execute(sql, args)
        rows = cur.fetchall()
        c.commit(); c.close()
        return (rows[0] if rows else None) if one else rows

    def ins(self, sql, args=()):
        c = self._conn()
        cur = c.execute(sql, args)
        rid = cur.lastrowid
        c.commit(); c.close()
        return rid

    # --- users ---
    def user_by_email(self, email):
        return self.q("SELECT * FROM users WHERE email=?", (email.lower(),), one=True)

    def user(self, uid):
        return self.q("SELECT * FROM users WHERE id=?", (uid,), one=True)

    def create_user(self, email, name, org, pw_hash, is_admin=0):
        return self.ins("INSERT INTO users(email,name,org,pw_hash,is_admin,created)"
                        " VALUES(?,?,?,?,?,?)",
                        (email.lower(), name, org, pw_hash, is_admin, time.time()))

    # --- licenses / subs / payments ---
    def latest_license(self, uid):
        return self.q("SELECT * FROM licenses WHERE user_id=? ORDER BY created DESC",
                      (uid,), one=True)

    def latest_subscription(self, uid):
        return self.q("SELECT * FROM subscriptions WHERE user_id=? ORDER BY id DESC",
                      (uid,), one=True)

    def latest_payment(self, uid):
        return self.q("SELECT * FROM payments WHERE user_id=? ORDER BY id DESC",
                      (uid,), one=True)

    def all_users(self):
        return self.q("SELECT * FROM users ORDER BY created DESC")

    def all_licenses(self):
        return self.q("SELECT * FROM licenses ORDER BY created DESC")

    # --- verification / password tokens ---
    def set_verified(self, uid, value=1):
        self.q("UPDATE users SET verified=? WHERE id=?", (value, uid))

    def set_password(self, uid, pw_hash):
        self.q("UPDATE users SET pw_hash=? WHERE id=?", (pw_hash, uid))

    def create_token(self, user_id, kind, ttl=86400):
        import secrets
        tok = secrets.token_urlsafe(32)
        self.ins("INSERT INTO tokens(token,user_id,kind,expires,used) VALUES(?,?,?,?,0)",
                 (tok, user_id, kind, time.time() + ttl))
        return tok

    def use_token(self, tok, kind):
        """Return user_id if the token is valid+unused+unexpired, then mark it used."""
        row = self.q("SELECT * FROM tokens WHERE token=? AND kind=?", (tok, kind), one=True)
        if not row or row["used"] or row["expires"] < time.time():
            return None
        self.q("UPDATE tokens SET used=1 WHERE token=?", (tok,))
        return row["user_id"]