using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Timers;
using System.IO;
using System.Data.SqlClient;
using static BackupService.BackupService;
using System.Text;
using System.Data;
using System.IO.Compression;

namespace BackupService
{
    public class BackupWorker
    {
        private string dataPath = null;
        private Timer timer = null;
        private string fromTable = null;

        public BackupWorker()
        {
            timer = new Timer();
            timer.AutoReset = false;
            timer.Interval = 1000;
            timer.Enabled = false;
            timer.Elapsed += new ElapsedEventHandler(DoWork);
        }

        public string DataPath
        {
            get { return dataPath; }
            set { dataPath = value; }
        }

        public void Begin(string fromTable = null) 
        {
            this.fromTable = fromTable;
            timer.Start();
        }

        public void BackupOneTable(string tableName)
        {
            var nowtime = DateTime.UtcNow.AddHours(8);

            try
            {
                using (var conn = new SqlConnection("Data Source=sql5060.site4now.net;Initial Catalog=DB_A48A46_thinkyu;Persist Security Info=True;User ID=DB_A48A46_thinkyu_admin;Password=VUPm4ej03xu3"))
                {
                    conn.Open();
                    var cmd = new SqlCommand();
                    var da = new SqlDataAdapter();
                    cmd.Connection = conn;
                    cmd.CommandText = @"SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME, Count(sys.identity_columns.Column_ID) as HAS_IDENTITY 
                        FROM INFORMATION_SCHEMA.TABLES
                            LEFT JOIN sys.identity_columns on OBJECT_NAME(object_id) = INFORMATION_SCHEMA.TABLES.TABLE_NAME
                        WHERE TABLE_TYPE <> 'VIEW' and INFORMATION_SCHEMA.TABLES.TABLE_NAME = @TABLE_NAME
                        GROUP BY INFORMATION_SCHEMA.TABLES.TABLE_NAME";
                    cmd.Parameters.AddWithValue("TABLE_NAME", tableName);

                    var tableList = new List<TableItem>();

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            tableList.Add(new TableItem() { TableName = Convert.ToString(reader["TABLE_NAME"]), HasIdentity = Convert.ToInt32(reader["HAS_IDENTITY"]) == 1 });
                        }
                    }

                    foreach (var table in tableList)
                    {
                        var output = new StringBuilder();
                        var columnnames = new List<string>();
                        cmd.CommandText = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@TABLE_NAME order by ORDINAL_POSITION ";
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("TABLE_NAME", table.TableName);
                        da.SelectCommand = cmd;
                        var columns = new DataTable();
                        da.Fill(columns);
                        foreach (DataRow row in columns.Rows)
                        {
                            columnnames.Add(string.Format("[{0}]", row["COLUMN_NAME"]));
                        }
                        var columnlist = string.Join(", ", columnnames.ToArray());

                        output.AppendFormat("-- BACKUP DATE {0:yyyy/MM/dd HH:mm}\r\n", nowtime);
                        output.AppendFormat("-- TABLE [{0}]\r\n", table.TableName);

                        if (table.HasIdentity)
                            output.AppendFormat("set identity_insert [{0}] on;\r\n", table.TableName);

                        cmd.CommandText = string.Format("select * from [{0}]", table.TableName);
                        cmd.CreateParameter();
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var sql = string.Format("insert into [{0}] ({1}) values (", table.TableName, columnlist);
                                var values = new List<string>();
                                foreach (DataRow col in columns.Rows)
                                {
                                    var colname = Convert.ToString(col["COLUMN_NAME"]);
                                    var datatype = Convert.ToString(col["DATA_TYPE"]).ToLower();
                                    var value = reader[colname];

                                    if (value == DBNull.Value)
                                        values.Add("null");
                                    else if (datatype == "char" || datatype == "varchar")
                                        values.Add(string.Format("'{0}'", value));
                                    else if (datatype == "nvarchar" || datatype == "nchar")
                                        values.Add(string.Format("N'{0}'", value));
                                    else if (datatype == "nvarchar" || datatype == "datetime")
                                        values.Add(string.Format("'{0:yyyy/MM/dd HH:mm:ss.fff}'", value));
                                    else if (datatype == "bit")
                                        values.Add(string.Format("{0}", value));
                                    else
                                        values.Add(string.Format("{0}", value));
                                }
                                sql += string.Join(", ", values.ToArray()) + ");";
                                output.AppendLine(sql);
                            }
                        }

                        if (table.HasIdentity)
                            output.AppendFormat("set identity_insert [{0}] off;\r\n", table.TableName);

                        var buffer = Encoding.UTF8.GetBytes(output.ToString());

