using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.IO;
using Jhu.Graywulf.Schema;

using edu.jhu.Cas.Jobs;
using CasJobsWeb;
using edu.jhu.Cas.Users;
using edu.jhu.Cas.Servers;
using System.Web.SessionState;
using edu.jhu.Cas.Web;
using Jhu.Graywulf.Schema.SqlServer;
using System.Data.SqlClient;

namespace CasJobsWeb.schema
{
    public partial class Default : System.Web.UI.Page
    {
        private string path = @"C:\Users\Public\Documents\cjw_t\CasJobsWeb\CasJobsWeb\schema\log.txt";
        public static string GetUrl()
        {
            return "~/schema/Default.aspx";
        }

        public static string GetUrl(string objid)
        {
            return String.Format("~/Schema/Default.aspx?objid={0}", objid);
        }

        protected Jhu.Graywulf.Schema.SchemaManager schemaManager;

        public SchemaManager sm{

            get {
                if (schemaManager != null)
                    return schemaManager;
                else
                    return SchemaManager((long)Session["USER"]);
            }
        }


        public SchemaManager SchemaManager(long wsid)
        {
                
                if (schemaManager == null)
                {
                    schemaManager = new SchemaManager(); //(RegistryContext, Jhu.Graywulf.Registry.Federation.AppSettings.FederationName);

                    // Load datasets from the federation
                    //schemaManager.Datasets.LoadAll();

                    CasServers servf = new CasServers();
                    edu.jhu.Cas.Util.Server[] servs = servf.FindServers(edu.jhu.Cas.Util.ID.SERVICE_ID, wsid);

                    foreach (edu.jhu.Cas.Util.Server x in servs)
                    {

                        if (x.CStringExtra != null)
                        {
                            if (x.CStringExtra.ToUpper().IndexOf("MYSQL") == -1 && x.Target.ToUpper() != "MYDB")
                            {
                                //Add databases
                                var tmp_s = new CasJobsDataset();
                                tmp_s.ConnectionString = x.ConnectionString;
                                //tmp_s.Name = x.Name; //#dmedv
                                tmp_s.Name = x.Target; //#dmedv
                                tmp_s.DefaultSchemaName = "dbo";
                                tmp_s.IsMutable = true;
                                if (x.Name.ToUpper().IndexOf("MYDB") == -1)
                                    tmp_s.IsCacheable = true;

                                schemaManager.Datasets[tmp_s.Name] = tmp_s;
                            }
                        }
                        else
                        {
                            //Add databases
                            var tmp_s = new CasJobsDataset();
                            tmp_s.ConnectionString = x.ConnectionString;
                            //tmp_s.Name = x.Name; //#dmedv
                            tmp_s.Name = x.Target; //#dmedv
                            tmp_s.DefaultSchemaName = "dbo";
                            tmp_s.IsMutable = true;
                            if (x.Name.ToUpper().IndexOf("MYDB") == -1)
                                tmp_s.IsCacheable = true;

                            schemaManager.Datasets[tmp_s.Name] = tmp_s;
                        }
                    }

                    //Add myDB
                    CasUsers cUsers = new CasUsers();
                   
                    edu.jhu.Cas.Util.Server mydbServer = cUsers.MyDBServerObject(wsid);

                    var mydbds = new CasJobsDataset();
                    mydbds.ConnectionString = mydbServer.ConnectionString;
                    mydbds.Name = mydbServer.Name;
                    mydbds.DefaultSchemaName = "dbo";
                    mydbds.IsMutable = true;
                   

                    schemaManager.Datasets[mydbds.Name] = mydbds;
                }

                return schemaManager;
        }
        

        protected string SelectedSchemaObject
        {
            get { return (string)Session["SchemaSelectedObject"]; }
            set { Session["SchemaSelectedObject"] = value; }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            WebUtils.CheckLoggedIn(Session, Response, Request); //#dmedv
            if (!IsPostBack)
            {
                //WebUtils.CheckLoggedIn(Session, Response, Request); //#dmedv
                bool loggedin = Session["USER"] != null;
                //if (loggedin) wsid = (long)Session["USER"];
                schemaManager = SchemaManager((long)Session["USER"]);

                DatabaseObject dbobj = null;

                var dbobjid = (string)Request.QueryString["objid"] ?? (string)Session["SchemaSelectedObject"];
                if (dbobjid != null)
                {

                    try
                    {
                        dbobj = sm.GetDatabaseObjectByKey(dbobjid);
                    }
                    catch
                    {

                    }
                }

                if (dbobj != null)
                {
                    RefreshDatasetList();
                    //DatasetList.SelectedValue = dbobj.DatasetName; //#dmedv
                    DatasetList.SelectedValue = dbobj.DatabaseName; //#dmedv

                    RefreshObjectTypeList();
                    ObjectTypeList.SelectedValue = dbobj.ObjectType.ToString();

                    RefreshObjectList();
                    ObjectList.SelectedValue = dbobj.UniqueKey;

                    ShowDetails(dbobjid);
                }
                else
                {
                    RefreshDatasetList();
                    RefreshObjectTypeList();
                    RefreshObjectList();
                }
            }
        }

