const express = require('express')
const sqlite3 = require("sqlite3").verbose()
const app = express()

const db = new sqlite3.Database("db/student.db")

app.get('/', function (req, res) { //根路由返回index.html前端文件
    res.setHeader('Content-Type', 'text/html')
    res.sendFile(`${__dirname}/static/index.html`)
})
app.get('/select', (req, response) => { //查询
    const saddr = req.query.saddr ?? ""
    console.log(saddr)
    if (saddr) {
        db.all(`select * from S where SADDR = '${saddr}';`, (e, res) => {
            console.log(res)
            response.json(res)
        })
    } else {
        db.all(`select * from S;`, (e, res) => {
            console.log(res)
            response.json(res)
        })
    }
})

app.get('/update', (req, res) => { //修改
    const sno = req.query.sno
    const sname = req.query.sname
    const ssex = req.query.ssex
    const sage = req.query.sage
    const saddr = req.query.saddr
    if (sno) {
        db.run(`update S set SNAME = '${sname}', SSEX = '${ssex}', SAGE = ${sage}, SADDR = '${saddr}' where SNO = ${sno};`, (e) => {
            if (!e) {
                res.json({success: "更新成功"})
            } else {
                res.json({err: e.toString()})
            }
        })
    } else {
        res.json({err: "sno is required"})
    }
})

app.get('/insert', (req, res) => { //插入
    const sno = req.query.sno
    const sname = req.query.sname
    const ssex = req.query.ssex
    const sage = req.query.sage
    const saddr = req.query.saddr
    if (sno) {
        db.run(`insert into S values(${sno}, '${sname}', '${ssex}', ${sage}, '${saddr}')`, (e) => {
            if (!e) {
                res.json({success: "插入成功"})
            } else {
                res.json({err: e.toString()})
            }
        })
    } else {
        res.json({err: "sno is required"})
    }
})

app.get('/delete', (req, res) => { //删除
    const sno = req.query.sno
    if (sno) {
        db.run(`delete from S where SNO = ${sno}`, (e) => {
            if (!e) {
                res.json({success: "删除成功"})
            } else {
                res.json({err: e.toString()})
            }
        })
    } else {
        res.json({err: "sno is required"})
    }
})
app.listen(3000)
console.log("程序运行在 http://127.0.0.1:3000")