﻿using DbConnector.Core;
using Recipes.DbConnector.Models;
using Recipes.LargeBatch;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;

namespace Recipes.DbConnector.LargeBatch
{
    public class LargeBatchScenario : ScenarioBase, ILargeBatchScenario<EmployeeSimple>
    {
        public LargeBatchScenario(string connectionString) : base(connectionString)
        { }

        public int CountByLastName(string lastName)
        {
            const string sql = "SELECT Count(*) FROM HR.EmployeeDetail e WHERE e.LastName = @lastName";

            return DbConnector.Scalar<int>(sql, new { lastName }).Execute();
        }

        public int MaximumBatchSize => int.MaxValue;

        virtual public void InsertLargeBatch(IList<EmployeeSimple> employees)
        {
            InsertLargeBatch(employees, 250);
        }

        virtual public void InsertLargeBatch(IList<EmployeeSimple> employees, int batchSize)
        {
            if (employees == null || !employees.Any())
                throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

            //Best approach for unlimited inserts since SQL server has parameter amount restrictions
            //https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
            //Also, notice the "batchSize" argument is not necessary.
            DbConnector.Build<int?>(
                    sql: @$"INSERT INTO {EmployeeSimple.TableName}
                    (
                        CellPhone,
                        EmployeeClassificationKey,
                        FirstName,
                        LastName,
                        MiddleName,
                        OfficePhone,
                        Title
                    ) 
                    VALUES (
                        @{nameof(EmployeeSimple.CellPhone)},
                        @{nameof(EmployeeSimple.EmployeeClassificationKey)},
                        @{nameof(EmployeeSimple.FirstName)},
                        @{nameof(EmployeeSimple.LastName)},
                        @{nameof(EmployeeSimple.MiddleName)},
                        @{nameof(EmployeeSimple.OfficePhone)},
                        @{nameof(EmployeeSimple.Title)}
                    )",
                    param: employees.First(),
                    onExecute: (int? result, IDbExecutionModel em) =>
                    {
                        //Set the command
                        DbCommand command = em.Command;

                        //Execute first row.
                        em.NumberOfRowsAffected = command.ExecuteNonQuery();

                        //Set and execute remaining rows.
                        foreach (var emp in employees.Skip(1))
                        {
                            command.Parameters[nameof(EmployeeSimple.CellPhone)].Value = emp.CellPhone ?? (object)DBNull.Value;
                            command.Parameters[nameof(EmployeeSimple.EmployeeClassificationKey)].Value = emp.EmployeeClassificationKey;
                            command.Parameters[nameof(EmployeeSimple.FirstName)].Value = emp.FirstName ?? (object)DBNull.Value;
                            command.Parameters[nameof(EmployeeSimple.LastName)].Value = emp.LastName ?? (object)DBNull.Value;
                            command.Parameters[nameof(EmployeeSimple.MiddleName)].Value = emp.MiddleName ?? (object)DBNull.Value;
                            command.Parameters[nameof(EmployeeSimple.OfficePhone)].Value = emp.OfficePhone ?? (object)DBNull.Value;
                            command.Parameters[nameof(EmployeeSimple.Title)].Value = emp.Title ?? (object)DBNull.Value;

                            em.NumberOfRowsAffected += command.ExecuteNonQuery();
                        }

                        return em.NumberOfRowsAffected;
                    }
                )
                .WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
                .Execute();
        }
    }
}
