using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.IO;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;


namespace joinconverter
{
    enum jointype {inner, left, right };

    class JoinRec
    {
        public string lefttable, righttable, leftcolumn,rightcolumn;
        public jointype jt;
        public bool used ;
    }

    class getJoinVisitor
    {
        
        
        List <JoinRec> jrs = new List<JoinRec>();

        public List<JoinRec>  getJrs()
        {
            return jrs;
        }


        public Boolean treenodevisitor(TLz_Node pnode, Boolean pIsLeafNode)
        {
            TLzCustomExpression expr = (TLzCustomExpression)pnode;
            if (expr.oper == TLzOpType.Expr_AND)
            {
                TLzCustomExpression lexpr = (TLzCustomExpression)expr.lexpr;
                TLzCustomExpression rexpr = (TLzCustomExpression)expr.rexpr;

                TLzCustomExpression slexpr;
                TLzCustomExpression srexpr;

                if (lexpr.oper == TLzOpType.Expr_Comparison)
                {
                    slexpr = (TLzCustomExpression)lexpr.lexpr;
                    srexpr = (TLzCustomExpression)lexpr.rexpr;
                    if (     ((slexpr.oper == TLzOpType.Expr_Attr) ||(slexpr.oper == TLzOpType.Expr_OuterJoin))
                          && ((srexpr.oper == TLzOpType.Expr_Attr) || (srexpr.oper == TLzOpType.Expr_OuterJoin))
                        )
                    {
                        TLz_Attr lattr = null,rattr=null;
                        JoinRec jr = new JoinRec();
                        jr.used = false;
                        jr.jt = jointype.inner;

                        if (slexpr.oper == TLzOpType.Expr_Attr)
                        {
                            lattr = (TLz_Attr)slexpr.lexpr;
                        }else if (slexpr.oper == TLzOpType.Expr_OuterJoin){
                            lattr = (TLz_Attr)((TLzCustomExpression)slexpr.lexpr).lexpr;
                            jr.jt = jointype.left;
                        }
                        jr.lefttable = lattr.Prefix;
                        jr.leftcolumn = lattr.lastname;

                        if (srexpr.oper == TLzOpType.Expr_Attr)
                        {
                            rattr = (TLz_Attr)srexpr.lexpr;
                        }
                        else if (srexpr.oper == TLzOpType.Expr_OuterJoin)
                        {
                            rattr = (TLz_Attr)((TLzCustomExpression)srexpr.lexpr).lexpr;
                            jr.jt = jointype.right;
                        }

                        jr.righttable = rattr.Prefix;
                        jr.rightcolumn = rattr.lastname;

                        if ((jr.lefttable != "") && (jr.righttable != "")){
                            lexpr.AsText = " ";
                            jrs.Add(jr);
                        }
                    }
                }

                if (rexpr.oper == TLzOpType.Expr_Comparison)
                {
                    slexpr = (TLzCustomExpression)rexpr.lexpr;
                    srexpr = (TLzCustomExpression)rexpr.rexpr;
                    if (     ((slexpr.oper == TLzOpType.Expr_Attr) || (slexpr.oper == TLzOpType.Expr_OuterJoin))
                          && ((srexpr.oper == TLzOpType.Expr_Attr) || (srexpr.oper == TLzOpType.Expr_OuterJoin)
                          )
                        )
                    {
                        TLz_Attr lattr = null, rattr = null;
                        JoinRec jr = new JoinRec();
                        jr.used = false;
                        jr.jt = jointype.inner;

                        if (slexpr.oper == TLzOpType.Expr_Attr)
                        {
                            lattr = (TLz_Attr)slexpr.lexpr;
                        }
                        else if (slexpr.oper == TLzOpType.Expr_OuterJoin)
                        {
                            lattr = (TLz_Attr)((TLzCustomExpression)slexpr.lexpr).lexpr;
                            jr.jt = jointype.left;
                        }
                        jr.lefttable = lattr.Prefix;
                        jr.leftcolumn = lattr.lastname;


                        if (srexpr.oper == TLzOpType.Expr_Attr)
                        {
                            rattr = (TLz_Attr)srexpr.lexpr;
                        }
                        else if (srexpr.oper == TLzOpType.Expr_OuterJoin)
                        {
                            rattr = (TLz_Attr)((TLzCustomExpression)srexpr.lexpr).lexpr;
                            jr.jt = jointype.right;
                        }

                        jr.righttable = rattr.Prefix;
                        jr.rightcolumn = rattr.lastname;

                        if ((jr.lefttable != "") && (jr.righttable != ""))
                        {
                            jrs.Add(jr);
                            rexpr.AsText = " ";
                        }
                    }
                }

               // Console.WriteLine(expr.oper.ToString());
               // Console.WriteLine(lexpr.oper.ToString()+" "+lexpr.AsText);
               // Console.WriteLine(rexpr.oper.ToString() + " " + rexpr.AsText);
                return true;
            }
            else
            {
                return false;
            }
        }
    }

