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

namespace PayRollDAL.Query.Job
{
    public static class JobQB
    {
        // ── Picklist (paged, dual-dialect) ──────────────────────────────────
        // Indexes recommended:
        //   TJOB: IX_TJOB_JOBNUMBER (JOBNUMBER), IX_TJOB_DEPARTMENTID (DEPARTMENTID),
        //         IX_TJOB_REPORTINGTOEMPLOYEEID (REPORTINGTOEMPLOYEEID)

        public const string GET_SELECTLIST_JOB_SQL = @"
WITH Paged AS (
    SELECT JOB.JOBID                 AS JobId,
           JOB.JOBNUMBER              AS JobNumber,
           JOB.TITLE                  AS JobTitle,
           JOB.DESCRIPTION            AS JobDescription,
           JOB.DEPARTMENTID           AS DepartmentId,
           DEP.DEPARTMENTCODE         AS DepartmentCode,
           DEP.DEPARTMENTNAME         AS DepartmentName,
           JOB.REPORTINGTOEMPLOYEEID  AS ReportingToEmployeeId,
           EMP.EMPLOYEECODE           AS ReportingToEmployeeCode,
           EMP.EMPLOYEENAME           AS ReportingToEmployeeName,
           ROW_NUMBER() OVER (ORDER BY JOB.JOBNUMBER) AS RowNum
    FROM   TJOB JOB
    LEFT JOIN MDEPARTMENT DEP ON JOB.DEPARTMENTID = DEP.DEPARTMENTID
    LEFT JOIN MEMPLOYEE EMP   ON JOB.REPORTINGTOEMPLOYEEID = EMP.EMPLOYEEID
    WHERE  1 = 1 /*FILTER*/
)
SELECT JobId, JobNumber, JobTitle, JobDescription, DepartmentId, DepartmentCode, DepartmentName,
       ReportingToEmployeeId, ReportingToEmployeeCode, ReportingToEmployeeName
FROM   Paged
WHERE  (@firstnumber = -1 AND @maxresult = -1)
   OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_JOB_PG = @"
WITH Paged AS (
    SELECT JOB.JOBID                 AS ""JobId"",
           JOB.JOBNUMBER              AS ""JobNumber"",
           JOB.TITLE                  AS ""JobTitle"",
           JOB.DESCRIPTION            AS ""JobDescription"",
           JOB.DEPARTMENTID           AS ""DepartmentId"",
           DEP.DEPARTMENTCODE         AS ""DepartmentCode"",
           DEP.DEPARTMENTNAME         AS ""DepartmentName"",
           JOB.REPORTINGTOEMPLOYEEID  AS ""ReportingToEmployeeId"",
           EMP.EMPLOYEECODE           AS ""ReportingToEmployeeCode"",
           EMP.EMPLOYEENAME           AS ""ReportingToEmployeeName"",
           ROW_NUMBER() OVER (ORDER BY JOB.JOBNUMBER) AS ""RowNum""
    FROM   TJOB JOB
    LEFT JOIN MDEPARTMENT DEP ON JOB.DEPARTMENTID = DEP.DEPARTMENTID
    LEFT JOIN MEMPLOYEE EMP   ON JOB.REPORTINGTOEMPLOYEEID = EMP.EMPLOYEEID
    WHERE  1 = 1 /*FILTER*/
)
SELECT ""JobId"", ""JobNumber"", ""JobTitle"", ""JobDescription"", ""DepartmentId"", ""DepartmentCode"", ""DepartmentName"",
       ""ReportingToEmployeeId"", ""ReportingToEmployeeCode"", ""ReportingToEmployeeName""
FROM   Paged
WHERE  (:firstnumber = -1 AND :maxresult = -1)
   OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        public const string GET_SELECTLIST_JOB_COUNT_SQL = @"
SELECT COUNT(*)
FROM   TJOB JOB
LEFT JOIN MDEPARTMENT DEP ON JOB.DEPARTMENTID = DEP.DEPARTMENTID
LEFT JOIN MEMPLOYEE EMP   ON JOB.REPORTINGTOEMPLOYEEID = EMP.EMPLOYEEID
WHERE  1 = 1 /*FILTER*/;";

        public const string GET_SELECTLIST_JOB_COUNT_PG = @"
SELECT COUNT(*)
FROM   TJOB JOB
LEFT JOIN MDEPARTMENT DEP ON JOB.DEPARTMENTID = DEP.DEPARTMENTID
LEFT JOIN MEMPLOYEE EMP   ON JOB.REPORTINGTOEMPLOYEEID = EMP.EMPLOYEEID
WHERE  1 = 1 /*FILTER*/;";

        // ── Get single header (all FK joins, dual-dialect) ──────────────────

        public const string GET_JOB_SQL = @"
SELECT
    J.JOBID                        AS JobId,

    J.OUID                         AS OUId,
    OU.ORGANIZATIONUNITCODE        AS OUCode,
    OU.ORGANIZATIONUNITNAME        AS OUName,

    J.PERIODID                     AS PeriodId,
    P.PERIODCODE                   AS PeriodCode,
    P.PERIODNAME                   AS PeriodName,
    P.FROMDATE                     AS PeriodFromDate,
    P.TODATE                       AS PeriodToDate,

    J.BIZTRANSACTIONTYPEID         AS BIZTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE     AS BIZTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME     AS BIZTransactionTypeName,
    BTT.ASSETPOSTTYPE              AS BIZTransactionTypeAssetPostType,

    J.JOBNUMBER                    AS JobNumber,
    J.JOBDATE                      AS JobDate,
    J.REFERENCENUMBER              AS JobReferenceNumber,
    J.REFERENCEDATE                AS JobReferenceDate,
    J.JOBFORTYPE                   AS JobJobForType,
    J.TITLE                        AS JobTitle,
    J.DESCRIPTION                  AS JobDescription,
    J.REQUIREMENTS                 AS JobRequirements,
    J.PLACEOFWORK                  AS JobPlaceOfWork,

    J.EMPLOYMENTTYPE                AS EmployementTypeId,
    ET.GCMCODE                      AS EmployementTypeCode,
    ET.GCMNAME                      AS EmployementTypeName,

