namespace AccountsDAL.Query.BankBranch
{
    public static class BankBranchQB
    {
        public const string GET_BANKBRANCH = @"
                SELECT
                    -- Bank Branch
                    BB.BANKBRANCHID           AS BankBranchId,
                    BB.BANKBRANCHNAME         AS BankBranchBankBranchName,

                    -- Bank (MGCM)
                    BB.BANKID                 AS BankId,
                    G.GCMCODE                 AS BankCode,
                    G.GCMNAME                 AS BankName,

                    -- Address
                    BB.ADDRESSID              AS AddressId,
                    A.ADDRESSLINE1            AS AddressLine1,
                    A.ADDRESSLINE2            AS AddressLine2,
                    A.ADDRESSLINE3            AS AddressLine3,
                    A.ADDRESSLINE4            AS AddressLine4,
                    A.ADDRESSLINE5            AS AddressLine5,
                    A.ZIPCODE                 AS AddressZipCode,
                    A.MAIL                    AS AddressMail,

                    -- Country
                    BB.COUNTRYID              AS CountryId,
                    C.COUNTRYCODE             AS CountryCode,
                    C.COUNTRYNAME             AS CountryName,

                    -- State
                    BB.STATEID                AS StateId,
                    ST.STATECODE              AS StateCode,
                    ST.STATENAME              AS StateName,

                    -- City
                    BB.CITYID                 AS CityId,
                    CI.CITYCODE               AS CityCode,
                    CI.CITYNAME               AS CityName,

                    -- Contact Details
                    BB.MAILID                 AS BankBranchMailId,
                    BB.PHONE                  AS BankBranchPhone,
                    BB.MOBILE                 AS BankBranchMobile,
                    BB.IFSC                   AS BankBranchIfsc,
                    BB.SWIFT                  AS BankBranchSwift,
                    BB.BBAN                   AS BankBranchBban,

                    -- Other Details
                    BB.REMARKS                AS BankBranchRemarks,
                    BB.SORTORDER              AS BankBranchSortOrder,
                    BB.STATUS                 AS BankBranchStatus,
                    BB.VERSION                AS BankBranchVersion,
                    BB.SOURCETYPE             AS BankBranchSourceType,
                    BB.CREATEDBYID            AS BankBranchCreatedById,
                    BB.CREATEDON              AS BankBranchCreatedOn,
                    BB.MODIFIEDBYID           AS BankBranchModifiedById,
                    BB.MODIFIEDON             AS BankBranchModifiedOn,
                    BB.TENANTID               AS TenantId

                FROM MBANKBRANCH BB

                LEFT JOIN MGCM G
                       ON BB.BANKID = G.GCMID

                LEFT JOIN MADDRESS A
                       ON BB.ADDRESSID = A.ADDRESSID

                LEFT JOIN MCOUNTRY C
                       ON BB.COUNTRYID = C.COUNTRYID

                LEFT JOIN MSTATE ST
                       ON BB.STATEID = ST.STATEID

                LEFT JOIN MCITY CI
                       ON BB.CITYID = CI.CITYID

                LEFT JOIN MCLIENT CL
                       ON BB.TENANTID = CL.CLIENTID
                WHERE BB.BANKBRANCHID = @BankBranchId;";

        // Full-DTO bank branch list — same column set/joins as GET_BANKBRANCH (returns BankBranchDTO,
        // not BankBranchPicklistDTO) but for every matching row instead of one, criteria-filtered like
        // AccountQB.GET_ACCOUNT_LIST rather than paged — no FirstNumber/MaxResult, fixed ORDER BY only.
        public const string GET_BANKBRANCH_LIST = @"
                SELECT
                    -- Bank Branch
                    BB.BANKBRANCHID           AS BankBranchId,
                    BB.BANKBRANCHNAME         AS BankBranchBankBranchName,

                    -- Bank (MGCM)
                    BB.BANKID                 AS BankId,
                    G.GCMCODE                 AS BankCode,
                    G.GCMNAME                 AS BankName,

                    -- Address
                    BB.ADDRESSID              AS AddressId,
                    A.ADDRESSLINE1            AS AddressLine1,
                    A.ADDRESSLINE2            AS AddressLine2,
                    A.ADDRESSLINE3            AS AddressLine3,
                    A.ADDRESSLINE4            AS AddressLine4,
                    A.ADDRESSLINE5            AS AddressLine5,
                    A.ZIPCODE                 AS AddressZipCode,
                    A.MAIL                    AS AddressMail,

                    -- Country
                    BB.COUNTRYID              AS CountryId,
                    C.COUNTRYCODE             AS CountryCode,
                    C.COUNTRYNAME             AS CountryName,

                    -- State
                    BB.STATEID                AS StateId,
                    ST.STATECODE              AS StateCode,
                    ST.STATENAME              AS StateName,

                    -- City
                    BB.CITYID                 AS CityId,
                    CI.CITYCODE               AS CityCode,
                    CI.CITYNAME               AS CityName,

