# Human Resources Management - SPARQL Query Templates
# Collection of 12 essential queries for HR operations

PREFIX hr: <https://ggen.ai/ontology/hr-management#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>

# Query 1: Employee directory with department and position
SELECT ?employee ?name ?email ?department ?position ?manager
WHERE {
    ?employee a hr:Employee ;
              foaf:name ?name ;
              foaf:mbox ?email ;
              hr:worksIn ?dept ;
              hr:hasPosition ?pos ;
              hr:reportsTo ?mgr .
    ?dept hr:departmentName ?department .
    ?pos hr:positionTitle ?position .
    ?mgr foaf:name ?manager .
    FILTER(?department = "Engineering")
}
ORDER BY ?name

# Query 2: Performance review summary
SELECT ?employee ?name ?reviewDate ?period ?overallRating ?reviewer
WHERE {
    ?employee a hr:Employee ;
              foaf:name ?name ;
              hr:hasReview ?review .
    ?review hr:reviewDate ?reviewDate ;
            hr:reviewPeriod ?period ;
            hr:overallRating ?overallRating ;
            hr:reviewer ?reviewerObj .
    ?reviewerObj foaf:name ?reviewer .
    FILTER(?reviewDate >= "2024-01-01"^^xsd:date)
}
ORDER BY DESC(?reviewDate)

# Query 3: Open job applications by status
SELECT ?posting ?positionTitle ?applicant ?applicationDate ?status ?interviews
WHERE {
    ?posting a hr:JobPosting ;
             hr:forPosition ?position ;
             hr:hasApplication ?application .
    ?position hr:positionTitle ?positionTitle .
    ?application hr:applicant ?applicantObj ;
                 hr:applicationDate ?applicationDate ;
                 hr:applicationStatus ?status .
    ?applicantObj foaf:name ?applicant .
    {
        SELECT ?application (COUNT(?interview) AS ?interviews)
        WHERE {
            OPTIONAL {
                ?application hr:hasInterview ?interview
            }
        }
        GROUP BY ?application
    }
    FILTER(?status IN ("screening", "interview", "offer"))
}
ORDER BY ?applicationDate

# Query 4: Attendance and time tracking
SELECT ?employee ?name ?date ?checkIn ?checkOut ?hoursWorked
WHERE {
    ?employee a hr:Employee ;
              foaf:name ?name ;
              hr:hasAttendance ?attendance .
    ?attendance hr:attendanceDate ?date ;
                hr:checkInTime ?checkIn ;
                hr:checkOutTime ?checkOut .
    BIND((?checkOut - ?checkIn) AS ?hoursWorked)
    FILTER(?date >= "2025-01-01"^^xsd:date)
}
ORDER BY ?date ?name

# Query 5: Leave balance and requests
SELECT ?employee ?name ?leaveType ?startDate ?endDate ?leaveDays ?status
WHERE {
    ?employee a hr:Employee ;
              foaf:name ?name ;
              hr:hasLeave ?leave .
    ?leave a ?leaveType ;
           hr:leaveStartDate ?startDate ;
           hr:leaveEndDate ?endDate ;
           hr:leaveDays ?leaveDays ;
           hr:leaveStatus ?status .
    FILTER(?leaveType IN (hr:Vacation, hr:SickLeave, hr:PersonalLeave))
}
ORDER BY ?startDate

# Query 6: Payroll summary with deductions
SELECT ?employee ?name ?baseSalary ?bonus ?grossPay ?deductions ?netPay ?paymentDate
WHERE {
    ?employee a hr:Employee ;
              foaf:name ?name ;
              hr:hasSalary ?salary ;
              hr:hasPayslip ?payslip .
    ?salary hr:baseSalary ?baseSalary .
    ?payslip hr:grossPay ?grossPay ;
             hr:netPay ?netPay ;
             hr:paymentDate ?paymentDate .
    OPTIONAL {
        ?payslip hr:bonus ?bonus
    }
    {
        SELECT ?payslip (SUM(?deduction) AS ?deductions)
        WHERE {
            ?payslip hr:hasDeduction ?ded .
            ?ded hr:deductionAmount ?deduction
        }
        GROUP BY ?payslip
    }
}
ORDER BY ?paymentDate

