41 lines
1.6 KiB
SQL
41 lines
1.6 KiB
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role ENUM('curator', 'helper', 'user') DEFAULT 'user',
|
|
status ENUM('active', 'blocked') DEFAULT 'active',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS tickets (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
helper_id INT,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
category VARCHAR(255),
|
|
priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
|
|
status ENUM('open', 'in_progress', 'awaiting_response', 'closed') DEFAULT 'open',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
FOREIGN KEY (helper_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
ticket_id INT NOT NULL,
|
|
user_id INT NOT NULL,
|
|
message TEXT,
|
|
file_path VARCHAR(255),
|
|
file_name VARCHAR(255),
|
|
file_type VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Seed initial curator
|
|
-- Password is 'admin123'
|
|
INSERT IGNORE INTO users (username, password_hash, role) VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'curator');
|