src/database.py

"""
Database configuration and models for FitScore Feedback Agent - Hugging Face Deployment
"""

from sqlalchemy import create_engine, Column, Integer, String, Text, DateTime, Float, Boolean, JSON, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
import os
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

# Database URL - Use PostgreSQL for production
DATABASE_URL = os.getenv("DATABASE_URL", "")

# Create SQLAlchemy engine
if DATABASE_URL.startswith("sqlite"):
    engine = create_engine(
        DATABASE_URL,
        connect_args={"check_same_thread": False}  # Required for SQLite
    )
else:
    # PostgreSQL configuration
    engine = create_engine(
        DATABASE_URL,
        pool_pre_ping=True,
        pool_size=5,
        max_overflow=10,
        pool_recycle=300,
        echo=False  # Set to True for debugging
    )

# Create session factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Create base class
Base = declarative_base()

# Database models
class GlobalPrompt(Base):
    """Global prompt for FitScore evaluation"""
    __tablename__ = "global_prompts"
    
    id = Column(Integer, primary_key=True, index=True)
    version = Column(String, nullable=False)
    prompt_content = Column(Text, nullable=False)
    category_weights = Column(JSON, nullable=False)
    scoring_rules = Column(JSON, nullable=False)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class LocalPrompt(Base):
    """Local prompt for specific jobs"""
    __tablename__ = "local_prompts"
    
    id = Column(Integer, primary_key=True, index=True)
    job_id = Column(String, nullable=False, index=True)
    company_id = Column(String, nullable=False)
    version = Column(String, nullable=False)
    prompt_content = Column(Text, nullable=False)
    category_weights = Column(JSON, nullable=False)
    scoring_rules = Column(JSON, nullable=False)
    location_enforced = Column(Boolean, default=False)
    location_radius = Column(Float, nullable=True)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class CandidateEvaluation(Base):
    """Candidate evaluation results"""
    __tablename__ = "candidate_evaluations"
    
    id = Column(Integer, primary_key=True, index=True)
    evaluation_id = Column(String, unique=True, index=True, nullable=False)
    candidate_id = Column(String, nullable=False, index=True)
    job_id = Column(String, nullable=False, index=True)
    # recruiter_id removed to match existing PostgreSQL schema
    fitscore = Column(Float, nullable=False)
    verdict = Column(String, nullable=False)
    confidence = Column(Float, nullable=False)
    category_scores = Column(JSON, nullable=False)
    justification = Column(Text, nullable=False)
    model_version = Column(String, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

class Feedback(Base):
    """Feedback entries"""
    __tablename__ = "feedback"
    
    id = Column(Integer, primary_key=True, index=True)
    feedback_id = Column(String, unique=True, index=True, nullable=False)
    job_id = Column(String, nullable=False, index=True)
    company_id = Column(String, nullable=False)
    analysis_id = Column(String, nullable=False)
    feedback_type = Column(String, nullable=False)  # hired, accepted, rejected, interviewed
    feedback_text = Column(Text, nullable=False)
    feedback_category = Column(String, nullable=False)  # skills, experience, location, education, other
    confidence_score = Column(Float, nullable=False)
    email = Column(String, nullable=True)
    linkedin_url = Column(String, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)

class FeedbackPattern(Base):
    """Feedback patterns for learning"""
    __tablename__ = "feedback_patterns"
    
    id = Column(Integer, primary_key=True, index=True)
    pattern_text = Column(String, nullable=False)
    category = Column(String, nullable=False)
    frequency = Column(Integer, default=1)
    affected_jobs = Column(JSON, nullable=False)
    affected_companies = Column(JSON, nullable=False)
    is_global = Column(Boolean, default=False)
    first_seen = Column(DateTime, default=datetime.utcnow)
    last_seen = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

# Additional models for reinforcement learning and advanced features
class CandidateSubmission(Base):
    """Candidate submissions for reinforcement learning"""
    __tablename__ = "candidate_submissions"
    
    id = Column(Integer, primary_key=True, index=True)
    submission_id = Column(String, unique=True, index=True, nullable=False)
    candidate_id = Column(String, nullable=False, index=True)
    job_id = Column(String, nullable=False, index=True)
    recruiter_id = Column(String, nullable=False)
    fit_score = Column(Float, nullable=False)
    fitscore = Column(Float, nullable=True)
    fitscore_confidence = Column(Float, nullable=True)
    fitscore_model_version = Column(String, nullable=True)
    uncertainty_score = Column(Float, nullable=True)
    similarity_score = Column(Float, nullable=True)
    submission_notes = Column(Text, nullable=True)
    outcome = Column(String, nullable=True)  # hired, rejected, interviewed, pending
    outcome_notes = Column(Text, nullable=True)
    outcome_date = Column(DateTime, nullable=True)
    reward_signal = Column(Float, nullable=True)
    feedback_category = Column(String, nullable=True)
    learning_applied = Column(Boolean, default=False)
    status = Column(String, default="pending")
    created_at = Column(DateTime, default=datetime.utcnow)

class RewardHistory(Base):
    """Reward history for reinforcement learning"""
    __tablename__ = "reward_history"
    
    id = Column(Integer, primary_key=True, index=True)
    submission_id = Column(String, nullable=False, index=True)
    candidate_id = Column(String, nullable=False)
    job_id = Column(String, nullable=False)
    outcome = Column(String, nullable=False)
    reward_value = Column(Float, nullable=False)
    weight_version = Column(String, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

class WeightAdjustment(Base):
    """Weight adjustment history"""
    __tablename__ = "weight_adjustments"
    
    id = Column(Integer, primary_key=True, index=True)
    previous_version = Column(String, nullable=False)
    new_version = Column(String, nullable=False)
    total_rewards = Column(Float, nullable=False)
    reward_count = Column(Integer, nullable=False)
    average_reward = Column(Float, nullable=False)
    learning_rate = Column(Float, nullable=False)
    applied_at = Column(DateTime, default=datetime.utcnow)

class FitScoreWeights(Base):
    """FitScore model weights"""
    __tablename__ = "fit_score_weights"
    
    id = Column(Integer, primary_key=True, index=True)
    version = Column(String, nullable=False)
    weights = Column(JSON, nullable=False)
    learning_rate = Column(Float, nullable=True)
    total_rewards = Column(Float, nullable=True)
    reward_count = Column(Integer, nullable=True)
    average_reward = Column(Float, nullable=True)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)

class LearningEvent(Base):
    """Learning events for advanced learning systems"""
    __tablename__ = "learning_events"
    
    id = Column(Integer, primary_key=True, index=True)
    event_id = Column(String, unique=True, index=True, nullable=False)
    event_type = Column(String, nullable=False)  # rlhf, contrastive, few_shot, curriculum, online, bayesian, active
    candidate_id = Column(String, nullable=False, index=True)
    job_id = Column(String, nullable=False, index=True)
    submission_id = Column(String, nullable=True)
    input_data = Column(JSON, nullable=False)
    outcome_data = Column(JSON, nullable=False)
    confidence_score = Column(Float, nullable=False)
    model_version = Column(String, nullable=False)
    learning_signal = Column(Float, nullable=False)
    processed = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)

