import sqlite3
from pathlib import Path

DB_DIR = Path("data")
DB_DIR.mkdir(exist_ok=True)

DB_PATH = DB_DIR / "bot.db"


def get_connection():

    return sqlite3.connect(DB_PATH)


def init_db():

    conn = get_connection()

    cursor = conn.cursor()

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (

        id INTEGER PRIMARY KEY AUTOINCREMENT,

        telegram_id INTEGER,

        username TEXT,

        first_name TEXT,

        created_at TEXT,

        group_name TEXT,

        q1 TEXT,
        q2 TEXT,
        q3 TEXT,
        q4 TEXT,
        q5 TEXT,
        q6 TEXT,
        q7 TEXT,
        q8 TEXT,
        q9 TEXT,
        q10 TEXT
    )
    """)

    conn.commit()
    conn.close()


def user_exists(telegram_id):

    conn = get_connection()

    cursor = conn.cursor()

    cursor.execute(
        "SELECT id FROM users WHERE telegram_id=?",
        (telegram_id,)
    )

    result = cursor.fetchone()

    conn.close()

    return result is not None


def save_user(
    telegram_id,
    username,
    first_name,
    group_name,
    answers
):

    conn = get_connection()

    cursor = conn.cursor()

    cursor.execute(
        """
        INSERT INTO users (
            telegram_id,
            username,
            first_name,
            created_at,
            group_name,
            q1,q2,q3,q4,q5,
            q6,q7,q8,q9,q10
        )
        VALUES (
            ?,?,?,datetime('now'),
            ?,?,?,?,?,?,
            ?,?,?,?,?
        )
        """,
        (
            telegram_id,
            username,
            first_name,
            group_name,

            answers.get("q1"),
            answers.get("q2"),
            answers.get("q3"),
            answers.get("q4"),
            answers.get("q5"),
            answers.get("q6"),
            answers.get("q7"),
            answers.get("q8"),
            answers.get("q9"),
            answers.get("q10")
        )
    )

    conn.commit()
    conn.close()


def get_all_users():

    conn = get_connection()

    cursor = conn.cursor()

    cursor.execute(
        "SELECT * FROM users"
    )

    rows = cursor.fetchall()

    conn.close()

    return rows