import os
from django.core.management.base import BaseCommand
from uw_sws.term import get_current_term
from data_aggregator.utilities import get_week_of_term, get_view_name
from django.db import connection


def create(sis_term_id, week):
    """
    Create rad db view for given week and sis-term-id
    """

    view_name = get_view_name(sis_term_id, week, "rad")
    assignments_view_name = get_view_name(sis_term_id,
                                          week,
                                          "assignments")
    participations_view_name = get_view_name(sis_term_id,
                                             week,
                                             "participations")

    cursor = connection.cursor()

    env = os.getenv("ENV")
    if env == "localdev" or not env:
        create_action = (
            'CREATE VIEW "{view_name}"'.format(view_name=view_name))
        cursor.execute(
            'DROP VIEW IF EXISTS "{view_name}"'.format(view_name=view_name)
        )
    else:
        create_action = (
            'CREATE OR REPLACE VIEW "{view_name}"'.format(view_name=view_name))

    cursor.execute(
        '''
        {create_action} AS
        SELECT DISTINCT
            u.canvas_user_id,
            u.full_name,
            '{sis_term_id}' as term,
            {week} as week,
            assignment_score,
            participation_score,
            grade
        FROM
        (
            SELECT
                norm_ra.user_id,
                AVG(normalized_assignment_score) AS assignment_score,
                AVG(normalized_participation_score) AS participation_score
            FROM
            (
                SELECT
                    p1.user_id,
                    p1.course_id,
                    p1.week_id,
                        coalesce(
                            cast((p1.participations - min_raw_participation_score) as decimal) /
                            NULLIF( cast((max_raw_participation_score - min_raw_participation_score) as decimal) / 10, 0),
                        0) - 5
                    AS normalized_participation_score,
                        coalesce(
                            cast(((2 * p1.time_on_time + p1.time_late) - min_raw_assignment_score) as decimal) /
                            NULLIF( cast((max_raw_assignment_score - min_raw_assignment_score) as decimal) / 10, 0)
                                , 0) - 5
                    AS normalized_assignment_score
                FROM "{participations_view_name}" p1
                JOIN (
                    SELECT
                        course_id,
                        MIN(p2.participations) AS min_raw_participation_score,
                        MAX(p2.participations) AS max_raw_participation_score,
                        MIN(2 * p2.time_on_time + p2.time_late) AS min_raw_assignment_score,
                        MAX(2 * p2.time_on_time + p2.time_late) AS max_raw_assignment_score
                    FROM "{participations_view_name}" p2
                    GROUP BY 
                        course_id
                ) ra ON p1.course_id  = ra.course_id
                GROUP BY
                    p1.user_id,
                    p1.course_id,
                    p1.week_id,
                    participations,
                    p1.time_on_time,
                    p1.time_late,
                    normalized_participation_score,
                    normalized_assignment_score
            ) norm_ra
            GROUP BY
                norm_ra.user_id
        ) AS p JOIN 
        (
            SELECT DISTINCT
                a1.user_id,
                AVG(normalized_score) AS grade
            FROM (
            SELECT
                a2.user_id,
                CASE
                WHEN (COALESCE(a2.max_score, 0) - COALESCE(a2.min_score, 0)) = 0 THEN 0
                WHEN a2.score  IS NULL THEN -5
                ELSE ((10 * (COALESCE(a2.score, 0) - COALESCE(a2.min_score, 0))) /
                        (COALESCE(a2.max_score, 0) - COALESCE(a2.min_score, 0))) - 5
                END AS normalized_score
            FROM "{assignments_view_name}" a2
            WHERE a2.status = 'on_time' OR a2.status = 'late' OR a2.status = 'missing'
            GROUP BY a2.user_id, normalized_score 
            ) a1
            GROUP BY
                a1.user_id
        ) AS a
        ON p.user_id = a.user_id
        JOIN data_aggregator_user u ON p.user_id = u.id
        '''  # noqa
        .format(
            create_action=create_action,
            week=week,
            sis_term_id=sis_term_id,
            participations_view_name=participations_view_name,
            assignments_view_name=assignments_view_name)
    )
    return True


class Command(BaseCommand):

    help = ("Creates RAD db view for given week.")

    def handle(self, *args, **options):
        """
        Create rad db view for current week
        """
        sws_term = get_current_term()
        sis_term_id = sws_term.canvas_sis_id()
        week = get_week_of_term(sws_term.first_day_quarter)
        create(sis_term_id, week)

@@ -1183,245 +1183,35 @@
     
-SELECT DISTINCT%0A            u.canvas_user_id,%0A            u.full_name,%0A            '%7Bsis_term_id%7D' as term,%0A            %7Bweek%7D as week,%0A            assignment_score,%0A            participation_score,%0A            grade%0A        FROM%0A       
+WITH%0A        avg_norm_ap AS
  (%0A 
@@ -1241,34 +1241,34 @@
            norm_
-r
 a
+p
 .user_id,%0A      
@@ -1422,28 +1422,16 @@
     FROM
-%0A           
  (%0A     
@@ -2863,16 +2863,27 @@
     ) ra
+w_ap_bounds
  ON p1.c
@@ -2896,16 +2896,27 @@
 id  = ra
+w_ap_bounds
 .course_
@@ -3261,24 +3261,27 @@
        )
+ AS
  norm_
-r
 a
+p
 %0A       
@@ -3315,18 +3315,18 @@
    norm_
-r
 a
+p
 .user_id
@@ -3339,27 +3339,32 @@
    )
- AS p JOIN %0A       
+,%0A        avg_norm_gr AS
  (%0A 
@@ -4157,25 +4157,297 @@
    )
- AS a%0A        ON 
+%0A        SELECT DISTINCT%0A            u.canvas_user_id,%0A            u.full_name,%0A            '%7Bsis_term_id%7D' as term,%0A            %7Bweek%7D as week,%0A            assignment_score,%0A            participation_score,%0A            grade%0A        FROM avg_norm_ap%0A        JOIN avg_norm_gr ON avg_norm_a
 p.us
@@ -4455,16 +4455,26 @@
 r_id = a
+vg_norm_gr
 .user_id
@@ -4513,16 +4513,26 @@
 er u ON 
+avg_norm_a
 p.user_i
@@ -4551,17 +4551,16 @@
      '''
- 
  # noqa%0A

Use CTEs instead of normal subqueries