                    -- Contact Details
                    BB.MAILID                 AS BankBranchMailId,
                    BB.PHONE                  AS BankBranchPhone,
                    BB.MOBILE                 AS BankBranchMobile,
                    BB.IFSC                   AS BankBranchIfsc,
                    BB.SWIFT                  AS BankBranchSwift,
                    BB.BBAN                   AS BankBranchBban,

                    -- Other Details
                    BB.REMARKS                AS BankBranchRemarks,
                    BB.SORTORDER              AS BankBranchSortOrder,
                    BB.STATUS                 AS BankBranchStatus,
                    BB.VERSION                AS BankBranchVersion,
                    BB.SOURCETYPE             AS BankBranchSourceType,
                    BB.CREATEDBYID            AS BankBranchCreatedById,
                    BB.CREATEDON              AS BankBranchCreatedOn,
                    BB.MODIFIEDBYID           AS BankBranchModifiedById,
                    BB.MODIFIEDON             AS BankBranchModifiedOn,
                    BB.TENANTID               AS TenantId

                FROM MBANKBRANCH BB

                LEFT JOIN MGCM G
                       ON BB.BANKID = G.GCMID

                LEFT JOIN MADDRESS A
                       ON BB.ADDRESSID = A.ADDRESSID

                LEFT JOIN MCOUNTRY C
                       ON BB.COUNTRYID = C.COUNTRYID

                LEFT JOIN MSTATE ST
                       ON BB.STATEID = ST.STATEID

                LEFT JOIN MCITY CI
                       ON BB.CITYID = CI.CITYID

                LEFT JOIN MCLIENT CL
                       ON BB.TENANTID = CL.CLIENTID
                WHERE (@SearchText IS NULL OR @SearchText = '' OR BB.BANKBRANCHNAME LIKE '%' + @SearchText + '%')
                  AND (@StatusEquals IS NULL OR BB.STATUS = @StatusEquals)
                  AND (@StatusNotEquals IS NULL OR BB.STATUS <> @StatusNotEquals)
                ORDER BY BB.BANKBRANCHNAME;";

        public const string SAVE_BANKBRANCH = @"
            INSERT INTO MBANKBRANCH
            (
                BANKBRANCHID, BANKBRANCHNAME, BANKID,
                ADDRESSID,COUNTRYID, STATEID, CITYID, MAILID,PHONE, MOBILE, IFSC, SWIFT, BBAN, REMARKS,
                SORTORDER, STATUS, VERSION, SOURCETYPE,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            )
            VALUES
            (
                @BankBranchId, @BankBranchBankBranchName, @BankId,
                @AddressId, @CountryId,  @StateId, @CityId,@BankBranchMailId, @BankBranchPhone, @BankBranchMobile, @BankBranchIfsc,@BankBranchSwift,
                @BankBranchBban, @BankBranchRemarks, @BankBranchSortOrder, @BankBranchStatus, @BankBranchVersion, @BankBranchSourceType,
                @BankBranchCreatedById, @BankBranchCreatedOn, @BankBranchModifiedById, @BankBranchModifiedOn
            );";

        public const string UPDATE_BANKBRANCH = @"
            UPDATE MBANKBRANCH
            SET
                BANKBRANCHNAME      = @BankBranchBankBranchName,
                BANKID              = @BankId,
                ADDRESSID           = @AddressId,
                COUNTRYID           = @CountryId,
                STATEID             = @StateId,
                CITYID              = @CityId,
                MAILID              = @BankBranchMailId,
                PHONE               = @BankBranchPhone,
                MOBILE              = @BankBranchMobile,
                IFSC                = @BankBranchIfsc,
                SWIFT               = @BankBranchSwift,
                BBAN                = @BankBranchBban,
                REMARKS             = @BankBranchRemarks,
                SORTORDER           = @BankBranchSortOrder,
                STATUS              = @BankBranchStatus,
                VERSION             = @BankBranchVersion,
                SOURCETYPE          = @BankBranchSourceType,
                MODIFIEDBYID        = @BankBranchModifiedById,
                MODIFIEDON          = @BankBranchModifiedOn
            WHERE BANKBRANCHID      = @BankBranchId
              ;";

        public const string DELETE_BANKBRANCH = @"
            DELETE FROM MBANKBRANCH
            WHERE BANKBRANCHID = @BankBranchId
            ";


        public const string GET_SELECTLIST_BANKBRANCH = @"
                SELECT
                    BB.BANKBRANCHID      AS Id,
                    BB.BANKID            AS BankId,
                    G.GCMNAME            AS BankName,
                    BB.BANKBRANCHNAME    AS BranchName,
                    BB.IFSC              AS IFSC,
                    BB.SWIFT             AS BankSwift
                FROM MBANKBRANCH BB
                INNER JOIN MGCM G
                    ON G.GCMID = BB.BANKID
                ORDER BY BB.BANKBRANCHNAME;";
            }
}