namespace FrameworkDAL.Query.MessageHub
{
    /// <summary>
    /// QueryBuilder for MessageHub
    /// Handles MessageHub template resolution
    /// EntityId may be positive (business entity)
    /// or negative (system/framework entity logic)
    /// </summary>
    public static class MessageHubGeneratorQB
    {
        /// <summary>
        /// Get all active templates + actions for a given EntityId.
        /// Handles both positive and negative IDs using MENTITY only.
        /// </summary>
        public const string GET_TEMPLATES_BY_ENTITYID = @"SELECT
    E.ENTITYID                  AS EntityId,
    E.ENTITYCODE                AS EntityCode,
    E.ENTITYNAME                AS EntityName,

    ET.EVENTTYPEID              AS EventTypeId,
    ET.EVENTTYPECODE            AS EventTypeCode,
    ET.EVENTTYPENAME            AS EventTypeName,

    MT.MAILTEMPLATEID           AS MailTemplateId,
    MT.MAILTEMPLATECODE         AS MailTemplateCode,
    MT.MAILTEMPLATENAME         AS MailTemplateName,
    MT.SUBJECT                  AS Subject,
    MT.BODY                     AS BodyHtml,

    A.ACTIONID                  AS ActionId,
    A.ACTIONTYPE                AS ActionType,

    ETAD.EVENTTYPEACTIONDETAILID AS EventTypeActionDetailId,
    ETAD.ISDIRECTCALL           AS IsDirectCall,
    ETAD.ISSCHEDULERCALL        AS IsSchedulerCall

FROM MEVENTTYPE ET
INNER JOIN MENTITY E
    ON ET.ENTITYID = E.ENTITYID
   AND E.STATUS = 1

INNER JOIN MMAILTEMPLATE MT
    ON MT.EVENTTYPEID = ET.EVENTTYPEID
   AND MT.STATUS = 1

INNER JOIN MACTION A
    ON A.TEMPLATEID = MT.MAILTEMPLATEID
   AND A.EVENTTYPEID = ET.EVENTTYPEID
   AND A.STATUS = 1

INNER JOIN MEVENTTYPEACTION ETA
    ON ETA.EVENTTYPEID = ET.EVENTTYPEID
   AND ETA.STATUS = 1

INNER JOIN MEVENTTYPEACTIONDETAIL ETAD
    ON ETAD.EVENTTYPEACTIONID = ETA.EVENTTYPEACTIONID
   AND ETAD.ACTIONID = A.ACTIONID
   AND ETAD.ISACTIVE = 1

WHERE ET.ENTITYID = @EntityId

ORDER BY MT.MAILTEMPLATEID, A.ACTIONID;
        ";

        /// <summary>
        /// Insert message into TMESSAGEHUB
        /// </summary>
        public const string INSERT_MESSAGEHUB = @"
            INSERT INTO TMESSAGEHUB
            (
                MESSAGEHUBID,
                ACTIONID,
                ACTIONTYPE,
                MAILTEMPLATEID,
                RECIPIENT,
                MESSAGETEXT,
                DELIVERYSTATUS,
                SORTORDER,
                STATUS,
                VERSION,
                SOURCETYPE,
                TENANTID,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            )
            VALUES
            (
                @MessageHubId,
                @ActionId,
                @ActionType,
                @MailTemplateId,
                @Recipient,
                @MessageText,
                0,
                @SortOrder,
                1,
                1,
                @SourceType,
                @TenantId,
                @CreatedById,
                GETDATE(),
                @ModifiedById,
                GETDATE()
            );
        ";

