#!/usr/bin/env python
# -*- coding: UTF-8 -*-

__author__ = "Yate"
__version__ = "1.0.0"
__maintainer__ = "Yate"

import sys
import os
import re

class Database(object):
    def __init__(self):
        self.tables = []

    def AddTable(self,name):
        t = Table(name)
        self.tables.append(t)

    def GetLastTable(self):
        return self.tables[-1]

class Table:
    def __init__(self,name):
        self.name=name
        self.fields=[]
        self.pks=[]

    def AddField(self,name,t,length,IsPK):
        fd = Field(name,t,length,IsPK)
        self.fields.append(fd)

    def AddPK(self,name):
        self.pks.append(name)
        for f in self.fields:
            if f.name == name:
                f.IsPK = True


class Field:
    def __init__(self,name,t,length,IsPK):
        self.name=name
        self.type=t
        self.length=length
        self.IsPK=IsPK

class JavaType:
    def __init__(self,pkg,tn):
        self.pkg = pkg
        self.table_name = tn
        self.clazz = ""
        self.fields = []

    def AddJavaField(self,sql_fn,java_fn,sql_type,java_type,IsPK):
        fd = JavaFieldType(sql_fn,java_fn,sql_type,java_type,IsPK)
        self.fields.append(fd)

class JavaFieldType:
    def __init__(self,sql_fn,java_fn,sql_type,java_type,IsPK):
        self.sql_fn = sql_fn
        self.java_fn = java_fn
        self.sql_type = sql_type
        self.java_type = java_type
        self.IsPK = IsPK

def issubstr_exp(exp,s):
    r = re.match(exp,s,re.I)
    if r:
        return True;
    return False;

def issubstr(s1, s2):
    return s1 in s2

def get_between(str):
    num1 = str.find("`", 0)
    num2 = str.find("`", num1+1)
    if num2 -num1 <= 0:
        return str.split(" ")[-1].replace("(","")
    res = str[(num1+1):num2]
    return res

def get_field_name(str):
    num1 = str.find("`", 0)
    num2 = str.find("`", num1+1)
    if num2 -num1 <= 0:
        return str
    res = str[(num1+1):num2]
    return res

def get_field_type(str):
    num1 = str.find("(", 0)
    num2 = str.find(")", num1+1)
    print str,num1,num2
    if num2 == -1 and num1 > 0:
        return str[:num1]
    if num2 -num1 <= 0:
        return str
    res = str[:num1]
    print str,num1,num2,res
    return res

def get_field_length(str):
    num1 = str.find("(", 1)
    num2 = str.find(")", num1+1)
    if num2 -num1 <= 0:
        return 0
    res = str[(num1+1):num2]
    return res

def sqltype2javatype(type):
    if type == 'int':
        return "Integer"
    if type == 'tinyint':
        return "Integer"
    if type == 'smallint':
        return "Integer"
    if type == 'bigint':
        return "Long"
    if type == 'varchar': 
        return "String"
    if type == 'char': 
        return "String"
    if type == 'timestamp':
        return "java.util.Date"
    if type == 'datetime': 
        return "java.util.Date"
    if type == 'date':
        return "java.util.Date"
    if type == 'decimal':
        return "java.math.BigDecimal"
    if type == 'double':
        return "java.math.BigDecimal"
    if type == 'float':
        return "java.math.BigDecimal"
    if type == 'text':
        return "String"
    

def py2java(pkg,Database):
    for ts in db.tables: 
        jt = JavaType(pkg,ts.name.replace("\n",""))

        javaclass=""
        tmp1 = ts.name.split("_")
        for x1 in tmp1:
            javaclass += x1.capitalize()

        jt.clazz = javaclass.replace("\n","")
        
        
        for fs in ts.fields:
            sql_fn = fs.name
            sql_type=fs.type
            java_fn ="" 
            java_type=""

            tmp2 = fs.name.split("_")
            for x2 in tmp2:
                if java_fn == "":
                    java_fn += x2
                else:
                    java_fn += x2.capitalize()
            java_type=sqltype2javatype(fs.type)

            jt.AddJavaField(sql_fn,java_fn,sql_type,java_type,fs.IsPK)

        writeDotJava(jt)
        wirteMyBatis(jt)
        wirteMapper(jt)
        wirteService(jt)
        wirteBaseService(jt)


