import logging
from datetime import datetime
from db.database import cursor, conn

log = logging.getLogger("bot.transaction")


def save_transaction(user_id, package_type, package_label, harga_bayar, promo_used=None, status="approved", transaction_code=None):
    """
    Simpan transaksi ke database
    
    Args:
        user_id: ID user yang melakukan transaksi
        package_type: Tipe paket ('single', 'take_several', 'take_all')
        package_label: Label/nama paket untuk display
        harga_bayar: Harga yang dibayar
        promo_used: Kode promo yang digunakan (optional)
        status: Status transaksi ('approved', 'pending', 'rejected')
        transaction_code: Kode transaksi unik (optional)
    
    Returns:
        Transaction ID jika berhasil, None jika gagal
    """
    try:
        cursor.execute("""
            INSERT INTO transactions 
            (user_id, package_type, package_label, harga_bayar, promo_used, status, transaction_code)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        """, (user_id, package_type, package_label, harga_bayar, promo_used, status, transaction_code))
        conn.commit()
        
        transaction_id = cursor.lastrowid
        log.info(
            "[TRANSACTION] Transaksi ID %d (%s) disimpan - User: %s | Paket: %s | Harga: %s | Promo: %s | Status: %s",
            transaction_id, transaction_code or "-", user_id, package_label, harga_bayar, promo_used or "Tidak ada", status
        )
        return transaction_id
    except Exception as e:
        log.error("[TRANSACTION] Gagal menyimpan transaksi: %s", e)
        return None


def get_transaction(transaction_id):
    """
    Ambil detail transaksi berdasarkan ID
    
    Args:
        transaction_id: ID transaksi
    
    Returns:
        Dict berisi data transaksi atau None
    """
    try:
        cursor.execute("SELECT * FROM transactions WHERE id = ?", (transaction_id,))
        row = cursor.fetchone()
        if row:
            return dict(row)
        return None
    except Exception as e:
        log.error("[TRANSACTION] Gagal mengambil transaksi %s: %s", transaction_id, e)
        return None


def get_user_transactions(user_id, limit=10, offset=0):
    """
    Ambil daftar transaksi user
    
    Args:
        user_id: ID user
        limit: Jumlah transaksi yang diambil
        offset: Offset untuk pagination
    
    Returns:
        List of transactions
    """
    try:
        cursor.execute("""
            SELECT * FROM transactions 
            WHERE user_id = ? 
            ORDER BY created_at DESC 
            LIMIT ? OFFSET ?
        """, (user_id, limit, offset))
        rows = cursor.fetchall()
        return [dict(row) for row in rows]
    except Exception as e:
        log.error("[TRANSACTION] Gagal mengambil transaksi user %s: %s", user_id, e)
        return []


def get_user_transaction_count(user_id):
    """
    Hitung jumlah total transaksi user
    
    Args:
        user_id: ID user
    
    Returns:
        Jumlah transaksi
    """
    try:
        cursor.execute("SELECT COUNT(*) as count FROM transactions WHERE user_id = ?", (user_id,))
        row = cursor.fetchone()
        return row['count'] if row else 0
    except Exception as e:
        log.error("[TRANSACTION] Gagal menghitung transaksi user %s: %s", user_id, e)
        return 0


def update_transaction_status(transaction_id, status):
    """
    Update status transaksi
    
    Args:
        transaction_id: ID transaksi
        status: Status baru ('approved', 'pending', 'rejected')
    
    Returns:
        True jika berhasil, False jika gagal
    """
    try:
        cursor.execute(
            "UPDATE transactions SET status = ? WHERE id = ?",
            (status, transaction_id)
        )
        conn.commit()
        log.info("[TRANSACTION] Status transaksi %s diupdate menjadi: %s", transaction_id, status)
        return True
    except Exception as e:
        log.error("[TRANSACTION] Gagal update status transaksi %s: %s", transaction_id, e)
        return False


