# -*- coding: utf-8 -*-
# Generated by Django 1.9 on 2015-12-14 02:31
from __future__ import unicode_literals

from django.db import migrations


class Migration(migrations.Migration):
    dependencies = [
        ('crowdsourcing', '0000_get_worker_ratings_fn'),
    ]

    operations = [
        migrations.RunSQL('''
            CREATE OR REPLACE FUNCTION get_min_ratings()
              RETURNS TABLE(module_id INTEGER, owner_id INTEGER,  min_rating DOUBLE PRECISION)
            AS $$

			WITH module_seconds_worked AS (
			    SELECT m.id, COUNT(tw.id) * (60 * m.price / .1) module_seconds
			    FROM crowdsourcing_module m
			    INNER JOIN crowdsourcing_task t ON m.id=t.module_id
			    INNER JOIN crowdsourcing_taskworker tw ON t.id=tw.task_id
			    WHERE tw.task_status=2 AND EXTRACT('EPOCH' FROM (NOW() - tw.last_updated)) <= EXTRACT('EPOCH' FROM INTERVAL '1 hour')
			    GROUP BY m.id
			), potential_module_seconds_worked AS (
			    SELECT m.id, m.owner_id,  m.min_rating, (COUNT(t.id) * m.repetition - COUNT(CASE WHEN tw.task_status IN (2, 3, 5) THEN 1 ELSE NULL END)) * (60 * m.price / .1) potential_module_seconds
			    FROM crowdsourcing_module m
			    INNER JOIN crowdsourcing_task t ON m.id=t.module_id
			    LEFT OUTER JOIN crowdsourcing_taskworker tw ON t.id=tw.task_id
			    WHERE m.status=3
			    GROUP BY m.id
			)

			SELECT p.id module_id, p.owner_id,
			CASE WHEN sms.sum_module_seconds IS NULL THEN p.min_rating
			WHEN sps.sum_potential_seconds=0 THEN p.min_rating
			WHEN m.module_seconds / sms.sum_module_seconds >= p.potential_module_seconds / sps.sum_potential_seconds THEN p.min_rating
			ELSE p.min_rating * (1 - (p.potential_module_seconds / sps.sum_potential_seconds - COALESCE(m.module_seconds, 0) / sms.sum_module_seconds)) END min_rating
			FROM potential_module_seconds_worked p
			LEFT OUTER JOIN module_seconds_worked m ON p.id=m.id
			INNER JOIN (SELECT SUM(module_seconds) sum_module_seconds FROM module_seconds_worked) sms ON TRUE
			INNER JOIN (SELECT SUM(potential_module_seconds) sum_potential_seconds FROM potential_module_seconds_worked) sps ON TRUE

            $$
            LANGUAGE SQL
            STABLE
            RETURNS NULL ON NULL INPUT;
        ''')
    ]
