namespace MarketingDAL.Query.ICP;

/// <summary>SQL constants for MICP — MarketMind AI Ideal Customer Profile master data.</summary>
public static class ICPQB
{
    public const string GET_ICP = @"
SELECT
    I.ICPID                         AS ICPId,
    I.ICPCODE                       AS ICPCode,
    I.ICPNAME                       AS ICPName,
    I.SEGMENTID                     AS SegmentId,
    S.SEGMENTCODE                   AS SegmentCode,
    S.SEGMENTNAME                   AS SegmentName,
    I.BUSINESSTYPE                  AS BusinessType,
    I.LEADGENEFFORTSCORE            AS LeadGenEffortScore,
    I.SALESCONVERSIONEFFORTSCORE    AS SalesConversionEffortScore,
    I.RETURNSCORE                   AS ReturnScore,
    I.ABCDCATEGORY                  AS ABCDCategory,
    I.REMARKS                       AS Remarks,

    I.CREATEDBYID                   AS CreatedById,
    I.CREATEDON                     AS CreatedOn,
    CB.USERNAME                     AS CreatedByName,
    I.MODIFIEDBYID                  AS ModifiedById,
    I.MODIFIEDON                    AS ModifiedOn,
    MB.USERNAME                     AS ModifiedByName,

    I.SORTORDER                     AS SortOrder,
    I.STATUS                        AS Status,
    I.VERSION                       AS Version,
    I.TENANTID                      AS TenantId
FROM MICP I
LEFT JOIN MSEGMENT S ON S.SEGMENTID = I.SEGMENTID
LEFT JOIN MUSER CB ON CB.USERID = I.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = I.MODIFIEDBYID
WHERE I.ICPID = @ICPId;
";

    public const string SAVE_ICP = @"
INSERT INTO MICP (
    ICPID, ICPCODE, ICPNAME, SEGMENTID, BUSINESSTYPE, LEADGENEFFORTSCORE,
    SALESCONVERSIONEFFORTSCORE, RETURNSCORE, ABCDCATEGORY, REMARKS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @ICPId, @ICPCode, @ICPName, @SegmentId, @BusinessType, @LeadGenEffortScore,
    @SalesConversionEffortScore, @ReturnScore, @ABCDCategory, @Remarks,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_ICP = @"
UPDATE MICP
SET
    ICPCODE                     = @ICPCode,
    ICPNAME                     = @ICPName,
    SEGMENTID                   = @SegmentId,
    BUSINESSTYPE                = @BusinessType,
    LEADGENEFFORTSCORE          = @LeadGenEffortScore,
    SALESCONVERSIONEFFORTSCORE  = @SalesConversionEffortScore,
    RETURNSCORE                 = @ReturnScore,
    ABCDCATEGORY                = @ABCDCategory,
    REMARKS                     = @Remarks,
    MODIFIEDBYID                = @ModifiedById,
    MODIFIEDON                  = @ModifiedOn,
    SORTORDER                   = @SortOrder,
    STATUS                      = @Status,
    VERSION                     = @Version
WHERE ICPID = @ICPId;
";

    public const string DELETE_ICP = @"
DELETE FROM MICP
WHERE ICPID = @ICPId;
";

    public const string GET_SELECTLIST_ICP = @"
SELECT
    ICPID    AS Id,
    ICPCODE  AS Code,
    ICPNAME  AS Name
FROM MICP
WHERE STATUS = 1
ORDER BY ICPNAME;
";
}