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(string branch) {
            BRANCH = branch;
            stop_watch.Start();
            if (CreateConnection()) {
                Main();
            }
            Debug.WriteLine(stop_watch.Elapsed);
        }

        public void Main() {
            string path, txt;
            CheckDirectories();

            GetTables()
                .ForEachAsync( tbl => {
                    path = CreateFileName(TBL_PATH + tbl.Name);
                    txt = TransformSQL(tbl.Script());
                    Task.Run(() => SaveStatement(path, txt));
            });

            GetStoredProcedures()
                .ForEachAsync(usp => {
                    path = CreateFileName(SP_PATH + usp.Name);
                    txt = TransformSQL(usp.Script());
                    Task.Run(() => SaveStatement(path, txt));
                });
        }

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

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

        public string CreateFileName(string path) {
            return string.Format("{0}{1}", path, FILE_TYPE);
        }

        public string TransformSQL(StringCollection collection) {
            var strings = collection.Cast<string>().ToList();
            return string.Join("", strings);
        }

        public async void SaveStatement(string path, string txt) {
            byte[] encodedText = Encoding.Unicode.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);
            };
        }

        public void CheckDirectories() {
            SP_PATH = string.Format("{0}\\{1}\\{2}\\", ROOT, BRANCH, USP);
            TBL_PATH = string.Format("{0}\\{1}\\{2}\\", ROOT, BRANCH, TBL);
            var dirs = new[] { SP_PATH, TBL_PATH };

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

        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];
                return true;
            }
            catch (Exception e) {
                Debug.WriteLine(e.ToString());
                return false;
            }

        }

        #region SetUpVars
            Stopwatch stop_watch = new Stopwatch();
            private ServerConnection conn;
            private Server svr;
            private Database db;
            const string ROOT = @"\\g3mesacc\d$\utils\Scripts\";

            //going to have to figure out a good way to
            //to handle db changes
            //▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
            string SERVER_NAME = "server_name",
                   USER = "user",
                   PWD = "pwd",
                   DB = "db_bane",
                   SP_SYS_OBJ = "IsSystemObject",
                   DATE = DateTime.Today.ToString("MMddyyyy"),
                   TBL = "tbl",
                   USP = "usp",
                   FILE_TYPE = ".sql",
                   BRANCH,
                   SP_PATH,
                   TBL_PATH;
        #endregion
    }

}