database.py

from sqlalchemy import create_engine, Column, Integer, String, DateTime, Boolean
from sqlalchemy.orm import declarative_base
from dotenv import load_dotenv
import os

#   table definition SQLAlchemy uses to generate SQL
#   This pattern is called an ORM (Object Relational Mapper)
#    — it maps Python objects to database rows so you never have to write raw SQL.

#   loads .env file into environment
load_dotenv()

Base = declarative_base()

class Job(Base):
    __tablename__ = "jobs"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    company = Column(String)
    category = Column(String)
    location = Column(String)
    salary = Column(String)
    tags = Column(String)
    url = Column(String)
    is_active = Column(Boolean, default=True)
    last_checked = Column(DateTime, nullable=True)

DATABASE_URL = os.getenv("DATABASE_URL")

#   Create Engine and Table
engine = create_engine(DATABASE_URL)

#   Reads model classes and writes the table structure
#   "create_all" is safe to call on every run - skips existing tables
Base.metadata.create_all(engine)
print("Database and table created successfully")