using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RecruitmentDAL.Query.JobRequisition
{
    public static class JobRequisitionQB
    {
        // Covering index note: TJOBREQUISITION should carry
        // IX_TJOBREQUISITION_TENANTID_STATUS (TENANTID, STATUS) INCLUDE (REQUISITIONSTATUS)
        // to keep GET/SELECTLIST queries index-covered as row volume grows.
        public const string GET_JOBREQUISITION = @"
    SELECT
    JR.JOBREQUISITIONID            AS JobRequisitionId,
    JR.REQUISITIONCODE             AS RequisitionCode,
    JR.JOBPOSITIONID               AS JobPositionId,
    JR.JOBPROFILEID                AS JobProfileId,
    JR.SELECTIONPROCESSTEMPLATEID  AS SelectionProcessTemplateId,
    JR.REQUISITIONTYPE             AS RequisitionType,
    JR.REPLACEMENTFOREMPLOYEEID    AS ReplacementForEmployeeId,
    JR.DEPARTMENTID                AS DepartmentId,
    JR.NOOFOPENINGS                AS NoOfOpenings,
    JR.BUDGETREFERENCEID           AS BudgetReferenceId,
    JR.REQUISITIONSTATUS           AS RequisitionStatus,
    JR.REQUESTEDBYID               AS RequestedById,
    JR.APPROVEDBYID                AS ApprovedById,
    JR.APPROVALDATE                AS ApprovalDate,
    JR.TARGETJOININGDATE           AS TargetJoiningDate,
    JR.ASSIGNEDRECRUITERID         AS AssignedRecruiterId,

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

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

    JR.SORTORDER                   AS SortOrder,
    JR.STATUS                      AS Status,
    JR.VERSION                     AS Version,
    JR.TENANTID                    AS TenantId
FROM TJOBREQUISITION JR
LEFT JOIN MUSER CU ON CU.USERID = JR.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = JR.MODIFIEDBYID
WHERE JR.JOBREQUISITIONID = @JobRequisitionId
AND   JR.TENANTID = @TenantId;
";

        public const string SAVE_JOBREQUISITION = @"
    INSERT INTO TJOBREQUISITION (
    JOBREQUISITIONID,
    REQUISITIONCODE,
    JOBPOSITIONID,
    JOBPROFILEID,
    SELECTIONPROCESSTEMPLATEID,
    REQUISITIONTYPE,
    REPLACEMENTFOREMPLOYEEID,
    DEPARTMENTID,
    NOOFOPENINGS,
    BUDGETREFERENCEID,
    REQUISITIONSTATUS,
    REQUESTEDBYID,
    APPROVEDBYID,
    APPROVALDATE,
    TARGETJOININGDATE,
    ASSIGNEDRECRUITERID,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    STATUS,
    VERSION,
    TENANTID
)
VALUES (
    @JobRequisitionId,
    @RequisitionCode,
    @JobPositionId,
    @JobProfileId,
    @SelectionProcessTemplateId,
    @RequisitionType,
    @ReplacementForEmployeeId,
    @DepartmentId,
    @NoOfOpenings,
    @BudgetReferenceId,
    @RequisitionStatus,
    @RequestedById,
    @ApprovedById,
    @ApprovalDate,
    @TargetJoiningDate,
    @AssignedRecruiterId,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,
    @SortOrder,
    @Status,
    @Version,
    @TenantId
);
";

        public const string UPDATE_JOBREQUISITION = @"
UPDATE TJOBREQUISITION
SET
    REQUISITIONCODE             = @RequisitionCode,
    JOBPOSITIONID                = @JobPositionId,
    JOBPROFILEID                 = @JobProfileId,
    SELECTIONPROCESSTEMPLATEID   = @SelectionProcessTemplateId,
    REQUISITIONTYPE              = @RequisitionType,
    REPLACEMENTFOREMPLOYEEID     = @ReplacementForEmployeeId,
    DEPARTMENTID                 = @DepartmentId,
    NOOFOPENINGS                 = @NoOfOpenings,
    BUDGETREFERENCEID            = @BudgetReferenceId,
    REQUISITIONSTATUS            = @RequisitionStatus,
    REQUESTEDBYID                = @RequestedById,
    APPROVEDBYID                 = @ApprovedById,
    APPROVALDATE                 = @ApprovalDate,
    TARGETJOININGDATE            = @TargetJoiningDate,
    ASSIGNEDRECRUITERID          = @AssignedRecruiterId,
    MODIFIEDBYID                 = @ModifiedById,
    MODIFIEDON                   = @ModifiedOn,
    SORTORDER                    = @SortOrder,
    STATUS                       = @Status,
    VERSION                      = @Version
WHERE JOBREQUISITIONID = @JobRequisitionId
AND   TENANTID          = @TenantId;
";

        // Soft delete — TJOBREQUISITION is a transactional entity referenced by downstream
        // hiring records (SelectionProcess/Application/JobOffer), 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),
        // the canonical STATUS convention also used by FM/FAM soft-delete QB queries.
        public const string DELETE_JOBREQUISITION = @"
UPDATE TJOBREQUISITION
SET    STATUS       = 2,
       MODIFIEDBYID = @ModifiedById,
       MODIFIEDON   = @ModifiedOn
WHERE  JOBREQUISITIONID = @JobRequisitionId
AND    TENANTID          = @TenantId;
";

        // Base select-list shape mirrors JobPositionQB.GET_SELECTLIST_JOBPosition (active rows,
        // tenant filter), extended with a REQUISITIONSTATUS filter so callers can render a
        // "pending my approval" style list (e.g. status = 2) as well as an unfiltered active list.
        public const string GET_SELECTLIST_JOBREQUISITION = @"
SELECT
    JR.JOBREQUISITIONID            AS JobRequisitionId,
    JR.REQUISITIONCODE             AS RequisitionCode,
    JR.JOBPOSITIONID               AS JobPositionId,
    JR.JOBPROFILEID                AS JobProfileId,
    JR.SELECTIONPROCESSTEMPLATEID  AS SelectionProcessTemplateId,
    JR.REQUISITIONTYPE             AS RequisitionType,
    JR.REPLACEMENTFOREMPLOYEEID    AS ReplacementForEmployeeId,
    JR.DEPARTMENTID                AS DepartmentId,
    JR.NOOFOPENINGS                AS NoOfOpenings,
    JR.BUDGETREFERENCEID           AS BudgetReferenceId,
    JR.REQUISITIONSTATUS           AS RequisitionStatus,
    JR.REQUESTEDBYID               AS RequestedById,
    JR.APPROVEDBYID                AS ApprovedById,
    JR.APPROVALDATE                AS ApprovalDate,
    JR.TARGETJOININGDATE           AS TargetJoiningDate,
    JR.ASSIGNEDRECRUITERID         AS AssignedRecruiterId,

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

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

    JR.SORTORDER                   AS SortOrder,
    JR.STATUS                      AS Status,
    JR.VERSION                     AS Version,
    JR.TENANTID                    AS TenantId
FROM TJOBREQUISITION JR
LEFT JOIN MUSER CU ON CU.USERID = JR.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = JR.MODIFIEDBYID
WHERE JR.STATUS = 1
AND   JR.TENANTID = @TenantId
AND   (@RequisitionStatus IS NULL OR JR.REQUISITIONSTATUS = @RequisitionStatus)
ORDER BY JR.CREATEDON DESC;
";
    }
}