using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataLibrary;
using System.Configuration;
using System.IO;

namespace PLImporter
{
    class Program
    {
        private static Sta ta;
        private static string connectionString;

        static void Main(string[] args)
        {
            Console.WriteLine("Do you alredy config app.config file ? (y/n)");
            string readLine = Console.ReadLine();
            if (readLine.ToLower() == "y")
            {
                connectionString = ConfigurationManager.AppSettings["ConnectionString"];
                ta = new Sta(connectionString);
                try
                {
                    BeginImport();
                    Console.WriteLine("SUCCESSFUL ....................................");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("!!Error > " + ex.Message);
                }
                ta.Close();
            }
            else
            {
                Console.WriteLine("Good bye ..........");
            }
        }

        private static void BeginImport()
        {
            // รับค่า config
            bool dropFunction = ConfigurationManager.AppSettings["Drop_all_Function_before_import"].ToLower() == "true";
            bool dropPackage = ConfigurationManager.AppSettings["Drop_all_Package_before_import"].ToLower() == "true";
            bool dropTypes = ConfigurationManager.AppSettings["Drop_all_Type_before_import"].ToLower() == "true";
            bool debugMode = ConfigurationManager.AppSettings["Debug_mode"].ToLower() == "true";
            string plPath = ConfigurationManager.AppSettings["PLSQL_Path"];
            string schema = connectionString.Substring(connectionString.ToLower().IndexOf("user id=") + 8);
            schema = schema.Substring(0, schema.IndexOf(";"));
            schema = schema.ToUpper();

            // ทำการ drop Types ตามค่า config
            if (dropTypes)
            {
                string sql = "select type_name from ALL_TYPES where owner = '" + schema + "'";
                Sdt dt = ta.Query(sql);
                while (dt.Next())
                {
                    string sqlDrop = "DROP TYPE " + dt.GetString("type_name") + " FORCE";
                    Console.WriteLine("SQL>" + sqlDrop);
                    ta.Exe(sqlDrop);
                }
            }

            // ทำการ import Types
            DirectoryInfo dirTypes = new DirectoryInfo(plPath + "\\Types");
            FileInfo[] fTypes = dirTypes.GetFiles("*.pls");
            for (int i = (fTypes.Length - 1); i >= 0; i--)
            {
                string typesName = fTypes[i].Name;
                string sqlTypeFull = File.ReadAllText(fTypes[i].FullName, Encoding.GetEncoding("tis-620"));
                int ii = ta.Exe(sqlTypeFull);
                Console.WriteLine("Import TYPE>" + typesName);
            }

            // ทำการ drop Package ตามค่า config
            if (dropPackage)
            {
                string sql = "select distinct object_name from dba_procedures where owner = '" + schema + "' and object_type = 'PACKAGE' order by object_name";
                Sdt dt = ta.Query(sql);
                while (dt.Next())
                {
                    string sqlDrop = "DROP PACKAGE " + dt.GetString("object_name") + "";
                    Console.WriteLine("SQL>" + sqlDrop);
                    ta.Exe(sqlDrop);
                }
            }


            // ทำการ import Package
            DirectoryInfo dirPackage = new DirectoryInfo(plPath + "\\Packages");
            FileInfo[] fPackage = dirPackage.GetFiles("*.pls");
            for (int i = (fPackage.Length - 1); i >= 0; i--)
            {
                string packagesName = fPackage[i].Name;
                string sqlPackageFull = File.ReadAllText(fPackage[i].FullName, Encoding.GetEncoding("tis-620"));
                int ii = ta.Exe(sqlPackageFull);
                Console.WriteLine("Import PACKAGE>" + packagesName);
            }

            // ทำการ drop FUNCTION ตามค่า config
            if (dropFunction)
            {
                string sql = "select distinct object_name from dba_procedures where owner = '" + schema + "' and object_type = 'FUNCTION' order by object_name";
                Sdt dt = ta.Query(sql);
                while (dt.Next())
                {
                    string sqlDrop = "DROP FUNCTION " + dt.GetString("object_name") + "";
                    Console.WriteLine("SQL>" + sqlDrop);
                    ta.Exe(sqlDrop);
                }
            }

            // ทำการ import FUNCTION
            DirectoryInfo dirFunction = new DirectoryInfo(plPath + "\\Functions");
            FileInfo[] fFunction = dirFunction.GetFiles("*.pls");
            for (int i = (fFunction.Length - 1); i >= 0; i--)
            {
                string functionsName = fFunction[i].Name;
                string sqlFunctionFull = File.ReadAllText(fFunction[i].FullName, Encoding.GetEncoding("tis-620"));
                int ii = ta.Exe(sqlFunctionFull);
                Console.WriteLine("Import FUNCTION>" + functionsName);
            }

            //เขียนคำสั่ง compile อีกรอบเพื่อความถูกต้อง
            string compileType = @"
begin FOR cur IN (
			      SELECT OBJECT_NAME, OBJECT_TYPE, owner, 
			        (select count(1) from all_dependencies ad
			         where ad.owner = ao.owner and name = OBJECT_NAME
			        ) priority /* all_dependencies is transitive, therefore using simpler aggregate query instead of hierarchical*/			       
			      FROM all_objects ao
			      WHERE object_type in ('TYPE','TYPE BODY') and owner = '" + schema + @"' 
			   order by priority
			  ) LOOP 
BEGIN
  if cur.OBJECT_TYPE = 'TYPE BODY' then 
    EXECUTE IMMEDIATE 'alter type ' || cur.owner || '.' || cur.OBJECT_NAME || ' compile " + (debugMode ? "debug" : "") + @" body'; 
  else 
    EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' ' || cur.owner || '.' || cur.OBJECT_NAME || ' compile " + (debugMode ? "debug" : "") + @"'; 
  end if; 
EXCEPTION
  WHEN OTHERS THEN NULL; 
END;
end loop; end;";
            string compilePackage = @"
begin FOR cur IN (SELECT OBJECT_NAME, OBJECT_TYPE, owner FROM all_objects WHERE object_type in ('PACKAGE','PACKAGE BODY') and owner = '" + schema + @"' ) LOOP 
BEGIN
  if cur.OBJECT_TYPE = 'PACKAGE BODY' then 
    " + "EXECUTE IMMEDIATE 'alter package \"' || cur.owner || '\".\"' || cur.OBJECT_NAME || '\" compile " + (debugMode ? "debug" : "") + @" body'; " + @"
  else 
    " + "EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' \"' || cur.owner || '\".\"' || cur.OBJECT_NAME || '\" compile " + (debugMode ? "debug" : "") + @"'; " + @"
  end if; 
EXCEPTION
  WHEN OTHERS THEN NULL; 
END;
end loop; end;";

            string compileFunction = @"
begin FOR cur IN (SELECT OBJECT_NAME, OBJECT_TYPE, owner FROM all_objects WHERE object_type in ('FUNCTION') and owner = '" + schema + @"' ) LOOP 
BEGIN
  if cur.OBJECT_TYPE = 'PACKAGE BODY' then 
    " + "EXECUTE IMMEDIATE 'alter package \"' || cur.owner || '\".\"' || cur.OBJECT_NAME || '\" compile " + (debugMode ? "debug" : "") + @" body'; " + @"
  else 
    " + "EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' \"' || cur.owner || '\".\"' || cur.OBJECT_NAME || '\" compile " + (debugMode ? "debug" : "") + @"'; " + @"
  end if; 
EXCEPTION
  WHEN OTHERS THEN NULL; 
END;
end loop; end;";

            //Compile ใหม่อีกรอบ
            int iii = 0;
            iii = ta.Exe(compileType);
            Console.WriteLine("COMPILE TYPES " + (debugMode ? "with debug" : "") + @"...");
            iii = ta.Exe(compilePackage);
            Console.WriteLine("COMPILE PACKAGES " + (debugMode ? "with debug" : "") + @"...");
            iii = ta.Exe(compileFunction);
            Console.WriteLine("COMPILE FUNCTION " + (debugMode ? "with debug" : "") + @"...");

            Console.WriteLine();

            Console.WriteLine("Press any key ....");

            Console.ReadLine();
        }
    }
}