import aiomysql
import logging
import os
from datetime import datetime, timedelta
from config import DB_HOST, DB_USER, DB_PASSWORD, DB_NAME
from config import (DEFAULT_AUTH_LIMIT, DEFAULT_PUAN_LIMIT, DEFAULT_VBV_LIMIT, 
                    DEFAULT_IYZICO_LIMIT, DEFAULT_AUTH2_LIMIT, DEFAULT_MOKA_LIMIT, 
                    DEFAULT_DISNEY_LIMIT, DEFAULT_STRIPE_LIMIT, 
                    DEFAULT_STRIPEGENEL_LIMIT, DEFAULT_AUTHNET_LIMIT)

logger = logging.getLogger(__name__)

# Limit takip file path
LIMIT_TAKIP_FILE = os.path.join(os.path.dirname(__file__), 'limittakip.txt')

class Database:
    def __init__(self):
        self.pool = None
    
    async def connect(self):
        try:
            self.pool = await aiomysql.create_pool(
                host=DB_HOST,
                user=DB_USER,
                password=DB_PASSWORD,
                db=DB_NAME,
                autocommit=True,
                maxsize=10,
                minsize=1,
                connect_timeout=5,
                pool_recycle=3600
            )
            logger.info("Database pool created successfully")
        except Exception as e:
            logger.error(f"Database connection failed: {e}")
            raise
    
    async def close(self):
        if self.pool:
            self.pool.close()
            await self.pool.wait_closed()
            logger.info("Database pool closed")
    
    async def user_exists(self, chat_id):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        "SELECT chatid FROM users WHERE chatid = %s",
                        (chat_id,)
                    )
                    result = await cursor.fetchone()
                    return result is not None
        except Exception as e:
            logger.error(f"Error checking user existence: {e}")
            return False
    
    async def add_user(self, chat_id, username, referral_code=None, referred_by=None):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        """INSERT INTO users (chatid, username, authlimit, puanlimit, vbvlimit, iyzicolimit, auth2limit, mokalimit, disneylimit, stripelimit, shopifylimit, referral_code, referred_by) 
                           VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
                        (chat_id, username, DEFAULT_AUTH_LIMIT, DEFAULT_PUAN_LIMIT, DEFAULT_VBV_LIMIT, DEFAULT_IYZICO_LIMIT, DEFAULT_AUTH2_LIMIT, DEFAULT_MOKA_LIMIT, DEFAULT_DISNEY_LIMIT, DEFAULT_STRIPE_LIMIT, 10, referral_code, referred_by)
                    )
                    logger.info(f"User {chat_id} added to database with referral code {referral_code}")
                    return True
        except Exception as e:
            logger.error(f"Error adding user: {e}")
            return False
    
    async def get_user(self, chat_id):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute(
                        "SELECT * FROM users WHERE chatid = %s",
                        (chat_id,)
                    )
                    result = await cursor.fetchone()
                    return result
        except Exception as e:
            logger.error(f"Error getting user: {e}")
            return None
    
    async def get_all_users(self):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute("SELECT * FROM users")
                    result = await cursor.fetchall()
                    return result
        except Exception as e:
            logger.error(f"Error getting all users: {e}")
            return []
    
    async def ban_user(self, chat_id):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        "UPDATE users SET banned = 1 WHERE chatid = %s",
                        (chat_id,)
                    )
                    logger.info(f"User {chat_id} banned")
                    return True
        except Exception as e:
            logger.error(f"Error banning user: {e}")
            return False
    
    async def unban_user(self, chat_id):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        "UPDATE users SET banned = 0 WHERE chatid = %s",
                        (chat_id,)
                    )
                    logger.info(f"User {chat_id} unbanned")
                    return True
        except Exception as e:
            logger.error(f"Error unbanning user: {e}")
            return False
    
    async def search_user(self, search_term):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute(
                        """SELECT * FROM users 
                           WHERE chatid LIKE %s OR username LIKE %s""",
                        (f"%{search_term}%", f"%{search_term}%")
                    )
                    result = await cursor.fetchall()
                    return result
        except Exception as e:
            logger.error(f"Error searching user: {e}")
            return []
    
    async def set_membership(self, chat_id, membership_type, auth_limit, puan_limit, vbv_limit, iyzico_limit, auth2_limit, moka_limit, disney_limit, stripe_limit):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        """UPDATE users 
                           SET membership_type = %s, authlimit = %s, 
                               puanlimit = %s, vbvlimit = %s, iyzicolimit = %s, auth2limit = %s, 
                               mokalimit = %s, disneylimit = %s, stripegenellimit = %s, authnetlimit = %s, shopifylimit = %s
                           WHERE chatid = %s""",
                        (membership_type, auth_limit, puan_limit, vbv_limit, iyzico_limit, auth2_limit, moka_limit, disney_limit, stripe_limit, stripe_limit, 10, chat_id)
                    )
                    logger.info(f"User {chat_id} upgraded to {membership_type} membership")
                    return True
        except Exception as e:
            logger.error(f"Error setting membership: {e}")
            return False
    
    async def get_user_by_referral_code(self, referral_code):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute(
                        "SELECT * FROM users WHERE referral_code = %s",
                        (referral_code,)
                    )
                    result = await cursor.fetchone()
                    return result
        except Exception as e:
            logger.error(f"Error getting user by referral code: {e}")
            return None
    
    async def increment_referral_count(self, referral_code):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        "UPDATE users SET total_referrals = total_referrals + 1 WHERE referral_code = %s",
                        (referral_code,)
                    )
                    logger.info(f"Incremented referral count for code {referral_code}")
                    return True
        except Exception as e:
            logger.error(f"Error incrementing referral count: {e}")
            return False
    
    async def check_and_upgrade_referral(self, chat_id):
        """Check referral count and auto-upgrade to DESTEKCI if >= 5"""
        try:
            user = await self.get_user(chat_id)
            if not user:
                return False
            
            total_refs = user.get('total_referrals', 0)
            current_membership = user.get('membership_type', 'normal')
            
            # Only upgrade if user is still 'normal' and has 5+ referrals
            if current_membership == 'normal' and total_refs >= 5:
                from config import (DEFAULT_AUTH_LIMIT, DEFAULT_PUAN_LIMIT, DEFAULT_PUAN2_LIMIT,
                                   DEFAULT_VBV_LIMIT, DEFAULT_IYZICO_LIMIT, DEFAULT_AUTH2_LIMIT, 
                                   DEFAULT_MOKA_LIMIT, DEFAULT_DISNEY_LIMIT, DEFAULT_STRIPE_LIMIT)
                
                success = await self.set_membership(
                    chat_id, 'destekci',
                    DEFAULT_AUTH_LIMIT * 2,
                    DEFAULT_PUAN_LIMIT * 2,
                    DEFAULT_PUAN2_LIMIT * 2,
                    DEFAULT_VBV_LIMIT * 2,
                    DEFAULT_IYZICO_LIMIT * 2,
                    DEFAULT_AUTH2_LIMIT * 2,
                    DEFAULT_AUTH_LIMIT * 2,  # mokalimit = authlimit
                    DEFAULT_DISNEY_LIMIT * 2,  # disneylimit
                    DEFAULT_STRIPE_LIMIT * 2   # stripelimit
                )
                
                if success:
                    logger.info(f"User {chat_id} auto-upgraded to DESTEKCI (5+ referrals)")
                    return True
            
            return False
        except Exception as e:
            logger.error(f"Error checking and upgrading referral: {e}")
            return False
    
    async def get_user_limit(self, chat_id, limit_type):
        """Get specific limit for a user"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        f"SELECT {limit_type} FROM users WHERE chatid = %s",
                        (chat_id,)
                    )
                    result = await cursor.fetchone()
                    if result:
                        return result[0]
                    return 0
        except Exception as e:
            logger.error(f"Error getting user limit: {e}")
            return 0
    
    async def decrement_limit(self, chat_id, limit_type):
        """Decrement specific limit for a user"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        f"UPDATE users SET {limit_type} = {limit_type} - 1 WHERE chatid = %s",
                        (chat_id,)
                    )
                    logger.info(f"Decremented {limit_type} for user {chat_id}")
                    return True
        except Exception as e:
            logger.error(f"Error decrementing limit: {e}")
            return False
    
    async def decrement_limit_bulk(self, chat_id, limit_type, amount):
        """Decrement specific limit for a user by given amount (single transaction)"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    # Set lock wait timeout to 5 seconds for this session
                    await cursor.execute("SET SESSION innodb_lock_wait_timeout = 5")
                    await cursor.execute(
                        f"UPDATE users SET {limit_type} = {limit_type} - %s WHERE chatid = %s",
                        (amount, chat_id)
                    )
                    logger.info(f"Decremented {limit_type} by {amount} for user {chat_id}")
                    return True
        except Exception as e:
            logger.error(f"Error bulk decrementing limit: {e}")
            return False
    
    async def get_bin_info(self, bin_number):
        """Get BIN information from binapi"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute(
                        "SELECT * FROM bins WHERE bin = %s",
                        (bin_number,)
                    )
                    result = await cursor.fetchone()
                    return result
        except Exception as e:
            logger.error(f"Error getting BIN info: {e}")
            return None
    
    async def get_gate_status(self, gate_name):
        """Get gate active status"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute(
                        "SELECT is_active FROM gateyonetim WHERE gate_name = %s",
                        (gate_name,)
                    )
                    result = await cursor.fetchone()
                    return result['is_active'] if result else 1  # Default to active if not found
        except Exception as e:
            logger.error(f"Error getting gate status: {e}")
            return 1  # Default to active on error
    
    async def toggle_gate_status(self, gate_name):
        """Toggle gate active status"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    # Toggle the status
                    await cursor.execute(
                        "UPDATE gateyonetim SET is_active = 1 - is_active WHERE gate_name = %s",
                        (gate_name,)
                    )
                    
                    # Get new status
                    await cursor.execute(
                        "SELECT is_active FROM gateyonetim WHERE gate_name = %s",
                        (gate_name,)
                    )
                    result = await cursor.fetchone()
                    new_status = result[0] if result else None
                    
                    logger.info(f"Toggled gate {gate_name} to {'active' if new_status else 'disabled'}")
                    return new_status
        except Exception as e:
            logger.error(f"Error toggling gate status: {e}")
            return None
    
    async def get_all_gates(self):
        """Get all gates with their status"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute(
                        "SELECT gate_name, is_active FROM gateyonetim ORDER BY id"
                    )
                    result = await cursor.fetchall()
                    return result
        except Exception as e:
            logger.error(f"Error getting all gates: {e}")
            return []
    
    async def use_referral_code(self, user_chat_id, referral_code):
        """User uses someone else's referral code (max 5 referrals per user)"""
        try:
            # Get the referrer
            referrer = await self.get_user_by_referral_code(referral_code)
            if not referrer:
                return False, "Referral code not found."
            
            # Check if trying to use own code
            if referrer['chatid'] == user_chat_id:
                return False, "You cannot use your own referral code."
            
            # Check if user already used a code
            user = await self.get_user(user_chat_id)
            if user and user.get('referred_by'):
                return False, "You have already used a referral code."
            
            # Check if referrer has available slots (max 5)
            ref_slots = [referrer.get('ref1'), referrer.get('ref2'), referrer.get('ref3'), 
                        referrer.get('ref4'), referrer.get('ref5')]
            available_slot = None
            for i, slot in enumerate(ref_slots, 1):
                if slot is None:
                    available_slot = i
                    break
            
            if available_slot is None:
                return False, "quota_full"  # Special error code for quota full
            
            # Update user's referred_by and add to referrer's slot
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    # Update user
                    await cursor.execute(
                        "UPDATE users SET referred_by = %s WHERE chatid = %s",
                        (referral_code, user_chat_id)
                    )
                    
                    # Add to referrer's available slot
                    slot_column = f"ref{available_slot}"
                    await cursor.execute(
                        f"UPDATE users SET {slot_column} = %s, total_referrals = total_referrals + 1 WHERE referral_code = %s",
                        (user_chat_id, referral_code)
                    )
                    
                    logger.info(f"User {user_chat_id} used referral code {referral_code} (slot {available_slot})")
                    return True, f"Success! You used @{referrer.get('username', 'user')}'s referral code."
        except Exception as e:
            logger.error(f"Error using referral code: {e}")
            return False, "An error occurred."
    
    async def get_user_referrals(self, referral_code, limit=5):
        """Get list of users from ref1-ref5 slots"""
        try:
            # Get the user by referral code to access ref1-ref5
            referrer = await self.get_user_by_referral_code(referral_code)
            if not referrer:
                return []
            
            # Collect chat_ids from ref slots
            ref_chat_ids = []
            for i in range(1, 6):  # ref1 to ref5
                chat_id = referrer.get(f'ref{i}')
                if chat_id:
                    ref_chat_ids.append(chat_id)
            
            if not ref_chat_ids:
                return []
            
            # Get usernames for these chat_ids
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    placeholders = ','.join(['%s'] * len(ref_chat_ids))
                    await cursor.execute(
                        f"SELECT chatid, username FROM users WHERE chatid IN ({placeholders})",
                        ref_chat_ids
                    )
                    result = await cursor.fetchall()
                    return result
        except Exception as e:
            logger.error(f"Error getting user referrals: {e}")
            return []
    
    def read_next_reset_time(self):
        """Read next reset time from limittakip.txt"""
        try:
            if os.path.exists(LIMIT_TAKIP_FILE):
                with open(LIMIT_TAKIP_FILE, 'r', encoding='utf-8') as f:
                    timestamp_str = f.read().strip()
                    if timestamp_str:
                        return datetime.fromisoformat(timestamp_str)
            # If file doesn't exist or is empty, set next reset to 24 hours from now
            next_reset = datetime.now() + timedelta(hours=24)
            self.write_next_reset_time(next_reset)
            return next_reset
        except Exception as e:
            logger.error(f"Error reading next reset time: {e}")
            # Default: 24 hours from now
            return datetime.now() + timedelta(hours=24)
    
    def write_next_reset_time(self, next_reset_time):
        """Write next reset time to limittakip.txt"""
        try:
            with open(LIMIT_TAKIP_FILE, 'w', encoding='utf-8') as f:
                f.write(next_reset_time.isoformat())
            logger.info(f"Next reset time written: {next_reset_time}")
            return True
        except Exception as e:
            logger.error(f"Error writing next reset time: {e}")
            return False
    
    async def reset_daily_limits(self):
        """Reset limits only for 'normal' and 'destekci' membership types"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    # Reset normal users (1x limits)
                    await cursor.execute(
                        """UPDATE users SET 
                            authlimit = %s,
                            puanlimit = %s,
                            vbvlimit = %s,
                            iyzicolimit = %s,
                            auth2limit = %s,
                            mokalimit = %s,
                            disneylimit = %s,
                            stripelimit = %s
                        WHERE membership_type = 'normal'""",
                        (DEFAULT_AUTH_LIMIT, DEFAULT_PUAN_LIMIT,
                         DEFAULT_VBV_LIMIT, DEFAULT_IYZICO_LIMIT, DEFAULT_AUTH2_LIMIT,
                         DEFAULT_MOKA_LIMIT, DEFAULT_DISNEY_LIMIT, DEFAULT_STRIPE_LIMIT)
                    )
                    normal_count = cursor.rowcount
                    
                    # Reset destekci users (2x limits)
                    await cursor.execute(
                        """UPDATE users SET 
                            authlimit = %s,
                            puanlimit = %s,
                            vbvlimit = %s,
                            iyzicolimit = %s,
                            auth2limit = %s,
                            mokalimit = %s,
                            disneylimit = %s,
                            stripelimit = %s
                        WHERE membership_type = 'destekci'""",
                        (DEFAULT_AUTH_LIMIT * 2, DEFAULT_PUAN_LIMIT * 2,
                         DEFAULT_VBV_LIMIT * 2, DEFAULT_IYZICO_LIMIT * 2, DEFAULT_AUTH2_LIMIT * 2,
                         DEFAULT_MOKA_LIMIT * 2, DEFAULT_DISNEY_LIMIT * 2, DEFAULT_STRIPE_LIMIT * 2)
                    )
                    destekci_count = cursor.rowcount
                    
                    # Update next reset time (24 hours from now)
                    next_reset = datetime.now() + timedelta(hours=24)
                    self.write_next_reset_time(next_reset)
                    
                    logger.info(f"Daily limits reset: {normal_count} normal users, {destekci_count} destekci users")
                    return True, normal_count, destekci_count
        except Exception as e:
            logger.error(f"Error resetting daily limits: {e}")
            return False, 0, 0
    
    # Redeem Code Functions
    async def create_redeem_code(self, code, limit_type, amount, max_uses, created_by):
        """Create a new redeem code"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        """INSERT INTO redeem_codes (code, limit_type, amount, max_uses, created_by)
                           VALUES (%s, %s, %s, %s, %s)""",
                        (code, limit_type, amount, max_uses, created_by)
                    )
                    logger.info(f"Redeem code '{code}' created by {created_by}")
                    return True
        except Exception as e:
            logger.error(f"Error creating redeem code: {e}")
            return False
    
    async def get_all_redeem_codes(self):
        """Get all redeem codes"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute(
                        "SELECT * FROM redeem_codes ORDER BY created_at DESC"
                    )
                    return await cursor.fetchall()
        except Exception as e:
            logger.error(f"Error fetching redeem codes: {e}")
            return []
    
    async def get_redeem_code_info(self, code):
        """Get information about a specific redeem code"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute(
                        "SELECT * FROM redeem_codes WHERE code = %s",
                        (code,)
                    )
                    return await cursor.fetchone()
        except Exception as e:
            logger.error(f"Error fetching redeem code info: {e}")
            return None
    
    async def delete_redeem_code(self, code):
        """Delete a redeem code"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute("DELETE FROM redeem_codes WHERE code = %s", (code,))
                    await cursor.execute("DELETE FROM code_usage WHERE code = %s", (code,))
                    logger.info(f"Redeem code '{code}' deleted")
                    return True
        except Exception as e:
            logger.error(f"Error deleting redeem code: {e}")
            return False
    
    async def check_user_used_code(self, user_id, code):
        """Check if user has already used this code"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        "SELECT * FROM code_usage WHERE user_id = %s AND code = %s",
                        (user_id, code)
                    )
                    result = await cursor.fetchone()
                    return result is not None
        except Exception as e:
            logger.error(f"Error checking code usage: {e}")
            return True  # Return True to prevent usage on error
    
    async def use_redeem_code(self, user_id, code):
        """Use a redeem code and add limits to user"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    # Get code info
                    await cursor.execute(
                        "SELECT * FROM redeem_codes WHERE code = %s",
                        (code,)
                    )
                    code_info = await cursor.fetchone()
                    
                    if not code_info:
                        return False, "Code not found"
                    
                    if code_info['used_count'] >= code_info['max_uses']:
                        return False, "Code usage limit reached"
                    
                    # Check if user already used this code
                    has_used = await self.check_user_used_code(user_id, code)
                    if has_used:
                        return False, "You have already used this code"
                    
                    # Add limit to user
                    limit_column = code_info['limit_type']
                    amount = code_info['amount']
                    
                    await cursor.execute(
                        f"UPDATE users SET {limit_column} = {limit_column} + %s WHERE chatid = %s",
                        (amount, user_id)
                    )
                    
                    # Record usage
                    await cursor.execute(
                        "INSERT INTO code_usage (code, user_id) VALUES (%s, %s)",
                        (code, user_id)
                    )
                    
                    # Increment used_count
                    await cursor.execute(
                        "UPDATE redeem_codes SET used_count = used_count + 1 WHERE code = %s",
                        (code,)
                    )
                    
                    logger.info(f"User {user_id} used redeem code '{code}'")
                    return True, code_info
        except Exception as e:
            logger.error(f"Error using redeem code: {e}")
            return False, "Database error"
    
    # ============================================
    # PUAN2 MASS QUEUE SYSTEM
    # ============================================
    
    async def add_to_puan2_queue(self, user_id, username, cards_count, cards_data):
        """Add a user's check request to the queue"""
        try:
            import json
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        """INSERT INTO puan2mass_queue 
                           (user_id, username, cards_count, cards_data, status) 
                           VALUES (%s, %s, %s, %s, 'waiting')""",
                        (user_id, username, cards_count, json.dumps(cards_data))
                    )
                    queue_id = cursor.lastrowid
                    logger.info(f"User {user_id} added to puan2mass queue (ID: {queue_id})")
                    return queue_id
        except Exception as e:
            logger.error(f"Error adding to queue: {e}")
            return None
    
    async def get_queue_position(self, queue_id):
        """Get position in queue (1-indexed). Returns 0 if not in waiting status."""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    # First check if this item is still waiting
                    await cursor.execute(
                        "SELECT status FROM puan2mass_queue WHERE id = %s",
                        (queue_id,)
                    )
                    status_row = await cursor.fetchone()
                    if not status_row or status_row[0] != 'waiting':
                        return 0  # Not waiting anymore
                    
                    # Count position
                    await cursor.execute(
                        """SELECT COUNT(*) + 1 as position 
                           FROM puan2mass_queue 
                           WHERE status = 'waiting' 
                           AND id < %s 
                           AND created_at <= (SELECT created_at FROM puan2mass_queue WHERE id = %s)""",
                        (queue_id, queue_id)
                    )
                    result = await cursor.fetchone()
                    return result[0] if result else 0
        except Exception as e:
            logger.error(f"Error getting queue position: {e}")
            return 0
    
    async def get_next_in_queue(self):
        """Get next waiting request from queue"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute(
                        """SELECT * FROM puan2mass_queue 
                           WHERE status = 'waiting' 
                           ORDER BY created_at ASC 
                           LIMIT 1"""
                    )
                    result = await cursor.fetchone()
                    return result
        except Exception as e:
            logger.error(f"Error getting next in queue: {e}")
            return None
    
    async def update_queue_status(self, queue_id, status, session_id=None):
        """Update queue item status"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    if status == 'processing':
                        await cursor.execute(
                            """UPDATE puan2mass_queue 
                               SET status = %s, started_at = NOW(), session_id = %s 
                               WHERE id = %s""",
                            (status, session_id, queue_id)
                        )
                    elif status == 'completed' or status == 'failed':
                        await cursor.execute(
                            """UPDATE puan2mass_queue 
                               SET status = %s, completed_at = NOW() 
                               WHERE id = %s""",
                            (status, queue_id)
                        )
                    else:
                        await cursor.execute(
                            "UPDATE puan2mass_queue SET status = %s WHERE id = %s",
                            (status, queue_id)
                        )
                    logger.info(f"Queue {queue_id} status updated to {status}")
        except Exception as e:
            logger.error(f"Error updating queue status: {e}")
    
    async def get_queue_count(self):
        """Get total number of waiting items in queue"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        "SELECT COUNT(*) FROM puan2mass_queue WHERE status = 'waiting'"
                    )
                    result = await cursor.fetchone()
                    return result[0] if result else 0
        except Exception as e:
            logger.error(f"Error getting queue count: {e}")
            return 0
    
    async def remove_from_queue(self, queue_id):
        """Remove item from queue (used for cancellation)"""
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        "DELETE FROM puan2mass_queue WHERE id = %s",
                        (queue_id,)
                    )
                    logger.info(f"Queue item {queue_id} removed")
        except Exception as e:
            logger.error(f"Error removing from queue: {e}")

db = Database()

