CREATE TABLE IF NOT EXISTS admins(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(64) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,is_root TINYINT(1) NOT NULL DEFAULT 0,active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS license_keys(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,license_key VARCHAR(96) UNIQUE NOT NULL,max_devices INT UNSIGNED NOT NULL DEFAULT 1,expires_at DATETIME NULL,created_by INT UNSIGNED NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX(expires_at),CONSTRAINT fk_key_admin FOREIGN KEY(created_by) REFERENCES admins(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS key_devices(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,key_id BIGINT UNSIGNED NOT NULL,device_id VARCHAR(191) NOT NULL,first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uq_key_device(key_id,device_id),CONSTRAINT fk_dev_key FOREIGN KEY(key_id) REFERENCES license_keys(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS api_logs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,key_text VARCHAR(96),device_id VARCHAR(191),ip VARCHAR(64),result VARCHAR(32),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;