77 lines
2.9 KiB
JavaScript
77 lines
2.9 KiB
JavaScript
require('dotenv').config();
|
|
const db = require('./db_config');
|
|
|
|
async function setupDatabase() {
|
|
try {
|
|
console.log("Connecting to MariaDB...");
|
|
const connection = await db.getConnection();
|
|
console.log("Connected.");
|
|
|
|
// 1. messages_log
|
|
console.log("Creating messages_log table...");
|
|
await connection.query(`
|
|
CREATE TABLE IF NOT EXISTS messages_log (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
source_number VARCHAR(50) NOT NULL,
|
|
target_number VARCHAR(50) NOT NULL,
|
|
message_content TEXT,
|
|
direction ENUM('INCOMING', 'OUTGOING') NOT NULL,
|
|
has_media BOOLEAN DEFAULT FALSE,
|
|
status VARCHAR(20) DEFAULT 'SENT',
|
|
error_log TEXT
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
`);
|
|
|
|
// 1.1 Add Index for messages_log
|
|
console.log("Adding index to messages_log...");
|
|
try {
|
|
await connection.query(`CREATE INDEX idx_source_timestamp ON messages_log(source_number, timestamp DESC)`);
|
|
} catch (e) {
|
|
if (e.code !== 'ER_DUP_KEYNAME') console.log("Index idx_source_timestamp might already exist or failed:", e.message);
|
|
}
|
|
|
|
// 2. message_queue
|
|
console.log("Creating message_queue table...");
|
|
await connection.query(`
|
|
CREATE TABLE IF NOT EXISTS message_queue (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
target_number VARCHAR(50) NOT NULL,
|
|
message TEXT NOT NULL,
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending, processing, failed, sent
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
`);
|
|
|
|
// 2.1 Add Index for message_queue
|
|
console.log("Adding index to message_queue...");
|
|
try {
|
|
await connection.query(`CREATE INDEX idx_queue_status ON message_queue(status)`);
|
|
} catch (e) {
|
|
if (e.code !== 'ER_DUP_KEYNAME') console.log("Index idx_queue_status might already exist or failed:", e.message);
|
|
}
|
|
|
|
// 3. contacts
|
|
console.log("Creating contacts table...");
|
|
await connection.query(`
|
|
CREATE TABLE IF NOT EXISTS contacts (
|
|
phone VARCHAR(50) PRIMARY KEY,
|
|
name VARCHAR(100),
|
|
saved_name VARCHAR(100),
|
|
is_my_contact BOOLEAN DEFAULT FALSE,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
`);
|
|
|
|
console.log("Database setup completed successfully.");
|
|
connection.release();
|
|
process.exit(0);
|
|
} catch (error) {
|
|
console.error("Database setup failed:", error);
|
|
process.exit(1);
|
|
}
|
|
}
|
|
|
|
setupDatabase();
|