    class oracleJoinConverter
    {
        private string query;
        public oracleJoinConverter(string sql)
        {
            this.query = sql;
        }

        public string getQuery()
        {
            return this.query;
        }

        public int convert()
        {
            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVOracle);
            sqlparser.SqlText.Text = this.query;
            int i = sqlparser.Parse();
            if (i != 0) return i;
            if (sqlparser.SqlStatements[0].SqlStatementType != TSqlStatementType.sstSelect) return 0;
            TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.SqlStatements[0];
            analyzeSelect(select);
            this.query = select.AsText;
            return 0;
        }

        List<TLzTable> leadingTables = new List<TLzTable>();
        private TLzTable getTablebyName(string tbname, TLzTableList tableList)
        {

            for (int i = 0; i < tableList.Count(); i++)
            {
                if ((tbname.Equals(tableList[i].TableFullname,StringComparison.CurrentCultureIgnoreCase))
                    ||(tbname.Equals(tableList[i].TableAlias,StringComparison.CurrentCultureIgnoreCase))
                    ){

                        return tableList[i];

                    }

            }

            return null;

        }
        private void addToLeadingTables(TLzTable table)
        {
            
            if (table == null) return;

            bool tableAlreadyExists = false;
            for (int i = 0; i < leadingTables.Count; i++)
            {
                if (table == leadingTables[i])
                {
                    tableAlreadyExists = true;
                    break;
                }
            }

            if (!tableAlreadyExists)
            {
                leadingTables.Add(table);
            }
        }

