Code Editor for app.py

import streamlit as st
import pandas as pd
import plotly.express as px
import matplotlib.pyplot as plt
from sqlalchemy import create_engine, Column, Integer, String, Float, Date, ForeignKey, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, Session
import datetime
import random

# Set page configuration
st.set_page_config(
    page_title="HRTech Dashboard",
    page_icon="👔",
    layout="wide"
)

# Initialize database
engine = create_engine("sqlite:///hrtech.db")
Base = declarative_base()

# Define database models
class Employer(Base):
    __tablename__ = "employers"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    industry = Column(String(50))
    location = Column(String(100))
    company_size = Column(String(50))
    founded_year = Column(Integer)
    jobs = relationship("Job", back_populates="employer")
    
class Job(Base):
    __tablename__ = "jobs"
    
    id = Column(Integer, primary_key=True)
    employer_id = Column(Integer, ForeignKey("employers.id"))
    title = Column(String(100), nullable=False)
    department = Column(String(50))
    location = Column(String(100))
    salary_min = Column(Float)
    salary_max = Column(Float)
    experience_required = Column(String(50))
    post_date = Column(Date)
    status = Column(String(20))  # Open, Filled, Closed
    employer = relationship("Employer", back_populates="jobs")
    applications = relationship("Application", back_populates="job")

class Applicant(Base):
    __tablename__ = "applicants"
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    email = Column(String(100))
    phone = Column(String(20))
    location = Column(String(100))
    years_experience = Column(Integer)
    education_level = Column(String(50))
    skills = Column(Text)
    applications = relationship("Application", back_populates="applicant")

class Application(Base):
    __tablename__ = "applications"
    
    id = Column(Integer, primary_key=True)
    job_id = Column(Integer, ForeignKey("jobs.id"))
    applicant_id = Column(Integer, ForeignKey("applicants.id"))
    apply_date = Column(Date)
    status = Column(String(20))  # Applied, Screening, Interview, Offer, Rejected, Hired
    job = relationship("Job", back_populates="applications")
    applicant = relationship("Applicant", back_populates="applications")

# Create tables
Base.metadata.create_all(bind=engine)

