import asyncio
import logging
from collections import defaultdict
import sqlite3
import config

logger = logging.getLogger("gamble_bot.database")

# Application level lock map to prevent race conditions on user accounts
_user_locks = defaultdict(asyncio.Lock)

def get_user_lock(discord_id: int) -> asyncio.Lock:
    """Returns a unique Lock for a given user ID to serialize balance actions."""
    return _user_locks[discord_id]

class InsufficientFunds(Exception):
    """Raised when a user does not have enough balance for an action."""
    pass

class UserBanned(Exception):
    """Raised when a banned user tries to perform economy actions."""
    pass

class DuplicateTransaction(Exception):
    """Raised when a transaction code is already used."""
    pass

class DatabaseManager:
    def __init__(self):
        self.pool = None  # For PostgreSQL pool
        self.sqlite_conn = None  # For SQLite connection

    async def connect(self):
        """Initializes connection to PostgreSQL or SQLite."""
        if config.USE_SQLITE:
            import aiosqlite
            logger.info(f"Connecting to SQLite database at {config.SQLITE_PATH}...")
            self.sqlite_conn = await aiosqlite.connect(config.SQLITE_PATH)
            # Enable Foreign Keys in SQLite
            await self.sqlite_conn.execute("PRAGMA foreign_keys = ON;")
            await self.sqlite_conn.commit()
        else:
            import asyncpg
            logger.info(f"Connecting to PostgreSQL database {config.DB_NAME} at {config.DB_HOST}:{config.DB_PORT}...")
            self.pool = await asyncpg.create_pool(
                host=config.DB_HOST,
                port=config.DB_PORT,
                user=config.DB_USER,
                password=config.DB_PASSWORD,
                database=config.DB_NAME,
                min_size=5,
                max_size=20
            )

    async def disconnect(self):
        """Closes all database connections."""
        if self.sqlite_conn:
            await self.sqlite_conn.close()
            logger.info("SQLite connection closed.")
        if self.pool:
            await self.pool.close()
            logger.info("PostgreSQL pool closed.")

    async def execute(self, query: str, *args):
        """Executes a non-returning query."""
        if config.USE_SQLITE:
            async with self.sqlite_conn.execute(query, args) as cursor:
                await self.sqlite_conn.commit()
                return cursor.rowcount
        else:
            async with self.pool.acquire() as conn:
                return await conn.execute(query, *args)

    async def fetch(self, query: str, *args):
        """Fetches multiple rows."""
        if config.USE_SQLITE:
            self.sqlite_conn.row_factory = sqlite3.Row
            async with self.sqlite_conn.execute(query, args) as cursor:
                rows = await cursor.fetchall()
                return [dict(row) for row in rows]
        else:
            async with self.pool.acquire() as conn:
                records = await conn.fetch(query, *args)
                return [dict(r) for r in records]

    async def fetchrow(self, query: str, *args):
        """Fetches a single row or None."""
        if config.USE_SQLITE:
            self.sqlite_conn.row_factory = sqlite3.Row
            async with self.sqlite_conn.execute(query, args) as cursor:
                row = await cursor.fetchone()
                return dict(row) if row else None
        else:
            async with self.pool.acquire() as conn:
                record = await conn.fetchrow(query, *args)
                return dict(record) if record else None

    async def initialize_schema(self):
        """Creates the necessary database tables if they do not exist."""
        # Note: SQLite uses INTEGER PRIMARY KEY and AUTOINCREMENT, PostgreSQL uses SERIAL
        id_type = "INTEGER PRIMARY KEY AUTOINCREMENT" if config.USE_SQLITE else "SERIAL PRIMARY KEY"
        numeric_type = "REAL" if config.USE_SQLITE else "NUMERIC(15, 2)"
        timestamp_default = "CURRENT_TIMESTAMP" if config.USE_SQLITE else "CURRENT_TIMESTAMP"

        queries = [
            f"""
            CREATE TABLE IF NOT EXISTS users (
                discord_id BIGINT PRIMARY KEY,
                game_username VARCHAR(100),
                balance {numeric_type} DEFAULT 0.00,
                created_at TIMESTAMP DEFAULT {timestamp_default},
                last_daily TIMESTAMP,
                is_banned BOOLEAN DEFAULT FALSE
            );
            """,
            f"""
            CREATE TABLE IF NOT EXISTS economy_transactions (
                id {id_type},
                discord_id BIGINT,
                type VARCHAR(20),
                amount {numeric_type},
                game_username VARCHAR(100),
                transaction_code VARCHAR(100) UNIQUE,
                status VARCHAR(20),
                created_at TIMESTAMP DEFAULT {timestamp_default},
                processed_by BIGINT,
                FOREIGN KEY(discord_id) REFERENCES users(discord_id)
            );
            """,
            f"""
            CREATE TABLE IF NOT EXISTS suspicious_logs (
                id {id_type},
                discord_id BIGINT,
                username VARCHAR(100),
                activity_type VARCHAR(50),
                details TEXT,
                created_at TIMESTAMP DEFAULT {timestamp_default}
            );
            """
        ]

        for query in queries:
            await self.execute(query)
        logger.info("Database schema initialized successfully.")

    # --- Economy and Balance Operations with Concurrency Locks ---

    async def register_user(self, discord_id: int, game_username: str = None) -> dict:
        """Registers a user or returns existing user data."""
        async with get_user_lock(discord_id):
            if game_username:
                existing = await self.fetchrow("SELECT discord_id FROM users WHERE game_username = ?", game_username) if config.USE_SQLITE else \
                           await self.fetchrow("SELECT discord_id FROM users WHERE game_username = $1", game_username)
                if existing and int(existing["discord_id"]) != int(discord_id):
                    raise ValueError("Bu Minecraft kullanıcı adı başka bir hesaba bağlı!")

            user = await self.fetchrow("SELECT * FROM users WHERE discord_id = ?", discord_id) if config.USE_SQLITE else \
                   await self.fetchrow("SELECT * FROM users WHERE discord_id = $1", discord_id)
            
            if not user:
                if config.USE_SQLITE:
                    await self.execute(
                        "INSERT INTO users (discord_id, game_username, balance) VALUES (?, ?, 0.00)",
                        discord_id, game_username
                    )
                else:
                    await self.execute(
                        "INSERT INTO users (discord_id, game_username, balance) VALUES ($1, $2, 0.00)",
                        discord_id, game_username
                    )
                user = {"discord_id": discord_id, "game_username": game_username, "balance": 0.00, "is_banned": False}
            elif game_username and user["game_username"] != game_username:
                # Update username if changed
                if config.USE_SQLITE:
                    await self.execute(
                        "UPDATE users SET game_username = ? WHERE discord_id = ?",
                        game_username, discord_id
                    )
                else:
                    await self.execute(
                        "UPDATE users SET game_username = $1 WHERE discord_id = $2",
                        game_username, discord_id
                    )
                user["game_username"] = game_username

            return user

    async def get_user(self, discord_id: int) -> dict:
        """Retrieves user profile."""
        if config.USE_SQLITE:
            return await self.fetchrow("SELECT * FROM users WHERE discord_id = ?", discord_id)
        else:
            return await self.fetchrow("SELECT * FROM users WHERE discord_id = $1", discord_id)

    async def update_balance(self, discord_id: int, amount: float, tx_type: str, details: str = "") -> float:
        """
        Updates user balance inside a transaction with lock.
        Allows negative amounts (deductions) and positive amounts (deposits/winnings).
        Checks for insufficient funds and bans.
        """
        async with get_user_lock(discord_id):
            if config.USE_SQLITE:
                # In SQLite, transactions are handled sequentially on the thread connection,
                # but we use a manual transaction to ensure atomicity.
                conn = self.sqlite_conn
                async with conn.execute("BEGIN TRANSACTION;"):
                    try:
                        # Fetch user balance
                        async with conn.execute("SELECT balance, is_banned FROM users WHERE discord_id = ?;", (discord_id,)) as cursor:
                            row = await cursor.fetchone()
                        if not row:
                            # Auto register
                            await conn.execute("INSERT INTO users (discord_id, balance) VALUES (?, 0.00);", (discord_id,))
                            row = (0.00, False)
                        
                        balance, is_banned = row[0], row[1]
                        if is_banned:
                            raise UserBanned("Kullanıcı banlanmış.")

                        new_balance = balance + amount
                        if new_balance < 0:
                            raise InsufficientFunds(f"Yetersiz bakiye. Mevcut: {balance}, İstenen: {-amount}")

                        # Update balance
                        await conn.execute("UPDATE users SET balance = ? WHERE discord_id = ?;", (new_balance, discord_id))
                        
                        # Log transaction
                        await conn.execute(
                            "INSERT INTO economy_transactions (discord_id, type, amount, status, transaction_code) VALUES (?, ?, ?, ?, ?);",
                            (discord_id, tx_type, amount, "completed", f"TX_{tx_type.upper()}_{discord_id}_{int(asyncio.get_event_loop().time()*1000)}")
                        )
                        await conn.execute("COMMIT;")
                        return new_balance
                    except Exception as e:
                        await conn.execute("ROLLBACK;")
                        raise e
            else:
                # PostgreSQL with row-level locks
                async with self.pool.acquire() as conn:
                    async with conn.transaction():
                        # Select with row lock (FOR UPDATE)
                        row = await conn.fetchrow("SELECT balance, is_banned FROM users WHERE discord_id = $1 FOR UPDATE;", discord_id)
                        if not row:
                            await conn.execute("INSERT INTO users (discord_id, balance) VALUES ($1, 0.00);", discord_id)
                            row = {"balance": 0.00, "is_banned": False}
                        
                        balance = float(row["balance"])
                        is_banned = row["is_banned"]

                        if is_banned:
                            raise UserBanned("Kullanıcı banlanmış.")

                        new_balance = balance + amount
                        if new_balance < 0:
                            raise InsufficientFunds(f"Yetersiz bakiye. Mevcut: {balance}, İstenen: {-amount}")

                        await conn.execute("UPDATE users SET balance = $1 WHERE discord_id = $2;", new_balance, discord_id)
                        
                        tx_code = f"TX_{tx_type.upper()}_{discord_id}_{int(asyncio.get_event_loop().time()*1000)}"
                        await conn.execute(
                            "INSERT INTO economy_transactions (discord_id, type, amount, status, transaction_code) VALUES ($1, $2, $3, $4, $5);",
                            discord_id, tx_type, amount, "completed", tx_code
                        )
                        return new_balance

    # --- Deposit and Withdrawal Logs ---

    async def process_deposit(self, discord_id: int, game_username: str, transaction_code: str, amount: float) -> float:
        """
        Validates transaction code uniqueness, credits balance, and records deposit.
        Ensures atomic operation to avoid double-spend.
        """
        async with get_user_lock(discord_id):
            if config.USE_SQLITE:
                # Check for double spend of transaction code
                exists = await self.fetchrow("SELECT 1 FROM economy_transactions WHERE transaction_code = ? AND type = 'deposit'", transaction_code)
                if exists:
                    raise DuplicateTransaction("Bu işlem kodu zaten kullanılmış!")

                # Register user if not exists
                await self.register_user(discord_id, game_username)

                conn = self.sqlite_conn
                async with conn.execute("BEGIN TRANSACTION;"):
                    try:
                        async with conn.execute("SELECT balance, is_banned FROM users WHERE discord_id = ?", (discord_id,)) as cursor:
                            row = await cursor.fetchone()
                        
                        balance, is_banned = row[0], row[1]
                        if is_banned:
                            raise UserBanned("Kullanıcı banlanmış.")

                        new_balance = balance + amount
                        await conn.execute("UPDATE users SET balance = ? WHERE discord_id = ?", (new_balance, discord_id))
                        
                        await conn.execute(
                            "INSERT INTO economy_transactions (discord_id, type, amount, game_username, transaction_code, status) VALUES (?, 'deposit', ?, ?, ?, 'completed')",
                            (discord_id, amount, game_username, transaction_code)
                        )
                        await conn.execute("COMMIT;")
                        return new_balance
                    except Exception as e:
                        await conn.execute("ROLLBACK;")
                        raise e
            else:
                # PostgreSQL
                async with self.pool.acquire() as conn:
                    async with conn.transaction():
                        # Lock user and check transaction code in same tx
                        exists = await conn.fetchrow("SELECT 1 FROM economy_transactions WHERE transaction_code = $1 AND type = 'deposit' FOR UPDATE", transaction_code)
                        if exists:
                            raise DuplicateTransaction("Bu işlem kodu zaten kullanılmış!")

                        await self.register_user(discord_id, game_username)
                        row = await conn.fetchrow("SELECT balance, is_banned FROM users WHERE discord_id = $1 FOR UPDATE", discord_id)
                        if row["is_banned"]:
                            raise UserBanned("Kullanıcı banlanmış.")

                        new_balance = float(row["balance"]) + amount
                        await conn.execute("UPDATE users SET balance = $1 WHERE discord_id = $2", new_balance, discord_id)
                        await conn.execute(
                            "INSERT INTO economy_transactions (discord_id, type, amount, game_username, transaction_code, status) VALUES ($1, 'deposit', $2, $3, $4, 'completed')",
                            discord_id, amount, game_username, transaction_code
                        )
                        return new_balance

    async def create_withdrawal_request(self, discord_id: int, game_username: str, amount: float, status: str) -> int:
        """Creates a pending or approved withdrawal transaction."""
        async with get_user_lock(discord_id):
            # First, check and lock balance
            if config.USE_SQLITE:
                user = await self.fetchrow("SELECT balance, is_banned FROM users WHERE discord_id = ?", discord_id)
                if not user or user["is_banned"]:
                    raise UserBanned("Kullanıcı kayıtlı değil veya banlanmış.")
                if user["balance"] < amount:
                    raise InsufficientFunds("Yetersiz bakiye.")

                conn = self.sqlite_conn
                async with conn.execute("BEGIN TRANSACTION;"):
                    try:
                        # For pending, we freeze/deduct immediately to avoid double spend.
                        # If rejected, we refund.
                        new_balance = user["balance"] - amount
                        await conn.execute("UPDATE users SET balance = ? WHERE discord_id = ?", (new_balance, discord_id))
                        
                        tx_code = f"WD_{discord_id}_{int(asyncio.get_event_loop().time()*1000)}"
                        cursor = await conn.execute(
                            "INSERT INTO economy_transactions (discord_id, type, amount, game_username, transaction_code, status) VALUES (?, 'withdraw', ?, ?, ?, ?)",
                            (discord_id, amount, game_username, tx_code, status)
                        )
                        tx_id = cursor.lastrowid
                        await conn.execute("COMMIT;")
                        return tx_id
                    except Exception as e:
                        await conn.execute("ROLLBACK;")
                        raise e
            else:
                async with self.pool.acquire() as conn:
                    async with conn.transaction():
                        row = await conn.fetchrow("SELECT balance, is_banned FROM users WHERE discord_id = $1 FOR UPDATE", discord_id)
                        if not row or row["is_banned"]:
                            raise UserBanned("Kullanıcı kayıtlı değil veya banlanmış.")
                        if float(row["balance"]) < amount:
                            raise InsufficientFunds("Yetersiz bakiye.")

                        new_balance = float(row["balance"]) - amount
                        await conn.execute("UPDATE users SET balance = $1 WHERE discord_id = $2", new_balance, discord_id)
                        
                        tx_code = f"WD_{discord_id}_{int(asyncio.get_event_loop().time()*1000)}"
                        tx_id = await conn.fetchval(
                            "INSERT INTO economy_transactions (discord_id, type, amount, game_username, transaction_code, status) VALUES ($1, 'withdraw', $2, $3, $4, $5) RETURNING id",
                            discord_id, amount, game_username, tx_code, status
                        )
                        return tx_id

    async def update_transaction_status(self, tx_id: int, status: str, admin_id: int = None) -> dict:
        """Updates withdrawal status (approving logs or returning refunds on reject)."""
        if config.USE_SQLITE:
            tx = await self.fetchrow("SELECT * FROM economy_transactions WHERE id = ?", tx_id)
            if not tx:
                return None
            
            if tx["status"] != "pending":
                return tx

            conn = self.sqlite_conn
            async with conn.execute("BEGIN TRANSACTION;"):
                try:
                    await conn.execute(
                        "UPDATE economy_transactions SET status = ?, processed_by = ? WHERE id = ?",
                        (status, admin_id, tx_id)
                    )
                    
                    if status == "rejected":
                        # Refund balance
                        async with conn.execute("SELECT balance FROM users WHERE discord_id = ?", (tx["discord_id"],)) as cursor:
                            row = await cursor.fetchone()
                        new_balance = row[0] + tx["amount"]
                        await conn.execute("UPDATE users SET balance = ? WHERE discord_id = ?", (new_balance, tx["discord_id"]))
                        
                    await conn.execute("COMMIT;")
                    return await self.fetchrow("SELECT * FROM economy_transactions WHERE id = ?", tx_id)
                except Exception as e:
                    await conn.execute("ROLLBACK;")
                    raise e
        else:
            async with self.pool.acquire() as conn:
                async with conn.transaction():
                    tx = await conn.fetchrow("SELECT * FROM economy_transactions WHERE id = $1 FOR UPDATE", tx_id)
                    if not tx:
                        return None
                    if tx["status"] != "pending":
                        return tx

                    await conn.execute(
                        "UPDATE economy_transactions SET status = $1, processed_by = $2 WHERE id = $3",
                        status, admin_id, tx_id
                    )

                    if status == "rejected":
                        # Refund balance
                        row = await conn.fetchrow("SELECT balance FROM users WHERE discord_id = $1 FOR UPDATE", tx["discord_id"])
                        new_balance = float(row["balance"]) + float(tx["amount"])
                        await conn.execute("UPDATE users SET balance = $1 WHERE discord_id = $2", new_balance, tx["discord_id"])

                    return await conn.fetchrow("SELECT * FROM economy_transactions WHERE id = $1", tx_id)

    # --- Suspicious Activity Logger ---

    async def log_suspicious(self, discord_id: int, username: str, activity_type: str, details: str):
        """Logs suspicious activity to database."""
        if config.USE_SQLITE:
            await self.execute(
                "INSERT INTO suspicious_logs (discord_id, username, activity_type, details) VALUES (?, ?, ?, ?)",
                discord_id, username, activity_type, details
            )
        else:
            await self.execute(
                "INSERT INTO suspicious_logs (discord_id, username, activity_type, details) VALUES ($1, $2, $3, $4)",
                discord_id, username, activity_type, details
            )
        logger.warning(f"SUSPICIOUS ACTIVITY: User {username} ({discord_id}) - {activity_type} - {details}")

    async def ban_user(self, discord_id: int, status: bool = True):
        """Bans or unbans a user from using the bot economy."""
        if config.USE_SQLITE:
            await self.execute("UPDATE users SET is_banned = ? WHERE discord_id = ?", status, discord_id)
        else:
            await self.execute("UPDATE users SET is_banned = $1 WHERE discord_id = $2", status, discord_id)

    async def claim_daily_reward(self, discord_id: int, daily_amount: float = 1000.0) -> tuple[bool, float, any]:
        """
        Locks the user, checks eligibility for daily reward, and updates balance if eligible.
        Returns (success: bool, balance: float, last_daily_time: datetime)
        """
        async with get_user_lock(discord_id):
            user = await self.fetchrow("SELECT * FROM users WHERE discord_id = ?", discord_id) if config.USE_SQLITE else \
                   await self.fetchrow("SELECT * FROM users WHERE discord_id = $1", discord_id)
            
            if not user:
                # Register first
                if config.USE_SQLITE:
                    await self.execute("INSERT INTO users (discord_id, balance) VALUES (?, 0.00)", discord_id)
                else:
                    await self.execute("INSERT INTO users (discord_id, balance) VALUES ($1, 0.00)", discord_id)
                user = {"discord_id": discord_id, "balance": 0.00, "last_daily": None}

            last_daily = user.get("last_daily")
            from datetime import datetime, timezone, timedelta
            now = datetime.now(timezone.utc)

            if last_daily:
                if isinstance(last_daily, str):
                    try:
                        last_daily = datetime.fromisoformat(last_daily)
                    except ValueError:
                        last_daily = None
                if last_daily:
                    if last_daily.tzinfo is None:
                        last_daily = last_daily.replace(tzinfo=timezone.utc)
                    
                    elapsed = now - last_daily
                    if elapsed < timedelta(days=1):
                        return False, float(user["balance"]), last_daily

            # Update database
            new_balance = float(user["balance"]) + daily_amount
            if config.USE_SQLITE:
                await self.execute("UPDATE users SET last_daily = ?, balance = ? WHERE discord_id = ?", now.isoformat(), new_balance, discord_id)
            else:
                await self.execute("UPDATE users SET last_daily = $1, balance = $2 WHERE discord_id = $3", now, new_balance, discord_id)

            return True, new_balance, now

# Create singleton instance
db = DatabaseManager()
