namespace AccountsDAL.Query.Party
{
    public static class PartyQB
    {
        // Display Code/Name joins confirmed against legacy GB4 NHibernate mappings
        // (D:\GB4 Service\DAL\AccountsDAL\HibernateMapFile\{Party,Account}.hbm.xml,
        // D:\GB4 Service\DAL\AccountsDAL\HibernateMapFile\... AccountGroup) since none of these
        // tables had an existing GB5 join to crib column names from directly for this query.
        // TDS/Bank fields (TDSCategoryId, AccountIsTDSApplicable, AccountIsCompany, AccountTDSState,
        // AccountBankBranchId, AccountBankAccountNumber/Type, BankId/BankSwiftCode/BankIFSCCode) live
        // on MACCOUNT, not MPARTY — confirmed via legacy PartyBLL.NewPartySaveTuned, which auto-creates
        // a dedicated MACCOUNT row per Party (AccountId == PartyId, AccountType = 3) to hold them and
        // points P.ACCOUNTLINKID at it (see PartyBLL.LinkPartyAccountAsync). Pulled here off the same
        // ALK join already used for AccountLinkCode/Name, plus MTDSCATEGORY/MBANKBRANCH lookups
        // mirroring AccountQB.GET_ACCOUNT_ID's existing, confirmed joins.
        public const string GET_PARTY = @"
SELECT
    P.PARTYID AS PartyId,
    P.PARTYCODE AS PartyCode,
    P.PARTYNAME AS PartyName,
    P.PARTYTYPE AS PartyType,
    P.CODEDEFINEID AS CodeDefineId,
    ISNULL(CD.CATEGORYCODE, 'NONE') AS CodeDefineCategoryCode,
    ISNULL(CD.CATEGORYNAME, 'NONE') AS CodeDefineCategoryName,
    P.PARTYFAVOURINGNAME AS PartyFavouringName,
    P.PARTYSHORTNAME AS PartyShortName,
    P.PARTYCOMBINEID AS PartyCombineId,
    ISNULL(PC.PARTYCODE, 'NONE') AS PartyCombineCode,
    ISNULL(PC.PARTYNAME, 'NONE') AS PartyCombineName,
    P.ISVENDOR AS PartyIsVendor,
    P.ISSERVICEPROVIDER AS PartyIsServiceProvider,
    P.ISACCOUNT AS PartyIsAccount,
    P.ISCUSTOMER AS PartyIsCustomer,
    P.ISPROSPECT AS PartyIsProspect,
    P.ISBANK AS PartyIsBank,
    P.CONTROLACCOUNTID AS ControlAccountId,
    ISNULL(CA.ACCOUNTCODE, 'NONE') AS ControlAccountCode,
    ISNULL(CA.ACCOUNTNAME, 'NONE') AS ControlAccountName,
    CA.CODEDEFINEID AS ControlAccountCodeDefineId,
    P.ACCOUNTLINKID AS AccountLinkId,
    ISNULL(ALK.ACCOUNTCODE, 'NONE') AS AccountLinkCode,
    ISNULL(ALK.ACCOUNTNAME, 'NONE') AS AccountLinkName,
    ALK.TDSCATEGORYID AS TDSCategoryId,
    ISNULL(TDS.TDSCATEGORYCODE, 'NONE') AS TDSCategoryCode,
    ISNULL(TDS.TDSCATEGORYNAME, 'NONE') AS TDSCategoryName,
    ALK.ISTDSAPPLICABLE AS AccountIsTDSApplicable,
    ALK.ISCOMPANY AS AccountIsCompany,
    ISNULL(ALK.TDSSTATE, 'NONE') AS AccountTDSState,
    ALK.ACCOUNTBANKBRANCHID AS AccountBankBranchId,
    ISNULL(ALK.BANKACCOUNTNUMBER, 'NONE') AS AccountBankAccountNumber,
    ISNULL(ALK.BANKACCOUNTTYPE, 'NONE') AS AccountBankAccountType,
    BB.BANKID AS BankId,
    ISNULL(BB.SWIFT, 'NONE') AS BankSwiftCode,
    ISNULL(BB.IFSC, 'NONE') AS BankIFSCCode,
    BB.BANKBRANCHID AS BankBranchId,
    P.ACCOUNTGROUPID AS AccountGroupId,
    ISNULL(AG.ACCOUNTGROUPCODE, 'NONE') AS AccountGroupCode,
    ISNULL(AG.ACCOUNTGROUPNAME, 'NONE') AS AccountGroupName,
    P.CURRENCYID AS CurrencyId,
    ISNULL(CUR.CURRENCYCODE, 'NONE') AS CurrencyCode,
    ISNULL(CUR.CURRENCYNAME, 'NONE') AS CurrencyName,
    P.DEFAULTADDRESSID AS DefaultAddressId,
    P.SORTORDER AS PartySortOrder,
    P.STATUS AS PartyStatus,
    P.VERSION AS PartyVersion,
    P.SOURCETYPE AS PartySourceType,
    P.TENANTID AS TenantId,
    P.THUMBNAIL AS PartyThumbNail,
    P.CREATEDBYID AS PartyCreatedById,
    ISNULL(U_CREATED.USERNAME, 'NONE') AS PartyCreatedByName,
    P.CREATEDON AS PartyCreatedOn,
    P.MODIFIEDBYID AS PartyModifiedById,
    ISNULL(U_MODIFIED.USERNAME, 'NONE') AS PartyModifiedByName,
    P.MODIFIEDON AS PartyModifiedOn
FROM MPARTY P
LEFT JOIN MCODEDEFINE CD ON CD.CODEDEFINEID = P.CODEDEFINEID
LEFT JOIN MPARTY PC ON PC.PARTYID = P.PARTYCOMBINEID
LEFT JOIN MACCOUNT CA ON CA.ACCOUNTID = P.CONTROLACCOUNTID
LEFT JOIN MACCOUNT ALK ON ALK.ACCOUNTID = P.ACCOUNTLINKID
LEFT JOIN MTDSCATEGORY TDS ON TDS.TDSCATEGORYID = ALK.TDSCATEGORYID
LEFT JOIN MBANKBRANCH BB ON BB.BANKBRANCHID = ALK.ACCOUNTBANKBRANCHID
LEFT JOIN MACCOUNTGROUP AG ON AG.ACCOUNTGROUPID = P.ACCOUNTGROUPID
LEFT JOIN MUSER U_CREATED ON U_CREATED.USERID = P.CREATEDBYID
LEFT JOIN MUSER U_MODIFIED ON U_MODIFIED.USERID = P.MODIFIEDBYID
LEFT JOIN MCURRENCY CUR ON CUR.CURRENCYID = P.CURRENCYID
WHERE P.PARTYID = @PartyId;";

