using System;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using System.IO;
using System.Diagnostics;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Reactive.Linq;
using System.Threading.Tasks;

namespace SQLScripter {
    class Scripter {

        public Scripter(Repo repo) {
            PATH = repo.path;
            BRANCH = repo.branch;
            SERVER_NAME = repo.server;
            USER = repo.user;
            PWD = repo.pwd;
            DB = repo.db;
            TBL_PREFIX = repo.tbl;
        }

        public void Main() {
            string path,
                   txt;

            /* time how long it takes */
            stop_watch.Start();


            // Make sure save directories are there
            CheckDirectories();

            Console.WriteLine("bee bop, Tables");
            Console.WriteLine("This takes like 15sec, hold on sweetie");

            // emits a table object on change
            GetTables()
                .ForEachAsync( tbl => {
                    path = CreateFilePath(TBL_PATH + tbl.Name);
                    txt = TransformSQL(tbl.Script());
                    Task.Run(() => SaveStatement(path, txt));
            });

            Console.WriteLine(stop_watch.Elapsed);
            Console.WriteLine("bee bop, Stored Procs and Views");
            Console.WriteLine("This takes like 15sec, chill, we're almost done");

            // emits a view object on change
            GetViews()
                .ForEachAsync(vw => {
                    path = CreateFilePath(VW_PATH + vw.Name);
                    txt = TransformSQL(vw.Script());
                    Task.Run(() => SaveStatement(path, txt));
                });

            // emits a stored procedure object on change
            GetStoredProcedures()
                .ForEachAsync(usp => {
                    path = CreateFilePath(SP_PATH + usp.Name);
                    txt = TransformSQL(usp.Script());
                    Task.Run(() => SaveStatement(path, txt));
                });

            Console.WriteLine(stop_watch.Elapsed);
        }

        public IObservable<Table> GetTables() {
            return db.Tables
                     .OfType<Table>()
                     .Where(tbl => tbl.Name.StartsWith(TBL_PREFIX))
                     .ToObservable();
        }

        public IObservable<View> GetViews() {
            return db.Views
                     .OfType<View>()
                     .Where(vw => !vw.IsSystemObject)
                     .ToObservable();
        }

        public IObservable<StoredProcedure> GetStoredProcedures() {
            return db.StoredProcedures
                     .OfType<StoredProcedure>()
                     .Where(sp => !sp.IsSystemObject)
                     .ToObservable();
        }

        /* makes file name */
        public string CreateFilePath(string path) {
            return string.Format("{0}{1}", path, FILE_TYPE);
        }

        /* gets actual sql command out of object */
        public string TransformSQL(StringCollection collection) {
            return string.Join("", collection.Cast<string>().ToList());
        }

        /* saves the sql string to a file */
        public async void SaveStatement(string path, string txt) {
            byte[] encodedText = Encoding.UTF8.GetBytes(txt);
            using (FileStream sourceStream = new FileStream(path,
                FileMode.Create, FileAccess.Write, FileShare.None,
                bufferSize: 4096, useAsync: true)) {
                await sourceStream.WriteAsync(encodedText, 0, encodedText.Length);
            };
        }

        /* if a directory is not there, it makes it */
        public void CheckDirectories() {
            SP_PATH = string.Format("{0}\\{1}\\", PATH, USP);
            TBL_PATH = string.Format("{0}\\{1}\\", PATH, TBL);
            VW_PATH = string.Format("{0}\\{1}\\", PATH, VW);
            var dirs = new[] { SP_PATH, TBL_PATH, VW_PATH };

            dirs.Where(dir => !Directory.Exists(dir))
                .ToList()
                .ForEach(dir=> Directory.CreateDirectory(dir));
        }

        /* tries to connect to server/db */
        public bool CreateConnection() {
            try {
                conn = new ServerConnection(SERVER_NAME);
                conn.LoginSecure = false;
                conn.Login = this.USER;
                conn.Password = this.PWD;
                conn.MultipleActiveResultSets = true;
                svr = new Server(conn);
                svr.SetDefaultInitFields(typeof(StoredProcedure), this.SP_SYS_OBJ);
                svr.SetDefaultInitFields(typeof(Table), true);
                db = svr.Databases[this.DB];
            }
            catch (Exception e) {
                Debug.WriteLine(e.ToString());
                return false;
            }

            return true;
        }

        #region SetUpVars
            Stopwatch stop_watch = new Stopwatch();
            private ServerConnection conn;
            private Server svr;
            private Database db;

            string DATE = DateTime.Today.ToString("MMddyyyy"),
                   SP_SYS_OBJ = "IsSystemObject",
                   TBL = "tbl",
                   USP = "usp",
                   VW = "vw",
                   FILE_TYPE = ".sql",
                   BRANCH,
                   PATH,
                   SP_PATH,
                   TBL_PATH,
                   VW_PATH,
                   SERVER_NAME,
                   TBL_PREFIX,
                   USER,
                   PWD,
                   DB;
        #endregion
    }

}