namespace MarketingDAL.Query.Product;

/// <summary>SQL constants for MMKTPRODUCT — MarketMind AI Product master data.</summary>
public static class ProductQB
{
    public const string GET_PRODUCT = @"
SELECT
    P.PRODUCTID               AS ProductId,
    P.PRODUCTCODE             AS ProductCode,
    P.PRODUCTNAME             AS ProductName,
    P.DELIVERYEFFORTSCORE     AS DeliveryEffortScore,
    P.RETURNSCORE             AS ReturnScore,
    P.MARGINPERCENT           AS MarginPercent,
    P.OPERATIONALEFFORTSCORE  AS OperationalEffortScore,
    P.PRODUCTLABEL            AS ProductLabel,
    P.SOURCETYPE              AS SourceType,
    P.SOURCEREFID             AS SourceRefId,

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

    P.SORTORDER               AS SortOrder,
    P.STATUS                  AS Status,
    P.VERSION                 AS Version,
    P.TENANTID                AS TenantId
FROM MMKTPRODUCT P
LEFT JOIN MUSER CB ON CB.USERID = P.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = P.MODIFIEDBYID
WHERE P.PRODUCTID = @ProductId;
";

    public const string SAVE_PRODUCT = @"
INSERT INTO MMKTPRODUCT (
    PRODUCTID, PRODUCTCODE, PRODUCTNAME, DELIVERYEFFORTSCORE, RETURNSCORE,
    MARGINPERCENT, OPERATIONALEFFORTSCORE, PRODUCTLABEL, SOURCETYPE, SOURCEREFID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @ProductId, @ProductCode, @ProductName, @DeliveryEffortScore, @ReturnScore,
    @MarginPercent, @OperationalEffortScore, @ProductLabel, @SourceType, @SourceRefId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_PRODUCT = @"
UPDATE MMKTPRODUCT
SET
    PRODUCTCODE              = @ProductCode,
    PRODUCTNAME              = @ProductName,
    DELIVERYEFFORTSCORE      = @DeliveryEffortScore,
    RETURNSCORE              = @ReturnScore,
    MARGINPERCENT            = @MarginPercent,
    OPERATIONALEFFORTSCORE   = @OperationalEffortScore,
    PRODUCTLABEL             = @ProductLabel,
    SOURCETYPE               = @SourceType,
    SOURCEREFID              = @SourceRefId,
    MODIFIEDBYID             = @ModifiedById,
    MODIFIEDON               = @ModifiedOn,
    SORTORDER                = @SortOrder,
    STATUS                   = @Status,
    VERSION                  = @Version
WHERE PRODUCTID = @ProductId;
";

    public const string DELETE_PRODUCT = @"
DELETE FROM MMKTPRODUCT
WHERE PRODUCTID = @ProductId;
";

    public const string GET_SELECTLIST_PRODUCT = @"
SELECT
    PRODUCTID    AS Id,
    PRODUCTCODE  AS Code,
    PRODUCTNAME  AS Name
FROM MMKTPRODUCT
WHERE STATUS = 1
ORDER BY PRODUCTNAME;
";
}