def get_transaction_summary(user_id):
    """
    Ambil ringkasan transaksi user
    
    Args:
        user_id: ID user
    
    Returns:
        Dict berisi summary transaksi
    """
    try:
        cursor.execute("""
            SELECT 
                COUNT(*) as total_transactions,
                SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved_count,
                SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected_count,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
                SUM(harga_bayar) as total_spent
            FROM transactions 
            WHERE user_id = ?
        """, (user_id,))
        row = cursor.fetchone()
        if row:
            return dict(row)
        return {
            "total_transactions": 0,
            "approved_count": 0,
            "rejected_count": 0,
            "pending_count": 0,
            "total_spent": 0
        }
    except Exception as e:
        log.error("[TRANSACTION] Gagal mengambil summary transaksi user %s: %s", user_id, e)
        return None


def get_user_total_approved_payment(user_id):
    try:
        cursor.execute(
            """
            SELECT COALESCE(SUM(harga_bayar), 0) as total_payment
            FROM transactions
            WHERE user_id = ? AND status = 'approved'
            """,
            (user_id,)
        )
        row = cursor.fetchone()
        return row["total_payment"] if row else 0
    except Exception as e:
        log.error("[TRANSACTION] Gagal menghitung total payment user %s: %s", user_id, e)
        return 0


def get_user_approved_vip_transactions(user_id):
    try:
        cursor.execute(
            """
            SELECT * FROM transactions
            WHERE user_id = ?
              AND status = 'approved'
              AND package_type IN ('single', 'take_several', 'take_all')
            ORDER BY created_at DESC
            """,
            (user_id,)
        )
        return [dict(r) for r in cursor.fetchall()]
    except Exception as e:
        log.error("[TRANSACTION] Gagal mengambil transaksi VIP user %s: %s", user_id, e)
        return []


def get_all_transactions(limit=50, offset=0):
    """
    Ambil semua transaksi (untuk admin reporting)
    
    Args:
        limit: Jumlah transaksi
        offset: Offset untuk pagination
    
    Returns:
        List of transactions
    """
    try:
        cursor.execute("""
            SELECT * FROM transactions 
            ORDER BY created_at DESC 
            LIMIT ? OFFSET ?
        """, (limit, offset))
        rows = cursor.fetchall()
        return [dict(row) for row in rows]
    except Exception as e:
        log.error("[TRANSACTION] Gagal mengambil semua transaksi: %s", e)
        return []


def _format_datetime_filter(value):
    if isinstance(value, datetime):
        return value.strftime("%Y-%m-%d %H:%M:%S")
    return value


def _created_at_where(start_at=None, end_at=None, column="created_at"):
    conditions = []
    params = []
    if start_at:
        conditions.append(f"{column} >= ?")
        params.append(_format_datetime_filter(start_at))
    if end_at:
        conditions.append(f"{column} < ?")
        params.append(_format_datetime_filter(end_at))
    if not conditions:
        return "", params
    return "WHERE " + " AND ".join(conditions), params


def _empty_stats():
    return {
        "total_transactions": 0,
        "total_users": 0,
        "approved_count": 0,
        "rejected_count": 0,
        "pending_count": 0,
        "total_revenue": 0,
        "total_amount": 0,
    }


def get_transactions_by_period(start_at=None, end_at=None, limit=50, offset=0):
    """Ambil transaksi dalam rentang waktu tertentu untuk laporan owner."""
    try:
        where_sql, params = _created_at_where(start_at, end_at)
        cursor.execute(f"""
            SELECT * FROM transactions
            {where_sql}
            ORDER BY created_at DESC
            LIMIT ? OFFSET ?
        """, (*params, limit, offset))
        rows = cursor.fetchall()
        return [dict(row) for row in rows]
    except Exception as e:
        log.error("[TRANSACTION] Gagal mengambil transaksi periode: %s", e)
        return []


