-- 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;