def writeDotJava(jt):
    author = """
/**
* 本段代码由sql2java自动生成.
* https://github.com/yangting/sql2java
* @author Yate
*/\n"""

    s = "package "+jt.pkg+".metadata.entity;\n\n"
    s +="import java.io.Serializable;\n\nimport lombok.EqualsAndHashCode;\nimport lombok.ToString;\nimport lombok.experimental.Accessors;"
    s += author
    s += "@ToString\n@EqualsAndHashCode(exclude={\""+jt.fields[0].java_fn+"\"})\n"
    s +="public class "+jt.clazz+" implements Serializable{\n"        

    for f in jt.fields:
        print jt.clazz,f.java_type ,f.java_fn
        s+="private "+f.java_type +" "+f.java_fn+";\n"

    for f in jt.fields:
        s+="public void set"+f.java_fn[0].capitalize()+f.java_fn[1:]+"("+f.java_type+" v){\n\tthis."+f.java_fn+"=v;\n}\n\n"
        s+="public "+f.java_type+" get"+f.java_fn[0].capitalize()+f.java_fn[1:]+"(){\n\treturn this."+f.java_fn+";\n}\n\n";

    s+="}"

    x = jt.pkg.replace(".", "/")
    if not os.path.exists("src/main/java/"+x+"/metadata/entity"):
        os.makedirs("src/main/java/"+x+"/metadata/entity")

    f = open("src/main/java/"+x+"/metadata/entity/"+jt.clazz+".java",'w')
    f.write(s) # python will convert \n to os.linesep
    f.close() # you can omit in most cases as the destructor will call it

