#!/usr/bin/env python

from string import split

f = file("create_table.sql", "r")
all_lines = f.readlines()
f.close()

current_table_name = ""

for each_line in all_lines:
    
    if each_line.find( "CREATE TABLE") > -1:
        elements = split(each_line)
        current_table_name = elements[2]
    
    if each_line.find( "REFERENCES") > -1 and each_line.find("ALTER TABLE") == -1 :
        # Now we need to create the alter table commands
        # The line is in the form of  < `user_id` integer NOT NULL REFERENCES `auth_user` (`id`), >
        # It needs to get converted to
        # ALTER TABLE `crm_sector` ADD CONSTRAINT industry_id_refs_id_3656de07 
        # FOREIGN KEY (`industry_id`) REFERENCES `crm_industry` (`id`);

        elements = split(each_line)
        references_index =  elements.index("REFERENCES");
        refering_key = elements[0]
        refered_table = elements[references_index + 1]
        refered_key = elements[references_index + 2]
        if refered_key.find(",") > -1:
            refered_key = refered_key.replace(",", "")
        
        constraint_name = "fk_" + current_table_name.strip("`") + "_" +  refering_key.strip("`")
        
        alter_stmt = "ALTER TABLE " + current_table_name + \
                     " ADD CONSTRAINT " + constraint_name + \
                     " FOREIGN KEY (" + refering_key + ") REFERENCES " +\
                     refered_table + " " + refered_key;
        
        print alter_stmt;