"""
sqldiff.py - Prints the (approximated) difference between models and database

TODO:
 - better support for relations
 - better support for constraints (mainly postgresql?)
 - support for table spaces with postgresql
 - when a table is not managed (meta.managed==False) then only do a one-way
   sqldiff ? show differences from db->table but not the other way around since
   it's not managed.
 
KNOWN ISSUES:
 - MySQL has by far the most problems with introspection. Please be
   carefull when using MySQL with sqldiff. 
   - Booleans are reported back as Integers, so there's know way to know if
     there was a real change.
   - Varchar sizes are reported back without unicode support so there size
     may change in comparison to the real length of the varchar.   
   - Some of the 'fixes' to counter these problems might create false 
     positives or false negatives.
"""

from django.core.management.base import BaseCommand
from django.core.management import sql as _sql
from django.core.management import CommandError
from django.core.management.color import no_style
from django.db import transaction, connection
from django.db.models.fields import IntegerField
from optparse import make_option

ORDERING_FIELD = IntegerField('_order', null=True)

def flatten(l, ltypes=(list, tuple)):
    ltype = type(l)
    l = list(l)
    i = 0
    while i < len(l):
        while isinstance(l[i], ltypes):
            if not l[i]:
                l.pop(i)
                i -= 1
                break
            else:
                l[i:i + 1] = l[i]
        i += 1
    return ltype(l)

class SQLDiff(object):
    DATA_TYPES_REVERSE_OVERRIDE = {
    }
    
    DIFF_TYPES = [
        'comment',
        'table-missing-in-db',
        'field-missing-in-db',
        'field-missing-in-model',
        'index-missing-in-db',
        'index-missing-in-model',
        'unique-missing-in-db',
        'unique-missing-in-model',
        'field-type-differ',
        'field-parameter-differ',
    ]
    DIFF_TEXTS = {
        'comment': 'comment: %(0)s',
        'table-missing-in-db': "table '%(0)s' missing in database",
        'field-missing-in-db' : "field '%(1)s' defined in model but missing in database",
        'field-missing-in-model' : "field '%(1)s' defined in database but missing in model",
        'index-missing-in-db' : "field '%(1)s' INDEX defined in model but missing in database",
        'index-missing-in-model' : "field '%(1)s' INDEX defined in database schema but missing in model",
        'unique-missing-in-db' : "field '%(1)s' UNIQUE defined in model but missing in database",
        'unique-missing-in-model' : "field '%(1)s' UNIQUE defined in database schema but missing in model",
        'field-type-differ' : "field '%(1)s' not of same type: db='%(3)s', model='%(2)s'",
        'field-parameter-differ' : "field '%(1)s' parameters differ: db='%(3)s', model='%(2)s'",
    }

    SQL_FIELD_MISSING_IN_DB = lambda self, style, qn, args: "%s %s\n\t%s %s %s;" % (style.SQL_KEYWORD('ALTER TABLE'), style.SQL_TABLE(qn(args[0])), style.SQL_KEYWORD('ADD'), style.SQL_FIELD(qn(args[1])), style.SQL_COLTYPE(args[2]))
    SQL_FIELD_MISSING_IN_MODEL = lambda self, style, qn, args: "%s %s\n\t%s %s;" % (style.SQL_KEYWORD('ALTER TABLE'), style.SQL_TABLE(qn(args[0])), style.SQL_KEYWORD('DROP COLUMN'), style.SQL_FIELD(qn(args[1])))
    SQL_INDEX_MISSING_IN_DB = lambda self, style, qn, args: "%s %s\n\t%s %s (%s);" % (style.SQL_KEYWORD('CREATE INDEX'), style.SQL_TABLE(qn("%s_idx" % '_'.join(args[0:2]))), style.SQL_KEYWORD('ON'), style.SQL_TABLE(qn(args[0])), style.SQL_FIELD(qn(args[1])))