CREATE TABLE `todo_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(150) CHARACTER SET utf8 NOT NULL,
  `status` enum('Doing','Finished') DEFAULT 'Doing',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

npm init -y
npm install express typescript ts-node mysql2 @types/node @types/express --save-dev
npx tsc --init

{
  "compilerOptions": {
    "target": "ES2020",
    "module": "CommonJS",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true,
    "outDir": "./dist"
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules"]
}

docker run -d --name demo-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-root-pass -e MYSQL_DATABASE=todo_db mysql:8.3.1

import express, { Request, Response } from 'express';
import mysql, { RowDataPacket, ResultSetHeader } from 'mysql2';

interface TodoItem extends RowDataPacket {
  id: number;
  title: string;
  status: 'Doing' | 'Finished';
  created_at: string;
  updated_at: string;
}

interface CreateTodoItem {
  title: string;
}

interface UpdateTodoItem {
  title?: string;
  status?: 'Doing' | 'Finished';
}

const db = mysql.createConnection({
  host: 'localhost',
  user: process.env.USER,
  password: process.env.PASS,
  database: process.env.DATABASE,
});

db.connect((err) => {
  if (err) {
    console.error('Error connect:', err.message);
  } else {
    console.log('Connected to MySQL');
  }
});

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

app.post('/v1/items', (req: Request<{}, {}, CreateTodoItem>, res: Response) => {
  const { title } = req.body;

  if (!title || !title.trim()) {
    return res.status(400).json({ message: 'Title cannot be empty' });
  }

  const sql = 'INSERT INTO todo_items (title) VALUES (?)';
  db.query<ResultSetHeader>(sql, [title], (err, result) => {
    if (err) {
      return res.status(500).json({ message: 'Database error', error: err });
    }
    return res.status(201).json({ id: result.insertId });
  });
});

app.get('/v1/items', (req: Request, res: Response) => {
  const page = parseInt(req.query.page as string) || 1;
  const limit = parseInt(req.query.limit as string) || 10;
  const offset = (page - 1) * limit;

  const sql = 'SELECT * FROM todo_items LIMIT ? OFFSET ?';
  db.query<TodoItem[]>(sql, [limit, offset], (err, results) => {
    if (err) {
      return res.status(500).json({ message: 'Database error', error: err });
    }
    return res.status(200).json(results);
  });
});

app.put(
  '/v1/items/:id',
  (req: Request<{ id: string }, {}, UpdateTodoItem>, res: Response) => {
    const { id } = req.params;
    const { title, status } = req.body;

    if (!title && !status) {
      return res.status(400).json({ message: 'Nothing to update' });
    }

    const updates: any[] = [];
    let sql = 'UPDATE todo_items SET ';

    if (title) {
      sql += 'title = ? ';
      updates.push(title);
    }

    if (status) {
      if (updates.length > 0) {
        sql += ', ';
      }
      sql += 'status = ? ';
      updates.push(status);
    }

    sql += 'WHERE id = ?';
    updates.push(id);

    db.query<ResultSetHeader>(sql, updates, (err, result) => {
      if (err) {
        return res.status(500).json({ message: 'Database error', error: err });
      }

      if (result.affectedRows === 0) {
        return res.status(404).json({ message: 'Item not found' });
      }

      return res.status(200).json({ message: 'Item updated successfully' });
    });
  }
);

app.delete('/v1/items/:id', (req: Request<{ id: string }>, res: Response) => {
  const { id } = req.params;

  const sql = 'DELETE FROM todo_items WHERE id = ?';
  db.query<ResultSetHeader>(sql, [id], (err, result) => {
    if (err) {
      return res.status(500).json({ message: 'Database error', error: err });
    }

    if (result.affectedRows === 0) {
      // Không tìm thấy item với ID đã cung cấp
      return res.status(404).json({ message: 'Item not found' });
    }

    return res.status(200).json({ message: 'Item deleted successfully' });
  });
});

app.get('/v1/items/:id', (req: Request<{ id: string }>, res: Response) => {
  const { id } = req.params;

  const sql = 'SELECT * FROM todo_items WHERE id = ?';
  db.query<TodoItem[]>(sql, [id], (err, result) => {
    if (err) {
      return res.status(500).json({ message: 'Database error', error: err });
    }
    if (result.length === 0) {
      return res.status(404).json({ message: 'Item not found' });
    }
    return res.status(200).json(result[0]);
  });
});

const PORT = 8080;
app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`);
});