# -*- coding: utf-8 -*-
# Generated by Django 1.9.2 on 2016-03-03 10:48
from __future__ import unicode_literals

import django.contrib.gis.db.models.fields
from django.db import migrations, models
import django.db.models.deletion
import mesa.models


class Migration(migrations.Migration):

    initial = True

    dependencies = [
    ]

    fdi_graph_data_operation = migrations.CreateModel(
            name='FdiGraphData',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('point_id', models.IntegerField()),
                ('point_name', models.CharField(max_length=100)),
                ('value', models.FloatField()),
                ('value_class', models.CharField(max_length=20)),
                ('target_date_time', models.DateTimeField()),
            ],
            options={
                'abstract': False,
                'managed': False,
            },
        )

    fire_event_operation = migrations.CreateModel(
            name='FireEvent',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('description', models.CharField(blank=True, default=b'', max_length=50)),
                ('status', models.CharField(blank=True, choices=[(b'confirmed', b'Confirmed'), (b'merged', b'Merged'), (b'hotspot', b'Hotspot'), (b'out', b'Out')], default=b'', max_length=20)),
                ('area', models.FloatField()),
                ('first_seen', models.DateTimeField()),
                ('last_seen', models.DateTimeField()),
                ('max_frp', models.FloatField()),
                ('max_frp_date', models.DateTimeField()),
                ('current_fdi', models.IntegerField()),
                ('current_fdi_date', models.DateTimeField()),
                ('start_fdi', models.IntegerField()),
                ('max_fdi', models.IntegerField()),
                ('max_fdi_date', models.DateTimeField()),
                ('west', models.FloatField()),
                ('east', models.FloatField()),
                ('south', models.FloatField()),
                ('north', models.FloatField()),
                ('centroid_x', models.FloatField()),
                ('centroid_y', models.FloatField()),
            ],
            options={
                'abstract': False,
                'managed': False,
            },
            bases=(models.Model, mesa.models.NotifySave),
        )

    operations = [

        migrations.CreateModel(
            name='ConfigSetting',
            fields=[
                ('name', models.CharField(max_length=50, primary_key=True, serialize=False)),
                ('type', models.CharField(choices=[(b'int', b'Integer'), (b'float', b'Float'), (b'str', b'String')], default=b'str', max_length=20)),
                ('value', models.CharField(blank=True, max_length=100, null=True)),
            ],
            bases=(models.Model, mesa.models.NotifySave),
        ),
        migrations.CreateModel(
            name='FdiMeasurement',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('rain_mm', models.FloatField()),
                ('windspd_kmh', models.FloatField()),
                ('winddir_deg', models.FloatField()),
                ('rh_pct', models.FloatField()),
                ('fdi_value', models.IntegerField()),
                ('fdi_rgb', models.CharField(blank=True, default=b'', max_length=10)),
                ('temp_c', models.FloatField()),
                ('date_time', models.DateTimeField(blank=True, null=True)),
            ],
            bases=(models.Model, mesa.models.NotifySave),
        ),
        migrations.CreateModel(
            name='FdiPoint',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('name', models.CharField(max_length=40, unique=True)),
                ('type', models.CharField(blank=True, choices=[(b'wstation', b'Weather station'), (b'poi', b'Point of interest')], default=b'poi', max_length=20)),
                ('point', django.contrib.gis.db.models.fields.PointField(srid=4326)),
                ('lon', models.FloatField()),
                ('lat', models.FloatField()),
                ('station_name', models.CharField(blank=True, default=None, max_length=40, null=True, unique=True)),
                ('station_id', models.CharField(blank=True, default=None, max_length=40, null=True, unique=True)),
            ],
            bases=(models.Model, mesa.models.NotifySave),
        ),
        migrations.CreateModel(
            name='FireCluster',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('description', models.CharField(blank=True, default=b'', max_length=100)),
                ('status', models.CharField(blank=True, choices=[(b'confirmed', b'Confirmed'), (b'merged', b'Merged'), (b'hotspot', b'Hotspot'), (b'out', b'Out')], default=b'', max_length=20)),
                ('border', django.contrib.gis.db.models.fields.PolygonField(srid=4326)),
            ],
            bases=(models.Model, mesa.models.NotifySave),
        ),
        migrations.CreateModel(
            name='FirePixel',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('type', models.CharField(blank=True, default=b'', max_length=40)),
                ('point', django.contrib.gis.db.models.fields.PointField(srid=4326)),
                ('vsize', models.FloatField(default=0)),
                ('hsize', models.FloatField(default=0)),
                ('date_time', models.DateTimeField(blank=True, null=True)),
                ('src', models.CharField(blank=True, default=b'', max_length=20)),
                ('sat', models.CharField(blank=True, default=b'', max_length=20)),
                ('frp', models.FloatField(blank=True)),
                ('btemp', models.FloatField(blank=True)),
                ('to_cluster', models.BooleanField(blank=False, default=False)),
                ('fire', models.ForeignKey(blank=True, null=True, on_delete=django.db.models.deletion.CASCADE, to='mesa.FireCluster')),
            ],
            bases=(models.Model, mesa.models.NotifySave),
        ),
        migrations.AddField(
            model_name='fdimeasurement',
            name='fdi_point',
            field=models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='mesa.FdiPoint'),
        ),


        migrations.RunSQL(
            sql = """
                CREATE INDEX index_mesa_fire_border
                ON mesa_firecluster
                USING gist
                (border);

                CREATE INDEX index_mesa_fire_status
                ON mesa_firecluster
                USING btree
                (status);

                CREATE INDEX index_mesa_fire_id
                ON mesa_firecluster
                USING btree
                (id);
            """,
            reverse_sql = """
                DROP INDEX IF EXISTS index_mesa_fire_border;
                DROP INDEX IF EXISTS index_mesa_fire_status;
                DROP INDEX IF EXISTS index_mesa_fire_id;
            """,
        ),
        migrations.RunSQL(
            sql = """
                ALTER TABLE mesa_firepixel ALTER COLUMN date_time TYPE timestamp without time zone;
            """,
            reverse_sql = """
                ALTER TABLE mesa_firepixel ALTER COLUMN date_time TYPE timestamp with time zone;
            """,
        ),        
        migrations.RunSQL(
            sql = """
                CREATE INDEX index_mesa_firepixel_fire_id
                ON mesa_firepixel
                USING btree
                (fire_id);

                CREATE INDEX index_mesa_firepixel_date_time
                ON mesa_firepixel
                USING btree
                (date_time);

                CREATE INDEX index_mesa_firepixel_point
                ON mesa_firepixel
                USING gist
                (point);
            """,
            reverse_sql = """
                DROP INDEX IF EXISTS index_mesa_firepixel_fire_id;
                DROP INDEX IF EXISTS index_mesa_firepixel_date_time;
                DROP INDEX IF EXISTS index_mesa_firepixel_point;
            """,
        ),

  
        migrations.RunSQL(
            sql = """
                CREATE OR REPLACE FUNCTION update_fire_merge(fireid integer)
                  RETURNS integer AS
                $BODY$
                DECLARE
                    spatial_m  int = 3000;
                    temporal_h int = 12;
                    new_fireid int;
                BEGIN

                    -- find the nearest neighbour in range to merge with
                    RAISE NOTICE 'looking for nearest neighbour within range of: %', fireid;
                    new_fireid := (
                        WITH neighbours AS (
                            SELECT b.id, ST_Distance(a.border, b.border) AS distance_m FROM mesa_firecluster a, mesa_firecluster b WHERE a.id = fireid AND a.id < b.id ORDER BY a.border <#> b.border LIMIT 20
                        )
                        SELECT id FROM neighbours WHERE distance_m < 0.03 ORDER BY distance_m ASC LIMIT 1
                    );

                    IF new_fireid IS NULL THEN
                        -- stop recursion
                        RAISE NOTICE 'stop: %', fireid;
                        RETURN fireid;
                    ELSE
                        -- merge firepixels into selected fire
                        RAISE NOTICE 'merge firepixels';
                        UPDATE mesa_firepixel SET fire_id = new_fireid WHERE fire_id = fireid;
                        -- determine the new fire border
                        RAISE NOTICE 'update border';
                        PERFORM update_fire_border(new_fireid);
                        -- merge recursively   
                        RAISE NOTICE 'in: %', new_fireid;
                        new_fireid := update_fire_merge(new_fireid);
                        RAISE NOTICE 'out: %', new_fireid;
                        RETURN new_fireid;
                    END IF;
                    
                END
                $BODY$
                  LANGUAGE plpgsql VOLATILE
                  COST 1000;
            """,
            reverse_sql = """
                DROP FUNCTION IF EXISTS update_fire_merge(integer)
            """,
        ),

        migrations.RunSQL(
            sql = """
                CREATE OR REPLACE FUNCTION update_fire_border(fireid integer)
                    RETURNS double precision AS
                $BODY$
                DECLARE
                    spatial_m  int = 1000;
                    temporal_h int = 12;
                BEGIN  

                    UPDATE mesa_firecluster SET border = 
                    (
                        SELECT 
                            ST_Union(ST_Buffer(ST_ConCavehull(ST_Collect(p.point), 0.8), p.size), ST_Union(p.buffer)) AS border
                        FROM mesa_firecluster f,
                            ( SELECT id,
                                point,
                                st_buffer(mesa_firepixel.point, 1::double precision * GREATEST(mesa_firepixel.hsize, mesa_firepixel.vsize)) AS buffer,
                                GREATEST(mesa_firepixel.hsize, mesa_firepixel.vsize) AS size,
                                fire_id
                              FROM mesa_firepixel
                            ) p
                        WHERE p.fire_id = f.id AND f.id = fireid
                        GROUP BY f.id, p.size
                    )
                    WHERE id = fireid;
                    RETURN (SELECT ST_Area(border::geography) FROM mesa_firecluster f WHERE id = fireid);
                END
                $BODY$
                  LANGUAGE plpgsql VOLATILE
                  COST 1000;
            """,
            reverse_sql = """
                DROP  FUNCTION IF EXISTS update_fire_border(integer)
            """,
        ),

        migrations.RunSQL(
            sql = """
                CREATE OR REPLACE FUNCTION tf_firepixel_nearest_fire()
                RETURNS trigger AS
                $BODY$
                BEGIN
                    -- find the closest fire within range
                    NEW.fire_id := (
                        WITH neighbours AS ( 
                            SELECT id, ST_Distance_Sphere(border, NEW.point) AS distance_m FROM mesa_firecluster ORDER BY border::geometry <-> NEW.point::geometry LIMIT 10
                        )
                        SELECT id FROM neighbours WHERE distance_m < 3000 ORDER BY distance_m ASC LIMIT 1   
                    ); 
                    IF NEW.fire_id IS NULL THEN
                        INSERT INTO mesa_firecluster (description, status, border) SELECT 'Fire #' || NEW.id, 'hotspot', st_buffer(NEW.point, 2::double precision * GREATEST(NEW.hsize, NEW.vsize)) RETURNING id INTO NEW.fire_id;
                    END IF;
                    RETURN NEW;
                END
                $BODY$
                    LANGUAGE plpgsql VOLATILE
                    COST 100;
            """,
            reverse_sql = """
                DROP FUNCTION IF EXISTS tf_firepixel_nearest_fire();
            """,
        ),


        migrations.RunSQL(
            sql = """
            CREATE OR REPLACE FUNCTION tf_firepixel_fire_update()
            RETURNS trigger AS
            $BODY$
            BEGIN

                PERFORM update_fire_border(NEW.fire_id);
                PERFORM update_fire_merge(NEW.fire_id);
                RETURN NEW;
                
            END
            $BODY$
                LANGUAGE plpgsql VOLATILE
                COST 100;
            """,
            reverse_sql = """
                DROP FUNCTION IF EXISTS tf_firepixel_fire_update();
            """,
        ),


        migrations.RunSQL(
            sql = """
                CREATE TRIGGER mesa_firepixel_before_insert
                BEFORE INSERT
                ON mesa_firepixel
                FOR EACH ROW
                WHEN ((new.fire_id IS NULL) AND (new.to_cluster=True))
                EXECUTE PROCEDURE tf_firepixel_nearest_fire();
            """,
            reverse_sql = """
                DROP TRIGGER IF EXISTS mesa_firepixel_before_insert ON mesa_firepixel;
            """,
        ),

        migrations.RunSQL(
            sql = """
                CREATE TRIGGER mesa_firepixel_after_insert
                AFTER INSERT
                ON mesa_firepixel
                FOR EACH ROW
                WHEN ((new.fire_id IS NOT NULL) AND (new.to_cluster=True))
                EXECUTE PROCEDURE tf_firepixel_fire_update();
            """,
            reverse_sql = """
                DROP TRIGGER IF EXISTS mesa_firepixel_after_insert ON mesa_firepixel;
            """,
        ),


        migrations.RunSQL(
            sql = """
                CREATE TABLE lfdi_fwi_raster
                (
                  rid serial NOT NULL,
                  datetime timestamp without time zone,
                  target_datetime timestamp without time zone,
                  product character varying,
                  rasterfile character varying,
                  rast raster,
                  CONSTRAINT lfdi_fwi_raster_pkey PRIMARY KEY (rid)
                )
                WITH (
                  OIDS=FALSE
                );
                ALTER TABLE lfdi_fwi_raster
                  OWNER TO postgres;
            """,
            reverse_sql = """
                DROP TABLE IF EXISTS lfdi_fwi_raster;
            """,
        ),
                

        migrations.RunSQL(
            sql = """
                CREATE OR REPLACE VIEW firedanger_point_forecast AS 
                 SELECT DISTINCT ON (r.target_datetime, r.datetime, p.id, r.product) p.id AS point_id,
                    p.name AS point_name,
                    p.point,
                    r.datetime AS source_date_time,
                    r.target_datetime AS target_date_time,
                    st_value(r.rast, p.point) AS value,
                    r.product
                   FROM lfdi_fwi_raster r,
                    mesa_fdipoint p
                  WHERE st_intersects(r.rast, p.point)
                  ORDER BY r.target_datetime, r.datetime DESC;
            """,
            reverse_sql = """
                DROP VIEW IF EXISTS firedanger_point_forecast;
            """,
        ),

                
        migrations.RunSQL(
            sql = """
                CREATE OR REPLACE VIEW firedanger_point_forecast_and_measured AS 
                 SELECT forecast_and_measured.point_id,
                    forecast_and_measured.point_name,
                    forecast_and_measured.point,
                    forecast_and_measured.source_date_time,
                    forecast_and_measured.target_date_time,
                    forecast_and_measured.value,
                    forecast_and_measured.product,
                    forecast_and_measured.value_class
                   FROM (( SELECT pf.point_id,
                            pf.point_name,
                            pf.point,
                            pf.source_date_time,
                            pf.target_date_time,
                            pf.value,
                            pf.product,
                            'Forecast'::text AS value_class
                           FROM firedanger_point_forecast pf
                          WHERE pf.source_date_time = (( SELECT max(pf2.source_date_time) AS max
                                   FROM firedanger_point_forecast pf2
                                  WHERE pf.product::text = pf2.product::text))
                          ORDER BY pf.target_date_time)
                        UNION
                         SELECT fp.id AS point_id,
                            fp.name AS point_name,
                            fp.point,
                            fm.date_time AS source_date_time,
                            fm.date_time AS target_date_time,
                            fm.fdi_value AS value,
                            'lfdid'::character varying AS product,
                            'Actual'::text AS value_class
                           FROM mesa_fdimeasurement fm,
                            mesa_fdipoint fp
                          WHERE fm.fdi_point_id = fp.id) forecast_and_measured
                  ORDER BY forecast_and_measured.point_id, forecast_and_measured.target_date_time;
            """,
            reverse_sql = """
                DROP VIEW IF EXISTS firedanger_point_forecast_and_measured;
            """,
        ),

        migrations.RunSQL(
            sql = """
                CREATE SCHEMA imagemosaic
                AUTHORIZATION docker;

                GRANT ALL ON SCHEMA imagemosaic TO docker;
                GRANT ALL ON SCHEMA imagemosaic TO public;
            """,
            reverse_sql = """
                DROP SCHEMA imagemosaic;
            """,
        ),                
                
        migrations.RunSQL(
            sql = """
                CREATE OR REPLACE VIEW firedanger_point_forecast_and_measured_current AS 
                 SELECT p.id AS point_id,
                    p.name AS point_name,
                    p.type AS point_type,
                    p.point,
                    data.source_date_time,
                    data.target_date_time,
                    data.value,
                    data.product,
                    data.value_class
                   FROM mesa_fdipoint p
                     LEFT JOIN ( SELECT DISTINCT ON (firedanger_point_forecast_and_measured.point_id, firedanger_point_forecast_and_measured.product) firedanger_point_forecast_and_measured.point_id,
                            firedanger_point_forecast_and_measured.point_name,
                            firedanger_point_forecast_and_measured.point,
                            firedanger_point_forecast_and_measured.source_date_time,
                            firedanger_point_forecast_and_measured.target_date_time,
                            firedanger_point_forecast_and_measured.value,
                            firedanger_point_forecast_and_measured.product,
                            firedanger_point_forecast_and_measured.value_class
                           FROM firedanger_point_forecast_and_measured
                          WHERE (firedanger_point_forecast_and_measured.target_date_time + '01:00:00'::interval) > now()) data ON p.id = data.point_id;
            """,
            reverse_sql = """
                DROP VIEW IF EXISTS firedanger_point_forecast_and_measured_current;
            """,
        ),
                
        migrations.RunSQL(
            sql="CREATE OR REPLACE VIEW mesa_fdigraphdata AS SELECT -1 AS id, point_id, point_name, target_date_time, value, value_class FROM firedanger_point_forecast_and_measured WHERE product = 'lfdid' ORDER BY point_id, target_date_time ASC;",
            reverse_sql='DROP VIEW IF EXISTS mesa_fdigraphdata CASCADE;',
            state_operations=[fdi_graph_data_operation]
        ),

        migrations.RunSQL(
            sql = """ 
                CREATE OR REPLACE FUNCTION firedanger_forecast_at(IN x double precision, IN y double precision, IN t timestamp, IN prod character varying DEFAULT 'lfdid') RETURNS RECORD AS
                $BODY$
                DECLARE result_record RECORD;
                BEGIN
                  SELECT DISTINCT ON (r.target_datetime, r.datetime, r.product)
                    r.datetime AS source_date_time,
                    r.target_datetime AS target_date_time,
                    st_value(r.rast, ('SRID=4326; POINT ( ' || x || ' ' || y || ' )')::geometry) AS value,
                    r.product
                   INTO result_record
                   FROM lfdi_fwi_raster r
                   WHERE product = prod AND r.target_datetime + '12 hours'::INTERVAL > t
                  ORDER BY r.target_datetime, r.datetime DESC;
                
                  RETURN result_record;
                END
                $BODY$
                LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;            
            """,
            reverse_sql=""" 
                DROP FUNCTION IF EXISTS firedanger_forecast_at(IN x double precision, IN y double precision, IN t timestamp, IN prod character varying) CASCADE;
            """, 
            state_operations=[],
        ),

        migrations.RunSQL(
            sql = """ 
            CREATE OR REPLACE VIEW mesa_fireevent AS 
            WITH dims AS (
                     SELECT mesa_firecluster.id,
                        st_xmax(mesa_firecluster.border::box3d) - st_xmin(mesa_firecluster.border::box3d) AS width,
                        st_ymax(mesa_firecluster.border::box3d) - st_ymin(mesa_firecluster.border::box3d) AS height,
                        st_xmin(mesa_firecluster.border::box3d) - (st_xmax(mesa_firecluster.border::box3d) - st_xmin(mesa_firecluster.border::box3d)) * 0.05 AS west,
                        st_xmax(mesa_firecluster.border::box3d) + (st_xmax(mesa_firecluster.border::box3d) - st_xmin(mesa_firecluster.border::box3d)) * 0.05 AS east,
                        st_ymin(mesa_firecluster.border::box3d) - (st_ymax(mesa_firecluster.border::box3d) - st_ymin(mesa_firecluster.border::box3d)) * 0.05 AS south,
                        st_ymax(mesa_firecluster.border::box3d) + (st_ymax(mesa_firecluster.border::box3d) - st_ymin(mesa_firecluster.border::box3d)) * 0.05 AS north,
                        st_x(st_centroid(mesa_firecluster.border)) AS centroid_x,
                        st_y(st_centroid(mesa_firecluster.border)) AS centroid_y
                       FROM mesa_firecluster
                    )
             SELECT f.id,
                f.description,
                f.status,
                f.border,
                st_area(st_transform(f.border, 54008)) AS area,
                min(p.date_time) AS first_seen,
                max(p.date_time) AS last_seen,
                max(p.frp) AS max_frp,
                min(p.date_time) AS max_frp_date,
                (SELECT value FROM firedanger_forecast_at(dims.centroid_x, dims.centroid_y, now()::timestamp) AS (source_date_time timestamp,target_date_time timestamp,value double precision,product character varying)) AS current_fdi,
                now()::timestamp AS current_fdi_date,
                (SELECT value FROM firedanger_forecast_at(dims.centroid_x, dims.centroid_y, min(p.date_time)::timestamp) AS (source_date_time timestamp,target_date_time timestamp,value double precision,product character varying)) AS start_fdi,
                (SELECT value FROM firedanger_forecast_at(dims.centroid_x, dims.centroid_y, max(p.date_time)::timestamp) AS (source_date_time timestamp,target_date_time timestamp,value double precision,product character varying)) AS max_fdi,
                max(p.date_time) AS max_fdi_date,
                (max(p.date_time) + '5 days'::interval) > now() AS is_active,
                dims.centroid_x, dims.centroid_y, dims.west, dims.north, dims.east, dims.south, dims.width, dims.height
               FROM mesa_firecluster f, mesa_firepixel p, dims 
               WHERE p.fire_id = f.id AND f.id = dims.id
               
              GROUP BY f.id, f.description, f.status, dims.centroid_x, dims.centroid_y, dims.west, dims.north, dims.east, dims.south, dims.width, dims.height;
            """,
            reverse_sql=""" 
                DROP VIEW IF EXISTS mesa_fireevent CASCADE;
            """, 
            state_operations=[fire_event_operation],
        ),

                
                
    ]
