73 lines
2.0 KiB
JavaScript
73 lines
2.0 KiB
JavaScript
const sqlite3 = require('sqlite3').verbose();
|
|
const path = require('path');
|
|
|
|
const dbPath = process.env.NODE_ENV === 'production'
|
|
? '/app/data/cashback.db'
|
|
: path.join(__dirname, 'cashback.db');
|
|
|
|
const db = new sqlite3.Database(dbPath, (err) => {
|
|
if (err) {
|
|
console.error('Error opening database:', err);
|
|
} else {
|
|
console.log('Connected to SQLite database');
|
|
}
|
|
});
|
|
|
|
// Initialize tables
|
|
db.serialize(() => {
|
|
// Cards table
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS cards (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
bank TEXT NOT NULL,
|
|
description TEXT,
|
|
image_url TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
// Categories table
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
// Monthly categories table
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS monthly_categories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
card_id INTEGER NOT NULL,
|
|
category_id INTEGER NOT NULL,
|
|
month INTEGER NOT NULL,
|
|
year INTEGER NOT NULL,
|
|
cashback_percent REAL NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (card_id) REFERENCES cards (id) ON DELETE CASCADE,
|
|
FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE,
|
|
UNIQUE(card_id, category_id, month, year)
|
|
)
|
|
`);
|
|
|
|
// Transactions table
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS transactions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
card_id INTEGER NOT NULL,
|
|
category_id INTEGER NOT NULL,
|
|
amount REAL NOT NULL,
|
|
cashback_amount REAL NOT NULL,
|
|
date DATE NOT NULL,
|
|
description TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (card_id) REFERENCES cards (id) ON DELETE CASCADE,
|
|
FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
});
|
|
|
|
module.exports = db; |