# Generated by Django 2.1.9 on 2019-06-21 14:34

from django.db import migrations


forward = """
  CREATE OR REPLACE FUNCTION on_edit_treenode_update_summary_and_edges() RETURNS trigger
  LANGUAGE plpgsql AS
  $$
  BEGIN
      WITH updated_parent_edge_data AS (
          -- Find all nodes that changed their position or parent
          SELECT t.id, t.project_id, t.skeleton_id, t.creation_time,
              t.edition_time, t.editor_id, ST_MakeLine(
                  ST_MakePoint(t.location_x, t.location_y, t.location_z),
                  ST_MakePoint(p.location_x, p.location_y, p.location_z)
              ) AS edge,
              t.parent_id,
              ot.edition_time as old_edition_time,
              ot.creation_time AS old_creation_time,
              ot.skeleton_id AS old_skeleton_id,
              -- Trigger event with information on changed edges (old and new)
              -- as JSON encoded payload.
              notify_conditionally('catmaid.spatial-update', '{"project_id": ' ||
                  t.project_id || ', "type": "edges", "edges": [[[' ||
                  t.location_x || ',' || t.location_y || ',' || t.location_z || '], [' ||
                  p.location_x || ',' || p.location_y || ',' || p.location_z ||
                  ']], [[' ||
                  ot.location_x || ',' || ot.location_y || ',' || ot.location_z || '], [' ||
                  p.location_x || ',' || p.location_y || ',' || p.location_z || ']]]}')
          FROM new_treenode t
          JOIN old_treenode ot
              ON t.id = ot.id
          JOIN treenode p
              ON (t.parent_id IS NOT NULL AND p.id = t.parent_id) OR
                 (t.parent_id IS NULL AND p.id = t.id)
          WHERE ot.parent_id IS DISTINCT FROM t.parent_id OR
             ot.location_x != t.location_x OR
             ot.location_y != t.location_y OR
             ot.location_z != t.location_z OR
             ot.skeleton_id != t.skeleton_id
      ), updated_child_edge_data AS (
          -- Find all unseen child nodes of the nodes with a changed
          -- edge using an anti join.
          SELECT c.id, c.project_id, c.skeleton_id, c.creation_time,
              c.edition_time, c.editor_id, ST_MakeLine(
                  ST_MakePoint(c.location_x, c.location_y, c.location_z),
                  ST_MakePoint(e.location_x, e.location_y, e.location_z)
              ) AS edge,
              c.parent_id,
              c.edition_time AS old_edition_time,
              c.creation_time AS old_creation_time,
              c.skeleton_id AS old_skeleton_id,
              -- Trigger event with edge information as JSON encoded payload.
              notify_conditionally('catmaid.spatial-update', '{"project_id": ' ||
              e.project_id || ', "type": "edges", "edges": [[[' ||
                  c.location_x || ',' || c.location_y || ',' || c.location_z || '], [' ||
                  e.location_x || ',' || e.location_y || ',' || e.location_z ||
                  ']], [[' ||
                  c.location_x || ',' || c.location_y || ',' || c.location_z || '], [' ||
                  ot.location_x || ',' || ot.location_y || ',' || ot.location_z || ']]]}')
          FROM treenode c
          JOIN old_treenode ot
              ON ot.id = c.parent_id
          JOIN new_treenode e
              ON c.parent_id = e.id
          LEFT JOIN new_treenode c2
              ON c.id = c2.id
          WHERE c2.id IS NULL
      ), updated_edge_data AS (
          -- Combine all directly changed nodes with a changed
          -- location as well as the extra child nodes where the
          -- parent changed location. The limit is needed to indicate
          -- to the planner an upper limit of this CTE. This is
          -- unfortunately needed, because no real estimates are done
          -- on CTEs and no actual stats are used. This leads to
          -- unfortunate join plans in the updated_edge CTE.
          (SELECT *
          FROM updated_parent_edge_data
          LIMIT (SELECT COUNT(*) FROM updated_parent_edge_data))
          UNION ALL
          (SELECT *
          FROM updated_child_edge_data
          LIMIT (SELECT COUNT(*) FROM updated_child_edge_data))
      ), old_edge AS (
          -- Get all old edges of changed nodes as well as their
          -- children (if any). Child edges contribute to the cable
          -- length as well and need to be updated.
          SELECT t.id, t.project_id, t.old_skeleton_id AS skeleton_id,
              t.old_creation_time AS creation_time,
              t.old_edition_time AS edition_time,
              e.edge,
              t.editor_id
          FROM updated_edge_data t
          JOIN treenode_edge e
              ON e.id = t.id
      ), updated_edge AS (
          -- Update all changed edges. To have this join work fast, we
          -- rely on reasonable statistics on the row count of
          -- updated_edge_data. This is provided, by setting (obivious)
          -- limits on its size when creating it.
          UPDATE treenode_edge e
          SET edge = ue.edge
          FROM updated_edge_data ue
          WHERE e.id = ue.id
          RETURNING e.id
      ), new_edge AS (
          -- Collect changed nodes both with and without location
          -- change. Updated edge information takes precedence.
          SELECT ue.id, ue.project_id, ue.skeleton_id,
              ue.creation_time, ue.edition_time, ue.edge, ue.editor_id
          FROM updated_edge_data ue
          UNION ALL
          SELECT nt.id, nt.project_id, nt.skeleton_id,
              nt.creation_time, nt.edition_time, oe.edge, nt.editor_id
          FROM new_treenode nt
          LEFT JOIN updated_edge_data ue
              ON nt.id = ue.id
          JOIN old_edge oe
              ON nt.id = oe.id
          WHERE ue.id IS NULL
      ), old_skeleton_data AS (
          -- Aggregate data over old skeleton datas to delete for summary.
          SELECT skeleton_id, project_id,
              -COUNT(*) AS num_nodes,
              -SUM(ST_3DLength(edge)) AS length,
              MIN(creation_time) AS min_creation_time,
              MAX(edition_time) AS max_edition_time,
              last_editor_id
          FROM (
              SELECT skeleton_id, project_id, edge, creation_time, edition_time,
                  first_value(editor_id) OVER w AS last_editor_id
              FROM old_edge
              WINDOW w AS (PARTITION BY skeleton_id, project_id ORDER BY edition_time DESC)
          ) edge_info
          GROUP BY skeleton_id, project_id, last_editor_id
      ), new_skeleton_data AS (
          -- Aggregate data over skeletons to prepare for summary update.
          SELECT skeleton_id, project_id,
              COUNT(*) AS num_nodes,
              SUM(ST_3DLength(edge)) AS length,
              MIN(creation_time) AS min_creation_time,
              MAX(edition_time) AS max_edition_time,
              last_editor_id
          FROM (
              SELECT skeleton_id, project_id, edge, creation_time, edition_time,
                  first_value(editor_id) OVER w AS last_editor_id
              FROM new_edge
              WINDOW w AS (PARTITION BY skeleton_id, project_id ORDER BY edition_time DESC)
          ) edge_info
          GROUP BY skeleton_id, project_id, last_editor_id
      ), summary_update_delta AS (
          SELECT skeleton_id, project_id,
              SUM(num_nodes) AS num_nodes,
              SUM(length) AS length,
              MIN(min_creation_time) AS min_creation_time,
              MAX(max_edition_time) AS max_edition_time,
              last_editor_id
          FROM (
              SELECT skeleton_id, project_id, num_nodes, length,
                  min_creation_time, max_edition_time,
                  first_value(last_editor_id) OVER w AS last_editor_id
              FROM (
                  SELECT os.skeleton_id, os.project_id, os.num_nodes,
                      os.length, os.min_creation_time, os.max_edition_time,
                      os.last_editor_id
                  FROM old_skeleton_data os
                  UNION ALL
                  SELECT ns.skeleton_id, ns.project_id, ns.num_nodes,
                      ns.length, ns.min_creation_time, ns.max_edition_time,
                      ns.last_editor_id
                  FROM new_skeleton_data ns
              ) _update_data
              WINDOW w AS (PARTITION BY skeleton_id, project_id ORDER BY max_edition_time DESC)
          ) update_data
          GROUP BY skeleton_id, project_id, last_editor_id
      )
      INSERT INTO catmaid_skeleton_summary (project_id, skeleton_id,
          last_summary_update, original_creation_time,
          last_edition_time, last_editor_id, num_nodes, cable_length)
      (
          SELECT s.project_id, s.skeleton_id, now(), s.min_creation_time,
              s.max_edition_time,
              COALESCE(NULLIF(current_setting('catmaid.user_id', TRUE), '')::integer, s.last_editor_id),
              s.num_nodes, s.length
          FROM summary_update_delta s
      )
      ON CONFLICT (skeleton_id) DO UPDATE
      SET num_nodes = catmaid_skeleton_summary.num_nodes + EXCLUDED.num_nodes,
          last_summary_update = EXCLUDED.last_summary_update,
          last_edition_time = GREATEST(
              catmaid_skeleton_summary.last_edition_time,
              EXCLUDED.last_edition_time),
          last_editor_id = EXCLUDED.last_editor_id,
          cable_length = catmaid_skeleton_summary.cable_length + EXCLUDED.cable_length;

      RETURN NEW;
  END;
  $$;
"""


