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 AccountRepository : IAccountRepository
    {
        public IDbTransaction Transaction { get; }
        public IDbConnection Connection => Transaction.Connection;

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

        public IEnumerable<AccountUser> GetAccountUsers()
        {
            var sql = $@"SELECT * FROM {nameof(AccountUser)}";

            return Connection.Query<AccountUser>(sql, null, Transaction);
        }

        public AccountUser GetAccountUserByAccount(string account)
        {
            var sql = $@"SELECT * FROM {nameof(AccountUser)} 
                                 WHERE {nameof(AccountUser.Account)} = @{nameof(AccountUser.Account)}";

            return Connection.QueryFirstOrDefault<AccountUser>(sql, new { Account = account }, Transaction);
        }

        public AccountUser GetAccountUserByAccount(string account, string password)
        {
            var sql = $@"SELECT * FROM {nameof(AccountUser)} 
                                 WHERE {nameof(AccountUser.Account)} = @{nameof(AccountUser.Account)}
                                   AND {nameof(AccountUser.Password)} = @{nameof(AccountUser.Password)}";

            return Connection.QueryFirstOrDefault<AccountUser>(sql, new { Account = account, Password = password }, Transaction);
        }

        public AccountUser GetAccountUserById(int id)
        {
            var sql = $@"SELECT * FROM {nameof(AccountUser)} 
                                 WHERE {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";

            return Connection.QueryFirstOrDefault<AccountUser>(sql, new { Id = id }, Transaction);
        }

        public IEnumerable<AccountRole> GetAccountRoles()
        {
            var sql = $@"SELECT * FROM {nameof(AccountRole)}";

            return Connection.Query<AccountRole>(sql, null, Transaction);
        }

        public IEnumerable<AccountUserSite> GetAccountUserSite(int id)
        {
            var sql = $@"SELECT * FROM {nameof(AccountUserSite)} 
                                 WHERE {nameof(AccountUserSite.UserId)} = @{nameof(AccountUserSite.UserId)}";

            return Connection.Query<AccountUserSite>(sql, new { UserId = id }, Transaction);
        }

        public IEnumerable<AccountUserSite> GetAccountUserSites()
        {
            var sql = $@"SELECT * FROM {nameof(AccountUserSite)}";

            return Connection.Query<AccountUserSite>(sql, null, Transaction);
        }

        public int InsertAccountUser(AccountUser accountUser)
        {
            var sql = $@"INSERT INTO {nameof(AccountUser)} (
                                     {nameof(AccountUser.Account)},
                                     {nameof(AccountUser.Password)}, 
                                     {nameof(AccountUser.UserName)}, 
                                     {nameof(AccountUser.PhoneNumber)}, 
                                     {nameof(AccountUser.Email)}, 
                                     {nameof(AccountUser.RoleCodeId)}, 
                                     {nameof(AccountUser.PoliceStationId)}, 
                                     {nameof(AccountUser.CreatorId)}, 
                                     {nameof(AccountUser.CreatedOn)}, 
                                     {nameof(AccountUser.ChangePwdOn)}) 
	                          VALUES (@{nameof(AccountUser.Account)},
                                      @{nameof(AccountUser.Password)}, 
                                      @{nameof(AccountUser.UserName)}, 
                                      @{nameof(AccountUser.PhoneNumber)}, 
                                      @{nameof(AccountUser.Email)}, 
                                      @{nameof(AccountUser.RoleCodeId)},
                                      @{nameof(AccountUser.PoliceStationId)},
                                      @{nameof(AccountUser.CreatorId)}, 
                                      @{nameof(AccountUser.CreatedOn)},
                                      @{nameof(AccountUser.ChangePwdOn)});
                              SELECT LAST_INSERT_ID() AS `User_id`;";
            return Connection.QuerySingle<int>(sql, accountUser, Transaction);
        }

        public int InsertAccountRole(AccountRole accountUser)
        {
            var sql = $@"INSERT INTO {nameof(AccountRole)} (
                                     {nameof(AccountRole.RoleName)},
                                     {nameof(AccountRole.CreatorId)}, 
                                     {nameof(AccountRole.CreatedOn)}) 
	                          VALUES (@{nameof(AccountRole.RoleName)},
                                      @{nameof(AccountRole.CreatorId)}, 
                                      @{nameof(AccountRole.CreatedOn)});
                                    SELECT LAST_INSERT_ID() AS `User_id`;";
            return Connection.QuerySingle<int>(sql, accountUser, Transaction);
        }

        public bool InsertAccountUserSite(List<AccountUserSite> userSite)
        {
            var sql = $@"INSERT INTO {nameof(AccountUserSite)} (
                                     {nameof(AccountUserSite.UserId)},
                                     {nameof(AccountUserSite.SiteId)})   
	                          VALUES (@{nameof(AccountUserSite.UserId)},
                                      @{nameof(AccountUserSite.SiteId)})";

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

        public bool UpdateAccountUserbyAdmin(AccountUser accountUser)
        {
            var sql = $@"UPDATE {nameof(AccountUser)}
	                        SET {nameof(AccountUser.Account)}=@{nameof(AccountUser.Account)},
                                {nameof(AccountUser.UserName)}=@{nameof(AccountUser.UserName)},
                                {nameof(AccountUser.PhoneNumber)}=@{nameof(AccountUser.PhoneNumber)},
                                {nameof(AccountUser.Email)}=@{nameof(AccountUser.Email)},
                                {nameof(AccountUser.RoleCodeId)}=@{nameof(AccountUser.RoleCodeId)},
                                {nameof(AccountUser.PoliceStationId)}=@{nameof(AccountUser.PoliceStationId)},    
                                {nameof(AccountUser.UpdatorId)}=@{nameof(AccountUser.UpdatorId)},    
                                {nameof(AccountUser.UpdatedOn)}=@{nameof(AccountUser.UpdatedOn)}     
	                      WHERE {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";

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

        public bool UpdateAccountUserPasswordByAdmin(int id, string newPassword, int byWho, DateTime updateOn)
        {
            var sql = $@"UPDATE {nameof(AccountUser)}
	                        SET {nameof(AccountUser.Password)}=@newPassword,       
                                {nameof(AccountUser.UpdatorId)}=@{nameof(AccountUser.UpdatorId)},    
                                {nameof(AccountUser.UpdatedOn)}=@{nameof(AccountUser.UpdatedOn)},   
                                {nameof(AccountUser.ChangePwdOn)}=@{nameof(AccountUser.UpdatedOn)},   
                                {nameof(AccountUser.ErrorCount)}=0      
	                      WHERE {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";

            var result = Connection.Execute(sql, new { Id = id, newPassword, UpdatorId = byWho, UpdatedOn = updateOn }, Transaction);
            return result == 1;
        }

        public bool UpdateAccountUserbyUser(AccountUser accountUser)
        {
            var sql = $@"UPDATE {nameof(AccountUser)}
	                        SET {nameof(AccountUser.UserName)}=@{nameof(AccountUser.UserName)},
                                {nameof(AccountUser.PhoneNumber)}=@{nameof(AccountUser.PhoneNumber)},
                                {nameof(AccountUser.Email)}=@{nameof(AccountUser.Email)},    
                                {nameof(AccountUser.UpdatorId)}=@{nameof(AccountUser.UpdatorId)},    
                                {nameof(AccountUser.UpdatedOn)}=@{nameof(AccountUser.UpdatedOn)} 
	                      WHERE {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";

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

        public bool UpdateAccountUserPasswordByUser(int id, string oldPassword, string newPassword, DateTime updatedOn)
        {
            var sql = $@"UPDATE {nameof(AccountUser)}
	                        SET {nameof(AccountUser.Password)}=@newPassword,    
                                {nameof(AccountUser.UpdatorId)}=@{nameof(AccountUser.UpdatorId)},    
                                {nameof(AccountUser.UpdatedOn)}=@{nameof(AccountUser.UpdatedOn)},   
                                {nameof(AccountUser.ChangePwdOn)}=@{nameof(AccountUser.UpdatedOn)},   
                                {nameof(AccountUser.ErrorCount)}=0     
	                      WHERE {nameof(AccountUser.Password)} = @oldPassword And {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";

            var result = Connection.Execute(sql, new { Id = id, oldPassword, newPassword, UpdatorId = id, UpdatedOn = updatedOn }, Transaction);
            return result == 1;
        }

        public bool InsertUserLoginLog(UserLoginLog userLogin)
        {
            var sql = $@"INSERT INTO {nameof(UserLoginLog)} (
                                     {nameof(UserLoginLog.UserId)},
                                     {nameof(UserLoginLog.LoginIP)}, 
                                     {nameof(UserLoginLog.LoginOn)}, 
                                     {nameof(UserLoginLog.Status)}) 
	                          VALUES (@{nameof(UserLoginLog.UserId)},
                                      @{nameof(UserLoginLog.LoginIP)}, 
                                      @{nameof(UserLoginLog.LoginOn)}, 
                                      @{nameof(UserLoginLog.Status)})";

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

        public bool UpdateUserErrorCount(int id, int errorCount)
        {
            var sql = $@"UPDATE {nameof(AccountUser)}
	                        SET {nameof(AccountUser.ErrorCount)}=@{nameof(AccountUser.ErrorCount)}
	                      WHERE {nameof(AccountUser.Id)} = @{nameof(AccountUser.Id)}";

            var result = Connection.Execute(sql, new { id, errorCount }, Transaction);
            return result == 1;
        }

        public bool DisableUser(int byWho, int id, int status)
        {
            var sql = $@"UPDATE {nameof(AccountUser)}
	                        SET {nameof(AccountUser.Status)} = @{nameof(status)},
                                {nameof(AccountUser.UpdatorId)} = @{nameof(byWho)}, 
                                {nameof(AccountUser.UpdatedOn)} = NOW()  
	                      WHERE {nameof(AccountUser.Id)} = @{nameof(id)}";

            var result = Connection.Execute(sql, new { byWho, id, status }, Transaction);
            return result == 1;
        }

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

        public bool DeleteUserSite(int userId)
        {
            var sql = $@"DELETE FROM {nameof(AccountUserSite)} WHERE  {nameof(AccountUserSite.UserId)} = @{nameof(AccountUserSite.UserId)}";
            var result = Connection.Execute(sql, new { UserId = userId }, Transaction);
            return result >= 1;
        }

        public IEnumerable<AccountPwd> GetAccountPwdTop3(string account)
        {
            var sql = $@"SELECT * FROM {nameof(AccountPwd)} 
                                 WHERE {nameof(AccountPwd.Account)} = {nameof(account)}  
                              ORDER BY {nameof(AccountPwd.CreatedOn)} DESC 
                                 LIMIT 3";

            return Connection.Query<AccountPwd>(sql, new { account }, Transaction);
        }

        public bool InsertAccountPwd(AccountPwd accountUser)
        {
            var sql = $@"INSERT INTO {nameof(AccountPwd)} (
                                     {nameof(AccountPwd.Account)},
                                     {nameof(AccountPwd.Password)}, 
                                     {nameof(AccountPwd.CreatedOn)}) 
	                          VALUES (@{nameof(AccountPwd.Account)},
                                      @{nameof(AccountPwd.Password)},  
                                      @{nameof(AccountPwd.CreatedOn)})";
            var result = Connection.Execute(sql, accountUser, Transaction);
            return result >= 1;
        }
    }
}