namespace RecruitmentDAL.Query.Agency
{
    public static class AgencyQB
    {
        // ==============================
        // GET
        // ==============================

        // Covering index: IX_MRECRUITMENTAGENCY_TENANTID already ships with the Phase 6 Agency
        // migration.
        public const string GET_AGENCY = @"
SELECT
    AG.RECRUITMENTAGENCYID   AS RecruitmentAgencyId,
    AG.DXPPARTYID            AS DxpPartyId,
    AG.AGENCYNAME            AS AgencyName,
    AG.CONTACTPERSONNAME     AS ContactPersonName,
    AG.CONTACTEMAIL          AS ContactEmail,
    AG.CONTACTMOBILE         AS ContactMobile,
    AG.CONTRACTTERMS         AS ContractTerms,
    AG.COMMISSIONPERCENTAGE  AS CommissionPercentage,
    AG.SPECIALIZATIONTAGS    AS SpecializationTags,

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

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

    AG.MODIFIEDBYID          AS ModifiedById,
    AG.MODIFIEDON            AS ModifiedOn,
    MU.USERNAME              AS ModifiedByName
FROM MRECRUITMENTAGENCY AG
LEFT JOIN MUSER CU ON CU.USERID = AG.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = AG.MODIFIEDBYID
WHERE AG.RECRUITMENTAGENCYID = @RecruitmentAgencyId
AND   AG.TENANTID             = @TenantId;";

        // ==============================
        // SAVE / UPDATE
        // ==============================
        public const string SAVE_AGENCY = @"
INSERT INTO MRECRUITMENTAGENCY
(
    RECRUITMENTAGENCYID, DXPPARTYID, AGENCYNAME, CONTACTPERSONNAME, CONTACTEMAIL,
    CONTACTMOBILE, CONTRACTTERMS, COMMISSIONPERCENTAGE, SPECIALIZATIONTAGS,
    SORTORDER, STATUS, VERSION, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @RecruitmentAgencyId, @DxpPartyId, @AgencyName, @ContactPersonName, @ContactEmail,
    @ContactMobile, @ContractTerms, @CommissionPercentage, @SpecializationTags,
    @SortOrder, @Status, @Version, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
);";

        public const string UPDATE_AGENCY = @"
UPDATE MRECRUITMENTAGENCY SET
    DXPPARTYID            = @DxpPartyId,
    AGENCYNAME            = @AgencyName,
    CONTACTPERSONNAME     = @ContactPersonName,
    CONTACTEMAIL          = @ContactEmail,
    CONTACTMOBILE         = @ContactMobile,
    CONTRACTTERMS         = @ContractTerms,
    COMMISSIONPERCENTAGE  = @CommissionPercentage,
    SPECIALIZATIONTAGS    = @SpecializationTags,
    SORTORDER             = @SortOrder,
    STATUS                = @Status,
    VERSION               = @Version,
    MODIFIEDBYID          = @ModifiedById,
    MODIFIEDON            = @ModifiedOn
WHERE RECRUITMENTAGENCYID = @RecruitmentAgencyId
AND   TENANTID             = @TenantId;";

        // ==============================
        // DELETE (soft delete — STATUS=2, matching every other Recruitment entity's convention)
        // ==============================
        public const string DELETE_AGENCY = @"
UPDATE MRECRUITMENTAGENCY SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE RECRUITMENTAGENCYID = @RecruitmentAgencyId
AND   TENANTID             = @TenantId;";

        // ==============================
        // SELECT LIST — agency roster browse/dropdown, optional name search. Small, manually
        // curated master list (never expected to reach thousands of rows) — same "small bounded
        // master list" shape as SelectionProcess/JobProfileLibrary's own GetSelectList queries in
        // this module, so a plain non-paged active-rows query is sufficient here.
        // ==============================
        public const string GET_SELECTLIST_AGENCY = @"
SELECT
    AG.RECRUITMENTAGENCYID   AS RecruitmentAgencyId,
    AG.DXPPARTYID            AS DxpPartyId,
    AG.AGENCYNAME            AS AgencyName,
    AG.CONTACTPERSONNAME     AS ContactPersonName,
    AG.CONTACTEMAIL          AS ContactEmail,
    AG.CONTACTMOBILE         AS ContactMobile,
    AG.CONTRACTTERMS         AS ContractTerms,
    AG.COMMISSIONPERCENTAGE  AS CommissionPercentage,
    AG.SPECIALIZATIONTAGS    AS SpecializationTags,
    AG.SORTORDER             AS SortOrder,
    AG.STATUS                AS Status,
    AG.TENANTID              AS TenantId
FROM MRECRUITMENTAGENCY AG
WHERE AG.STATUS   = 1
AND   AG.TENANTID = @TenantId
AND   (@SearchKeyword IS NULL OR @SearchKeyword = '' OR AG.AGENCYNAME LIKE '%' + @SearchKeyword + '%')
ORDER BY AG.AGENCYNAME;";
    }
}