Code Editor for app.py

import streamlit as st
from sqlalchemy import create_engine, Column, Integer, String, Float, Text, DateTime, func
from sqlalchemy.orm import Session, DeclarativeBase
import datetime
import pandas as pd

# Database setup
class Base(DeclarativeBase):
    pass

class Job(Base):
    __tablename__ = "jobs"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)
    company = Column(String(100), nullable=False)
    location = Column(String(100))
    salary = Column(Float)
    description = Column(Text)
    requirements = Column(Text)
    posted_date = Column(DateTime, default=datetime.datetime.utcnow)
    url = Column(String(500))
    
engine = create_engine("sqlite:///jobs.sqlite")
Base.metadata.create_all(bind=engine)

# Add sample data function
def add_sample_data():
    with Session(engine) as session:
        # Check if data already exists
        if session.query(Job).count() == 0:
            sample_jobs = [
                Job(
                    title="Frontend Developer",
                    company="TechCorp",
                    location="New York, NY (Remote)",
                    salary=95000.00,
                    description="Develop and maintain frontend applications using React and modern JavaScript.",
                    requirements="3+ years of experience with React, JavaScript, and CSS. Experience with TypeScript is a plus.",
                    posted_date=datetime.datetime.now() - datetime.timedelta(days=2),
                    url="https://example.com/jobs/frontend-dev"
                ),
                Job(
                    title="Data Scientist",
                    company="DataInsights",
                    location="San Francisco, CA",
                    salary=120000.00,
                    description="Apply machine learning techniques to solve complex business problems.",
                    requirements="Masters or PhD in a quantitative field. Experience with Python, sklearn, and PyTorch.",
                    posted_date=datetime.datetime.now() - datetime.timedelta(days=5),
                    url="https://example.com/jobs/data-scientist"
                ),
                Job(
                    title="DevOps Engineer",
                    company="CloudSolutions",
                    location="Remote",
                    salary=110000.00,
                    description="Build and maintain CI/CD pipelines and cloud infrastructure.",
                    requirements="Experience with AWS, Kubernetes, Docker, and CI/CD tools.",
                    posted_date=datetime.datetime.now() - datetime.timedelta(days=1),
                    url="https://example.com/jobs/devops-engineer"
                ),
                Job(
                    title="Backend Developer",
                    company="SoftServe",
                    location="Chicago, IL",
                    salary=105000.00,
                    description="Design and develop scalable backend services using Java and Spring Boot.",
                    requirements="5+ years experience with Java, Spring Boot, and SQL databases.",
                    posted_date=datetime.datetime.now() - datetime.timedelta(days=7),
                    url="https://example.com/jobs/backend-dev"
                ),
                Job(
                    title="UX/UI Designer",
                    company="DesignWorks",
                    location="Austin, TX (Hybrid)",
                    salary=90000.00,
                    description="Create user-centered designs for web and mobile applications.",
                    requirements="Portfolio showcasing UX/UI design work. Experience with Figma and Adobe Creative Suite.",
                    posted_date=datetime.datetime.now() - datetime.timedelta(days=3),
                    url="https://example.com/jobs/ux-designer"
                ),
                Job(
                    title="Full Stack Developer",
                    company="WebSolutions",
                    location="Remote",
                    salary=115000.00,
                    description="Develop and maintain web applications using the MERN stack.",
                    requirements="Experience with MongoDB, Express, React, and Node.js.",
                    posted_date=datetime.datetime.now() - datetime.timedelta(days=4),
                    url="https://example.com/jobs/fullstack-dev"
                ),
                Job(
                    title="Machine Learning Engineer",
                    company="AI Innovations",
                    location="Seattle, WA",
                    salary=130000.00,
                    description="Develop and deploy machine learning models to production.",
                    requirements="Strong background in ML algorithms and experience with TensorFlow or PyTorch.",
                    posted_date=datetime.datetime.now() - datetime.timedelta(days=6),
                    url="https://example.com/jobs/ml-engineer"
                ),
                Job(
                    title="Product Manager",
                    company="ProductLabs",
                    location="Boston, MA (Hybrid)",
                    salary=110000.00,
                    description="Lead product development from conception to launch.",
                    requirements="3+ years experience in product management for tech products.",
                    posted_date=datetime.datetime.now() - datetime.timedelta(days=8),
                    url="https://example.com/jobs/product-manager"
                ),
            ]
            session.add_all(sample_jobs)
            session.commit()

# Call the function to add sample data
add_sample_data()

# App title and description
st.title("Job Search App")
st.write("Find your next dream job in tech!")

# Sidebar filters
st.sidebar.header("Filters")

# Get all available companies and locations for filters
with Session(engine) as session:
    companies = [company[0] for company in session.query(Job.company).distinct()]
    locations = [location[0] for location in session.query(Job.location).distinct()]

# Filter by company
selected_companies = st.sidebar.multiselect("Company", companies)

