import re
from datetime import datetime, timedelta
from decimal import Decimal
from typing import Any, Optional

from config import CONFIG
from db import db_connection


_IDENTIFIER_RE = re.compile(r"^[A-Za-z0-9_]+$")
_FROZEN_MARK_RE = re.compile(r"[\(（]?\s*已冻结\s*[\)）]?")


class MappingError(RuntimeError):
    pass


def safe_identifier(value: str) -> str:
    if not value or not _IDENTIFIER_RE.fullmatch(value):
        raise MappingError(f"不安全或無效的資料庫識別字：{value!r}")
    return f"`{value}`"


def table_name(value: str) -> str:
    return safe_identifier(value)


def decimal_string(value: Any, default: str = "0.0000") -> str:
    if value is None or value == "":
        return default
    try:
        return format(Decimal(str(value)), "f")
    except Exception:
        return str(value)


def format_datetime(value: Any) -> str:
    if value is None:
        return ""
    if isinstance(value, datetime):
        return value.strftime("%Y-%m-%d %H:%M:%S")
    return str(value)


def clean_frozen_username(value: str) -> str:
    cleaned = _FROZEN_MARK_RE.sub("", str(value or ""))
    return cleaned.strip()


def is_finished_status(value: Any) -> bool:
    status = str(value or "").strip().lower()
    if not status:
        return False

    final_keywords = (
        "success", "successful", "completed", "complete", "approved",
        "paid", "done", "rejected", "cancelled", "canceled", "failed",
        "成功", "完成", "已支付", "已出款", "出款成功", "审核通过",
        "審核通過", "通过", "通過", "拒绝", "拒絕", "驳回", "駁回",
        "取消", "失败", "失敗",
    )
    return any(keyword in status for keyword in final_keywords)