    J.EXPERIENCE                    AS ExperianceId,
    EX.GCMCODE                      AS ExperianceCode,
    EX.GCMNAME                      AS ExperianceName,

    J.DESIGNATIONLEVEL              AS DesignationLevelId,
    DL.GCMCODE                      AS DesignationLevelCode,
    DL.GCMNAME                      AS DesignationLevelName,

    J.JOBFUNCTION                   AS JobFunctionId,
    JF.GCMCODE                      AS JobFunctionCode,
    JF.GCMNAME                      AS JobFunctionName,

    J.EDUCATIONLEVEL                AS EducationLevelId,
    EL.GCMCODE                      AS EducationLevelCode,
    EL.GCMNAME                      AS EducationLevelName,

    J.DEPARTMENTID                  AS DepartmentId,
    DEP.DEPARTMENTCODE              AS DepartmentCode,
    DEP.DEPARTMENTNAME              AS DepartmentName,

    J.DESIGNATIONID                 AS DesignationId,
    DG.GCMCODE                      AS DesignationCode,
    DG.GCMNAME                      AS DesignationName,

    J.REPORTINGTODESIGNATIONID      AS ReportingToDesignationId,
    RD.GCMCODE                      AS ReportingToDesignationCode,
    RD.GCMNAME                      AS ReportingToDesignationName,

    J.HIRINGMANAGERID               AS HiringManagerId,
    HM.EMPLOYEECODE                 AS HiringManagerCode,
    HM.EMPLOYEENAME                 AS HiringManagerName,

    J.HRINCHARGEID                  AS HRInchargeId,
    HR.EMPLOYEECODE                 AS HRInchargeCode,
    HR.EMPLOYEENAME                 AS HRInchargeName,

    J.RECRUITMENTSTARTDATE          AS JobRecruitmentStartDate,
    J.RECRUITMENTENDDATE            AS JobRecruitmentEndDate,
    J.SALARYMAXIMUM                 AS JobSalaryMaximum,
    J.SALARYMINIMUM                 AS JobSalaryMinimum,
    J.SALARYPARTICULARS             AS JobSalaryParticulars,

    J.GRADEID                       AS GradeId,
    GR.GCMCODE                      AS GradeCode,
    GR.GCMNAME                      AS GradeName,

    J.BENEFITS                      AS JobBenifits,

    J.SELECTIONCYCLETYPEID          AS SelectionCycleTypeId,
    SC.SELECTIONCYCLECODE           AS SelectionCycleTypeCode,
    SC.SELECTIONCYCLENAME           AS SelectionCycleTypeName,
    SC.REMARKS                      AS SelectionCycleTypeRemarks,

    J.PARTYID                       AS PartyId,
    PT.PARTYCODE                    AS PartyCode,
    PT.PARTYNAME                    AS PartyName,

    J.NOOFREQUIREMENTS              AS JobNoOfRequirements,
    J.RESPONSIBILITIES              AS JobResponsibilities,
    J.REMARKS                       AS JobRemarks,
    J.JOBSTATUS                     AS JobJobStatus,

    J.STATUS                        AS JobStatus,
    J.VERSION                       AS JobVersion,

    J.CREATEDBYID                   AS JobCreatedById,
    CU.USERNAME                     AS JobCreatedByName,
    J.CREATEDON                     AS JobCreatedOn,
    J.MODIFIEDBYID                  AS JobModifiedById,
    MU.USERNAME                     AS JobModifiedByName,
    J.MODIFIEDON                    AS JobModifiedOn,

    J.JOBPROFILEID                  AS JobProfileId,

    J.PRIMARYLOCATIONID             AS PrimaryLocationId,
    PL.LOCATIONCODE                 AS PrimaryLocationCode,
    PL.LOCATIONNAME                 AS PrimaryLocationName,

    J.FORLOCATIONIDS                AS ForLocationIds,
    (
        SELECT STRING_AGG(FL.LOCATIONCODE, ', ')
        FROM MLOCATION FL
        WHERE ',' + ISNULL(J.FORLOCATIONIDS, '') + ','
              LIKE '%,' + CAST(FL.LOCATIONID AS VARCHAR(20)) + ',%'
    )                                AS ForLocationIdCodes,
    (
        SELECT STRING_AGG(FL.LOCATIONNAME, ', ')
        FROM MLOCATION FL
        WHERE ',' + ISNULL(J.FORLOCATIONIDS, '') + ','
              LIKE '%,' + CAST(FL.LOCATIONID AS VARCHAR(20)) + ',%'
    )                                AS ForLocationIdNames,

    J.FOROUID                       AS ForOUId,
    FOU.ORGANIZATIONUNITCODE        AS ForOUCode,
    FOU.ORGANIZATIONUNITNAME        AS ForOUName,

    J.FOROUIDS                      AS ForOUIds,
    (
        SELECT STRING_AGG(FO.ORGANIZATIONUNITCODE, ', ')
        FROM MORGANIZATIONUNIT FO
        WHERE ',' + ISNULL(J.FOROUIDS, '') + ','
              LIKE '%,' + CAST(FO.OUID AS VARCHAR(20)) + ',%'
    )                                AS ForOUCodes,
    (
        SELECT STRING_AGG(FO.ORGANIZATIONUNITNAME, ', ')
        FROM MORGANIZATIONUNIT FO
        WHERE ',' + ISNULL(J.FOROUIDS, '') + ','
              LIKE '%,' + CAST(FO.OUID AS VARCHAR(20)) + ',%'
    )                                AS ForOUNames,

    J.BASISON                       AS BasisOn,

    J.ALLOCATIONID                  AS AllocationId,
    AL.ALLOCATIONCODE               AS AllocationCode,
    AL.ALLOCATIONNAME               AS AllocationName,

    J.PARTYBRANCHID                 AS PartyBranchId,
    PB.PARTYBRANCHCODE              AS PartyBranchCode,
    PB.PARTYBRANCHNAME              AS PartyBranchName,