# Function to generate sample data if database is empty
def generate_sample_data():
    with Session(engine) as session:
        # Check if data already exists
        if session.query(Employer).count() > 0:
            return
        
        # Create sample employers
        employers = [
            Employer(name="TechCorp", industry="Technology", location="San Francisco, CA", company_size="500-1000", founded_year=2005),
            Employer(name="FinanceHub", industry="Finance", location="New York, NY", company_size="1000-5000", founded_year=1995),
            Employer(name="HealthPlus", industry="Healthcare", location="Boston, MA", company_size="100-500", founded_year=2010),
            Employer(name="RetailGiant", industry="Retail", location="Seattle, WA", company_size="5000+", founded_year=1980),
            Employer(name="EduTech", industry="Education", location="Austin, TX", company_size="50-100", founded_year=2015)
        ]
        session.add_all(employers)
        session.commit()
        
        # Create sample jobs
        job_titles = [
            "Software Engineer", "Data Scientist", "Product Manager", "UX Designer",
            "Marketing Specialist", "HR Manager", "Financial Analyst", "Sales Representative",
            "Customer Support", "Operations Manager"
        ]
        
        departments = ["Engineering", "Data", "Product", "Design", "Marketing", "HR", "Finance", "Sales", "Support", "Operations"]
        
        experience_levels = ["Entry Level", "1-3 years", "3-5 years", "5-10 years", "10+ years"]
        
        status_options = ["Open", "Filled", "Closed"]
        
        jobs = []
        for employer in employers:
            num_jobs = random.randint(3, 8)
            for _ in range(num_jobs):
                job_idx = random.randint(0, len(job_titles) - 1)
                salary_min = random.randint(50000, 100000)
                salary_max = salary_min + random.randint(10000, 50000)
                post_date = datetime.date.today() - datetime.timedelta(days=random.randint(1, 180))
                
                jobs.append(Job(
                    employer_id=employer.id,
                    title=job_titles[job_idx],
                    department=departments[job_idx],
                    location=employer.location,
                    salary_min=salary_min,
                    salary_max=salary_max,
                    experience_required=experience_levels[random.randint(0, len(experience_levels) - 1)],
                    post_date=post_date,
                    status=status_options[random.randint(0, len(status_options) - 1)]
                ))
        
        session.add_all(jobs)
        session.commit()
        
        # Create sample applicants
        first_names = ["James", "Mary", "John", "Patricia", "Robert", "Jennifer", "Michael", "Linda", "William", "Elizabeth", 
                     "David", "Susan", "Richard", "Jessica", "Joseph", "Sarah", "Thomas", "Karen", "Charles", "Nancy"]
        last_names = ["Smith", "Johnson", "Williams", "Jones", "Brown", "Davis", "Miller", "Wilson", "Moore", "Taylor", 
                    "Anderson", "Thomas", "Jackson", "White", "Harris", "Martin", "Thompson", "Garcia", "Martinez", "Robinson"]
        
        education_levels = ["High School", "Associate's Degree", "Bachelor's Degree", "Master's Degree", "PhD"]
        
        locations = ["San Francisco, CA", "New York, NY", "Boston, MA", "Seattle, WA", "Austin, TX", 
                   "Chicago, IL", "Los Angeles, CA", "Denver, CO", "Atlanta, GA", "Dallas, TX"]
        
        skill_sets = [
            "Python, SQL, Data Analysis",
            "JavaScript, React, HTML, CSS",
            "Project Management, Agile, Scrum",
            "UI/UX Design, Figma, Adobe XD",
            "Marketing, Social Media, Content Creation",
            "HR, Recruitment, Training",
            "Financial Modeling, Excel, Accounting",
            "Sales, CRM, Negotiation",
            "Customer Service, Communication",
            "Operations, Supply Chain, Logistics"
        ]
        
        applicants = []
        for i in range(100):
            first_name = first_names[random.randint(0, len(first_names) - 1)]
            last_name = last_names[random.randint(0, len(last_names) - 1)]
            
            applicants.append(Applicant(
                name=f"{first_name} {last_name}",
                email=f"{first_name.lower()}.{last_name.lower()}@example.com",
                phone=f"555-{random.randint(100, 999)}-{random.randint(1000, 9999)}",
                location=locations[random.randint(0, len(locations) - 1)],
                years_experience=random.randint(0, 15),
                education_level=education_levels[random.randint(0, len(education_levels) - 1)],
                skills=skill_sets[random.randint(0, len(skill_sets) - 1)]
            ))
        
        session.add_all(applicants)
        session.commit()
        
        # Create sample applications
        application_status = ["Applied", "Screening", "Interview", "Offer", "Rejected", "Hired"]
        
        applications = []
        for job in jobs:
            if job.status != "Closed":
                num_applications = random.randint(5, 20)
                applicant_indices = random.sample(range(len(applicants)), min(num_applications, len(applicants)))
                
                for idx in applicant_indices:
                    apply_date = job.post_date + datetime.timedelta(days=random.randint(1, 30))
                    if apply_date > datetime.date.today():
                        apply_date = datetime.date.today()
                    
                    applications.append(Application(
                        job_id=job.id,
                        applicant_id=applicants[idx].id,
                        apply_date=apply_date,
                        status=application_status[random.randint(0, len(application_status) - 1)]
                    ))
        
        session.add_all(applications)
        session.commit()

# Generate sample data if needed
generate_sample_data()

# Sidebar for navigation
st.sidebar.title("HRTech Dashboard")
page = st.sidebar.selectbox("Select Page", ["Overview", "Employers", "Jobs", "Applicants", "Applications"])

# Fetch data for the dashboard
def load_data():
    with Session(engine) as session:
        employers_df = pd.read_sql("SELECT * FROM employers", engine)
        jobs_df = pd.read_sql("SELECT * FROM jobs", engine)
        applicants_df = pd.read_sql("SELECT * FROM applicants", engine)
        applications_df = pd.read_sql("SELECT * FROM applications", engine)
        
        # Join tables for more detailed views
        jobs_with_employer = pd.read_sql("""
            SELECT j.*, e.name as employer_name, e.industry
            FROM jobs j
            JOIN employers e ON j.employer_id = e.id
        """, engine)
        
        applications_with_details = pd.read_sql("""
            SELECT a.*, j.title as job_title, e.name as employer_name, 
                   ap.name as applicant_name, ap.years_experience, ap.education_level
            FROM applications a
            JOIN jobs j ON a.job_id = j.id
            JOIN employers e ON j.employer_id = e.id
            JOIN applicants ap ON a.applicant_id = ap.id
        """, engine)
        
        return {
            "employers": employers_df,
            "jobs": jobs_df,
            "applicants": applicants_df,
            "applications": applications_df,
            "jobs_with_employer": jobs_with_employer,
            "applications_with_details": applications_with_details
        }

