database.py

import sqlite3
from datetime import datetime

DB_PATH = "threads_bot.db"

DEFAULT_SYSTEM_PROMPT = """Ты — живой пользователь Threads, разбираешься в VPN и интернет-безопасности.
Ты ненавязчиво представляешь сервис.
Правила:
— Только русский язык
— Звучи как живой человек, не реклама
— Не используй: "конечно", "разумеется", "безусловно", "отличный выбор"
— Не начинай с "Привет!" каждый раз
— Упоминай сервис максимум раз и только если уместно
— Никаких гарантий и громких обещаний"""

def get_conn():
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    return conn

def init_db():
    conn = get_conn()
    c = conn.cursor()

    c.execute("""
        CREATE TABLE IF NOT EXISTS accounts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            access_token TEXT NOT NULL,
            user_id TEXT NOT NULL,
            is_active INTEGER DEFAULT 1,
            added_at TEXT DEFAULT (datetime('now')),
            last_used TEXT
        )
    """)

    c.execute("""
        CREATE TABLE IF NOT EXISTS autopost_settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            account_id INTEGER UNIQUE,
            enabled INTEGER DEFAULT 0,
            posts_per_day INTEGER DEFAULT 2,
            topic TEXT DEFAULT 'VPN сервис',
            tone TEXT DEFAULT 'дружелюбный',
            service_name TEXT DEFAULT '',
            service_link TEXT DEFAULT '',
            last_posted TEXT,
            FOREIGN KEY(account_id) REFERENCES accounts(id)
        )
    """)

    c.execute("""
        CREATE TABLE IF NOT EXISTS rate_settings (
            account_id INTEGER PRIMARY KEY,
            reply_interval_sec INTEGER DEFAULT 30,
            post_interval_sec INTEGER DEFAULT 1800,
            max_replies_per_hour INTEGER DEFAULT 20,
            max_posts_per_day INTEGER DEFAULT 5,
            max_searches_per_hour INTEGER DEFAULT 60,
            FOREIGN KEY(account_id) REFERENCES accounts(id)
        )
    """)

    c.execute("""
        CREATE TABLE IF NOT EXISTS keywords (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            word TEXT UNIQUE NOT NULL,
            is_hashtag INTEGER DEFAULT 0,
            is_active INTEGER DEFAULT 1
        )
    """)

    c.execute("""
        CREATE TABLE IF NOT EXISTS action_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            account_id INTEGER,
            action_type TEXT,
            target_id TEXT,
            content TEXT,
            status TEXT DEFAULT 'ok',
            error_msg TEXT,
            created_at TEXT DEFAULT (datetime('now')),
            FOREIGN KEY(account_id) REFERENCES accounts(id)
        )
    """)

    c.execute("""
        CREATE TABLE IF NOT EXISTS processed_posts (
            thread_id TEXT PRIMARY KEY,
            account_id INTEGER,
            reply_text TEXT,
            processed_at TEXT DEFAULT (datetime('now'))
        )
    """)
    c.execute("""
        CREATE TABLE IF NOT EXISTS ai_settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            account_id INTEGER UNIQUE,
            system_prompt TEXT DEFAULT '',
            max_reply_chars INTEGER DEFAULT 200,
            ai_model TEXT DEFAULT 'gemini-2.0-flash',
            FOREIGN KEY(account_id) REFERENCES accounts(id)
        )
    """)

    defaults = [
        ("впн", 0), ("vpn", 0), ("посоветуйте впн", 0),
        ("какой впн", 0), ("нужен впн", 0), ("обойти блокировку", 0),
        ("заблокировали", 0), ("vpn для", 0),
        ("vpn", 1), ("впн", 1), ("интернетбезопасность", 1),
    ]
    for word, is_ht in defaults:
        c.execute("INSERT OR IGNORE INTO keywords (word, is_hashtag) VALUES (?,?)", (word, is_ht))

    conn.commit()
    conn.close()

def log_action(account_id, action_type, target_id=None, content=None, status='ok', error_msg=None):
    conn = get_conn()
    conn.execute("""
        INSERT INTO action_log (account_id, action_type, target_id, content, status, error_msg)
        VALUES (?,?,?,?,?,?)
    """, (account_id, action_type, target_id, content, status, error_msg))
    conn.commit()
    conn.close()

def get_rate_settings(account_id: int) -> dict:
    conn = get_conn()
    row = conn.execute(
        "SELECT * FROM rate_settings WHERE account_id=?", (account_id,)
    ).fetchone()
    conn.close()
    if row:
        return dict(row)
    return {
        "reply_interval_sec": 30,
        "post_interval_sec": 1800,
        "max_replies_per_hour": 20,
        "max_posts_per_day": 5,
        "max_searches_per_hour": 60,
    }

def save_rate_settings(account_id: int, settings: dict):
    conn = get_conn()
    conn.execute("""
        INSERT INTO rate_settings
            (account_id, reply_interval_sec, post_interval_sec,
             max_replies_per_hour, max_posts_per_day, max_searches_per_hour)
        VALUES (?,?,?,?,?,?)
        ON CONFLICT(account_id) DO UPDATE SET
            reply_interval_sec=excluded.reply_interval_sec,
            post_interval_sec=excluded.post_interval_sec,
            max_replies_per_hour=excluded.max_replies_per_hour,
            max_posts_per_day=excluded.max_posts_per_day,
            max_searches_per_hour=excluded.max_searches_per_hour
    """, (
        account_id,
        settings.get("reply_interval_sec", 30),
        settings.get("post_interval_sec", 1800),
        settings.get("max_replies_per_hour", 20),
        settings.get("max_posts_per_day", 5),
        settings.get("max_searches_per_hour", 60),
    ))
    conn.commit()
    conn.close()

def get_ai_settings(account_id: int) -> dict:
    conn = get_conn()
    row = conn.execute(
        "SELECT * FROM ai_settings WHERE account_id=?", (account_id,)
    ).fetchone()
    conn.close()
    if row:
        d = dict(row)
        if not d.get("system_prompt"):
            d["system_prompt"] = DEFAULT_SYSTEM_PROMPT
        return d
    return {
        "account_id": account_id,
        "system_prompt": DEFAULT_SYSTEM_PROMPT,
        "max_reply_chars": 200,
        "ai_model": "gemini-2.0-flash",
    }

def save_ai_settings(account_id: int, system_prompt: str, max_reply_chars: int = 200, ai_model: str = "gemini-2.0-flash"):
    conn = get_conn()
    conn.execute("""
        INSERT INTO ai_settings (account_id, system_prompt, max_reply_chars, ai_model)
        VALUES (?,?,?,?)
        ON CONFLICT(account_id) DO UPDATE SET
            system_prompt=excluded.system_prompt,
            max_reply_chars=excluded.max_reply_chars,
            ai_model=excluded.ai_model
    """, (account_id, system_prompt, max_reply_chars, ai_model))
    conn.commit()
    conn.close()