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

namespace gettable
{
    class getTable
    {
        static void Main(string[] args)
        {

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


            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVOracle);
            sqlparser.Sqlfilename = args[0];

            Console.WriteLine("Processing..");
            int i2 = sqlparser.Parse();
            if (i2 == 0)
            {

                TLzScriptExplorer se = new TLzScriptExplorer();
                se.sqlstatements = sqlparser.SqlStatements;
                se.AnalyzeScript();
                se.FlattenDBObjectList();
                Console.WriteLine(se.GetDBObjectInfoIntoList());

            }
            else
                Console.WriteLine(sqlparser.ErrorMessages);


        }
    }

    public class TLzScriptExplorer
    {
        public TLzStatementList sqlstatements;
        public TLzDBObjectList topleveldbobjectlist;
        public TLzDBObjectList serverlist, databaselist, schemalist, tablelist, indexlist, viewlist, functionlist, triggerlist, sequencelist, packagelist;

        TSourceToken CurrentDatabaseToken;
        public Boolean isSaveXML;
        public String XML;

        public TLzScriptExplorer()
        {
            sqlstatements = null;
            topleveldbobjectlist = new TLzDBObjectList(false);
            isSaveXML = true;
            XML = "";
            CurrentDatabaseToken = null;

            serverlist = new TLzDBObjectList(false);
            databaselist = new TLzDBObjectList(false);
            schemalist = new TLzDBObjectList(false);
            tablelist = new TLzDBObjectList(false);
            indexlist = new TLzDBObjectList(false);
            viewlist = new TLzDBObjectList(false);
            functionlist = new TLzDBObjectList(false);
            triggerlist = new TLzDBObjectList(false);
            sequencelist = new TLzDBObjectList(false);
            packagelist = new TLzDBObjectList(false);
        }

        public void DoSortDBObjectList(TLzDBObjectList pList)
        {
            if (pList.Count() == 0) { return; }
            if (pList.Count() == 1)
            {
                if (pList[0].ObjectType == TDBObjType.ttObjTable)
                {
                    DoSortDBObjectList(pList[0].ChildDBObjects);
                }
                return;
            }

            int j = 0;
            string str1, str2;
            if (pList[0].ObjectType != TDBObjType.ttObjField)
            {
                //sort by object prefix first
                for (int i = 0; i <= pList.Count() - 2; i++)
                {
                    str1 = pList[i].ObjectPrefix;
                    for (int k = i + 1; k <= pList.Count() - 1; k++)
                    {
                        str2 = pList[k].ObjectPrefix;
                        if (str1.ToLower().CompareTo(str2.ToLower()) > 0)
                        {
                            TLzDBObject lcobj = pList[k];
                            pList[k] = pList[i];
                            pList[i] = lcobj;
                            str1 = str2;
                        }
                    }
                }

                //sort of object in the same prefix
                j = 0;

                str1 = pList[0].ObjectPrefix;

                for (int i = 1; i <= pList.Count() - 1; i++)
                {
                    str2 = pList[i].ObjectPrefix;
                    if (str1.ToLower().CompareTo(str2.ToLower()) != 0)
                    {
                        SortObjectInSamePrefix(j, i - 1, pList);
                        j = i;
                        str1 = str2;
                    }
                }

            }

            SortObjectInSamePrefix(j, pList.Count() - 1, pList);

            if (pList[0].ObjectType == TDBObjType.ttObjTable)
            {
                foreach (TLzDBObject tableobj in pList)
                {
                    DoSortDBObjectList(tableobj.ChildDBObjects);
                }
            }
        }

        public void SortObjectInSamePrefix(int pstart, int pend, TLzDBObjectList plist)
        {
            string str1, str2;

            if (pend - pstart < 1) { return; }
            for (int i = pstart; i <= pend - 1; i++)
            {
                str1 = plist[i].ObjectName;
                for (int j = i + 1; j <= pend; j++)
                {
                    str2 = plist[j].ObjectName;
                    if (str1.ToLower().CompareTo(str2.ToLower()) > 0)
                    {
                        TLzDBObject lcobj = plist[j];
                        plist[j] = plist[i];
                        plist[i] = lcobj;
                        str1 = str2;
                    }
                }
            }
        }

        public void SortDBObjectList()
        {
            DoSortDBObjectList(serverlist);
            DoSortDBObjectList(databaselist);
            DoSortDBObjectList(schemalist);
            DoSortDBObjectList(tablelist);
            DoSortDBObjectList(functionlist);
            DoSortDBObjectList(triggerlist);
            DoSortDBObjectList(viewlist);
            DoSortDBObjectList(indexlist);
            DoSortDBObjectList(sequencelist);
            DoSortDBObjectList(packagelist);
        }

        public string getdbobjectstr(TLzDBObjectList plist)
        {
            StringBuilder st = new StringBuilder();
            foreach (TLzDBObject obj in plist)
            {
                if (st.Length > 0)
                {
                    st.Append(Environment.NewLine);
                }
                st.Append(obj.ObjectFullName);
            }

            return st.ToString();
        }

        public string GetDBObjectInfoIntoList()
        {
            StringBuilder st = new StringBuilder();
            st.Append("tables\n");
            st.Append(getdbobjectstr(tablelist) + "\n");

            st.Append("fields\n");
            foreach (TLzDBObject tableobj in tablelist)
            {
                foreach (TLzDBObject fieldobj in tableobj.ChildDBObjects)
                {
                    st.Append(tableobj.ObjectFullName + "." + fieldobj.ObjectName + "\n");
                }
            }

            if (serverlist.Count() > 0)
            {
                st.Append("server\n");
                st.Append(getdbobjectstr(serverlist) + "\n");
            }

            if (databaselist.Count() > 0)
            {
                st.Append("database\n");
                st.Append(getdbobjectstr(databaselist) + "\n");
            }

            if (schemalist.Count() > 0)
            {
                st.Append("schema\n");
                st.Append(getdbobjectstr(schemalist) + "\n");
            }

            if (functionlist.Count() > 0)
            {
                st.Append("functions\n");
                st.Append(getdbobjectstr(functionlist) + "\n");
            }

            if (indexlist.Count() > 0)
            {
                st.Append("index\n");
                st.Append(getdbobjectstr(indexlist) + "\n");
            }

            if (viewlist.Count() > 0)
            {
                st.Append("view\n");
                st.Append(getdbobjectstr(viewlist) + "\n");
            }

            if (triggerlist.Count() > 0)
            {
                st.Append("triggers\n");
                st.Append(getdbobjectstr(triggerlist) + "\n");
            }

            if (sequencelist.Count() > 0)
            {
                st.Append("sequences\n");
                st.Append(getdbobjectstr(sequencelist) + "\n");
            }

            if (packagelist.Count() > 0)
            {
                st.Append("packages\n");
                st.Append(getdbobjectstr(packagelist) + "\n");
            }

            return st.ToString();
        }

        public void FlattenDBObjectList()
        {
            serverlist.Clear();
            databaselist.Clear();
            schemalist.Clear();
            tablelist.Clear();
            indexlist.Clear();
            viewlist.Clear();
            functionlist.Clear();
            triggerlist.Clear();
            sequencelist.Clear();
            packagelist.Clear();

            foreach (TLzDBObject serverobj in topleveldbobjectlist)
            {
                if (serverobj.ObjectToken.TokenStatus != TTokenStatus.tsAddbyHand)
                {
                    serverlist.Add(serverobj);
                }

                foreach (TLzDBObject databaseobj in serverobj.ChildDBObjects)
                {
                    if (databaseobj.ObjectType != TDBObjType.ttobjDatabase)
                    {
                        continue;
                    }

                    if (databaseobj.ObjectToken.TokenStatus != TTokenStatus.tsAddbyHand)
                    {
                        databaselist.Add(databaseobj);
                    }

                    foreach (TLzDBObject schemaobj in databaseobj.ChildDBObjects)
                    {
                        if (schemaobj.ObjectType != TDBObjType.ttobjSchema)
                        {
                            continue;
                        }
                        if (schemaobj.ObjectToken.TokenStatus != TTokenStatus.tsAddbyHand)
                        {
                            schemalist.Add(schemaobj);
                        }

                        foreach (TLzDBObject dbobj in schemaobj.ChildDBObjects)
                        {
                            switch (dbobj.ObjectType)
                            {
                                case TDBObjType.ttObjTable:
                                    tablelist.Add(dbobj);
                                    break;
                                case TDBObjType.ttObjView:
                                    viewlist.Add(dbobj);
                                    break;
                                case TDBObjType.ttObjIndex:
                                    indexlist.Add(dbobj);
                                    break;
                                case TDBObjType.ttObjFunction:
                                    functionlist.Add(dbobj);
                                    break;
                                case TDBObjType.ttObjTrigger:
                                    triggerlist.Add(dbobj);
                                    break;
                                case TDBObjType.ttobjSequence:
                                    sequencelist.Add(dbobj);
                                    break;
                                case TDBObjType.ttobjPackage:
                                    packagelist.Add(dbobj);
                                    break;
                                default:
                                    break;

                            }
                        }
                    }
                }
            }

            SortDBObjectList();
        }

        public TLzDBObject AddTokenToDBObjectList(TSourceToken pToken, TLzDBObjectList pList, TDBObjType pType)
        {
            TLzDBObject dbobj;
            dbobj = pList.FindDBObjectByNameAndType(pToken.AsText, pType);
            if (dbobj == null)
            {
                dbobj = new TLzDBObject(null);
                dbobj.ObjectType = pType;
                dbobj.ObjectToken = pToken;
                pList.Add(dbobj);
                if (pToken.TokenStatus != TTokenStatus.tsAddbyHand)
                {
                    dbobj.TokensInScript.Add(pToken);
                    pToken.TokenStatus = TTokenStatus.tsAddedInTokensInScript;
                }
            }
            else
            {
                if ((pToken.TokenStatus != TTokenStatus.tsAddbyHand) &
                    (pToken.TokenStatus != TTokenStatus.tsAddedInTokensInScript))
                {
                    dbobj.TokensInScript.Add(pToken);
                    pToken.TokenStatus = TTokenStatus.tsAddedInTokensInScript;
                }
            }

            return dbobj;
        }

        public TLzDBObject AddDatabaseTokenToServer(TSourceToken pToken)
        {
            TLzDBObject dbobj;

            if (pToken.ParentToken != null)
            {
                dbobj = AddTokenToDBObjectList(pToken.ParentToken, topleveldbobjectlist, TDBObjType.ttobjServer);
            }
            else
            {
                TSourceToken st = new TSourceToken(null);
                st.AsText = "defaultServer";
                st.TokenStatus = TTokenStatus.tsAddbyHand;
                dbobj = AddTokenToDBObjectList(st, topleveldbobjectlist, TDBObjType.ttobjServer);
            }

            return AddTokenToDBObjectList(pToken, dbobj.ChildDBObjects, TDBObjType.ttobjDatabase);
        }

        public TLzDBObject AddSchemaTokenToDatabase(TSourceToken pToken)
        {
            TLzDBObject dbobj;

            if (pToken.ParentToken != null)
            {
                dbobj = AddDatabaseTokenToServer(pToken.ParentToken);
            }
            else
            {
                TSourceToken st = new TSourceToken(null);
                st.AsText = "defaulDatabase";
                st.TokenStatus = TTokenStatus.tsAddbyHand;
                dbobj = AddDatabaseTokenToServer(st);
            }

            return AddTokenToDBObjectList(pToken, dbobj.ChildDBObjects, TDBObjType.ttobjSchema);

        }

        public void AddDBObjectTokenToSchema(TSourceTokenList pList, TDBObjType pType)
        {
            TLzDBObject dbobj, dbobj2;

            foreach (TSourceToken st in pList)
            {
                if (st.ParentToken != null)
                {
                    dbobj = AddSchemaTokenToDatabase(st.ParentToken);
                }
                else
                {
                    TSourceToken st2 = new TSourceToken(null);
                    st2.AsText = "defaultSchema";
                    st2.TokenStatus = TTokenStatus.tsAddbyHand;
                    if (CurrentDatabaseToken != null)
                    {
                        st2.ParentToken = CurrentDatabaseToken;
                    }

                    dbobj = AddSchemaTokenToDatabase(st2);
                }

                dbobj2 = AddTokenToDBObjectList(st, dbobj.ChildDBObjects, pType);

                if (pType == TDBObjType.ttObjTable)
                {
                    //add ref table token to Tokens in script of this table db object
                    foreach (TSourceToken st3 in st.RelatedTokens)
                    {
                        if ((st3.DBObjType == TDBObjType.ttObjTable) || (st3.DBObjType == TDBObjType.ttobjTableCTE))
                        {
                            dbobj2.TokensInScript.Add(st3);
                        }
                    }

                    AddFieldTokenToTable(st, dbobj2);
                }
            }
        }

        public void AddFieldTokenToTable(TSourceToken tabletoken, TLzDBObject tableobj)
        {

            foreach (TSourceToken st in tabletoken.RelatedTokens)
            { //get all ref table token
                if (st.DBObjType == TDBObjType.ttObjTable)
                {
                    if (st.ChildToken != null)
                    { //add field linked to this ref table to declare table object
                        AddTokenToDBObjectList(st.ChildToken, tableobj.ChildDBObjects, TDBObjType.ttObjField);
                    }
                }

                if (st.DBObjType == TDBObjType.ttobjTableCTE)
                {
                    foreach (TSourceToken st2 in st.RelatedTokens)
                    {
                        if (st2.DBObjType == TDBObjType.ttObjField)
                        {
                            AddTokenToDBObjectList(st2, tableobj.ChildDBObjects, TDBObjType.ttObjField);
                        }
                    }
                }
            }

            if (tabletoken.RelatedToken != null)
            {
                //alias token of this table
                TSourceToken tablealiastoken = tabletoken.RelatedToken;

                foreach (TSourceToken st in tablealiastoken.RelatedTokens)
                {
                    if (st.DBObjType == TDBObjType.ttObjTableAlias)
                    {
                        if (st.ChildToken != null)
                        {
                            AddTokenToDBObjectList(st.ChildToken, tableobj.ChildDBObjects, TDBObjType.ttObjField);
                        }
                    }
                }
            }

            foreach (TSourceToken st in tabletoken.RelatedTokens)
            {
                // get all field tokens link with table token (those token not linked by syntax like tablename.fieldname)
                // but like this : select f from t

                if (st.DBObjType == TDBObjType.ttObjField)
                {
                    AddTokenToDBObjectList(st, tableobj.ChildDBObjects, TDBObjType.ttObjField);
                }

            }


        }

        public string TokensOfDBObject(TLzDBObject pObject)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("<tokens num=\"{0}\">\n", pObject.TokensInScript.Count()));
            foreach (TSourceToken st in pObject.TokensInScript)
            {
                sb.Append(string.Format("<token text=\"{0}\" x=\"{1}\" y=\"{2}\" />\n", st.AsText, st.XPosition, st.YPosition - st.AsText.Length));
            }
            sb.Append("\n</tokens>");

            return sb.ToString();
        }

        void analyzeStatement(TCustomSqlStatement stmt)
        {
            if (stmt.SqlStatementType == TSqlStatementType.sstMssqlUse)
            {
                CurrentDatabaseToken = stmt.DatabaseTokens[0];
            }

            if (stmt.SqlStatementType == TSqlStatementType.sstInsert)
            {
                Console.WriteLine("insert");
            }

            //server
            foreach (TSourceToken servertoken in stmt.ServerTokens)
            {
                AddTokenToDBObjectList(servertoken, topleveldbobjectlist, TDBObjType.ttobjServer);
            }

            //database
            foreach (TSourceToken databasetoken in stmt.DatabaseTokens)
            {
                AddDatabaseTokenToServer(databasetoken);
            }

            //schema
            foreach (TSourceToken schematoken in stmt.SchemaTokens)
            {
                AddSchemaTokenToDatabase(schematoken);
            }

            //other db objects such as table,functions
            AddDBObjectTokenToSchema(stmt.TableTokens, TDBObjType.ttObjTable);
            AddDBObjectTokenToSchema(stmt.ViewTokens, TDBObjType.ttObjView);
            AddDBObjectTokenToSchema(stmt.IndexTokens, TDBObjType.ttObjIndex);
            AddDBObjectTokenToSchema(stmt.FunctionTokens, TDBObjType.ttObjFunction);
            AddDBObjectTokenToSchema(stmt.TriggerTokens, TDBObjType.ttObjTrigger);
            AddDBObjectTokenToSchema(stmt.SequenceTokens, TDBObjType.ttobjSequence);
            AddDBObjectTokenToSchema(stmt.PackageTokens, TDBObjType.ttobjPackage);

            for (int i = 0; i < stmt.ChildNodes.Count(); i++)
            {
                if (stmt.ChildNodes[i] is TCustomSqlStatement)
                {
                    analyzeStatement((TCustomSqlStatement)stmt.ChildNodes[i]);
                }
            }

        }

        public void AnalyzeScript()
        {
            if (sqlstatements == null) { return; }
            foreach (TCustomSqlStatement sql in sqlstatements)
            {
                TCustomSqlStatement stmt;
                if (sql is TPlsqlStatement)
                {
                    stmt = sql.Root as TCustomSqlStatement;
                }
                else
                {
                    stmt = sql;
                }

                analyzeStatement(stmt);


            }//for statement

            if (!isSaveXML) { return; }

            XML = "<dbobjectlist>";
            StringBuilder sb = new StringBuilder();

            foreach (TLzDBObject serverobj in topleveldbobjectlist)
            {
                sb.Append(string.Format("<server name=\"{0}\">\n", serverobj.ObjectName));
                if (serverobj.ObjectToken.TokenStatus != TTokenStatus.tsAddbyHand)
                {
                    sb.Append(TokensOfDBObject(serverobj));
                }

                foreach (TLzDBObject databaseobj in serverobj.ChildDBObjects)
                {
                    sb.Append(string.Format("<database name=\"{0}\">\n", databaseobj.ObjectName));
                    if (databaseobj.ObjectToken.TokenStatus != TTokenStatus.tsAddbyHand)
                    {
                        sb.Append(TokensOfDBObject(databaseobj));
                    }

                    foreach (TLzDBObject schemaobj in databaseobj.ChildDBObjects)
                    {
                        sb.Append(string.Format("<schema name=\"{0}\">\n", schemaobj.ObjectName));
                        if (schemaobj.ObjectToken.TokenStatus != TTokenStatus.tsAddbyHand)
                        {
                            sb.Append(TokensOfDBObject(schemaobj));
                        }

                        foreach (TLzDBObject dbobj in schemaobj.ChildDBObjects)
                        {
                            sb.Append(string.Format("<dbobject name=\"{0}\" type=\"{1}\" >\n", dbobj.ObjectName, dbobj.ObjectType.ToString()));
                            if (dbobj.ObjectToken.TokenStatus != TTokenStatus.tsAddbyHand)
                            {
                                sb.Append(TokensOfDBObject(dbobj));
                            }
                            if (dbobj.ObjectType == TDBObjType.ttObjTable)
                            {
                                foreach (TLzDBObject fieldobj in dbobj.ChildDBObjects)
                                {
                                    sb.Append(string.Format("<field name=\"{0}\">\n", fieldobj.ObjectName));
                                    if (fieldobj.ObjectToken.TokenStatus != TTokenStatus.tsAddbyHand)
                                    {
                                        sb.Append(TokensOfDBObject(fieldobj));
                                    }
                                    sb.Append("\n</field>");
                                }
                            }
                            sb.Append("\n</dbobject>");
                        }

                        sb.Append("\n</schema>");
                    }

                    sb.Append("\n</database>");
                }

                sb.Append("\n</server>");
            }

            XML = XML + Environment.NewLine + sb.ToString() + "</dbobjectlist>";
        }
    }

}