import sqlite3

con = sqlite3.connect("data.db")

cur = con.cursor()

cur.execute(
    """CREATE TABLE IF NOT EXISTS messages (id INTEGER PRIMARY KEY AUTOINCREMENT, 
                                            author TEXT NOT NULL, 
                                            recipient TEXT NOT NULL,
                                            content TEXT
                                            );
    """
)
cur.close()
con.close()


def DB_get_msg(id: int = None):
    con = sqlite3.connect("data.db")
    cur = con.cursor()
    cur.execute("SELECT * FROM messages WHERE id=?", (id,))
    row = cur.fetchone()
    cur.close()
    con.close()
    return row


def DB_new_msg(
    author: str = "Unknown", recipient: str = "Unknown", content: str = "N/A"
):
    con = sqlite3.connect("data.db")
    cur = con.cursor()
    data = (author, recipient, content)
    cur.execute(
        "INSERT INTO messages (author, recipient, content) VALUES(?, ?, ?)", (data)
    )
    con.commit()
    cur.close()
    con.close()


def DB_get_recent_msgs(last: int = 10):
    con = sqlite3.connect("data.db")
    cur = con.cursor()
    cur.execute("SELECT * FROM messages ORDER BY id DESC")
    rows = cur.fetchmany(last)
    con.close()
    print(rows)
    return rows