import re

regex_tables=re.compile("""^[\w]+\.define_table\(\s*[\'\"](?P<name>[\w_]+)[\'\"]""",flags=re.M)

conversion={
'references':'integer',
'clob':'text',
'varchar':'string',
'int':'integer',
'int4':'integer',
'bool':'boolean',
'float':'double',
'date':'date',
'datetime':'datetime',
'timestamp':'datetime',
'time':'time'
}

def convert(field):
    if conversion.has_key(field): return conversion[field]
    return field

def auto(data):
    regex1=re.compile('\s*(?P<name>\S+\s+\S+).*')
    data=data.lower()
    tables={}
    output=''
    for item in re.compile('create\s+table\s+(?P<tablename>\S+)\s*\((?P<fields>.*)\);').findall(data):
        tablename,fields=item
        fields=[regex1.match(i).group('name').split() for i in fields.split(',')]
        sfields=''.join(["SQLField('%s','%s'),\n    " % (k,convert(v)) for k,v in fields if not k.lower() in ['index','contraint']])
        output+="db.define_table('%s',\n    %smigrate='%s.mig')\n" % (tablename, sfields, tablename)
        tables[tablename]=[k for k,v in fields]
        labels=','.join(["'%s':T('%s')"%(f,f) for f in tables[tablename]])
        output+='%s_labels={%s}\n\n' % (tablename,labels)
    tablenames=tables.keys()
    tablenames.sort()
    model=output
    output=''
    for tablename in tablenames:
        output+="""
def list_%(table)s():
    rows=db().select(db.%(table)s.ALL)
    return dict(rows=rows)
def create_%(table)s():
    form=SQLFORM(db.%(table)s,fields=%(fields)s,labels=%(table)s_labels)
    if form.accepts(request.vars,session):
        session.flash=T('new %(table)s created')
        redirect(URL(r=request,f='list_%(table)s'))
    elif form.errors:
        reponse.flash=T('invalid form')
    return dict(form=form)
def update_%(table)s():
    if len(request.args)<1: rows=[]
    else: rows=db(db.%(table)s.id==request.args[0]).select()
    if not len(rows): 
        session.flash=T('%(table)s not found')
        redirect(URL(r=request,f='list_%(table)s'))
    form=SQLFORM(db.%(table)s,rows[0],fields=%(fields)s,labels=%(table)s_labels)
    if form.accepts(request.vars,session):
        session.flash=T('%(table)s stored')
        redirect(URL(r=request,f='list_%(table)s'))
    elif form.errors:
        reponse.flash=T('invalid form')
    return dict(form=form)
def delete_%(table)s():
    if len(request.args)!=1: 
        session.flash=T('%(table)s not found')
        redirect(URL(r=request,f='list_%(table)s'))
    if request.vars.sure=='yes':
        rows=db(db.%(table)s.id==request.args[0]).select()
        if not rows:
            session.flash=T('%(table)s not found')
        else: 
            db(db.%(table)s.id==request.args[0]).delete()
            session.flash=T('%(table)s deleted')
        redirect(URL(r=request,f='list_%(table)s'))
    else:
        form=DIV(A(T('yes'),_href=URL(r=request,args=request.args,vars=dict(sure='yes'))),A(T('no'),_href=URL(r=request,args=request.args,vars=dict(sure='no'))))
    return dict(form=form)
""" % dict(table=tablename,fields=tables[tablename])
    controller=output
    return '# MODEL\n'+model+'# CONTROLLER\n'+controller

if __name__=='__main__':
    print auto(open(sys.argv[1],'r').read())