CREATE DATABASE IF NOT EXISTS dnmeta CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE dnmeta;
CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, first_name VARCHAR(100) DEFAULT '', last_name VARCHAR(100) DEFAULT '', role ENUM('admin','user') DEFAULT 'user', status ENUM('active','inactive','pending') DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_login TIMESTAMP NULL, created_by INT NULL);
CREATE TABLE IF NOT EXISTS settings (id INT AUTO_INCREMENT PRIMARY KEY, setting_key VARCHAR(100) UNIQUE NOT NULL, setting_value TEXT, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
INSERT INTO settings (setting_key, setting_value) VALUES ('allow_signups','false'),('maintenance_mode','false'),('site_name','DNmeta') ON DUPLICATE KEY UPDATE setting_value=setting_value;
INSERT INTO users (email,password_hash,first_name,last_name,role,status) VALUES ('admin@dnmeta.com','$2y$12$LCCxgGJRpDhHWVT.0p7XCuqwVR0bm4JThF3SNmX2jFB/3qhqrBpOO','Elie','Bot','admin','active') ON DUPLICATE KEY UPDATE email=email;
CREATE USER IF NOT EXISTS 'dnmeta_user'@'localhost' IDENTIFIED BY 'DNmeta@2024!Secure';
GRANT ALL PRIVILEGES ON dnmeta.* TO 'dnmeta_user'@'localhost';
FLUSH PRIVILEGES;