data = load_data()

# Overview Page
if page == "Overview":
    st.title("HR Analytics Dashboard")
    
    # Key metrics
    col1, col2, col3, col4 = st.columns(4)
    
    with col1:
        st.metric("Employers", len(data["employers"]))
    with col2:
        st.metric("Active Jobs", len(data["jobs"][data["jobs"]["status"] == "Open"]))
    with col3:
        st.metric("Applicants", len(data["applicants"]))
    with col4:
        st.metric("Applications", len(data["applications"]))
    
    st.subheader("Job Applications by Status")
    fig = px.bar(
        data["applications_with_details"]["status"].value_counts().reset_index(),
        x="index",
        y="status",
        labels={"index": "Status", "status": "Count"},
        color="index",
        title="Application Status Distribution"
    )
    st.plotly_chart(fig, use_container_width=True)
    
    col1, col2 = st.columns(2)
    
    with col1:
        st.subheader("Jobs by Industry")
        industry_job_counts = data["jobs_with_employer"].groupby("industry").size().reset_index(name="count")
        fig = px.pie(industry_job_counts, values="count", names="industry", title="Job Distribution by Industry")
        st.plotly_chart(fig, use_container_width=True)
    
    with col2:
        st.subheader("Applications over Time")
        # Convert apply_date to datetime
        data["applications_with_details"]["apply_date"] = pd.to_datetime(data["applications_with_details"]["apply_date"])
        apps_by_date = data["applications_with_details"].groupby(data["applications_with_details"]["apply_date"].dt.strftime("%Y-%m")).size().reset_index(name="count")
        fig = px.line(apps_by_date, x="apply_date", y="count", title="Applications Trend")
        st.plotly_chart(fig, use_container_width=True)
    
    st.subheader("Top Skills in Demand")
    # Extract skills from applicants
    all_skills = []
    for skills in data["applicants"]["skills"]:
        if skills and isinstance(skills, str):
            skill_list = [s.strip() for s in skills.split(",")]
            all_skills.extend(skill_list)
    
    skill_counts = pd.Series(all_skills).value_counts().head(10)
    fig = px.bar(
        x=skill_counts.index,
        y=skill_counts.values,
        labels={"x": "Skill", "y": "Count"},
        title="Top Skills Among Applicants"
    )
    st.plotly_chart(fig, use_container_width=True)

# Employers Page
elif page == "Employers":
    st.title("Employers Dashboard")
    
    # Filter options
    industry_filter = st.selectbox("Filter by Industry", ["All"] + list(data["employers"]["industry"].unique()))
    
    filtered_employers = data["employers"]
    if industry_filter != "All":
        filtered_employers = filtered_employers[filtered_employers["industry"] == industry_filter]
    
    # Display employers data
    st.subheader("Employers List")
    st.dataframe(filtered_employers)
    
    # Employer statistics
    st.subheader("Employer Statistics")
    
    col1, col2 = st.columns(2)
    
    with col1:
        # Company size distribution
        company_size_counts = data["employers"]["company_size"].value_counts().reset_index()
        fig = px.pie(company_size_counts, values="company_size", names="index", title="Company Size Distribution")
        st.plotly_chart(fig, use_container_width=True)
    
    with col2:
        # Founded year distribution
        fig = px.histogram(
            data["employers"],
            x="founded_year",
            nbins=20,
            title="Company Age Distribution"
        )
        st.plotly_chart(fig, use_container_width=True)
    
    # Jobs per employer
    jobs_per_employer = data["jobs"].groupby("employer_id").size().reset_index(name="job_count")
    jobs_per_employer = jobs_per_employer.merge(
        data["employers"][["id", "name"]],
        left_on="employer_id",
        right_on="id"
    )
    
    fig = px.bar(
        jobs_per_employer.sort_values("job_count", ascending=False),
        x="name",
        y="job_count",
        title="Jobs Posted per Employer",
        labels={"name": "Employer", "job_count": "Number of Jobs"}
    )
    st.plotly_chart(fig, use_container_width=True)

