using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Traffic.Data.Models;
using Traffic.Repository.Interfaces;

namespace Traffic.Repository.Implements
{
    public class CompanyInformationRepository : ICompanyInformationRepository
    {
        public IDbTransaction Transaction { get; }
        public IDbConnection Connection => Transaction.Connection;

        public CompanyInformationRepository(IDbTransaction transaction)
        {
            Transaction = transaction;
        }

        /// <summary>
        /// 分頁 select * from persons limit A offset B;
        /// A就是你需要顯示多少行； 
        /// B就是查詢的起點位置。
        /// </summary>
        /// <param name="pageNumber">第幾頁</param>
        /// <param name="pageSize">一頁幾行</param>
        /// <returns></returns>
        public IEnumerable<CompanyInformation> GetCompanyInformations()
        {
            var sql = $"SELECT * FROM {nameof(CompanyInformation)}";
            return Connection.Query<CompanyInformation>(sql, null, Transaction);
        }

        public CompanyInformation GetCompanyInformationById(int id)
        {
            var sql = $"SELECT * FROM {nameof(CompanyInformation)} Where {nameof(CompanyInformation.Id)} = @{nameof(CompanyInformation.Id)}";
            return Connection.QueryFirstOrDefault<CompanyInformation>(sql, new { id }, Transaction);
        }

        public bool InsertCompanyInformation(CompanyInformation company)
        {
            var sql = $@"INSERT INTO {nameof(CompanyInformation)} (
                                     {nameof(CompanyInformation.CompanyName)}, 
                                     {nameof(CompanyInformation.CompanyCode)}, 
                                     {nameof(CompanyInformation.Email)})
	                          VALUES(@{nameof(CompanyInformation.CompanyName)}, 
                                     @{nameof(CompanyInformation.CompanyCode)}, 
                                     @{nameof(CompanyInformation.Email)})";

            var result = Connection.Execute(sql, company, Transaction);
            return result == 1;
        }

        public bool UpdateCompanyInformation(CompanyInformation company)
        {
            var sql = $@"UPDATE {nameof(CompanyInformation)}
	                        SET {nameof(CompanyInformation.CompanyName)} = @{nameof(CompanyInformation.CompanyName)}, 
                                {nameof(CompanyInformation.CompanyCode)} = @{nameof(CompanyInformation.CompanyCode)}, 
                                {nameof(CompanyInformation.Email)} = @{nameof(CompanyInformation.Email)}  
	                      WHERE {nameof(CompanyInformation.Id)} = @{nameof(CompanyInformation.Id)}";
            var result = Connection.Execute(sql, company, Transaction);
            return result == 1;
        }

        public bool DeleteCompanyInformation(int id)
        {
            var sql = $@"DELETE FROM {nameof(CompanyInformation)} WHERE  {nameof(CompanyInformation.Id)} = @{nameof(CompanyInformation.Id)}";
            var result = Connection.Execute(sql, new { id }, Transaction);
            return result == 1;
        }
    }
}