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

namespace MarketingDAL.Query.Lead
{
    public static class LeadQB
    {
        public const string GET_LEAD = @"SELECT
    -- Lead Region
    LEAD.LEADID AS LeadId,
    LEAD.LEADCODE AS LeadCode,
    LEAD.LEADNAME AS LeadName,
    LEAD.DESCRIPTION AS LeadDescription,
    LEAD.LEADDATE AS LeadDate,
    LEAD.REFERENCENUMBER AS LeadReferenceNumber,
    LEAD.REFERENCEDATE AS LeadReferenceDate,
    LEAD.TAGS AS LeadTags,
    LEAD.TURNOVER AS LeadTurnOver,
    LEAD.NOOFEMPLOYEES AS LeadNoOfEmployees,
    LEAD.RATING AS LeadRating,
    LEAD.CAMPAIGNID AS LeadCampaignId,
    LEAD.GENERATEDON AS LeadGeneratedOn,
    LEAD.ORDERCONFIDENCE AS LeadOrderConfidence,
    LEAD.EXPECTEDDATE AS LeadExpectedDate,
    LEAD.EXPECTEDFCREVENUE AS LeadExpectedFCRevenue,
    LEAD.CURRENCYCONVERSION AS LeadCurrencyConversion,
    LEAD.EXPECTEDREVENUE AS LeadExpectedRevenue,
    LEAD.REVENUECONFIDENCE AS LeadRevenueConfidence,
    LEAD.LEADINFOLEVEL AS LeadLeadInfoLevel,
    LEAD.REMARKS AS LeadRemarks,
    LEAD.SUCESSSTATUS AS LeadSuccessStatus,
    LEAD.LEADSTATUS AS LeadLeadStatus,
    LEAD.ORDERWONLOSTDATE AS LeadOrderWonLostDate,
    LEAD.LOSTREMARKS AS LeadLostRemarks,
    LEAD.ALLOTTEDTOIDS AS AllottedToIds,
    LEAD.CONTACTIDS AS ContactIds,
    LEAD.LEADNAME AS AllottedToName,

    -- OU Region
    LEAD.OUID AS OUId,
    OU.ORGANIZATIONUNITCODE AS OUCode,
    OU.ORGANIZATIONUNITNAME AS OUName,

    -- Party Region
    LEAD.PARTYID AS PartyId,
    PARTY.PARTYCODE AS PartyCode,
    PARTY.PARTYNAME AS PartyName,

    -- Party Branch Region
    LEAD.PARTYBRANCHID AS PartyBranchId,
    PB.PARTYBRANCHCODE AS PartyBranchCode,
    PB.PARTYBRANCHNAME AS PartyBranchName,

    -- Contact Region
    LEAD.CONTACTID AS ContactId,
    CONTACT.FIRSTNAME AS ContactFirstName,
    CONTACT.LASTNAME AS ContactLastName,

    -- Lead Category Region
    LEAD.LEADCATEGORYID AS LeadCategoryId,
    LC.LEADCATEGORYCODE AS LeadCategoryCode,
    LC.LEADCATEGORYNAME AS LeadCategoryName,
    LC.ITEMCATEGORYIDS AS LeadCategoryItemCategoryIds,

    -- Lead Cycle Region
    LEAD.LEADCYCLEID AS LeadCycleId,
    LCYCLE.LEADCYCLECODE AS LeadCycleCode,
    LCYCLE.LEADCYCLENAME AS LeadCycleName,

    -- Lead Cycle Detail Region
    LEAD.LEADCYCLEDETAILID AS LeadCycleDetailId,
    LCD.STAGECODE AS LeadCycleDetailStageCode,
    LCD.STAGENAME AS LeadCycleDetailStageName,
    LCD.PRESTAGESLNO AS LeadCycleDetailPreStageSlNo,
    LCD.ISOPEN AS LeadCycleDetailIsOpen,

    -- Employee Region
    LEAD.INCHARGEID AS InchargeId,
    INCHARGE.EMPLOYEECODE AS InchargeCode,
    INCHARGE.EMPLOYEENAME AS InchargeName,

    -- Lead Source Detail Region
    LEAD.LEADSOURCEDETAILID AS LeadSourceDetailId,
    LSD.DETAIL AS LeadSourceDetailDetail,
    LSD.REMARKS AS LeadSourceDetailRemarks,
    LSD.LEADSOURCEID AS LeadSourceDetailLeadSourceId,

    -- Source Incharge Region
    LEAD.SOURCEINCHARGEID AS SourceInchargeId,
    SINC.EMPLOYEECODE AS SourceInchargeCode,
    SINC.EMPLOYEENAME AS SourceInchargeName,

    -- Current Incharge Region
    LEAD.CURRENTINCHARGEID AS CurrentInchargeId,
    CINC.EMPLOYEECODE AS CurrentInchargeCode,
    CINC.EMPLOYEENAME AS CurrentInchargeName,

    -- Currency Region
    LEAD.CURRENCYID AS CurrencyId,
    CUR.CURRENCYCODE AS CurrencyCode,
    CUR.CURRENCYNAME AS CurrencyName,

    -- Lead Chart Region
    LEAD.LEADCHARTID AS LeadChartId,

    -- Lost Reason Region
    LEAD.LOSTREASONID AS LostReasonId,
    LR.REASONCODE AS LostReasonCode,
    LR.REASONNAME AS LostReasonName,

