SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS users(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password VARCHAR(255) NOT NULL,role ENUM('admin','user') NOT NULL DEFAULT 'user',status ENUM('active','inactive') NOT NULL DEFAULT 'active',last_login_at DATETIME NULL,created_at DATETIME NOT NULL,updated_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS login_attempts(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,ip VARCHAR(45) NOT NULL,email VARCHAR(190) NOT NULL,success TINYINT(1) NOT NULL DEFAULT 0,created_at DATETIME NOT NULL,INDEX(ip,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS plans(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,days INT NOT NULL,traffic_gb DECIMAL(10,2) NOT NULL,max_devices INT NOT NULL DEFAULT 1,price DECIMAL(15,2) NOT NULL DEFAULT 0,status ENUM('active','inactive') NOT NULL DEFAULT 'active',created_at DATETIME NOT NULL,updated_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS servers(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,type ENUM('direct','tunnel') NOT NULL,base_url VARCHAR(255) NOT NULL,web_path VARCHAR(190) NOT NULL DEFAULT '',api_username TEXT NOT NULL,api_password TEXT NOT NULL,inbound_id INT NOT NULL,public_host VARCHAR(255) NOT NULL,remark_prefix VARCHAR(80) NOT NULL DEFAULT 'HOSTINGSHOP',verify_ssl TINYINT(1) NOT NULL DEFAULT 1,status ENUM('active','inactive','maintenance') NOT NULL DEFAULT 'active',created_at DATETIME NOT NULL,updated_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS user_server_access(user_id BIGINT UNSIGNED NOT NULL,server_id BIGINT UNSIGNED NOT NULL,created_at DATETIME NOT NULL,PRIMARY KEY(user_id,server_id),CONSTRAINT usa_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,CONSTRAINT usa_server FOREIGN KEY(server_id) REFERENCES servers(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS services(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,plan_id BIGINT UNSIGNED NOT NULL,server_id BIGINT UNSIGNED NOT NULL,inbound_id INT NOT NULL,client_id VARCHAR(80) NOT NULL UNIQUE,client_email VARCHAR(190) NOT NULL UNIQUE,sub_id VARCHAR(80) NULL,total_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,used_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,started_at DATETIME NOT NULL,expires_at DATETIME NOT NULL,status ENUM('pending','active','expired','disabled','error') NOT NULL DEFAULT 'pending',last_sync_at DATETIME NULL,created_at DATETIME NOT NULL,updated_at DATETIME NULL,CONSTRAINT srv_user FOREIGN KEY(user_id) REFERENCES users(id),CONSTRAINT srv_plan FOREIGN KEY(plan_id) REFERENCES plans(id),CONSTRAINT srv_server FOREIGN KEY(server_id) REFERENCES servers(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_logs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NULL,action VARCHAR(100) NOT NULL,entity_type VARCHAR(80) NULL,entity_id VARCHAR(80) NULL,ip VARCHAR(45) NULL,meta JSON NULL,created_at DATETIME NOT NULL,INDEX(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_settings(setting_key VARCHAR(120) PRIMARY KEY,setting_value TEXT NULL,updated_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO app_settings(setting_key,setting_value,updated_at) VALUES ('brand_name','hostingshop',NOW()) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value),updated_at=NOW();