# Query 7: Benefits enrollment status
SELECT ?employee ?name ?benefitType ?enrollmentDate ?coverageLevel ?premium
WHERE {
    ?employee a hr:Employee ;
              foaf:name ?name ;
              hr:hasEnrollment ?enrollment .
    ?enrollment hr:enrolledIn ?benefit ;
                hr:enrollmentDate ?enrollmentDate .
    ?benefit a ?benefitType ;
             hr:coverageLevel ?coverageLevel ;
             hr:premiumAmount ?premium .
    FILTER(?benefitType IN (hr:HealthInsurance, hr:RetirementPlan, hr:LifeInsurance))
}

# Query 8: Training and certification tracking
SELECT ?employee ?name ?training ?certificationName ?certDate ?expirationDate ?daysUntilExpiry
WHERE {
    ?employee a hr:Employee ;
              foaf:name ?name ;
              hr:hasCertification ?cert .
    ?cert hr:certificationName ?certificationName ;
          hr:certificationDate ?certDate ;
          hr:expirationDate ?expirationDate .
    OPTIONAL {
        ?cert hr:relatedTraining ?training
    }
    BIND((?expirationDate - NOW()) AS ?daysUntilExpiry)
    FILTER(?daysUntilExpiry <= 90)
}
ORDER BY ?daysUntilExpiry

# Query 9: Organization chart hierarchy
SELECT ?employee ?name ?position ?level ?manager ?department
WHERE {
    ?employee a hr:Employee ;
              foaf:name ?name ;
              hr:hasPosition ?pos ;
              hr:worksIn ?dept .
    ?pos hr:positionTitle ?position ;
         hr:jobLevel ?levelObj .
    ?levelObj hr:levelOrder ?level .
    ?dept hr:departmentName ?department .
    OPTIONAL {
        ?employee hr:reportsTo ?mgr .
        ?mgr foaf:name ?manager
    }
}
ORDER BY ?level ?department

# Query 10: Goal progress and KPIs
SELECT ?employee ?name ?goal ?goalDescription ?targetDate ?progress ?status
WHERE {
    ?employee a hr:Employee ;
              foaf:name ?name ;
              hr:hasGoal ?goalObj .
    ?goalObj hr:goalDescription ?goalDescription ;
             hr:goalTargetDate ?targetDate ;
             hr:goalProgress ?progress ;
             hr:goalStatus ?status .
    FILTER(?status = "in-progress")
}
ORDER BY ?targetDate

# Query 11: Recruitment pipeline analytics
SELECT ?position ?stage (COUNT(?application) AS ?count) (AVG(?daysInStage) AS ?avgDays)
WHERE {
    ?posting a hr:JobPosting ;
             hr:forPosition ?position ;
             hr:hasApplication ?application .
    ?application hr:applicationStatus ?stage ;
                 hr:stageEnterDate ?enterDate .
    BIND((NOW() - ?enterDate) AS ?daysInStage)
}
GROUP BY ?position ?stage
ORDER BY ?position ?stage

# Query 12: Compensation analysis by department and position
SELECT ?department ?position (AVG(?salary) AS ?avgSalary) (COUNT(?employee) AS ?employees)
WHERE {
    ?employee a hr:Employee ;
              hr:worksIn ?dept ;
              hr:hasPosition ?pos ;
              hr:hasSalary ?salaryObj .
    ?dept hr:departmentName ?department .
    ?pos hr:positionTitle ?position .
    ?salaryObj hr:baseSalary ?salary .
}
GROUP BY ?department ?position
ORDER BY ?department ?avgSalary
