242 lines
8.7 KiB
JavaScript
242 lines
8.7 KiB
JavaScript
const Database = require('better-sqlite3');
|
|
const path = require('path');
|
|
const fs = require('fs');
|
|
|
|
// Ensure data directory exists
|
|
const dbDir = path.join(__dirname, 'data');
|
|
if (!fs.existsSync(dbDir)) {
|
|
fs.mkdirSync(dbDir, { recursive: true });
|
|
}
|
|
|
|
const db = new Database(path.join(dbDir, 'ebay_items.db'));
|
|
|
|
// Create tables
|
|
function initDb() {
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS profiles (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
price_ratio REAL DEFAULT 0.85,
|
|
common_keywords TEXT,
|
|
last_scan_time DATETIME,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS search_keywords (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
profile_id INTEGER NOT NULL,
|
|
part_number TEXT,
|
|
keywords TEXT NOT NULL, -- JSON array
|
|
target_price REAL NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS items (
|
|
id TEXT PRIMARY KEY,
|
|
profile_id INTEGER,
|
|
keyword_id INTEGER,
|
|
searchType TEXT,
|
|
partNumber TEXT,
|
|
manufacturer TEXT,
|
|
specs TEXT,
|
|
title TEXT,
|
|
url TEXT,
|
|
price REAL,
|
|
shipping REAL,
|
|
shippingLabel TEXT,
|
|
total REAL,
|
|
qty INTEGER,
|
|
avgPrice REAL,
|
|
available INTEGER,
|
|
targetPrice REAL,
|
|
threshold REAL,
|
|
passFail TEXT,
|
|
profit REAL,
|
|
images TEXT, -- JSON
|
|
detail_response TEXT, -- JSON
|
|
seller_username TEXT,
|
|
seller_feedback_score INTEGER,
|
|
seller_feedback_percent REAL,
|
|
review_status TEXT DEFAULT 'waiting', -- 'waiting', 'done', 'skip'
|
|
ai_suggestion TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
|
|
);
|
|
`);
|
|
|
|
// Migration: Add columns if they don't exist
|
|
try { db.exec("ALTER TABLE profiles ADD COLUMN last_scan_time DATETIME;"); } catch(e){}
|
|
try { db.exec("ALTER TABLE profiles ADD COLUMN common_keywords TEXT;"); } catch(e){}
|
|
// Migration: rename ignore_keywords to common_keywords if it exists
|
|
try {
|
|
const tableInfo = db.prepare("PRAGMA table_info(profiles)").all();
|
|
const hasIgnore = tableInfo.some(col => col.name === 'ignore_keywords');
|
|
const hasCommon = tableInfo.some(col => col.name === 'common_keywords');
|
|
if (hasIgnore && !hasCommon) {
|
|
db.exec("ALTER TABLE profiles RENAME COLUMN ignore_keywords TO common_keywords");
|
|
}
|
|
} catch(e){}
|
|
try { db.exec("ALTER TABLE items ADD COLUMN profile_id INTEGER;"); } catch(e){}
|
|
try { db.exec("ALTER TABLE items ADD COLUMN keyword_id INTEGER;"); } catch(e){}
|
|
}
|
|
|
|
initDb();
|
|
|
|
const stmts = {
|
|
// Profiles
|
|
getProfiles: db.prepare('SELECT * FROM profiles ORDER BY name'),
|
|
getProfile: db.prepare('SELECT * FROM profiles WHERE id = ?'),
|
|
insertProfile: db.prepare('INSERT INTO profiles (name, price_ratio, common_keywords) VALUES (?, ?, ?)'),
|
|
deleteProfile: db.prepare('DELETE FROM profiles WHERE id = ?'),
|
|
updateProfile: db.prepare('UPDATE profiles SET name = ?, price_ratio = ?, common_keywords = ? WHERE id = ?'),
|
|
updateProfileScanTime: db.prepare('UPDATE profiles SET last_scan_time = CURRENT_TIMESTAMP WHERE id = ?'),
|
|
|
|
// Search Keywords
|
|
getKeywordsByProfile: db.prepare('SELECT * FROM search_keywords WHERE profile_id = ?'),
|
|
insertKeyword: db.prepare('INSERT INTO search_keywords (profile_id, part_number, keywords, target_price) VALUES (?, ?, ?, ?)'),
|
|
deleteKeyword: db.prepare('DELETE FROM search_keywords WHERE id = ?'),
|
|
updateKeyword: db.prepare('UPDATE search_keywords SET part_number = ?, keywords = ?, target_price = ? WHERE id = ?'),
|
|
|
|
// Items
|
|
insertOrUpdateItem: db.prepare(`
|
|
INSERT INTO items (
|
|
id, profile_id, keyword_id, searchType, partNumber, manufacturer, specs, title, url,
|
|
price, shipping, shippingLabel, total, qty, avgPrice, available,
|
|
targetPrice, threshold, passFail, profit, images, detail_response,
|
|
seller_username, seller_feedback_score, seller_feedback_percent,
|
|
review_status
|
|
) VALUES (
|
|
@id, @profile_id, @keyword_id, @searchType, @partNumber, @manufacturer, @specs, @title, @url,
|
|
@price, @shipping, @shippingLabel, @total, @qty, @avgPrice, @available,
|
|
@targetPrice, @threshold, @passFail, @profit, @images, @detail_response,
|
|
@seller_username, @seller_feedback_score, @seller_feedback_percent,
|
|
COALESCE((SELECT review_status FROM items WHERE id = @id), 'waiting')
|
|
)
|
|
ON CONFLICT(id) DO UPDATE SET
|
|
profile_id=excluded.profile_id,
|
|
keyword_id=excluded.keyword_id,
|
|
searchType=excluded.searchType,
|
|
partNumber=excluded.partNumber,
|
|
manufacturer=excluded.manufacturer,
|
|
specs=excluded.specs,
|
|
title=excluded.title,
|
|
url=excluded.url,
|
|
price=excluded.price,
|
|
shipping=excluded.shipping,
|
|
shippingLabel=excluded.shippingLabel,
|
|
total=excluded.total,
|
|
qty=excluded.qty,
|
|
avgPrice=excluded.avgPrice,
|
|
available=excluded.available,
|
|
targetPrice=excluded.targetPrice,
|
|
threshold=excluded.threshold,
|
|
passFail=excluded.passFail,
|
|
profit=excluded.profit,
|
|
images=excluded.images,
|
|
detail_response=excluded.detail_response,
|
|
seller_username=excluded.seller_username,
|
|
seller_feedback_score=excluded.seller_feedback_score,
|
|
seller_feedback_percent=excluded.seller_feedback_percent,
|
|
updated_at=CURRENT_TIMESTAMP
|
|
`),
|
|
|
|
getItem: db.prepare('SELECT * FROM items WHERE id = ?'),
|
|
|
|
getWaitingPassItemsByProfile: db.prepare(`
|
|
SELECT * FROM items
|
|
WHERE passFail = 'PASS' AND review_status = 'waiting' AND profile_id = ?
|
|
ORDER BY profit DESC
|
|
`),
|
|
|
|
getWaitingPassItemsAll: db.prepare(`
|
|
SELECT * FROM items
|
|
WHERE passFail = 'PASS' AND review_status = 'waiting'
|
|
ORDER BY profit DESC
|
|
`),
|
|
|
|
updateReviewStatus: db.prepare('UPDATE items SET review_status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?'),
|
|
updateAiSuggestion: db.prepare('UPDATE items SET ai_suggestion = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?'),
|
|
|
|
getAllMissingAiSuggestion: db.prepare(`
|
|
SELECT * FROM items
|
|
WHERE passFail = 'PASS' AND review_status = 'waiting' AND ai_suggestion IS NULL AND detail_response IS NOT NULL
|
|
`),
|
|
|
|
getAllItems: db.prepare('SELECT * FROM items'),
|
|
|
|
getTopAiItems: db.prepare(`
|
|
SELECT * FROM items
|
|
WHERE passFail = 'PASS'
|
|
AND review_status = 'waiting'
|
|
AND ai_suggestion LIKE '%Hãy mua%'
|
|
`)
|
|
};
|
|
|
|
module.exports = {
|
|
db,
|
|
|
|
// Profiles
|
|
getProfiles() { return stmts.getProfiles.all(); },
|
|
getProfile(id) { return stmts.getProfile.get(id); },
|
|
addProfile(name, ratio, commonKeywords) { return stmts.insertProfile.run(name, ratio, commonKeywords); },
|
|
deleteProfile(id) { return stmts.deleteProfile.run(id); },
|
|
updateProfile(id, name, ratio, commonKeywords) { return stmts.updateProfile.run(name, ratio, commonKeywords, id); },
|
|
updateProfileScanTime(id) { return stmts.updateProfileScanTime.run(id); },
|
|
|
|
// Keywords
|
|
getKeywords(profileId) {
|
|
return stmts.getKeywordsByProfile.all(profileId).map(kw => ({
|
|
...kw,
|
|
keywords: JSON.parse(kw.keywords)
|
|
}));
|
|
},
|
|
addKeyword(profileId, partNumber, keywords, targetPrice) {
|
|
return stmts.insertKeyword.run(profileId, partNumber, JSON.stringify(keywords), targetPrice);
|
|
},
|
|
deleteKeyword(id) { return stmts.deleteKeyword.run(id); },
|
|
updateKeyword(id, partNumber, keywords, targetPrice) {
|
|
return stmts.updateKeyword.run(partNumber, JSON.stringify(keywords), targetPrice, id);
|
|
},
|
|
|
|
// Items
|
|
saveItem(itemData) {
|
|
return stmts.insertOrUpdateItem.run(itemData);
|
|
},
|
|
getItem(id) {
|
|
return stmts.getItem.get(id);
|
|
},
|
|
getWaitingPassItems(profileId) {
|
|
const items = profileId
|
|
? stmts.getWaitingPassItemsByProfile.all(profileId)
|
|
: stmts.getWaitingPassItemsAll.all();
|
|
|
|
return items.map(item => ({
|
|
...item,
|
|
images: item.images ? JSON.parse(item.images) : [],
|
|
detail_response: item.detail_response ? JSON.parse(item.detail_response) : null
|
|
}));
|
|
},
|
|
updateReviewStatus(id, status) {
|
|
return stmts.updateReviewStatus.run(status, id);
|
|
},
|
|
updateAiSuggestion(id, suggestion) {
|
|
return stmts.updateAiSuggestion.run(suggestion, id);
|
|
},
|
|
getMissingAiSuggestionItems() {
|
|
return stmts.getAllMissingAiSuggestion.all().map(item => ({
|
|
...item,
|
|
images: item.images ? JSON.parse(item.images) : [],
|
|
detail_response: item.detail_response ? JSON.parse(item.detail_response) : null
|
|
}));
|
|
},
|
|
getTopAiItems() {
|
|
return stmts.getTopAiItems.all().map(item => ({
|
|
...item,
|
|
images: item.images ? JSON.parse(item.images) : [],
|
|
detail_response: item.detail_response ? JSON.parse(item.detail_response) : null
|
|
}));
|
|
}
|
|
};
|