CREATE DATABASE IF NOT EXISTS chat_restriction;
USE chat_restriction;
-- Table storing user information
CREATE TABLE IF NOT EXISTS users (
id INT PRIMARY KEY AUTO_INCREMENT,
nickname VARCHAR(255) UNIQUE,
ip VARCHAR(45),
authid VARCHAR(255)
);
CREATE INDEX idx_users_authid ON users(authid);
-- Table storing server information
CREATE TABLE IF NOT EXISTS servers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255)
);
-- Table storing restriction information
CREATE TABLE IF NOT EXISTS restrictions (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
server_id INT,
duration INT,
restriction_flags VARCHAR(10),
reason TEXT,
admin_user_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (server_id) REFERENCES servers(id),
FOREIGN KEY (admin_user_id) REFERENCES users(id)
);
CREATE INDEX idx_restrictions_user_id ON restrictions(user_id);
CREATE INDEX idx_restrictions_server_id ON restrictions(server_id);
CREATE INDEX idx_restrictions_created_at ON restrictions(created_at);
CREATE INDEX idx_restrictions_is_active ON restrictions(is_active);
-- View that returns active restrictions
CREATE
OR REPLACE VIEW active_restrictions AS
SELECT
r.id,
r.user_id,
r.server_id,
r.duration,
r.restriction_flags,
r.reason,
r.admin_user_id,
r.created_at,
r.is_active
FROM
restrictions r
JOIN users u ON r.user_id = u.id
JOIN servers s ON r.server_id = s.id
WHERE
r.is_active = TRUE
AND (
r.duration = 0
OR TIMESTAMPADD(MINUTE, r.duration, r.created_at) > NOW()
);