# Jobs Page
elif page == "Jobs":
    st.title("Jobs Dashboard")
    
    # Filter options
    col1, col2, col3 = st.columns(3)
    
    with col1:
        employer_filter = st.selectbox("Filter by Employer", ["All"] + list(data["employers"]["name"]))
    with col2:
        department_filter = st.selectbox("Filter by Department", ["All"] + list(data["jobs"]["department"].unique()))
    with col3:
        status_filter = st.selectbox("Filter by Status", ["All"] + list(data["jobs"]["status"].unique()))
    
    # Apply filters
    filtered_jobs = data["jobs_with_employer"]
    if employer_filter != "All":
        filtered_jobs = filtered_jobs[filtered_jobs["employer_name"] == employer_filter]
    if department_filter != "All":
        filtered_jobs = filtered_jobs[filtered_jobs["department"] == department_filter]
    if status_filter != "All":
        filtered_jobs = filtered_jobs[filtered_jobs["status"] == status_filter]
    
    # Display jobs data
    st.subheader("Jobs List")
    st.dataframe(filtered_jobs)
    
    # Job statistics
    st.subheader("Job Statistics")
    
    col1, col2 = st.columns(2)
    
    with col1:
        # Job status distribution
        status_counts = data["jobs"]["status"].value_counts().reset_index()
        fig = px.pie(status_counts, values="status", names="index", title="Job Status Distribution")
        st.plotly_chart(fig, use_container_width=True)
    
    with col2:
        # Experience required distribution
        exp_counts = data["jobs"]["experience_required"].value_counts().reset_index()
        fig = px.bar(
            exp_counts,
            x="index",
            y="experience_required",
            title="Experience Requirements",
            labels={"index": "Experience Level", "experience_required": "Number of Jobs"}
        )
        st.plotly_chart(fig, use_container_width=True)
    
    # Salary ranges
    salary_data = data["jobs"][["title", "salary_min", "salary_max"]].dropna()
    salary_data["avg_salary"] = (salary_data["salary_min"] + salary_data["salary_max"]) / 2
    
    # Group by job title and calculate average salary
    avg_salary_by_title = salary_data.groupby("title")["avg_salary"].mean().reset_index()
    avg_salary_by_title = avg_salary_by_title.sort_values("avg_salary", ascending=False)
    
    fig = px.bar(
        avg_salary_by_title,
        x="title",
        y="avg_salary",
        title="Average Salary by Job Title",
        labels={"title": "Job Title", "avg_salary": "Average Salary ($)"}
    )
    st.plotly_chart(fig, use_container_width=True)

# Applicants Page
elif page == "Applicants":
    st.title("Applicants Dashboard")
    
    # Filter options
    col1, col2 = st.columns(2)
    
    with col1:
        education_filter = st.selectbox("Filter by Education", ["All"] + list(data["applicants"]["education_level"].unique()))
    with col2:
        experience_range = st.slider("Years of Experience", 0, 15, (0, 15))
    
    # Apply filters
    filtered_applicants = data["applicants"]
    if education_filter != "All":
        filtered_applicants = filtered_applicants[filtered_applicants["education_level"] == education_filter]
    
    filtered_applicants = filtered_applicants[
        (filtered_applicants["years_experience"] >= experience_range[0]) & 
        (filtered_applicants["years_experience"] <= experience_range[1])
    ]
    
    # Display applicants data
    st.subheader("Applicants List")
    st.dataframe(filtered_applicants)
    
    # Applicant statistics
    st.subheader("Applicant Statistics")
    
    col1, col2 = st.columns(2)
    
    with col1:
        # Education level distribution
        edu_counts = data["applicants"]["education_level"].value_counts().reset_index()
        fig = px.pie(edu_counts, values="education_level", names="index", title="Education Level Distribution")
        st.plotly_chart(fig, use_container_width=True)
    
    with col2:
        # Years of experience distribution
        fig = px.histogram(
            data["applicants"],
            x="years_experience",
            nbins=15,
            title="Years of Experience Distribution"
        )
        st.plotly_chart(fig, use_container_width=True)
    
    # Location distribution
    location_counts = data["applicants"]["location"].value_counts().head(10).reset_index()
    fig = px.bar(
        location_counts,
        x="index",
        y="location",
        title="Top 10 Applicant Locations",
        labels={"index": "Location", "location": "Number of Applicants"}
    )
    st.plotly_chart(fig, use_container_width=True)

