﻿using System;
using System.Data.SqlClient;
using HomeBudget.Dao.Base.Interfaces;
using HomeManager.Model.Bo.Impl;
using HomeManager.Model.Dao.Base;
using HomeManager.Utils.Extensions;

namespace HomeManager.Model.Dao.Impl
{
    public class ExpenseDao : BaseDao, IExpenseRepository
    {
        public void Save(ExpenseBo bo, bool createReceipt)
        {
            if (bo.IsNewRecord)
            {
                Insert(bo, createReceipt);
            }
            else
            {
                Update(bo, createReceipt);
            }
        }

        private void Update(ExpenseBo expense, bool createReceipt)
        {
            using (SqlConnection conn = CreateConnection())
            {
                using (SqlCommand cmd = CreateSpCommand("spExpenseUpd", conn))
                {
                    cmd.Parameters.AddWithValue("@Id", expense.Id);
                    cmd.Parameters.AddWithNullableValue("@Category", expense.Category);
                    cmd.Parameters.AddWithNullableValue("@Description", expense.Description);
                    cmd.Parameters.AddWithValue("@Amount", expense.Amount);
                    cmd.Parameters.AddWithValue("@Date", expense.Date);
                    cmd.Parameters.AddWithValue("@CreateReceipt", createReceipt);
                    cmd.AddIntOutParam("@Receipt");
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    expense.Id = cmd.GetParamValue<int>("@Id");
                    expense.Receipt = cmd.GetParamValue<int?>("@Receipt");
                }
            }
        }

        private void Insert(ExpenseBo expense, bool createReceipt)
        {
            using (SqlConnection conn = CreateConnection())
            {
                using (SqlCommand cmd = CreateSpCommand("spExpenseIns", conn))
                {
                    cmd.AddIntOutParam("@Id");
                    cmd.Parameters.AddWithNullableValue("@Category", expense.Category);
                    cmd.Parameters.AddWithNullableValue("@Description", expense.Description);
                    cmd.Parameters.AddWithValue("@Amount", expense.Amount);
                    cmd.Parameters.AddWithValue("@Date", expense.Date);
                    cmd.Parameters.AddWithValue("@CreateReceipt", createReceipt);
                    cmd.AddIntOutParam("@Receipt");
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    expense.Id = cmd.GetParamValue<int>("@Id");
                    expense.Receipt = cmd.GetParamValue<int?>("@Receipt");
                }
            }
        }

        public ExpenseBo Get(int id)
        {
            using (SqlConnection conn = CreateConnection())
            {
                using (SqlCommand cmd = CreateSpCommand("spExpenseGet", conn))
                {
                    cmd.Parameters.AddWithValue("@Id", id);
                    cmd.AddIntOutParam("@Category");
                    cmd.AddVarCharOutParam("@Description", 200);
                    cmd.AddDecimalOutParam("@Amount", 16, 2);
                    cmd.AddDateTimeOutParam("@Date");
                    cmd.AddIntOutParam("@Receipt");
                    
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();

                    var expenseBo = new ExpenseBo();
                    expenseBo.Id = id;
                    expenseBo.Category = cmd.GetParamValue<int?>("@Category");
                    expenseBo.Description = cmd.GetParamValue<string>("@Description");
                    expenseBo.Amount = cmd.GetParamValue<decimal>("@Amount");
                    expenseBo.Date = cmd.GetParamValue<DateTime>("@Date");
                    expenseBo.Receipt = cmd.GetParamValue<int?>("@Receipt");
                    return expenseBo;
                }
            }
        }

        public void Delete(int id)
        {
            using (SqlConnection conn = CreateConnection())
            {
                using (SqlCommand cmd = CreateSpCommand("spExpenseDelete", conn))
                {
                    cmd.Parameters.AddWithValue("@Id", id);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }
    }
}