import sqlite3 import json import os import logging logger = logging.getLogger(__name__) DB_PATH = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'data', 'offer.db') def get_conn(): conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row return conn def init_db(): conn = get_conn() conn.executescript(""" CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT DEFAULT '', school TEXT DEFAULT '', major TEXT DEFAULT '', grade TEXT DEFAULT '', skills TEXT DEFAULT '[]', projects TEXT DEFAULT '[]', summary TEXT DEFAULT '', experience TEXT DEFAULT '', education TEXT DEFAULT '', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS jobs ( id INTEGER PRIMARY KEY AUTOINCREMENT, job_id INTEGER UNIQUE, name TEXT NOT NULL, company TEXT DEFAULT '', city TEXT DEFAULT '', salary TEXT DEFAULT '', tags TEXT DEFAULT '[]', description TEXT DEFAULT '', requirements TEXT DEFAULT '', expectation_signals TEXT DEFAULT '[]', embedding TEXT DEFAULT '', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS diagnoses ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER REFERENCES users(id), job_id INTEGER REFERENCES jobs(job_id), match_report TEXT DEFAULT '{}', advice TEXT DEFAULT '{}', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); """) count = conn.execute("SELECT COUNT(*) FROM jobs").fetchone()[0] if count == 0: jobs_path = os.path.join(os.path.dirname(DB_PATH), 'jobs.json') jobs = json.load(open(jobs_path, encoding='utf-8')) for j in jobs: conn.execute( "INSERT INTO jobs (job_id, name, company, city, salary, tags, description, requirements, expectation_signals) VALUES (?,?,?,?,?,?,?,?,?)", (int(j['id'].split('_')[-1]) if isinstance(j['id'], str) else j['id'], j['name'], j.get('company',''), j.get('city',''), j.get('salary',''), json.dumps(j.get('tags',[]), ensure_ascii=False), j.get('description',''), j.get('requirements',''), json.dumps(j.get('expectation_signals',[]), ensure_ascii=False)) ) conn.commit() _compute_job_embeddings(conn) def _compute_job_embeddings(conn): try: from app.services.embedding_service import compute_embedding rows = conn.execute("SELECT job_id, name, description, requirements, tags FROM jobs").fetchall() for r in rows: text = " ".join([ r["name"] or "", r["description"] or "", r["requirements"] or "", " ".join(json.loads(r["tags"] or "[]")), ]) vec = compute_embedding(text) conn.execute( "UPDATE jobs SET embedding=? WHERE job_id=?", (json.dumps(vec, ensure_ascii=False), r["job_id"]) ) conn.commit() logger.info("Computed embeddings for %d jobs", len(rows)) except Exception as e: logger.warning("Failed to compute job embeddings: %s", e) count = conn.execute("SELECT COUNT(*) FROM users").fetchone()[0] if count == 0: conn.execute( "INSERT INTO users (name, school, major, grade, skills, projects, summary) VALUES (?,?,?,?,?,?,?)", ('张三', '华南理工大学', '计算机科学与技术', '研二', json.dumps(['Python','Java','SQL','PyTorch','Flask'], ensure_ascii=False), json.dumps(['基于协同过滤的推荐系统','在线商城后端开发'], ensure_ascii=False), '技术栈集中在Python/Java后端开发,有推荐系统和Web开发项目经验') ) conn.commit() conn.close() def migrate_db(): conn = get_conn() try: conn.execute("SELECT embedding FROM jobs LIMIT 1") except: conn.execute("ALTER TABLE jobs ADD COLUMN embedding TEXT DEFAULT ''") conn.commit() empty = conn.execute("SELECT COUNT(*) FROM jobs WHERE embedding IS NULL OR embedding = ''").fetchone()[0] if empty > 0: try: _compute_job_embeddings(conn) except Exception as e: logger.warning("Embedding computation failed: %s", e) conn.close()