-- CEDP V010: mobile devices, Web Push and idempotent offline synchronization
CREATE TABLE IF NOT EXISTS mobile_devices_v010 (id VARCHAR(36) PRIMARY KEY,user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,device_key VARCHAR(180) NOT NULL,platform VARCHAR(30) NOT NULL DEFAULT 'pwa',device_name VARCHAR(180) NOT NULL DEFAULT '',app_version VARCHAR(30) NOT NULL DEFAULT '1.0.0',locale VARCHAR(20) NOT NULL DEFAULT 'pt-BR',timezone VARCHAR(80) NOT NULL DEFAULT 'America/Sao_Paulo',capabilities JSON NOT NULL DEFAULT '{}',active BOOLEAN NOT NULL DEFAULT TRUE,last_seen_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,CONSTRAINT uq_mobile_device_user_key_v010 UNIQUE(user_id,device_key));
CREATE TABLE IF NOT EXISTS push_subscriptions_v010 (id VARCHAR(36) PRIMARY KEY,user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,device_id VARCHAR(36) REFERENCES mobile_devices_v010(id) ON DELETE CASCADE,endpoint_hash VARCHAR(128) NOT NULL UNIQUE,encrypted_subscription TEXT NOT NULL,content_encoding VARCHAR(30) NOT NULL DEFAULT 'aes128gcm',active BOOLEAN NOT NULL DEFAULT TRUE,failure_count INTEGER NOT NULL DEFAULT 0,last_success_at TIMESTAMPTZ,last_failure_at TIMESTAMPTZ,created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS mobile_notification_preferences_v010 (user_id VARCHAR(36) PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,push_enabled BOOLEAN NOT NULL DEFAULT TRUE,mentions BOOLEAN NOT NULL DEFAULT TRUE,direct_messages BOOLEAN NOT NULL DEFAULT TRUE,workflow_tasks BOOLEAN NOT NULL DEFAULT TRUE,alerts BOOLEAN NOT NULL DEFAULT TRUE,reports BOOLEAN NOT NULL DEFAULT TRUE,quiet_hours_start VARCHAR(5) NOT NULL DEFAULT '22:00',quiet_hours_end VARCHAR(5) NOT NULL DEFAULT '07:00',updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS offline_sync_operations_v010 (id VARCHAR(36) PRIMARY KEY,user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,device_id VARCHAR(36) REFERENCES mobile_devices_v010(id) ON DELETE SET NULL,client_operation_id VARCHAR(100) NOT NULL,operation_type VARCHAR(60) NOT NULL,payload JSON NOT NULL DEFAULT '{}',status VARCHAR(30) NOT NULL DEFAULT 'received',result JSON NOT NULL DEFAULT '{}',error TEXT NOT NULL DEFAULT '',client_created_at TIMESTAMPTZ,processed_at TIMESTAMPTZ,created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,CONSTRAINT uq_offline_operation_v010 UNIQUE(user_id,client_operation_id));
CREATE TABLE IF NOT EXISTS mobile_sync_cursors_v010 (id VARCHAR(36) PRIMARY KEY,user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,device_id VARCHAR(36) NOT NULL REFERENCES mobile_devices_v010(id) ON DELETE CASCADE,resource_type VARCHAR(60) NOT NULL,cursor_value VARCHAR(200) NOT NULL DEFAULT '',updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,CONSTRAINT uq_mobile_sync_cursor_v010 UNIQUE(user_id,device_id,resource_type));
CREATE INDEX IF NOT EXISTS ix_offline_sync_status_v010 ON offline_sync_operations_v010(user_id,status,created_at);
CREATE TABLE IF NOT EXISTS push_deliveries_v010 (id VARCHAR(36) PRIMARY KEY,notification_id VARCHAR(36) NOT NULL REFERENCES notifications(id) ON DELETE CASCADE,subscription_id VARCHAR(36) NOT NULL REFERENCES push_subscriptions_v010(id) ON DELETE CASCADE,status VARCHAR(30) NOT NULL DEFAULT 'pending',attempts INTEGER NOT NULL DEFAULT 0,response_code INTEGER NOT NULL DEFAULT 0,error TEXT NOT NULL DEFAULT '',delivered_at TIMESTAMPTZ,created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,CONSTRAINT uq_push_delivery_v010 UNIQUE(notification_id,subscription_id));