def wirteMyBatis(jt):
    s="<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"
    s+="<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\n"
    s+="<mapper namespace=\""+jt.pkg+".metadata.dao.mapper."+jt.clazz+"Mapper\">\n\n"

    s+="<resultMap type=\""+jt.pkg+".metadata.entity."+jt.clazz+"\" id=\""+jt.clazz+"Map\">\n"
    for f in jt.fields:
        if f.IsPK:
            s+="<id property=\""+f.java_fn+"\" column=\""+f.sql_fn+"\" />\n"
        else:
            s+="<result property=\""+f.java_fn+"\" column=\""+f.sql_fn+"\" />\n"
    s+="</resultMap>\n\n"

    s+="<!-- auto implement code by "+jt.pkg+".metadata.dao.IBaseMapperDao.java -->\n"
    s+="<select id=\"getEntity\" resultMap=\""+jt.clazz+"Map\">\n"
    s+="select * from "+jt.table_name+" where "
    for f in jt.fields:
        if f.IsPK:
            s+= f.sql_fn + "= #{"+f.java_fn+"} and "
    s = s[0:-4]+"\n"
    s+="</select>\n\n"


    s+="<insert id=\"add\">\n"
    s+="<selectKey resultType=\""+jt.fields[0].java_type+"\" order=\"AFTER\" keyProperty=\"id\">\n"
    s+="SELECT LAST_INSERT_ID() AS id\n"
    s+="</selectKey>\n"
    s+="insert into "+jt.table_name+"("
    for f in jt.fields:
        if not f.IsPK:
            s+=f.sql_fn+","
    s=s[:-1]+") values("
    for f in jt.fields:
        if not f.IsPK:
            s+="#{"+f.java_fn+"},"
    s=s[:-1]+")\n"
    s+="</insert>\n\n"

    s+="<insert id=\"batchAdd\">"
    s+="insert into "+jt.table_name+"("
    for f in jt.fields:
        if not f.IsPK:
            s+=f.sql_fn+","
    s=s[:-1]+") values\n"
    s+="<foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\">\n"
    s+="("
    for f in jt.fields:
        if not f.IsPK:
            s+="#{item."+f.java_fn+"},"
    s=s[:-1]+")\n"
    s+="</foreach>\n"
    s+="</insert>\n\n"


    s+="<update id=\"update\">\n"
    s+="update "+jt.table_name+ "\n<set>\n"
    for f in jt.fields:
        if not f.IsPK:
            s+="<if test=\""+f.java_fn+"!=null\" >\n"
            s+=f.sql_fn+"=#{"+f.java_fn+"},\n"
            s+="</if>\n"
    s+="</set>\n"
    s+="where "+jt.fields[0].sql_fn+"=#{"+jt.fields[0].java_fn+"}\n"
    s+="</update>\n\n"

    s+="<delete id=\"remove\">\n"
    s+="delete from "+jt.table_name+ "\n"
    s+="where "+jt.fields[0].sql_fn+"=#{"+jt.fields[0].java_fn+"}\n"
    s+="</delete>\n\n"

    s+="<delete id=\"batchRemove\">\n"
    s+="delete from "+jt.table_name+ " where id in \n"
    s+="<foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\" open=\"(\" close=\")\">\n"
    s+="#{item."+jt.fields[0].java_fn+"}\n"
    s+="</foreach>\n"
    s+="</delete>\n\n"

    s+="</mapper>"

    if not os.path.exists("src/main/resources/mybatis/mapper"):
        os.makedirs("src/main/resources/mybatis/mapper")

    f = open("src/main/resources/mybatis/mapper/"+jt.clazz+"Mapper.xml",'w')
    f.write(s) # python will convert \n to os.linesep
    f.close() # you can omit in most cases as the destructor will call it
    
def wirteMapper(jt):
    author = """
/**
* 本段代码由sql2java自动生成.
* https://github.com/yangting/sql2java
* @author Yate
*/\n"""
    s = "package "+jt.pkg+".metadata.dao.mapper;\n"
    s +="import org.springframework.stereotype.Repository;\n"
    s +="import "+jt.pkg+".metadata.dao.IBaseMapperDao;\n"
    s +="import "+jt.pkg+".metadata.entity."+jt.clazz+";\n\n"
    s += author
    s += "@Repository\n"
    s +="public interface "+jt.clazz+"Mapper extends IBaseMapperDao<"+jt.clazz+", "+jt.fields[0].java_type+">{\n" 
    s +="}\n" 

    x = jt.pkg.replace(".", "/")
    if not os.path.exists("src/main/java/"+x+"/metadata/dao/mapper"):
        os.makedirs("src/main/java/"+x+"/metadata/dao/mapper")

    f = open("src/main/java/"+x+"/metadata/dao/mapper/"+jt.clazz+"Mapper.java",'w')
    f.write(s) # python will convert \n to os.linesep
    f.close() # you can omit in most cases as the destructor will call it

    base="""import java.util.List;

import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;

/**
* 本段代码由sql2java自动生成.
* https://github.com/yangting/sql2java
* @author Yate
*/
public interface IBaseMapperDao<E, PK> {
    /**
     * @description 通过实体进行添加
     * @param e
     */
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void add(final E e);

    /**
     * @description 通过集合进行批量添加
     * @param e
     */
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void batchAdd(final List<E> list);

    /**
     * @description 通过主键进行删除
     * @param e
     */
    Integer remove(@Param(value = "id") final PK e);

    /**
     * @description 通过主键进行批量删除
     * @param e
     */
    void batchRemove(final PK[] ids);

    /**
     * @description 通过实体更新
     * @param e
     */
    Integer update(final E e);

    /**
     * @description 通过主键查询
     * @param id
     * @return
     * @throws Exception
     */
    E getEntity(@Param(value = "id") final PK id);
}"""

    f1 = open("src/main/java/"+x+"/metadata/dao/IBaseMapperDao.java",'w')
    f1.write("package "+jt.pkg+".metadata.dao;\n"+base) # python will convert \n to os.linesep
    f1.close() # you can omit in most cases as the destructor will call it