        // @SearchText filters PartyCode/PartyName/PartyShortName (LIKE, OR'd) — mirrors the
        // picklist's typed-search criteria (FieldName PartyCode/PartyName, OperationType Like) sent
        // from the frontend, which this query previously ignored entirely (always returned the same
        // static first-N-by-name page regardless of what was typed).
        // FirstNumber=-1/MaxResult=-1 is this app's "no paging, return everything matching" sentinel
        // (see AccountQB.Get_Select_List_Account) — without the explicit case below, the BETWEEN
        // clause collapses to "BETWEEN -1 AND -3", an inverted range that always matches zero rows.
        public const string GET_SELECTLIST_PARTY = @"
WITH PagedParty AS
(
    SELECT
        P.PARTYID AS Id,
        P.PARTYCODE AS Code,
        P.PARTYNAME AS Name,
        P.PARTYSHORTNAME AS ShortName,
        ROW_NUMBER() OVER (ORDER BY P.PARTYNAME) AS RowNum
    FROM MPARTY P
    WHERE P.STATUS <> 2
      AND (@SearchText IS NULL OR @SearchText = ''
           OR P.PARTYCODE LIKE '%' + @SearchText + '%'
           OR P.PARTYNAME LIKE '%' + @SearchText + '%'
           OR P.PARTYSHORTNAME LIKE '%' + @SearchText + '%')
)
SELECT Id, Code, Name, ShortName
FROM PagedParty
WHERE (@FirstNumber = -1 AND @MaxResult = -1)
   OR (RowNum BETWEEN @FirstNumber AND (@FirstNumber + @MaxResult - 1))
ORDER BY RowNum;";

