module.exports = class BookModel {
    constructor(db) {
        this.db = db;
    }

    async createList(listname, user_id) {
        return new Promise((resolve, reject) => {
            this.db.query(
                'INSERT INTO book_lists (listname, user_id) VALUES (?, ?)',
                [listname, user_id],
                (err, results) => {
                    if (err) reject(err);
                    resolve(results);
                }
            );
        });
    }

    async getListsByUser(user_id) {
        return new Promise((resolve, reject) => {
            this.db.query(
                'SELECT * FROM book_lists WHERE user_id = ?',
                [user_id],
                (err, results) => {
                    if (err) reject(err);
                    resolve(results);
                }
            );
        });
    }

    async updateList(list_id, listname, user_id) {
        return new Promise((resolve, reject) => {
            this.db.query(
                'UPDATE book_lists SET listname = ? WHERE list_id = ? AND user_id = ?',
                [listname, list_id, user_id],
                (err, results) => {
                    if (err) reject(err);
                    resolve(results);
                }
            );
        });
    }

    async deleteList(list_id, user_id) {
        return new Promise((resolve, reject) => {
            this.db.query(
                'DELETE FROM book_lists WHERE list_id = ? AND user_id = ?',
                [list_id, user_id],
                (err, results) => {
                    if (err) reject(err);
                    resolve(results);
                }
            );
        });
    }

    async addBook(bookname, author, category, type, list_id, user_id, image_url) {
        return new Promise((resolve, reject) => {
            this.db.query(
                'INSERT INTO books (bookname, author, category, type, list_id, user_id, image_url) VALUES (?, ?, ?, ?, ?, ?, ?)',
                [bookname, author, category, type, list_id, user_id, image_url],
                (err, results) => {
                    if (err) reject(err);
                    resolve(results);
                }
            );
        });
    }

    async getBooksByList(list_id) {
        return new Promise((resolve, reject) => {
            this.db.query(
                'SELECT * FROM books WHERE list_id = ?',
                [list_id],
                (err, results) => {
                    if (err) reject(err);
                    resolve(results);
                }
            );
        });
    }

    async getBookById(book_id) {
        return new Promise((resolve, reject) => {
            this.db.query(
                'SELECT * FROM books WHERE book_id = ?',
                [book_id],
                (err, results) => {
                    if (err) reject(err);
                    resolve(results);
                }
            );
        });
    }
    
    async updateBook(book_id, user_id, { bookname, author, category, type, image_url }) {
        return new Promise((resolve, reject) => {
            const sql = `
                UPDATE books 
                SET bookname = ?, 
                    author = ?, 
                    category = ?, 
                    type = ?, 
                    image_url = ? 
                WHERE book_id = ? AND user_id = ?`;
                
            this.db.query(
                sql,
                [bookname, author, category, type, image_url, book_id, user_id],
                (err, results) => {
                    if (err) {
                        console.error('Database error:', err);
                        reject(err);
                        return;
                    }
                    resolve(results);
                }
            );
        });
    }
    
    async deleteBook(book_id, user_id) {
        return new Promise((resolve, reject) => {
            // Begin transaction
            this.db.beginTransaction(async (err) => {
                if (err) {
                    return reject(err);
                }
    
                try {
                    // First delete all reviews for this book
                    await new Promise((res, rej) => {
                        this.db.query(
                            'DELETE FROM book_reviews WHERE book_id = ?',
                            [book_id],
                            (err) => {
                                if (err) rej(err);
                                res();
                            }
                        );
                    });
    
                    // Then delete the book
                    await new Promise((res, rej) => {
                        this.db.query(
                            'DELETE FROM books WHERE book_id = ? AND user_id = ?',
                            [book_id, user_id],
                            (err) => {
                                if (err) rej(err);
                                res();
                            }
                        );
                    });
    
                    // If everything successful, commit the transaction
                    this.db.commit((err) => {
                        if (err) {
                            return this.db.rollback(() => {
                                reject(err);
                            });
                        }
                        resolve();
                    });
    
                } catch (error) {
                    // If error occurs, rollback the transaction
                    return this.db.rollback(() => {
                        reject(error);
                    });
                }
            });
        });
    }
    
    async getRecentLists(limit, user_id) {
        return new Promise((resolve, reject) => {
            this.db.query(
                `SELECT bl.*, u.username 
                 FROM book_lists bl 
                 JOIN users u ON bl.user_id = u.user_id 
                 WHERE bl.user_id = ?
                 ORDER BY bl.list_id DESC 
                 LIMIT ?`,
                [user_id, limit],
                (err, results) => {
                    if (err) reject(err);
                    resolve(results);
                }
            );
        });
    }

    async createBook({ bookname, author, category, type, image_url, list_id, user_id }) {
        return new Promise((resolve, reject) => {
            this.db.query(
                'INSERT INTO books (bookname, author, category, type, image_url, list_id, user_id) VALUES (?, ?, ?, ?, ?, ?, ?)',
                [bookname, author, category, type, image_url, list_id, user_id],
                (err, results) => {
                    if (err) {
                        console.error('Database error:', err);
                        reject(err);
                        return;
                    }
                    resolve(results);
                }
            );
        });
    }

    async searchBooks(query, searchType, user_id) {
        return new Promise((resolve, reject) => {
            let sql = '';
            let params = [];
    
            switch(searchType) {
                case 'book':
                    sql = `SELECT b.*, bl.listname, u.username 
                           FROM books b 
                           JOIN book_lists bl ON b.list_id = bl.list_id 
                           JOIN users u ON b.user_id = u.user_id 
                           WHERE (b.bookname LIKE ? OR b.author LIKE ? 
                           OR b.category LIKE ? OR b.type LIKE ?)
                           AND b.user_id = ?`;
                    params = [`%${query}%`, `%${query}%`, `%${query}%`, `%${query}%`, user_id];
                    break;
                case 'list':
                    sql = `SELECT bl.*, u.username 
                           FROM book_lists bl 
                           JOIN users u ON bl.user_id = u.user_id 
                           WHERE bl.listname LIKE ? 
                           AND bl.user_id = ?`;
                    params = [`%${query}%`, user_id];
                    break;
            }
    
            this.db.query(sql, params, (err, results) => {
                if (err) reject(err);
                resolve(results);
            });
        });
    }

    async getBookByIdAndUser(book_id, user_id) {
        return new Promise((resolve, reject) => {
            this.db.query(
                'SELECT * FROM books WHERE book_id = ? AND user_id = ?',
                [book_id, user_id],
                (err, results) => {
                    if (err) reject(err);
                    resolve(results[0]);
                }
            );
        });
    }
};