底层设计：轮询系统 - 边缘情况

create database polling_system;use polling_system;create table polls (    poll_id int auto_increment primary key,    current_version_id int,    created_at timestamp default current_timestamp,    foreign key (current_version_id) references poll_versions(version_id) on delete set null);create table poll_versions (    version_id int auto_increment primary key,    poll_id int,    question varchar(255) not null,    created_at timestamp default current_timestamp,    foreign key (poll_id) references polls(poll_id) on delete cascade);create table options (    option_id int auto_increment primary key,    poll_id int,    option_text varchar(255) not null,    foreign key (poll_id) references polls(poll_id) on delete cascade);create table votes (    vote_id int auto_increment primary key,    poll_id int,    user_id varchar(255) not null,    option_id int,    created_at timestamp default current_timestamp,    foreign key (poll_id) references polls(poll_id) on delete cascade,    foreign key (option_id) references options(option_id) on delete cascade);

const pool = require('../db/db');// create pollexports.createpoll = async (req, res) => {    const { question, options } = req.body;    if (!question || !options || !array.isarray(options) || options.length  {            return connection.execute(                'insert into options (poll_id, option_text) values (?, ?)',                [pollid, option]            );        });        await promise.all(optionqueries);        await connection.commit();        connection.release();        res.status(201).json({ pollid, message: "poll created successfully." });    } catch (error) {        console.error("error creating poll:", error.message);        res.status(500).json({ message: "error creating poll." });    }};// update pollexports.updatepoll = async (req, res) => {    const { pollid } = req.params;    const { question, options } = req.body;    if (!pollid || !options || !array.isarray(options) || options.length  {            return connection.execute(                'insert into options (poll_id, option_text) values (?, ?)',                [pollid, option]            );        });        await promise.all(optionqueries);        await connection.commit();        connection.release();        res.status(200).json({ message: "poll updated successfully." });    } catch (error) {        console.error("error updating poll:", error.message);        await connection.rollback();        res.status(500).json({ message: "error updating poll." });    }};// delete pollexports.deletepoll = async (req, res) => {    const { pollid } = req.params;    try {        const connection = await pool.getconnection();        const [result] = await connection.execute(            'delete from polls where poll_id = ?',            [pollid]        );        connection.release();        if (result.affectedrows === 0) {            return res.status(404).json({ message: "poll not found." });        }        res.status(200).json({ message: "poll deleted successfully." });    } catch (error) {        console.error("error deleting poll:", error.message);        res.status(500).json({ message: "error deleting poll." });    }};// vote in pollexports.voteinpoll = async (req, res) => {    const { pollid } = req.params;    const { userid, option } = req.body;    if (!userid || !option) {        return res.status(400).json({ message: "user id and option are required." });    }    try {        const connection = await pool.getconnection();        const [uservote] = await connection.execute(            'select * from votes where poll_id = ? and user_id = ?',            [pollid, userid]        );        if (uservote.length > 0) {            connection.release();            return res.status(400).json({ message: "user has already voted." });        }        const [optionresult] = await connection.execute(            'select option_id from options where poll_id = ? and option_text = ?',            [pollid, option]        );        if (optionresult.length === 0) {            connection.release();            return res.status(404).json({ message: "option not found." });        }        const optionid = optionresult[0].option_id;        await connection.execute(            'insert into votes (poll_id, user_id, option_id) values (?, ?, ?)',            [pollid, userid, optionid]        );        connection.release();        res.status(200).json({ message: "vote cast successfully." });    } catch (error) {        console.error("error casting vote:", error.message);        res.status(500).json({ message: "error casting vote." });    }};// view poll resultsexports.viewpollresults = async (req, res) => {    const { pollid } = req.params;    try {        const connection = await pool.getconnection();        const [poll] = await connection.execute(            'select * from polls where poll_id = ?',            [pollid]        );        if (poll.length === 0) {            connection.release();            return res.status(404).json({ message: "poll not found." });        }        const [options] = await connection.execute(            'select option_text, count(votes.option_id) as vote_count from options ' +            'left join votes on options.option_id = votes.option_id ' +            'where options.poll_id = ? group by options.option_id',            [pollid]        );        connection.release();        res.status(200).json({            pollid: poll[0].poll_id,            question: poll[0].question,            results: options.reduce((acc, option) => {                acc[option.option_text] = option.vote_count;                return acc;            }, {})        });    } catch (error) {        console.error("error viewing poll results:", error.message);        res.status(500).json({ message: "error viewing poll results." });    }};

const express = require('express');const router = express.Router();const pollController = require('../controllers/pollController');// Routesrouter.post('/polls', pollController.createPoll);router.put('/polls/:pollId', pollController.updatePoll);router.delete('/polls/:pollId', pollController.deletePoll);router.post('/polls/:pollId/vote', pollController.voteInPoll);router.get('/polls/:pollId/results', pollController.viewPollResults);module.exports = router;