"""Своя БД ловца — без привязки к панели OZPAY."""

from __future__ import annotations

import json
import sqlite3
import time
from typing import Optional

from config import DATA_DIR, DB_PATH, SESSIONS_DIR


def get_conn() -> sqlite3.Connection:
    DATA_DIR.mkdir(parents=True, exist_ok=True)
    SESSIONS_DIR.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS owners (
            tg_id INTEGER PRIMARY KEY,
            first_name TEXT,
            last_name TEXT,
            username TEXT,
            created_at REAL NOT NULL,
            seen_at REAL NOT NULL,
            balance REAL NOT NULL DEFAULT 0
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS sessions (
            owner_tg_id INTEGER PRIMARY KEY,
            phone TEXT,
            tg_user_id INTEGER,
            first_name TEXT,
            last_name TEXT,
            username TEXT,
            status TEXT NOT NULL DEFAULT 'logged_out',
            updated_at REAL NOT NULL,
            FOREIGN KEY (owner_tg_id) REFERENCES owners(tg_id)
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS watch_settings (
            owner_tg_id INTEGER PRIMARY KEY,
            value TEXT NOT NULL,
            FOREIGN KEY (owner_tg_id) REFERENCES owners(tg_id)
        )
        """
    )
    cur.execute("PRAGMA table_info(owners)")
    owner_cols = {row[1] for row in cur.fetchall()}
    if "balance" not in owner_cols:
        cur.execute("ALTER TABLE owners ADD COLUMN balance REAL NOT NULL DEFAULT 0")
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS invoices (
            invoice_id INTEGER PRIMARY KEY,
            owner_tg_id INTEGER NOT NULL,
            amount_usd REAL NOT NULL,
            status TEXT NOT NULL DEFAULT 'active',
            pay_url TEXT,
            mini_url TEXT,
            created_at REAL NOT NULL,
            paid_at REAL
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS balance_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            owner_tg_id INTEGER NOT NULL,
            amount REAL NOT NULL,
            reason TEXT,
            actor_tg_id INTEGER,
            created_at REAL NOT NULL
        )
        """
    )
    conn.commit()
    return conn


def session_path(owner_tg_id: int) -> str:
    SESSIONS_DIR.mkdir(parents=True, exist_ok=True)
    return str(SESSIONS_DIR / f"u{int(owner_tg_id)}")


def upsert_owner(user: dict) -> dict:
    tg_id = int(user["id"])
    now = time.time()
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("SELECT tg_id FROM owners WHERE tg_id = ?", (tg_id,))
    exists = cur.fetchone() is not None
    if exists:
        cur.execute(
            """
            UPDATE owners
            SET first_name = ?, last_name = ?, username = ?, seen_at = ?
            WHERE tg_id = ?
            """,
            (
                user.get("first_name") or "",
                user.get("last_name") or "",
                user.get("username") or "",
                now,
                tg_id,
            ),
        )
    else:
        cur.execute(
            """
            INSERT INTO owners (tg_id, first_name, last_name, username, created_at, seen_at)
            VALUES (?, ?, ?, ?, ?, ?)
            """,
            (
                tg_id,
                user.get("first_name") or "",
                user.get("last_name") or "",
                user.get("username") or "",
                now,
                now,
            ),
        )
    conn.commit()
    conn.close()
    return get_owner(tg_id)


def get_owner(tg_id: int) -> dict:
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        "SELECT tg_id, first_name, last_name, username, balance FROM owners WHERE tg_id = ?",
        (int(tg_id),),
    )
    row = cur.fetchone()
    conn.close()
    if not row:
        return {
            "id": int(tg_id),
            "first_name": "",
            "last_name": "",
            "username": "",
            "balance": 0,
        }
    return {
        "id": int(row["tg_id"]),
        "first_name": row["first_name"] or "",
        "last_name": row["last_name"] or "",
        "username": row["username"] or "",
        "balance": float(row["balance"] or 0),
    }


def list_session_owners() -> list[int]:
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("SELECT owner_tg_id FROM sessions")
    rows = [int(row[0]) for row in cur.fetchall()]
    conn.close()
    extra = []
    if SESSIONS_DIR.is_dir():
        for path in SESSIONS_DIR.glob("u*.session"):
            try:
                extra.append(int(path.stem[1:]))
            except ValueError:
                continue
    return sorted(set(rows) | set(extra))


def save_session_meta(owner_tg_id: int, *, phone: Optional[str], me: Optional[dict], status: str) -> None:
    me = me or {}
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        """
        INSERT INTO sessions (
            owner_tg_id, phone, tg_user_id, first_name, last_name, username, status, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(owner_tg_id) DO UPDATE SET
            phone = excluded.phone,
            tg_user_id = excluded.tg_user_id,
            first_name = excluded.first_name,
            last_name = excluded.last_name,
            username = excluded.username,
            status = excluded.status,
            updated_at = excluded.updated_at
        """,
        (
            int(owner_tg_id),
            phone,
            me.get("id"),
            me.get("first_name") or "",
            me.get("last_name") or "",
            me.get("username") or "",
            status,
            time.time(),
        ),
    )
    conn.commit()
    conn.close()


