using System;

namespace RecruitmentDAL.Query.JobProfile
{
    public static class JobProfileQB
    {
        #region GET

        public const string GET_JOBPROFILE = @"
SELECT 
    JP.JOBPROFILEID       AS JobProfileId,
    JP.JOBPROFILENAME     AS JobProfileName,
    JP.JOBPROFILETYPE     AS JobProfileType,

    JP.DEPARTMENTID       AS DepartmentId,
    D.DEPARTMENTNAME      AS DepartmentName,

    JP.DESIGNATIONID      AS DesignationId,
    DG.GCMNAME            AS DesignationName,

    JP.REPORTINGTODESIGNATIONID AS ReportingToDesignationId,
    RDG.GCMNAME           AS ReportingToDesignationName,

    JP.JOBID              AS JobId,
    J.JOBTITLE            AS JobTitle,

    JP.EMPLOYEEID         AS EmployeeId,
    E.EMPLOYEENAME        AS EmployeeName,

    JP.REPORTINGTOID      AS ReportingToId,
    RE.EMPLOYEENAME       AS ReportingToName,

    JP.FROMDATE           AS JobProfileFromDate,
    JP.TODATE             AS JobProfileToDate,

    JP.SUMMARY            AS JobProfileSummary,
    JP.EXPERIENCE         AS Experience,
    JP.QUALIFICATION      AS Qualification,
    JP.REMARKS            AS JobProfileRemarks,

    -- ✅ BASE FIELDS
    JP.SORTORDER          AS JobProfileSortOrder,
    JP.STATUS             AS JobProfileStatus,
    JP.VERSION            AS JobProfileVersion,
    JP.SOURCETYPE         AS JobProfileSourceType,

    -- ✅ STANDARD FIELDS
    JP.CREATEDBYID        AS JobProfileCreatedById,
    UC.USERNAME           AS JobProfileCreatedByName,
    JP.CREATEDON          AS JobProfileCreatedOn,

    JP.MODIFIEDBYID       AS JobProfileModifiedById,
    UM.USERNAME           AS JobProfileModifiedByName,
    JP.MODIFIEDON         AS JobProfileModifiedOn

FROM MJOBPROFILE JP

LEFT JOIN MDEPARTMENT D 
    ON D.DEPARTMENTID = JP.DEPARTMENTID

LEFT JOIN MGCM DG 
    ON DG.GCMID = JP.DESIGNATIONID

LEFT JOIN MGCM RDG 
    ON RDG.GCMID = JP.REPORTINGTODESIGNATIONID

LEFT JOIN TJOB J 
    ON J.JOBID = JP.JOBID

LEFT JOIN MEMPLOYEE E 
    ON E.EMPLOYEEID = JP.EMPLOYEEID

LEFT JOIN MEMPLOYEE RE 
    ON RE.EMPLOYEEID = JP.REPORTINGTOID

-- ✅ STANDARD USER JOINS
LEFT JOIN MUSER UC 
    ON JP.CREATEDBYID = UC.USERID

LEFT JOIN MUSER UM 
    ON JP.MODIFIEDBYID = UM.USERID

WHERE JP.JOBPROFILEID = @JobProfileId;
";

        public const string GET_JOBPROFILEDETAIL = @"
        SELECT 
            JPD.JOBPROFILEDETAILID AS JobProfileDetailId,
            JPD.JOBPROFILEID       AS JobProfileId,
            JPD.DETAILTYPE         AS JobProfileDetailDetailType,
            JPD.SLNO               AS JobProfileDetailSlNo,
            JPD.DOMAINID           AS DomainId,
            D.GCMNAME              AS DomainName,
            JPD.SECTIONID          AS SectionId,
            S.GCMNAME              AS SectionName,
            JPD.OBJECTIVEID        AS ObjectiveId,
            O.OBJECTIVENAME        AS ObjectiveName,
            JPD.KRAID              AS KRAId,
            K.KRANAME              AS KRAName,
            JPD.SKILLID            AS SkillId,
            SK.SKILLNAME           AS SkillName,
            JPD.LEVELID            AS LevelId,
            JPD.PARTICULARS        AS JobProfileDetailParticulars
        FROM MJOBPROFILEDETAIL JPD
        LEFT JOIN MGCM D ON D.GCMID = JPD.DOMAINID
        LEFT JOIN MGCM S ON S.GCMID = JPD.SECTIONID
        LEFT JOIN MOBJECTIVE O ON O.OBJECTIVEID = JPD.OBJECTIVEID
        LEFT JOIN MKRA K ON K.KRAID = JPD.KRAID
        LEFT JOIN MSKILL SK ON SK.SKILLID = JPD.SKILLID
        LEFT JOIN MLEVEL L ON L.LEVELID = JPD.LEVELID
        WHERE JPD.JOBPROFILEDETAILID = @JobProfileDetailId;
        ";