    J.REQUIREMENTBEFORE             AS RequirementBefore,
    J.REQUIREDFROMDATE              AS RequiredFromDate,
    J.REQUIREDTODATE                AS RequiredToDate,

    J.REPORTINGTOEMPLOYEEID         AS ReportingToEmployeeId,
    RE.EMPLOYEECODE                 AS ReportingToEmployeeCode,
    RE.EMPLOYEENAME                 AS ReportingToEmployeeName,

    J.REASONID                      AS ReasonId,
    RS.REASONCODE                   AS ReasonCode,
    RS.REASONNAME                   AS ReasonName,
    J.REASONFOROPENING              AS ReasonForOpening,

    J.SEARCHTYPE                    AS SearchType,
    J.ADDITIONALINFORMATION         AS AdditionalInformation,
    J.OTHERATTRACTIONS              AS OtherAttractions,

    J.SKILLSETID                    AS SkillSetId,
    SK.SKILLSETCODE                 AS SkillsetCode,
    SK.SKILLSETNAME                 AS SkillsetName,

    J.SELECTIONPROCESSID            AS SelectionProcessId,
    WT.WORKTYPECODE                 AS SelectionProcessCode,
    WT.WORKTYPENAME                 AS SelectionProcessName

FROM TJOB J
LEFT JOIN MORGANIZATIONUNIT OU  ON J.OUID = OU.OUID
LEFT JOIN MPERIOD P             ON J.PERIODID = P.PERIODID
LEFT JOIN MBIZTRANSACTIONTYPE BTT ON J.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MGCM ET               ON J.EMPLOYMENTTYPE = ET.GCMID
LEFT JOIN MGCM EX               ON J.EXPERIENCE = EX.GCMID
LEFT JOIN MGCM DL               ON J.DESIGNATIONLEVEL = DL.GCMID
LEFT JOIN MGCM JF               ON J.JOBFUNCTION = JF.GCMID
LEFT JOIN MGCM EL               ON J.EDUCATIONLEVEL = EL.GCMID
LEFT JOIN MDEPARTMENT DEP       ON J.DEPARTMENTID = DEP.DEPARTMENTID
LEFT JOIN MGCM DG                ON J.DESIGNATIONID = DG.GCMID
LEFT JOIN MGCM RD                ON J.REPORTINGTODESIGNATIONID = RD.GCMID
LEFT JOIN MEMPLOYEE HM          ON J.HIRINGMANAGERID = HM.EMPLOYEEID
LEFT JOIN MEMPLOYEE HR          ON J.HRINCHARGEID = HR.EMPLOYEEID
LEFT JOIN MGCM GR                ON J.GRADEID = GR.GCMID
LEFT JOIN MSELECTIONCYCLE SC    ON J.SELECTIONCYCLETYPEID = SC.SELECTIONCYCLEID
LEFT JOIN MPARTY PT             ON J.PARTYID = PT.PARTYID
LEFT JOIN MUSER CU              ON J.CREATEDBYID = CU.USERID
LEFT JOIN MUSER MU              ON J.MODIFIEDBYID = MU.USERID
LEFT JOIN MLOCATION PL          ON J.PRIMARYLOCATIONID = PL.LOCATIONID
LEFT JOIN MORGANIZATIONUNIT FOU ON J.FOROUID = FOU.OUID
LEFT JOIN MALLOCATION AL        ON J.ALLOCATIONID = AL.ALLOCATIONID
LEFT JOIN MPARTYBRANCH PB       ON J.PARTYBRANCHID = PB.PARTYBRANCHID
LEFT JOIN MEMPLOYEE RE          ON J.REPORTINGTOEMPLOYEEID = RE.EMPLOYEEID
LEFT JOIN MREASON RS            ON J.REASONID = RS.REASONID
LEFT JOIN MSKILLSET SK          ON J.SKILLSETID = SK.SKILLSETID
LEFT JOIN MWORKTYPE WT          ON J.SELECTIONPROCESSID = WT.WORKTYPEID
WHERE J.JOBID = @jobid;";

        public const string GET_JOB_PG = @"
SELECT
    J.JOBID                        AS ""JobId"",

    J.OUID                         AS ""OUId"",
    OU.ORGANIZATIONUNITCODE        AS ""OUCode"",
    OU.ORGANIZATIONUNITNAME        AS ""OUName"",

    J.PERIODID                     AS ""PeriodId"",
    P.PERIODCODE                   AS ""PeriodCode"",
    P.PERIODNAME                   AS ""PeriodName"",
    P.FROMDATE                     AS ""PeriodFromDate"",
    P.TODATE                       AS ""PeriodToDate"",

    J.BIZTRANSACTIONTYPEID         AS ""BIZTransactionTypeId"",
    BTT.BIZTRANSACTIONTYPECODE     AS ""BIZTransactionTypeCode"",
    BTT.BIZTRANSACTIONTYPENAME     AS ""BIZTransactionTypeName"",
    BTT.ASSETPOSTTYPE              AS ""BIZTransactionTypeAssetPostType"",

    J.JOBNUMBER                    AS ""JobNumber"",
    J.JOBDATE                      AS ""JobDate"",
    J.REFERENCENUMBER              AS ""JobReferenceNumber"",
    J.REFERENCEDATE                AS ""JobReferenceDate"",
    J.JOBFORTYPE                   AS ""JobJobForType"",
    J.TITLE                        AS ""JobTitle"",
    J.DESCRIPTION                  AS ""JobDescription"",
    J.REQUIREMENTS                 AS ""JobRequirements"",
    J.PLACEOFWORK                  AS ""JobPlaceOfWork"",

    J.EMPLOYMENTTYPE                AS ""EmployementTypeId"",
    ET.GCMCODE                      AS ""EmployementTypeCode"",
    ET.GCMNAME                      AS ""EmployementTypeName"",

    J.EXPERIENCE                    AS ""ExperianceId"",
    EX.GCMCODE                      AS ""ExperianceCode"",
    EX.GCMNAME                      AS ""ExperianceName"",

    J.DESIGNATIONLEVEL              AS ""DesignationLevelId"",
    DL.GCMCODE                      AS ""DesignationLevelCode"",
    DL.GCMNAME                      AS ""DesignationLevelName"",

