from backend.code.modules.database.database import DatabaseModule

class SessionRepository():
    def __init__(self, repo: DatabaseModule):
        self.conn = repo.conn
        self.cursor = repo.cursor

    def SelectByUsername(self, username: str):
        sql = '''
        SELECT a.*, u.name, u.surnames, u.email
        FROM user_accounts a
        JOIN users u ON u.id = a.user_id
        WHERE a.username = ?
        AND a.active
        '''
        self.cursor.execute(sql, [username])
        return self.cursor.fetchone()

    def SelectByToken(self, token: str):
        sql = '''
        SELECT uas.account_id, u.id
        FROM user_account_sessions uas
        JOIN user_accounts ua ON uas.account_id = ua.id
        JOIN users u ON ua.user_id = u.id
        WHERE token = ?
        AND uas.active
        '''
        self.cursor.execute(sql, [token])
        return self.cursor.fetchone()

    def RefreshAttempts(self, account_id: int, attempts: int, locked: bool):
        sql = '''
        UPDATE user_accounts
        SET attempts = ?, locked = ?
        WHERE id = ?
        '''
        self.cursor.execute(sql, [attempts, locked, account_id])
        self.conn.commit()
        return self.cursor.rowcount
        
    def RegisterSession(self, accountId: int, token: str):
        sql = '''
        INSERT INTO user_account_sessions(account_id, token)
        VALUES (?, ?)
        '''
        self.cursor.execute(sql, [accountId, token])
        self.conn.commit()
        return self.cursor.rowcount

    def DisableToken(self, token: str):
        sql = '''
        UPDATE user_account_sessions
        SET active = false
        WHERE token = ?
        '''
        self.cursor.execute(sql, [token])
        self.conn.commit()
        return self.cursor.rowcount

    def InsertRecoverRequest(self, accountId: int, token: str):
        sql = '''
        INSERT INTO user_account_recover_requests(account_id, token)
        VALUES(?, ?)
        '''
        self.cursor.execute(sql, [accountId, token])
        self.conn.commit()
        return self.cursor.rowcount

    def SelectRecoverRequest(self, accountId: int, token: str):
        sql = '''
        SELECT *
        FROM user_account_recover_requests
        WHERE account_id = ?
        AND token = ?
        AND active
        '''
        self.cursor.execute(sql, [accountId, token])
        return self.cursor.fetchone()

    def DisableRecoverRequest(self, token: str):
        sql = '''
        UPDATE user_account_recover_requests
        SET active = false
        WHERE token = ?
        AND active
        '''
        self.cursor.execute(sql, [token])
        self.conn.commit()
        self.cursor.rowcount