namespace MarketingDAL.Query.Collateral;

/// <summary>SQL constants for MCOLLATERAL — MarketMind AI Collateral data. No inline SQL elsewhere.</summary>
public static class CollateralQB
{
    public const string GET_COLLATERAL = @"
SELECT
    L.COLLATERALID       AS CollateralId,
    L.COLLATERALCODE     AS CollateralCode,
    L.COLLATERALTITLE    AS CollateralTitle,
    L.FORMAT             AS Format,
    L.LINKEDPRODUCTID    AS LinkedProductId,
    P.PRODUCTCODE        AS LinkedProductCode,
    P.PRODUCTNAME        AS LinkedProductName,
    L.LINKEDPERSONAID    AS LinkedPersonaId,
    PE.PERSONACODE       AS LinkedPersonaCode,
    PE.PERSONANAME       AS LinkedPersonaName,
    L.SOURCEDOCUMENTID   AS SourceDocumentId,
    L.REMARKS            AS Remarks,

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

    L.SORTORDER          AS SortOrder,
    L.STATUS             AS Status,
    L.VERSION            AS Version,
    L.TENANTID           AS TenantId
FROM MCOLLATERAL L
LEFT JOIN MMKTPRODUCT P ON P.PRODUCTID = L.LINKEDPRODUCTID
LEFT JOIN MPERSONA PE ON PE.PERSONAID = L.LINKEDPERSONAID
LEFT JOIN MUSER CB ON CB.USERID = L.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = L.MODIFIEDBYID
WHERE L.COLLATERALID = @CollateralId;
";

    public const string SAVE_COLLATERAL = @"
INSERT INTO MCOLLATERAL (
    COLLATERALID, COLLATERALCODE, COLLATERALTITLE, FORMAT, LINKEDPRODUCTID, LINKEDPERSONAID, SOURCEDOCUMENTID, REMARKS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @CollateralId, @CollateralCode, @CollateralTitle, @Format, @LinkedProductId, @LinkedPersonaId, @SourceDocumentId, @Remarks,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_COLLATERAL = @"
UPDATE MCOLLATERAL
SET
    COLLATERALCODE      = @CollateralCode,
    COLLATERALTITLE     = @CollateralTitle,
    FORMAT              = @Format,
    LINKEDPRODUCTID     = @LinkedProductId,
    LINKEDPERSONAID     = @LinkedPersonaId,
    SOURCEDOCUMENTID    = @SourceDocumentId,
    REMARKS             = @Remarks,
    MODIFIEDBYID        = @ModifiedById,
    MODIFIEDON          = @ModifiedOn,
    SORTORDER           = @SortOrder,
    STATUS              = @Status,
    VERSION             = @Version
WHERE COLLATERALID = @CollateralId;
";

    public const string DELETE_COLLATERAL = @"
DELETE FROM MCOLLATERAL
WHERE COLLATERALID = @CollateralId;
";

    public const string GET_SELECTLIST_COLLATERAL = @"
SELECT
    COLLATERALID     AS Id,
    COLLATERALCODE   AS Code,
    COLLATERALTITLE  AS Name
FROM MCOLLATERAL
WHERE STATUS = 1
ORDER BY COLLATERALTITLE;
";
}