using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;

namespace findVariables
{
    class findVariables
    {
        static int select_count = 0, update_count = 0, insert_count = 0;
        static List<string> variables = new List<string>();

        static void Main(string[] args)
        {
            int c = Environment.TickCount;

            if (args.Length == 0)
            {
                Console.WriteLine("Usage: {0} scriptfile", Environment.GetCommandLineArgs()[0]);
                return;
            }


            TDbVendor db = TDbVendor.DbVMssql; //DbVDB2;

            TGSqlParser sqlparser = new TGSqlParser(db);
            sqlparser.Sqlfilename = args[0];
            switch(db){
                
               case TDbVendor.DbVDB2:
                   db2Variable(sqlparser);
                    break;
                default:
                    sqlServerVariable(sqlparser);
                    break;
            }



        } //main

        static void db2Variable(TGSqlParser sqlparser)
        {
            int iRet = sqlparser.Parse();

            if (iRet != 0)
            {
                Console.WriteLine(sqlparser.ErrorMessages);
                return;
            }


            
            if (sqlparser.SqlStatements[0].SqlStatementType != TSqlStatementType.sstDb2CreateProcedure)
            {
                Console.WriteLine("Only process variables in db2 create procedure");
                return;
            }

            TDb2CreateProcedure stmt = (TDb2CreateProcedure)sqlparser.SqlStatements[0];

            Console.WriteLine("procedure name: {0}\n", ((_TDb2CreateProcedure)(stmt.Root))._ndProcedureName.AsText);

            for (int i = 0; i < stmt.SqlStatements.Count(); i++)
            {
                TCustomSqlStatement sql = stmt.SqlStatements[i];
                switch (sql.SqlStatementType)
                {
                    case TSqlStatementType.sstDb2SqlVariableDeclaration:
                        _TDb2SqlVariableDeclaration lcStmt = (_TDb2SqlVariableDeclaration)sql.fcRoot;
                        for (int j = 0; j < lcStmt._lstNames.Count(); j++)
                        {
                            TSourceToken st = (TSourceToken)lcStmt._lstNames[j];
                            variables.Add(st.AsText);
                            Console.WriteLine("Variable: {0,-20}, Type: {1}", st.AsText, lcStmt._ndTypename.AsText);
                        }
                        break;
                    default:

                        searchVariable(sql);
                        
                        // Console.WriteLine("Not analyzed yet: {0}",sql.SqlStatementType);
                        break;
                }
            }

            //Console.WriteLine("\nselect {0}, insert {1}, update {2}",select_count,insert_count,update_count++);
        }

        static bool variableInField(string v, TLzField field)
        {
            bool ret = false;
            for (int i = field.StartToken.posinlist; i <= field.EndToken.posinlist; i++)
            {
                TSourceToken st = field.StartToken.Container[i];
                ret = v.Equals(st.AsText, StringComparison.OrdinalIgnoreCase);
                if (ret) break;
            }
            return ret;
        }

        static void searchVariable(TCustomSqlStatement pstmt)
        {
           // Console.WriteLine("sql type {0}",pstmt.SqlStatementType);
            
            switch(pstmt.SqlStatementType){
                case TSqlStatementType.sstSelect:
                    select_count++;
                    break;
                case TSqlStatementType.sstInsert:
                    insert_count++;
                    // is variable in values list?
                    TInsertSqlStatement insert = (TInsertSqlStatement)pstmt;
                    for (int i = 0; i < insert.MultiValues.Count(); i++)
                    {
                        TLzFieldList fields = (TLzFieldList)insert.MultiValues[i];
                        for (int j = 0; j < fields.Count(); j++)
                        {
                            TLzField field = fields[j];
                            for (int k = 0; k < variables.Count; k++)
                            {
                                if (variableInField(variables[k], field))
                                {
                                    string columnName = "";
                                    if (insert.Fields.Count() > j)
                                    {
                                        columnName = insert.Fields[j].AsText;
                                    }
                                    Console.WriteLine("\nvariable: {0}\ninsert table: {3,10}\ncolumn: {2}\ninsert value: {1}", variables[k], field.AsText, columnName, insert.Table.AsText);
                                }
                            }
                        }
                    }
                    break;
                case TSqlStatementType.sstUpdate:
                    TUpdateSqlStatement update = (TUpdateSqlStatement)pstmt;
                    foreach (TLzField field in update.Fields)
                    {
                        for (int k = 0; k < variables.Count; k++)
                        {
                            if (variableInField(variables[k], field))
                            {
                                Console.WriteLine("\nvariable: {1}\nupdate table: {0,10}\ncolumn: {2}\nvalue: {3}", update.Table.AsText, variables[k], field.FieldExpr.lexpr.AsText, field.FieldExpr.rexpr.AsText);
                            }
                        }
                       
                    }
                    update_count++;
                    break;
                default:
                    break;
            }

            for (int j = 0; j < pstmt.ChildNodes.Count(); j++)
            {
                if (pstmt.ChildNodes[j] is TCustomSqlStatement)
                {
                    searchVariable((TCustomSqlStatement)(pstmt.ChildNodes[j]));
                }
            }

        }


        static void sqlServerVariable(TGSqlParser sqlparser)
        {
            int iRet = sqlparser.GetRawSqlStatements();// Parse();

            if (iRet == 0)
            {
                if (sqlparser.SourceTokenList.Count() > 0)
                {
                    foreach (TSourceToken st in sqlparser.SourceTokenList)
                    {
                        if (st.TokenType == TTokenType.ttSqlVar)
                        {
                            Console.WriteLine("{0}", st.AsText);
                        }
                    }
                }
                else
                {
                    Console.WriteLine("no source token was found");
                }
            }
            else
            {
                Console.WriteLine("Syntax error found in input sql:");
                Console.WriteLine(sqlparser.ErrorMessages);

            }
        }

    } //class
}