namespace MarketingDAL.Query.CustomerProductMatrixCell;

/// <summary>SQL constants for MCUSTPRODMATRIXCELL — MarketMind AI CustomerProductMatrixCell data.
/// No inline SQL elsewhere. RevenueTargetAmount is NOT selected here — it's computed in
/// CustomerProductMatrixCellBLL on read, never stored.</summary>
public static class CustomerProductMatrixCellQB
{
    public const string GET_CUSTOMERPRODUCTMATRIXCELL = @"
SELECT
    X.CUSTOMERPRODUCTMATRIXCELLID  AS CustomerProductMatrixCellId,
    X.CELLCODE                     AS CellCode,
    X.CELLNAME                     AS CellName,
    X.ABCDCATEGORY                 AS ABCDCategory,
    X.PRODUCTLABEL                 AS ProductLabel,
    X.REVENUETARGETPERCENT         AS RevenueTargetPercent,
    X.REMARKS                      AS Remarks,

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

    X.SORTORDER                    AS SortOrder,
    X.STATUS                       AS Status,
    X.VERSION                      AS Version,
    X.TENANTID                     AS TenantId
FROM MCUSTPRODMATRIXCELL X
LEFT JOIN MUSER CB ON CB.USERID = X.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = X.MODIFIEDBYID
WHERE X.CUSTOMERPRODUCTMATRIXCELLID = @CustomerProductMatrixCellId;
";

    public const string SAVE_CUSTOMERPRODUCTMATRIXCELL = @"
INSERT INTO MCUSTPRODMATRIXCELL (
    CUSTOMERPRODUCTMATRIXCELLID, CELLCODE, CELLNAME, ABCDCATEGORY, PRODUCTLABEL, REVENUETARGETPERCENT, REMARKS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @CustomerProductMatrixCellId, @CellCode, @CellName, @ABCDCategory, @ProductLabel, @RevenueTargetPercent, @Remarks,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_CUSTOMERPRODUCTMATRIXCELL = @"
UPDATE MCUSTPRODMATRIXCELL
SET
    CELLCODE               = @CellCode,
    CELLNAME               = @CellName,
    ABCDCATEGORY           = @ABCDCategory,
    PRODUCTLABEL           = @ProductLabel,
    REVENUETARGETPERCENT   = @RevenueTargetPercent,
    REMARKS                = @Remarks,
    MODIFIEDBYID           = @ModifiedById,
    MODIFIEDON             = @ModifiedOn,
    SORTORDER              = @SortOrder,
    STATUS                 = @Status,
    VERSION                = @Version
WHERE CUSTOMERPRODUCTMATRIXCELLID = @CustomerProductMatrixCellId;
";

    public const string DELETE_CUSTOMERPRODUCTMATRIXCELL = @"
DELETE FROM MCUSTPRODMATRIXCELL
WHERE CUSTOMERPRODUCTMATRIXCELLID = @CustomerProductMatrixCellId;
";

    public const string GET_SELECTLIST_CUSTOMERPRODUCTMATRIXCELL = @"
SELECT
    CUSTOMERPRODUCTMATRIXCELLID    AS Id,
    CELLCODE                       AS Code,
    CELLNAME                       AS Name
FROM MCUSTPRODMATRIXCELL
WHERE STATUS = 1
ORDER BY CELLNAME;
";
}