import sqlite3, secrets
from datetime import datetime, date

class DB:
    def __init__(self, path):
        self.conn=sqlite3.connect(path, check_same_thread=False)
        self.conn.row_factory=sqlite3.Row
        self.init()

    def init(self):
        self.conn.executescript("""
        CREATE TABLE IF NOT EXISTS users(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          telegram_id INTEGER UNIQUE,
          username TEXT,
          balance REAL DEFAULT 0,
          created_at TEXT
        );
        CREATE TABLE IF NOT EXISTS orders(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id INTEGER,
          game TEXT,
          product TEXT,
          price REAL,
          status TEXT DEFAULT 'PROCESSING',
          created_at TEXT
        );
        CREATE TABLE IF NOT EXISTS payments(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id INTEGER,
          amount REAL,
          status TEXT DEFAULT 'PENDING',
          created_at TEXT
        );
        CREATE TABLE IF NOT EXISTS api_keys(
          user_id INTEGER PRIMARY KEY,
          api_key TEXT UNIQUE
        );
        CREATE TABLE IF NOT EXISTS region_checks(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id INTEGER,
          day TEXT
        );
        CREATE TABLE IF NOT EXISTS deposits(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id INTEGER NOT NULL,
          provider TEXT NOT NULL,
          network TEXT NOT NULL,
          coin TEXT NOT NULL,
          address_in TEXT UNIQUE NOT NULL,
          callback_url TEXT,
          nonce TEXT UNIQUE NOT NULL,
          status TEXT DEFAULT 'WAITING',
          pending_amount REAL DEFAULT 0,
          confirmed_amount REAL DEFAULT 0,
          credited_amount REAL DEFAULT 0,
          last_txid TEXT,
          last_uuid TEXT UNIQUE,
          created_at TEXT NOT NULL,
          updated_at TEXT NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_deposits_user ON deposits(user_id);
        CREATE INDEX IF NOT EXISTS idx_deposits_address ON deposits(address_in);
        """)
        self.conn.commit()

    def get_or_create_user(self, tg):
        row=self.conn.execute("SELECT * FROM users WHERE telegram_id=?", (tg.id,)).fetchone()
        if row:
            self.conn.execute("UPDATE users SET username=? WHERE telegram_id=?", (tg.username,tg.id)); self.conn.commit()
            row=self.conn.execute("SELECT * FROM users WHERE telegram_id=?", (tg.id,)).fetchone()
            d=dict(row); d["orders"]=self.conn.execute("SELECT COUNT(*) FROM orders WHERE user_id=?",(d["id"],)).fetchone()[0]; return d
        self.conn.execute("INSERT INTO users(telegram_id,username,created_at) VALUES(?,?,?)",(tg.id,tg.username,datetime.utcnow().isoformat())); self.conn.commit()
        return self.get_or_create_user(tg)

    def user_by_id(self, uid):
        row=self.conn.execute("SELECT * FROM users WHERE id=?",(uid,)).fetchone()
        return dict(row) if row else None
    def find_user(self,tg_id):
        row=self.conn.execute("SELECT * FROM users WHERE telegram_id=?",(tg_id,)).fetchone()
        return dict(row) if row else None
    def change_balance(self,uid,amount):
        self.conn.execute("UPDATE users SET balance=balance+? WHERE id=?",(amount,uid)); self.conn.commit()
    def set_balance(self,uid,amount):
        self.conn.execute("UPDATE users SET balance=? WHERE id=?",(amount,uid)); self.conn.commit()

    def create_order(self,uid,game,product,price):
        cur=self.conn.execute("INSERT INTO orders(user_id,game,product,price,status,created_at) VALUES(?,?,?,?,?,?)",(uid,game,product,price,"PROCESSING",datetime.utcnow().isoformat())); self.conn.commit(); return cur.lastrowid
    def orders(self,uid,limit=10):
        return self.conn.execute("SELECT * FROM orders WHERE user_id=? ORDER BY id DESC LIMIT ?",(uid,limit)).fetchall()
    def all_orders(self,limit=20):
        return self.conn.execute("SELECT o.*,u.username FROM orders o JOIN users u ON u.id=o.user_id ORDER BY o.id DESC LIMIT ?",(limit,)).fetchall()
    def completed_count(self,uid): return self.conn.execute("SELECT COUNT(*) FROM orders WHERE user_id=? AND status='COMPLETED'",(uid,)).fetchone()[0]
    def processing_count(self,uid): return self.conn.execute("SELECT COUNT(*) FROM orders WHERE user_id=? AND status='PROCESSING'",(uid,)).fetchone()[0]
    def total_spent(self,uid): return self.conn.execute("SELECT COALESCE(SUM(price),0) FROM orders WHERE user_id=? AND status='COMPLETED'",(uid,)).fetchone()[0]
    def today_spent(self,uid):
        return self.conn.execute("SELECT COALESCE(SUM(price),0) FROM orders WHERE user_id=? AND status='COMPLETED' AND substr(created_at,1,10)=?",(uid,date.today().isoformat())).fetchone()[0]

    def payments(self,uid): return self.conn.execute("SELECT * FROM payments WHERE user_id=? ORDER BY id DESC LIMIT 10",(uid,)).fetchall()
    def all_payments(self,limit=20): return self.conn.execute("SELECT p.*,u.username FROM payments p JOIN users u ON u.id=p.user_id ORDER BY p.id DESC LIMIT ?",(limit,)).fetchall()

    def get_api_key(self,uid):
        row=self.conn.execute("SELECT api_key FROM api_keys WHERE user_id=?",(uid,)).fetchone()
        if row:return row["api_key"]
        key="gw_"+secrets.token_urlsafe(24); self.conn.execute("INSERT INTO api_keys(user_id,api_key) VALUES(?,?)",(uid,key)); self.conn.commit(); return key
    def api_users(self,limit=20):
        return self.conn.execute("SELECT a.api_key,u.username FROM api_keys a JOIN users u ON u.id=a.user_id ORDER BY u.id DESC LIMIT ?",(limit,)).fetchall()

    def region_used_today(self,uid): return self.conn.execute("SELECT COUNT(*) FROM region_checks WHERE user_id=? AND day=?",(uid,date.today().isoformat())).fetchone()[0]
    def use_region(self,uid): self.conn.execute("INSERT INTO region_checks(user_id,day) VALUES(?,?)",(uid,date.today().isoformat())); self.conn.commit()
    def all_users(self,limit=20): return self.conn.execute("SELECT * FROM users ORDER BY id DESC LIMIT ?",(limit,)).fetchall()
    def telegram_ids(self): return [r[0] for r in self.conn.execute("SELECT telegram_id FROM users").fetchall()]

    def stats(self):
        return {
            "users": self.conn.execute("SELECT COUNT(*) FROM users").fetchone()[0],
            "orders": self.conn.execute("SELECT COUNT(*) FROM orders").fetchone()[0],
            "processing": self.conn.execute("SELECT COUNT(*) FROM orders WHERE status='PROCESSING'").fetchone()[0],
            "completed": self.conn.execute("SELECT COUNT(*) FROM orders WHERE status='COMPLETED'").fetchone()[0],
            "sales": self.conn.execute("SELECT COALESCE(SUM(price),0) FROM orders WHERE status='COMPLETED'").fetchone()[0],
            "payments": self.conn.execute("SELECT COALESCE(SUM(amount),0) FROM payments WHERE status='APPROVED'").fetchone()[0],
        }

    # ---------- deposits ----------
    def get_deposit(self, uid, network=None):
        if network:
            row=self.conn.execute("SELECT * FROM deposits WHERE user_id=? AND network=? ORDER BY id DESC LIMIT 1",(uid,network)).fetchone()
        else:
            row=self.conn.execute("SELECT * FROM deposits WHERE user_id=? ORDER BY id DESC LIMIT 1",(uid,)).fetchone()
        return dict(row) if row else None

    def get_deposit_by_address(self, address):
        row=self.conn.execute("SELECT * FROM deposits WHERE address_in=?",(address,)).fetchone()
        return dict(row) if row else None

    def create_deposit(self, uid, provider, network, coin, address_in, callback_url, nonce):
        now=datetime.utcnow().isoformat()
        cur=self.conn.execute("""INSERT INTO deposits
          (user_id,provider,network,coin,address_in,callback_url,nonce,status,created_at,updated_at)
          VALUES(?,?,?,?,?,?,?,'WAITING',?,?)""",
          (uid,provider,network,coin,address_in,callback_url,nonce,now,now))
        self.conn.commit()
        return cur.lastrowid

    def get_deposit_by_uuid(self, uuid):
        row=self.conn.execute("SELECT * FROM deposits WHERE last_uuid=?",(uuid,)).fetchone()
        return dict(row) if row else None

    def mark_deposit(self, deposit_id, status, pending_amount, confirmed_amount, txid, uuid):
        now=datetime.utcnow().isoformat()
        cur=self.conn.execute("""UPDATE deposits
          SET status=?, pending_amount=?, confirmed_amount=?, last_txid=?, last_uuid=?, updated_at=?
          WHERE id=? AND (last_uuid IS NULL OR last_uuid<>?)""",
          (status,pending_amount,confirmed_amount,txid,uuid,now,deposit_id,uuid))
        self.conn.commit()
        return cur.rowcount

    def credit_deposit_once(self, deposit_id, amount):
        row=self.conn.execute("SELECT credited_amount,user_id FROM deposits WHERE id=?",(deposit_id,)).fetchone()
        if not row or amount <= 0: return None
        if row["credited_amount"] > 0:
            return 0
        self.conn.execute("UPDATE deposits SET credited_amount=?,updated_at=? WHERE id=? AND credited_amount=0",
                          (amount,datetime.utcnow().isoformat(),deposit_id))
        if self.conn.total_changes == 0:
            self.conn.commit(); return 0
        self.conn.execute("UPDATE users SET balance=balance+? WHERE id=?",(amount,row["user_id"]))
        self.conn.commit()
        return row["user_id"]

    def deposits(self, uid, limit=10):
        return self.conn.execute("SELECT * FROM deposits WHERE user_id=? ORDER BY id DESC LIMIT ?",(uid,limit)).fetchall()
    def all_deposits(self, limit=30):
        return self.conn.execute("""SELECT d.*,u.username,u.telegram_id
          FROM deposits d JOIN users u ON u.id=d.user_id
          ORDER BY d.id DESC LIMIT ?""",(limit,)).fetchall()