using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Zadachi4
{
    public partial class Form1 : Form
    {
        private Int32 LastStatementId = -1;

        private string MemberId;
        private string ProviderId;
        private string Category;
        private double Amount;



        public Form1()
        {
            InitializeComponent();
        }

        private void AddStatement_Click(object sender, EventArgs e)
        {
            using (IDbConnection connection = new SqlConnection(Properties.Settings.Default.DbContext))
            {
                MemberId = MemberIdTb.Text;
                ProviderId = ProviderIdTb.Text;
                Category = CategoryTb.Text;
                Amount = double.Parse(AmountTb.Text);

                string sqlquery = string.Format(@"INSERT INTO statement
                                                                ([member_no]
                                                                ,[statement_dt]
                                                                ,[due_dt]
                                                                ,[statement_amt]
                                                                ,[statement_code])
                                                                VALUES
                                                                ({0}
                                                                ,CURRENT_TIMESTAMP
                                                                ,CURRENT_TIMESTAMP
                                                                ,1500.00
                                                                ,0)", MemberId);
                IDbCommand command = new SqlCommand(sqlquery);
                command.Connection = connection;
                connection.Open();
                command.ExecuteNonQuery();
                command.CommandText = "SELECT MAX(statement.statement_no) FROM statement; ";
                IDataReader reader = command.ExecuteReader();
                reader.Read();
                LastStatementId = reader.GetInt32(0);
            }
        }

        private void AddPaymentWithoutRollback_Click(object sender, EventArgs e)
        {
            if (LastStatementId < 0)
                return;
            int ErrorVar = 0;
            Double PaySum = 500.0;
            Double PayedTotal = 0.0;
            Double ToBePayed = 0.0;
            string FirstQuery = @"INSERT INTO payment
                                                ([member_no]
                                                ,[payment_dt]
                                                ,[payment_amt]
                                                ,[statement_no]
                                                ,[payment_code])
                                                VALUES
                                                (10001
                                                ,CURRENT_TIMESTAMP
                                                ," + PaySum +
                                                " ," + LastStatementId +
                                                ",0)";
            string SecondQuery = @"SELECT SUM(payment.payment_amt)
                                                FROM payment
                                                WHERE payment.statement_no = " + LastStatementId;
            int Code = 0;
            string ThirdQuery = @"SELECT statement.statement_amt
                                                FROM statement
                                                WHERE statement.statement_no = " + LastStatementId;
            string FourthQuery = @"UPDATE statement
                                                SET statement_code = {0}" +
                                                "WHERE statement_no = " + LastStatementId;

            using (IDbConnection connection = new SqlConnection(Properties.Settings.Default.DbContext))
            {
                IDbCommand command = new SqlCommand(FirstQuery);

                command.Connection = connection;
                connection.Open();

                IDbTransaction transact;
                transact = connection.BeginTransaction();
                command.Transaction = transact;

                try
                {
                    command.ExecuteNonQuery();
                    int a = 10 / ErrorVar; // Генерираме Exeption
                    command.CommandText = SecondQuery;
                    IDataReader reader = command.ExecuteReader();
                    reader.Read();
                    PayedTotal = reader.GetDouble(0);
                    command.CommandText = ThirdQuery;
                    reader = command.ExecuteReader();
                    reader.Read();
                    ToBePayed = reader.GetDouble(0);
                    if (PayedTotal == 0.0)
                    { Code = 0; }
                    else if (PayedTotal < ToBePayed)
                    { Code = 1; }
                    else { Code = 2; }
                    command.CommandText = String.Format(FourthQuery, Code);
                    command.ExecuteNonQuery();
                    command.Transaction.Commit();

                }
                catch (Exception ex)
                {
                    command.Transaction.Rollback();
                    MessageBox.Show(ex.Message, "Грешка, но пък правим rollback");
                }
            }
        }

        private void AddCharge_Click(object sender, EventArgs e)
        {

        }

        private void AddPaymentWithRollback_Click(object sender, EventArgs e)
        {
            MemberId = MemberIdTb.Text;
            ProviderId = ProviderIdTb.Text;
            Category = CategoryTb.Text;
            Amount = double.Parse(AmountTb.Text);

            string InsertIntoStatement = string.Format(@"INSERT INTO statement([member_no], [statement_dt], [due_dt], [statement_amt], [statement_code])
                                            VALUES({0}, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1000, 1)", MemberId);

            string GetLastStatement = @" SELECT MAX(dbo.statement.statement_no)
                                            FROM STATEMENT;";



            string SecondQuery = @"SELECT SUM(payment.payment_amt)
                        FROM payment
                        WHERE payment.statement_no = " + LastStatementId;

            int Code = 0;
            string ThirdQuery = @"SELECT statement.statement_amt
                        FROM statement
                        WHERE statement.statement_no = " + LastStatementId;

            string FourthQuery = @"UPDATE statement
                        SET statement_code = {0}" +
                        "WHERE statement_no = " + LastStatementId;

            string ChargeStatement = string.Format(@"UPDATE charge
                                     SET charge_amt = charge_amt - {0}
                                     WHERE [member_no] = {1};", Amount, MemberId);

            using (IDbConnection connection = new SqlConnection(Properties.Settings.Default.DbContext))
            {
                IDbCommand command = new SqlCommand(InsertIntoStatement);

                command.Connection = connection;
                connection.Open();

                IDbTransaction transact;
                transact = connection.BeginTransaction();
                command.Transaction = transact;

                try
                {
                    // INSERT Into Statement
                    command.ExecuteNonQuery();

                    // Get Last
                    command.CommandText = GetLastStatement;

                    // Get last id
                    IDataReader reader = command.ExecuteReader();
                    reader.Read();
                    LastStatementId = reader.GetInt32(0);
                    reader.Close();

                    string InsertIntoCharge = string.Format(@"INSERT INTO charge (
                        [member_no], [provider_no], [category_no], [charge_dt], [charge_amt], [statement_no], [charge_code])
                        VALUES ({0}, {1}, {2}, CURRENT_TIMESTAMP, {3}, {4}, 0);", MemberId, ProviderId, Category, Amount, LastStatementId);

                    // INSERT INTO CHARGE
                    command.CommandText = InsertIntoCharge;
                    command.ExecuteNonQuery();

                    // UPDATE Charge
                    command.CommandText = ChargeStatement;
                    command.ExecuteNonQuery();

                    command.Transaction.Commit();

                }
                catch (Exception ex)
                {
                    command.Transaction.Rollback();
                    MessageBox.Show(ex.Message, "Грешка, но пък правим rollback");
                }
            }
        }
    }
}