def get_transaction_stats_by_period(start_at=None, end_at=None):
    """Ambil statistik transaksi dalam rentang waktu tertentu."""
    try:
        where_sql, params = _created_at_where(start_at, end_at)
        cursor.execute(f"""
            SELECT
                COUNT(*) as total_transactions,
                COUNT(DISTINCT user_id) as total_users,
                SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved_count,
                SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected_count,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
                SUM(CASE WHEN status = 'approved' THEN COALESCE(harga_bayar, 0) ELSE 0 END) as total_revenue,
                SUM(COALESCE(harga_bayar, 0)) as total_amount
            FROM transactions
            {where_sql}
        """, params)
        row = cursor.fetchone()
        if not row:
            return _empty_stats()
        stats = dict(row)
        empty = _empty_stats()
        for key, value in empty.items():
            stats[key] = stats.get(key) or value
        return stats
    except Exception as e:
        log.error("[TRANSACTION] Gagal mengambil statistik periode: %s", e)
        return _empty_stats()


def _admin_user_filter(search):
    value = str(search or "").strip()
    if value.startswith("@"):
        value = value[1:]
    if value.isdigit():
        return "t.user_id = ?", [int(value)]
    return "LOWER(u.username) = LOWER(?)", [value]


def get_user_transactions_for_admin(search, limit=10, offset=0):
    """Cari transaksi user untuk owner berdasarkan User ID atau username."""
    if not str(search or "").strip():
        return []
    try:
        where_sql, params = _admin_user_filter(search)
        cursor.execute(f"""
            SELECT
                t.*,
                u.first_name,
                u.username
            FROM transactions t
            LEFT JOIN users u ON u.user_id = t.user_id
            WHERE {where_sql}
            ORDER BY t.created_at DESC
            LIMIT ? OFFSET ?
        """, (*params, limit, offset))
        rows = cursor.fetchall()
        return [dict(row) for row in rows]
    except Exception as e:
        log.error("[TRANSACTION] Gagal mencari transaksi user %s: %s", search, e)
        return []


def get_user_transaction_stats_for_admin(search):
    """Ambil ringkasan transaksi user untuk owner berdasarkan User ID atau username."""
    if not str(search or "").strip():
        return _empty_stats()
    try:
        where_sql, params = _admin_user_filter(search)
        cursor.execute(f"""
            SELECT
                COUNT(*) as total_transactions,
                COUNT(DISTINCT t.user_id) as total_users,
                SUM(CASE WHEN t.status = 'approved' THEN 1 ELSE 0 END) as approved_count,
                SUM(CASE WHEN t.status = 'rejected' THEN 1 ELSE 0 END) as rejected_count,
                SUM(CASE WHEN t.status = 'pending' THEN 1 ELSE 0 END) as pending_count,
                SUM(CASE WHEN t.status = 'approved' THEN COALESCE(t.harga_bayar, 0) ELSE 0 END) as total_revenue,
                SUM(COALESCE(t.harga_bayar, 0)) as total_amount
            FROM transactions t
            LEFT JOIN users u ON u.user_id = t.user_id
            WHERE {where_sql}
        """, params)
        row = cursor.fetchone()
        if not row:
            return _empty_stats()
        stats = dict(row)
        empty = _empty_stats()
        for key, value in empty.items():
            stats[key] = stats.get(key) or value
        return stats
    except Exception as e:
        log.error("[TRANSACTION] Gagal mengambil statistik user %s: %s", search, e)
        return _empty_stats()


def get_transaction_stats():
    """
    Ambil statistik transaksi keseluruhan (untuk admin dashboard)
    
    Returns:
        Dict berisi statistik
    """
    try:
        cursor.execute("""
            SELECT 
                COUNT(*) as total_transactions,
                COUNT(DISTINCT user_id) as total_users,
                SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved_count,
                SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected_count,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
                SUM(harga_bayar) as total_revenue
            FROM transactions
        """)
        row = cursor.fetchone()
        if row:
            return dict(row)
        return {
            "total_transactions": 0,
            "total_users": 0,
            "approved_count": 0,
            "rejected_count": 0,
            "pending_count": 0,
            "total_revenue": 0
        }
    except Exception as e:
        log.error("[TRANSACTION] Gagal mengambil statistik transaksi: %s", e)
        return None