const oracledb = require("oracledb");
const { logger } = require("./logger")

async function createOraclePool(poolSettings) {
    return oracledb.createPool(poolSettings);
}

async function initOraclePool(poolSettings) {
    logger.info("Initializing Oracle Pool");
    oracledb.fetchAsBuffer = [oracledb.BLOB];
    oracledb.fetchAsString = [oracledb.CLOB];
    await createOraclePool(poolSettings);
    logger.info("Oracle Pool Initialized");
}

async function closeOraclePool() {
    logger.info("Closing Oracle Pool");
    await oracledb.getPool().close();
    logger.info("Closed Oracle Pool");
}

function setup(settings) {
    if (settings.thin) {
        logger.info("Running in thin Mode");
        return;
    }
    let libPath;
    logger.info("Setting up oracle Libraries");
    if (process.platform === 'win32') {           // Windows
    } else if (process.platform === 'darwin') {   // macOS
        libPath = process.env.HOME + '/Downloads/instantclient_19_8';
    }
    if (libPath) {
        oracledb.initOracleClient({ libPath });
    } else {
        oracledb.initOracleClient();
    }
    if (oracledb.thin) {
        logger.info("Running in : Thin Model");
    } else {
        logger.info("Running in : Thick Model");
        logger.info("Oracle client version:" + oracledb.oracleClientVersionString);
    }
}

const connectionWrapper = async function (fn) {
    let connection;
    try {

        // Get a connection from the default pool
        connection = await oracledb.getConnection();
        const result = await fn(connection);
        return result
    } catch (err) {
        throw err;
    } finally {
        if (connection) {
            try {
                // Put the connection back in the pool
                await connection.close();
            } catch (err) {
                console.error(err);
            }
        }
    }
}

const options = {
    maxDepthOfData: 32,
    maxDepthOfQuery: 12,
    outFormat: oracledb.OUT_FORMAT_OBJECT,
};
const productUtils = {
    create: async (body) => {
        const { NAME, CATEGORY, PRICE, IS_ACTIVE, DETAILS, INFO } = body;

        if (!NAME || !CATEGORY || !PRICE || !IS_ACTIVE || !DETAILS || !INFO) {
            throw createError(new Error("All fields (NAME, CATEGORY, PRICE, IS_ACTIVE, DETAILS, INFO) are required"), 400);
        }
        const insertQuery = `
    INSERT INTO PRODUCT (NAME, CATEGORY, PRICE, IS_ACTIVE, DETAILS, INFO)
    VALUES (:NAME, :CATEGORY, :PRICE, :IS_ACTIVE, :DETAILS, :INFO)
    RETURNING ID INTO :ID
  `;


        return await connectionWrapper(async (connection) => {
            const result = await connection.execute(insertQuery, {
                NAME,
                CATEGORY,
                PRICE,
                IS_ACTIVE,
                DETAILS,
                INFO,
                ID: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER }
            }, { ...options, autoCommit: true });

            return {
                ID: result.outBinds.ID[0]
            };
        });
    },
    findById: async (id) => {
        const query = `
        SELECT ID, NAME, CATEGORY, PRICE, IS_ACTIVE, DETAILS, INFO
        FROM PRODUCT
        WHERE ID = :id
      `;

        return connectionWrapper(async (connection) => {

            const result = await connection.execute(query, [id], options);
            if (result.rows.length === 0) {
                console.log(`Product with ID ${id} not found.`);
                throw createError(new Error("NOT_FOUND"), 404);
            }
            return result.rows[0];
        })
    },
    patchById: async (id, body) => {
        const { NAME, CATEGORY, PRICE, IS_ACTIVE, DETAILS, INFO } = body;


        let updateFields = [];
        let bindParams = { ID: id };
        if (NAME) { updateFields.push(`NAME = :NAME`); bindParams.NAME = NAME; }
        if (CATEGORY) { updateFields.push(`CATEGORY = :CATEGORY`); bindParams.CATEGORY = CATEGORY; }
        if (PRICE) { updateFields.push(`PRICE = :PRICE`); bindParams.PRICE = PRICE; }
        if (IS_ACTIVE) { updateFields.push(`IS_ACTIVE = :IS_ACTIVE`); bindParams.IS_ACTIVE = IS_ACTIVE; }
        if (DETAILS) { updateFields.push(`DETAILS = :DETAILS`); bindParams.DETAILS = DETAILS; }
        if (INFO) { updateFields.push(`INFO = :INFO`); bindParams.INFO = INFO; }


        if (updateFields.length === 0) {
            throw createError(new Error("No fields to update"), 400);
        }


        const updateQuery = `
        UPDATE PRODUCT
        SET ${updateFields.join(', ')}
        WHERE ID = :ID
      `;

        await connectionWrapper(async (connection) => {
            // Execute the update query
            const result = await connection.execute(updateQuery, bindParams, { ...options, autoCommit: true });
            if (result.rowsAffected === 0) {
                return res.status(404).json({ error: "Product not found" });
            }
        });

        return productUtils.findById(id);
    },
    deleteById: async (id) => {
        const deleteQuery = `
          DELETE FROM PRODUCT
          WHERE ID = :ID
        `;

        await connectionWrapper(async (connection) => {
            // Execute the delete query
            const result = await connection.execute(deleteQuery, { ID: id }, { autoCommit: true });

            // Check if any rows were deleted
            if (result.rowsAffected === 0) {
                throw createError(new Error("Product not found"), 404);
            }
        });
    }
}

function createError(error, statusCode) {
    error.status = statusCode;
    return error;
}

module.exports = {
    initOraclePool,
    closeOraclePool,
    setup,
    productUtils,
    createError
}
