from typing import Optional
from services.database import db
from config.settings import ALLOWED_ROLE


DEP_STATUS = {0: "Pending", 1: "Success", 2: "Failed", 3: "Processing"}
WD_STATUS = {
    0: "Pending", 1: "Approved", 2: "Rejected",
    3: "Processing", 4: "Success", 5: "Failed",
}


async def find_by_username(username: str):
    return await db.fetchone(
        """
        SELECT id, username, full_name, balance, status, role, api_key
        FROM users
        WHERE username = %s AND role = %s AND status = 1
        LIMIT 1
        """,
        (username.strip(), ALLOWED_ROLE),
    )


async def find_by_api_key(api_key: str):
    return await db.fetchone(
        """
        SELECT id, username, full_name, balance, status, role, api_key
        FROM users
        WHERE api_key = %s AND role = %s AND status = 1
        LIMIT 1
        """,
        (api_key.strip(), ALLOWED_ROLE),
    )


async def get_group_linked(chat_id: int):
    return await db.fetchone(
        """
        SELECT u.id, u.username, u.full_name, u.balance, u.status,
               gl.linked_by, gl.linked_at, gl.chat_title
        FROM telegram_group_links gl
        JOIN users u ON u.id = gl.merchant_id
        WHERE gl.chat_id = %s
          AND gl.status = 1
          AND u.status = 1
          AND u.role = %s
        LIMIT 1
        """,
        (chat_id, ALLOWED_ROLE),
    )


async def link_group(chat_id: int, chat_title: Optional[str], merchant_id: int,
                     linked_by_user_id: int, linked_by: str):
    await db.execute(
        """
        UPDATE telegram_group_links
        SET status = 0, unlinked_at = NOW()
        WHERE chat_id = %s AND status = 1
        """,
        (chat_id,),
    )
    await db.execute(
        """
        INSERT INTO telegram_group_links
            (chat_id, chat_title, merchant_id, linked_by_user_id, linked_by, status)
        VALUES (%s, %s, %s, %s, %s, 1)
        """,
        (chat_id, chat_title, merchant_id, linked_by_user_id, linked_by),
    )


async def unlink_group(chat_id: int):
    await db.execute(
        """
        UPDATE telegram_group_links
        SET status = 0, unlinked_at = NOW()
        WHERE chat_id = %s AND status = 1
        """,
        (chat_id,),
    )


async def get_balance(user_id: int) -> float:
    row = await db.fetchone(
        "SELECT balance FROM users WHERE id = %s LIMIT 1",
        (user_id,),
    )
    return float(row["balance"]) if row else 0.0


async def get_payin_order(user_id: int, order_no: str):
    return await db.fetchone(
        """
        SELECT order_no, plat_order_no, amount, paid_amount, net_amount,
               status, pay_type, created_at, completed_at
        FROM deposits
        WHERE merchant_id = %s AND order_no = %s
        LIMIT 1
        """,
        (user_id, order_no.strip()),
    )


async def get_payout_order(user_id: int, order_no: str):
    return await db.fetchone(
        """
        SELECT order_no, plat_order_no, amount, fee, net_amount,
               status, account_type, account_number, created_at, completed_at
        FROM withdrawals
        WHERE user_id = %s AND order_no = %s
        LIMIT 1
        """,
        (user_id, order_no.strip()),
    )


async def get_recent_payins(user_id: int, limit: int = 5):
    return await db.fetchall(
        """
        SELECT order_no, amount, status, created_at
        FROM deposits
        WHERE merchant_id = %s
        ORDER BY id DESC
        LIMIT %s
        """,
        (user_id, limit),
    )


async def get_recent_payouts(user_id: int, limit: int = 5):
    return await db.fetchall(
        """
        SELECT order_no, amount, status, created_at
        FROM withdrawals
        WHERE user_id = %s
        ORDER BY id DESC
        LIMIT %s
        """,
        (user_id, limit),
    )