# -*- coding: utf-8 -*-
# Generated by Django 1.9.9 on 2016-10-04 08:25
from __future__ import unicode_literals

from django.db import migrations

UPDATE_PARENT_ID = """
    update snoop_document as doc set parent_id = case
      when path like '%/%' then
        (select id from temp_folder as tmp where
          tmp.path = regexp_replace(doc.path, '/[^/]*$', '') and
          (
            tmp.container_id = doc.container_id or
            (
              tmp.container_id is null and
              tmp.container_id is null
            )
          )
        )
      when container_id is not null then
        container_id
      when path != '' then
        (select id from temp_folder where container_id is null and path = '')
      else
        null
      end
"""

POPULATE_TABLE_TEMP_FOLDER = """
insert into temp_folder(id, container_id, path)
  select id, container_id, path
  from snoop_document
  where content_type = 'application/x-directory'
"""

CREATE_TABLE_TEMP_FOLDER = """create table temp_folder(
  id integer PRIMARY KEY,
  container_id integer,
  path text NOT NULL
)
"""

DROP_TABLE_TEMP_FOLDER = 'drop table temp_folder'

INSERT_ROOT = """
  insert into snoop_document(
    path,
    filename,
    disk_size,
    content_type,
    md5,
    sha1,
    broken,
    flags
  ) values (
    '',
    '',
    0,
    'application/x-directory',
    'd41d8cd98f00b204e9800998ecf8427e',
    'da39a3ee5e6b4b0d3255bfef95601890afd80709',
    '',
    '{}'
  )
"""
DELETE_ROOT = """
  delete from snoop_document where
    path = '' and filename = '' and content_type = 'application/x-directory'
"""

def count_documents(apps, schema_editor):
    db_alias = schema_editor.connection.alias
    Document = apps.get_model('snoop', 'Document')
    document_count = Document.objects.using(db_alias).count()
    return document_count

def insert_root(apps, schema_editor):
    document_count = count_documents(apps, schema_editor)
    if document_count > 0:
        schema_editor.execute(INSERT_ROOT)

def delete_root(apps, schema_editor):
    document_count = count_documents(apps, schema_editor)
    if document_count > 0:
        schema_editor.execute(DELETE_ROOT)

class Migration(migrations.Migration):

    dependencies = [
        ('snoop', '0003_auto_20161003_0755'),
    ]

    operations = [
        migrations.RunPython( # create root document
            insert_root,
            delete_root,
        ),
        migrations.RunSQL( # create temporary table with id, path
            CREATE_TABLE_TEMP_FOLDER,
            DROP_TABLE_TEMP_FOLDER,
        ),
        migrations.RunSQL(  # index on temp table's path
            "create index temp_folder_path_idx on temp_folder(container_id, path)",
            "drop index if exists temp_folder_path_idx",
        ),
        migrations.RunSQL( # populate the temp table with id, path from snoop_document
            POPULATE_TABLE_TEMP_FOLDER,
            "delete from temp_folder",
        ),
        migrations.RunSQL( # fill snoop_document.parent_id
            UPDATE_PARENT_ID,
            "update snoop_document set parent_id = null",
        ),
        migrations.RunSQL(  # drop the temporary table
            DROP_TABLE_TEMP_FOLDER,
            CREATE_TABLE_TEMP_FOLDER,
        )
    ]

@@ -1603,22 +1603,16 @@
 %22%22%0A%0Adef 
-count_
 document
@@ -1604,32 +1604,38 @@
 %22%0A%0Adef documents
+_exist
 (apps, schema_ed
@@ -1825,16 +1825,20 @@
 nt_count
+ %3E 0
 %0A%0Adef in
@@ -1869,32 +1869,35 @@
 ditor):%0A    
+if 
 document
 _count = cou
@@ -1888,79 +1888,36 @@
 ment
-_count = count_documents(apps, schema_editor)%0A    if document_count %3E 0
+s_exist(apps, schema_editor)
 :%0A  
@@ -1996,32 +1996,35 @@
 ditor):%0A    
+if 
 document
 _count = cou
@@ -2015,79 +2015,36 @@
 ment
-_count = count_documents(apps, schema_editor)%0A    if document_count %3E 0
+s_exist(apps, schema_editor)
 :%0A  

simplify data migration