122 lines
3.8 KiB
JavaScript
122 lines
3.8 KiB
JavaScript
import mysql from 'mysql2/promise';
|
|
|
|
const pool = mysql.createPool({
|
|
host: process.env.DB_HOST || 'localhost',
|
|
port: parseInt(process.env.DB_PORT || '3306'),
|
|
user: process.env.DB_USER || 'root',
|
|
password: process.env.DB_PASSWORD || 'scrumpass',
|
|
database: process.env.DB_NAME || 'scrum_manager',
|
|
waitForConnections: true,
|
|
connectionLimit: 10,
|
|
queueLimit: 0,
|
|
});
|
|
|
|
export async function initDB() {
|
|
const conn = await pool.getConnection();
|
|
try {
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
role VARCHAR(50) NOT NULL DEFAULT 'employee',
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
color VARCHAR(20) DEFAULT '#818cf8',
|
|
avatar VARCHAR(10) DEFAULT '',
|
|
dept VARCHAR(100) DEFAULT ''
|
|
)
|
|
`);
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
title VARCHAR(500) NOT NULL,
|
|
description TEXT,
|
|
status ENUM('todo','inprogress','review','done') NOT NULL DEFAULT 'todo',
|
|
priority ENUM('critical','high','medium','low') NOT NULL DEFAULT 'medium',
|
|
assignee_id VARCHAR(36),
|
|
reporter_id VARCHAR(36),
|
|
due_date DATE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE SET NULL
|
|
)
|
|
`);
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS subtasks (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
task_id VARCHAR(36) NOT NULL,
|
|
title VARCHAR(500) NOT NULL,
|
|
done BOOLEAN DEFAULT FALSE,
|
|
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS comments (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
task_id VARCHAR(36) NOT NULL,
|
|
user_id VARCHAR(36),
|
|
text TEXT NOT NULL,
|
|
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
)
|
|
`);
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS activities (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
task_id VARCHAR(36) NOT NULL,
|
|
text TEXT NOT NULL,
|
|
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS task_tags (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
task_id VARCHAR(36) NOT NULL,
|
|
tag VARCHAR(100) NOT NULL,
|
|
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_task_tag (task_id, tag)
|
|
)
|
|
`);
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS dependencies (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
task_id VARCHAR(36) NOT NULL,
|
|
depends_on_user_id VARCHAR(36),
|
|
description TEXT NOT NULL,
|
|
resolved BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (depends_on_user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
)
|
|
`);
|
|
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS notifications (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
user_id VARCHAR(36) NOT NULL,
|
|
type VARCHAR(50) NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
message TEXT,
|
|
link VARCHAR(255),
|
|
is_read BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
console.log('✅ Database tables initialized');
|
|
} finally {
|
|
conn.release();
|
|
}
|
|
}
|
|
|
|
export default pool;
|