def wirteService(jt):
    author = """
/**
* 本段代码由sql2java自动生成.
* https://github.com/yangting/sql2java
* @author Yate
*/\n"""
    s = "package "+jt.pkg+".service;\n\n"
    s +="import "+jt.pkg+".metadata.entity."+jt.clazz+";\n\n"
    s += author
    s += "public interface I"+jt.clazz+"Service extends IBaseService<"+jt.clazz+","+jt.fields[0].java_type+">{\n\n"
    s += "}"

    x = jt.pkg.replace(".", "/")
    if not os.path.exists("src/main/java/"+x+"/service"):
        os.makedirs("src/main/java/"+x+"/service")


    f1 = open("src/main/java/"+x+"/service/I"+jt.clazz+"Service.java",'w')
    f1.write(s) # python will convert \n to os.linesep
    f1.close() # you can omit in most cases as the destructor will call it

    s1 ="package "+jt.pkg+".service.impl;\n\n"
    s1+="import org.springframework.stereotype.Service;\n"
    s1+="import javax.annotation.Resource;\n"
    s1+="import "+jt.pkg+".metadata.dao.IBaseMapperDao;\n"
    s1+="import "+jt.pkg+".metadata.dao.mapper."+jt.clazz+"Mapper;\n"
    s1+="import "+jt.pkg+".service.I"+jt.clazz+"Service;\n"
    s1+="import "+jt.pkg+".metadata.entity."+jt.clazz+";\n\n"
    s1+=author
    s1+="@Service\n"
    s1+="public class "+jt.clazz+"ServiceImpl extends BaseServiceImpl<"+jt.clazz+","+jt.fields[0].java_type+"> implements I"+jt.clazz+"Service {\n"
    s1+="@Resource\n"
    s1+="private "+jt.clazz+"Mapper mapper;\n\n"
    s1+="protected IBaseMapperDao<"+jt.clazz+","+jt.fields[0].java_type+"> getMapperDao() {\n"
    s1+="return mapper;\n"    
    s1+="}\n}"

    x1 = jt.pkg.replace(".", "/")
    if not os.path.exists("src/main/java/"+x1+"/service/impl"):
        os.makedirs("src/main/java/"+x1+"/service/impl")

    f1 = open("src/main/java/"+x1+"/service/impl/"+jt.clazz+"ServiceImpl.java",'w')
    f1.write(s1) # python will convert \n to os.linesep
    f1.close() # you can omit in most cases as the destructor will call it


