imc-vibe/backend/internal/db/queries/schema.sql

52 lines
2.1 KiB
SQL

-- ISPmail tables (existing)
CREATE TABLE IF NOT EXISTS virtual_domains (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS virtual_users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
domain_id INT UNSIGNED NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(150) NOT NULL,
quota BIGINT DEFAULT 0,
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS virtual_aliases (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
domain_id INT UNSIGNED NOT NULL,
source VARCHAR(100) NOT NULL,
destination VARCHAR(100) NOT NULL,
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- IMC application tables
CREATE TABLE IF NOT EXISTS imc_users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
role ENUM('admin','user') DEFAULT 'user',
domain_id INT UNSIGNED NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS imc_login_attempts (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL,
ip_address VARCHAR(45) NOT NULL,
attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
successful BOOLEAN DEFAULT FALSE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS imc_users2domains (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
domain_id INT UNSIGNED NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_user_domain (user_id, domain_id),
FOREIGN KEY (user_id) REFERENCES imc_users(id) ON DELETE CASCADE,
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;