        #endregion

        #region SAVE

        public const string SAVE_JOBPROFILE = @"
        INSERT INTO MJOBPROFILE
        (
            JOBPROFILEID, JOBPROFILENAME, JOBPROFILETYPE,
            DEPARTMENTID, DESIGNATIONID, REPORTINGTODESIGNATIONID,
            JOBID, EMPLOYEEID, REPORTINGTOID,
            FROMDATE, TODATE, SUMMARY, REMARKS,
            EXPERIENCE, QUALIFICATION,
            SORTORDER, STATUS, VERSION,
            CREATEDBYID, CREATEDON,
            MODIFIEDBYID, MODIFIEDON,
            SOURCETYPE
        )
        VALUES
        (
            @JobProfileId, @JobProfileName, @JobProfileType,
            @DepartmentId, @DesignationId, @ReportingToDesignationId,
            @JobId, @EmployeeId, @ReportingToId,
            @JobProfileFromDate, @JobProfileToDate,
            @JobProfileSummary, @JobProfileRemarks,
            @Experience, @Qualification,
            @JobProfileSortOrder, @JobProfileStatus, @JobProfileVersion,
            @JobProfileCreatedById, @JobProfileCreatedOn,
            @JobProfileModifiedById, @JobProfileModifiedOn,
            @JobProfileSourceType
        );
        ";

        public const string SAVE_JOBPROFILEDETAIL = @"
        INSERT INTO MJOBPROFILEDETAIL
        (
            JOBPROFILEDETAILID, JOBPROFILEID, DETAILTYPE,
            SLNO, DOMAINID, SECTIONID,
            OBJECTIVEID, KRAID, PARTICULARS, SKILLID, LEVELID
        )
        VALUES
        (
            @JobProfileDetailId, @JobProfileId, @JobProfileDetailDetailType,
            @JobProfileDetailSlNo, @DomainId, @SectionId,
            @ObjectiveId, @KRAId, @JobProfileDetailParticulars, @SkillId, @LevelId
        );
        ";

        #endregion

        #region UPDATE

        public const string UPDATE_JOBPROFILE = @"
        UPDATE MJOBPROFILE SET
            JOBPROFILENAME = @JobProfileName,
            JOBPROFILETYPE = @JobProfileType,
            DEPARTMENTID = @DepartmentId,
            DESIGNATIONID = @DesignationId,
            REPORTINGTODESIGNATIONID = @ReportingToDesignationId,
            JOBID = @JobId,
            EMPLOYEEID = @EmployeeId,
            REPORTINGTOID = @ReportingToId,
            FROMDATE = @JobProfileFromDate,
            TODATE = @JobProfileToDate,
            SUMMARY = @JobProfileSummary,
            EXPERIENCE = @Experience,
            QUALIFICATION = @Qualification,
            REMARKS = @JobProfileRemarks,
            MODIFIEDBYID = @JobProfileModifiedById,
            MODIFIEDON = @JobProfileModifiedOn
        WHERE JOBPROFILEID = @JobProfileId;
        ";

        public const string UPDATE_JOBPROFILEDETAIL = @"
        UPDATE MJOBPROFILEDETAIL SET
            DETAILTYPE = @JobProfileDetailDetailType,
            SLNO = @JobProfileDetailSlNo,
            DOMAINID = @DomainId,
            SECTIONID = @SectionId,
            OBJECTIVEID = @ObjectiveId,
            KRAID = @KRAId,
            PARTICULARS = @JobProfileDetailParticulars,
            SKILLID = @SkillId,
            LEVELID = @LevelId
        WHERE JOBPROFILEDETAILID = @JobProfileDetailId;
        ";

        #endregion

        #region DELETE

        public const string DELETE_JOBPROFILE = @"
        DELETE FROM MJOBPROFILE
        WHERE JOBPROFILEID = @JobProfileId;
        ";

        public const string DELETE_JOBPROFILEDETAIL = @"
        DELETE FROM MJOBPROFILEDETAIL
        WHERE JOBPROFILEDETAILID = @JobProfileDetailId;
        ";

        #endregion

        #region SELECT LIST

        public const string GET_SELECTLIST_JOBPROFILE = @"
        SELECT 
            JOBPROFILEID AS Id,
            JOBPROFILENAME AS Name
        FROM MJOBPROFILE
        WHERE STATUS = 1
        ORDER BY JOBPROFILENAME;
        ";

        public const string GET_SELECTLIST_JOBPROFILEDETAIL = @"
        SELECT 
            JOBPROFILEDETAILID AS Id,
            PARTICULARS AS Particulars
        FROM MJOBPROFILEDETAIL
        ORDER BY SLNO;
        ";

        #endregion
    }
}