        public const string SAVE_PARTY = @"
INSERT INTO MPARTY
(
    PARTYID,
    PARTYCODE,
    PARTYNAME,
    PARTYTYPE,
    PARTYFAVOURINGNAME,
    PARTYSHORTNAME,
    PARTYCOMBINEID,
    ISVENDOR,
    ISSERVICEPROVIDER,
    ISACCOUNT,
    ISCUSTOMER,
    ISPROSPECT,
    ISBANK,
    CONTROLACCOUNTID,
    ACCOUNTLINKID,
    ACCOUNTGROUPID,
    CURRENCYID,
    DEFAULTADDRESSID,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    TENANTID,
    THUMBNAIL,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    CODEDEFINEID
)
VALUES
(
    @PartyId,
    @PartyCode,
    @PartyName,
    @PartyType,
    @PartyFavouringName,
    @PartyShortName,
    @PartyCombineId,
    @PartyIsVendor,
    @PartyIsServiceProvider,
    @PartyIsAccount,
    @PartyIsCustomer,
    @PartyIsProspect,
    @PartyIsBank,
    @ControlAccountId,
    @AccountLinkId,
    @AccountGroupId,
    @CurrencyId,
    @DefaultAddressId,
    @PartySortOrder,
    @PartyStatus,
    @PartyVersion,
    @PartySourceType,
    @TenantId,
    @PartyThumbNail,
    @PartyCreatedById,
    @PartyCreatedOn,
    @PartyModifiedById,
    @PartyModifiedOn,
    @CodeDefineId
);";

        public const string UPDATE_PARTY = @"
UPDATE MPARTY SET
    PARTYCODE = @PartyCode,
    PARTYNAME = @PartyName,
    PARTYTYPE = @PartyType,
    PARTYFAVOURINGNAME = @PartyFavouringName,
    PARTYSHORTNAME = @PartyShortName,
    PARTYCOMBINEID = @PartyCombineId,
    ISVENDOR = @PartyIsVendor,
    ISSERVICEPROVIDER = @PartyIsServiceProvider,
    ISACCOUNT = @PartyIsAccount,
    ISCUSTOMER = @PartyIsCustomer,
    ISPROSPECT = @PartyIsProspect,
    ISBANK = @PartyIsBank,
    CONTROLACCOUNTID = @ControlAccountId,
    ACCOUNTLINKID = @AccountLinkId,
    ACCOUNTGROUPID = @AccountGroupId,
    CURRENCYID = @CurrencyId,
    DEFAULTADDRESSID = @DefaultAddressId,
    SORTORDER = @PartySortOrder,
    STATUS = @PartyStatus,
    VERSION = @PartyVersion,
    SOURCETYPE = @PartySourceType,
    MODIFIEDBYID = @PartyModifiedById,
    MODIFIEDON = @PartyModifiedOn,
    CODEDEFINEID = @CodeDefineId
WHERE PARTYID = @PartyId;";

        public const string DELETE_PARTY_ACCOUNT_OU = @"
DELETE FROM MPARTYACCOUNTOU
WHERE PARTYID = @PartyId;";

        // Must be read BEFORE any deletes — MPARTYBRANCH.DEFAULTADDRESSID is FK'd to MADDRESS
        // (FKMPARTYBRANCH_DEFAULTADDRESSID), so once the branch rows are deleted we can no longer
        // find them via a subquery to know which MADDRESS rows to clean up.
        public const string GET_PARTY_BRANCH_IDS = @"
SELECT PARTYBRANCHID FROM MPARTYBRANCH WHERE PARTYID = @PartyId;";

        // MPARTYBRANCHADDON.PARTYBRANCHID is a confirmed column (PartyDetailQB.GET_PARTYBRANCH_ADDON_BASED_ON_ID).
        // Must run before DELETE_PARTY_BRANCHES since it joins against MPARTYBRANCH to find this party's branches.
        public const string DELETE_PARTY_BRANCH_ADDON = @"
DELETE FROM MPARTYBRANCHADDON
WHERE PARTYBRANCHID IN (SELECT PARTYBRANCHID FROM MPARTYBRANCH WHERE PARTYID = @PartyId);";

        public const string DELETE_PARTY_BRANCHES = @"
DELETE FROM MPARTYBRANCH
WHERE PARTYID = @PartyId;";

        // Must run AFTER DELETE_PARTY_BRANCHES — MPARTYBRANCH.DEFAULTADDRESSID references MADDRESS,
        // so the referencing branch rows have to be gone before their address rows can be deleted.
        // @BranchIds is the branch id list captured via GET_PARTY_BRANCH_IDS before any deletes ran.
        public const string DELETE_PARTY_BRANCH_ADDRESS = @"
DELETE FROM MADDRESS
WHERE OBJECTTYPEID = @ObjectTypeId
  AND OBJECTID IN @BranchIds;";

        public const string DELETE_PARTY = @"
DELETE FROM MPARTY
WHERE PARTYID = @PartyId;";
    }
}