namespace RecruitmentDAL.Query.FlsBridge
{
    public static class RecruitmentFlsActionQB
    {
        // ==============================
        // GET
        // ==============================
        public const string GET_RECRUITMENTFLSACTION = @"
SELECT
    FA.RECRUITMENTFLSACTIONID    AS RecruitmentFlsActionId,
    FA.FLSRESPONDENTID           AS FlsRespondentId,
    FA.ACTIONTYPE                AS ActionType,
    FA.APPLICATIONID             AS ApplicationId,
    FA.JOBOFFERID                AS JobOfferId,
    FA.INTERVIEWID                AS InterviewId,
    FA.APPLICATIONSTAGEHISTORYID  AS ApplicationStageHistoryId,
    FA.BGVERIFICATIONID           AS BgVerificationId,
    FA.RESPONSEPAYLOAD           AS ResponsePayload,
    FA.RESPONDEDON               AS RespondedOn,

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

    FA.MODIFIEDBYID                AS ModifiedById,
    FA.MODIFIEDON                  AS ModifiedOn,
    MU.USERNAME                    AS ModifiedByName,

    FA.STATUS                     AS Status,
    FA.VERSION                    AS Version,
    FA.TENANTID                   AS TenantId
FROM TRECRUITMENTFLSACTION FA
LEFT JOIN MUSER CU ON CU.USERID = FA.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = FA.MODIFIEDBYID
WHERE FA.RECRUITMENTFLSACTIONID = @RecruitmentFlsActionId
AND   FA.TENANTID = @TenantId;";

        // Cross-tenant, pre-authentication lookup — mirrors AssessmentFlsBridgeQB.GET_BRIDGE_BY_RESPONDENTID
        // and FlsRespondentDAL.GetByTokenAsync's own ClientId=0/UserId=-1 precedent. No TENANTID
        // filter here deliberately: the caller does not yet know the real tenant until this row
        // is read (its own TENANTID column IS the tenant to operate against from that point on).
        public const string GET_RECRUITMENTFLSACTION_BY_RESPONDENTID = @"
SELECT
    FA.RECRUITMENTFLSACTIONID    AS RecruitmentFlsActionId,
    FA.FLSRESPONDENTID           AS FlsRespondentId,
    FA.ACTIONTYPE                AS ActionType,
    FA.APPLICATIONID             AS ApplicationId,
    FA.JOBOFFERID                AS JobOfferId,
    FA.INTERVIEWID                AS InterviewId,
    FA.APPLICATIONSTAGEHISTORYID  AS ApplicationStageHistoryId,
    FA.BGVERIFICATIONID           AS BgVerificationId,
    FA.RESPONSEPAYLOAD           AS ResponsePayload,
    FA.RESPONDEDON               AS RespondedOn,
    FA.CREATEDBYID                AS CreatedById,
    FA.CREATEDON                  AS CreatedOn,
    FA.MODIFIEDBYID                AS ModifiedById,
    FA.MODIFIEDON                  AS ModifiedOn,
    FA.STATUS                     AS Status,
    FA.VERSION                    AS Version,
    FA.TENANTID                   AS TenantId
FROM TRECRUITMENTFLSACTION FA
WHERE FA.FLSRESPONDENTID = @FlsRespondentId
AND   FA.STATUS <> 2;";

        // ==============================
        // SAVE / UPDATE
        // ==============================
        public const string SAVE_RECRUITMENTFLSACTION = @"
INSERT INTO TRECRUITMENTFLSACTION (
    RECRUITMENTFLSACTIONID,
    FLSRESPONDENTID,
    ACTIONTYPE,
    APPLICATIONID,
    JOBOFFERID,
    INTERVIEWID,
    APPLICATIONSTAGEHISTORYID,
    BGVERIFICATIONID,
    RESPONSEPAYLOAD,
    RESPONDEDON,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    STATUS,
    VERSION,
    TENANTID
)
VALUES (
    @RecruitmentFlsActionId,
    @FlsRespondentId,
    @ActionType,
    @ApplicationId,
    @JobOfferId,
    @InterviewId,
    @ApplicationStageHistoryId,
    @BgVerificationId,
    @ResponsePayload,
    @RespondedOn,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,
    @Status,
    @Version,
    @TenantId
);";