# Applications Page
elif page == "Applications":
    st.title("Applications Dashboard")
    
    # Filter options
    col1, col2, col3 = st.columns(3)
    
    with col1:
        employer_filter = st.selectbox("Filter by Employer", ["All"] + list(data["employers"]["name"]))
    with col2:
        job_filter = st.selectbox("Filter by Job Title", ["All"] + list(data["jobs"]["title"].unique()))
    with col3:
        status_filter = st.selectbox("Filter by Application Status", ["All"] + list(data["applications"]["status"].unique()))
    
    # Apply filters
    filtered_applications = data["applications_with_details"]
    if employer_filter != "All":
        filtered_applications = filtered_applications[filtered_applications["employer_name"] == employer_filter]
    if job_filter != "All":
        filtered_applications = filtered_applications[filtered_applications["job_title"] == job_filter]
    if status_filter != "All":
        filtered_applications = filtered_applications[filtered_applications["status"] == status_filter]
    
    # Display applications data
    st.subheader("Applications List")
    st.dataframe(filtered_applications)
    
    # Application statistics
    st.subheader("Application Statistics")
    
    col1, col2 = st.columns(2)
    
    with col1:
        # Application status distribution
        status_counts = data["applications"]["status"].value_counts().reset_index()
        fig = px.pie(status_counts, values="status", names="index", title="Application Status Distribution")
        st.plotly_chart(fig, use_container_width=True)
    
    with col2:
        # Applications over time
        data["applications"]["apply_date"] = pd.to_datetime(data["applications"]["apply_date"])
        apps_by_date = data["applications"].groupby(data["applications"]["apply_date"].dt.strftime("%Y-%m-%d")).size().reset_index(name="count")
        apps_by_date["apply_date"] = pd.to_datetime(apps_by_date["apply_date"])
        apps_by_date = apps_by_date.sort_values("apply_date")
        
        fig = px.line(
            apps_by_date,
            x="apply_date",
            y="count",
            title="Applications Over Time",
            labels={"apply_date": "Date", "count": "Number of Applications"}
        )
        st.plotly_chart(fig, use_container_width=True)
    
    # Success rate by education level
    app_by_edu = data["applications_with_details"].copy()
    app_by_edu["is_successful"] = app_by_edu["status"].isin(["Offer", "Hired"])
    
    success_by_edu = app_by_edu.groupby("education_level")["is_successful"].agg(["mean", "count"]).reset_index()
    success_by_edu["success_rate"] = success_by_edu["mean"] * 100
    
    fig = px.bar(
        success_by_edu,
        x="education_level",
        y="success_rate",
        title="Success Rate by Education Level",
        labels={"education_level": "Education Level", "success_rate": "Success Rate (%)"},
        text=success_by_edu["count"].astype(str) + " applications"
    )
    st.plotly_chart(fig, use_container_width=True)
    
    # Success rate by experience
    app_by_exp = data["applications_with_details"].copy()
    app_by_exp["experience_bracket"] = pd.cut(
        app_by_exp["years_experience"],
        bins=[0, 2, 5, 10, 15, 20],
        labels=["0-2 years", "3-5 years", "6-10 years", "11-15 years", "16+ years"]
    )
    
    success_by_exp = app_by_exp.groupby("experience_bracket")["is_successful"].agg(["mean", "count"]).reset_index()
    success_by_exp["success_rate"] = success_by_exp["mean"] * 100
    
    fig = px.bar(
        success_by_exp,
        x="experience_bracket",
        y="success_rate",
        title="Success Rate by Experience Level",
        labels={"experience_bracket": "Years of Experience", "success_rate": "Success Rate (%)"},
        text=success_by_exp["count"].astype(str) + " applications"
    )
    st.plotly_chart(fig, use_container_width=True)

# Footer
st.markdown("---")
st.markdown("© 2023 HRTech Dashboard. All Rights Reserved.")