File size: 4,680 Bytes
61fd574
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
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()