    J.JOBFUNCTION                   AS ""JobFunctionId"",
    JF.GCMCODE                      AS ""JobFunctionCode"",
    JF.GCMNAME                      AS ""JobFunctionName"",

    J.EDUCATIONLEVEL                AS ""EducationLevelId"",
    EL.GCMCODE                      AS ""EducationLevelCode"",
    EL.GCMNAME                      AS ""EducationLevelName"",

    J.DEPARTMENTID                  AS ""DepartmentId"",
    DEP.DEPARTMENTCODE              AS ""DepartmentCode"",
    DEP.DEPARTMENTNAME              AS ""DepartmentName"",

    J.DESIGNATIONID                 AS ""DesignationId"",
    DG.GCMCODE                      AS ""DesignationCode"",
    DG.GCMNAME                      AS ""DesignationName"",

    J.REPORTINGTODESIGNATIONID      AS ""ReportingToDesignationId"",
    RD.GCMCODE                      AS ""ReportingToDesignationCode"",
    RD.GCMNAME                      AS ""ReportingToDesignationName"",

    J.HIRINGMANAGERID               AS ""HiringManagerId"",
    HM.EMPLOYEECODE                 AS ""HiringManagerCode"",
    HM.EMPLOYEENAME                 AS ""HiringManagerName"",

    J.HRINCHARGEID                  AS ""HRInchargeId"",
    HR.EMPLOYEECODE                 AS ""HRInchargeCode"",
    HR.EMPLOYEENAME                 AS ""HRInchargeName"",

    J.RECRUITMENTSTARTDATE          AS ""JobRecruitmentStartDate"",
    J.RECRUITMENTENDDATE            AS ""JobRecruitmentEndDate"",
    J.SALARYMAXIMUM                 AS ""JobSalaryMaximum"",
    J.SALARYMINIMUM                 AS ""JobSalaryMinimum"",
    J.SALARYPARTICULARS             AS ""JobSalaryParticulars"",

    J.GRADEID                       AS ""GradeId"",
    GR.GCMCODE                      AS ""GradeCode"",
    GR.GCMNAME                      AS ""GradeName"",

    J.BENEFITS                      AS ""JobBenifits"",

    J.SELECTIONCYCLETYPEID          AS ""SelectionCycleTypeId"",
    SC.SELECTIONCYCLECODE           AS ""SelectionCycleTypeCode"",
    SC.SELECTIONCYCLENAME           AS ""SelectionCycleTypeName"",
    SC.REMARKS                      AS ""SelectionCycleTypeRemarks"",

    J.PARTYID                       AS ""PartyId"",
    PT.PARTYCODE                    AS ""PartyCode"",
    PT.PARTYNAME                    AS ""PartyName"",

    J.NOOFREQUIREMENTS              AS ""JobNoOfRequirements"",
    J.RESPONSIBILITIES              AS ""JobResponsibilities"",
    J.REMARKS                       AS ""JobRemarks"",
    J.JOBSTATUS                     AS ""JobJobStatus"",

    J.STATUS                        AS ""JobStatus"",
    J.VERSION                       AS ""JobVersion"",

    J.CREATEDBYID                   AS ""JobCreatedById"",
    CU.USERNAME                     AS ""JobCreatedByName"",
    J.CREATEDON                     AS ""JobCreatedOn"",
    J.MODIFIEDBYID                  AS ""JobModifiedById"",
    MU.USERNAME                     AS ""JobModifiedByName"",
    J.MODIFIEDON                    AS ""JobModifiedOn"",

    J.JOBPROFILEID                  AS ""JobProfileId"",

    J.PRIMARYLOCATIONID             AS ""PrimaryLocationId"",
    PL.LOCATIONCODE                 AS ""PrimaryLocationCode"",
    PL.LOCATIONNAME                 AS ""PrimaryLocationName"",

    J.FORLOCATIONIDS                AS ""ForLocationIds"",
    (
        SELECT STRING_AGG(FL.LOCATIONCODE, ', ')
        FROM MLOCATION FL
        WHERE ',' || COALESCE(J.FORLOCATIONIDS, '') || ','
              LIKE '%,' || CAST(FL.LOCATIONID AS VARCHAR(20)) || ',%'
    )                                AS ""ForLocationIdCodes"",
    (
        SELECT STRING_AGG(FL.LOCATIONNAME, ', ')
        FROM MLOCATION FL
        WHERE ',' || COALESCE(J.FORLOCATIONIDS, '') || ','
              LIKE '%,' || CAST(FL.LOCATIONID AS VARCHAR(20)) || ',%'
    )                                AS ""ForLocationIdNames"",

    J.FOROUID                       AS ""ForOUId"",
    FOU.ORGANIZATIONUNITCODE        AS ""ForOUCode"",
    FOU.ORGANIZATIONUNITNAME        AS ""ForOUName"",

    J.FOROUIDS                      AS ""ForOUIds"",
    (
        SELECT STRING_AGG(FO.ORGANIZATIONUNITCODE, ', ')
        FROM MORGANIZATIONUNIT FO
        WHERE ',' || COALESCE(J.FOROUIDS, '') || ','
              LIKE '%,' || CAST(FO.OUID AS VARCHAR(20)) || ',%'
    )                                AS ""ForOUCodes"",
    (
        SELECT STRING_AGG(FO.ORGANIZATIONUNITNAME, ', ')
        FROM MORGANIZATIONUNIT FO
        WHERE ',' || COALESCE(J.FOROUIDS, '') || ','
              LIKE '%,' || CAST(FO.OUID AS VARCHAR(20)) || ',%'
    )                                AS ""ForOUNames"",

    J.BASISON                       AS ""BasisOn"",

    J.ALLOCATIONID                  AS ""AllocationId"",
    AL.ALLOCATIONCODE               AS ""AllocationCode"",
    AL.ALLOCATIONNAME               AS ""AllocationName"",