        public const string UPDATE_RECRUITMENTFLSACTION = @"
UPDATE TRECRUITMENTFLSACTION
SET
    FLSRESPONDENTID           = @FlsRespondentId,
    ACTIONTYPE                = @ActionType,
    APPLICATIONID             = @ApplicationId,
    JOBOFFERID                = @JobOfferId,
    INTERVIEWID                = @InterviewId,
    APPLICATIONSTAGEHISTORYID  = @ApplicationStageHistoryId,
    BGVERIFICATIONID           = @BgVerificationId,
    RESPONSEPAYLOAD           = @ResponsePayload,
    RESPONDEDON               = @RespondedOn,
    MODIFIEDBYID               = @ModifiedById,
    MODIFIEDON                 = @ModifiedOn,
    STATUS                    = @Status,
    VERSION                   = @Version
WHERE RECRUITMENTFLSACTIONID = @RecruitmentFlsActionId
AND   TENANTID = @TenantId;";

        // ==============================
        // DELETE (soft delete — STATUS=2, matching ApplicationQB.DELETE_APPLICATION /
        // DELETE_APPLICATIONSTAGEHISTORY and CandidateQB.DELETE_CANDIDATE's established
        // Phase 1 convention for this module)
        // ==============================
        public const string DELETE_RECRUITMENTFLSACTION = @"
UPDATE TRECRUITMENTFLSACTION SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE RECRUITMENTFLSACTIONID = @RecruitmentFlsActionId
AND   TENANTID = @TenantId;";

        // ==============================
        // SELECT LIST
        // ==============================
        public const string GET_SELECTLIST_RECRUITMENTFLSACTION = @"
SELECT
    FA.RECRUITMENTFLSACTIONID    AS RecruitmentFlsActionId,
    FA.FLSRESPONDENTID           AS FlsRespondentId,
    FA.ACTIONTYPE                AS ActionType,
    FA.APPLICATIONID             AS ApplicationId,
    FA.RESPONDEDON               AS RespondedOn,
    FA.STATUS                    AS Status,
    FA.TENANTID                  AS TenantId
FROM TRECRUITMENTFLSACTION FA
WHERE FA.STATUS = 1
AND   FA.TENANTID = @TenantId
ORDER BY FA.CREATEDON DESC;";

        // ==============================
        // Coordinator natural-key lookups — see migration header for why no separate
        // coordinator table is added; the shared per-tenant MFLSREGISTRATION/MFLSINSTANCE/
        // MFLSINSTANCEGROUP triple is instead located/created lazily by these codes.
        // ==============================
        public const string RESOLVE_FORMTEMPLATEID_BY_CODE = @"
SELECT FORMTEMPLATEID FROM MFORMTEMPLATE WHERE FORMTEMPLATECODE = @Code;";

        public const string RESOLVE_MODULEID_BY_CODE = @"
SELECT MODULEID FROM MMODULE WHERE MODULECODE = @Code;";

        public const string RESOLVE_REGISTRATIONID_BY_CODE = @"
SELECT FLSREGISTRATIONID FROM MFLSREGISTRATION WHERE REGISTRATIONCODE = @Code AND TENANTID = @TenantId;";

        public const string RESOLVE_INSTANCEID_BY_CODE = @"
SELECT FLSINSTANCEID FROM MFLSINSTANCE WHERE INSTANCECODE = @Code AND FLSREGISTRATIONID = @FlsRegistrationId AND TENANTID = @TenantId;";

        public const string RESOLVE_GROUPID_BY_CODE = @"
SELECT GROUPID FROM MFLSINSTANCEGROUP WHERE GROUPCODE = @Code AND FLSINSTANCEID = @FlsInstanceId AND TENANTID = @TenantId;";

        // Raw INSERT — deliberately bypasses FlsInstanceBLL.CreateInstanceAsync/ScheduleInstanceAsync/
        // OpenInstanceAsync, mirroring TMS AssessmentFlsBridgeQB's identical documented precedent
        // (no existing caller anywhere for that 3-step path; this coordinator instance never needs
        // a scheduled open/close window — it stays open indefinitely for on-demand dispatch).
        public const string INSERT_FLS_INSTANCE_OPEN = @"
INSERT INTO MFLSINSTANCE
(FLSINSTANCEID, FLSREGISTRATIONID, INSTANCECODE, INSTANCENAME, INSTANCESTATUS, CREATEDBYID, MODIFIEDBYID, TENANTID)
VALUES (@FlsInstanceId, @FlsRegistrationId, @InstanceCode, @InstanceName, 2, @CreatedById, @ModifiedById, @TenantId);";

        public const string INSERT_FLS_INSTANCE_GROUP = @"
INSERT INTO MFLSINSTANCEGROUP
(GROUPID, FLSINSTANCEID, GROUPCODE, GROUPNAME, CREATEDBYID, MODIFIEDBYID, TENANTID)
VALUES (@GroupId, @FlsInstanceId, @GroupCode, @GroupName, @CreatedById, @ModifiedById, @TenantId);";
    }
}