from datetime import datetime
from typing import Optional, List, Set
from conversation.models import Tweetdj, Treedj
from community.models import Community
from community.helpers import (
    get_community_bot_socialuser,
)
from django.db.utils import DatabaseError

def get_community_roots(
        community: Community,
        lower_dt: Optional[datetime] = None,
        upper_dt: Optional[datetime] = None,
    ):    
    su = get_community_bot_socialuser(community)
    if not su:
        return
    rt_by_qs = Tweetdj.objects.filter(
        retweeted_by=su,
        json__in_reply_to_status_id=None
    )
    if lower_dt or upper_dt:
        rt_by_qs = limit_by_dt(
            rt_by_qs,
            lower_dt,
            upper_dt
        )
    rt_by_ids = rt_by_qs.values_list("statusid", flat=True)
    roots = list(
        Treedj.objects
        .filter(statusid__in=[rt_by_ids])
        .order_by("-statusid")
        .values_list("statusid", flat=True)
    )
    return roots

def limit_by_dt(
        qs,
        lower_dt: Optional[datetime] = None,
        upper_dt: Optional[datetime] = None,
    ):
    if lower_dt and upper_dt:
        return qs.filter(created_at__gte=lower_dt, created_at__lte=upper_dt)
    elif lower_dt:
        return qs.filter(created_at__gte=lower_dt)
    elif upper_dt:
        return qs.filter(created_at__lte=upper_dt)
    else:
        return qs

def add_leaf(parent_id, statusid):
    try:
        parent = Treedj.objects.get(statusid=parent_id)
    except Treedj.DoesNotExist:
        return
    try:
        Treedj.objects.create(
            statusid=statusid,
            parent=parent
        )
    except DatabaseError:
        return

def add_tweetdj_to_treedj():
    # create a set of all treedj pk
    tree_sid: Set[int] = set(
        Treedj.objects.values_list("statusid", flat=True)
    )
    # loop over all tweetdj and check if the parent tweet is in the treedj set
    for tweetdj in Tweetdj.objects.all():
        parent_id = tweetdj.parentid
        #parent_id = tweetdj.json["in_reply_to_status_id"]
        if parent_id in tree_sid and tweetdj.statusid not in tree_sid:
            add_leaf(parent_id, tweetdj.statusid)
                    
@@ -1932,18 +1932,25 @@
 for 
-tweetdj in
+_dct in (%0A       
  Twe
@@ -1965,15 +1965,8 @@
 ects
-.all():
 %0A   
@@ -1974,36 +1974,77 @@
     
-parent_id = tweetdj.
+.filter(parentid__isnull=False)%0A        .values(%22statusid%22, %22
 parentid
 %0A   
@@ -2039,21 +2039,26 @@
 parentid
+%22)
 %0A    
+):%0A
     #par
@@ -2115,25 +2115,32 @@
      if 
+_dct%5B%22
 parent
-_
 id
+%22%5D
  in tree
@@ -2148,32 +2148,30 @@
 sid and 
-tweetdj.
+_dct%5B%22
 statusid
  not in 
@@ -2162,16 +2162,18 @@
 statusid
+%22%5D
  not in 
@@ -2207,35 +2207,40 @@
 eaf(
+_dct%5B%22
 parent
-_
 id
-, tweetdj.
+%22%5D, _dct%5B%22
 statusid
 )%0A  
@@ -2235,16 +2235,18 @@
 statusid
+%22%5D
 )%0A      

improve add_tweetdj_to_treedj() even more again