"""
MySQL connection using pymysql
"""
import pymysql
from pymysql.cursors import DictCursor
from config import DB_CONFIG


def get_connection():
    conn = pymysql.connect(
        host=DB_CONFIG["host"],
        port=DB_CONFIG["port"],
        user=DB_CONFIG["user"],
        password=DB_CONFIG["password"],
        database=DB_CONFIG["database"],
        charset=DB_CONFIG["charset"],
        cursorclass=DictCursor,
        autocommit=True,
    )
    return conn


def execute_one(sql, args=None):
    """Execute INSERT/UPDATE/DELETE and return affected rows"""
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            affected = cur.execute(sql, args)
            return affected
    finally:
        conn.close()


def query_one(sql, args=None):
    """Query single row"""
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(sql, args)
            return cur.fetchone()
    finally:
        conn.close()


def query_all(sql, args=None):
    """Query all rows"""
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(sql, args)
            return cur.fetchall()
    finally:
        conn.close()


def insert_and_get_id(sql, args=None):
    """INSERT and return last insert id"""
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(sql, args)
            return cur.lastrowid
    finally:
        conn.close()


def insert_many(sql, rows):
    """
    Bulk INSERT using executemany.
    rows: list of tuples, each tuple is one row's VALUES.
    Returns total rows inserted.
    """
    if not rows:
        return 0
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            affected = cur.executemany(sql, rows)
            return affected
    finally:
        conn.close()


def update_many(sql, rows):
    """
    Bulk UPDATE using executemany.
    rows: list of tuples, each tuple is one row's SET values + WHERE clause args.
    Returns total rows affected.
    """
    if not rows:
        return 0
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            affected = cur.executemany(sql, rows)
            return affected
    finally:
        conn.close()