using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GB5Shared.Query.FrameWork.User
{
    public static class UserQB
    {
        public const string USER_DETAIL =
            @" Select
                 a.userId as UserId, 
                 a.userCode as UserCode, 
                 a.userName as UserName, 
                 a.Password as UserPassword, 
                 a.LastSuccessLoginOn as UserLastSuccessLoginOn, 
                 a.Status as CheckStatus, 
                 a.ValidFrom as UserValidFrom, 
                 a.ValidTo as UserValidTo ,
                 a.RoleId as RoleId ,
                 a.workouid as UserWorkOuId ,
                 d.ORGANIZATIONUNITCODE as UserWorkOuCode ,
                 d.ORGANIZATIONUNITNAME as UserWorkOuName ,


                 a.WORKPERIODID as UserWorkPeriodId ,
                 a.WorkPartyBranchId as UserWorkPartyBranchId ,
                 C.PartyId as UserWorkPartyId ,
                 a.WorkStoreId as UserWorkStoreId ,
                 a.WorkDate as UserWorkDate ,
                 a.ModeofOperation as CheckModeofOperation ,
                 a.UserCriteriaConfigId as UserCriteriaConfigId,
                 a.USERDATEFORMAT as UserDateFormat,
                 a.USERTIMEFORMAT as UserTimeFormat,
                 a.USERCURRENCYFORMAT as UserCurrencyFormat,
                 a.USERQUANTITYFORMAT as UserQuantityFormat,
                 a.DELIMITER as UserDelimiter,
                 a.USERLOGINNAME as UserLoginName,
                 a.PRIMARYMAIL as UserPrimaryMail, 
                  a.NOOFSESSIONS as CheckUserNoOfSessions,
                  a.LOGINONWORKDAYSONLY as CheckUserLoginonWorkDaysOnly,
                  a.LOGINFROMTIME as CheckUserLoginFromTime,
                  a.LOGINTOTIME as CheckUserLoginToTime,
                  a.LOGINMACHINE as UserLoginMachine,
                  a.LOGINIP as UserLoginIp, 
                  a.WORKFINANCEBOOKID as WorkFinanceBookId,
                  b.TIMEZONEID as  TimeZoneId,
                  b.UTCOFFSET as  TimeZone,
                  b.DISPLAYNAME as TimeZoneDisplayName,
                  ISNULL(vv.COUNTEROPERATIONID,-1) as CounterOperationId,
                  ISNULL(e.ISENCRYPTIONREQUIRED,0) as IsEncryptionRequired , 
                  dblevelsetting.SelectlistOperationType as SelectlistOperationType ,
                  dblevelsetting.ExpiryTime as ExpiryTime ,
                  dblevelsetting.GraceTime as GraceTime ,
                  dblevelsetting.IsIpBasedCheckingRequired as IsIpBasedCheckingRequired,  
                  dblevelsetting.ATTACHMENTOPTION as TempAttachmentOption,
                  a.MFAUSERSETTING as CheckUserMFAUserSetting,
                  a.MFAUSERACCOUNTID as UserMFAUserAccountId,
                  a.MFAUSERSECRETKEY as UserMFAUserSecretKey,
                  a.MFAQRCODE as UserMFAQRCode,
                  a.SHOWQRCODE as CheckUserShowQRCode,
                  a.PASSWORDCHANGEDON as UserPasswordChangedOn,
                  a.ISPARTNER as CheckIsPartner,
                  CASE WHEN ISNULL(emp.num,0)=0 THEN 1 ELSE 0 END AS CheckIsEmployee,
                  a.USERTYPE as UserType
                   from
                   MUser a
                   outer apply(
                      select Count(*)num from MEMPLOYEE WHERE EMPLOYEEID=a.userid
                   ) emp
                   left outer join(select COUNTEROPERATIONID, OUID, PERIODID,
                    COUNTERSTATUS, CASHIERID from tcounteroperation where 1=1)vv 
                    on(vv.OUID= a.workouid  and vv.PERIODID= a.workperiodid and 
                    vv.COUNTERSTATUS= 0 and CASHIERID = a.UserId)
                   left outer join(Select IsEncryptionRequired from MDEVELOPER 
                   where developerid = @developerid) e on(1=1),
                   MTIMEZONE b, 
                   MPartyBranch c,
                   MorganizationUnit d, 
                   MDBLEVELSETTING dblevelsetting
                   where
                        a.userCode=@usercode
                    and a.TIMEZONEID=b.TIMEZONEID
                    and a.WorkPartyBranchId= c.PartyBranchId
                    and a.WORKOUID= d.OUID
                    and a.TENANTID=@clientid ";

            public const string PG_USER_DETAIL =
                                @"SELECT
                                a.userid AS UserId,
                                a.usercode AS UserCode,
                                a.username AS UserName,
                                a.password AS UserPassword,
                                a.lastsuccessloginon AS UserLastSuccessLoginOn,
                                a.status AS CheckStatus,
                                a.validfrom AS UserValidFrom,
                                a.validto AS UserValidTo,
                                a.roleid AS RoleId,
                                a.workouid AS UserWorkOuId,
                                d.organizationunitcode AS UserWorkOuCode,
                                d.organizationunitname AS UserWorkOuName,
                                a.workperiodid AS UserWorkPeriodId,
                                a.workpartybranchid AS UserWorkPartyBranchId,
                                c.partyid AS UserWorkPartyId,
                                a.workstoreid AS UserWorkStoreId,
                                a.workdate AS UserWorkDate,
                                a.modeofoperation AS CheckModeofOperation,
                                a.usercriteriaconfigid AS UserCriteriaConfigId,
                                a.userdateformat AS UserDateFormat,
                                a.usertimeformat AS UserTimeFormat,
                                a.usercurrencyformat AS UserCurrencyFormat,
                                a.userquantityformat AS UserQuantityFormat,
                                a.delimiter AS UserDelimiter,
                                a.userloginname AS UserLoginName,
                                a.primarymail AS UserPrimaryMail,
                                a.noofsessions AS CheckUserNoOfSessions,
                                a.loginonworkdaysonly AS CheckUserLoginonWorkDaysOnly,
                                a.loginfromtime AS CheckUserLoginFromTime,
                                a.logintotime AS CheckUserLoginToTime,
                                a.loginmachine AS UserLoginMachine,
                                a.loginip AS UserLoginIp,
                                a.workfinancebookid AS WorkFinanceBookId,

                                b.timezoneid AS TimeZoneId,
                                b.utcoffset AS TimeZone,
                                b.displayname AS TimeZoneDisplayName,

                                COALESCE(vv.counteroperationid, -1) AS CounterOperationId,
                                COALESCE(e.isencryptionrequired, 0) AS IsEncryptionRequired,

                                dblevelsetting.selectlistoperationtype AS SelectlistOperationType,
                                dblevelsetting.expirytime AS ExpiryTime,
                                dblevelsetting.gracetime AS GraceTime,
                                dblevelsetting.isipbasedcheckingrequired AS IsIpBasedCheckingRequired,
                                dblevelsetting.attachmentoption AS TempAttachmentOption,

                                a.mfausersetting AS CheckUserMFAUserSetting,
                                a.mfauseraccountid AS UserMFAUserAccountId,
                                a.mfausersecretkey AS UserMFAUserSecretKey,
                                a.mfaqrcode AS UserMFAQRCode,
                                a.showqrcode AS CheckUserShowQRCode,

                                a.passwordchangedon AS UserPasswordChangedOn,
                                a.ispartner AS CheckIsPartner,

                                CASE WHEN COALESCE(emp.num, 0) = 0 THEN 1 ELSE 0 END AS CheckIsEmployee,

                                a.usertype AS UserType

                            FROM
                                muser a

                                -- SQL Server OUTER APPLY → PostgreSQL LEFT JOIN LATERAL
                                LEFT JOIN LATERAL (
                                    SELECT COUNT(*) AS num
                                    FROM memployee
                                    WHERE employeeid = a.userid
                                ) emp ON TRUE

                                LEFT JOIN (
                                    SELECT counteroperationid, ouid, periodid, counterstatus, cashierid
                                    FROM tcounteroperation
                                ) vv ON (
                                    vv.ouid = a.workouid
                                    AND vv.periodid = a.workperiodid
                                    AND vv.counterstatus = 0
                                    AND vv.cashierid = a.userid
                                )

                                LEFT JOIN (
                                    SELECT isencryptionrequired
                                    FROM mdeveloper
                                    WHERE developerid = @developerid
                                ) e ON TRUE

                                JOIN mtimezone b ON a.timezoneid = b.timezoneid
                                JOIN mpartybranch c ON a.workpartybranchid = c.partybranchid
                                JOIN morganizationunit d ON a.workouid = d.ouid
                                JOIN mdblevelsetting dblevelsetting ON TRUE

                            WHERE
                                a.usercode = @usercode
                                AND a.tenantid = @clientid;";



        public const string GET_USER_DETAIL_BY_GMAIL =
           @" Select
                 a.userId as UserId, 
                 a.userCode as UserCode, 
                 a.userName as UserName, 
                 a.Password as UserPassword, 
                 a.LastSuccessLoginOn as UserLastSuccessLoginOn, 
                 a.Status as CheckStatus, 
                 a.ValidFrom as UserValidFrom, 
                 a.ValidTo as UserValidTo ,
                 a.RoleId as RoleId ,
                 a.workouid as UserWorkOuId ,
                 d.ORGANIZATIONUNITCODE as UserWorkOuCode ,
                 d.ORGANIZATIONUNITNAME as UserWorkOuName ,


                 a.WORKPERIODID as UserWorkPeriodId ,
                 a.WorkPartyBranchId as UserWorkPartyBranchId ,
                 C.PartyId as UserWorkPartyId ,
                 a.WorkStoreId as UserWorkStoreId ,
                 a.WorkDate as UserWorkDate ,
                 a.ModeofOperation as CheckModeofOperation ,
                 a.UserCriteriaConfigId as UserCriteriaConfigId,
                 a.USERDATEFORMAT as UserDateFormat,
                 a.USERTIMEFORMAT as UserTimeFormat,
                 a.USERCURRENCYFORMAT as UserCurrencyFormat,
                 a.USERQUANTITYFORMAT as UserQuantityFormat,
                 a.DELIMITER as UserDelimiter,
                 a.USERLOGINNAME as UserLoginName,
                 a.PRIMARYMAIL as UserPrimaryMail, 
                  a.NOOFSESSIONS as CheckUserNoOfSessions,
                  a.LOGINONWORKDAYSONLY as CheckUserLoginonWorkDaysOnly,
                  a.LOGINFROMTIME as CheckUserLoginFromTime,
                  a.LOGINTOTIME as CheckUserLoginToTime,
                  a.LOGINMACHINE as UserLoginMachine,
                  a.LOGINIP as UserLoginIp, 
                  a.WORKFINANCEBOOKID as WorkFinanceBookId,
                  b.TIMEZONEID as  TimeZoneId,
                  b.UTCOFFSET as  TimeZone,
                  b.DISPLAYNAME as TimeZoneDisplayName,
                  ISNULL(vv.COUNTEROPERATIONID,-1) as CounterOperationId,
                  ISNULL(e.ISENCRYPTIONREQUIRED,0) as IsEncryptionRequired , 
                  dblevelsetting.SelectlistOperationType as SelectlistOperationType ,
                  dblevelsetting.ExpiryTime as ExpiryTime ,
                  dblevelsetting.GraceTime as GraceTime ,
                  dblevelsetting.IsIpBasedCheckingRequired as IsIpBasedCheckingRequired,  
                  dblevelsetting.ATTACHMENTOPTION as TempAttachmentOption,
                  a.MFAUSERSETTING as CheckUserMFAUserSetting,
                  a.MFAUSERACCOUNTID as UserMFAUserAccountId,
                  a.MFAUSERSECRETKEY as UserMFAUserSecretKey,
                  a.MFAQRCODE as UserMFAQRCode,
                  a.SHOWQRCODE as CheckUserShowQRCode,
                  a.PASSWORDCHANGEDON as UserPasswordChangedOn,
                  a.ISPARTNER as CheckIsPartner,
                  CASE WHEN ISNULL(emp.num,0)=0 THEN 1 ELSE 0 END AS CheckIsEmployee,
                  a.USERTYPE as UserType
                   from
                   MUser a
                   outer apply(
                      select Count(*)num from MEMPLOYEE WHERE EMPLOYEEID=a.userid
                   ) emp
                   left outer join(select COUNTEROPERATIONID, OUID, PERIODID,
                    COUNTERSTATUS, CASHIERID from tcounteroperation where 1=1)vv 
                    on(vv.OUID= a.workouid  and vv.PERIODID= a.workperiodid and 
                    vv.COUNTERSTATUS= 0 and CASHIERID = a.UserId)
                   left outer join(Select IsEncryptionRequired from MDEVELOPER 
                   where developerid = @developerid) e on(1=1),
                   MTIMEZONE b, 
                   MPartyBranch c,
                   MorganizationUnit d, 
                   MDBLEVELSETTING dblevelsetting
                   where
                        a.PRIMARYMAIL=@primarymail
                    and a.TIMEZONEID=b.TIMEZONEID
                    and a.WorkPartyBranchId= c.PartyBranchId
                    and a.WORKOUID= d.OUID
                    and a.TENANTID=@clientid ";
    }
}