from flask import jsonify
from sqlalchemy.types import String
from sqlalchemy import func
import datetime

from .. import main
from ...models import db, Framework, DraftService, Service, User, Supplier, SelectionAnswers, AuditEvent


@main.route('/frameworks', methods=['GET'])
def list_frameworks():
    frameworks = Framework.query.all()

    return jsonify(
        frameworks=[f.serialize() for f in frameworks]
    )


@main.route('/frameworks/<string:framework_slug>/stats', methods=['GET'])
def get_framework_stats(framework_slug):

    framework = Framework.query.filter(
        Framework.slug == framework_slug
    ).first()

    if not framework:
        abort(404, "'{}' is not a framework".format(framework.slug))

    seven_days_ago = datetime.datetime.utcnow() + datetime.timedelta(-7)
    lot_column = DraftService.data['lot'].cast(String).label('lot')

    return str({
        'services_by_status': dict(db.session.query(
            DraftService.status, func.count(DraftService.status)
        ).group_by(
            DraftService.status
        ).filter(
            DraftService.framework_id == framework.id
        )),
        'services_by_lot': dict(db.session.query(
            lot_column, func.count(lot_column)
        ).group_by(
            lot_column
        ).filter(
            DraftService.framework_id == framework.id
        ).all()),
        'supplier_users': User.query.filter(
            User.role == 'supplier'
        ).count(),
        'active_supplier_users': User.query.filter(
            User.role == 'supplier',
            User.logged_in_at > seven_days_ago
        ).count(),
        'suppliers': Supplier.query.count(),
        'suppliers_interested': AuditEvent.query.filter(
            AuditEvent.data['frameworkSlug'].cast(String) == framework_slug,
            AuditEvent.type == 'register_framework_interest'
        ).count(),
        'suppliers_with_complete_declaration': SelectionAnswers.find_by_framework(framework_slug).count()
    })

@@ -54,16 +54,25 @@
 t String
+, Boolean
 %0Afrom sq
@@ -91,16 +91,21 @@
 ort func
+, or_
 %0Aimport 
@@ -885,56 +885,230 @@
     
-return str(%7B%0A        'services_by_status': dict(
+def label_columns(labels, query):%0A        return %5Bdict(zip(labels, item)) for item in query%5D%0A%0A    return jsonify(%7B%0A        'services': label_columns(%0A            %5B'status', 'lot', 'declaration_made', 'count'%5D,%0A            
 db.s
@@ -1113,32 +1113,36 @@
 .session.query(%0A
+    
             Draf
@@ -1162,316 +1162,299 @@
 us, 
-func.count(DraftService.status)%0A        ).group_by(%0A            DraftService.status%0A        ).filter(%0A            DraftService.framework_id == framework.id%0A        )),%0A        'services_by_lot': dict(db.session.query(%0A            lot_column, func.count(lot_column)%0A        ).group_by(%0A            lot_column%0A
+lot_column, SelectionAnswers.framework_id.isnot(None), func.count()%0A            ).outerjoin(%0A                SelectionAnswers, DraftService.supplier_id == SelectionAnswers.supplier_id%0A            ).group_by(%0A                DraftService.status, lot_column, SelectionAnswers.framework_id%0A    
     
@@ -1459,32 +1459,36 @@
       ).filter(%0A
+    
             Draf
@@ -1537,15 +1537,28 @@
     
+    
 ).all()
+%0A        
 ),%0A 
@@ -1586,125 +1586,170 @@
 s': 
-User.query.filter(%0A            User.role == 'supplier'%0A        ).count(),%0A        'active_supplier_users': User.query
+label_columns(%0A            %5B'recent_login', 'count'%5D,%0A            db.session.query(%0A                User.logged_in_at %3E seven_days_ago, func.count()%0A            )
 .fil
@@ -1761,24 +1761,28 @@
             
+   
+ 
 User.role ==
@@ -1792,18 +1792,45 @@
 upplier'
-,
 %0A
+            ).group_by(%0A    
         
@@ -1880,62 +1880,123 @@
     
-).count(),%0A        'suppliers': Supplier.query.
+    ).all()%0A        ),%0A        'interested_suppliers': label_columns(%0A            %5B'has_made_declaration', '
 count
-()
+'%5D
 ,%0A  
@@ -2005,134 +2005,269 @@
     
-'suppliers_interested': AuditEvent.query.filter(%0A            AuditEvent.data%5B'frameworkSlug'%5D.cast(String) == framework_slug,%0A
+    db.session.query(%0A                SelectionAnswers.framework_id.isnot(None), func.count()%0A            ).select_from(%0A                Supplier%0A            ).outerjoin(%0A                AuditEvent, AuditEvent.object_id == Supplier.id%0A            ).filter(%0A    
     
@@ -2335,18 +2335,19 @@
     
+   
+ 
 ).
-count(),
+join(
 %0A   
@@ -2355,104 +2355,145 @@
     
-'suppliers_with_complete_declaration': SelectionAnswers.find_by_framework(framework_slug).count(
+        SelectionAnswers, isouter=True%0A            ).group_by(%0A                SelectionAnswers.framework_id%0A            ).all()%0A        
 )%0A  

Rewrite queries to use all the joins