class PlatformRepository:
    """依照現有 member_system 真實表結構建立的唯讀資料層。"""

    def ensure_simulation_tables(self) -> None:
        """唯讀測試版不建立資料表，也不對採集庫執行寫入。"""
        return

    def get_user_account(self, username: str) -> dict[str, Any]:
        """查詢 members，並由 username 是否包含「已冻结」判斷鎖定。"""
        login_username = str(username or "").strip()
        if not login_username:
            return self._not_found_account("")

        members = table_name(CONFIG.members_table)
        sql = f"""
            SELECT
                id,
                uid,
                username,
                username_clean,
                member_type,
                vip_level,
                points,
                tier,
                balance,
                frozen_balance,
                other_balance,
                deposit_count,
                deposit_amount,
                withdraw_count,
                withdraw_amount,
                register_time,
                last_login_time,
                remark,
                updated_at
            FROM {members}
            WHERE username = %s OR username_clean = %s
            ORDER BY
                CASE
                    WHEN username = %s THEN 0
                    WHEN username_clean = %s THEN 1
                    ELSE 2
                END,
                updated_at DESC,
                id DESC
            LIMIT 1
        """

        with db_connection() as conn, conn.cursor() as cursor:
            cursor.execute(
                sql,
                (login_username, login_username, login_username, login_username),
            )
            row = cursor.fetchone()

            # 少數舊資料可能未回填 username_clean；再用移除凍結標記後比對。
            if not row:
                fallback_sql = f"""
                    SELECT
                        id,
                        uid,
                        username,
                        username_clean,
                        member_type,
                        vip_level,
                        points,
                        tier,
                        balance,
                        frozen_balance,
                        other_balance,
                        deposit_count,
                        deposit_amount,
                        withdraw_count,
                        withdraw_amount,
                        register_time,
                        last_login_time,
                        remark,
                        updated_at
                    FROM {members}
                    WHERE TRIM(
                        REPLACE(
                            REPLACE(
                                REPLACE(username, '(已冻结)', ''),
                                '（已冻结）', ''
                            ),
                            '已冻结', ''
                        )
                    ) = %s
                    ORDER BY updated_at DESC, id DESC
                    LIMIT 1
                """
                cursor.execute(fallback_sql, (login_username,))
                row = cursor.fetchone()

        if not row:
            return self._not_found_account(login_username)

        raw_username = str(row.get("username") or login_username).strip()
        clean_username = str(row.get("username_clean") or "").strip()
        if not clean_username:
            clean_username = clean_frozen_username(raw_username)
        if not clean_username:
            clean_username = login_username

        locked = "已冻结" in raw_username
        remark = str(row.get("remark") or "").strip()

        return {
            "username": clean_username,
            "raw_username": raw_username,
            "username_clean": clean_username,
            "uid": row.get("uid"),
            "is_exist": True,
            "status": "locked" if locked else "normal",
            "locked_reason": remark if locked else "",
            "remark": remark,
            "member_type": str(row.get("member_type") or ""),
            "vip_level": str(row.get("vip_level") or ""),
            "points": decimal_string(row.get("points")),
            "tier": str(row.get("tier") or ""),
            "balance": decimal_string(row.get("balance")),
            "frozen_balance": decimal_string(row.get("frozen_balance")),
            "other_balance": decimal_string(row.get("other_balance")),
            "deposit_count": int(row.get("deposit_count") or 0),
            "deposit_amount": decimal_string(row.get("deposit_amount")),
            "withdraw_count": int(row.get("withdraw_count") or 0),
            "withdraw_amount": decimal_string(row.get("withdraw_amount")),
            "register_time": format_datetime(row.get("register_time")),
            "last_login_time": format_datetime(row.get("last_login_time")),
        }

    @staticmethod
    def _not_found_account(username: str) -> dict[str, Any]:
        return {
            "username": username,
            "raw_username": "",
            "username_clean": username,
            "uid": None,
            "is_exist": False,
            "status": "not_found",
            "locked_reason": "",
        }

    def get_balance(self, username: str) -> Decimal:
        account = self.get_user_account(username)
        if not account["is_exist"]:
            raise MappingError(f"members 表找不到會員：{username}")
        return Decimal(str(account.get("balance") or "0"))

    def get_withdraw_orders(
        self,
        username: str,
        unfinished_only: bool = False,
    ) -> list[dict[str, Any]]:
        account = self.get_user_account(username)
        clean_username = (
            account["username_clean"] if account["is_exist"] else str(username).strip()
        )
        raw_username = (
            account["raw_username"] if account["is_exist"] else str(username).strip()
        )

        records = self._query_withdraw_records(clean_username, raw_username)
        requests = self._query_withdraw_requests(clean_username, raw_username)
        combined = records + requests

        if unfinished_only:
            combined = [
                item for item in combined
                if not is_finished_status(item.get("status"))
            ]

        combined.sort(
            key=lambda item: item.get("sort_time") or datetime.min,
            reverse=True,
        )
        for item in combined:
            item.pop("sort_time", None)
        return combined[: CONFIG.max_orders]

    def _query_withdraw_records(
        self,
        clean_username: str,
        raw_username: str,
    ) -> list[dict[str, Any]]:
        table = table_name(CONFIG.withdraw_table)
        sql = f"""
            SELECT
                withdraw_id,
                withdraw_id_2,
                username,
                username_clean,
                withdraw_amount,
                fee,
                arrival_amount,
                withdraw_channel,
                withdraw_time,
                status,
                remark,
                reviewer,
                review_time,
                processor,
                process_time
            FROM {table}
            WHERE
                username_clean = %s
                OR username = %s
                OR username = %s
            ORDER BY withdraw_time DESC, id DESC
            LIMIT %s
        """
        with db_connection() as conn, conn.cursor() as cursor:
            cursor.execute(
                sql,
                (clean_username, clean_username, raw_username, CONFIG.max_orders),
            )
            rows = cursor.fetchall()

        return [
            {
                "source": "withdraw_records",
                "order_id": str(
                    row.get("withdraw_id") or row.get("withdraw_id_2") or ""
                ),
                "username": clean_username,
                "status": str(row.get("status") or ""),
                "amount": float(row.get("withdraw_amount") or 0),
                "fee": float(row.get("fee") or 0),
                "arrival_amount": float(row.get("arrival_amount") or 0),
                "withdraw_channel": str(row.get("withdraw_channel") or ""),
                "create_time": format_datetime(row.get("withdraw_time")),
                "fail_reason": str(row.get("remark") or ""),
                "reviewer": str(row.get("reviewer") or ""),
                "review_time": format_datetime(row.get("review_time")),
                "processor": str(row.get("processor") or ""),
                "process_time": format_datetime(row.get("process_time")),
                "sort_time": row.get("withdraw_time") or datetime.min,
            }
            for row in rows
        ]

    def _query_withdraw_requests(
        self,
        clean_username: str,
        raw_username: str,
    ) -> list[dict[str, Any]]:
        """查詢提款申請。

        狀態判斷口徑：
        1. operation 為主要狀態欄位；
        2. operation 為空時才使用 audit_status；
        3. 同一 withdraw_request_id 只保留最新一筆；
        4. withdraw_request_id 為空時保留該筆資料，不做錯誤合併。
        """
        table = table_name(CONFIG.withdraw_request_table)

        # 多抓一些歷史快照，再於 Python 端依 withdraw_request_id 去重。
        # SQL 已按 request_time、id 倒序，所以第一筆就是最新狀態。
        fetch_limit = max(CONFIG.max_orders * 5, CONFIG.max_orders)

        sql = f"""
            SELECT
                id,
                withdraw_request_id,
                username_raw,
                username_clean,
                current_balance,
                withdraw_channel,
                withdraw_amount,
                request_time,
                turnover,
                operation,
                auto_withdraw,
                audit_status,
                remark
            FROM {table}
            WHERE
                username_clean = %s
                OR username_raw = %s
                OR username_raw = %s
                OR username_raw LIKE %s
                OR username_raw LIKE %s
            ORDER BY request_time DESC, id DESC
            LIMIT %s
        """

        with db_connection() as conn, conn.cursor() as cursor:
            cursor.execute(
                sql,
                (
                    clean_username,
                    clean_username,
                    raw_username,
                    f"{clean_username}(%",
                    f"{clean_username}（%",
                    fetch_limit,
                ),
            )
            rows = cursor.fetchall()

        latest_rows: list[dict[str, Any]] = []
        seen_request_ids: set[str] = set()

        for row in rows:
            request_id = row.get("withdraw_request_id")

            # 有 request_id：只保留倒序結果中的第一筆，也就是最新狀態。
            if request_id not in (None, ""):
                request_key = str(request_id)
                if request_key in seen_request_ids:
                    continue
                seen_request_ids.add(request_key)

            # 沒有 request_id：無法可靠判定是否同一筆申請，因此保留。
            latest_rows.append(row)

            if len(latest_rows) >= CONFIG.max_orders:
                break

        result: list[dict[str, Any]] = []

        for row in latest_rows:
            operation = str(row.get("operation") or "").strip()
            audit_status = str(row.get("audit_status") or "").strip()

            # 提款申請狀態以 operation 為主，audit_status 僅作備援。
            status = operation or audit_status or "UNKNOWN"

            result.append(
                {
                    "source": "withdraw_request_records",
                    "order_id": str(row.get("withdraw_request_id") or ""),
                    "record_id": int(row.get("id") or 0),
                    "username": clean_username,
                    "status": status,
                    "operation": operation,
                    "audit_status": audit_status,
                    "amount": float(row.get("withdraw_amount") or 0),
                    "fee": 0.0,
                    "arrival_amount": 0.0,
                    "current_balance": float(row.get("current_balance") or 0),
                    "turnover": float(row.get("turnover") or 0),
                    "withdraw_channel": str(row.get("withdraw_channel") or ""),
                    "create_time": format_datetime(row.get("request_time")),
                    "fail_reason": str(row.get("remark") or ""),
                    "auto_withdraw": str(row.get("auto_withdraw") or ""),
                    "sort_time": row.get("request_time") or datetime.min,
                }
            )

        return result

    def get_deposit_orders(
        self,
        username: str,
        amount: Optional[Decimal] = None,
        created_at: Optional[datetime] = None,
    ) -> list[dict[str, Any]]:
        account = self.get_user_account(username)
        clean_username = (
            account["username_clean"] if account["is_exist"] else str(username).strip()
        )
        raw_username = (
            account["raw_username"] if account["is_exist"] else str(username).strip()
        )

        table = table_name(CONFIG.deposit_table)
        conditions = [
            "(username_clean = %s OR username = %s OR username = %s)"
        ]
        params: list[Any] = [clean_username, clean_username, raw_username]

        if amount is not None:
            conditions.append("ABS(deposit_amount - %s) < 0.01")
            params.append(amount)

        if created_at is not None:
            hours = CONFIG.deposit_search_hours
            conditions.append("deposit_time BETWEEN %s AND %s")
            params.extend(
                [
                    created_at - timedelta(hours=hours),
                    created_at + timedelta(hours=hours),
                ]
            )

        sql = f"""
            SELECT
                deposit_id,
                deposit_no,
                username,
                username_clean,
                deposit_amount,
                actual_arrival_amount,
                crypto_amount,
                fee,
                deposit_bank,
                deposit_type,
                status,
                description,
                deposit_time,
                arrival_time,
                operation_time,
                backend_remark,
                frontend_remark
            FROM {table}
            WHERE {' AND '.join(conditions)}
            ORDER BY deposit_time DESC, id DESC
            LIMIT %s
        """
        params.append(CONFIG.max_orders)

        with db_connection() as conn, conn.cursor() as cursor:
            cursor.execute(sql, params)
            rows = cursor.fetchall()

        return [
            {
                "username": clean_username,
                "thirdparty_order_id": str(
                    row.get("deposit_no") or row.get("deposit_id") or ""
                ),
                "deposit_id": str(row.get("deposit_id") or ""),
                "deposit_no": str(row.get("deposit_no") or ""),
                "amount": float(row.get("deposit_amount") or 0),
                "actual_arrival_amount": float(
                    row.get("actual_arrival_amount") or 0
                ),
                "crypto_amount": float(row.get("crypto_amount") or 0),
                "fee": float(row.get("fee") or 0),
                "status": str(row.get("status") or ""),
                "deposit_bank": str(row.get("deposit_bank") or ""),
                "deposit_type": str(row.get("deposit_type") or ""),
                "description": str(row.get("description") or ""),
                "created_at": format_datetime(row.get("deposit_time")),
                "arrival_time": format_datetime(row.get("arrival_time")),
                "operation_time": format_datetime(row.get("operation_time")),
                "backend_remark": str(row.get("backend_remark") or ""),
                "frontend_remark": str(row.get("frontend_remark") or ""),
            }
            for row in rows
        ]

    def apply_activity(self, username: str, activity_id: str) -> dict[str, Any]:
        """唯讀測試：只模擬資格回傳，不寫入資料庫。"""
        account = self.get_user_account(username)
        if not account["is_exist"]:
            return {
                "activity_found": False,
                "qualified": False,
                "status": "user_not_found",
                "simulated": True,
            }

        activity_found = activity_id == CONFIG.default_activity_id
        return {
            "activity_found": activity_found,
            "qualified": activity_found,
            "status": (
                "simulated_qualified" if activity_found else "activity_not_found"
            ),
            "simulated": True,
        }

    def log_balance_fix(self, username: str, balance: Decimal) -> None:
        """唯讀模式不寫入任何修復日誌。"""
        return