using System;

namespace RecruitmentDAL.Query.Application
{
    public static class ApplicationQB
    {
        #region GET

        // Covering index: IX_TAPPLICATION_TENANTID + IX_TAPPLICATION_CANDIDATEID +
        // IX_TAPPLICATION_JOBREQUISITIONID_STAGE already ship with the Phase1 migration.
        public const string GET_APPLICATION = @"
SELECT
    A.APPLICATIONID        AS ApplicationId,
    A.CANDIDATEID          AS CandidateId,
    A.JOBREQUISITIONID     AS JobRequisitionId,
    A.CURRENTSTAGEID       AS CurrentStageId,
    A.APPLICATIONSOURCE    AS ApplicationSource,
    A.AGENCYID             AS AgencyId,
    A.APPLIEDON            AS AppliedOn,

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

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

    A.MODIFIEDBYID         AS ModifiedById,
    A.MODIFIEDON           AS ModifiedOn,
    MU.USERNAME            AS ModifiedByName
FROM TAPPLICATION A
LEFT JOIN MUSER CU ON CU.USERID = A.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = A.MODIFIEDBYID
WHERE A.APPLICATIONID = @ApplicationId
AND   A.TENANTID       = @TenantId;
";

        // Covering index: IX_TAPPLICATIONSTAGEHISTORY_APPID_ENTEREDON +
        // IX_TAPPLICATIONSTAGEHISTORY_TENANTID already ship with the Phase1 migration.
        public const string GET_APPLICATIONSTAGEHISTORY = @"
SELECT
    H.APPLICATIONSTAGEHISTORYID AS ApplicationStageHistoryId,
    H.APPLICATIONID             AS ApplicationId,
    H.SELECTIONPROCESSSTAGEID   AS SelectionProcessStageId,
    H.STAGEENTEREDON            AS StageEnteredOn,
    H.STAGEEXITEDON             AS StageExitedOn,
    H.OUTCOME                   AS Outcome,
    H.DROPOFFREASON             AS DropOffReason,
    H.EVALUATORID                AS EvaluatorId,
    H.RECOMMENDATION            AS Recommendation,
    H.REMARKS                   AS Remarks,

    H.STATUS                    AS Status,
    H.VERSION                   AS Version,
    H.TENANTID                  AS TenantId,

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

    H.MODIFIEDBYID                AS ModifiedById,
    H.MODIFIEDON                AS ModifiedOn,
    MU.USERNAME                 AS ModifiedByName
FROM TAPPLICATIONSTAGEHISTORY H
LEFT JOIN MUSER CU ON CU.USERID = H.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = H.MODIFIEDBYID
WHERE H.APPLICATIONSTAGEHISTORYID = @ApplicationStageHistoryId
AND   H.TENANTID                  = @TenantId;
";

        #endregion

        #region SAVE

        public const string SAVE_APPLICATION = @"
INSERT INTO TAPPLICATION
(
    APPLICATIONID, CANDIDATEID, JOBREQUISITIONID, CURRENTSTAGEID,
    APPLICATIONSOURCE, AGENCYID, APPLIEDON,
    SORTORDER, STATUS, VERSION, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @ApplicationId, @CandidateId, @JobRequisitionId, @CurrentStageId,
    @ApplicationSource, @AgencyId, @AppliedOn,
    @SortOrder, @Status, @Version, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
);
";

        public const string SAVE_APPLICATIONSTAGEHISTORY = @"
INSERT INTO TAPPLICATIONSTAGEHISTORY
(
    APPLICATIONSTAGEHISTORYID, APPLICATIONID, SELECTIONPROCESSSTAGEID,
    STAGEENTEREDON, STAGEEXITEDON, OUTCOME, DROPOFFREASON, EVALUATORID,
    RECOMMENDATION, REMARKS,
    STATUS, VERSION, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @ApplicationStageHistoryId, @ApplicationId, @SelectionProcessStageId,
    @StageEnteredOn, @StageExitedOn, @Outcome, @DropOffReason, @EvaluatorId,
    @Recommendation, @Remarks,
    @Status, @Version, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
);
";