def wirteBaseService(jt):
    author = """
/**
* 本段代码由sql2java自动生成.
* https://github.com/yangting/sql2java
* @author Yate
*/\n"""

    ibase = """

    import java.util.List;

/**
 * 本段代码由sql2java自动生成.
 * https://github.com/yangting/sql2java
 * @author Yate
 */
public interface IBaseService<E, PK> {
    void add(final E e);

    void batchAdd(final List<E> list);

    int remove(final PK id);

    void batchRemove(final PK[] ids);
 
    int update(final E e);
    
    E getEntity(final PK id);

}"""
    
    x = jt.pkg.replace(".", "/")
    if not os.path.exists("src/main/java/"+x+"/service"):
        os.makedirs("src/main/java/"+x+"/service")

    f1 = open("src/main/java/"+x+"/service/IBaseService.java",'w')
    f1.write("package "+jt.pkg+".service;\n"+ibase) # python will convert \n to os.linesep
    f1.close() # you can omit in most cases as the destructor will call it

    xbase ="import "+jt.pkg+".metadata.dao.IBaseMapperDao;\n"
    xbase +="import "+jt.pkg+".service.IBaseService;\n"
    xbase +="import java.util.List;\n\n"
    xbase +=author
    xbase +="public abstract class BaseServiceImpl<E, PK> implements IBaseService<E, PK> {\n"
    xbase +="protected abstract IBaseMapperDao<E, PK> getMapperDao();\n"
    xbase +="public void add(E e) {\n"
    xbase +="this.getMapperDao().add(e);\n"
    xbase +="}\n\n"
    xbase +="public void batchAdd(List<E> list) {\n"
    xbase +="this.getMapperDao().batchAdd(list);\n"
    xbase +="}\n\n"
    xbase +="public int remove(PK id) {\n"
    xbase +="return this.getMapperDao().remove(id);\n"
    xbase +="}\n\n"
    xbase +="public void batchRemove(PK[] ids) {\n"
    xbase +="this.getMapperDao().batchRemove(ids);\n"
    xbase +="}\n\n"
    xbase +="public int update(E e) {\n"
    xbase +="return this.getMapperDao().update(e);\n"
    xbase +="}\n\n"
    xbase +="public E getEntity(PK id) {\n"
    xbase +="return this.getMapperDao().getEntity(id);\n"
    xbase +="}\n\n}"

    f1 = open("src/main/java/"+x+"/service/impl/BaseServiceImpl.java",'w')
    f1.write("package "+jt.pkg+".service.impl;\n\n"+xbase) # python will convert \n to os.linesep
    f1.close() # you can omit in most cases as the destructor will call it



if __name__=='__main__':
    sql_file = sys.argv[1]
    pkg_name = sys.argv[2]
    f = open(sql_file)

    db = Database()

    line = f.readline()
    skip = False
    while line:
        print line
        if skip :
            if issubstr_exp(".*\*/",line):
                skip = False
                line = f.readline()
                continue
            else:
                line = f.readline()
                continue
        if issubstr_exp("^/\*.*",line):
            skip = True
            line = f.readline()
            continue
        if issubstr_exp("^SET.*",line):
            line = f.readline()
            continue
        if issubstr_exp(".*INDEX.*",line):
            line = f.readline()
            continue
        if issubstr_exp("DROP.*",line):
            line = f.readline()
            continue
        if issubstr_exp(".*SET.*FOREIGN_KEY_CHECKS.*",line):
            line = f.readline()
            continue
        if issubstr_exp("^--.*",line):
            line = f.readline()
            continue
        if issubstr_exp("^/\*.*",line):
            line = f.readline()
            continue
        if issubstr_exp(".*drop.*table.*",line):
            line = f.readline()
            continue
        if issubstr_exp(".*create.*table.*",line):
            table_name = get_between(line)
            print table_name
            db.AddTable(table_name)
        elif issubstr_exp(".*primary.*key.*",line):
            r = re.match(".*\((.*)\)",line,re.I)
            if r:
                pkstr = r.group(1)
                pkarr = pkstr.split(',')
                #print pkstr,pkarr
                for x in pkarr:
                    #print x.replace("`","")
                    db.GetLastTable().AddPK(x.replace("`",""))
            line = f.readline()            
            continue
        elif issubstr_exp("\s*key.*",line):
            line = f.readline()
            continue
        elif issubstr_exp(".*engine.*",line):
            line = f.readline()
            continue
        else:
            ds = line.split()
            if len(ds) > 0:
                fname = get_field_name(ds[0])
                if ds[1]:
                    x = ds[1]
                    ftype = get_field_type(x)
                    flen = get_field_length(x)
                    print "test=",db.GetLastTable().name,fname,ftype,flen
                    if fname == None :
                        break;
                    if ftype == None :
                        break;
                    db.GetLastTable().AddField(fname,ftype,flen,False)
        line = f.readline()
    f.close()

    py2java(pkg_name,db)