    J.PARTYBRANCHID                 AS ""PartyBranchId"",
    PB.PARTYBRANCHCODE              AS ""PartyBranchCode"",
    PB.PARTYBRANCHNAME              AS ""PartyBranchName"",

    J.REQUIREMENTBEFORE             AS ""RequirementBefore"",
    J.REQUIREDFROMDATE              AS ""RequiredFromDate"",
    J.REQUIREDTODATE                AS ""RequiredToDate"",

    J.REPORTINGTOEMPLOYEEID         AS ""ReportingToEmployeeId"",
    RE.EMPLOYEECODE                 AS ""ReportingToEmployeeCode"",
    RE.EMPLOYEENAME                 AS ""ReportingToEmployeeName"",

    J.REASONID                      AS ""ReasonId"",
    RS.REASONCODE                   AS ""ReasonCode"",
    RS.REASONNAME                   AS ""ReasonName"",
    J.REASONFOROPENING              AS ""ReasonForOpening"",

    J.SEARCHTYPE                    AS ""SearchType"",
    J.ADDITIONALINFORMATION         AS ""AdditionalInformation"",
    J.OTHERATTRACTIONS              AS ""OtherAttractions"",

    J.SKILLSETID                    AS ""SkillSetId"",
    SK.SKILLSETCODE                 AS ""SkillsetCode"",
    SK.SKILLSETNAME                 AS ""SkillsetName"",

    J.SELECTIONPROCESSID            AS ""SelectionProcessId"",
    WT.WORKTYPECODE                 AS ""SelectionProcessCode"",
    WT.WORKTYPENAME                 AS ""SelectionProcessName""

FROM TJOB J
LEFT JOIN MORGANIZATIONUNIT OU  ON J.OUID = OU.OUID
LEFT JOIN MPERIOD P             ON J.PERIODID = P.PERIODID
LEFT JOIN MBIZTRANSACTIONTYPE BTT ON J.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MGCM ET               ON J.EMPLOYMENTTYPE = ET.GCMID
LEFT JOIN MGCM EX               ON J.EXPERIENCE = EX.GCMID
LEFT JOIN MGCM DL               ON J.DESIGNATIONLEVEL = DL.GCMID
LEFT JOIN MGCM JF               ON J.JOBFUNCTION = JF.GCMID
LEFT JOIN MGCM EL               ON J.EDUCATIONLEVEL = EL.GCMID
LEFT JOIN MDEPARTMENT DEP       ON J.DEPARTMENTID = DEP.DEPARTMENTID
LEFT JOIN MGCM DG                ON J.DESIGNATIONID = DG.GCMID
LEFT JOIN MGCM RD                ON J.REPORTINGTODESIGNATIONID = RD.GCMID
LEFT JOIN MEMPLOYEE HM          ON J.HIRINGMANAGERID = HM.EMPLOYEEID
LEFT JOIN MEMPLOYEE HR          ON J.HRINCHARGEID = HR.EMPLOYEEID
LEFT JOIN MGCM GR                ON J.GRADEID = GR.GCMID
LEFT JOIN MSELECTIONCYCLE SC    ON J.SELECTIONCYCLETYPEID = SC.SELECTIONCYCLEID
LEFT JOIN MPARTY PT             ON J.PARTYID = PT.PARTYID
LEFT JOIN MUSER CU              ON J.CREATEDBYID = CU.USERID
LEFT JOIN MUSER MU              ON J.MODIFIEDBYID = MU.USERID
LEFT JOIN MLOCATION PL          ON J.PRIMARYLOCATIONID = PL.LOCATIONID
LEFT JOIN MORGANIZATIONUNIT FOU ON J.FOROUID = FOU.OUID
LEFT JOIN MALLOCATION AL        ON J.ALLOCATIONID = AL.ALLOCATIONID
LEFT JOIN MPARTYBRANCH PB       ON J.PARTYBRANCHID = PB.PARTYBRANCHID
LEFT JOIN MEMPLOYEE RE          ON J.REPORTINGTOEMPLOYEEID = RE.EMPLOYEEID
LEFT JOIN MREASON RS            ON J.REASONID = RS.REASONID
LEFT JOIN MSKILLSET SK          ON J.SKILLSETID = SK.SKILLSETID
LEFT JOIN MWORKTYPE WT          ON J.SELECTIONPROCESSID = WT.WORKTYPEID
WHERE J.JOBID = :jobid;";

        // ── Save / Update / Delete ───────────────────────────────────────────

        public const string SAVE_JOB = @"INSERT INTO TJOB
(
    JOBID, OUID, PERIODID, BIZTRANSACTIONTYPEID, JOBNUMBER, JOBDATE,
    REFERENCENUMBER, REFERENCEDATE, JOBFORTYPE, PARTYID, TITLE, DESCRIPTION,
    REQUIREMENTS, PLACEOFWORK, EMPLOYMENTTYPE, EXPERIENCE, DESIGNATIONLEVEL,
    JOBFUNCTION, EDUCATIONLEVEL, DEPARTMENTID, DESIGNATIONID, REPORTINGTODESIGNATIONID,
    HIRINGMANAGERID, HRINCHARGEID, RECRUITMENTSTARTDATE, RECRUITMENTENDDATE,
    SALARYMAXIMUM, SALARYMINIMUM, SALARYPARTICULARS, GRADEID, BENEFITS,
    SELECTIONCYCLETYPEID, NOOFREQUIREMENTS, RESPONSIBILITIES, REMARKS, JOBSTATUS,
    STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
    JOBPROFILEID, PRIMARYLOCATIONID, FORLOCATIONIDS, FOROUID, FOROUIDS, BASISON,
    ALLOCATIONID, PARTYBRANCHID, REQUIREMENTBEFORE, REQUIREDFROMDATE, REQUIREDTODATE,
    REPORTINGTOEMPLOYEEID, REASONID, REASONFOROPENING, SEARCHTYPE, ADDITIONALINFORMATION,
    OTHERATTRACTIONS, SKILLSETID, SELECTIONPROCESSID
)
VALUES
(
    @JobId, @OUId, @PeriodId, @BIZTransactionTypeId, @JobNumber, @JobDate,
    @JobReferenceNumber, @JobReferenceDate, @JobJobForType, @PartyId, @JobTitle, @JobDescription,
    @JobRequirements, @JobPlaceOfWork, @EmployementTypeId, @ExperianceId, @DesignationLevelId,
    @JobFunctionId, @EducationLevelId, @DepartmentId, @DesignationId, @ReportingToDesignationId,
    @HiringManagerId, @HRInchargeId, @JobRecruitmentStartDate, @JobRecruitmentEndDate,
    @JobSalaryMaximum, @JobSalaryMinimum, @JobSalaryParticulars, @GradeId, @JobBenifits,
    @SelectionCycleTypeId, @JobNoOfRequirements, @JobResponsibilities, @JobRemarks, @JobJobStatus,
    @JobStatus, @JobVersion, @JobCreatedById, @JobCreatedOn, @JobModifiedById, @JobModifiedOn,
    @JobProfileId, @PrimaryLocationId, @ForLocationIds, @ForOUId, @ForOUIds, @BasisOn,
    @AllocationId, @PartyBranchId, @RequirementBefore, @RequiredFromDate, @RequiredToDate,
    @ReportingToEmployeeId, @ReasonId, @ReasonForOpening, @SearchType, @AdditionalInformation,
    @OtherAttractions, @SkillSetId, @SelectionProcessId
);";

