import MySQLdb
from modules import config

db = MySQLdb.connect(
    user=config.db["username"],
    passwd=config.db["password"],
    host=config.db["host"],
    port=config.db["port"],
    db=config.db["schema"],
)


def ping() -> bool:
    try:
        c = db.cursor()
        c.execute("SELECT version();")
        db_version = c.fetchone()
    except MySQLdb.Error as err:
        return err
    else:
        return db_version[0]


def get_latest_sub_id(author: str, nsfw: bool):
    try:
        c = db.cursor()
        if nsfw:
            query = "SELECT id FROM submission WHERE author_name = %s ORDER BY id DESC LIMIT 1"
        else:
            query = "SELECT id FROM submission WHERE author_name = %s AND rating = 'general' ORDER BY id DESC LIMIT 1"
        c.execute(query, (author,))
        (sub_id,) = c.fetchone()
        return sub_id
    except MySQLdb.Error as err:
        return err


def get_random_sub_id(author: str, nsfw: bool):
    try:
        c = db.cursor()
        if nsfw:
            query = "SELECT id FROM submission WHERE author_name = %s ORDER BY RAND() LIMIT 1"
        else:
            query = "SELECT id FROM submission WHERE author_name = %s AND rating = 'general' ORDER BY RAND() LIMIT 1"
        c.execute(query, (author,))
        (sub_id,) = c.fetchone()
        return sub_id
    except MySQLdb.Error as err:
        return err


def get_sub_id_list(author: str, nsfw: bool) -> list:
    try:
        c = db.cursor()
        if nsfw:
            query = "SELECT id FROM submission WHERE author_name = %s"
        else:
            query = "SELECT id FROM submission WHERE author_name = %s AND rating = 'general'"
        c.execute(query, (author,))
        res = c.fetchall()
        id_list = [x for (x,) in res]
        return id_list
    except MySQLdb.Error as err:
        return err


def update_author_data_table(name: str, icon_url: str):
    query = "REPLACE INTO author (author_name, icon_url) VALUES (%s, %s)"
    try:
        c = db.cursor()
        c.execute(query, (name, icon_url))
        db.commit()
    except MySQLdb.Error as err:
        return err
    else:
        return None


def update_gallery_index_table(
    gallery_items: list,
) -> bool:
    query = "REPLACE INTO submission (id, title, author_name, rating) VALUES (%s, %s, %s, %s)"
    payload = [(x.id, x.title, x.author.name, x.rating) for x in gallery_items]
    try:
        c = db.cursor()
        c.executemany(query, payload)
        db.commit()
    except MySQLdb.Error as err:
        return err
    else:
        return None

import MySQLdb
from modules import config

db = MySQLdb.connect(
    user=config.db["username"],
    passwd=config.db["password"],
    host=config.db["host"],
    port=config.db["port"],
    db=config.db["schema"],
)


def ping() -> bool:
    try:
        c = db.cursor()
        c.execute("SELECT version();")
        db_version = c.fetchone()
    except MySQLdb.Error as err:
        return err
    else:
        return db_version[0]


def get_latest_sub_id(author: str, nsfw: bool):
    try:
        c = db.cursor()
        if nsfw:
            query = "SELECT id FROM submission WHERE author_name = %s ORDER BY id DESC LIMIT 1"
        else:
            query = "SELECT id FROM submission WHERE author_name = %s AND rating = 'general' ORDER BY id DESC LIMIT 1"
        c.execute(query, (author,))
        (sub_id,) = c.fetchone()
        return sub_id
    except MySQLdb.Error as err:
        return err


def get_random_sub_id(author: str, nsfw: bool):
    try:
        c = db.cursor()
        if nsfw:
            query = "SELECT id FROM submission WHERE author_name = %s ORDER BY RAND() LIMIT 1"
        else:
            query = "SELECT id FROM submission WHERE author_name = %s AND rating = 'general' ORDER BY RAND() LIMIT 1"
        c.execute(query, (author,))
        (sub_id,) = c.fetchone()
        return sub_id
    except MySQLdb.Error as err:
        return err


def get_sub_id_list(author: str, nsfw: bool) -> list:
    try:
        c = db.cursor()
        if nsfw:
            query = "SELECT id FROM submission WHERE author_name = %s"
        else:
            query = "SELECT id FROM submission WHERE author_name = %s AND rating = 'general'"
        c.execute(query, (author,))
        res = c.fetchall()
        id_list = [x for (x,) in res]
        return id_list
    except MySQLdb.Error as err:
        return err


def update_author_data_table(name: str, icon_url: str):
    query = "REPLACE INTO author (author_name, icon_url) VALUES (%s, %s)"
    try:
        c = db.cursor()
        c.execute(query, (name, icon_url))
        db.commit()
    except MySQLdb.Error as err:
        return err
    else:
        return None


def update_gallery_index_table(
    gallery_items: list,
) -> bool:
    query = "REPLACE INTO submission (id, title, author_name, rating) VALUES (%s, %s, %s, %s)"
    payload = [(x.id, x.title, x.author.name, x.rating) for x in gallery_items]
    try:
        c = db.cursor()
        c.executemany(query, payload)
        db.commit()
    except MySQLdb.Error as err:
        return err
    else:
        return None