                        using (FileStream stream = new FileStream(string.Format("{0}/{1}.gz", dataPath, table.TableName), FileMode.Create, FileAccess.Write))
                        {
                            using (GZipStream gZip = new GZipStream(stream, CompressionMode.Compress))
                            {
                                gZip.Write(buffer, 0, buffer.Length);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorLogger.WriteException(ex);
            }
        }

        private void DoWork(object sender, ElapsedEventArgs e)
        {
            var nowtime = DateTime.UtcNow.AddHours(8);

            try
            {
                using (var conn = new SqlConnection("Data Source=sql5060.site4now.net;Initial Catalog=DB_A48A46_thinkyu;Persist Security Info=True;User ID=DB_A48A46_thinkyu_admin;Password=VUPm4ej03xu3"))
                {
                    conn.Open();
                    var cmd = new SqlCommand();
                    var da = new SqlDataAdapter();
                    cmd.Connection = conn;
                    cmd.CommandText = @"SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME, Count(sys.identity_columns.Column_ID) as HAS_IDENTITY 
                        FROM INFORMATION_SCHEMA.TABLES
                            LEFT JOIN sys.identity_columns on OBJECT_NAME(object_id) = INFORMATION_SCHEMA.TABLES.TABLE_NAME
                        WHERE TABLE_TYPE <> 'VIEW'
                        GROUP BY INFORMATION_SCHEMA.TABLES.TABLE_NAME";

                    var tableList = new List<TableItem>();

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string tableName = Convert.ToString(reader["TABLE_NAME"]);
                            bool hasIdentity = Convert.ToInt32(reader["HAS_IDENTITY"]) == 1;

                            if (string.IsNullOrEmpty(this.fromTable) || tableName.CompareTo(this.fromTable) >= 0) {
                                tableList.Add(new TableItem() { TableName = tableName, HasIdentity = hasIdentity });
                            }
                        }
                    }

                    foreach (var table in tableList)
                    {
                        var output = new StringBuilder();
                        var columnnames = new List<string>();
                        cmd.CommandText = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@TABLE_NAME order by ORDINAL_POSITION ";
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("TABLE_NAME", table.TableName);
                        da.SelectCommand = cmd;
                        var columns = new DataTable();
                        da.Fill(columns);
                        foreach (DataRow row in columns.Rows)
                        {
                            columnnames.Add(string.Format("[{0}]", row["COLUMN_NAME"]));
                        }
                        var columnlist = string.Join(", ", columnnames.ToArray());

                        output.AppendFormat("-- BACKUP DATE {0:yyyy/MM/dd HH:mm}\r\n", nowtime);
                        output.AppendFormat("-- TABLE [{0}]\r\n", table.TableName);

                        if (table.HasIdentity)
                            output.AppendFormat("set identity_insert [{0}] on;\r\n", table.TableName);

                        cmd.CommandText = string.Format("select * from [{0}]", table.TableName);
                        cmd.CreateParameter();
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var sql = string.Format("insert into [{0}] ({1}) values (", table.TableName, columnlist);
                                var values = new List<string>();
                                foreach (DataRow col in columns.Rows)
                                {
                                    var colname = Convert.ToString(col["COLUMN_NAME"]);
                                    var datatype = Convert.ToString(col["DATA_TYPE"]).ToLower();
                                    var value = reader[colname];

                                    if (value == DBNull.Value)
                                        values.Add("null");
                                    else if (datatype == "char" || datatype == "varchar")
                                        values.Add(string.Format("'{0}'", value));
                                    else if (datatype == "nvarchar" || datatype == "nchar")
                                        values.Add(string.Format("N'{0}'", value));
                                    else if (datatype == "nvarchar" || datatype == "datetime")
                                        values.Add(string.Format("'{0:yyyy/MM/dd HH:mm:ss.fff}'", value));
                                    else if (datatype == "bit")
                                        values.Add(string.Format("{0}", value));
                                    else
                                        values.Add(string.Format("{0}", value));
                                }
                                sql += string.Join(", ", values.ToArray()) + ");";
                                output.AppendLine(sql);
                            }
                        }

                        if (table.HasIdentity)
                            output.AppendFormat("set identity_insert [{0}] off;\r\n", table.TableName);

                        var buffer = Encoding.UTF8.GetBytes(output.ToString());

                        using (FileStream stream = new FileStream(string.Format("{0}/{1}.gz", dataPath, table.TableName), FileMode.Create, FileAccess.Write))
                        {
                            using (GZipStream gZip = new GZipStream(stream, CompressionMode.Compress))
                            {
                                gZip.Write(buffer, 0, buffer.Length);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorLogger.WriteException(ex);
            }
        }
    }
}