def find_owner_by_userbot_id(tg_user_id: int) -> Optional[int]:
    if not tg_user_id:
        return None
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        "SELECT owner_tg_id FROM sessions WHERE tg_user_id = ?",
        (int(tg_user_id),),
    )
    row = cur.fetchone()
    conn.close()
    return int(row[0]) if row else None


def delete_session_meta(owner_tg_id: int) -> None:
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("DELETE FROM sessions WHERE owner_tg_id = ?", (int(owner_tg_id),))
    conn.commit()
    conn.close()


def _default_watch(owner_tg_id: int) -> dict:
    return {
        "enabled": True,
        "keywords": [],
        "chats": [],
        "alert_chat_id": int(owner_tg_id),
        "max_chars": None,
    }


def _normalize_keyword(value) -> str:
    return " ".join(str(value or "").split())


def _normalize_chat(value) -> Optional[dict]:
    if not isinstance(value, dict):
        return None
    raw_id = value.get("id")
    if raw_id in (None, ""):
        return None
    try:
        chat_id = int(raw_id)
    except (TypeError, ValueError):
        return None
    title = " ".join(str(value.get("title") or chat_id).split())[:120]
    kind = str(value.get("kind") or "").strip()[:16]
    return {"id": chat_id, "title": title or str(chat_id), "kind": kind}


def _parse_watch(raw, owner_tg_id: int) -> dict:
    data = _default_watch(owner_tg_id)
    if not raw:
        return data
    if isinstance(raw, dict):
        payload = raw
    else:
        try:
            payload = json.loads(raw)
        except (TypeError, ValueError, json.JSONDecodeError):
            return data
    if not isinstance(payload, dict):
        return data
    if "enabled" in payload:
        data["enabled"] = bool(payload.get("enabled"))
    keywords = []
    seen = set()
    for item in payload.get("keywords") or []:
        word = _normalize_keyword(item)
        key = word.casefold()
        if not word or key in seen:
            continue
        seen.add(key)
        keywords.append(word[:80])
        if len(keywords) >= 50:
            break
    data["keywords"] = keywords
    chats = []
    seen_ids = set()
    for item in payload.get("chats") or []:
        chat = _normalize_chat(item)
        if not chat or chat["id"] in seen_ids:
            continue
        seen_ids.add(chat["id"])
        chats.append(chat)
        if len(chats) >= 100:
            break
    data["chats"] = chats
    alert = payload.get("alert_chat_id")
    if alert in (None, ""):
        data["alert_chat_id"] = int(owner_tg_id)
    else:
        try:
            data["alert_chat_id"] = int(alert)
        except (TypeError, ValueError):
            data["alert_chat_id"] = int(owner_tg_id)
    if "max_chars" in payload:
        raw_max = payload.get("max_chars")
        if raw_max in (None, ""):
            data["max_chars"] = None
        else:
            try:
                limit = int(raw_max)
            except (TypeError, ValueError):
                limit = None
            if limit is None or limit <= 0:
                data["max_chars"] = None
            else:
                data["max_chars"] = min(limit, 4096)
    return data


def get_watch_settings(owner_tg_id: int) -> dict:
    owner_tg_id = int(owner_tg_id)
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        "SELECT value FROM watch_settings WHERE owner_tg_id = ?",
        (owner_tg_id,),
    )
    row = cur.fetchone()
    conn.close()
    return _parse_watch(row[0] if row else None, owner_tg_id)


def save_watch_settings(owner_tg_id: int, payload: dict) -> dict:
    owner_tg_id = int(owner_tg_id)
    current = get_watch_settings(owner_tg_id)
    if not isinstance(payload, dict):
        payload = {}
    merged = {
        "enabled": payload["enabled"] if "enabled" in payload else current["enabled"],
        "keywords": payload["keywords"] if "keywords" in payload else current["keywords"],
        "chats": payload["chats"] if "chats" in payload else current["chats"],
        "alert_chat_id": payload["alert_chat_id"] if "alert_chat_id" in payload else current["alert_chat_id"],
        "max_chars": payload["max_chars"] if "max_chars" in payload else current["max_chars"],
    }
    data = _parse_watch(merged, owner_tg_id)
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        """
        INSERT INTO watch_settings (owner_tg_id, value) VALUES (?, ?)
        ON CONFLICT(owner_tg_id) DO UPDATE SET value = excluded.value
        """,
        (owner_tg_id, json.dumps(data, ensure_ascii=False)),
    )
    conn.commit()
    conn.close()
    return json.loads(json.dumps(data))


def ensure_owner(tg_id: int) -> dict:
    tg_id = int(tg_id)
    now = time.time()
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        """
        INSERT OR IGNORE INTO owners (tg_id, first_name, last_name, username, created_at, seen_at, balance)
        VALUES (?, '', '', '', ?, ?, 0)
        """,
        (tg_id, now, now),
    )
    conn.commit()
    conn.close()
    return get_owner(tg_id)


