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

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

    def FilterUsers(self, filters: dict = None):
        params = list()

        sql = '''
        SELECT u.*, a.username, a.locked, a.active
        FROM users u
        LEFT JOIN user_accounts a ON a.user_id = u.id
        WHERE 1=1
        '''

        if filters is not None:
            if 'name' in filters:
                sql = sql + " AND u.name LIKE ?"
                params.append(filters['name'])

            if 'surnames' in filters:
                sql = sql + " AND u.surnames LIKE ?"
                params.append(filters['surnames'])

            if 'email' in filters:
                sql = sql + " AND u.email LIKE ?"
                params.append(filters['email'])

            if 'username' in filters:
                sql = sql + " AND a.username LIKE ?"
                params.append(filters['username'])

        # print(sql)

        self.cursor.execute(sql, params)
        return self.cursor.fetchall()

    def SelectUserById(self, id: int):
        sql = '''
        SELECT u.*, a.username, a.locked, a.active
        FROM users u
        LEFT JOIN user_accounts a ON a.user_id = u.id
        WHERE u.id = ?
        '''
        self.cursor.execute(sql, [id])
        return self.cursor.fetchone()
    
    def InsertUser(self, name: str, surnames: str, email: str):
        sql = '''
        INSERT INTO users(name, surnames, email)
        VALUES(?, ?, ?)
        '''
        self.cursor.execute(sql, [name, surnames, email])
        self.conn.commit()
        return self.cursor.lastrowid

    def UpdateUser(self, id: int, name: str, surnames: str, email: str):
        sql = '''
        UPDATE users
        SET name = ?, surnames = ?, email = ?
        WHERE id = ?
        '''
        self.cursor.execute(sql, [name, surnames, email, id])
        self.conn.commit()
        return self.cursor.rowcount

    def DeleteUser(self, id: int):
        sql = '''
        DELETE FROM users
        WHERE id = ?
        '''
        self.cursor.execute(sql, [id])
        self.conn.commit()
        return self.cursor.rowcount