namespace MarketingDAL.Query.Segment;

/// <summary>SQL constants for MSEGMENT — MarketMind AI Segment master data. No inline SQL elsewhere.</summary>
public static class SegmentQB
{
    public const string GET_SEGMENT = @"
SELECT
    S.SEGMENTID                    AS SegmentId,
    S.SEGMENTCODE                  AS SegmentCode,
    S.SEGMENTNAME                  AS SegmentName,
    S.SEGMENTTYPE                  AS SegmentType,
    S.AVGBILLINGVALUE              AS AvgBillingValue,
    S.GROSSPROFITMARGINPERCENT     AS GrossProfitMarginPercent,
    S.REPEATPURCHASE               AS RepeatPurchase,
    S.COLLECTIONCYCLEDAYS          AS CollectionCycleDays,
    S.REMARKS                      AS Remarks,

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

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

    public const string SAVE_SEGMENT = @"
INSERT INTO MSEGMENT (
    SEGMENTID, SEGMENTCODE, SEGMENTNAME, SEGMENTTYPE, AVGBILLINGVALUE,
    GROSSPROFITMARGINPERCENT, REPEATPURCHASE, COLLECTIONCYCLEDAYS, REMARKS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @SegmentId, @SegmentCode, @SegmentName, @SegmentType, @AvgBillingValue,
    @GrossProfitMarginPercent, @RepeatPurchase, @CollectionCycleDays, @Remarks,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_SEGMENT = @"
UPDATE MSEGMENT
SET
    SEGMENTCODE               = @SegmentCode,
    SEGMENTNAME               = @SegmentName,
    SEGMENTTYPE               = @SegmentType,
    AVGBILLINGVALUE           = @AvgBillingValue,
    GROSSPROFITMARGINPERCENT  = @GrossProfitMarginPercent,
    REPEATPURCHASE            = @RepeatPurchase,
    COLLECTIONCYCLEDAYS       = @CollectionCycleDays,
    REMARKS                   = @Remarks,
    MODIFIEDBYID              = @ModifiedById,
    MODIFIEDON                = @ModifiedOn,
    SORTORDER                 = @SortOrder,
    STATUS                    = @Status,
    VERSION                   = @Version
WHERE SEGMENTID = @SegmentId;
";

    public const string DELETE_SEGMENT = @"
DELETE FROM MSEGMENT
WHERE SEGMENTID = @SegmentId;
";

    public const string GET_SELECTLIST_SEGMENT = @"
SELECT
    SEGMENTID    AS Id,
    SEGMENTCODE  AS Code,
    SEGMENTNAME  AS Name
FROM MSEGMENT
WHERE STATUS = 1
ORDER BY SEGMENTNAME;
";
}