using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using Dapper;
using apiServer.Data.Models;

namespace apiServer.Data
{
    public class DataRepository : IDataRepository
    {
        private readonly string _connectionString;
        public DataRepository(IConfiguration configuration)
        {
            _connectionString = configuration["ConnectionStrings:DefaultConnection"];
        }

        public void DepartmentDelete(DepartmentDeleteRequest departmentDeleteRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                connection.Execute(@"EXEC dbo.Department_Delete @DepartmentID = @DepartmentID",
                  new { DepartmentID = departmentDeleteRequest.DepartmentID }
                );
            }
        }

        public IEnumerable<DepartmentGetManyResponse> DepartmentGetMany()
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.Query<DepartmentGetManyResponse>(@"EXEC dbo.Department_GetMany");
            }
        }

        public DepartmentGetOneResponse DepartmentGetOne(DepartmentGetOneRequest departmentGetOneRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<DepartmentGetOneResponse>(
                  @"EXEC dbo.Department_GetOne @DepartmentID = @DepartmentID",
                  departmentGetOneRequest
                );
            }
        }

        public DepartmentPostResponse DepartmentPost(DepartmentPostRequest departmentPostRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<DepartmentPostResponse>(
                  @"EXEC dbo.Department_Post @DepartmentName = @DepartmentName",
                  departmentPostRequest
                );
            }
        }

        public void GeneralClassDelete(GeneralClassDeleteRequest generalClassDeleteRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Execute(@"EXEC dbo.GeneralClass_Delete @GeneralClassID = @GeneralClassID",
                    generalClassDeleteRequest
                );
            }
        }

        public IEnumerable<GeneralClassGetManyResponse> GeneralClassGetMany()
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                var results =  connection.Query<GeneralClassGetManyResponse>(@"EXEC dbo.GeneralClass_GetMany");
                return (IEnumerable<GeneralClassGetManyResponse>)results;
            }
        }

        public GeneralClassGetOneResponse GeneralClassGetOne(GeneralClassGetOneRequest generalClassGetOneRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<GeneralClassGetOneResponse>(
                  @"EXEC dbo.GeneralClass_GetOne @GeneralClassID = @GeneralClassID",
                  generalClassGetOneRequest
                );
            }
        }

        public GeneralClassPostResponse GeneralClassPost(GeneralClassPostRequest generalClassPostRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<GeneralClassPostResponse>(
                  @"EXEC dbo.GeneralClass_Post @GeneralClassName = @GeneralClassName",
                  generalClassPostRequest
                );
            }
        }

        public void OwershipDelete(OwershipDeleteRequest owershipDeleteRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Execute(@"EXEC dbo.Owership_Delete @OwershipID = @OwershipID",
                    owershipDeleteRequest
                );
            }
        }

        public IEnumerable<OwershipGetManyBySearchProjectIDResponse> OwershipGetManyBySearchProjectID(OwershipGetManyBySearchProjectIDRequest owershipGetManyBySearchProjectIDRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.Query<OwershipGetManyBySearchProjectIDResponse>(
                  @"EXEC dbo.Owership_GetMany_BySearch_ProjectID @ProjectID = @ProjectID",
                  owershipGetManyBySearchProjectIDRequest
                );
            }
        }

        public IEnumerable<OwershipGetManyBySearchTeacherIDResponse> OwershipGetManyBySearchTeacherID(OwershipGetManyBySearchTeacherIDRequest owershipGetManyBySearchTeacherIDRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.Query<OwershipGetManyBySearchTeacherIDResponse>(
                  @"EXEC dbo.Owership_GetMany_BySearch_TeacherID @TeacherID = @TeacherID",
                  owershipGetManyBySearchTeacherIDRequest
                );
            }
        }

        public OwershipPostResponse OwershipPost(OwershipPostRequest owershipPostRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<OwershipPostResponse>(
                  @"EXEC dbo.Owership_Post @ProjectID = @ProjectID, @TeacherID = @TeacherID",
                  owershipPostRequest
                );
            }
        }

        public void ProjectDelete(ProjectDeleteRequest projectDeleteRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Execute(@"EXEC dbo.Project_Delete @ProjectID = @ProjectID",
                    projectDeleteRequest
                );
            }
        }

        public IEnumerable<ProjectGetManyResponse> ProjectGetMany()
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.Query<ProjectGetManyResponse>(
                  @"EXEC dbo.Project_GetMany"
                );
            }
        }

        public IEnumerable<ProjectGetManyBySearchResponse> ProjectGetManyBySearch(ProjectGetManyBySearchRequest projectGetManyBySearchRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.Query<ProjectGetManyBySearchResponse>(
                  @"EXEC dbo.Project_GetMany_BySearch @Keyword = @Keyword",
                  projectGetManyBySearchRequest
                );
            }
        }

        public ProjectGetOneResponse ProjectGetOne(ProjectGetOneRequest projectGetOneRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<ProjectGetOneResponse>(
                  @"EXEC dbo.Project_GetOne @ProjectID = @ProjectID",
                  projectGetOneRequest
                );
            }
        }

        public ProjectPostResponse ProjectPost(ProjectPostRequest projectPostRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<ProjectPostResponse>(
                  @"EXEC dbo.Project_Post @ProjectName=@ProjectName, @GeneralClassID=@GeneralClassID, @DepartmentID=@DepartmentID, @SubjectClassID=@SubjectClassID, @InChargeTeacherID=@InChargeTeacherID, @StartTime=@StartTime, @EndTime=@EndTime, @MoneyValue=@MoneyValue, @Finished=@Finished, @Agreed=@Agreed",
                  projectPostRequest
                );
            }
        }

        public ProjectPutResponse ProjectPut(ProjectPutRequest projectPutRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<ProjectPutResponse>(
                  @"EXEC dbo.Project_Put @ProjectID=@ProjectID, @ProjectName=@ProjectName, @GeneralClassID=@GeneralClassID, @DepartmentID=@DepartmentID, @SubjectClassID=@SubjectClassID, @InChargeTeacherID=@InChargeTeacherID, @StartTime=@StartTime, @EndTime=@EndTime, @MoneyValue=@MoneyValue, @Finished=@Finished, @Agreed=@Agreed",
                  projectPutRequest
                );
            }
        }

        public void SubjectClassDelete(SubjectClassDeleteRequest subjectClassDeleteRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Execute(@"EXEC dbo.SubjectClass_Delete @SubjectClassID = @SubjectClassID",
                    subjectClassDeleteRequest
                );
            }
        }

        public IEnumerable<SubjectClassGetManyResponse> SubjectClassGetMany()
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.Query<SubjectClassGetManyResponse>(
                  @"EXEC dbo.SubjectClass_GetMany"
                );
            }
        }

        public SubjectClassGetOneResponse SubjectClassGetOne(SubjectClassGetOneRequest subjectClassGetOneRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<SubjectClassGetOneResponse>(
                  @"EXEC dbo.SubjectClass_GetOne @SubjectClassID=@SubjectClassID",
                  subjectClassGetOneRequest
                );
            }
        }

        public SubjectClassPostResponse SubjectClassPost(SubjectClassPostRequest subjectClassPostRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<SubjectClassPostResponse>(
                  @"EXEC dbo.SubjectClass_Post @SubjectClassName=@SubjectClassName",
                  subjectClassPostRequest
                );
            }
        }

        public void TeacherDelete(TeacherDeleteRequest teacherDeleteRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Execute(@"EXEC dbo.Teacher_Delete @TeacherID = @TeacherID",
                    teacherDeleteRequest
                );
            }
        }

        public IEnumerable<TeacherGetManyResponse> TeacherGetMany()
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.Query<TeacherGetManyResponse>(
                  @"EXEC dbo.Teacher_GetMany"
                );
            }
        }

        public IEnumerable<TeacherGetManyBySearchResponse> TeacherGetManyBySearch(TeacherGetManyBySearchRequest teacherGetManyBySearchRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.Query<TeacherGetManyBySearchResponse>(
                  @"EXEC dbo.Teacher_GetMany_BySearch @Keyword=@Keyword",
                  teacherGetManyBySearchRequest
                );
            }
        }

        public TeacherGetOneResponse TeacherGetOne(TeacherGetOneRequest teacherGetOneRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<TeacherGetOneResponse>(
                  @"EXEC dbo.Teacher_GetOne @TeacherID=@TeacherID",
                  teacherGetOneRequest
                );
            }
        }

        public bool TeacherAuth(AuthRequest authRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<bool>(
                  @"EXEC dbo.Teacher_Auth @TeacherRealID=@TeacherRealID, @TeacherPassword=@TeacherPassword",
                  authRequest
                );
            }
        }

        public bool TeacherIsAdmin(AuthRequest authRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<bool>(
                  @"EXEC dbo.Teacher_IsAdmin @TeacherRealID=@TeacherRealID",
                  authRequest
                );
            }
        }

        public TeacherPostResponse TeacherPost(TeacherPostRequest teacherPostRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<TeacherPostResponse>(
                  @"EXEC dbo.Teacher_Post @TeacherRealID=@TeacherRealID, @TeacherName=@TeacherName, @TeacherPassword=@TeacherPassword, @TeacherIsAdmin=@TeacherIsAdmin",
                  teacherPostRequest
                );
            }
        }
    
        public TeacherPutResponse TeacherPut(TeacherPutRequest teacherPutRequest)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                return connection.QueryFirst<TeacherPutResponse>(
                  @"EXEC dbo.Teacher_Put @TeacherID=@TeacherID, @TeacherRealID=@TeacherRealID, @TeacherName=@TeacherName, @TeacherPassword=@TeacherPassword, @TeacherIsAdmin=@TeacherIsAdmin",
                  teacherPutRequest
                );
            }
        }
    }
}
