using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Threading;
using System.Transactions;

namespace CopyRates
{
    class Program
    {
        private static SqlConnection conAn = new SqlConnection(Properties.Settings.Default.An);
        private static SqlConnection conWin = new SqlConnection(Properties.Settings.Default.Win);

        static void Main(string[] args)
        {
            if (GetMaxDateWin() > GetMaxDateAn())
            {
                Thread.Sleep(5000);
                UpdateRates();
                Console.Out.WriteLine("Updated");
            }
            else
            {
                Console.Out.WriteLine("Not Updated");
            }
        }

        private static void UpdateRates()
        {
            using (TransactionScope transactionScope = new TransactionScope())
            {
                try
                {
                    SqlCommand cmdWin = new SqlCommand();
                    cmdWin.Connection = conWin;

                    SqlCommand cmdAn = new SqlCommand();
                    cmdAn.Connection = conAn;

                    conWin.Open();
                    conAn.Open();

                    cmdWin.CommandText = " select CurrencyId, BranchGroupId, CurrencyBuying, CurrencySelling " +
                                         " from tbl_ExchangeRate                                             ";

                    cmdAn.CommandText = " INSERT INTO[dbo].[tbl_ExchangeRate] " +
                                        "         ([CurrencyId]               " +
                                        "         ,[BranchGroupId]            " +
                                        "         ,[CurrencyBuying]           " +
                                        "         ,[CurrencySelling]          " +
                                        "         ,[UpdatedDate])             " +
                                        "   VALUES                            " +
                                        "         ( @CurrencyId               " +
                                        "         , @BranchGroup              " +
                                        "         , @Buying                   " +
                                        "         , @Selling                  " +
                                        "         , @Date)                    ";

                    SqlDataReader winReader = cmdWin.ExecuteReader();

                    SqlCommand cmdAnDelete = new SqlCommand();
                    cmdAnDelete.Connection = conAn;
                    cmdAnDelete.CommandText = "DELETE FROM tbl_ExchangeRate";
                    cmdAnDelete.ExecuteNonQuery();

                    while (winReader.Read())
                    {
                        cmdAn.Parameters.Clear();
                        cmdAn.Parameters.AddWithValue("@CurrencyId", winReader[0]);
                        cmdAn.Parameters.AddWithValue("@BranchGroup", 41);
                        cmdAn.Parameters.AddWithValue("@Buying", winReader[2]);
                        cmdAn.Parameters.AddWithValue("@Selling", winReader[3]);
                        cmdAn.Parameters.AddWithValue("@Date", DateTime.Now);
                        cmdAn.ExecuteNonQuery();
                    }

                    conWin.Close();
                    conAn.Close();
                    transactionScope.Complete();
                }
                catch(Exception ex)
                {
                    Console.Out.WriteLine(ex.Message);
                    Console.Out.WriteLine(ex.StackTrace);
                    transactionScope.Dispose();
                }
            }
        }

        private static DateTime GetMaxDateWin()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conWin;
            cmd.CommandText = " SELECT     MAX(UpdatedDate) " +
                              " FROM tbl_ExchangeRate       ";

            conWin.Open();

            DateTime max = Convert.ToDateTime(cmd.ExecuteScalar());

            conWin.Close();

            return max;
        }

        private static DateTime GetMaxDateAn()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conAn;
            cmd.CommandText = " SELECT     MAX(UpdatedDate) " +
                              " FROM tbl_ExchangeRate       ";

            conAn.Open();

            DateTime max = Convert.ToDateTime(cmd.ExecuteScalar());

            conAn.Close();

            return max;
        }
    }
}