        public const string UPDATE_JOB = @"UPDATE TJOB
SET
    OUID = @OUId,
    PERIODID = @PeriodId,
    BIZTRANSACTIONTYPEID = @BIZTransactionTypeId,
    JOBNUMBER = @JobNumber,
    JOBDATE = @JobDate,
    REFERENCENUMBER = @JobReferenceNumber,
    REFERENCEDATE = @JobReferenceDate,
    JOBFORTYPE = @JobJobForType,
    PARTYID = @PartyId,
    TITLE = @JobTitle,
    DESCRIPTION = @JobDescription,
    REQUIREMENTS = @JobRequirements,
    PLACEOFWORK = @JobPlaceOfWork,
    EMPLOYMENTTYPE = @EmployementTypeId,
    EXPERIENCE = @ExperianceId,
    DESIGNATIONLEVEL = @DesignationLevelId,
    JOBFUNCTION = @JobFunctionId,
    EDUCATIONLEVEL = @EducationLevelId,
    DEPARTMENTID = @DepartmentId,
    DESIGNATIONID = @DesignationId,
    REPORTINGTODESIGNATIONID = @ReportingToDesignationId,
    HIRINGMANAGERID = @HiringManagerId,
    HRINCHARGEID = @HRInchargeId,
    RECRUITMENTSTARTDATE = @JobRecruitmentStartDate,
    RECRUITMENTENDDATE = @JobRecruitmentEndDate,
    SALARYMAXIMUM = @JobSalaryMaximum,
    SALARYMINIMUM = @JobSalaryMinimum,
    SALARYPARTICULARS = @JobSalaryParticulars,
    GRADEID = @GradeId,
    BENEFITS = @JobBenifits,
    SELECTIONCYCLETYPEID = @SelectionCycleTypeId,
    NOOFREQUIREMENTS = @JobNoOfRequirements,
    RESPONSIBILITIES = @JobResponsibilities,
    REMARKS = @JobRemarks,
    JOBSTATUS = @JobJobStatus,
    STATUS = @JobStatus,
    VERSION = VERSION + 1,
    MODIFIEDBYID = @JobModifiedById,
    MODIFIEDON = @JobModifiedOn,
    JOBPROFILEID = @JobProfileId,
    PRIMARYLOCATIONID = @PrimaryLocationId,
    FORLOCATIONIDS = @ForLocationIds,
    FOROUID = @ForOUId,
    FOROUIDS = @ForOUIds,
    BASISON = @BasisOn,
    ALLOCATIONID = @AllocationId,
    PARTYBRANCHID = @PartyBranchId,
    REQUIREMENTBEFORE = @RequirementBefore,
    REQUIREDFROMDATE = @RequiredFromDate,
    REQUIREDTODATE = @RequiredToDate,
    REPORTINGTOEMPLOYEEID = @ReportingToEmployeeId,
    REASONID = @ReasonId,
    REASONFOROPENING = @ReasonForOpening,
    SEARCHTYPE = @SearchType,
    ADDITIONALINFORMATION = @AdditionalInformation,
    OTHERATTRACTIONS = @OtherAttractions,
    SKILLSETID = @SkillSetId,
    SELECTIONPROCESSID = @SelectionProcessId
WHERE JOBID = @JobId;";

        public const string DELETE_JOB = @"DELETE FROM TJOB WHERE JOBID = @jobid;";

        // ── Financial period check (mirrors TimeSlipQB.GET_PERIOD) ─────────

        public const string GET_PERIOD = @"SELECT TOP 1
                                            PERIODID        AS PeriodId,
                                            FROMDATE  AS PeriodFromDate,
                                            TODATE    AS PeriodToDate
                                        FROM MPERIOD
                                        WHERE PERIODID = @PeriodId;";

        // ── Reporting-chain / "must be an employee" check ───────────────────

        public const string GET_EMPLOYEE_FOR_JOB = @"
            SELECT
                e.EMPLOYEEID                AS EmployeeId,
                e.EMPLOYEECODE               AS EmployeeCode,
                e.EMPLOYEENAME               AS EmployeeName,
                e.REPORTINGTOEMPLOYEEID      AS ReportingToEmployeeId
            FROM MEMPLOYEE e
            WHERE e.EMPLOYEEID = @EmployeeId;";

        // ── List grid (reasonably scoped, prioritizing key fields) ──────────

        public const string GET_JOB_LIST = @"
SELECT
    J.JOBID                    AS JobId,
    J.JOBNUMBER                 AS JobNumber,
    J.JOBDATE                   AS JobDate,
    J.TITLE                     AS JobTitle,

    J.OUID                      AS OUId,
    OU.ORGANIZATIONUNITCODE     AS OUCode,
    OU.ORGANIZATIONUNITNAME     AS OUName,