        private Dictionary<string, string> DBName=new Dictionary<string, string>();
        private void RefreshDatasetList()
        {
            DatasetList.Items.Clear();
            Hashtable tList = null;
            tList = new Hashtable();
            DBName.Clear();
            // Add MyDB as the first item

            /*var mydbli = new ListItem(MyDBDatabaseDefinition.Name, MyDBDatabaseDefinition.Name);
            mydbli.Attributes.CssStyle.Add("font-weight", "bold");
            DatasetList.Items.Add(mydbli);*/

            // Add other registered catalogs            

            sm.Datasets.LoadAll();
            foreach (var dsd in sm.Datasets.Values.OrderBy(k => k.Name))
            {
                if (tList.Contains(dsd.DatabaseName))
                {
                    ((ArrayList)tList[dsd.DatabaseName]).Add(dsd);
                }
                else
                {
                    ArrayList al = new ArrayList();
                    al.Add(dsd);
                    tList.Add(dsd.DatabaseName,al);
                    if (dsd.DatabaseName.Contains("MYDB"))
                    {
                        DatasetList.Items.Add("MYDB");
                        DBName.Add("MYDB", dsd.Name);
                    }
                    else
                    {
                        //DatasetList.Items.Add(dsd.DatabaseName); //#dmedv
                        DatasetList.Items.Add(new ListItem(dsd.Name, dsd.DatabaseName)); //#dmedv
                        DBName.Add(dsd.DatabaseName, dsd.Name);
                    }
                }
            }
        }

        private void RefreshObjectTypeList()
        {
            // Nothing to do here at the moment, only tables and views are listed
        }

        private void RefreshObjectList()
        {
            ObjectList.Items.Clear();

            if (!DBName.ContainsKey("MYDB") && DatasetList.SelectedValue == "MYDB")
            {
                foreach (var dsd in sm.Datasets.Values.OrderBy(k => k.Name))
                {
                    if (dsd.DatabaseName.Contains("MYDB"))
                    {
                        DBName.Add("MYDB", dsd.Name);
                        break;
                    }
                }
            }
            else
            {
                if (!DBName.ContainsKey(DatasetList.SelectedValue))
                {
                    foreach (var dsd in sm.Datasets.Values.OrderBy(k => k.Name))
                    {
                        if (dsd.DatabaseName == DatasetList.SelectedValue)
                        {
                            DBName.Add(dsd.DatabaseName, dsd.Name);
                            break;
                        }
                    }
                }
            }

            var dataset = sm.Datasets[DBName[DatasetList.SelectedValue]];

            DatabaseObjectType type;
            if (Enum.TryParse<DatabaseObjectType>(ObjectTypeList.SelectedValue, out type))
            {
                try
                {
                    var li = new ListItem("(select item)", "");
                    ObjectList.Items.Add(li);

                    switch (type)
                    {
                        case DatabaseObjectType.Table:
                            LoadTables(dataset);
                            break;
                        case DatabaseObjectType.View:
                            LoadViews(dataset);
                            break;
                        case DatabaseObjectType.TableValuedFunction:
                            LoadTableValuedFunctions(dataset);
                            break;
                        case DatabaseObjectType.ScalarFunction:
                            LoadScalarFunctions(dataset);
                            break;
                        case DatabaseObjectType.StoredProcedure:
                            LoadStoredProcedures(dataset);
                            break;
                        default:
                            throw new NotImplementedException();
                    }
                }
                catch (SqlException ex)
                {
                    ObjectList.Items.Clear();
                    var li = new ListItem(ex.Message, "");
                    ObjectList.Items.Add(li);
                }
            }
            else
            {
                var li = new ListItem("(no items)", "");
                ObjectList.Items.Add(li);
            }
        }

        protected void LoadTables(DatasetBase dataset)
        {
            dataset.Tables.LoadAll();
            LoadDatabaseObjects(dataset.Tables.Values);
        }

        protected void LoadViews(DatasetBase dataset)
        {
            dataset.Views.LoadAll();
            LoadDatabaseObjects(dataset.Views.Values);
        }

        protected void LoadTableValuedFunctions(DatasetBase dataset)
        {

            dataset.TableValuedFunctions.LoadAll();
            LoadDatabaseObjects(dataset.TableValuedFunctions.Values);
        }

        protected void LoadScalarFunctions(DatasetBase dataset)
        {
            dataset.ScalarFunctions.LoadAll();
            LoadDatabaseObjects(dataset.ScalarFunctions.Values);
        }

        protected void LoadStoredProcedures(DatasetBase dataset)
        {
            dataset.StoredProcedures.LoadAll();
            LoadDatabaseObjects(dataset.StoredProcedures.Values);
        }

        protected void LoadDatabaseObjects(IEnumerable<DatabaseObject> objects)
        {
            foreach (var d in objects.OrderBy(f => f.DisplayName))
            {
                var li = new ListItem(d.DisplayName, d.UniqueKey);
                ObjectList.Items.Add(li);
            }
        }

        private void HideAllPanels()
        {
            //IntroForm.Visible = false;
            //DetailsPanel.Visible = false;
        }

        private void ShowDetails(string dbobjid)
        {
            HideAllPanels();

            DatabaseObject dbobj = null;

            try
            {
                dbobj = sm.GetDatabaseObjectByKey(dbobjid);
            }
            catch
            {
            }

           

            if (dbobj != null)
            {
                // Display details
                DetailsPanel.DatabaseObjectID = dbobjid;
                DetailsPanel.Visible = true;

                SelectedSchemaObject = dbobjid;
            }
            
        }

        //--

        protected void DatasetList_SelectedIndexChanged(object sender, EventArgs e)
        {
            RefreshObjectTypeList();
            RefreshObjectList();
        }

        protected void ObjectTypeList_SelectedIndexChanged(object sender, EventArgs e)
        {
            RefreshObjectList();
        }

        protected void ObjectList_SelectedIndexChanged(object sender, EventArgs e)
        {
            ShowDetails(ObjectList.SelectedValue);
        }
    }
}