import sqlite3
import os
import asyncio
from datetime import datetime

DB_PATH = os.path.join(os.path.dirname(__file__), "hilman_bot.db")

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

def _init_db():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # 1. Uyarilar Tablosu
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS uyarilar (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        guild_id INTEGER,
        moderator_id INTEGER,
        sebep TEXT,
        tarih TEXT
    )
    """)
    
    # 2. Mutes Tablosu
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS mutes (
        user_id INTEGER,
        guild_id INTEGER,
        unmute_time TEXT,
        PRIMARY KEY (user_id, guild_id)
    )
    """)
    
    # 3. Anti-Raid Ayarlari Tablosu
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS anti_raid_ayarlar (
        guild_id INTEGER PRIMARY KEY,
        ban_limiti INTEGER DEFAULT 3,
        kick_limiti INTEGER DEFAULT 3,
        kanal_silme_limiti INTEGER DEFAULT 3,
        zaman_penceresi INTEGER DEFAULT 60,
        alt_yas_gunu INTEGER DEFAULT 14,
        benzerlik_orani REAL DEFAULT 0.8,
        oto_ban_alt INTEGER DEFAULT 1,
        log_kanal_id INTEGER DEFAULT 0,
        link_muaf_rol_id INTEGER DEFAULT 0
    )
    """)
    
    # 4. Anti-Raid Beyaz Liste
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS anti_raid_beyazliste (
        guild_id INTEGER,
        user_id INTEGER,
        PRIMARY KEY (guild_id, user_id)
    )
    """)
    
    # 5. Yapay Zeka Sohbet Gecmisi
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS sohbet_gecmisi (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        guild_id INTEGER,
        channel_id INTEGER,
        user_id INTEGER,
        username TEXT,
        role TEXT, -- 'user' veya 'assistant'
        message TEXT,
        timestamp TEXT
    )
    """)
    
    # 6. Partner Ayarlari
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS partner_ayarlar (
        guild_id INTEGER PRIMARY KEY,
        partner_kanal_id INTEGER DEFAULT 0,
        partner_text TEXT DEFAULT '',
        yetkili_rol_id INTEGER DEFAULT 0,
        yetkili_kanal_id INTEGER DEFAULT 0
    )
    """)
    
    # 7. Partner Kayitlari (Siralama Icin)
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS partner_kayitlar (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        guild_id INTEGER,
        user_id INTEGER,
        staff_id INTEGER, -- Bot ile onaylandiyse onaylayan yetkili, yoksa NULL
        invite_link TEXT,
        partner_text TEXT,
        type TEXT, -- 'bot_approval' veya 'manual_post'
        timestamp TEXT
    )
    """)
    
    # 8. Mod Log Ayarlari
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS log_ayarlar (
        guild_id INTEGER PRIMARY KEY,
        log_kanal_id INTEGER DEFAULT 0
    )
    """)

    # 9. Aktiflik Mesaj Sayilari
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS aktiflik_mesaj (
        guild_id INTEGER,
        user_id INTEGER,
        gun_tarih TEXT,
        mesaj_sayisi INTEGER DEFAULT 0,
        PRIMARY KEY (guild_id, user_id, gun_tarih)
    )
    """)

    # 10. Aktiflik Ses Sureleri
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS aktiflik_ses (
        guild_id INTEGER,
        user_id INTEGER,
        gun_tarih TEXT,
        ses_suresi_sn INTEGER DEFAULT 0,
        PRIMARY KEY (guild_id, user_id, gun_tarih)
    )
    """)

    # 11. Welcome / Goodbye ayarlari
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS welcome_ayarlar (
        guild_id INTEGER PRIMARY KEY,
        kanal_id INTEGER DEFAULT 0,
        mesaj TEXT DEFAULT '',
        ayrilma_mesaj TEXT DEFAULT '',
        aktif INTEGER DEFAULT 0
    )
    """)

    # 12. Seviye sistemi
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS seviye (
        guild_id INTEGER,
        user_id INTEGER,
        xp INTEGER DEFAULT 0,
        seviye INTEGER DEFAULT 0,
        PRIMARY KEY (guild_id, user_id)
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS seviye_ayarlar (
        guild_id INTEGER PRIMARY KEY,
        mesaj_kanal_id INTEGER DEFAULT 0,
        aktif INTEGER DEFAULT 1,
        zorluk INTEGER DEFAULT 100
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS seviye_oduller (
        guild_id INTEGER,
        seviye INTEGER,
        rol_id INTEGER,
        PRIMARY KEY (guild_id, seviye)
    )
    """)

    # 13. Cekilis sistemi
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS cekilisler (
        mesaj_id INTEGER PRIMARY KEY,
        kanal_id INTEGER,
        guild_id INTEGER,
        baslatan_id INTEGER,
        odul TEXT,
        sure INTEGER,
        kazsayi INTEGER,
        bitis_zamani TEXT,
        aktif INTEGER DEFAULT 1
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS cekilis_katilimcilar (
        mesaj_id INTEGER,
        user_id INTEGER,
        PRIMARY KEY (mesaj_id, user_id)
    )
    """)

    # 14. Oneri sistemi
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS oneriler (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        guild_id INTEGER,
        kanal_id INTEGER,
        user_id INTEGER,
        mesaj_id INTEGER,
        icerik TEXT,
        durum TEXT DEFAULT 'acik',
        timestamp TEXT
    )
    """)

    # 15. Reaction roller
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS reaction_roller (
        mesaj_id INTEGER,
        kanal_id INTEGER,
        guild_id INTEGER,
        emoji TEXT,
        rol_id INTEGER,
        aciklama TEXT,
        PRIMARY KEY (mesaj_id, emoji)
    )
    """)

    # 16. Oto-rol ayarlari
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS otorol_ayarlar (
        guild_id INTEGER PRIMARY KEY,
        rol_id INTEGER DEFAULT 0,
        bot_rol_id INTEGER DEFAULT 0,
        aktif INTEGER DEFAULT 0
    )
    """)

    # 17. Dogrulama ayarlari
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS dogrulama_ayarlar (
        guild_id INTEGER PRIMARY KEY,
        kanal_id INTEGER DEFAULT 0,
        rol_id INTEGER DEFAULT 0,
        aktif INTEGER DEFAULT 0
    )
    """)

    # 18. Gecici kanal ayarlari
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS gecici_kanal_ayarlar (
        guild_id INTEGER PRIMARY KEY,
        kategori_id INTEGER DEFAULT 0,
        kanal_olustur_kanal_id INTEGER DEFAULT 0,
        aktif INTEGER DEFAULT 0
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS gecici_kanallar (
        kanal_id INTEGER PRIMARY KEY,
        guild_id INTEGER,
        sahip_id INTEGER
    )
    """)

    # 19. Ekonomi sistemi
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS ekonomi (
        guild_id INTEGER,
        user_id INTEGER,
        bakiye INTEGER DEFAULT 0,
        banka INTEGER DEFAULT 0,
        son_gunluk TEXT,
        PRIMARY KEY (guild_id, user_id)
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS ekonomi_magaza (
        guild_id INTEGER,
        urun_id INTEGER PRIMARY KEY AUTOINCREMENT,
        isim TEXT,
        fiyat INTEGER,
        rol_id INTEGER DEFAULT 0,
        stok INTEGER DEFAULT -1
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS ekonomi_envanter (
        guild_id INTEGER,
        user_id INTEGER,
        urun_id INTEGER,
        adet INTEGER DEFAULT 1,
        PRIMARY KEY (guild_id, user_id, urun_id)
    )
    """)

    # 20. Dogum gunu
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS dogum_gunleri (
        guild_id INTEGER,
        user_id INTEGER,
        gun INTEGER,
        ay INTEGER,
        PRIMARY KEY (guild_id, user_id)
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS dogum_gunu_ayarlar (
        guild_id INTEGER PRIMARY KEY,
        kanal_id INTEGER DEFAULT 0,
        rol_id INTEGER DEFAULT 0,
        aktif INTEGER DEFAULT 0
    )
    """)

    # 21. Sayi sayma
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS sayi_sayma (
        guild_id INTEGER PRIMARY KEY,
        kanal_id INTEGER DEFAULT 0,
        son_sayi INTEGER DEFAULT 0,
        son_atan INTEGER DEFAULT 0,
        rekor INTEGER DEFAULT 0,
        aktif INTEGER DEFAULT 0
    )
    """)

    # 22. Hatirlatici
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS hatirlaticilar (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        guild_id INTEGER,
        kanal_id INTEGER,
        user_id INTEGER,
        mesaj TEXT,
        bitis_zamani TEXT
    )
    """)

    # 23. Ozel komutlar
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS ozel_komutlar (
        guild_id INTEGER,
        isim TEXT,
        yanit TEXT,
        PRIMARY KEY (guild_id, isim)
    )
    """)

    # 24. Stream bildirimleri
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS stream_bildirimleri (
        guild_id INTEGER,
        kanal_id INTEGER,
        platform TEXT,
        kullanici_adi TEXT,
        PRIMARY KEY (guild_id, platform, kullanici_adi)
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS stream_aktif (
        guild_id INTEGER,
        platform TEXT,
        kullanici_adi TEXT,
        mesaj_id INTEGER,
        PRIMARY KEY (guild_id, platform, kullanici_adi)
    )
    """)

    # 25. Ticked sistemi
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS ticket_ayarlar (
        guild_id INTEGER PRIMARY KEY,
        kategori_id INTEGER DEFAULT 0,
        log_kanal_id INTEGER DEFAULT 0,
        yetkili_rol_id INTEGER DEFAULT 0,
        panel_mesaj_id INTEGER DEFAULT 0,
        panel_kanal_id INTEGER DEFAULT 0,
        aktif INTEGER DEFAULT 0
    )
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS ticket_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        guild_id INTEGER,
        kanal_id INTEGER,
        user_id INTEGER,
        durum TEXT,
        timestamp TEXT
    )
    """)
    
    # 26. Modul Durumlari
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS modul_durumlari (
        guild_id INTEGER,
        modul_adi TEXT,
        aktif INTEGER DEFAULT 1,
        PRIMARY KEY (guild_id, modul_adi)
    )
    """)
    
    # Sütun yoksa ekle (Migration)
    try:
        cursor.execute("ALTER TABLE anti_raid_ayarlar ADD COLUMN link_muaf_rol_id INTEGER DEFAULT 0")
    except sqlite3.OperationalError:
        pass
        
    conn.commit()
    conn.close()