        #endregion

        #region UPDATE

        public const string UPDATE_APPLICATION = @"
UPDATE TAPPLICATION SET
    CANDIDATEID        = @CandidateId,
    JOBREQUISITIONID   = @JobRequisitionId,
    CURRENTSTAGEID     = @CurrentStageId,
    APPLICATIONSOURCE  = @ApplicationSource,
    AGENCYID           = @AgencyId,
    APPLIEDON          = @AppliedOn,
    SORTORDER          = @SortOrder,
    STATUS             = @Status,
    VERSION            = @Version,
    MODIFIEDBYID       = @ModifiedById,
    MODIFIEDON         = @ModifiedOn
WHERE APPLICATIONID = @ApplicationId
AND   TENANTID      = @TenantId;
";

        public const string UPDATE_APPLICATIONSTAGEHISTORY = @"
UPDATE TAPPLICATIONSTAGEHISTORY SET
    APPLICATIONID           = @ApplicationId,
    SELECTIONPROCESSSTAGEID = @SelectionProcessStageId,
    STAGEENTEREDON          = @StageEnteredOn,
    STAGEEXITEDON           = @StageExitedOn,
    OUTCOME                 = @Outcome,
    DROPOFFREASON           = @DropOffReason,
    EVALUATORID             = @EvaluatorId,
    RECOMMENDATION          = @Recommendation,
    REMARKS                 = @Remarks,
    STATUS                  = @Status,
    VERSION                 = @Version,
    MODIFIEDBYID            = @ModifiedById,
    MODIFIEDON              = @ModifiedOn
WHERE APPLICATIONSTAGEHISTORYID = @ApplicationStageHistoryId
AND   TENANTID                  = @TenantId;
";

        // Dedicated, narrow follow-up UPDATE — fires from ApplicationBLL.SaveApplicationStageHistory
        // right after a new stage-history row is persisted, to advance the parent's pointer to the
        // stage the candidate is moving into. Deliberately not part of the stage-history's own
        // ExecuteSaveAsync transaction (see IApplicationDAL.UpdateApplicationCurrentStage).
        public const string UPDATE_APPLICATION_CURRENTSTAGE = @"
UPDATE TAPPLICATION SET
    CURRENTSTAGEID = @CurrentStageId,
    MODIFIEDBYID   = @ModifiedById,
    MODIFIEDON     = @ModifiedOn
WHERE APPLICATIONID = @ApplicationId
AND   TENANTID       = @TenantId;
";

        #endregion

        #region DELETE

        // Soft delete — TAPPLICATION is referenced by TAPPLICATIONSTAGEHISTORY (and future
        // JobOffer/Interview links), so rows are marked deleted (STATUS = 2) rather than
        // physically removed. Matches GB5Shared.GB5Constant.Enum.Gb4EnumDTO.Status
        // (0=Pending, 1=Active, 2=Deleted, 3=Amended, 4=Inactive, 5=Archived).
        public const string DELETE_APPLICATION = @"
UPDATE TAPPLICATION SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE APPLICATIONID = @ApplicationId
AND   TENANTID       = @TenantId;
";

        public const string DELETE_APPLICATIONSTAGEHISTORY = @"
UPDATE TAPPLICATIONSTAGEHISTORY SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE APPLICATIONSTAGEHISTORYID = @ApplicationStageHistoryId
AND   TENANTID                  = @TenantId;
";

        #endregion

        #region SELECT LIST

