import sqlite3

# Connect to database (will create if not exists)
conn = sqlite3.connect("resume_engine.db")
cursor = conn.cursor()

# Enable foreign key constraints
cursor.execute("PRAGMA foreign_keys = ON;")


# -------------------------------
# Table 1: resumes
# -------------------------------
cursor.execute("""
CREATE TABLE IF NOT EXISTS resumes (
    resume_id TEXT PRIMARY KEY,
    file_name TEXT,
    candidate_name TEXT,
    email TEXT,
    phone TEXT,
    resume_text TEXT,
    skills TEXT,
    soft_skills TEXT,
    experience_years REAL,
    last_company TEXT,
    job_titles TEXT,
    education TEXT,
    certifications TEXT,
    projects TEXT,
    hobbies TEXT,
    avg_tenure_months REAL,
    recency_flag TEXT,
    upload_batch TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")


# -------------------------------
# Table 2: job_descriptions
# -------------------------------
cursor.execute("""
CREATE TABLE IF NOT EXISTS job_descriptions (
    jd_id TEXT PRIMARY KEY,
    jd_title TEXT,
    jd_text TEXT,
    required_skills TEXT,
    preferred_skills TEXT,
    required_experience REAL,
    education_requirement TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")


# -------------------------------
# Table 3: match_results
# -------------------------------
cursor.execute("""
CREATE TABLE IF NOT EXISTS match_results (
    match_id TEXT PRIMARY KEY,
    resume_id TEXT,
    jd_id TEXT,
    skill_match_score REAL,
    experience_score REAL,
    semantic_score REAL,
    stability_score REAL,
    recency_score REAL,
    education_score REAL,
    final_score REAL,
    confidence_tier TEXT,
    matched_skills TEXT,
    missing_skills TEXT,
    standout_skills TEXT,
    summary TEXT,
    interview_questions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (resume_id) REFERENCES resumes(resume_id),
    FOREIGN KEY (jd_id) REFERENCES job_descriptions(jd_id)
);
""")


# Commit and close
conn.commit()
conn.close()

print("Database and tables created successfully!")