bookManagement/models.js

const { Sequelize, DataTypes, Op } = require('sequelize');

// Initialize Sequelize
const sequelize = new Sequelize({
    dialect: 'sqlite',
    storage: 'data/books.db',
});


// Test the connection

sequelize.authenticate()
    .then(() => {
        console.log('Connection has been established successfully.');
    })
    .catch(err => {
        console.error('Unable to connect to the database:', err);
    });


const Location = sequelize.define('Location', {
    id: {
        type: DataTypes.INTEGER,
        autoIncrement: true,
        primaryKey: true,
    },
    name: {
        type: DataTypes.TEXT,
        allowNull: false,
    },
    shelf: {
        type: DataTypes.TEXT,
        allowNull: false,
    }
}, {
    tableName: 'locations',
    timestamps: false, // If your table doesn't have `createdAt` and `updatedAt`
});
const Book = sequelize.define('Book', {
    id: {
        type: DataTypes.INTEGER,
        autoIncrement: true,
        primaryKey: true,
    },
    isbn: {
        type: DataTypes.TEXT,
        allowNull: false,
        unique: true,
    },
    title: {
        type: DataTypes.TEXT,
        allowNull: false,
    },
    authors: {
        type: DataTypes.TEXT,
    },
    publishedDate: {
        type: DataTypes.TEXT,
    },
    description: {
        type: DataTypes.TEXT,
    },
    url: {
        type: DataTypes.TEXT,
    },
    number_of_pages: {
        type: DataTypes.INTEGER,
    },
    identifiers: {
        type: DataTypes.TEXT,
    },
    publishers: {
        type: DataTypes.TEXT,
    },
    subjects: {
        type: DataTypes.TEXT,
    },
    notes: {
        type: DataTypes.TEXT,
    },
    cover_small: {
        type: DataTypes.TEXT,
    },
    cover_medium: {
        type: DataTypes.TEXT,
    },
    cover_large: {
        type: DataTypes.TEXT,
    },
    location_id: {
        type: DataTypes.INTEGER,
        references: {
            model: Location,
            key: 'id'
        }
    },
    status: { // Available, Checked Out, Lost, etc.
        type: DataTypes.TEXT,
        defaultValue: 'Available'
    }
}, {
    tableName: 'books',
    timestamps: false, // If your table doesn't have `createdAt` and `updatedAt`
});

// Define a table for checkouts
const Checkout = sequelize.define('Checkout', {
    id: {
        type: DataTypes.INTEGER,
        autoIncrement: true,
        primaryKey: true,
    },
    book_id: {
        type: DataTypes.INTEGER,
        references: {
            model: Book,
            key: 'id'
        }
    },
    user_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
    checkout_date: {
        type: DataTypes.DATE,
    },
    return_date: {
        type: DataTypes.DATE,
    },
    returned_date: {
        type: DataTypes.DATE,
    },
    approved: { // New column to track approval status
        type: DataTypes.BOOLEAN,
        defaultValue: false
    }
}, {
    tableName: 'checkouts',
    timestamps: false, // If your table doesn't have `createdAt` and `updatedAt`
});

// Define User table
const User = sequelize.define('User', {
    id: {
        type: DataTypes.INTEGER,
        autoIncrement: true,
        primaryKey: true,
    },
    name: {
        type: DataTypes.TEXT,
        allowNull: false,
    },
    email: {
        type: DataTypes.TEXT,
        allowNull: false,
    },
    role: {
        type: DataTypes.TEXT,
        defaultValue: 'user'
    }
}, {
    tableName: 'users',
    timestamps: false, // If your table doesn't have `createdAt` and `updatedAt`
});

// Define the relationships
Book.belongsTo(Location, { foreignKey: 'location_id' });
Location.hasMany(Book, { foreignKey: 'location_id' });
Checkout.belongsTo(Book, { foreignKey: 'book_id' });
Book.hasOne(Checkout, { foreignKey: 'book_id' });
Checkout.belongsTo(User, { foreignKey: 'user_id' });
User.hasMany(Checkout, { foreignKey: 'user_id' });

sequelize.sync().then(() => {
    console.log('Database & tables synced!');
});

exports.Location = Location;
exports.Book = Book;
exports.Checkout = Checkout;
exports.User = User;
exports.sequelize = sequelize;
exports.Op = Op;