import mysql.connector

def get_connection():
    return mysql.connector.connect(
        host='localhost',
        user='your_mysql_user',
        password='your_mysql_password',
        database='your_database'
    )

def get_user_accounts(user_id):
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT
            userAccounts.username,
            userAccounts.stream,
            userAccounts.streamURL,
            userAccounts.expiaryDate,
            userAccounts.password
        FROM users
        INNER JOIN userAccounts ON users.id = userAccounts.userID
        WHERE users.id = %s
    """, (user_id,))
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data if data else 'User Not Found'

def get_user_accounts_check(user_id):
    return get_user_accounts(user_id)

def get_all_user_accounts():
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT
            userAccounts.username,
            userAccounts.password,
            userAccounts.expiaryDate,
            userAccounts.stream
        FROM userAccounts
        WHERE userAccounts.expiaryDate != '0'
        ORDER BY userAccounts.id DESC
    """)
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data if data else 'User Not Found'

def get_all_unique_accounts():
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT DISTINCT userAccounts.stream, userAccounts.username,
            userAccounts.password, userAccounts.stream
        FROM userAccounts
        WHERE userAccounts.expiaryDate != '0'
        GROUP BY userAccounts.stream
    """)
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data if data else 'User Not Found'

def get_user_unique_accounts(user_id):
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT DISTINCT userAccounts.streamURL, userAccounts.username,
            userAccounts.password, userAccounts.stream, userAccounts.streamURL
        FROM userAccounts
        WHERE userAccounts.expiaryDate != '0'
            AND userAccounts.userId = %s
        GROUP BY userAccounts.stream
        ORDER BY userAccounts.stream ASC
    """, (user_id,))
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data if data else 'User Not Found'

def get_user_id(username):
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT id FROM users WHERE userName = %s", (username,))
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result["id"] if result else 'User Not Found'