import sqlite3, threading
from datetime import datetime, timezone, timedelta
from config import CONFIG

class DBLogger:
    def __init__(self):
        self.db_path = CONFIG["db_path"]
        self._lock = threading.Lock()
        self._init_db()

    def _init_db(self):
        with sqlite3.connect(self.db_path) as conn:
            conn.execute("""CREATE TABLE IF NOT EXISTS request_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL,
                agent_name TEXT NOT NULL, agent_id TEXT, client_name TEXT, cid TEXT,
                email TEXT, amount INTEGER NOT NULL, products TEXT, provider TEXT NOT NULL,
                link_type TEXT NOT NULL, url TEXT NOT NULL)""")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_ts ON request_log(timestamp DESC)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_cid ON request_log(cid)")
            conn.commit()

    def log_request(self, **kw):
        with self._lock:
            with sqlite3.connect(self.db_path) as conn:
                conn.execute("""INSERT INTO request_log
                    (timestamp,agent_name,agent_id,client_name,cid,email,amount,products,provider,link_type,url)
                    VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
                    (datetime.now(timezone.utc).isoformat(), kw["agent_name"], kw["agent_id"],
                     kw["client_name"], kw["cid"], kw["email"], kw["amount"], kw["products"],
                     kw["provider"], kw["link_type"], kw["url"]))
                conn.commit()

    def check_duplicate_cid(self, cid, hours=24):
        cutoff = (datetime.now(timezone.utc) - timedelta(hours=hours)).isoformat()
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            row = conn.execute(
                "SELECT * FROM request_log WHERE cid=? AND timestamp>? ORDER BY id DESC LIMIT 1",
                (cid, cutoff)).fetchone()
            if row:
                ts = datetime.fromisoformat(row["timestamp"])
                hours_ago = round((datetime.now(timezone.utc) - ts).total_seconds() / 3600, 1)
                return {"agent_name": row["agent_name"], "amount": row["amount"], "hours_ago": hours_ago}
        return None

    def get_daily_report(self):
        eet = timezone(timedelta(hours=2))
        now = datetime.now(eet)
        day_start = now.replace(hour=0, minute=0, second=0, microsecond=0)
        day_start_utc = day_start.astimezone(timezone.utc).isoformat()
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            rows = conn.execute("SELECT * FROM request_log WHERE timestamp>=?", (day_start_utc,)).fetchall()
            total = len(rows)
            total_amount = sum(r["amount"] for r in rows)
            unique_clients = len(set(r["cid"] for r in rows if r["cid"]))
            by_provider = {}
            by_agent = {}
            by_agent_amount = {}
            for r in rows:
                by_provider[r["provider"]] = by_provider.get(r["provider"], 0) + 1
                by_agent[r["agent_name"]] = by_agent.get(r["agent_name"], 0) + 1
                by_agent_amount[r["agent_name"]] = by_agent_amount.get(r["agent_name"], 0) + r["amount"]
            return {
                "date": now.strftime("%Y-%m-%d"),
                "total": total,
                "total_amount": total_amount,
                "unique_clients": unique_clients,
                "by_provider": by_provider,
                "by_agent": by_agent,
                "by_agent_amount": by_agent_amount,
            }

    def get_recent(self, limit=50):
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            return [dict(r) for r in conn.execute("SELECT * FROM request_log ORDER BY id DESC LIMIT ?", (limit,)).fetchall()]

    def get_total_count(self):
        with sqlite3.connect(self.db_path) as conn:
            return conn.execute("SELECT COUNT(*) FROM request_log").fetchone()[0]