import sqlite3
import json
import time
import os
import logging
from datetime import datetime
from typing import Optional, Dict, Any, List, Tuple
from contextlib import contextmanager


DB_PATH = "bot.db"
logger = logging.getLogger("DB")


@contextmanager
def get_db():
    conn = sqlite3.connect(DB_PATH, timeout=10)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    try:
        yield conn
    finally:
        conn.close()


def init_db():
    with get_db() as conn:
        cursor = conn.cursor()

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS group_accounts (
                group_id INTEGER PRIMARY KEY,
                owner_id INTEGER,
                owner_name TEXT,
                token TEXT,
                consent_data TEXT,
                session_data TEXT,
                fp TEXT,
                ddg1 TEXT,
                blocked INTEGER DEFAULT 0,
                revoked INTEGER DEFAULT 0,
                updating INTEGER DEFAULT 0,
                has_credentials INTEGER DEFAULT 0,
                week_info TEXT,
                token_expires_at REAL
            )
        """)

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS notes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                date TEXT NOT NULL,
                subject TEXT,
                text TEXT,
                created REAL NOT NULL
            )
        """)

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS ideas (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                user_name TEXT,
                idea TEXT NOT NULL,
                status TEXT DEFAULT 'новая',
                reply TEXT,
                timestamp TEXT NOT NULL
            )
        """)

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS reviews (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                user_name TEXT,
                text TEXT,
                rating INTEGER,
                timestamp TEXT NOT NULL,
                status TEXT DEFAULT 'новая'
            )
        """)

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS ip_whitelist (
                ip TEXT PRIMARY KEY,
                added_at REAL,
                expires_at REAL
            )
        ''')

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS bug_reports (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                user_name TEXT,
                text TEXT NOT NULL,
                reply_to_msg TEXT,
                reply_to_cmd TEXT,
                status TEXT DEFAULT 'новая',
                admin_reply TEXT,
                timestamp TEXT NOT NULL
            )
        ''')

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS dashboard_tokens (
                token TEXT PRIMARY KEY,
                user_id INTEGER NOT NULL,
                ip TEXT NOT NULL,
                expires_at TEXT NOT NULL,
                created_at TEXT NOT NULL
            )
        ''')

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS log_ip_whitelist (
                ip TEXT PRIMARY KEY,
                added_at REAL
            )
        ''')

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS changelog (
                version TEXT PRIMARY KEY,
                date TEXT NOT NULL,
                message TEXT NOT NULL
            )
        """)

        # ALTER TABLE для обратной совместимости со старыми БД
        try:
            cursor.execute("ALTER TABLE users ADD COLUMN schedule_format TEXT DEFAULT 'text'")
        except Exception as e:
            logger.debug(f"Column schedule_format may already exist: {e}")
        try:
            cursor.execute("ALTER TABLE users ADD COLUMN account_provider TEXT DEFAULT ''")
        except Exception as e:
            logger.debug(f"Column account_provider may already exist: {e}")
        try:
            cursor.execute("ALTER TABLE reviews ADD COLUMN status TEXT DEFAULT 'новая'")
        except Exception as e:
            logger.debug(f"Column status may already exist: {e}")
        try:
            cursor.execute("ALTER TABLE group_accounts ADD COLUMN token_expires_at REAL")
        except Exception as e:
            logger.debug(f"Column token_expires_at may already exist: {e}")
        try:
            cursor.execute("ALTER TABLE users ADD COLUMN notify_reply INTEGER DEFAULT 1")
        except Exception as e:
            logger.debug(f"Column notify_reply may already exist: {e}")

        conn.commit()


def register_user(user_id: int, key: str) -> Optional[int]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT group_id FROM keys WHERE key = ?", (key,))
        row = cursor.fetchone()
        if not row:
            return None
        group_id = row["group_id"]

        cursor.execute("DELETE FROM keys WHERE key = ?", (key,))

        cursor.execute("SELECT group_id FROM users WHERE user_id = ?", (user_id,))
        existing = cursor.fetchone()
        if existing:
            cursor.execute(
                "UPDATE users SET group_id = ?, registered_at = ? WHERE user_id = ?",
                (group_id, time.time(), user_id)
            )
        else:
            cursor.execute(
                "INSERT INTO users (user_id, group_id, registered_at) VALUES (?, ?, ?)",
                (user_id, group_id, time.time())
            )
        conn.commit()
        return group_id


def get_user_group(user_id: int) -> Optional[int]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT group_id FROM users WHERE user_id = ?", (user_id,))
        row = cursor.fetchone()
        return row["group_id"] if row else None


def get_user(user_id: int) -> Optional[Dict[str, Any]]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users WHERE user_id = ?", (user_id,))
        row = cursor.fetchone()
        if row:
            return dict(row)
        return None


def delete_user(user_id: int) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM users WHERE user_id = ?", (user_id,))
        conn.commit()
        return cursor.rowcount > 0


def get_all_users() -> List[Tuple[int, int]]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT user_id, group_id FROM users")
        return [(row["user_id"], row["group_id"]) for row in cursor.fetchall()]


def get_users_by_group(group_id: int) -> List[Tuple[int, int]]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT user_id, group_id FROM users WHERE group_id = ?", (group_id,))
        return [(row["user_id"], row["group_id"]) for row in cursor.fetchall()]


def add_key(key: str, group_id: int) -> None:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT OR REPLACE INTO keys (key, group_id, created_at) VALUES (?, ?, ?)",
            (key, group_id, time.time())
        )
        conn.commit()


def get_key(key: str) -> Optional[Dict[str, Any]]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM keys WHERE key = ?", (key,))
        row = cursor.fetchone()
        return dict(row) if row else None


def delete_key(key: str) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM keys WHERE key = ?", (key,))
        conn.commit()
        return cursor.rowcount > 0


def delete_keys_by_group(group_id: int) -> int:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM keys WHERE group_id = ?", (group_id,))
        conn.commit()
        return cursor.rowcount

def save_bot_version(version: str, message: str) -> None:
    """Вставляет или обновляет запись о версии бота."""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(
            """
            INSERT INTO changelog (version, date, message)
            VALUES (?, ?, ?)
            ON CONFLICT(version) DO UPDATE SET
                date = excluded.date,
                message = excluded.message
            """,
            (version, datetime.now().isoformat(), message)
        )
        conn.commit()
        
def save_group_account(group_id: int, data: Dict[str, Any]) -> None:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT OR REPLACE INTO group_accounts
            (group_id, owner_id, owner_name, token, consent_data, session_data, fp, ddg1,
             blocked, revoked, updating, has_credentials, week_info, token_expires_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            group_id,
            data.get("owner_id"),
            data.get("owner_name"),
            data.get("token"),
            json.dumps(data.get("consent")) if data.get("consent") else None,
            json.dumps(data.get("session")) if data.get("session") else None,
            data.get("fp"),
            data.get("ddg1"),
            1 if data.get("blocked") else 0,
            1 if data.get("revoked") else 0,
            1 if data.get("updating") else 0,
            1 if data.get("has_credentials") else 0,
            json.dumps(data.get("week_info")) if data.get("week_info") else None,
            data.get("token_expires_at")          # ← новое поле
        ))
        conn.commit()


def get_group_account(group_id: int) -> Optional[Dict[str, Any]]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM group_accounts WHERE group_id = ?", (group_id,))
        row = cursor.fetchone()
        if row:
            data = dict(row)
            if data:
                data["token_expires_at"] = row["token_expires_at"]   # может быть None
            if data.get("consent_data"):
                data["consent"] = json.loads(data["consent_data"])
            if data.get("session_data"):
                data["session"] = json.loads(data["session_data"])
            if data.get("week_info"):
                data["week_info"] = json.loads(data["week_info"])
            data["blocked"] = bool(data.get("blocked"))
            data["revoked"] = bool(data.get("revoked"))
            data["updating"] = bool(data.get("updating"))
            data["has_credentials"] = bool(data.get("has_credentials"))
            return data
        return None


def get_all_group_accounts() -> Dict[int, Dict[str, Any]]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT group_id FROM group_accounts")
        result = {}
        for row in cursor.fetchall():
            gid = row["group_id"]
            acc = get_group_account(gid)
            if acc:
                result[gid] = acc
        return result


def update_group_account_field(group_id: int, field: str, value: Any) -> None:
    # Белый список полей, которые можно безопасно менять
    allowed_fields = {
        "token", "blocked", "revoked", "updating",
        "has_credentials", "token_expires_at"
    }
    if field not in allowed_fields:
        raise ValueError(f"Поле '{field}' не разрешено для прямого обновления")
    
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(
            f"UPDATE group_accounts SET {field} = ? WHERE group_id = ?",
            (value, group_id)
        )
        conn.commit()


def delete_group_account(group_id: int) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM group_accounts WHERE group_id = ?", (group_id,))
        conn.commit()
        return cursor.rowcount > 0


def add_note(user_id: int, date: str, subject: str, text: str) -> int:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO notes (user_id, date, subject, text, created) VALUES (?, ?, ?, ?, ?)",
            (user_id, date, subject, text, time.time())
        )
        conn.commit()
        return cursor.lastrowid


def get_notes_by_user(user_id: int) -> List[Dict[str, Any]]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM notes WHERE user_id = ? ORDER BY date, created", (user_id,))
        return [dict(row) for row in cursor.fetchall()]


def delete_note(note_id: int) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM notes WHERE id = ?", (note_id,))
        conn.commit()
        return cursor.rowcount > 0


def add_idea(user_id: int, user_name: str, idea_text: str) -> int:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO ideas (user_id, user_name, idea, status, timestamp) VALUES (?, ?, ?, ?, ?)",
            (user_id, user_name, idea_text, "новая", datetime.now().isoformat())
        )
        conn.commit()
        return cursor.lastrowid


def get_all_ideas() -> List[Dict[str, Any]]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM ideas ORDER BY id DESC")
        return [dict(row) for row in cursor.fetchall()]


def update_idea_status(idea_id: int, new_status: str, reply: str = None) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        if reply:
            cursor.execute("UPDATE ideas SET status = ?, reply = ? WHERE id = ?", (new_status, reply, idea_id))
        else:
            cursor.execute("UPDATE ideas SET status = ? WHERE id = ?", (new_status, idea_id))
        conn.commit()
        return cursor.rowcount > 0


def delete_idea(idea_id: int) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM ideas WHERE id = ?", (idea_id,))
        conn.commit()
        return cursor.rowcount > 0

def get_idea_by_id(idea_id: int) -> Optional[Dict[str, Any]]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM ideas WHERE id = ?", (idea_id,))
        row = cursor.fetchone()
        return dict(row) if row else None

def get_review_by_id(review_id: int) -> Optional[Dict[str, Any]]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM reviews WHERE id = ?", (review_id,))
        row = cursor.fetchone()
        return dict(row) if row else None

def update_review_status(review_id: int, status: str) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("UPDATE reviews SET status = ? WHERE id = ?", (status, review_id))
        conn.commit()
        return cursor.rowcount > 0


def add_review(user_id: int, user_name: str, text: str, rating: int) -> int:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO reviews (user_id, user_name, text, rating, timestamp) VALUES (?, ?, ?, ?, ?)",
            (user_id, user_name, text, rating, datetime.now().isoformat())
        )
        conn.commit()
        return cursor.lastrowid


def get_reviews_page(page: int = 1, per_page: int = 10) -> Tuple[List[Dict[str, Any]], int]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM reviews")
        total = cursor.fetchone()[0]
        total_pages = (total + per_page - 1) // per_page if total > 0 else 1

        offset = (page - 1) * per_page
        cursor.execute(
            "SELECT * FROM reviews ORDER BY timestamp DESC LIMIT ? OFFSET ?",
            (per_page, offset)
        )
        reviews = [dict(row) for row in cursor.fetchall()]
        return reviews, total_pages


def get_user_reviews(user_id: int) -> List[Dict[str, Any]]:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM reviews WHERE user_id = ? ORDER BY timestamp DESC", (user_id,))
        return [dict(row) for row in cursor.fetchall()]

def delete_review(review_id: int) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM reviews WHERE id = ?", (review_id,))
        conn.commit()
        return cursor.rowcount > 0


def update_user_notified_version(user_id: int, version: str) -> None:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("UPDATE users SET notified_version = ? WHERE user_id = ?", (version, user_id))
        conn.commit()

def get_user_format(user_id: int) -> str:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT schedule_format FROM users WHERE user_id = ?", (user_id,))
        row = cursor.fetchone()
        return row["schedule_format"] if row and row["schedule_format"] else "text"

def set_user_format(user_id: int, fmt: str) -> None:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("UPDATE users SET schedule_format = ? WHERE user_id = ?", (fmt, user_id))
        conn.commit()

def get_user_notify_reply(user_id: int) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT notify_reply FROM users WHERE user_id = ?", (user_id,))
        row = cursor.fetchone()
        return bool(row["notify_reply"]) if row and row["notify_reply"] is not None else True

def set_user_notify_reply(user_id: int, value: bool) -> None:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("UPDATE users SET notify_reply = ? WHERE user_id = ?", (1 if value else 0, user_id))
        conn.commit()

def get_all_ips():
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT ip, added_at, expires_at FROM ip_whitelist")
        rows = cursor.fetchall()
        ips = {}
        for row in rows:
            ip = row["ip"]
            ips[ip] = {
                "added_at": row["added_at"],
                "expires_at": row["expires_at"]
            }
        return ips

def add_ip(ip, expires_at):
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT OR REPLACE INTO ip_whitelist (ip, added_at, expires_at) VALUES (?, ?, ?)",
            (ip, time.time(), expires_at)
        )
        conn.commit()

def remove_ip(ip):
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM ip_whitelist WHERE ip = ?", (ip,))
        conn.commit()

def get_log_ips():
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT ip FROM log_ip_whitelist")
        return {row["ip"] for row in cursor.fetchall()}

def add_log_ip(ip):
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT OR REPLACE INTO log_ip_whitelist (ip, added_at) VALUES (?, ?)",
            (ip, time.time())
        )
        conn.commit()

def remove_log_ip(ip):
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM log_ip_whitelist WHERE ip = ?", (ip,))
        conn.commit()

def add_bug_report(user_id: int, user_name: str, title: str, text: str, severity: str = "средняя", reply_to_msg: str = None, reply_to_cmd: str = None) -> int:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO bug_reports (user_id, user_name, text, reply_to_msg, reply_to_cmd, status, timestamp) VALUES (?, ?, ?, ?, ?, ?, ?)",
            (user_id, user_name, f"📌 {title}\n📋 {text}\n🔴 Важность: {severity}", reply_to_msg, reply_to_cmd, "новая", datetime.now().isoformat())
        )
        conn.commit()
        return cursor.lastrowid

def get_all_bug_reports() -> list:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM bug_reports ORDER BY id DESC")
        return [dict(row) for row in cursor.fetchall()]

def update_bug_status(bug_id: int, status: str, admin_reply: str = None) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        if admin_reply:
            cursor.execute("UPDATE bug_reports SET status = ?, admin_reply = ? WHERE id = ?", (status, admin_reply, bug_id))
        else:
            cursor.execute("UPDATE bug_reports SET status = ? WHERE id = ?", (status, bug_id))
        conn.commit()
        return cursor.rowcount > 0

def delete_bug_report(bug_id: int) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM bug_reports WHERE id = ?", (bug_id,))
        conn.commit()
        return cursor.rowcount > 0

def get_user_bug_reports(user_id: int) -> list:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM bug_reports WHERE user_id = ? ORDER BY id DESC", (user_id,))
        return [dict(row) for row in cursor.fetchall()]

def get_bug_report(bug_id: int) -> dict:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM bug_reports WHERE id = ?", (bug_id,))
        row = cursor.fetchone()
        return dict(row) if row else None

def create_dashboard_token(user_id: int, ip: str, token: str, expires_at: str) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        try:
            cursor.execute("DELETE FROM dashboard_tokens WHERE user_id = ?", (user_id,))
            cursor.execute(
                "INSERT INTO dashboard_tokens (user_id, token, ip, expires_at, created_at) VALUES (?, ?, ?, ?, ?)",
                (user_id, token, ip, expires_at, datetime.now().isoformat())
            )
            conn.commit()
            return True
        except Exception as e:
            logger.warning(f"Failed to create dashboard token for user {user_id}: {e}")
            return False

def get_dashboard_token(token: str) -> dict:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM dashboard_tokens WHERE token = ?", (token,))
        row = cursor.fetchone()
        return dict(row) if row else None

def delete_expired_dashboard_tokens() -> int:
    with get_db() as conn:
        cursor = conn.cursor()
        now = datetime.now().isoformat()
        cursor.execute("DELETE FROM dashboard_tokens WHERE expires_at < ?", (now,))
        conn.commit()
        return cursor.rowcount

def delete_dashboard_token(user_id: int) -> bool:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM dashboard_tokens WHERE user_id = ?", (user_id,))
        conn.commit()
        return cursor.rowcount > 0

def get_dashboard_token_by_user(user_id: int) -> dict:
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM dashboard_tokens WHERE user_id = ?", (user_id,))
        row = cursor.fetchone()
        return dict(row) if row else None

def set_account_provider(user_id: int, provider_name: str):
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("UPDATE users SET account_provider = ? WHERE user_id = ?", (provider_name, user_id))
        conn.commit()

def get_changelog(limit: int = 20) -> list:
    with get_db() as conn:
        cursor = conn.cursor()
        if limit and limit > 0:
            cursor.execute("SELECT version, date, message FROM changelog ORDER BY date DESC LIMIT ?", (limit,))
        else:
            cursor.execute("SELECT version, date, message FROM changelog ORDER BY date DESC")
        return [dict(row) for row in cursor.fetchall()]