# Veritabani Ilk Kurulumu
_init_db()

class Database:
    @staticmethod
    def _execute(query, params=(), fetch=None):
        conn = sqlite3.connect(DB_PATH)
        conn.row_factory = dict_factory
        cursor = conn.cursor()
        try:
            cursor.execute(query, params)
            if fetch == "all":
                res = cursor.fetchall()
            elif fetch == "one":
                res = cursor.fetchone()
            else:
                conn.commit()
                res = cursor.lastrowid if query.strip().upper().startswith("INSERT") else cursor.rowcount
            return res
        finally:
            conn.close()

    @classmethod
    async def execute(cls, query, params=()):
        return await asyncio.to_thread(cls._execute, query, params)

    @classmethod
    async def fetch_all(cls, query, params=()):
        return await asyncio.to_thread(cls._execute, query, params, fetch="all")

    @classmethod
    async def fetch_one(cls, query, params=()):
        return await asyncio.to_thread(cls._execute, query, params, fetch="one")

    # --- UYARI METODLARI ---
    @classmethod
    async def uyari_ekle(cls, user_id, guild_id, moderator_id, sebep):
        tarih = datetime.now().isoformat()
        return await cls.execute(
            "INSERT INTO uyarilar (user_id, guild_id, moderator_id, sebep, tarih) VALUES (?, ?, ?, ?, ?)",
            (user_id, guild_id, moderator_id, sebep, tarih)
        )

    @classmethod
    async def uyari_sil(cls, uyari_id, guild_id):
        return await cls.execute(
            "DELETE FROM uyarilar WHERE id = ? AND guild_id = ?",
            (uyari_id, guild_id)
        )

    @classmethod
    async def uyarilari_temizle(cls, user_id, guild_id):
        return await cls.execute(
            "DELETE FROM uyarilar WHERE user_id = ? AND guild_id = ?",
            (user_id, guild_id)
        )

    @classmethod
    async def uyarilari_getir(cls, user_id, guild_id):
        return await cls.fetch_all(
            "SELECT * FROM uyarilar WHERE user_id = ? AND guild_id = ? ORDER BY id DESC",
            (user_id, guild_id)
        )

    # --- MUTE METODLARI ---
    @classmethod
    async def mute_ekle(cls, user_id, guild_id, unmute_time):
        return await cls.execute(
            "INSERT OR REPLACE INTO mutes (user_id, guild_id, unmute_time) VALUES (?, ?, ?)",
            (user_id, guild_id, unmute_time.isoformat())
        )

    @classmethod
    async def mute_kaldir(cls, user_id, guild_id):
        return await cls.execute(
            "DELETE FROM mutes WHERE user_id = ? AND guild_id = ?",
            (user_id, guild_id)
        )

    @classmethod
    async def aktif_mutelari_getir(cls):
        return await cls.fetch_all("SELECT * FROM mutes")

    # --- ANTI-RAID AYAR METODLARI ---
    @classmethod
    async def anti_raid_ayarlarini_getir(cls, guild_id):
        row = await cls.fetch_one("SELECT * FROM anti_raid_ayarlar WHERE guild_id = ?", (guild_id,))
        if not row:
            # Varsayilan kayit olustur
            await cls.execute("INSERT OR IGNORE INTO anti_raid_ayarlar (guild_id) VALUES (?)", (guild_id,))
            row = await cls.fetch_one("SELECT * FROM anti_raid_ayarlar WHERE guild_id = ?", (guild_id,))
        return row

    @classmethod
    async def anti_raid_ayarla(cls, guild_id, ban_l, kick_l, kanal_silme_l, pencere, alt_yas, benzerlik, oto_ban, log_id):
        await cls.execute("INSERT OR IGNORE INTO anti_raid_ayarlar (guild_id) VALUES (?)", (guild_id,))
        return await cls.execute(
            """UPDATE anti_raid_ayarlar SET 
            ban_limiti = ?, kick_limiti = ?, kanal_silme_limiti = ?, zaman_penceresi = ?, 
            alt_yas_gunu = ?, benzerlik_orani = ?, oto_ban_alt = ?, log_kanal_id = ?
            WHERE guild_id = ?""",
            (ban_l, kick_l, kanal_silme_l, pencere, alt_yas, benzerlik, oto_ban, log_id, guild_id)
        )

    @classmethod
    async def link_muaf_rol_ayarla(cls, guild_id, rol_id):
        await cls.execute("INSERT OR IGNORE INTO anti_raid_ayarlar (guild_id) VALUES (?)", (guild_id,))
        return await cls.execute(
            "UPDATE anti_raid_ayarlar SET link_muaf_rol_id = ? WHERE guild_id = ?",
            (rol_id, guild_id)
        )

    @classmethod
    async def beyaz_liste_ekle(cls, guild_id, user_id):
        return await cls.execute("INSERT OR IGNORE INTO anti_raid_beyazliste (guild_id, user_id) VALUES (?, ?)", (guild_id, user_id))

    @classmethod
    async def beyaz_liste_sil(cls, guild_id, user_id):
        return await cls.execute("DELETE FROM anti_raid_beyazliste WHERE guild_id = ? AND user_id = ?", (guild_id, user_id))

    @classmethod
    async def beyaz_liste_kontrol(cls, guild_id, user_id):
        row = await cls.fetch_one("SELECT 1 FROM anti_raid_beyazliste WHERE guild_id = ? AND user_id = ?", (guild_id, user_id))
        return row is not None

    @classmethod
    async def beyaz_liste_getir(cls, guild_id):
        return await cls.fetch_all("SELECT user_id FROM anti_raid_beyazliste WHERE guild_id = ?", (guild_id,))

    # --- SOHBET GECMISI METODLARI ---
    @classmethod
    async def sohbet_kaydet(cls, guild_id, channel_id, user_id, username, role, message):
        tarih = datetime.now().isoformat()
        return await cls.execute(
            "INSERT INTO sohbet_gecmisi (guild_id, channel_id, user_id, username, role, message, timestamp) VALUES (?, ?, ?, ?, ?, ?, ?)",
            (guild_id, channel_id, user_id, username, role, message, tarih)
        )

    @classmethod
    async def sohbet_gecmisi_getir(cls, channel_id, limit=10):
        return await cls.fetch_all(
            "SELECT role, message FROM sohbet_gecmisi WHERE channel_id = ? ORDER BY id DESC LIMIT ?",
            (channel_id, limit)
        )

    # --- PARTNER AYAR METODLARI ---
    @classmethod
    async def partner_ayarlarini_getir(cls, guild_id):
        row = await cls.fetch_one("SELECT * FROM partner_ayarlar WHERE guild_id = ?", (guild_id,))
        if not row:
            await cls.execute("INSERT OR IGNORE INTO partner_ayarlar (guild_id) VALUES (?)", (guild_id,))
            row = await cls.fetch_one("SELECT * FROM partner_ayarlar WHERE guild_id = ?", (guild_id,))
        return row

    @classmethod
    async def partner_ayarla(cls, guild_id, kanal_id, text, rol_id, yetkili_kanal_id):
        return await cls.execute(
            "INSERT OR REPLACE INTO partner_ayarlar (guild_id, partner_kanal_id, partner_text, yetkili_rol_id, yetkili_kanal_id) VALUES (?, ?, ?, ?, ?)",
            (guild_id, kanal_id, text, rol_id, yetkili_kanal_id)
        )

    # --- PARTNER KAYIT METODLARI ---
    @classmethod
    async def partner_kaydet(cls, guild_id, user_id, staff_id, invite_link, partner_text, p_type):
        tarih = datetime.now().isoformat()
        return await cls.execute(
            "INSERT INTO partner_kayitlar (guild_id, user_id, staff_id, invite_link, partner_text, type, timestamp) VALUES (?, ?, ?, ?, ?, ?, ?)",
            (guild_id, user_id, staff_id, invite_link, partner_text, p_type, tarih)
        )

    @classmethod
    async def partner_siralamasi_getir(cls, guild_id, baslangic_tarihi):
        # Baslangic tarihinden itibaren partner_kayitlar tablosundaki kayitlari say
        # Hem user_id'ye gore hem de staff_id'ye gore toplam katilimlari topla veya ayri ayri sirala.
        # User ve staff'in her ikisini de kim kac partnerlik yapmis olarak sayalim.
        # Manuel post'larda user_id vardir, staff_id boştur. Bot ile onayda user_id (ortaklik yapan kisi) ve staff_id (onaylayan yetkili) vardir.
        # Her ikisi icin de ayri ayri veya birlesik sayabiliriz. Kullanici: "kim kac tane atmis hepsini yazsin, botu onaylamak dahil".
        # Yani onaylayan yetkiliyi de, linki atan kisi veya yetkiliyi de sayacagiz.
        # Sorgu: Her bir user_id veya staff_id icin basari sayilari.
        # Bir kişinin hem üye olarak yaptığı, hem de yetkili olarak onayladığı toplam partner sayısını hesaplamak için birleşik bir sorgu yapalım.
        query = """
        SELECT user_id_comb as user_id, COUNT(*) as count FROM (
            SELECT user_id as user_id_comb FROM partner_kayitlar WHERE guild_id = ? AND timestamp >= ?
            UNION ALL
            SELECT staff_id as user_id_comb FROM partner_kayitlar WHERE guild_id = ? AND timestamp >= ? AND staff_id IS NOT NULL
        )
        GROUP BY user_id_comb
        ORDER BY count DESC
        """
        return await cls.fetch_all(query, (guild_id, baslangic_tarihi.isoformat(), guild_id, baslangic_tarihi.isoformat()))

    # --- LOG AYAR METODLARI ---
    @classmethod
    async def log_kanali_getir(cls, guild_id):
        row = await cls.fetch_one("SELECT log_kanal_id FROM log_ayarlar WHERE guild_id = ?", (guild_id,))
        return row['log_kanal_id'] if row else 0

    @classmethod
    async def log_kanali_ayarla(cls, guild_id, kanal_id):
        return await cls.execute(
            "INSERT OR REPLACE INTO log_ayarlar (guild_id, log_kanal_id) VALUES (?, ?)",
            (guild_id, kanal_id)
        )

    # --- AKTIFLIK METODLARI ---
    @classmethod
    async def aktiflik_mesaj_arttir(cls, guild_id, user_id, gun_tarih):
        return await cls.execute(
            """INSERT INTO aktiflik_mesaj (guild_id, user_id, gun_tarih, mesaj_sayisi) 
            VALUES (?, ?, ?, 1) 
            ON CONFLICT(guild_id, user_id, gun_tarih) 
            DO UPDATE SET mesaj_sayisi = mesaj_sayisi + 1""",
            (guild_id, user_id, gun_tarih)
        )

    @classmethod
    async def aktiflik_ses_ekle(cls, guild_id, user_id, gun_tarih, saniye):
        return await cls.execute(
            """INSERT INTO aktiflik_ses (guild_id, user_id, gun_tarih, ses_suresi_sn) 
            VALUES (?, ?, ?, ?) 
            ON CONFLICT(guild_id, user_id, gun_tarih) 
            DO UPDATE SET ses_suresi_sn = ses_suresi_sn + ?""",
            (guild_id, user_id, gun_tarih, saniye, saniye)
        )

    @classmethod
    async def aktiflik_getir(cls, guild_id, user_id):
        toplam_mesaj = await cls.fetch_one("SELECT SUM(mesaj_sayisi) as toplam FROM aktiflik_mesaj WHERE guild_id = ? AND user_id = ?", (guild_id, user_id))
        toplam_ses = await cls.fetch_one("SELECT SUM(ses_suresi_sn) as toplam FROM aktiflik_ses WHERE guild_id = ? AND user_id = ?", (guild_id, user_id))
        
        son_7_gun_mesaj = await cls.fetch_all(
            "SELECT gun_tarih, mesaj_sayisi FROM aktiflik_mesaj WHERE guild_id = ? AND user_id = ? ORDER BY gun_tarih DESC LIMIT 7",
            (guild_id, user_id)
        )
        son_7_gun_ses = await cls.fetch_all(
            "SELECT gun_tarih, ses_suresi_sn FROM aktiflik_ses WHERE guild_id = ? AND user_id = ? ORDER BY gun_tarih DESC LIMIT 7",
            (guild_id, user_id)
        )
        return {
            "toplam_mesaj": toplam_mesaj['toplam'] if toplam_mesaj and toplam_mesaj['toplam'] else 0,
            "toplam_ses": toplam_ses['toplam'] if toplam_ses and toplam_ses['toplam'] else 0,
            "son_7_gun_mesaj": son_7_gun_mesaj,
            "son_7_gun_ses": son_7_gun_ses
        }

    @classmethod
    async def aktiflik_siralamasi_mesaj(cls, guild_id, limit=10):
        return await cls.fetch_all(
            "SELECT user_id, SUM(mesaj_sayisi) as toplam FROM aktiflik_mesaj WHERE guild_id = ? GROUP BY user_id ORDER BY toplam DESC LIMIT ?",
            (guild_id, limit)
        )

    @classmethod
    async def aktiflik_siralamasi_ses(cls, guild_id, limit=10):
        return await cls.fetch_all(
            "SELECT user_id, SUM(ses_suresi_sn) as toplam FROM aktiflik_ses WHERE guild_id = ? GROUP BY user_id ORDER BY toplam DESC LIMIT ?",
            (guild_id, limit)
        )

    @classmethod
    async def modul_aktif_mi(cls, guild_id, modul_adi):
        if not guild_id:
            return True
        row = await cls.fetch_one("SELECT aktif FROM modul_durumlari WHERE guild_id = ? AND modul_adi = ?", (guild_id, modul_adi.lower()))
        if row:
            return bool(row['aktif'])
        return True

    @classmethod
    async def modul_durum_set(cls, guild_id, modul_adi, aktif):
        return await cls.execute(
            "INSERT OR REPLACE INTO modul_durumlari (guild_id, modul_adi, aktif) VALUES (?, ?, ?)",
            (guild_id, modul_adi.lower(), 1 if aktif else 0)
        )