namespace RecruitmentDAL.Query.JobAdSpend
{
    public static class JobAdSpendQB
    {
        // ==============================
        // GET
        // ==============================

        // Covering index: IX_TJOBADSPEND_JOBREQUISITIONID + IX_TJOBADSPEND_TENANTID already ship
        // with the Phase 6 JobAdSpend migration.
        public const string GET_JOBADSPEND = @"
SELECT
    JS.JOBADSPENDID          AS JobAdSpendId,
    JS.JOBREQUISITIONID      AS JobRequisitionId,
    JS.CHANNELTYPE            AS ChannelType,
    JS.JOBBOARDNAME           AS JobBoardName,
    JS.POSTEDDATE             AS PostedDate,
    JS.SPENDAMOUNT            AS SpendAmount,
    JS.CURRENCYCODE           AS CurrencyCode,

    JS.SORTORDER              AS SortOrder,
    JS.STATUS                 AS Status,
    JS.VERSION                AS Version,
    JS.TENANTID               AS TenantId,

    JS.CREATEDBYID            AS CreatedById,
    JS.CREATEDON              AS CreatedOn,
    CU.USERNAME               AS CreatedByName,

    JS.MODIFIEDBYID           AS ModifiedById,
    JS.MODIFIEDON             AS ModifiedOn,
    MU.USERNAME               AS ModifiedByName
FROM TJOBADSPEND JS
LEFT JOIN MUSER CU ON CU.USERID = JS.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = JS.MODIFIEDBYID
WHERE JS.JOBADSPENDID = @JobAdSpendId
AND   JS.TENANTID      = @TenantId;";

        // ==============================
        // SAVE / UPDATE
        // ==============================
        public const string SAVE_JOBADSPEND = @"
INSERT INTO TJOBADSPEND
(
    JOBADSPENDID, JOBREQUISITIONID, CHANNELTYPE, JOBBOARDNAME,
    POSTEDDATE, SPENDAMOUNT, CURRENCYCODE,
    SORTORDER, STATUS, VERSION, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @JobAdSpendId, @JobRequisitionId, @ChannelType, @JobBoardName,
    @PostedDate, @SpendAmount, @CurrencyCode,
    @SortOrder, @Status, @Version, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
);";

        public const string UPDATE_JOBADSPEND = @"
UPDATE TJOBADSPEND SET
    JOBREQUISITIONID     = @JobRequisitionId,
    CHANNELTYPE           = @ChannelType,
    JOBBOARDNAME          = @JobBoardName,
    POSTEDDATE            = @PostedDate,
    SPENDAMOUNT           = @SpendAmount,
    CURRENCYCODE          = @CurrencyCode,
    SORTORDER             = @SortOrder,
    STATUS                = @Status,
    VERSION               = @Version,
    MODIFIEDBYID          = @ModifiedById,
    MODIFIEDON            = @ModifiedOn
WHERE JOBADSPENDID = @JobAdSpendId
AND   TENANTID      = @TenantId;";

        // ==============================
        // DELETE (soft delete — STATUS=2, matching BgVerificationQB.DELETE_BGVERIFICATION /
        // ApplicationQB.DELETE_APPLICATION's established convention)
        // ==============================
        public const string DELETE_JOBADSPEND = @"
UPDATE TJOBADSPEND SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE JOBADSPENDID = @JobAdSpendId
AND   TENANTID      = @TenantId;";

        // ==============================
        // SELECT LIST — the spend panel for one JobRequisition (every board/channel spend row,
        // newest first).
        // ==============================
        public const string GET_SELECTLIST_JOBADSPEND_BY_JOBREQUISITION = @"
SELECT
    JS.JOBADSPENDID          AS JobAdSpendId,
    JS.JOBREQUISITIONID      AS JobRequisitionId,
    JS.CHANNELTYPE            AS ChannelType,
    JS.JOBBOARDNAME           AS JobBoardName,
    JS.POSTEDDATE             AS PostedDate,
    JS.SPENDAMOUNT            AS SpendAmount,
    JS.CURRENCYCODE           AS CurrencyCode,
    JS.STATUS                 AS Status,
    JS.TENANTID               AS TenantId
FROM TJOBADSPEND JS
WHERE JS.JOBREQUISITIONID = @JobRequisitionId
AND   JS.STATUS           <> 2
AND   JS.TENANTID          = @TenantId
ORDER BY JS.POSTEDDATE DESC;";
    }
}