PantryHub/database/migrate.js

require("dotenv").config();

const db = require("../config/database");


console.log("Database migratie gestart...");


db.exec(`

CREATE TABLE IF NOT EXISTS migrations (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    name TEXT NOT NULL UNIQUE,

    executed_at DATETIME DEFAULT CURRENT_TIMESTAMP

);


CREATE TABLE IF NOT EXISTS users (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    name TEXT NOT NULL,

    email TEXT NOT NULL UNIQUE,

    password TEXT NOT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP

);


CREATE TABLE IF NOT EXISTS households (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    name TEXT NOT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP

);


CREATE TABLE IF NOT EXISTS household_members (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    household_id INTEGER NOT NULL,

    user_id INTEGER NOT NULL,

    role TEXT NOT NULL DEFAULT 'member',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,


    FOREIGN KEY (household_id)
        REFERENCES households(id)
        ON DELETE CASCADE,


    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE

);


CREATE TABLE IF NOT EXISTS categories (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    name TEXT NOT NULL,

    icon TEXT,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP

);


CREATE TABLE IF NOT EXISTS products (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    name TEXT NOT NULL,

    category_id INTEGER,

    barcode TEXT,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,


    FOREIGN KEY(category_id)
        REFERENCES categories(id)

);


CREATE TABLE IF NOT EXISTS shopping_lists (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    household_id INTEGER NOT NULL,

    name TEXT NOT NULL,

    archived INTEGER DEFAULT 0,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,


    FOREIGN KEY(household_id)
        REFERENCES households(id)

);


CREATE TABLE IF NOT EXISTS shopping_list_items (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    list_id INTEGER NOT NULL,

    product_id INTEGER NOT NULL,

    amount REAL DEFAULT 1,

    checked INTEGER DEFAULT 0,

    sort_order INTEGER DEFAULT 0,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,


    FOREIGN KEY(list_id)
        REFERENCES shopping_lists(id)
        ON DELETE CASCADE,


    FOREIGN KEY(product_id)
        REFERENCES products(id)

);

CREATE TABLE IF NOT EXISTS stores (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    name TEXT NOT NULL UNIQUE,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP

);


CREATE TABLE IF NOT EXISTS product_favorites (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    household_id INTEGER NOT NULL,

    product_id INTEGER NOT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,


    UNIQUE(household_id, product_id),


    FOREIGN KEY(household_id)
        REFERENCES households(id)
        ON DELETE CASCADE,


    FOREIGN KEY(product_id)
        REFERENCES products(id)
        ON DELETE CASCADE

);


CREATE TABLE IF NOT EXISTS product_stores (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    product_id INTEGER NOT NULL,

    store_id INTEGER NOT NULL,


    aisle TEXT,


    FOREIGN KEY(product_id)
        REFERENCES products(id)
        ON DELETE CASCADE,


    FOREIGN KEY(store_id)
        REFERENCES stores(id)
        ON DELETE CASCADE

);

`);

const addColumnIfMissing = (table, column, definition) => {
    const columns = db.prepare(`PRAGMA table_info(${table})`).all();
    if (!columns.some(existing => existing.name === column)) {
        db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
    }
};

const migrateFavoriteTableToHouseholdScope = () => {
    const columns = db.prepare(`PRAGMA table_info(product_favorites)`).all();
    const hasHouseholdId = columns.some(column => column.name === "household_id");

    if (!hasHouseholdId) {
        db.exec(`ALTER TABLE product_favorites ADD COLUMN household_id INTEGER`);
        db.exec(`UPDATE product_favorites
                 SET household_id = (
                    SELECT products.household_id
                    FROM products
                    WHERE products.id = product_favorites.product_id
                 )
                 WHERE household_id IS NULL`);

        db.exec(`DELETE FROM product_favorites
                 WHERE id NOT IN (
                    SELECT MIN(id)
                    FROM product_favorites
                    GROUP BY household_id, product_id
                 )`);

        db.exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_product_favorites_household_product
                 ON product_favorites (household_id, product_id)`);
    }
};

addColumnIfMissing("categories", "household_id", "INTEGER REFERENCES households(id)");
addColumnIfMissing("products", "household_id", "INTEGER REFERENCES households(id)");
migrateFavoriteTableToHouseholdScope();

const defaultHousehold = db.prepare("SELECT id FROM households ORDER BY id LIMIT 1").get();
if (defaultHousehold) {
    db.prepare("UPDATE categories SET household_id = ? WHERE household_id IS NULL")
        .run(defaultHousehold.id);
    db.prepare("UPDATE products SET household_id = ? WHERE household_id IS NULL")
        .run(defaultHousehold.id);
}


console.log("Database migratie voltooid.");