    -- Base Region
    LEAD.CREATEDBYID AS LeadCreatedById,
    LEAD.CREATEDON AS LeadCreatedOn,
    LEAD.MODIFIEDBYID AS LeadModifiedById,
    LEAD.MODIFIEDON AS LeadModifiedOn,
    CREBY.USERNAME AS LeadCreatedByName,
    MODBY.USERNAME AS LeadModifiedByName,
    LEAD.LEADCYCLEID AS LeadBaseClinetId,

    -- Standard Region
    LEAD.SOURCETYPE AS LeadSortOrder,
    LEAD.STATUS AS Status,
    LEAD.VERSION AS LeadVersion,
    LEAD.SOURCETYPE AS LeadSourceType,

    -- Additional Fields
    LEAD.LEADDATE AS StringLeadDate,
    LEAD.REFERENCEDATE AS StringLeadReferenceDate,
    LEAD.GENERATEDON AS StringLeadGeneratedOn,
    LEAD.EXPECTEDDATE AS StringLeadExpectedDate,
    LEAD.ORDERWONLOSTDATE AS StringLeadOrderWonLostDate,
    LEAD.REMARKS AS AllottedToMailIds,
    LEAD.LEADNAME AS ContactNames,
    LEAD.COMBINELEADID AS CombineLeadId,
    LEAD.LEADCODE AS CombineLeadCode,
    LEAD.LEADNAME AS CombineLeadName,
    LS.LEADSOURCENAME AS LeadSourceDetailLeadSourceName,
    PR.GCMCODE AS PriorityCode,
    PR.GCMNAME AS PriorityName,
    SC.PARTYBRANCHCODE AS SourceChannelIdCode,
    SC.PARTYBRANCHNAME AS SourceChannelIdName,
    PARTY_SC.PARTYNAME AS SourceChannelIdPartyBranchPartyName,
    SCC.NAME AS SourceChannelContactIdName,
    CC.PARTYBRANCHCODE AS CurrentChannelIdCode,
    CC.PARTYBRANCHNAME AS CurrentChannelIdName,
    PARTY_CC.PARTYNAME AS CurrentChannelIdPartyBranchPartyName,
    CCC.NAME AS CurrentChannelContactIdName,
    LEAD.REMARKS AS Remarks,

    -- MarketMind Region (added 2026-09-01 — LEADSCORE/SEGMENTID/ICPID new TLEAD columns)
    LEAD.LEADSCORE AS LeadScore,
    LEAD.SEGMENTID AS SegmentId,
    LEAD.ICPID AS ICPId

FROM TLEAD LEAD
LEFT JOIN MORGANIZATIONUNIT OU ON LEAD.OUID = OU.OUId
LEFT JOIN MPARTY PARTY ON LEAD.PARTYID = PARTY.PARTYID
LEFT JOIN MPARTYBRANCH PB ON LEAD.PARTYBRANCHID = PB.PARTYBRANCHID
LEFT JOIN MCONTACT CONTACT ON LEAD.CONTACTID = CONTACT.CONTACTID
LEFT JOIN MLEADCATEGORY LC ON LEAD.LEADCATEGORYID = LC.LEADCATEGORYID
LEFT JOIN MLEADCYCLE LCYCLE ON LEAD.LEADCYCLEID = LCYCLE.LEADCYCLEID
LEFT JOIN MLEADCYCLEDETAIL LCD ON LEAD.LEADCYCLEDETAILID = LCD.LEADCYCLEDETAILID
LEFT JOIN MEMPLOYEE INCHARGE ON LEAD.INCHARGEID = INCHARGE.EMPLOYEEID
LEFT JOIN MLEADSOURCEDETAIL LSD ON LEAD.LEADSOURCEDETAILID = LSD.LEADSOURCEDETAILID
LEFT JOIN MEMPLOYEE SINC ON LEAD.SOURCEINCHARGEID = SINC.EMPLOYEEID
LEFT JOIN MEMPLOYEE CINC ON LEAD.CURRENTINCHARGEID = CINC.EMPLOYEEID
LEFT JOIN MCURRENCY CUR ON LEAD.CURRENCYID = CUR.CURRENCYID
LEFT JOIN MREASON LR ON LEAD.LOSTREASONID = LR.REASONID
LEFT JOIN MUSER CREBY ON LEAD.CREATEDBYID = CREBY.USERID
LEFT JOIN MUSER MODBY ON LEAD.MODIFIEDBYID = MODBY.USERID
LEFT JOIN TLEAD COMBINELEAD ON LEAD.COMBINELEADID = COMBINELEAD.LEADID
LEFT JOIN MLEADSOURCE LS ON LSD.LEADSOURCEID = LS.LEADSOURCEID
LEFT JOIN MGCM PR ON LEAD.PRIORITYID = PR.GCMID
LEFT JOIN MPARTYBRANCH SC ON LEAD.SOURCECHANNELID = SC.PARTYBRANCHID
LEFT JOIN MPARTY PARTY_SC ON SC.PARTYID = PARTY_SC.PARTYID
LEFT JOIN MPARTYBRANCH CC ON LEAD.CURRENTCHANNELID = CC.PARTYBRANCHID
LEFT JOIN MPARTY PARTY_CC ON CC.PARTYID = PARTY_CC.PARTYID
LEFT JOIN MCONTACT SCC ON LEAD.SOURCECHANNELCONTACTID = SCC.CONTACTID
LEFT JOIN MCONTACT CCC ON LEAD.CURRENTCHANNELCONTACTID = CCC.CONTACTID
WHERE LEAD.LEADID = @leadid;
";
    }
}