const express = require('express');
const mysql = require('mysql');
const cors = require('cors');

const app = express()
app.use(cors())
app.use(express.json())

/* DATABASE */
const db = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "psm_portal"
})

/* TOTALS */
app.get('/totals', (req, res)=> {

    const sql = `SELECT 
    (SELECT COUNT(*) FROM products) AS total_products,
    (SELECT COUNT(*) FROM customers) AS total_customers,
    (SELECT SUM(total_amount) FROM sales) AS total_sales,
    (SELECT COUNT(*) FROM invoice_items) AS total_invoice
    `;

    db.query(sql, (err, data) => {
    if (err) return res.json(err);
    return res.json(data[0]);
    });
})

/* GET PRODUCTS */
app.get('/products', (req, res)=>{
    const sql = "SELECT * FROM products ORDER BY id DESC"
    db.query(sql, (err, data)=>{
        if(err) return res.json(err);
        return res.json(data);
    })
})

/* ADD PRODUCTS */
app.post("/products",(req,res)=>{
    const {name,unit,status,price,sale_price} = req.body
    const slug = name.toLowerCase().replace(/\s+/g,"-")
    const sql = `INSERT INTO products(name,unit,status,price,sale_price,slug)VALUES(?,?,?,?,?,?)`
    db.query(sql,[name,unit,status,price,sale_price,slug],(err,result)=>{
        if(err){
            res.status(500).json(err)
        }else{
            res.json({
                message:"Product added",
                id:result.insertId
            })
        }
    })
})

/* UPDATE PRODUCTS */
app.put("/products/:id",(req,res)=>{
    const id = req.params.id
    const {name,unit,status,price,sale_price} = req.body
    const slug = name.toLowerCase().replace(/\s+/g,"-")
    const sql = `UPDATE products SET name=?, unit=?, status=?, price=?, sale_price=?, slug=? WHERE id=?`
    db.query(sql,[name,unit,status,price,sale_price,slug,id],(err,result)=>{
        if(err){
            res.status(500).json(err)
        }else{
            res.json({message:"Product updated"})
        }
    })
})

/* DELETE CATEGORY */

app.delete("/products/:id",(req,res)=>{
    const id = req.params.id
    const sql = "DELETE FROM products WHERE id=?"
    db.query(sql,[id],(err,result)=>{
        if(err){
            res.status(500).json(err)
        }else{
            res.json({message:"Product deleted"})
        }
    })
})

/* GET CATEGORY */
app.get("/categories",(req,res)=>{
    const sql = "SELECT * FROM categories ORDER BY id DESC"
    db.query(sql,(err,result)=>{
    if(err){
        res.status(500).json(err)
    }else{
        res.json(result)
    }
    })

})


/* ADD CATEGORY */
app.post("/categories",(req,res)=>{
    const {name,ctype,description} = req.body
    const slug = name.toLowerCase().replace(/\s+/g,"-")
    const sql = `INSERT INTO categories(name,ctype,description,slug)VALUES(?,?,?,?)`
    db.query(sql,[name,ctype,description,slug],(err,result)=>{
        if(err){
            res.status(500).json(err)
        }else{
            res.json({
                message:"Category added",
                id:result.insertId
            })
        }
    })
})


/* UPDATE CATEGORY */
app.put("/categories/:id",(req,res)=>{
    const id = req.params.id
    const {name,ctype,description} = req.body
    const slug = name.toLowerCase().replace(/\s+/g,"-")
    const sql = `UPDATE categories SET name=?, ctype=?, description=?, slug=? WHERE id=?`
    db.query(sql,[name,ctype,description,slug,id],(err,result)=>{
        if(err){
            res.status(500).json(err)
        }else{
            res.json({message:"Category updated"})
        }
    })
})


/* DELETE CATEGORY */

app.delete("/categories/:id",(req,res)=>{
    const id = req.params.id
    const sql = "DELETE FROM categories WHERE id=?"
    db.query(sql,[id],(err,result)=>{
        if(err){
            res.status(500).json(err)
        }else{
            res.json({message:"Category deleted"})
        }
    })
})

/* GET CUSTOMERS */
app.get("/customers",(req,res)=>{
    const sql = "SELECT * FROM customers ORDER BY id DESC"
    db.query(sql,(err,result)=>{
    if(err){
        res.status(500).json(err)
    }else{
        res.json(result)
    }
    })

})


/* ADD CUSTOMERS */
app.post("/customers",(req,res)=>{
    const {name,phone,address,ctype} = req.body
    const slug = name.toLowerCase().replace(/\s+/g,"-")
    const sql = `INSERT INTO customers(name,phone,address,ctype)VALUES(?,?,?,?)`
    db.query(sql,[name,phone,address,ctype],(err,result)=>{
        if(err){
            res.status(500).json(err)
        }else{
            res.json({
                message:"Customers added",
                id:result.insertId
            })
        }
    })
})


/* UPDATE CUSTOMERS */
app.put("/customers/:id",(req,res)=>{
    const id = req.params.id
    const {name,phone,address,ctype} = req.body
    const slug = name.toLowerCase().replace(/\s+/g,"-")
    const sql = `UPDATE customers SET name=?, phone=?, address=?, ctype=? WHERE id=?`
    db.query(sql,[name,phone,address,ctype,id],(err,result)=>{
        if(err){
            return res.status(500).json(err)
        }else{
            res.json({message:"Customers updated"})
        }
    })
})


/* DELETE CUSTOMERS */

app.delete("/customers/:id",(req,res)=>{
    const id = req.params.id
    const sql = "DELETE FROM customers WHERE id=?"
    db.query(sql,[id],(err,result)=>{
        if(err){
            res.status(500).json(err)
        }else{
            res.json({message:"Customers deleted"})
        }
    })
})

/* ========================= */
/* ✅ ADD THIS PART (IMPORTANT) */
/* ========================= */

// Serve Vite build
app.use(express.static(path.join(__dirname, "dist")));

// React routing fix
app.get("*", (req, res) => {
    res.sendFile(path.join(__dirname, "dist", "index.html"));
});

/* ========================= */

const PORT = process.env.PORT || 8000; // ✅ UPDATED

app.listen(PORT, ()=>{
    console.log("Listening on port", PORT);
})