from __future__ import with_statement
import os, sys
import csv
import re
import warnings

import topological_sort

RESERVED_FILEPATH = os.path.join (os.path.dirname (__file__), "reserved.txt")
try:
  KEYWORDS = set (open (RESERVED_FILEPATH).read ().split ())
except IOError:
  warnings.warn ("Unable to open %s" % RESERVED_FILEPATH)
  KEYWORDS = set ([])

r_inline_comments = re.compile (r"--.*\n")
r_multiline_comments = re.compile (r"/\*.*?\*/", re.DOTALL)
r_identifiers = re.compile (r"(?:(\w+)\.)?(\w+)")
r_object_name = re.compile (r"(?:alter|create|drop)\s+(table|proc(?:edure)?|view|function|trigger)\s+(?:(\w+)\.)?([A-Za-z0-9#_]+)(?:\s+on\s+(?:(\w+)\.)?(\w+))?", re.IGNORECASE)

def dependencies_for (filename, interactive=False):
  basename = os.path.basename (filename)
  base, ext = os.path.splitext (basename)
  text = open (filename).read ()
  text = r_multiline_comments.sub ("", text)
  text = r_inline_comments.sub ("", text)
  objects = [(type, schema or table_schema or "dbo", name) for (type, schema, name, table_schema, table_name) in r_object_name.findall (text) if name and not name.startswith ("#")]
  if not objects:
    if interactive: print "*** WARNING: filename %r does not contain any names" % base
    return (None, None, None), set ()
  
  object_type, object_schema, object_name = objects[0]
  if object_name <> base:
    if interactive: 
      print "*** WARNING: filename %r does not match object name %r" % (base, object_name)
      raw_input ("Press enter...")
  
  #
  # The problem is that, if a schema-qualified references an unqualified
  # object, the unqualified object could be in the same schema or the
  # dbo schema. We have both cases in our code base.
  #
  identifiers = set ((schema or object_schema, name) for (schema, name) in r_identifiers.findall (text) if not name.isdigit () and name.upper () not in KEYWORDS)
  return (object_type, object_schema, object_name), identifiers

def dependencies (root, interactive=False):
  object_dependencies = {}
  objects = {}
  for dirpath, dirnames, filenames in os.walk (root):
    for filename in (f for f in filenames if f.endswith (".sql")):
      if interactive: print filename
      (object_type, object_schema, object_name), identifiers = dependencies_for (os.path.join (dirpath, filename))
      if object_name:
        objects[object_schema, object_name] = object_type.upper ()
        object_dependencies[object_schema, object_name] = identifiers

  object_names = set (object_dependencies)  
  dependencies = dict ((object_name, identifiers & (object_names - set ([object_name]))) for (object_name, identifiers) in object_dependencies.items ())
  
  topological_dependencies = []
  for object_name, depends_on in dependencies.items ():
    if "confirmation" in object_name: print object_name, "DEPENDS ON", ", ".join (str (d) for d in depends_on)
    topological_dependencies.extend ((depend_on, object_name) for depend_on in depends_on)
  for name in topological_sort.sort (object_names, topological_dependencies):
    yield objects[name], name

def main (args=[]):
  ARGS = ROOT, OUTPUT_FILENAME = ["base", "dependencies.txt"]
  root, output_filename = [i or j for (i, j) in zip (args + [None for _ in ARGS], [None for _ in ARGS])]

  if root is None:
    root = raw_input ("Files are in : [%s] " % ROOT) or ROOT
  if output_filename is None:
    output_filename = raw_input ("Results to : [%s] " % OUTPUT_FILENAME) or OUTPUT_FILENAME

  with open (output_filename, "w") as f:
    f.write ("\n".join ("%s\t%s.%s" % (type, schema, name) for (type, (schema, name)) in dependencies (root, True)))

if __name__ == '__main__':
  main (sys.argv[1:])