def add_balance(tg_id: int, amount, *, reason: str = "", actor_tg_id: Optional[int] = None) -> dict:
    tg_id = int(tg_id)
    try:
        delta = round(float(amount), 2)
    except (TypeError, ValueError) as exc:
        raise ValueError("Некорректная сумма") from exc
    if abs(delta) < 0.01:
        raise ValueError("Сумма слишком маленькая")
    ensure_owner(tg_id)
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        "UPDATE owners SET balance = ROUND(balance + ?, 2) WHERE tg_id = ?",
        (delta, tg_id),
    )
    cur.execute(
        """
        INSERT INTO balance_log (owner_tg_id, amount, reason, actor_tg_id, created_at)
        VALUES (?, ?, ?, ?, ?)
        """,
        (tg_id, delta, (reason or "")[:120], actor_tg_id, time.time()),
    )
    conn.commit()
    conn.close()
    return get_owner(tg_id)


def list_owners(query: str = "", limit: int = 40) -> list[dict]:
    needle = " ".join(str(query or "").split())
    conn = get_conn()
    cur = conn.cursor()
    if needle:
        like = f"%{needle}%"
        if needle.lstrip("-").isdigit():
            cur.execute(
                """
                SELECT tg_id, first_name, last_name, username, balance, seen_at
                FROM owners
                WHERE tg_id = ? OR CAST(tg_id AS TEXT) LIKE ? OR username LIKE ? OR first_name LIKE ?
                ORDER BY seen_at DESC
                LIMIT ?
                """,
                (int(needle), like, like, like, int(limit)),
            )
        else:
            cur.execute(
                """
                SELECT tg_id, first_name, last_name, username, balance, seen_at
                FROM owners
                WHERE username LIKE ? OR first_name LIKE ? OR last_name LIKE ?
                ORDER BY seen_at DESC
                LIMIT ?
                """,
                (like, like, like, int(limit)),
            )
    else:
        cur.execute(
            """
            SELECT tg_id, first_name, last_name, username, balance, seen_at
            FROM owners
            ORDER BY seen_at DESC
            LIMIT ?
            """,
            (int(limit),),
        )
    rows = [
        {
            "id": int(row["tg_id"]),
            "first_name": row["first_name"] or "",
            "last_name": row["last_name"] or "",
            "username": row["username"] or "",
            "balance": float(row["balance"] or 0),
        }
        for row in cur.fetchall()
    ]
    conn.close()
    return rows


def save_invoice(invoice_id: int, owner_tg_id: int, amount_usd: float, pay_url: str = "", mini_url: str = "") -> dict:
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        """
        INSERT INTO invoices (invoice_id, owner_tg_id, amount_usd, status, pay_url, mini_url, created_at)
        VALUES (?, ?, ?, 'active', ?, ?, ?)
        ON CONFLICT(invoice_id) DO UPDATE SET
            pay_url = excluded.pay_url,
            mini_url = excluded.mini_url
        """,
        (int(invoice_id), int(owner_tg_id), float(amount_usd), pay_url or "", mini_url or "", time.time()),
    )
    conn.commit()
    conn.close()
    return {"invoice_id": int(invoice_id), "status": "active", "amount_usd": float(amount_usd)}


def get_invoice(invoice_id: int) -> Optional[dict]:
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        "SELECT invoice_id, owner_tg_id, amount_usd, status, pay_url, mini_url FROM invoices WHERE invoice_id = ?",
        (int(invoice_id),),
    )
    row = cur.fetchone()
    conn.close()
    if not row:
        return None
    return {
        "invoice_id": int(row["invoice_id"]),
        "owner_tg_id": int(row["owner_tg_id"]),
        "amount_usd": float(row["amount_usd"] or 0),
        "status": row["status"] or "active",
        "pay_url": row["pay_url"] or "",
        "mini_url": row["mini_url"] or "",
    }


def list_active_invoices(limit: int = 40) -> list[dict]:
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        """
        SELECT invoice_id, owner_tg_id, amount_usd, status
        FROM invoices
        WHERE status = 'active'
        ORDER BY created_at DESC
        LIMIT ?
        """,
        (int(limit),),
    )
    rows = [
        {
            "invoice_id": int(row["invoice_id"]),
            "owner_tg_id": int(row["owner_tg_id"]),
            "amount_usd": float(row["amount_usd"] or 0),
            "status": row["status"],
        }
        for row in cur.fetchall()
    ]
    conn.close()
    return rows


def mark_invoice_paid(invoice_id: int) -> Optional[dict]:
    invoice = get_invoice(invoice_id)
    if not invoice:
        return None
    if invoice["status"] == "paid":
        return invoice
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        "UPDATE invoices SET status = 'paid', paid_at = ? WHERE invoice_id = ? AND status != 'paid'",
        (time.time(), int(invoice_id)),
    )
    changed = cur.rowcount
    conn.commit()
    conn.close()
    if not changed:
        return get_invoice(invoice_id)
    owner = add_balance(
        invoice["owner_tg_id"],
        invoice["amount_usd"],
        reason=f"crypto:{invoice_id}",
        actor_tg_id=None,
    )
    invoice = get_invoice(invoice_id) or invoice
    invoice["owner"] = owner
    invoice["credited"] = True
    return invoice