backward = """
  CREATE OR REPLACE FUNCTION on_edit_treenode_update_summary_and_edges() RETURNS trigger
  LANGUAGE plpgsql AS
  $$
  BEGIN
      -- Transition tables (old_treenode and new_treenode) can only be
      -- read once per statement. This is why we select them
      -- completely into a CTE. With Postgres 10.4 this can be replaced
      -- by direct transition table access, because it fixes the bug
      -- causing the current behavior.
      WITH old_treenode_data AS (
          SELECT * FROM old_treenode
      ), new_treenode_data AS (
          SELECT * FROM new_treenode
      ), updated_parent_edge_data AS (
          -- Find all nodes that changed their position or parent
          SELECT t.id, t.project_id, t.skeleton_id, t.creation_time,
              t.edition_time, t.editor_id, ST_MakeLine(
                  ST_MakePoint(t.location_x, t.location_y, t.location_z),
                  ST_MakePoint(p.location_x, p.location_y, p.location_z)
              ) AS edge,
              t.parent_id,
              ot.edition_time as old_edition_time,
              ot.creation_time AS old_creation_time,
              ot.skeleton_id AS old_skeleton_id,
              -- Trigger event with information on changed edges (old and new)
              -- as JSON encoded payload.
              notify_conditionally('catmaid.spatial-update', '{"project_id": ' ||
                  t.project_id || ', "type": "edges", "edges": [[[' ||
                  t.location_x || ',' || t.location_y || ',' || t.location_z || '], [' ||
                  p.location_x || ',' || p.location_y || ',' || p.location_z ||
                  ']], [[' ||
                  ot.location_x || ',' || ot.location_y || ',' || ot.location_z || '], [' ||
                  p.location_x || ',' || p.location_y || ',' || p.location_z || ']]]}')
          FROM new_treenode_data t
          JOIN old_treenode_data ot
              ON t.id = ot.id
          JOIN treenode p
              ON (t.parent_id IS NOT NULL AND p.id = t.parent_id) OR
                 (t.parent_id IS NULL AND p.id = t.id)
          WHERE ot.parent_id IS DISTINCT FROM t.parent_id OR
             ot.location_x != t.location_x OR
             ot.location_y != t.location_y OR
             ot.location_z != t.location_z OR
             ot.skeleton_id != t.skeleton_id
      ), updated_child_edge_data AS (
          -- Find all unseen child nodes of the nodes with a changed
          -- edge using an anti join.
          SELECT c.id, c.project_id, c.skeleton_id, c.creation_time,
              c.edition_time, c.editor_id, ST_MakeLine(
                  ST_MakePoint(c.location_x, c.location_y, c.location_z),
                  ST_MakePoint(e.location_x, e.location_y, e.location_z)
              ) AS edge,
              c.parent_id,
              c.edition_time AS old_edition_time,
              c.creation_time AS old_creation_time,
              c.skeleton_id AS old_skeleton_id,
              -- Trigger event with edge information as JSON encoded payload.
              notify_conditionally('catmaid.spatial-update', '{"project_id": ' ||
              e.project_id || ', "type": "edges", "edges": [[[' ||
                  c.location_x || ',' || c.location_y || ',' || c.location_z || '], [' ||
                  e.location_x || ',' || e.location_y || ',' || e.location_z ||
                  ']], [[' ||
                  c.location_x || ',' || c.location_y || ',' || c.location_z || '], [' ||
                  ot.location_x || ',' || ot.location_y || ',' || ot.location_z || ']]]}')
          FROM treenode c
          JOIN old_treenode_data ot
              ON ot.id = c.parent_id
          JOIN new_treenode_data e
              ON c.parent_id = e.id
          LEFT JOIN new_treenode_data c2
              ON c.id = c2.id
          WHERE c2.id IS NULL
      ), updated_edge_data AS (
          -- Combine all directly changed nodes with a changed
          -- location as well as the extra child nodes where the
          -- parent changed location. The limit is needed to indicate
          -- to the planner an upper limit of this CTE. This is
          -- unfortunately needed, because no real estimates are done
          -- on CTEs and no actual stats are used. This leads to
          -- unfortunate join plans in the updated_edge CTE.
          (SELECT *
          FROM updated_parent_edge_data
          LIMIT (SELECT COUNT(*) FROM updated_parent_edge_data))
          UNION ALL
          (SELECT *
          FROM updated_child_edge_data
          LIMIT (SELECT COUNT(*) FROM updated_child_edge_data))
      ), old_edge AS (
          -- Get all old edges of changed nodes as well as their
          -- children (if any). Child edges contribute to the cable
          -- length as well and need to be updated.
          SELECT t.id, t.project_id, t.old_skeleton_id AS skeleton_id,
              t.old_creation_time AS creation_time,
              t.old_edition_time AS edition_time,
              e.edge,
              t.editor_id
          FROM updated_edge_data t
          JOIN treenode_edge e
              ON e.id = t.id
      ), updated_edge AS (
          -- Update all changed edges. To have this join work fast, we
          -- rely on reasonable statistics on the row count of
          -- updated_edge_data. This is provided, by setting (obivious)
          -- limits on its size when creating it.
          UPDATE treenode_edge e
          SET edge = ue.edge
          FROM updated_edge_data ue
          WHERE e.id = ue.id
          RETURNING e.id
      ), new_edge AS (
          -- Collect changed nodes both with and without location
          -- change. Updated edge information takes precedence.
          SELECT ue.id, ue.project_id, ue.skeleton_id,
              ue.creation_time, ue.edition_time, ue.edge, ue.editor_id
          FROM updated_edge_data ue
          UNION ALL
          SELECT nt.id, nt.project_id, nt.skeleton_id,
              nt.creation_time, nt.edition_time, oe.edge, nt.editor_id
          FROM new_treenode_data nt
          LEFT JOIN updated_edge_data ue
              ON nt.id = ue.id
          JOIN old_edge oe
              ON nt.id = oe.id
          WHERE ue.id IS NULL
      ), old_skeleton_data AS (
          -- Aggregate data over old skeleton datas to delete for summary.
          SELECT skeleton_id, project_id,
              -COUNT(*) AS num_nodes,
              -SUM(ST_3DLength(edge)) AS length,
              MIN(creation_time) AS min_creation_time,
              MAX(edition_time) AS max_edition_time,
              last_editor_id
          FROM (
              SELECT skeleton_id, project_id, edge, creation_time, edition_time,
                  first_value(editor_id) OVER w AS last_editor_id
              FROM old_edge
              WINDOW w AS (PARTITION BY skeleton_id, project_id ORDER BY edition_time DESC)
          ) edge_info
          GROUP BY skeleton_id, project_id, last_editor_id
      ), new_skeleton_data AS (
          -- Aggregate data over skeletons to prepare for summary update.
          SELECT skeleton_id, project_id,
              COUNT(*) AS num_nodes,
              SUM(ST_3DLength(edge)) AS length,
              MIN(creation_time) AS min_creation_time,
              MAX(edition_time) AS max_edition_time,
              last_editor_id
          FROM (
              SELECT skeleton_id, project_id, edge, creation_time, edition_time,
                  first_value(editor_id) OVER w AS last_editor_id
              FROM new_edge
              WINDOW w AS (PARTITION BY skeleton_id, project_id ORDER BY edition_time DESC)
          ) edge_info
          GROUP BY skeleton_id, project_id, last_editor_id
      ), summary_update_delta AS (
          SELECT skeleton_id, project_id,
              SUM(num_nodes) AS num_nodes,
              SUM(length) AS length,
              MIN(min_creation_time) AS min_creation_time,
              MAX(max_edition_time) AS max_edition_time,
              last_editor_id
          FROM (
              SELECT skeleton_id, project_id, num_nodes, length,
                  min_creation_time, max_edition_time,
                  first_value(last_editor_id) OVER w AS last_editor_id
              FROM (
                  SELECT os.skeleton_id, os.project_id, os.num_nodes,
                      os.length, os.min_creation_time, os.max_edition_time,
                      os.last_editor_id
                  FROM old_skeleton_data os
                  UNION ALL
                  SELECT ns.skeleton_id, ns.project_id, ns.num_nodes,
                      ns.length, ns.min_creation_time, ns.max_edition_time,
                      ns.last_editor_id
                  FROM new_skeleton_data ns
              ) _update_data
              WINDOW w AS (PARTITION BY skeleton_id, project_id ORDER BY max_edition_time DESC)
          ) update_data
          GROUP BY skeleton_id, project_id, last_editor_id
      )
      INSERT INTO catmaid_skeleton_summary (project_id, skeleton_id,
          last_summary_update, original_creation_time,
          last_edition_time, last_editor_id, num_nodes, cable_length)
      (
          SELECT s.project_id, s.skeleton_id, now(), s.min_creation_time,
              s.max_edition_time,
              COALESCE(NULLIF(current_setting('catmaid.user_id', TRUE), '')::integer, s.last_editor_id),
              s.num_nodes, s.length
          FROM summary_update_delta s
      )
      ON CONFLICT (skeleton_id) DO UPDATE
      SET num_nodes = catmaid_skeleton_summary.num_nodes + EXCLUDED.num_nodes,
          last_summary_update = EXCLUDED.last_summary_update,
          last_edition_time = GREATEST(
              catmaid_skeleton_summary.last_edition_time,
              EXCLUDED.last_edition_time),
          last_editor_id = EXCLUDED.last_editor_id,
          cable_length = catmaid_skeleton_summary.cable_length + EXCLUDED.cable_length;

      RETURN NEW;
  END;
  $$;
"""


class Migration(migrations.Migration):

    dependencies = [
        ('catmaid', '0069_fix_on_edit_treenode_connector_update_edges'),
    ]

    operations = [
            migrations.RunSQL(forward, backward),
    ]