# Filter by location
selected_locations = st.sidebar.multiselect("Location", locations)

# Filter by salary range
min_salary, max_salary = st.sidebar.slider(
    "Salary Range (USD)",
    min_value=50000,
    max_value=150000,
    value=(50000, 150000),
    step=5000
)

# Search by keywords
search_term = st.sidebar.text_input("Search in title or description")

# Add a new job listing
st.sidebar.header("Add New Job Listing")
if st.sidebar.button("Add New Job"):
    st.session_state.show_add_form = True
    
# Clear filters
if st.sidebar.button("Clear Filters"):
    st.session_state.search_term = ""
    st.session_state.selected_companies = []
    st.session_state.selected_locations = []
    st.session_state.min_salary = 50000
    st.session_state.max_salary = 150000
    st.rerun()

# Main content
tabs = st.tabs(["Job Listings", "Add Job", "Statistics"])

with tabs[0]:
    # Construct query with filters
    with Session(engine) as session:
        query = session.query(Job)
        
        if selected_companies:
            query = query.filter(Job.company.in_(selected_companies))
        
        if selected_locations:
            query = query.filter(Job.location.in_(selected_locations))
        
        query = query.filter(Job.salary >= min_salary, Job.salary <= max_salary)
        
        if search_term:
            search_pattern = f"%{search_term}%"
            query = query.filter(
                (Job.title.like(search_pattern)) | 
                (Job.description.like(search_pattern))
            )
            
        # Sort by most recently posted
        query = query.order_by(Job.posted_date.desc())
        
        # Get results
        jobs = query.all()
        
        if not jobs:
            st.info("No jobs match your search criteria. Try adjusting your filters.")
        else:
            st.write(f"Found {len(jobs)} jobs")
            
            # Display each job as an expandable card
            for job in jobs:
                with st.expander(f"{job.title} at {job.company}"):
                    col1, col2 = st.columns([3, 1])
                    
                    with col1:
                        st.subheader(job.title)
                        st.write(f"**Company:** {job.company}")
                        st.write(f"**Location:** {job.location}")
                        
                        if job.salary:
                            st.write(f"**Salary:** ${job.salary:,.2f}")
                        
                        st.write("**Description:**")
                        st.write(job.description)
                        
                        st.write("**Requirements:**")
                        st.write(job.requirements)
                        
                    with col2:
                        days_ago = (datetime.datetime.now() - job.posted_date).days
                        st.write(f"**Posted:** {days_ago} days ago")
                        
                        if job.url:
                            st.link_button("Apply Now", job.url)

with tabs[1]:
    st.header("Add a New Job Listing")
    
    with st.form("job_form"):
        title = st.text_input("Job Title")
        company = st.text_input("Company")
        location = st.text_input("Location")
        salary = st.number_input("Salary (USD)", min_value=0.0, step=5000.0)
        description = st.text_area("Job Description")
        requirements = st.text_area("Job Requirements")
        url = st.text_input("Application URL")
        
        submitted = st.form_submit_button("Submit Job Listing")
        
        if submitted:
            if title and company:  # Basic validation
                with Session(engine) as session:
                    new_job = Job(
                        title=title,
                        company=company,
                        location=location,
                        salary=salary if salary > 0 else None,
                        description=description,
                        requirements=requirements,
                        posted_date=datetime.datetime.now(),
                        url=url
                    )
                    session.add(new_job)
                    session.commit()
                    
                st.success("Job listing added successfully!")
            else:
                st.error("Job title and company are required fields.")

with tabs[2]:
    st.header("Job Market Statistics")
    
    with Session(engine) as session:
        # Average salary by company
        company_salary_data = session.query(
            Job.company, 
            func.avg(Job.salary).label('avg_salary')
        ).group_by(Job.company).all()
        
        if company_salary_data:
            company_df = pd.DataFrame(company_salary_data, columns=['Company', 'Average Salary'])
            company_df['Average Salary'] = company_df['Average Salary'].round(2)
            
            st.subheader("Average Salary by Company")
            st.bar_chart(company_df.set_index('Company'))
        
        # Job count by location
        location_data = session.query(
            Job.location, 
            func.count(Job.id).label('job_count')
        ).group_by(Job.location).all()
        
        if location_data:
            location_df = pd.DataFrame(location_data, columns=['Location', 'Job Count'])
            
            st.subheader("Jobs by Location")
            st.bar_chart(location_df.set_index('Location'))
        
        # Most recent job postings
        recent_jobs = session.query(
            Job.title, 
            Job.company, 
            Job.posted_date
        ).order_by(Job.posted_date.desc()).limit(5).all()
        
        if recent_jobs:
            recent_df = pd.DataFrame(recent_jobs, columns=['Title', 'Company', 'Posted Date'])
            recent_df['Posted Date'] = recent_df['Posted Date'].dt.strftime('%Y-%m-%d')
            
            st.subheader("Most Recent Job Postings")
            st.table(recent_df)