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();