const express = require("express");
const mysql = require("mysql2");
const bodyParser = require("body-parser");
var cors = require("cors");
const app = express();
const port = process.env.PORT || 8085;
const fs = require("fs");
app.use(cors());

app.use(bodyParser.json({ limit: "50mb" }));
app.use(bodyParser.urlencoded({ limit: "50mb", extended: true }));
app.use(express.json());

let db;
const createdbConnection = () => {
    db = mysql.createConnection({
        host: "localhost",
        user: "familpps_ashish",
        password: "Ashish@123",
        database: "familpps_dreamland"
        // host: "localhost",
        // user: "root",
        // password: "",
        // database: "dreamland",
    });
};
// createdbConnection();
// db.connect((err) => {
//     if (err) {
//         throw err;
//     } else {
//         console.log("connected to db");

//         // const createTableQuery = `
//         //     CREATE TABLE IF NOT EXISTS user_data (
//         //         id INT AUTO_INCREMENT PRIMARY KEY,
//         //         userdata JSON,
//         //         item_selected JSON,
//         //         item_info JSON
//         //     )
//         // `;
//         // db.query(createTableQuery, (error, results, fields) => {
//         //     if (error) {
//         //         throw error;
//         //     }
//         //     console.log("Table created successfully");
//         // });

//         // const data = [
//         //     { name: "John", age: 25 },
//         //     { name: "Jane", age: 30 },
//         //     { name: "Bob", age: 35 },
//         // ];

//         // const query = "INSERT INTO your_table (name, age) VALUES ?";
//         // const values = data.map(({ name, age }) => [name, age]);
//     //     const createTableQuery = `
//     //     CREATE TABLE IF NOT EXISTS avl_items (
//     //         id INT AUTO_INCREMENT PRIMARY KEY,
//     //         itemname VARCHAR(255),
//     //         itemcat VARCHAR(255),
//     //         itemtype VARCHAR(255),
//     //          itemsize VARCHAR(255),
//     //         quantity  INT
//     //     )
//     // `;
//     //     // Insert three rows
//     //     const insertRowsQuery = `
//     //         INSERT INTO avl_items (itemname,itemcat,itemtype, itemsize,quantity)    VALUES
//     //         ('Light Denim Jacket', 'jacket', 2, 'Small', 100),
//     //         ('Light Denim Jacket', 'jacket', 2, 'Medium', 325),
//     //         ('Light Denim Jacket', 'jacket', 2, 'Large', 270),
//     //         ('Light Denim Jacket', 'jacket', 2, 'Extra Large', 100),
//     //         ('Light Denim Jacket', 'jacket', 2, '2XL', 20),
//     //         ('Light Denim Jacket', 'jacket', 2, '3XL', 5),
//     //         ('Light Denim Jacket', 'jacket', 2, '4XL', 5),
//     //         ('Dark Denim Jacket', 'jacket', 3, 'Small', 100),
//     //         ('Dark Denim Jacket', 'jacket', 3, 'Medium', 325),
//     //         ('Dark Denim Jacket', 'jacket', 3, 'Large', 270),
//     //         ('Dark Denim Jacket', 'jacket', 3, 'Extra Large', 100),
//     //         ('Dark Denim Jacket', 'jacket', 3, '2XL', 20),
//     //         ('Dark Denim Jacket', 'jacket', 3, '3XL', 5),
//     //         ('Dark Denim Jacket', 'jacket', 3, '4XL', 5),
//     //         ('White Denim Jacket', 'jacket', 1, 'Small', 100),
//     //         ('White Denim Jacket', 'jacket', 1, 'Medium', 325),
//     //         ('White Denim Jacket', 'jacket', 1, 'Large', 270),
//     //         ('White Denim Jacket', 'jacket', 1, 'Extra Large', 100),
//     //         ('White Denim Jacket', 'jacket', 1, '2XL', 20),
//     //         ('White Denim Jacket', 'jacket', 1, '3XL', 5),
//     //         ('White Denim Jacket', 'jacket', 1, '4XL', 5),
//     //         ('Light Denim Bucket Hat', 'cap', 3, 'Small/Medium', 200),
//     //         ('Light Denim Bucket Hat', 'cap', 3, 'Large/XL', 150),
//     //         ('Dark Denim Bucket Hat', 'cap', 2, 'Small/Medium', 200),
//     //         ('Dark Denim Bucket Hat', 'cap', 2, 'Large/XL', 150),
//     //         ('Black Denim Bucket Hat', 'cap', 1, 'Small/Medium', 200),
//     //         ('Black Denim Bucket Hat', 'cap', 1, 'Large/XL', 150),
//     //         ('Light Denim Tote Bag', 'tote', 2, 'OS', 200),
//     //         ('Dark Denim Tote Bag', 'tote', 1, 'OS', 200),
//     //         ('Black Denim Tote Bag', 'tote', 3, 'OS', 200)
//     //         `;

//     //     db.query(createTableQuery, (error) => {
//     //         if (error) {
//     //             throw error;
//     //         }
//     //         db.query(insertRowsQuery, (error) => {
//     //             if (error) {
//     //                 throw error;
//     //             }
//     //             console.log("Table created successfully");
//     //         });
//     //     });

