-- Baltique OS Core 2.0.1 — Official localization, role workspaces and mobile staff platform
ALTER TABLE users ADD COLUMN preferred_locale VARCHAR(8) NULL AFTER status;
CREATE TABLE IF NOT EXISTS enterprise_i18n_overrides (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,locale VARCHAR(8) NOT NULL,translation_key VARCHAR(190) NOT NULL,translation_value TEXT NOT NULL,context_label VARCHAR(190) NULL,active TINYINT(1) NOT NULL DEFAULT 1,updated_by_user_id BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL,PRIMARY KEY(id),UNIQUE KEY uq_i18n_override(locale,translation_key)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS enterprise_role_module_access (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,role_key VARCHAR(80) NOT NULL,module_key VARCHAR(120) NOT NULL,access_level VARCHAR(30) NOT NULL DEFAULT 'view',visible_in_nav TINYINT(1) NOT NULL DEFAULT 1,active TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL,PRIMARY KEY(id),UNIQUE KEY uq_role_module(role_key,module_key)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS enterprise_role_workspace_preferences (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,user_id BIGINT UNSIGNED NOT NULL,widget_key VARCHAR(120) NOT NULL,visible_flag TINYINT(1) NOT NULL DEFAULT 1,sort_order INT NOT NULL DEFAULT 100,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL,PRIMARY KEY(id),UNIQUE KEY uq_workspace_pref(user_id,widget_key)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS enterprise_mobile_devices (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,user_id BIGINT UNSIGNED NOT NULL,device_uuid VARCHAR(120) NOT NULL,platform VARCHAR(30) NOT NULL DEFAULT 'pwa',device_label VARCHAR(190) NULL,locale VARCHAR(8) NOT NULL DEFAULT 'pl',last_seen_at DATETIME NULL,active TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL,PRIMARY KEY(id),UNIQUE KEY uq_mobile_device(user_id,device_uuid)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS enterprise_mobile_push_subscriptions (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,user_id BIGINT UNSIGNED NOT NULL,device_uuid VARCHAR(120) NOT NULL,endpoint_hash CHAR(64) NOT NULL,subscription_json JSON NOT NULL,permission_status VARCHAR(30) NOT NULL DEFAULT 'granted',active TINYINT(1) NOT NULL DEFAULT 1,last_seen_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL,PRIMARY KEY(id),UNIQUE KEY uq_mobile_push_endpoint(endpoint_hash),KEY idx_mobile_push_user(user_id,active)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS enterprise_mobile_notification_queue (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,user_id BIGINT UNSIGNED NULL,role_key VARCHAR(80) NULL,title VARCHAR(190) NOT NULL,body_text TEXT NOT NULL,target_url VARCHAR(255) NOT NULL DEFAULT '/mobile/index.php',priority VARCHAR(30) NOT NULL DEFAULT 'normal',status VARCHAR(30) NOT NULL DEFAULT 'draft',send_after DATETIME NULL,sent_at DATETIME NULL,evidence_json JSON NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL,PRIMARY KEY(id),KEY idx_mobile_notification(status,send_after),KEY idx_mobile_notification_user(user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
