import sqlite3

# Init DB and set CRUD functions
class AuthDB:
    def __init__(self, db_path="auth_keys.db"):
        self.db_path = db_path
        self._init_db()

    def _init_db(self):
        with sqlite3.connect(self.db_path) as conn:
            c = conn.cursor()
            c.execute('''
                CREATE TABLE IF NOT EXISTS verified_users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    irc_nick TEXT UNIQUE NOT NULL,
                    site_username TEXT NOT NULL,
                    irc_key TEXT NOT NULL,
                    group_id INTEGER NOT NULL DEFAULT 4,
                    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            conn.commit()

    def verify_user(self, irc_nick, site_username, irc_key, group_id):
        with sqlite3.connect(self.db_path) as conn:
            c = conn.cursor()
            c.execute('''
                INSERT INTO verified_users (irc_nick, site_username, irc_key, group_id)
                VALUES (?, ?, ?, ?)
                ON CONFLICT(irc_nick) DO UPDATE SET
                    site_username = excluded.site_username,
                    irc_key = excluded.irc_key,
                    group_id = excluded.group_id,
                    joined_at = CURRENT_TIMESTAMP
            ''', (irc_nick, site_username, irc_key, group_id))
            conn.commit()

    def get_verified_user(self, irc_nick):
        with sqlite3.connect(self.db_path) as conn:
            c = conn.cursor()
            c.execute('SELECT site_username, irc_key, group_id FROM verified_users WHERE irc_nick = ?', (irc_nick,))
            return c.fetchone()

    def remove_verified_user(self, irc_nick):
        with sqlite3.connect(self.db_path) as conn:
            c = conn.cursor()
            c.execute('DELETE FROM verified_users WHERE irc_nick = ?', (irc_nick,))
            conn.commit()

auth_db = AuthDB()