class FitScoreModel(Base):
    """FitScore model versions and performance"""
    __tablename__ = "fit_score_models"
    
    id = Column(Integer, primary_key=True, index=True)
    version = Column(String, nullable=False)
    accuracy = Column(Float, nullable=True)
    precision = Column(Float, nullable=True)
    recall = Column(Float, nullable=True)
    f1_score = Column(Float, nullable=True)
    training_data_count = Column(Integer, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)

# Additional models for advanced learning system
class ContrastivePair(Base):
    """Contrastive learning pairs"""
    __tablename__ = "contrastive_pairs"
    
    id = Column(Integer, primary_key=True, index=True)
    pair_id = Column(String, unique=True, index=True, nullable=False)
    positive_candidate_id = Column(String, nullable=False)
    negative_candidate_id = Column(String, nullable=False)
    job_id = Column(String, nullable=False)
    similarity_score = Column(Float, nullable=False)
    learning_signal = Column(Float, nullable=False)
    processed = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)

class FewShotExample(Base):
    """Few-shot learning examples"""
    __tablename__ = "few_shot_examples"
    
    id = Column(Integer, primary_key=True, index=True)
    example_id = Column(String, unique=True, index=True, nullable=False)
    candidate_id = Column(String, nullable=False)
    job_id = Column(String, nullable=False)
    example_type = Column(String, nullable=False)  # positive, negative, neutral
    example_text = Column(Text, nullable=False)
    confidence_score = Column(Float, nullable=False)
    used_in_training = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)

class CurriculumStage(Base):
    """Curriculum learning stages"""
    __tablename__ = "curriculum_stages"
    
    id = Column(Integer, primary_key=True, index=True)
    stage_id = Column(String, unique=True, index=True, nullable=False)
    stage_name = Column(String, nullable=False)
    difficulty_level = Column(Integer, nullable=False)
    required_skills = Column(JSON, nullable=False)
    learning_objectives = Column(JSON, nullable=False)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)

class BayesianUpdate(Base):
    """Bayesian learning updates"""
    __tablename__ = "bayesian_updates"
    
    id = Column(Integer, primary_key=True, index=True)
    update_id = Column(String, unique=True, index=True, nullable=False)
    candidate_id = Column(String, nullable=False)
    job_id = Column(String, nullable=False)
    prior_belief = Column(Float, nullable=False)
    likelihood = Column(Float, nullable=False)
    posterior_belief = Column(Float, nullable=False)
    uncertainty_reduction = Column(Float, nullable=False)
    model_version = Column(String, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

class ActiveLearningRequest(Base):
    """Active learning requests"""
    __tablename__ = "active_learning_requests"
    
    id = Column(Integer, primary_key=True, index=True)
    request_id = Column(String, unique=True, index=True, nullable=False)
    candidate_id = Column(String, nullable=False)
    job_id = Column(String, nullable=False)
    uncertainty_score = Column(Float, nullable=False)
    request_type = Column(String, nullable=False)  # query, sample, explore
    priority_score = Column(Float, nullable=False)
    processed = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)

# Database dependency
def get_db():
    """Get database session"""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

def create_tables():
    """Create all database tables"""
    Base.metadata.create_all(bind=engine)