    J.PERIODID                  AS PeriodId,
    P.PERIODCODE                AS PeriodCode,
    P.PERIODNAME                AS PeriodName,

    J.BIZTRANSACTIONTYPEID      AS BIZTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE  AS BIZTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME  AS BIZTransactionTypeName,

    J.DEPARTMENTID               AS DepartmentId,
    DEP.DEPARTMENTCODE           AS DepartmentCode,
    DEP.DEPARTMENTNAME           AS DepartmentName,

    J.DESIGNATIONID               AS DesignationId,
    DG.GCMCODE                    AS DesignationCode,
    DG.GCMNAME                    AS DesignationName,

    J.HIRINGMANAGERID            AS HiringManagerId,
    HM.EMPLOYEECODE              AS HiringManagerCode,
    HM.EMPLOYEENAME              AS HiringManagerName,

    J.RECRUITMENTSTARTDATE       AS JobRecruitmentStartDate,
    J.RECRUITMENTENDDATE         AS JobRecruitmentEndDate,
    J.NOOFREQUIREMENTS           AS JobNoOfRequirements,

    J.JOBSTATUS                  AS JobJobStatus,
    J.STATUS                     AS JobStatus

FROM TJOB J
LEFT JOIN MORGANIZATIONUNIT OU    ON J.OUID = OU.OUID
LEFT JOIN MPERIOD P               ON J.PERIODID = P.PERIODID
LEFT JOIN MBIZTRANSACTIONTYPE BTT ON J.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MDEPARTMENT DEP         ON J.DEPARTMENTID = DEP.DEPARTMENTID
LEFT JOIN MGCM DG                 ON J.DESIGNATIONID = DG.GCMID
LEFT JOIN MEMPLOYEE HM            ON J.HIRINGMANAGERID = HM.EMPLOYEEID
WHERE
    (@SearchText IS NULL OR @SearchText = ''
     OR J.JOBNUMBER LIKE '%' + @SearchText + '%'
     OR J.TITLE LIKE '%' + @SearchText + '%')
    AND (@StatusEquals IS NULL OR J.STATUS = @StatusEquals)
    AND (@StatusNotEquals IS NULL OR J.STATUS <> @StatusNotEquals)
    AND (@OUId IS NULL OR J.OUID = @OUId)
    AND (@DepartmentId IS NULL OR J.DEPARTMENTID = @DepartmentId)
ORDER BY J.JOBNUMBER;";

        // ── GB4 parity: EmployeeBLL.GetJOBDetailReport / EmployeeQueryBuilder.GET_EMPLOYEE_JOB_DETAIL ──
        // Wide job-opening report joining TJOB with GCM masters (employment type, experience,
        // designation level, job function, education level, grade, work type/selection process)
        // plus department/designation/party/partybranch/employee masters.

        public const string GET_JOB_DETAIL_REPORT = @"
SELECT
    J.JOBID                        AS JobId,
    J.JOBNUMBER                    AS JobNumber,
    J.JOBDATE                      AS JobDate,
    J.REFERENCENUMBER              AS JobReferenceNumber,
    J.REFERENCEDATE                AS JobReferenceDate,
    CASE WHEN J.JOBFORTYPE = 0 THEN 'Internal Need' ELSE 'Third Party' END AS JobForType,

    BTT.BIZTRANSACTIONTYPECODE     AS BIZTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME     AS BIZTransactionTypeName,

    J.PARTYID                      AS PartyId,
    PT.PARTYCODE                   AS PartyCode,
    PT.PARTYNAME                   AS PartyName,

    J.PARTYBRANCHID                 AS PartyBranchId,
    PB.PARTYBRANCHCODE              AS PartyBranchCode,
    PB.PARTYBRANCHNAME              AS PartyBranchName,

    J.TITLE                        AS JobTitle,
    J.DESCRIPTION                  AS JobDescription,
    J.REQUIREMENTS                 AS JobRequirements,
    J.PLACEOFWORK                  AS JobPlaceOfWork,

    J.EMPLOYMENTTYPE                AS EmployementTypeId,
    ET.GCMCODE                      AS EmployementTypeCode,
    ET.GCMNAME                      AS EmployementTypeName,

    J.EXPERIENCE                    AS ExperianceId,
    EX.GCMCODE                      AS ExperianceCode,
    EX.GCMNAME                      AS ExperianceName,

    J.DESIGNATIONLEVEL              AS DesignationLevelId,
    DL.GCMCODE                      AS DesignationLevelCode,
    DL.GCMNAME                      AS DesignationLevelName,

    J.JOBFUNCTION                   AS JobFunctionId,
    JF.GCMCODE                      AS JobFunctionCode,
    JF.GCMNAME                      AS JobFunctionName,

    J.EDUCATIONLEVEL                AS EducationLevelId,
    EL.GCMCODE                      AS EducationLevelCode,
    EL.GCMNAME                      AS EducationLevelName,

    J.DEPARTMENTID                  AS DepartmentId,
    DEP.DEPARTMENTCODE              AS DepartmentCode,
    DEP.DEPARTMENTNAME              AS DepartmentName,

    J.DESIGNATIONID                 AS DesignationId,
    DG.GCMCODE                      AS DesignationCode,
    DG.GCMNAME                      AS DesignationName,

    J.REPORTINGTODESIGNATIONID      AS ReportingToDesignationId,
    RD.GCMCODE                      AS ReportingToDesignationCode,
    RD.GCMNAME                      AS ReportingToDesignationName,

    J.HIRINGMANAGERID               AS HiringManagerId,
    HM.EMPLOYEECODE                 AS HiringManagerCode,
    HM.EMPLOYEENAME                 AS HiringManagerName,

    J.HRINCHARGEID                  AS HRInchargeId,
    HR.EMPLOYEECODE                 AS HRInchargeCode,
    HR.EMPLOYEENAME                 AS HRInchargeName,

    J.GRADEID                       AS GradeId,
    GR.GCMCODE                      AS GradeCode,
    GR.GCMNAME                      AS GradeName,