//     const createTableQuery = `
//         CREATE TABLE IF NOT EXISTS avl_patches (
//             unid INT AUTO_INCREMENT PRIMARY KEY,
//             id INT,
//             src VARCHAR(255),
//             quantity  INT
//         )
//     `;
//         // Insert three rows
//         const insertRowsQuery = `
//             INSERT INTO avl_patches (id,src,quantity)    VALUES
//             ( 1, "heart", 100), ( 2, "bw heart", 100), ( 3, "flower", 100), ( 4, "flower bw", 100), ( 5, "camera bw", 100), ( 5, "camera", 100), ( 7, "peace fingers", 100), ( 8, "peace fingers bw", 100), ( 10, "sun face", 100), ( 11, "sun face bw", 100), ( 12, "rainbow", 100), ( 14, "rainbow bw", 100), ( 14, "slay", 100), ( 15, "slay bw", 100), ( 16, "music butt", 100), ( 17, "music butt bw", 100), ( 18, "drip 1", 100), ( 19, "drip 1 bw", 100), ( 20, "drip 2", 100), ( 21, "drip 2 bw", 100), ( 22, "bulb", 100), ( 23, "bulb bw", 100), ( 24, "rocket", 100), ( 25, "eyes", 100), ( 25, "rocket bw", 100), ( 26, "eyes bw", 100), ( 26, "globe flower bw", 100), ( 26, "globe flower", 100), ( 27, "peace sign", 100), ( 28, "peace sign bw", 100), ( 29, "flame", 100), ( 30, "flame bw", 100), ( 31, "music note", 100), ( 32, "music note bw", 100), ( 33, "star", 100), ( 34, "star bw", 100), ( 35, "butterfly", 100), ( 36, "butterfly bw", 100), ( 37, "smile face", 100), ( 38, "smile face bw", 100), ( 39, "glasses", 100), ( 40, "glasses bw", 100), ( 41, "ying yang cat", 100), ( 42, "ying yang cat bw", 100), ( 43, "headphones", 100), ( 44, "headphones bw", 100), ( 45, "black wt", 100), ( 46, "black -bk", 100), ( 47, "aapi", 100), ( 48, "latino", 100), ( 49, "pride", 100), ( 50, "yt shorts", 100), ( 51, "partner program", 100), ( 52, "ypp black", 100), ( 53, "ypp white", 100), ( 54, "youtube black", 100)
//             `;

//         db.query(createTableQuery, (error) => {
//             if (error) {
//                 throw error;
//             }
//             db.query(insertRowsQuery, (error) => {
//                 if (error) {
//                     throw error;
//                 }
//                 console.log("Table created successfully");
//             });
//         });

//     }
// });

// db.on("error", (err) => console.log(err));

app.post("/api/save", (req, res) => {
    createdbConnection();
    db.connect((errdb) => {
        if (errdb) {
            throw errdb;
        } else {
            const data = req.body;
            const { userName, selectedItem, itemDetails } = data;

            const sql = `INSERT INTO user_data (userdata, item_selected, item_info) VALUES (?, ?, ?)`;

            const values = [
                JSON.stringify(userName),
                JSON.stringify(selectedItem),
                JSON.stringify(itemDetails),
            ];

            // [JSON.stringify(jsonData)]
            db.query(sql, values, (err, result) => {
                if (err) {
                    throw err;
                }
                res.send("Entry updated");
            });
            db.end();
        }
    });
});

app.get("/api/fetchdb", (req, res) => {
    createdbConnection();
    db.connect((err) => {
        if (err) {
            throw err;
        } else {
            console.log("fetching data from db");
            const query = "SELECT * FROM user_data ORDER BY id DESC";

            db.query(query, (err, result) => {
                if (err) {
                    throw err;
                } else {
                    res.status(200).json(result);
                }
            });
            db.end();
        }
    });
});

app.get("/api/fetchinventory", (req, res) => {
    createdbConnection();
    db.connect((err) => {
        if (err) {
            throw err;
        } else {
            console.log("fetching inventory from db");
            const query = "SELECT * FROM avl_items";

            db.query(query, (err, result) => {
                if (err) {
                    throw err;
                } else {
                    res.status(200).json(result);
                }
            });
            db.end();
        }
    });
});

app.get("/api/fetchpatches", (req, res) => {
    createdbConnection();
    db.connect((err) => {
        if (err) {
            throw err;
        } else {
            console.log("fetching fetchpatches from db");
            const query = "SELECT * FROM avl_patches";

            db.query(query, (err, result) => {
                if (err) {
                    throw err;
                } else {
                    res.status(200).json(result);
                }
            });
            db.end();
        }
    });
});

app.put("/api/updateinventory", (req, res) => {
    const data = req.body;
    const { selectedItem } = data;

    console.log(selectedItem);
    createdbConnection();
    db.connect((errdb) => {
        if (errdb) {
            throw errdb;
        } else {
            const updateRowsQuery = `
            UPDATE avl_items
SET quantity = quantity -1
WHERE itemcat = '${selectedItem.itemName}' AND itemtype = '${selectedItem.itemType}'  AND itemsize = '${selectedItem.itemSize}' ;


          `;

            db.query(updateRowsQuery, (err, result) => {
                if (err) {
                    throw err;
                } else {
                    res.send("Inventory updated successfully");
                    db.end();
                }
            });
        }
    });
});

app.put("/api/updatepatchinventory", (req, res) => {
    const data = req.body;
    createdbConnection();
    db.connect((errdb) => {
        if (errdb) {
            throw errdb;
        } else {
            let updateQuery = `UPDATE avl_patches SET quantity = CASE`;

            data.forEach((value) => {
                updateQuery += ` WHEN name = '${value}' THEN quantity -1`;
            });

            updateQuery += ` ELSE quantity END`;

            db.query(updateQuery, (err, result) => {
                if (err) {
                    throw err;
                } else {
                    res.send("Patch Inventory updated successfully");
                    db.end();
                }
            });
        }
    });
});

app.get("/", (req, res) => {
    res.send("Hey, its inforce");
});

app.listen(port, () => {
    console.log(`Server listening on port ${port}`);
});