        /// <summary>
        /// Common patch query.
        /// Column aliases MUST match template placeholders.
        /// </summary>
        public const string GET_MESSAGE_HUB_PATCH_QUERY = @"

SELECT

/* =========================
   MUSER
   ========================= */
U.USERID                 AS UserId,
U.USERCODE               AS UserCode,
U.USERNAME               AS UserName,
U.USERLOGINNAME          AS UserLoginName,
U.DOB                    AS UserDateOfBirth,
U.USERTYPE               AS UserType,
U.NATURE                 AS UserNature,
U.SECURITYLEVEL          AS UserSecurityLevel,
U.PRIMARYMAIL            AS UserPrimaryMail,
U.PRIMARYMOBILE          AS UserPrimaryMobile,
U.PRIMARYPHONE           AS UserPrimaryPhone,
U.WORKOUID               AS UserWorkOUId,
U.WORKPERIODID           AS UserWorkPeriodId,
U.WORKPARTYBRANCHID      AS UserWorkPartyBranchId,
U.WORKDATE               AS UserWorkDate,
U.LASTSUCCESSLOGINON     AS UserLastSuccessLoginOn,
U.LASTFAILUREATTEMPTON   AS UserLastFailureAttemptOn,
U.TIMEZONEID             AS TimeZoneId,
U.LANGUAGEID             AS LanguageId,
U.TENANTID               AS TenantId,
U.STATUS                 AS UserStatus,
U.THUMBNAIL              AS UserThumbnail,

/* =========================
   MEMPLOYEE
   ========================= */
E.EMPLOYEEID                 AS EmployeeId,
E.EMPLOYEECODE               AS EmployeeCode,
E.EMPLOYEENAME               AS EmployeeName,
E.FIRSTNAME                  AS EmployeeFirstName,
E.MIDDLENAME                 AS EmployeeMiddleName,
E.LASTNAME                   AS EmployeeLastName,
E.DEPARTMENTID               AS EmployeeDepartmentId,
E.DESIGNATIONID              AS EmployeeDesignationId,
E.EMPLOYEETYPEID             AS EmployeeTypeId,
E.DATEOFBIRTH                AS EmployeeDateOfBirth,
E.DATEOFJOINING              AS EmployeeDateOfJoining,
E.PRIMARYEMAILID             AS EmployeePrimaryEmail,
E.PRIMARYMOBILE              AS EmployeePrimaryMobile,
E.REPORTINGTOEMPLOYEEID      AS ReportingToEmployeeId,
E.WORKOUID                   AS EmployeeWorkOUId,
E.CONTACTID                  AS EmployeeContactId,
E.STATUS                     AS EmployeeStatus,

/* =========================
   MCONTACT
   ========================= */
MC.CONTACTID             AS ContactId,
MC.NAME                  AS ContactName,
MC.MAILID                AS ContactMailId,
MC.MOBILENO              AS ContactMobileNo,
MC.PHONENO               AS ContactPhoneNo,

/* =========================
   ORGANIZATION UNIT
   ========================= */
OU.OUID                          AS OUId,
OU.ORGANIZATIONUNITCODE          AS OUCode,
OU.ORGANIZATIONUNITNAME          AS OUName,
OU.ORGANIZATIONUNITSHORTNAME     AS OUShortName,
OU.COMPANYID                     AS OUCompanyId,
OU.BRANCHID                      AS OUBranchId,
OU.DIVISIONID                    AS OUDivisionId,
OU.CURRENCYID                    AS OUCurrencyId,
OU.COLORID                       AS OUColorId,
OU.CALENDARID                    AS OUCalendarId,
OU.DEFAULTADDRESSID              AS OUDefaultAddressId,
OU.STATUS                        AS OUStatus,

/* =========================
   ORGANIZATION GROUP
   ========================= */
OG.ORGANIZATIONGROUPID       AS OrganizationGroupId,
OG.ORGANIZATIONGROUPCODE     AS OrganizationGroupCode,
OG.ORGANIZATIONGROUPNAME     AS OrganizationGroupName,
OG.STATUS                    AS OrganizationGroupStatus,

/* =========================
   PARTY
   ========================= */
P.PARTYID                AS PartyId,
P.PARTYCODE              AS PartyCode,
P.PARTYNAME              AS PartyName,
P.PARTYSHORTNAME         AS PartyShortName,
P.ISVENDOR               AS IsVendor,
P.ISCUSTOMER             AS IsCustomer,
P.ISACCOUNT              AS IsAccount,
P.CURRENCYID             AS PartyCurrencyId,
P.STATUS                 AS PartyStatus,

/* =========================
   PARTY BRANCH
   ========================= */
PB.PARTYBRANCHID         AS PartyBranchId,
PB.PARTYBRANCHCODE       AS PartyBranchCode,
PB.PARTYBRANCHNAME       AS PartyBranchName,
PB.PARTYBRANCHSHORTNAME  AS PartyBranchShortName,
PB.PARTYBRANCHTYPE       AS PartyBranchType,
PB.CURRENCYID            AS PartyBranchCurrencyId,
PB.CREDITDAYS            AS PartyBranchCreditDays,
PB.NOOFBILLS             AS PartyBranchNoOfBills,
PB.DEFAULTADDRESSID      AS PartyBranchDefaultAddressId,
PB.STATUS                AS PartyBranchStatus,

/* =========================
   PERIOD
   ========================= */
PER.PERIODID         AS PeriodId,
PER.PERIODCODE       AS PeriodCode,
PER.PERIODNAME       AS PeriodName,
PER.FROMDATE         AS PeriodFromDate,
PER.TODATE           AS PeriodToDate,
PER.STATUS           AS PeriodStatus,

/* =========================
   ADDRESS
   ========================= */
A.ADDRESSID          AS AddressId,
A.ADDRESSLINE1       AS AddressLine1,
A.ADDRESSLINE2       AS AddressLine2,
A.ADDRESSLINE3       AS AddressLine3,
A.ZIPCODE            AS ZipCode,
A.PHONE              AS AddressPhone,
A.MOBILE             AS AddressMobile,
A.GSTIN              AS AddressGSTIN,
A.PAN                AS AddressPAN,
A.GEOLOCATION        AS AddressGeoLocation,

/* =========================
   LOCATION
   ========================= */
L.LOCATIONID         AS LocationId,
L.LOCATIONCODE       AS LocationCode,
L.LOCATIONNAME       AS LocationName,
L.GEOCODE            AS LocationGeoCode,
L.GEOLOCATION.STAsText() AS LocationGeoLocation,
L.SHAPETYPE          AS LocationShapeType,
L.RADIUS             AS LocationRadius,

/* =========================
   CITY / STATE / COUNTRY
   ========================= */
CI.CITYID            AS CityId,
CI.CITYCODE          AS CityCode,
CI.CITYNAME          AS CityName,

S.STATEID            AS StateId,
S.STATECODE          AS StateCode,
S.STATENAME          AS StateName,

CO.COUNTRYID         AS CountryId,
CO.COUNTRYCODE       AS CountryCode,
CO.COUNTRYNAME       AS CountryName,
CO.ISDCODE           AS CountryISDCode,
CO.GSTCOUNTRYCODE    AS CountryGSTCountryCode,

/* =========================
   LATEST CALL (EXTENDED)
   ========================= */

C.OUID                         AS CallOUId,
C.BIZTRANSACTIONTYPEID         AS CallBizTransactionTypeId,
C.PERIODID                     AS CallPeriodId,
C.REFERENCENUMBER              AS CallReferenceNumber,
C.REFERENCEDATE                AS CallReferenceDate,
C.OBJECTTYPEID                 AS CallObjectTypeId,
C.OBJECTID                     AS CallObjectId,
C.PARTYID                      AS CallPartyId,
C.PARTYBRANCHID                AS CallPartyBranchId,
C.EMPLOYEEID                   AS CallEmployeeId,
C.CONTACTID                    AS CallContactId,
C.ADDRESSID                    AS CallAddressId,
C.REPORTEDBY                   AS CallReportedBy,
C.REPORTEDBYID                 AS CallReportedById,
C.RECEIVEDBYID                 AS CallReceivedById,
C.CALLRECEIVEDMODE             AS CallReceivedMode,
C.CALLTYPEID                   AS CallTypeId,
C.CALLGENERATIONTYPE           AS CallGenerationType,
C.CONTRACTID                   AS CallContractId,
C.CONTRACTCYCLENUMBER          AS CallContractCycleNumber,
C.CALLNATUREID                 AS CallNatureId,
C.CALLPRIORITYID               AS CallPriorityId,
C.PROBLEMDATE                  AS CallProblemDate,
C.PROBLEMTIME                  AS CallProblemTime,
C.PROMISSEDDATE                AS CallPromisedDate,
C.ALLOTEDTOTYPE                AS CallAllotedToType,
C.ALLOTEDTOEMPLOYEEID          AS CallAllotedToEmployeeId,
C.ALLOTEDTOCONTACTID           AS CallAllotedToContactId,
C.ALLOTEDTOPARTYID             AS CallAllotedToPartyId,
C.CALLONOBJECTTYPEID           AS CallOnObjectTypeId,
C.CALLONOBJECTID               AS CallOnObjectId,
C.CALLONNAME                   AS CallOnName,
C.COVERAGESTATUS               AS CallCoverageStatus,
C.REPORTEDPROBLEM              AS CallReportedProblem,
C.ACTIONTAKEN                  AS CallActionTaken,
C.CUSTOMERREMARKS              AS CallCustomerRemarks,
C.PROBLEMNATUREID              AS CallProblemNatureId,
C.PLANDATE                     AS CallPlanDate,
C.POSSIBLEDATE                 AS CallPossibleDate,
C.ESTIMATEDLABOURAMOUNT        AS CallEstimatedLabourAmount,
C.ESTIMATEDSPARESAMOUNT        AS CallEstimatedSparesAmount,
C.ESTIMATEDOTHERSAMOUNT        AS CallEstimatedOthersAmount,
C.ESTIMATEDTOTALAMOUNT         AS CallEstimatedTotalAmount,
C.ESTIMATEDBYID                AS CallEstimatedById,
C.LABOURAMOUNT                 AS CallLabourAmount,
C.SPARESAMOUNT                 AS CallSpareAmount,
C.OTHERAMOUNT                  AS CallOtherAmount,
C.TOTALAMOUNT                  AS CallTotalAmount,
C.PERSONID                     AS CallPersonId,
C.INDOOROUTDOOR                AS CallIndoorOutdoor,
C.ISITEMRECEIVED               AS CallIsItemReceived,
C.ITEMRECEIVEDREMARKS          AS CallItemReceivedRemarks,
C.ISSTANDBYGIVEN               AS CallIsStandbyGiven,
C.STANDBYITEMID                AS CallStandbyItemId,
C.STANDBYREMARKS               AS CallStandbyRemarks,
C.COMPLETEDBYID                AS CallCompletedById,
C.COMPLETEDONTIME              AS CallCompletedOnTime,
C.CUSTOMERFEEDBACK             AS CallCustomerFeedback,
C.CLOSEDBYID                   AS CallClosedById,
C.CLOSEDON                     AS CallClosedOn,
C.CLOSEDONTIME                 AS CallClosedOnTime,
C.REASONID                     AS CallReasonId,
C.ISACCOUNTED                  AS CallIsAccounted,
C.STATUS                       AS CallRecordStatus,
C.VERSION                      AS CallVersion,
C.CREATEDBYID                  AS CallCreatedById,
C.CREATEDON                    AS CallCreatedOn,
C.MODIFIEDBYID                 AS CallModifiedById,
C.MODIFIEDON                   AS CallModifiedOn,
C.ALLOCATIONID                 AS CallAllocationId,
C.ROOTCAUSEID                  AS CallRootCauseId,
C.ROOTCAUSEDESCRIPTION         AS CallRootCauseDescription,
C.CALLONOBJECTSTATUS           AS CallOnObjectStatus,
C.MANUFACTURERSLNO             AS CallManufacturerSlNo,
C.ALLOTEDMODE                  AS CallAllotedMode,
C.ALLOTEDTONUMBER              AS CallAllotedToNumber,
C.TEMPLATEDATA                 AS CallTemplateData,

/* =========================
   LATEST VISITOR PASS
   ========================= */
VP.VISITORPASSID     AS VisitorPassId,
VP.PASSNUMBER        AS VisitorPassNumber,
VP.PASSDATE          AS VisitorPassDate,
VP.VISITORNAME       AS VisitorName,

PUNCH.PUNCHINDATE    AS VisitorPunchInDate,
PUNCH.PUNCHOUTDATE   AS VisitorPunchOutDate

FROM MUSER U

LEFT JOIN MEMPLOYEE E
    ON E.EMPLOYEEID = U.USERID
   AND E.TENANTID = U.TENANTID

LEFT JOIN MCONTACT MC
    ON MC.CONTACTID = E.CONTACTID

LEFT JOIN MORGANIZATIONUNIT OU
    ON OU.OUID = E.WORKOUID

LEFT JOIN MORGANIZATIONGROUP OG
    ON OG.ORGANIZATIONGROUPID = OU.PERIODGROUP

LEFT JOIN MPARTYBRANCH PB
    ON PB.PARTYBRANCHID = U.WORKPARTYBRANCHID

LEFT JOIN MPARTY P
    ON P.PARTYID = PB.PARTYID

LEFT JOIN MPERIOD PER
    ON PER.PERIODID = U.WORKPERIODID

LEFT JOIN MADDRESS A
    ON A.ADDRESSID = PB.DEFAULTADDRESSID

LEFT JOIN MLOCATION L
    ON L.LOCATIONID = A.LOCATIONID

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

LEFT JOIN MSTATE S
    ON S.STATEID = CI.STATEID

LEFT JOIN MCOUNTRY CO
    ON CO.COUNTRYID = CI.COUNTRYID

OUTER APPLY (
    SELECT TOP 1 *
    FROM TCALL C
    WHERE C.EMPLOYEEID = E.EMPLOYEEID
    ORDER BY C.CALLDATE DESC
) C

OUTER APPLY (
    SELECT TOP 1 *
    FROM TVISITORPASS VP
    WHERE VP.EMPLOYEEID = E.EMPLOYEEID
    ORDER BY VP.PASSDATE DESC
) VP

OUTER APPLY (
    SELECT TOP 1 *
    FROM TVISITORPUNCH PUNCH
    WHERE PUNCH.VISITORPASSID = VP.VISITORPASSID
    ORDER BY PUNCH.PUNCHINDATE DESC
) PUNCH

WHERE U.USERID = @userid;
";
    }
}