        // Optional @JobRequisitionId filter renders a "pipeline view of one requisition"
        // (kanban-by-stage); pass NULL to get every active application for the tenant.
        // CandidateId filtering ("this candidate's application history") is now wired as a
        // dedicated query — see GET_APPLICATIONS_BY_CANDIDATE below (Phase 3 candidate portal).
        public const string GET_SELECTLIST_APPLICATION = @"
SELECT
    A.APPLICATIONID        AS ApplicationId,
    A.CANDIDATEID          AS CandidateId,
    A.JOBREQUISITIONID     AS JobRequisitionId,
    A.CURRENTSTAGEID       AS CurrentStageId,
    A.APPLICATIONSOURCE    AS ApplicationSource,
    A.AGENCYID             AS AgencyId,
    A.APPLIEDON            AS AppliedOn,

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

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

    A.MODIFIEDBYID         AS ModifiedById,
    A.MODIFIEDON           AS ModifiedOn,
    MU.USERNAME            AS ModifiedByName
FROM TAPPLICATION A
LEFT JOIN MUSER CU ON CU.USERID = A.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = A.MODIFIEDBYID
WHERE A.STATUS   = 1
AND   A.TENANTID = @TenantId
AND   (@JobRequisitionId IS NULL OR A.JOBREQUISITIONID = @JobRequisitionId)
ORDER BY A.APPLIEDON DESC;
";

        // Candidate-scoped application history (Phase 3 candidate self-service portal) —
        // CandidateId is always resolved server-side from a verified CandidatePortal JWT claim,
        // never client-supplied, so this can never be used to read another candidate's history.
        // Same covering index note as GET_APPLICATION above (IX_TAPPLICATION_TENANTID +
        // IX_TAPPLICATION_CANDIDATEID).
        public const string GET_APPLICATIONS_BY_CANDIDATE = @"
SELECT
    A.APPLICATIONID        AS ApplicationId,
    A.CANDIDATEID          AS CandidateId,
    A.JOBREQUISITIONID     AS JobRequisitionId,
    A.CURRENTSTAGEID       AS CurrentStageId,
    A.APPLICATIONSOURCE    AS ApplicationSource,
    A.AGENCYID             AS AgencyId,
    A.APPLIEDON            AS AppliedOn,

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

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

    A.MODIFIEDBYID         AS ModifiedById,
    A.MODIFIEDON           AS ModifiedOn,
    MU.USERNAME            AS ModifiedByName
FROM TAPPLICATION A
LEFT JOIN MUSER CU ON CU.USERID = A.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = A.MODIFIEDBYID
WHERE A.STATUS      = 1
AND   A.TENANTID    = @TenantId
AND   A.CANDIDATEID = @CandidateId
ORDER BY A.APPLIEDON DESC;
";

        // Mandatory @ApplicationId filter — this is what a future "Candidate Profile" view reads
        // to render the full per-stage trail for one application, in the order it happened.
        public const string GET_SELECTLIST_APPLICATIONSTAGEHISTORY = @"
SELECT
    H.APPLICATIONSTAGEHISTORYID AS ApplicationStageHistoryId,
    H.APPLICATIONID             AS ApplicationId,
    H.SELECTIONPROCESSSTAGEID   AS SelectionProcessStageId,
    H.STAGEENTEREDON            AS StageEnteredOn,
    H.STAGEEXITEDON             AS StageExitedOn,
    H.OUTCOME                   AS Outcome,
    H.DROPOFFREASON             AS DropOffReason,
    H.EVALUATORID                AS EvaluatorId,
    H.RECOMMENDATION            AS Recommendation,
    H.REMARKS                   AS Remarks,

    H.STATUS                    AS Status,
    H.VERSION                   AS Version,
    H.TENANTID                  AS TenantId,

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

    H.MODIFIEDBYID                AS ModifiedById,
    H.MODIFIEDON                AS ModifiedOn,
    MU.USERNAME                 AS ModifiedByName
FROM TAPPLICATIONSTAGEHISTORY H
LEFT JOIN MUSER CU ON CU.USERID = H.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = H.MODIFIEDBYID
WHERE H.STATUS        = 1
AND   H.TENANTID      = @TenantId
AND   H.APPLICATIONID = @ApplicationId
ORDER BY H.STAGEENTEREDON ASC;
";

        #endregion
    }
}