module.exports = {
    up: async ({ context: db }) => {
        db.exec(`
            CREATE TABLE IF NOT EXISTS anniversaries (
                ID INTEGER PRIMARY KEY AUTOINCREMENT,
                name   VARCHAR(50) NOT NULL,
                guild_id TEXT NOT NULL,
                discord_id   VARCHAR(50) NOT NULL,
                last_anniversary_notification TEXT
            );
        `);
        db.exec(`
            CREATE TABLE IF NOT EXISTS lastfm (
                discord_id TEXT PRIMARY KEY NOT NULL,
                lastfm_name TEXT
            );
        `);
        db.exec(`
            CREATE TABLE IF NOT EXISTS bot_config (
                id INTEGER PRIMARY KEY CHECK (id = 1),
                weekly_budget REAL DEFAULT 0,
                last_date_msg_receipts DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
            );
        `);
        db.exec(`
            CREATE TABLE IF NOT EXISTS weekly_budgets (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                start_date DATE NOT NULL,
                end_date DATE NOT NULL,
                budget_amount DECIMAL(10, 2) NOT NULL,
                exchange_rate DECIMAL(10, 6) NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT date_check CHECK (end_date > start_date)
            );
        `);
        db.exec(`CREATE INDEX IF NOT EXISTS idx_budget_dates ON weekly_budgets (start_date, end_date);`);
        db.exec(`
            CREATE TABLE IF NOT EXISTS grocery_spendings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                discord_id VARCHAR(50) NOT NULL,
                message_id VARCHAR(50) NOT NULL,
                message_raw TEXT DEFAULT "-",
                amount DECIMAL(10, 2) NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                budget_id INTEGER,
                FOREIGN KEY (budget_id) REFERENCES weekly_budgets(id)
            );
        `);
        db.exec(`
            INSERT OR IGNORE INTO bot_config (id, weekly_budget) 
            VALUES (1, 10);
        `);
    },
    down: async ({ context: db }) => {
        db.exec(`DROP TABLE IF EXISTS grocery_spendings;`);
        db.exec(`DROP TABLE IF EXISTS weekly_budgets;`);
        db.exec(`DROP TABLE IF EXISTS bot_config;`);
        db.exec(`DROP TABLE IF EXISTS lastfm;`);
        db.exec(`DROP TABLE IF EXISTS anniversaries;`);
    }
};