    J.SELECTIONCYCLETYPEID          AS SelectionCycleTypeId,
    SC.SELECTIONCYCLECODE           AS SelectionCycleTypeCode,
    SC.SELECTIONCYCLENAME           AS SelectionCycleTypeName,
    SC.REMARKS                      AS SelectionCycleTypeRemarks,

    J.PRIMARYLOCATIONID             AS PrimaryLocationId,
    PL.LOCATIONCODE                 AS PrimaryLocationCode,
    PL.LOCATIONNAME                 AS PrimaryLocationName,

    J.FOROUID                       AS ForOUId,
    FOU.ORGANIZATIONUNITCODE        AS ForOUCode,
    FOU.ORGANIZATIONUNITNAME        AS ForOUName,

    J.ALLOCATIONID                  AS AllocationId,
    AL.ALLOCATIONNAME               AS AllocationName,

    J.REASONID                      AS ReasonId,
    RS.REASONCODE                   AS ReasonCode,
    RS.REASONNAME                   AS ReasonName,
    J.REASONFOROPENING              AS ReasonForOpening,

    J.SEARCHTYPE                    AS SearchType,
    J.ADDITIONALINFORMATION         AS AdditionalInformation,
    J.OTHERATTRACTIONS              AS OtherAttractions,

    J.SKILLSETID                    AS SkillSetId,
    SK.SKILLSETCODE                 AS SkillsetCode,
    SK.SKILLSETNAME                 AS SkillsetName,

    J.SELECTIONPROCESSID            AS SelectionProcessId,
    WT.WORKTYPECODE                 AS SelectionProcessCode,
    WT.WORKTYPENAME                 AS SelectionProcessName,

    J.REQUIREMENTBEFORE             AS RequirementBefore,
    J.REQUIREDFROMDATE              AS RequiredFromDate,
    J.REQUIREDTODATE                AS RequiredToDate,

    J.REPORTINGTOEMPLOYEEID         AS ReportingToEmployeeId,
    RE.EMPLOYEECODE                 AS ReportingToEmployeeCode,
    RE.EMPLOYEENAME                 AS ReportingToEmployeeName,

    P.PERIODNAME                    AS PeriodName,

    J.RECRUITMENTSTARTDATE          AS JobRecruitmentStartDate,
    J.RECRUITMENTENDDATE            AS JobRecruitmentEndDate,
    J.SALARYMINIMUM                 AS JobSalaryMinimum,
    J.SALARYMAXIMUM                 AS JobSalaryMaximum,
    J.SALARYPARTICULARS             AS JobSalaryParticulars,
    J.BENEFITS                      AS JobBenifits,
    J.NOOFREQUIREMENTS              AS JobNoOfRequirements,
    J.RESPONSIBILITIES              AS JobResponsibilities,
    J.REMARKS                       AS JobRemarks,

    CASE J.JOBSTATUS
        WHEN 0 THEN 'Draft'
        WHEN 1 THEN 'Pending'
        WHEN 2 THEN 'Approved'
        WHEN 3 THEN 'Open'
        WHEN 4 THEN 'InProgress'
        ELSE 'Closed'
    END                              AS JobStatus,

    CASE J.STATUS
        WHEN 0 THEN 'Pending'
        WHEN 1 THEN 'Active'
        WHEN 2 THEN 'Deleted'
        WHEN 3 THEN 'Amended'
        WHEN 4 THEN 'Inactive'
        ELSE 'Archived'
    END                              AS Status,

    J.JOBSTATUS                     AS StatusofJob

FROM TJOB J
LEFT JOIN MPERIOD P             ON J.PERIODID = P.PERIODID
LEFT JOIN MBIZTRANSACTIONTYPE BTT ON J.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MPARTY PT             ON J.PARTYID = PT.PARTYID
LEFT JOIN MPARTYBRANCH PB       ON J.PARTYBRANCHID = PB.PARTYBRANCHID
LEFT JOIN MGCM ET               ON J.EMPLOYMENTTYPE = ET.GCMID
LEFT JOIN MGCM EX               ON J.EXPERIENCE = EX.GCMID
LEFT JOIN MGCM DL               ON J.DESIGNATIONLEVEL = DL.GCMID
LEFT JOIN MGCM JF               ON J.JOBFUNCTION = JF.GCMID
LEFT JOIN MGCM EL               ON J.EDUCATIONLEVEL = EL.GCMID
LEFT JOIN MDEPARTMENT DEP       ON J.DEPARTMENTID = DEP.DEPARTMENTID
LEFT JOIN MGCM DG                ON J.DESIGNATIONID = DG.GCMID
LEFT JOIN MGCM RD                ON J.REPORTINGTODESIGNATIONID = RD.GCMID
LEFT JOIN MEMPLOYEE HM          ON J.HIRINGMANAGERID = HM.EMPLOYEEID
LEFT JOIN MEMPLOYEE HR          ON J.HRINCHARGEID = HR.EMPLOYEEID
LEFT JOIN MGCM GR                ON J.GRADEID = GR.GCMID
LEFT JOIN MSELECTIONCYCLE SC    ON J.SELECTIONCYCLETYPEID = SC.SELECTIONCYCLEID
LEFT JOIN MLOCATION PL          ON J.PRIMARYLOCATIONID = PL.LOCATIONID
LEFT JOIN MORGANIZATIONUNIT FOU ON J.FOROUID = FOU.OUID
LEFT JOIN MALLOCATION AL        ON J.ALLOCATIONID = AL.ALLOCATIONID
LEFT JOIN MEMPLOYEE RE          ON J.REPORTINGTOEMPLOYEEID = RE.EMPLOYEEID
LEFT JOIN MREASON RS            ON J.REASONID = RS.REASONID
LEFT JOIN MSKILLSET SK          ON J.SKILLSETID = SK.SKILLSETID
LEFT JOIN MWORKTYPE WT          ON J.SELECTIONPROCESSID = WT.WORKTYPEID
WHERE 1 = 1
:replacefields";

        public const string GET_JOB_DETAIL_REPORT_COUNT = @"
SELECT COUNT(1)
FROM TJOB J
WHERE 1 = 1
:replacefields;";
    }
}