        private void analyzeSelect(TSelectSqlStatement select)
        {
            if (select.SelectSetType == TSelectSetType.sltNone)
            {
                if (select.Tables.Count() == 1) return;

                if (select.WhereClause == null)
                {
                    if (select.Tables.Count() > 1)
                    {
                        //cross join
                        string str = select.Tables[0].AsText;
                        for (int i = 1; i < select.Tables.Count(); i++)
                        {
                            str = str + "\ncross join " + select.Tables[i].AsText;
                        }
                        select.FromClauseText = str;
                    }
                }
                else
                {
                   
                    getJoinVisitor v = new getJoinVisitor();
                    
                    //get join conditions
                    select.WhereClause.PreOrderTraverse(v.treenodevisitor);
                    List<JoinRec> jrs = v.getJrs();
                    
                    TLzTable table;
                    JoinRec jr;
                    leadingTables.Add(select.Tables[0]);
                    String fromclause = select.Tables[0].AsText;
                    int leadingTablesPos = 0;
                    for (; ; )
                    {
                        if (leadingTablesPos > leadingTables.Count - 1) break;
                       // Console.WriteLine(leadingTablesPos + ":" + leadingTables.Count+":"+jrs.Count);
                        
                        table = leadingTables[leadingTablesPos];
                        for (int i = 0; i < jrs.Count; i++)
                        {
                            jr = jrs[i];
                            if (jr.used) continue;

                            if (
                                (table.TableFullname.Equals(jr.lefttable, StringComparison.CurrentCultureIgnoreCase))
                                || (table.TableAlias.Equals(jr.lefttable, StringComparison.CurrentCultureIgnoreCase))
                                )
                            {

                                if (jr.jt == jointype.left)
                                {
                                    fromclause = fromclause + "\n" + " right join " + getTablebyName(jr.righttable, select.Tables).AsText + " on " + jr.lefttable + "." + jr.leftcolumn + "=" + jr.righttable + "." + jr.rightcolumn;
                                }
                                else if (jr.jt == jointype.right)
                                {
                                    fromclause = fromclause + "\n" + " left join " + getTablebyName(jr.righttable, select.Tables).AsText + " on " + jr.lefttable + "." + jr.leftcolumn + "=" + jr.righttable + "." + jr.rightcolumn;
                                }
                                else
                                {
                                    fromclause = fromclause + "\n" + " " + jr.jt.ToString() + " join " + getTablebyName(jr.righttable, select.Tables).AsText + " on " + jr.lefttable + "." + jr.leftcolumn + "=" + jr.righttable + "." + jr.rightcolumn;
                                }



                                jr.used = true;
                                addToLeadingTables(getTablebyName(jr.righttable, select.Tables));
                            }
                            else
                                if (
                                    (table.TableFullname.Equals(jr.righttable, StringComparison.CurrentCultureIgnoreCase))
                                    || (table.TableAlias.Equals(jr.righttable, StringComparison.CurrentCultureIgnoreCase))
                                    )
                                {
                                    fromclause = fromclause + "\n" + " " + jr.jt.ToString() + " join " + getTablebyName(jr.lefttable, select.Tables).AsText + " on " + jr.lefttable + "." + jr.leftcolumn + "=" + jr.righttable + "." + jr.rightcolumn;

                                    jr.used = true;

                                    addToLeadingTables(getTablebyName(jr.lefttable, select.Tables));
                                }

                        }
                        leadingTablesPos++;
                    }

                   // Console.WriteLine(fromclause);
                    select.FromClauseText = fromclause;


                }
            }
            else
            {
                analyzeSelect(select.LeftStmt);
                analyzeSelect(select.RightStmt);
            }
        }

    }


    struct TLzJoinInfo
    {
        public string lefttable, righttable;
        public TSelectJoinType jointype;
        public TLzCustomExpression expr;
        public Boolean isDuplicated;
    }

    

    class joinconveter
    {
        static TLzJoinInfo[] gJoinInfos = new TLzJoinInfo[100];
        static int JoinInfoCount = 0;
        static string gTableName = "";
        static ArrayList gJoinExprs = new ArrayList();

        public static TLzTable findTableByNameOrAlias(string pName, TLzTableList pTables)
        {
            TLzTable ret = null;

            foreach (TLzTable t in pTables)
            {
                if (t.AliasClause != null){
                    if (lzbasetype.MyCompareText(pName, t.AliasClause.aliastext) == 0)
                    {
                        ret = t;
                    }
                }

                if (ret == null)
                {
                    if (lzbasetype.MyCompareText(pName, t.TableName) == 0)
                    {
                        ret = t;
                    }
                }

                if (ret != null)
                {
                    break;
                }
            }
            
            return ret;
        }


        public static Boolean dofindJoinInfo(TLz_Node pnode, Boolean pIsLeafNode)
        {
            Boolean ret = true;
            TLzCustomExpression lcExpr;
            TLz_Attr lcAttr;
            if (pnode is TLzCustomExpression)
            {
                lcExpr = pnode as TLzCustomExpression;
                switch (lcExpr.oper)
                {
                    case TLzOpType.Expr_subquery:
                        lcExpr.IsVisitSubTree = false;
                        break;
                    case TLzOpType.Expr_Attr:
                        lcAttr = lcExpr.lexpr as TLz_Attr;
                        if (lcAttr.ObjectNameToken != null)
                        {
                            gTableName = lcAttr.ObjectNameToken.AsText;
                        }
                        lcExpr.IsVisitSubTree = false;
                        break;
                }

            }
            return ret;
        }


        public static void findJoinInfo(TLzCustomExpression pExpr)
        {
            if (pExpr.oper == TLzOpType.Expr_Leftjoin)
            {
                JoinInfoCount++;
                gJoinInfos[JoinInfoCount - 1].jointype = TSelectJoinType.sjtleft;
                gTableName = "";
                TLzCustomExpression lcExpr;
                lcExpr = pExpr.lexpr as TLzCustomExpression;
                lcExpr.PreOrderTraverse(dofindJoinInfo);
                gJoinInfos[JoinInfoCount - 1].lefttable = gTableName;

                gTableName = "";
                lcExpr = pExpr.rexpr as TLzCustomExpression;
                lcExpr.PreOrderTraverse(dofindJoinInfo);
                gJoinInfos[JoinInfoCount - 1].righttable = gTableName;
                
                gJoinInfos[JoinInfoCount - 1].expr = pExpr;


            }
            else if (pExpr.oper == TLzOpType.Expr_Rightjoin)
            {
                JoinInfoCount++;
                gJoinInfos[JoinInfoCount - 1].jointype = TSelectJoinType.sjtright;
                gTableName = "";
                TLzCustomExpression lcExpr;
                lcExpr = pExpr.lexpr as TLzCustomExpression;
                lcExpr.PreOrderTraverse(dofindJoinInfo);
                gJoinInfos[JoinInfoCount - 1].lefttable = gTableName;

                gTableName = "";
                lcExpr = pExpr.rexpr as TLzCustomExpression;
                lcExpr.PreOrderTraverse(dofindJoinInfo);
                gJoinInfos[JoinInfoCount - 1].righttable = gTableName;

                gJoinInfos[JoinInfoCount - 1].expr = pExpr;

            }

            if ((pExpr.oper == TLzOpType.Expr_Leftjoin) || (pExpr.oper == TLzOpType.Expr_Rightjoin))
            {
                gJoinInfos[JoinInfoCount - 1].isDuplicated = false;

                for (int i = 0; i < JoinInfoCount-1; i++)
                {
                    if (
                        (gJoinInfos[JoinInfoCount - 1].jointype == gJoinInfos[i].jointype)
                        && (lzbasetype.MyCompareText(gJoinInfos[JoinInfoCount - 1].lefttable, gJoinInfos[i].lefttable) == 0)
                        && (lzbasetype.MyCompareText(gJoinInfos[JoinInfoCount - 1].righttable, gJoinInfos[i].righttable) == 0)
                        )
                    {
                        gJoinInfos[JoinInfoCount - 1].isDuplicated = true;
                        break;
                    }
                    else if (
                        (gJoinInfos[JoinInfoCount - 1].jointype != gJoinInfos[i].jointype)
                        && (lzbasetype.MyCompareText(gJoinInfos[JoinInfoCount - 1].lefttable, gJoinInfos[i].righttable) == 0)
                        && (lzbasetype.MyCompareText(gJoinInfos[JoinInfoCount - 1].righttable, gJoinInfos[i].lefttable) == 0)
                        )
                    {
                        gJoinInfos[JoinInfoCount - 1].isDuplicated = true;
                        break;
                    }

                }
            }
            
    
        }

        public static Boolean dofindjoins(TLz_Node pnode, Boolean pIsLeafNode)
        {
            Boolean ret = true;
            TLzCustomExpression lcExpr;
            
            if (pnode is TLzCustomExpression)
            {
                lcExpr = pnode as TLzCustomExpression;
                switch (lcExpr.oper)
                {
                    case TLzOpType.Expr_subquery:
                        lcExpr.IsVisitSubTree = false;
                        break;
                    case TLzOpType.Expr_Leftjoin:
                        gJoinExprs.Add(lcExpr);
                        lcExpr.IsVisitSubTree = false;
                        break;
                    case TLzOpType.Expr_Rightjoin:
                        gJoinExprs.Add(lcExpr);
                        lcExpr.IsVisitSubTree = false;
                        break;
                }

            }
            return ret;
        }

        public static void findjoins(TLzCustomExpression pExpr){
          gJoinExprs.Clear();
          pExpr.PreOrderTraverse(dofindjoins);
        }

        public static int sqlserverjointoansi(TSelectSqlStatement pSelect) 
        {
            int ret = 0;
            for (int i = 0; i < pSelect.ChildNodes.Count() ; i++)
            {
                if (pSelect.ChildNodes[i] is TSelectSqlStatement)
                {
                    ret = ret + sqlserverjointoansi(pSelect.ChildNodes[i] as TSelectSqlStatement);
                }
            }

            string lcFromStr = "";
            if (pSelect.WhereClause != null)
            {
                TLzCustomExpression lcWhere = pSelect.WhereClause;
                findjoins(lcWhere);
                if (gJoinExprs.Count > 0)
                {
                    JoinInfoCount = 0;
                    for (int i = 0; i < gJoinExprs.Count; i++)
                    {
                        findJoinInfo(gJoinExprs[i] as TLzCustomExpression);
                    }

                    if (JoinInfoCount > 0)
                    {
                        for (int i = 0; i<JoinInfoCount;i++){
                            gJoinInfos[i].expr.opname.AsText = "=";
                            if (gJoinInfos[i].isDuplicated)
                            {
                                lcFromStr = lcFromStr + " and " + gJoinInfos[i].expr.AsText;
                                continue;
                            }

                            ret++;

                            TLzTable lcLeftTable, lcRightTable;
                            lcLeftTable = findTableByNameOrAlias(gJoinInfos[i].lefttable,pSelect.Tables);
                            lcRightTable = findTableByNameOrAlias(gJoinInfos[i].righttable, pSelect.Tables);
                            if (lcLeftTable == null) { continue;}
                            if (lcRightTable == null) { continue; }

                            if (gJoinInfos[i].jointype == TSelectJoinType.sjtleft){
                                if (i == 0){
                                    lcFromStr = lcLeftTable.AsText + " left join "+lcRightTable.AsText+" on "+gJoinInfos[i].expr.AsText;
                                }else{
                                    lcFromStr = lcFromStr+Environment.NewLine + " left join "+lcRightTable.AsText+" on "+gJoinInfos[i].expr.AsText;
                                }
                            }

                            if (gJoinInfos[i].jointype == TSelectJoinType.sjtright){
                                if (i == 0){
                                    lcFromStr = lcLeftTable.AsText + " right join "+lcRightTable.AsText+" on "+gJoinInfos[i].expr.AsText;
                                }else{
                                    lcFromStr = lcFromStr+Environment.NewLine + " right join "+lcRightTable.AsText+" on "+gJoinInfos[i].expr.AsText;
                                }
                            }

                            gJoinInfos[i].expr.IsParseAfterSetValue = false;
                            gJoinInfos[i].expr.AsText = " ";

                        }

                        pSelect.FromClauseText = lcFromStr;
                        

                    }

                }
            }

            return ret;

        }

        static void Main(string[] args)
        {
            string lcsqltext = "";

            if (args.Length == 0)
            {
                Console.WriteLine("{0} scriptfile", "joinconverter");
                return;
            }

            
            string inputfile = args[0];

#region  Reading from file
            try
            {
                StreamReader re = File.OpenText(inputfile); 

                string input = null;
                while ((input = re.ReadLine()) != null)
                {
                    if (lcsqltext.Length > 0)
                    {
                        lcsqltext = lcsqltext + Environment.NewLine;
                    }
                    lcsqltext = lcsqltext + input;
                }
            }
            catch (Exception e)
            {
                // Let the user know what went wrong.
                Console.WriteLine("File could not be read:");
                Console.WriteLine(e.Message);
                return;
            }
#endregion  Reading from file

            oracleJoinConverter c = new oracleJoinConverter(lcsqltext);
            c.convert();
            Console.WriteLine(c.getQuery());

            return ;

            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);
            sqlparser.SqlText.Text = lcsqltext;
            sqlparser.Parse();
            if (sqlparser.ErrorCount != 0)
            {
                Console.WriteLine(sqlparser.ErrorMessages);
                return;
            }

            if (sqlparser.SqlStatements.Count() == 0){
                Console.WriteLine("no sql found");
                return;
            }

            if (sqlparser.SqlStatements[0].SqlStatementType != TSqlStatementType.sstSelect)
            {
                Console.WriteLine("no select sql found");
                return;
            }

            
            sqlserverjointoansi(sqlparser.SqlStatements[0] as TSelectSqlStatement);
            Console.WriteLine("sql converted:");
            Console.WriteLine(sqlparser.SqlStatements[0].AsText);


        }
    }
}